CODEWITHSUNDEEP
pythonjsonpython-3.xpandasdataframe
Nasir Hussain
Nasir Hussain

Reputation: 105

pandas Dataframe to JSON list of dict

This is the sample dataset

datetime  2017-12-24  2017-12-31                             
a                1.5         0.5
b                0.0         1.5
c                1.5         0.0

I'm converting this to json using to_json and i get this output 

{"2017-12-24":{"a":1.5,"b":0.0,"c":1.5},"2017-12-31":{"a":0.5,"b":1.5,"c":0.0}}

What i'm trying to do is to get json a list of dict like this

[{"datetime":"2017-12-24","a":1.5,"b":0.0,"c":1.5},{"datetime":"2017-12-31", "a":0.5,"b":1.5,"c":0.0}]

my code is here

df = pd.DataFrame({'2017-12-24': [1.5, 0.0, 1.5], '2017-12-31': [0.5, 1.5, 0.0]}, index=['a', 'b', 'c'])
df.columns.name='datetime'
print(df.to_json())

Upvotes: 3

Views: 2583

Answers (2)

cs95
cs95

Reputation: 402363

transpose, reset_index and to_json

i = df.T.reset_index()
i

     datetime    a    b    c
0  2017-12-24  1.5  0.0  1.5
1  2017-12-31  0.5  1.5  0.0

i.to_json()
'[{"datetime":"2017-12-24","a":1.5,"b":0.0,"c":1.5},{"datetime":"2017-12-31","a":0.5,"b":1.5,"c":0.0}]'

stack + unstack

(df.stack()
   .unstack(0)
   .reset_index()
   .to_json(orient='records'))

'[{"datetime":"2017-12-24","a":1.5,"b":0.0,"c":1.5},{"datetime":"2017-12-31","a":0.5,"b":1.5,"c":0.0}]'

Upvotes: 4

Anton vBR
Anton vBR

Reputation: 18906

You could just reset index and output it? Note: I'm using to_dict here instead of to_json to use a simple print

import pandas as pd

df = pd.DataFrame({'2017-12-24': [1.5, 0.0, 1.5], 
                   '2017-12-31': [0.5, 1.5, 0.0]}, 
                  index=['a', 'b', 'c'])

print(df.T.reset_index().rename(columns={'index':'datetime'}).to_dict('r'))


[{'datetime': '2017-12-24', 'a': 1.5, 'b': 0.0, 'c': 1.5}, 
{'datetime': '2017-12-31', 'a': 0.5, 'b': 1.5, 'c': 0.0}]

Upvotes: 2

Related Questions