pandas dataframe from nested JSON

Question

I have the following json as web api response:

{"prices": [
    {
     "start_date": "2016-07-06T00:00:00+02:00",
     "end_date": "2016-07-07T00:00:00+02:00",
     "values": [
    {
    "start_date": "2016-07-06T00:00:00+02:00",
    "end_date": "2016-07-06T00:30:00+02:00",
    "upward_weighted": 45.66,
    "downward_weighted": 20.63,
    "upward_marginal": 30.1,
    "downward_marginal": 12.8,
    "updated_date": "2016-07-07T15:45:36+02:00"
    },
    {
    "start_date": "2016-07-06T00:30:00+02:00",
    "end_date": "2016-07-06T01:00:00+02:00",
    "upward_weighted": 45.66,
    "downward_weighted": 20.63,
    "upward_marginal": 30.1,
    "downward_marginal": 12.8,
    "updated_date": "2016-07-07T15:45:36+02:00"
    }
    ]}
    ]}

And I would to retrieve the prices-> values as a Dataframe.

start_date|end_date|upward_weighted|downward_weighted|...|updated_date|
----------|--------|---------------|-----------------|---|------------|
xxxxxxx   |xxxxxxx |xxxxxxxx       |xxxxxxx          |   |xxxxxx      |
xxxxxxx   |xxxxxxx |xxxxxxxx       |xxxxxxx          |   |xxxxxx      |

When I try pandas.read_json(resp.content) I get a wrong dataframe containing only one column 'prices' with dict.

Is it possible to tell pandas.read_json() to make a DataFrame with prices->values ?

jezrael · Accepted Answer · 2017-05-31 13:48:56Z

3

You can use json_normalize:

from pandas.io.json import json_normalize    
df = json_normalize(d['prices'], 'values')
print (df)
   downward_marginal  downward_weighted                   end_date  \
0               12.8              20.63  2016-07-06T00:30:00+02:00   
1               12.8              20.63  2016-07-06T01:00:00+02:00   

                  start_date               updated_date  upward_marginal  \
0  2016-07-06T00:00:00+02:00  2016-07-07T15:45:36+02:00             30.1   
1  2016-07-06T00:30:00+02:00  2016-07-07T15:45:36+02:00             30.1   

   upward_weighted  
0            45.66  
1            45.66

answered May 31, 2017 at 13:48

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

bAN Over a year ago

Thanks! it works! But I don't get the point what is exactly doing json_normalize?

Anup · Accepted Answer · 2017-05-31 14:47:09Z

1

please try this to solve your problem: Get the values (from Price dictionary) in form of "list of dictionaries" from web api response: such as values = [{}, {}] and pass it into pd dataframe.

 df = pd.DataFrame(values)
 print(df)

you will get which u want. For More details http://pbpython.com/pandas-list-dict.html

answered May 31, 2017 at 14:47

Anup

2001 silver badge13 bronze badges

Collectives™ on Stack Overflow

pandas dataframe from nested JSON

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related