如何在Python中将嵌套JSON的最低级别提取到数据帧中？

Question

我希望在Python中只提取JSON的最低级别对象。例如，API的前几条记录如下所示：

{
  "season": 2021,
  "charts": {
    "ARI": {
      "TE": [
        {
          "team": "ARI",
          "position": "TE",
          "depth": "1",
          "playerId": "1443",
          "name": "Dan Arnold"
        },
        {
          "team": "ARI",
          "position": "TE",
          "depth": "2",
          "playerId": "599",
          "name": "Maxx Williams"
        }
      ],
      "K": [
        {
          "team": "ARI",
          "position": "K",
          "depth": "1",
          "playerId": "1121",
          "name": "Zane Gonzalez"
        },
        {

我最终希望将所有这些结果放入具有以下结构的数据帧中：

| team | position | depth | playerId | name |
|:---- |:-------- |:----- |:-------- |:---- |

我尝试了以下代码的变体，但没有成功：

import requests as rq
import pandas as pd

# Retrieve Depth Charts
json_depthCharts = rq.get(f"https://api.fantasynerds.com/v1/nfl/depth?apikey={API_KEY}").json()

df_depthCharts = pd.json_normalize(json_depthCharts, 'charts', ['charts', 'team'])

print(df_depthCharts)

任何见解都将不胜感激

Answer 1

试试json_normalize()+melt()+explode()+Dataframe()：

df=pd.DataFrame(pd.json_normalize(json_depthCharts).melt('season')['value'].explode().tolist())

或

另一种方式是通过stack()+drop()组合代替melt()和rest，所有方法保持不变：

df=pd.DataFrame(pd.json_normalize(json_depthCharts).drop(columns='season').stack().explode().tolist())

df的输出：

        team    position    depth   playerId    name
0       ARI     TE          1       1443        Dan Arnold
1       ARI     TE          2       599         Maxx Williams
2       ARI     K           1       1121        Zane Gonzalez
3       ARI     K           2       1454        Brett Maher
4       ARI     LWR         1       338         DeAndre Hopkins
...     ...     ...        ...      ...         ...
932     WAS     LDE         2       179         Ryan Kerrigan
933     WAS     RCB         1       647         Ronald Darby
934     WAS     RB          1       1957        Antonio Gibson
935     WAS     RB          2       1542        Bryce Love
936     WAS     NB          1       1733        Jimmy Moreland

937 rows × 5 columns