使用Python pandas将嵌套JSON转换为指定分层格式CSV

目标是将给定的嵌套JSON财务数据按照指定规则转换为CSV数据框，为整体项目的组成环节。

待处理数据（response.json） #

[
    {
      "fiscalPeriodYearMonth": "2012-09",
      "revenuePer": {
        "yearOverYear": 19.57,
        "threeYearAvg": 28.24,
        "fiveYearAvg": 21.240000000000002,
        "tenYearAvg": 28.96
      },
      "operatingIncome": {
        "yearOverYear": 21.57,
        "threeYearAvg": 50.019999999999996,
        "fiveYearAvg": 30.3,
        "tenYearAvg": null
      },
      "netIncomePer": {
        "yearOverYear": 14.000000000000002,
        "threeYearAvg": 44.330000000000005,
        "fiveYearAvg": 29.01,
        "tenYearAvg": null
      },
      "epsPer": {
        "yearOverYear": 16.55,
        "threeYearAvg": 44.65,
        "fiveYearAvg": 30.830000000000002,
        "tenYearAvg": null
      }
    },
    {
      "fiscalPeriodYearMonth": "2013-09",
      "revenuePer": {
        "yearOverYear": 7.5600000000000005,
        "threeYearAvg": 18.87,
        "fiveYearAvg": 17.9,
        "tenYearAvg": 29.020000000000003
      },
      "operatingIncome": {
        "yearOverYear": 1.06,
        "threeYearAvg": 23.27,
        "fiveYearAvg": 34.11,
        "tenYearAvg": 58.93000000000001
      },
      "netIncomePer": {
        "yearOverYear": 0.77,
        "threeYearAvg": 22.42,
        "fiveYearAvg": 30.12,
        "tenYearAvg": 52.459999999999994
      },
      "epsPer": {
        "yearOverYear": 1.4500000000000002,
        "threeYearAvg": 23.46,
        "fiveYearAvg": 31.5,
        "tenYearAvg": 47.88
      }
    }
]

现有问题代码 #

import pandas as pd
df = pd.read_json(r'PATH TO JSON FILE', orient ='values')
print(df.T.to_csv("final_output.csv"))

上述代码未对嵌套JSON结构做扁平化、行结构重构、格式映射处理，运行后无法得到符合要求的输出。

期望输出规则 #

输出CSV以财期年月（如2012-09、2013-09）为列名，行按财务指标分层排列，具体规则：

• 首行为指标大类名称行：revenuePer对应显示为Revenue，operatingIncome、netIncomePer、epsPer直接使用原字段名，大类行对应列值留空
• 每个大类下依次排列4个指标行：Year Over Year、3-Year Average、5-Year Average、10-Year Average，填入对应财期数值，保留两位小数，空值显示为-
• 不同大类之间保留空行分隔

直接读取嵌套JSON后按规则手动构建行结构即可，可直接运行的代码如下：

import pandas as pd
import json

# 替换为你的response.json实际存储路径
with open("response.json", "r", encoding="utf-8") as f:
    raw_data = json.load(f)

# 配置映射规则
category_mapping = {
    "revenuePer": "Revenue",
    "operatingIncome": "operatingIncome",
    "netIncomePer": "netIncomePer",
    "epsPer": "epsPer"
}
sub_metric_order = [
    ("yearOverYear", "Year Over Year"),
    ("threeYearAvg", "3-Year Average"),
    ("fiveYearAvg", "5-Year Average"),
    ("tenYearAvg", "10-Year Average")
]

# 提取所有财期作为列
period_cols = [entry["fiscalPeriodYearMonth"] for entry in raw_data]
output_rows = []

for cat_key, cat_display in category_mapping.items():
    # 写入大类标题行
    output_rows.append([cat_display] + [""] * len(period_cols))
    # 写入该大类下所有子指标行
    for sub_key, sub_display in sub_metric_order:
        current_row = [sub_display]
        for entry in raw_data:
            val = entry[cat_key][sub_key]
            current_row.append("-" if val is None else f"{round(val, 2):.2f}")
        output_rows.append(current_row)
    # 大类之间加空行
    output_rows.append([""] * (len(period_cols) + 1))

# 移除末尾多余空行
output_rows = output_rows[:-1]
# 生成数据框并导出CSV
final_df = pd.DataFrame(output_rows, columns=["Metric"] + period_cols)
final_df.to_csv("final_output.csv", index=False, encoding="utf-8-sig")

运行代码后导出的final_output.csv完全匹配规则要求：数值自动保留两位小数、空值统一显示为-、大类间有空行分隔，列名对应各财期年月。

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