Pandas read nested json

Question

I am curious how I can use pandas to read nested json with the following structure:

{ "number": "", "date": "01.10.2016", "name": "R 3932", "locations": [ { "depTimeDiffMin": "0", "name": "Spital am Pyhrn Bahnhof", "arrTime": "", "depTime": "06:32", "platform": "2", "stationIdx": "0", "arrTimeDiffMin": "", "track": "R 3932" }, { "depTimeDiffMin": "0", "name": "Windischgarsten Bahnhof", "arrTime": "06:37", "depTime": "06:40", "platform": "2", "stationIdx": "1", "arrTimeDiffMin": "1", "track": "" }, { "depTimeDiffMin": "", "name": "Linz/Donau Hbf", "arrTime": "08:24", "depTime": "", "platform": "1A-B", "stationIdx": "22", "arrTimeDiffMin": "1", "track": "" } ] } 

The array is stored here as json. I would prefer it to be expanded into columns.

 pd.read_json("/myJson.json", orient='records') 

Edit

Thanks for the first answers. I have to clarify my question: Smoothing nested attributes in an array is optional. It would be normal only [A, B, C] to combine df.locations ['name'].

My file contains several JSON objects (one per line). I would like to keep a column of number, date, name and locations. However, I will need to join the location.

 allLocations = "" isFirst = True for location in result.locations: if isFirst: isFirst = False allLocations = location['name'] else: allLocations += "; " + location['name'] allLocations 

My approach here does not seem effective / pandas style.

Answer 1

You can use json_normalize :

 import json from pandas.io.json import json_normalize with open('myJson.json') as data_file: data = json.load(data_file) df = json_normalize(data, 'locations', ['date', 'number', 'name'], record_prefix='locations_') print (df) locations_arrTime locations_arrTimeDiffMin locations_depTime \ 0 06:32 1 06:37 1 06:40 2 08:24 1 locations_depTimeDiffMin locations_name locations_platform \ 0 0 Spital am Pyhrn Bahnhof 2 1 0 Windischgarsten Bahnhof 2 2 Linz/Donau Hbf 1A-B locations_stationIdx locations_track number name date 0 0 R 3932 R 3932 01.10.2016 1 1 R 3932 01.10.2016 2 22 R 3932 01.10.2016 

EDIT:

You can use read_json with name parsing by the DataFrame constructor and the last groupby using join :

 df = pd.read_json("myJson.json") df.locations = pd.DataFrame(df.locations.values.tolist())['name'] df = df.groupby(['date','name','number'])['locations'].apply(','.join).reset_index() print (df) date name number locations 0 2016-01-10 R 3932 Spital am Pyhrn Bahnhof,Windischgarsten Bahnho...