Normalize json column and concatenate with rest of dataframe-CodePudding

i am a rookie with writing python scripts, somehow I managed to normalize a single column named 'events' but I wanted to concatenate with other columns (id, symbol, name, calType) as well which are there in the original data frame.

{"id":1670,"symbol":"FX-GBP","name":"London","calType":"C","events":[{"date":"2021-01-01","name":"New Year's Day","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-04-02","name":"Good Friday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-04-05","name":"Easter Monday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-05-03","name":"Early May Bank Holiday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-05-31","name":"Late May Bank Holiday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-08-30","name":"Summer Bank Holiday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-12-27","name":"Christmas OBS","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-12-28","name":"Boxing Day OBS","type":"FULL","updateTime":"2021-06-18T18:15:55"}]}

How can this be done?

import requests
import json, pandas as pd
from pandas.io.json import json_normalize
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)

df=pd.json_normalize(json_data,'events')

df.to_csv("output.csv")

This is how it should be

CodePudding user response：

Try this:

import pandas as pd

json_response = {"id":1670,"symbol":"FX-GBP","name":"London","calType":"C","events":[{"date":"2021-01-01","name":"New Year's Day","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-04-02","name":"Good Friday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-04-05","name":"Easter Monday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-05-03","name":"Early May Bank Holiday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-05-31","name":"Late May Bank Holiday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-08-30","name":"Summer Bank Holiday","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-12-27","name":"Christmas OBS","type":"FULL","updateTime":"2021-06-18T18:15:55"},{"date":"2021-12-28","name":"Boxing Day OBS","type":"FULL","updateTime":"2021-06-18T18:15:55"}]}

df_rest = pd.DataFrame(json_response)
df_event = pd.json_normalize(json_response, 'events')
complete_df = pd.concat([df_rest, df_event], axis=1)
print(complete_df)