如何将Pandas DataFrame列值作为JSON键生成目标格式JSON

你的核心问题是分组时未将period纳入聚合逻辑，导致无法用其值作为JSON的键。以下是修正后的实现代码：

import pandas as pd
import json

# 原始DataFrame
ogdf = pd.DataFrame({'country': ["USA","USA","USA","USA","USA","USA","USA","USA","USA","USA","USA","USA"],
                   'scenario': ["actual", "target","actual", "target","actual", "target","actual", "target","actual", "target","actual", "target"],
                   'currency': ["USD","USD","USD","USD","USD","USD","USD","USD","USD","USD","USD","USD"],
                   'period': ['1998 1Q', '2000', '2001-12','1998 1Q', '2000', '2001-12','1998 1Q', '2000', '2001-12','1998 1Q', '2000', '2001-12'],
                   'account': ['SALES 1','SALES 2','SALES 3','SALES 4','SALES 5','SALES 6','SALES 7','SALES 8','SALES 9','SALES 10','SALES 11','SALES 12'],
                   'sale': [55, 40, 84, 31,55, 40, 84, 31,55, 40, 84, 31]})

# 1. 按多层维度分组，生成每个period对应的account-sale数据列表
grouped = ogdf.groupby(['country', 'scenario', 'currency', 'period']).apply(
    lambda x: x[['account', 'sale']].to_dict(orient='records')
).unstack(level='period')  # 将period从索引转为列名，作为后续的键

# 2. 构造目标格式的字典列表
result = grouped.reset_index().apply(
    lambda row: {
        'country': row['country'],
        'scenario': row['scenario'],
        'currency': row['currency'],
        **{period: row[period] for period in grouped.columns if pd.notna(row[period])}
    },
    axis=1
).tolist()

# 输出JSON格式结果
print(json.dumps(result, indent=2))

代码说明 #

• 分组逻辑调整：加入period作为分组键，确保每个时段的数据被单独聚合，而不是混在一起。
• unstack操作：将period从分组索引转为列名，让我们可以直接用period的实际值作为字典键。
• 字典构造：用**解包操作，将各个period对应的account-sale列表合并到外层字典中，同时保留country、scenario、currency字段。

输出示例 #

[
  {
    "country": "USA",
    "scenario": "actual",
    "currency": "USD",
    "1998 1Q": [
      {
        "account": "SALES 1",
        "sale": 55
      },
      {
        "account": "SALES 7",
        "sale": 84
      },
      {
        "account": "SALES 10",
        "sale": 55
      }
    ],
    "2000": [
      {
        "account": "SALES 5",
        "sale": 55
      },
      {
        "account": "SALES 8",
        "sale": 31
      },
      {
        "account": "SALES 11",
        "sale": 40
      }
    ],
    "2001-12": [
      {
        "account": "SALES 3",
        "sale": 84
      },
      {
        "account": "SALES 6",
        "sale": 40
      },
      {
        "account": "SALES 9",
        "sale": 55
      }
    ]
  },
  {
    "country": "USA",
    "scenario": "target",
    "currency": "USD",
    "1998 1Q": [
      {
        "account": "SALES 4",
        "sale": 31
      },
      {
        "account": "SALES 10",
        "sale": 55
      }
    ],
    "2000": [
      {
        "account": "SALES 2",
        "sale": 40
      },
      {
        "account": "SALES 8",
        "sale": 31
      },
      {
        "account": "SALES 11",
        "sale": 40
      }
    ],
    "2001-12": [
      {
        "account": "SALES 6",
        "sale": 40
      },
      {
        "account": "SALES 9",
        "sale": 55
      },
      {
        "account": "SALES 12",
        "sale": 31
      }
    ]
  }
]

内容的提问来源于stack exchange，提问作者berking11