Is there a better way to convert JSON into DataFrame?

user_8269522 0
Viewed 57

Want to convert JSON object to DataFrame.

This my JSON object

data = {'situation': {'OpenPlay': {'shots': 282,
   'goals': 33,
   'xG': 36.38206055667251,
   'against': {'shots': 276, 'goals': 29, 'xG': 33.0840025995858}},
  'FromCorner': {'shots': 46,
   'goals': 2,
   'xG': 2.861613758839667,
   'against': {'shots': 46, 'goals': 4, 'xG': 3.420699148438871}},
  'DirectFreekick': {'shots': 19,
   'goals': 1,
   'xG': 1.0674087516963482,
   'against': {'shots': 10, 'goals': 0, 'xG': 0.6329493299126625}},
  'SetPiece': {'shots': 14,
   'goals': 1,
   'xG': 0.6052199145779014,
   'against': {'shots': 21, 'goals': 1, 'xG': 2.118571280501783}},
  'Penalty': {'shots': 6,
   'goals': 6,
   'xG': 4.5670130252838135,
   'against': {'shots': 2, 'goals': 1, 'xG': 1.5222634673118591}}}

Want output:

enter image description here

My code:

df = pd.json_normalize(data['situation']['OpenPlay'])

for i in range(1,4):
    df = df.append(pd.json_normalize(data['situation'][type_of_play[i]]))
    
df = df.reset_index()

any efficient way of doing this?

pandas python json
3 Answers

First of all, your data lack of a '}' at the end. Try this code:

obj = [pd.json_normalize(data['situation'][e]) for e in data['situation']]
pd.concat(obj, ignore_index=True)

enter image description here

user_95727800

You can load the data into a dataframe regularly, then run json_normalize on the column that contains the remaining dicts, and join it with the main dataframe:

df = pd.DataFrame(data['situation']).T.reset_index()
df = df.join(pd.json_normalize(df.against), lsuffix='_against', how='left').drop(columns=['against'])

Result:

index shots_against goals_against xG_against shots goals xG
0 OpenPlay 282 33 36.3821 276 29 33.084
1 FromCorner 46 2 2.86161 46 4 3.4207
2 DirectFreekick 19 1 1.06741 10 0 0.632949
3 SetPiece 14 1 0.60522 21 1 2.11857
4 Penalty 6 6 4.56701 2 1 1.52226
user_113807950

For efficiency, it is best to process the data outside Pandas, within the dictionary, and then create the dataframe.

You could use jmespath to extract the data, before passing it into pandas; should be more efficient; you could run tests to check the speed:

Summary idea for jmespath; if you are accessing a key, use ., if it's an array/list, use []:

import jmespath 
expression = """{shots: *.*.shots[], 
                 goals: *.*.goals[], 
                   xG : *.*.xG[], 
                 against_shots: *.*.against.shots[], 
                 against_goals: *.*.against.goals[], 
                 against_XG: *.*.against.xG[]
                 }"""
expression = jmespath.compile(expression)
expression = expression.search(data)
#dataframe
pd.DataFrame(expression)
   shots  goals         xG  against_shots  against_goals  against_XG
0    282     33  36.382061            276             29   33.084003
1     46      2   2.861614             46              4    3.420699
2     19      1   1.067409             10              0    0.632949
3     14      1   0.605220             21              1    2.118571
4      6      6   4.567013              2              1    1.522263

jmespath can be convenient, especially as the nesting in the dict/json becomes more convoluted; most efficient however, would be to use the dictionary data structure directly:

from collections import defaultdict
df = defaultdict(list)
for key, value in data['situation'].items():
     df['shots'].append(value['shots'])
     df['goals'].append(value['goals'])
     df['xG'].append(value['xG'])
     df['against_shots'].append(value['against']['shots'])
     df['against_goals'].append(value['against']['goals'])
     df['against_xG'].append(value['against']['xG'])

# create dataframe
pd.DataFrame(df)
 
   shots  goals         xG  against_shots  against_goals  against_xG
0    282     33  36.382061            276             29   33.084003
1     46      2   2.861614             46              4    3.420699
2     19      1   1.067409             10              0    0.632949
3     14      1   0.605220             21              1    2.118571
4      6      6   4.567013              2              1    1.522263
user_71757130
Related