Creating JSON keys with array values in Pandas

Question

I have a dataframe that gets manipulated a few times. The end result is a data frame that looks like this:

    Company volume
1   NV      14
2   GD      8
3   AB      6

I covert this dataframe into json format like so:

formattedDF.to_json(orient='columns')

However, I get something like this:

'{"company":{"1":"NV","2":"GD","3":"AB"},"volume":{"1":14,"2":8,"3":6}}'

Which is very close to what I want, but the format I'm looking for is:

'{"company": ["NV", "GD","AB"], "volume":[14, 8, 6}]'

Where the keys give the values in array format instead. How can I achieve this?

U13-Forward · Accepted Answer · 2018-08-16 04:14:53Z

5

Or:

print(df.to_dict(orient='list'))

Output:

{'Company': ['NV', 'GD', 'AB'], 'volume': [14, 8, 6]}

answered Aug 16, 2018 at 4:14

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3483203 · Accepted Answer · 2018-08-16 04:14:12Z

2

Using a dictionary comprehension:

{k: df[k].tolist() for k in df}

Or using apply(list):

df.T.apply(list, 1).to_dict()

Both produce:

{'Company': ['NV', 'GD', 'AB'], 'volume': [14, 8, 6]}

answered Aug 16, 2018 at 4:14

user3483203

51.3k10 gold badges72 silver badges104 bronze badges