Load Multi Nested Dict/json Into Pandas

I'm trying to load a pretty confusing multi-nested JSON into pandas. I'm already using json_normalize but trying to figure out how I'd join 2 similair nested dicts as well as unpac

Solution 1:

I believe you can use:

need = ['member.id', 'member.name', 'member.level', 'member.attacks','id']
df1 = json_normalize(war['teams'],
                     'members',
                     ['id', 'name', 'level', 'attacks'], 
                     record_prefix='member.')[need]
#print (df1)

df2 = json_normalize(war['opponent'],
                     'members',
                     ['id', 'name', 'level', 'attacks'], 
                     record_prefix='member.')[need]
#print (df2)


df1.columns = np.where(df1.columns.str.startswith('member.'), 
                       df1.columns.str.split('.', n=1).str[1],
                       'member.' + df1.columns)
df2.columns = np.where(df2.columns.str.startswith('member.'), 
                       df2.columns.str.split('.', n=1).str[1],
                       'member.' + df2.columns)


df = pd.concat([df1, df2], sort=False, ignore_index=True)
print (df)
  id   name  level                                            attacks  \
01   John     12                                                NaN   
12    Tom     11  [{'attackerTag': '2', 'defenderTag': '4', 'dam...   
23  Betty     17  [{'attacker_id': '3', 'defender_id': '1', 'dam...   
34   Fred      9  [{'attacker_id': '4', 'defender_id': '9', 'dam...   

   member.id  
01234567911234567929876543213987654321

Solution 2:

Try using this four-liner:

d=war['teams']['members']+war['teams']['opponent']['members']
df = pd.DataFrame(d)
df = df.iloc[:,:4][['id','name','level','attacks']]
df['member.team_id']=[war['teams']['opponent']['id'] if i in war['teams']['opponent']['members'] else war['teams']['id'] for i in d]
print(df)

Output:

  id   name  level                                            attacks  \
01   John     12                                                NaN   
12    Tom     11  [{'attackerTag': '2', 'defenderTag': '4', 'dam...   
23  Betty     17  [{'attacker_id': '3', 'defender_id': '1', 'dam...   
34   Fred      9  [{'attacker_id': '4', 'defender_id': '9', 'dam...   

  member.team_id  
01234567911234567929876543213987654321