dsugasa dsugasa - 1 year ago 163
JSON Question

JSON to Dataframe with nested dict

I have some json data that I want to put into a pandas dataframe. The json looks like this:

{'date': [20170629,
20170630,
20170703,
20170705,
20170706,
20170707],
'errorMessage': None,
'seriesarr': [{'chartOnlyFlag': 'false',
'dqMaxValidStr': None,
'expression': 'DB(FXO,V1,EUR,USD,7D,VOL)',
'freq': None,
'frequency': None,
'iDailyDates': None,
'label': '',
'message': None,
'plotPoints': [0.0481411225888,
0.0462401214563,
0.0587196848727,
0.0765737640932,
0.0678912611279,
0.0675766942022],
}


I am trying to create a pandas DataFrame with
'date'
as the index and
'plotPoints'
as a second column. I don't need any of the other infomation.

I've tried

df = pd.io.json.json_normalize(data, record_path = 'date', meta = ['seriesarr', ['plotPoints']])


When I do this I get the following error:

KeyError: ("Try running with errors='ignore' as key %s is not always present", KeyError('plotPoints',)


Any help with this is appreciated.

Thanks!

Answer Source

IIUC, json_normalize may not be able to help you here. It might instead just be easier to extract that data and then load it into a dataframe directly. If need be, convert to datetime using pd.to_datetime:

date = data.get('date')
plotPoints = data.get('seriesarr')[0].get('plotPoints')

df = pd.DataFrame({'date' : pd.to_datetime(date, format='%Y%m%d'),
                   'plotPoints' : plotPoints})
df
        date  plotPoints
0 2017-06-29    0.048141
1 2017-06-30    0.046240
2 2017-07-03    0.058720
3 2017-07-05    0.076574
4 2017-07-06    0.067891
5 2017-07-07    0.067577

This is under the assumption that your data is exactly as shown in the question.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download