Create pandas df from JSON where Column Headers and Rows are in separate arrays

Question

An API is sending me data in the form of:

{
    uselessInfo: blabla,
    headers: [
        {type:DIMENSION, name:DATE},
        {type:DIMENSION, name:COUNTRY},
        {type:METRIC, name:REVENUE}
    ],
    rows: [
        ["2014-09-29","Germany",435],
        ["2014-09-28","USA",657],
        ...
        ["2014-09-13","Spain",321]
    ],
    average: [ some unwanted info ],
    total: [ some unwanted info ]
}

I want to create a dataframe in pandas from this object, using only:

the headers info to name my columns
the rows for the data
And ignore the rest.

So far, I have tried to change parameters in pandas' ".read_json" but without any good results. I could not find any similar examples.

unutbu · Accepted Answer · 2014-09-30 12:42:51Z

4

pandas.read_json can not turn all JSONs into DataFrames. The JSON has to have one of the formats described in the docs under the orient parameter.

Instead, use json.loads to convert the data into a Python object, then pick out the header and rows to form the DataFrame:

import json
import pandas as pd

content = '''{
    "uselessInfo": "blabla", 
    "headers": [
        { "type": "DIMENSION", "name": "DATE" }, 
        { "type": "DIMENSION", "name": "COUNTRY" }, 
        { "type": "METRIC", "name": "REVENUE" }
    ],
    "rows": [ [ "2014-09-29", "Germany", 435 ], 
        [ "2014-09-28", "USA", 657 ], 
        [ "2014-09-13", "Spain", 321 ]
    ], 
    "average": [ "some unwanted info" ], 
    "total": [ "some unwanted info" ]
}'''
data = json.loads(content)


columns = [dct['name'] for dct in data['headers']]
df = pd.DataFrame(data['rows'], columns=columns)
print(df)

yields

         DATE  COUNTRY  REVENUE
0  2014-09-29  Germany      435
1  2014-09-28      USA      657
2  2014-09-13    Spain      321

edited Sep 30, 2014 at 12:42

answered Sep 30, 2014 at 12:36

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

1 Comment

xav Over a year ago

Wow, sounds like a very straightforward workaround but could not think of it by myself :) Thanks a lot!

Collectives™ on Stack Overflow

Create pandas df from JSON where Column Headers and Rows are in separate arrays

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related