Pandas convert JSON string to Dataframe - Python

Question

i have a json string that need to be convert to a dataframe with desired column name.

my_json = {'2017-01-03': {'open': 214.86,
  'high': 220.33,
  'low': 210.96,
  'close': 216.99,
  'volume': 5923254},
'2017-12-29': {'open': 316.18,
  'high': 316.41,
  'low': 310.0,
  'close': 311.35,
  'volume': 3777155}}

use below code doesn't give the format i want

pd.DataFrame.from_dict(json_normalize(my_json), orient='columns')

my expected format is below

Not sure how to do it?

what happens if you try my_json { "date": ["2017", "2018"] } ? Put your values as an array when referring to the same key. — Shreamy
– Shreamy, Commented Dec 20, 2019 at 1:14

oppressionslayer · Accepted Answer · 2019-12-20 01:36:59Z

4

You can also do it this way to get the exact format:

pd.DataFrame(my_json).T.rename_axis(columns='Date')                                                                                                                                  

Date          open    high     low   close     volume
2017-01-03  214.86  220.33  210.96  216.99  5923254.0
2017-12-29  316.18  316.41  310.00  311.35  3777155.0

You can also read directly from the data to get the format with the missing date:

pd.DataFrame.from_dict(my_json, orient='index').rename_axis(columns='Date')                                                                                                          

Date          open    high     low   close   volume
2017-01-03  214.86  220.33  210.96  216.99  5923254
2017-12-29  316.18  316.41  310.00  311.35  3777155

edited Dec 20, 2019 at 1:36

answered Dec 20, 2019 at 1:22

oppressionslayer

7,2242 gold badges11 silver badges26 bronze badges

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

Comments

Andy L. · Accepted Answer · 2019-12-20 01:22:50Z

3

I assume my_json is dictionary. I.e, it it not in string format

pd.DataFrame.from_dict(my_json, orient='index').rename_axis('date').reset_index()

Out[632]:
         date    open    high     low   close   volume
0  2017-01-03  214.86  220.33  210.96  216.99  5923254
1  2017-12-29  316.18  316.41  310.00  311.35  3777155

If my_json in string format, you need call ast.iteral_eval to convert to dictionary before processing

import ast

d = ast.literal_eval(my_json)
pd.DataFrame.from_dict(d, orient='index').rename_axis('date').reset_index()

answered Dec 20, 2019 at 1:22

Andy L.

25.3k4 gold badges20 silver badges30 bronze badges

Comments

Lambda · Accepted Answer · 2019-12-20 01:14:46Z

2

You can use DataFrame directly, then transpose it and then reset_index

pd.DataFrame(my_json).T.reset_index().rename(columns={"index":"date"})

answered Dec 20, 2019 at 1:14

Lambda

1,3941 gold badge9 silver badges12 bronze badges

Collectives™ on Stack Overflow

Pandas convert JSON string to Dataframe - Python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related