Create Pandas Df From Json Where Column Headers And Rows Are In Separate Arrays

An API is sending me data in the form of: { uselessInfo: blabla, headers: [ {type:DIMENSION, name:DATE}, {type:DIMENSION, name:COUNTRY}, {type:METRI

Solution 1:

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'] fordctin data['headers']]
df = pd.DataFrame(data['rows'], columns=columns)
print(df)

yields

DATECOUNTRYREVENUE02014-09-29  Germany43512014-09-28      USA65722014-09-13    Spain321