How to use json normalize

Question

I am using the below code to parse JSON data to CSV from rest API, but there are some nested objects which I need in different columns, I have tried using JSON normalize but couldn't get through. can anyone please advise?

import requests
import json, pandas as pd
import csv
import urllib

URL = "https://dsv.ihsmvals.com/holidays/v1.0/symbol/FX-GBP/startDate/20210101/endDate/20211231"
response = urllib.request.urlopen(URL)

print(response)
text = response.read()
json_data = json.loads(text)

print(json_data)
df = pd.read_json(URL)
bn = pd.DataFrame(df.events.values.tolist())
pd.json_normalize(bn).head()
bn.to_csv("output.csv")

This is how the output looks

This is how it should be

As you are asking about `pandas`' function please consider adding `pandas` tag — Daweo, Oct 13 '21 at 07:31

U12-Forward · Answer 1 · 2021-10-13T07:46:46.040

0

You probably need to specify the record_path:

bn = pd.json_normalize(bn)
bn.join(pd.json_normalize(bn.pop('events'))).head()

edited Oct 13 '21 at 07:46

answered Oct 13 '21 at 07:32

U12-Forward

65,118
12
70
89

getting this - TypeError: string indices must be integers – Naina Oct 13 '21 at 07:45
@Naina Edited my answer – U12-Forward Oct 13 '21 at 07:46
@jezrael Done.. – U12-Forward Oct 13 '21 at 08:12
df=pd.json_normalize(json_data,'events') this worked but it it now only saving events column in csv – Naina Oct 15 '21 at 10:05
@Naina `bn.join(pd.json_normalize(bn.pop('events'))).head()` – U12-Forward Oct 15 '21 at 10:05
what is bn here – Naina Oct 15 '21 at 10:07
@Naina `bn = pd.json_normalize(bn)` – U12-Forward Oct 15 '21 at 10:07
is there a way we can have a chat to discuss it further ? – Naina Oct 15 '21 at 10:08
bn=pd.json_normalize(bn) NameError: name 'bn' is not defined – Naina Oct 15 '21 at 10:10

How to use json normalize

1 Answers1