CODEWITHSUNDEEP
pythonpandas
user10256905
user10256905

Reputation: 185

Nested json to one row with pandas python

{"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?

Upvotes: 0

Views: 356

Answers (2)

Corralien
Corralien

Reputation: 120399

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

Upvotes: 0

SeaBean
SeaBean

Reputation: 23217

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

Upvotes: 1

Related Questions