{"name": "Sara", "grade": "1", "school": "Buckeye", "teacher": "Ms. Black", "sci": {"gr": "A", "perc": "93"}, "math": {"gr": "B+", "perc": "88"}, "eng": {"gr": "A-", "perc": "91"}}
I have the json file above (named test) and I am trying to turn it into a dataframe in python using pandas. The pd.read_json(test.csv) command returns two lines 'gr' and 'perc' instead of one. Is there a way to make one row and the nested columns be gr.sci, gr.math, gr.eng, perc.sci, perc.math, perc.eng?
Try with pd.json_normalize(), as follows:
df = pd.json_normalize(test)
Result:
print(df)
name grade school teacher sci.gr sci.perc math.gr math.perc eng.gr eng.perc
0 Sara 1 Buckeye Ms. Black A 93 B+ 88 A- 91
Use pd.json_normalize after convert json file to python data structure:
import pandas as pd
import json
data = json.load('data.json')
df = pd.json_normalize(data)
>>> df
name grade school teacher sci.gr sci.perc math.gr math.perc eng.gr eng.perc
0 Sara 1 Buckeye Ms. Black A 93 B+ 88 A- 91
