Conversion of nested dictionary into data frame in Python

user_14117742 0
Viewed 41

My list/dictionary is nested with lists for different items in it like this:

scores = [{"Student":"Adam","Subjects":[{"Name":"Math","Score":85},{"Name":"Science","Score":90}]},
     {"Student":"Bec","Subjects":[{"Name":"Math","Score":70},{"Name":"English","Score":100}]}]

If I use pd.DataFrame directly on the dictionary I get:

enter image description here

What should I do in order to get a data frame that looks like this:

Student   Subject.Name   Subject.Score
 Adam         Math            85
 Adam         Science         90
 Bec          Math            70
 Bec          English         100

?

Thanks very much

pandas python
1 Answers

Use json_normalize with rename:

df = (pd.json_normalize(scores, 'Subjects','Student')
        .rename(columns={'Name':'Subject.Name','Score':'Subject.Score'}))
print (df)
  Subject.Name  Subject.Score Student
0         Math             85    Adam
1      Science             90    Adam
2         Math             70     Bec
3      English            100     Bec

Or list with dict comprehension and DataFrame constructor:

df = (pd.DataFrame([{**x, **{f'Subject.{k}': v for k, v in y.items()}} 
                     for x in scores for y in x.pop('Subjects')]))
print (df)
  Student Subject.Name  Subject.Score
0    Adam         Math             85
1    Adam      Science             90
2     Bec         Math             70
3     Bec      English            100
user_29010020
Related