将包含列表和dict的dict转换为df

Question

我有点问题。我有一张像这样的纸：

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

我在Steam网站上获取数据，使用ID作为API中的键来获取数据。基本上，有了一些内容，dict应该是这样的：

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]}}

我现在要做的是将这个字典转换成一个数据帧，它看起来像这样：

^{tb1}$

但是这个过程是如此复杂，我对dict和pandas也不太放心，所以我几乎不知道如何进行。如果有人能帮助我，我将不胜感激，因为我迷路了

Answer 1

我们可以尝试通过从game_name和game_playtime键中存在的嵌套列表中生成记录来展平嵌套字典game_owned，然后从这些记录中准备一个名为games的数据帧，并使用从键AUTHID和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)

替代方法基于纯熊猫的丑陋方法，我认为应该比第一种方法效率更低

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