Putting JSON API in Pandas Dataframe

Question

I have a problem converting a JSON API into a pandas dataframe. I have the following structure of the json file:

{"place":{"AMS":[{"UTC":"14-11-2017 10:00","ValidUTC":"14-11-2017 00:00","Cardinality":"4",...},{"UTC":"14-11-2017 11:00",...}]}}

Now in my pandas DataFrame I want the columns, UTC, ValidUtc, cardinality, etc. So I tried to use the Json normalize function:

main_api = ('https://api.xxx")
url=main_api
json_data = requests.get(url).json()
df = json_normalize(json_data, 'place', ['AMS'])

and

main_api = ('https://api.xxx")
url=main_api
json_data = requests.get(url).json()
df = json_normalize(json_data, 'place')
df = json_normalize(json_data, 'AMS')

but they do not seem to work. Anyone has an idea about how to convert the json correctly in the pandas DataFrame.

score 0 · Answer 1 · answered Nov 20 '17 at 10:58

0

Refer to JSON to pandas DataFrame it is very well described for normalizing JSON. Besides, you can to pass a parsing function for the json columns.

answered Nov 20 '17 at 10:58

privatevoid

192
5

score 0 · Answer 2 · answered Jan 15 '21 at 18:14

Not sure if I recreated your input correctly (you should add the full json without ...).

data = {"place":{"AMS":[{"UTC":"14-11-2017 10:00","ValidUTC":"14-11-2017 00:00","Cardinality":"4"}, {"UTC":"15-11-2017 10:00","ValidUTC":"15-11-2017 00:00","Cardinality":"5"} ]}}

pd.json_normalize(data['place']['AMS'])

Output

                UTC          ValidUTC Cardinality
0  14-11-2017 10:00  14-11-2017 00:00           4
1  15-11-2017 10:00  15-11-2017 00:00           5

Putting JSON API in Pandas Dataframe

2 Answers2