Pandas read nested json

Question

I am curious how I can use pandas to read nested json of 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": ""         }     ] } 

This here keeps the array as json. I would rather prefer it to be expanded into columns.

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

edit

Thanks for the first answers. I should refine my question: A flattening of the nested attributes in the array is not mandatory. It would be ok to just [A, B, C] concatenate the df.locations['name'].

My file contains multiple JSON objects (1 per line) I would like to keep number, date, name, and locations column. However, I would need to join the locations.

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 to be efficient / pandas style.

Answer 1

You can use json_normalize:

import json  with open('myJson.json') as data_file:         data = json.load(data_file)    df = pd.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 parsing name by DataFrame constructor and last groupby with apply 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...