pandas Dataframe to JSON list of dict

Question

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())

cs95 · Accepted Answer · 2019-01-13 05:00:53Z

4

`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}]'

edited Jan 13, 2019 at 5:00

answered Dec 28, 2017 at 18:03

cs95

406k106 gold badges745 silver badges798 bronze badges

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

3 Comments

Nasir Hussain Over a year ago

df.stack().unstack(0) means df.T So i could simply do ? df.T.reset_index().to_json(orient='records')

cs95 Over a year ago

@NasirHussain It is, so I added that option too :p

Anton vBR Over a year ago

Now that you added the Transpose +1 :)

Anton vBR · Accepted Answer · 2017-12-28 18:17:23Z

2

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}]

edited Dec 28, 2017 at 18:17

answered Dec 28, 2017 at 18:08

Anton vBR

19k6 gold badges47 silver badges47 bronze badges

3 Comments

cs95 Over a year ago

Ah sorry, just got the transpose option in :p

Anton vBR Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ hahah nps... Thought you overcomplicated it but I see you thought about it too.

cs95 Over a year ago

Yep, that shouldn't detract from your answer. You got my upvote for that.

Collectives™ on Stack Overflow

pandas Dataframe to JSON list of dict

2 Answers 2

`transpose`, `reset_index` and `to_json`

`stack` + `unstack`

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

transpose, reset_index and to_json

stack + unstack

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related

`transpose`, `reset_index` and `to_json`

`stack` + `unstack`