Convert dict with list and dict inside to a df

user_13500650 0
Viewed 64

I'm having a bit of an issue. I have a dict looking like this:

example_dict = {"AUTHID":[],"visibility":[], "game_owned": {"game_name": [], "game_playtime":[]}}

And I'm fetching data on the Steam website using ID as a key in the API to get the data. Basically, with some content in it, the dict should look like this:

example_dict = {"AUTHID":[7541,4878,1014],"visibility":["Yes","No","Yes"], "game_owned": {"game_name": [[CounterStrike, TF2], "None", [CounterStrike,Isaac, Bioshock]], "game_playtime":[[45,14],"None",[1,85,10]}}

What I'm trying to have now is to convert this dictionary into a dataframe, so it would look like this:

ID Visibility CounterStrike TF2 Isaac Bioshock
7541 yes 45 14 None None
4878 no None None None None
1014 yes 1 None 85 10

But the process is so complicated and I'm not really at ease with dict and pandas, so I have virtually no idea on how to proceed. If someone could help me, it would be greatly appreciated, because I'm quite lost.

pandas dataframe dictionary python
1 Answers

We can try flattening the nested dictionary game_owned by making the records from the nested lists present in game_name and game_playtime keys, then prepare a dataframe named games from these records and join it with the dataframe created from the keys AUTHID and visibility

g = dct['game_owned']
games = pd.DataFrame([dict(zip(x, y)) if x != 'None' else {}
                     for x, y in zip(g['game_name'], g['game_playtime'])])
                     
df = pd.DataFrame({k: dct[k] for k in ('AUTHID', 'visibility')}).join(games)

Alternate pure pandas based ugly approach, which i think should be less efficient than the first approach

pd.json_normalize(dct).apply(pd.Series.explode)\
  .set_index(['AUTHID', 'visibility']).apply(pd.Series.explode)\
  .set_index('game_owned.game_name', append=True)['game_owned.game_playtime']\
            .unstack().drop('None', 1).reset_index().rename_axis(columns=None)

>>> df

   AUTHID visibility  CounterStrike   TF2  Isaac  Bioshock
0    7541        Yes           45.0  14.0    NaN       NaN
1    4878         No            NaN   NaN    NaN       NaN
2    1014        Yes            1.0   NaN   85.0      10.0
user_128331660
Related