converting json to dataframe in python

Question

I want to convert multiple JSON files into one dataframe.

Below is the JSON object:

{'alerts': [{'affected_services': {'elevators': [],
                                   'services': [{'mode_name': 'Subway',
                                                 'route_id': 'Red',
                                              'route_name': 'Red''Line',
'route_type': '1',
'stop_id': '70061',
'stop_name': 'Alewife'},
{'mode_name': 'Subway',
'route_id': 'Red',
'route_name': 'Red ''Line',
'route_type': '1',
'stop_id': '70063',
'stop_name': 'Davis ''- ''Inbound'}]},
                         'alert_id': 176434,
                         'alert_lifecycle': 'Upcoming',
                         'cause': 'UNKNOWN_CAUSE',
                         'created_dt': '1491332705',
                         'description_text': 'Due to the Floating Slab '
                             'Project, buses will replaceRed ',
                         'effect': 'DETOUR',
                         'effect_name': 'Shuttle',
                         'effect_periods': [{'effect_end': '1493620200',
                         'effect_start': '1493454600'}],
                  'header_text': 'Buses will replace Red Line service',
                         'last_modified_dt': '1491332705',
                         'service_effect_text': 'Red Line shuttle',
                         'severity': 'Severe',
                    'short_header_text': 'Buses will replace Red Line ',
                    'timeframe_text': 'April 29-30',
'url': 'http://www.mbta.com/about_the_mbta/t_projects/default.asp?id=22956'}],
             'stop_id': 'place-alfcl',
             'stop_name': 'Alewife'}

Below is the code I tried:

from pandas.io.json import json_normalize
import pandas as pd
import glob

json_file_path = path_stop +'*'
lambda_file = lambda json_file_path : glob.glob(json_file_path)

for json_file in lambda_file(json_file_path):
    with open(json_file) as json_data:
        result = json_normalize(json_data,'alerts',['affected_services',['elevators','services',['mode_name','route_id','stop_id','stop_name']],'alert_lifecycle','cause','created_dt','effect','effect_name','severity','timeframe_text'],'stop_id','stop_name')

print(result)

Please someone help me. Thanks in advance!

Answer 1

This code should do what you want:

import pandas
import glob
json_files = glob.glob('*.json')
def merge_files(json_files):
    dfs = list()
    for json_file in json_files:
        df = pandas.read_json(json_file)
        dfs.append(df)
    df = pandas.concat(dfs)
    return df

df = merge_files(json_files)

However, I should caution you probably want to massage your input data a bit more in order to understand the shape and contents of the DataFrame that you are building.