使用Python Pandas从嵌套JSON提取BillingCode和Rate到CSV的报错解决

需求 #

从嵌套结构的JSON数据中提取BillingCode和Rate字段，使用Pandas保存为CSV文件。

原始JSON数据 #

{
"TransactionId": "1c9cc4b9-a0e1-4228-b382-b244a7674593",
"Number": "1022",
"Version": 534,
"StartDateTime": "2023-04-01 14:12:50.999",
"EndDateTime": "2023-04-01 14:15:32.038",
"LastUpdatedOn": "2023-04-01",
"RateSchedules": [{
        "BillingCodeType": "CPT",
        "BillingCodeTypeVersion": "2023",
        "BillingCode": "0107T",
        "Rates": [{
                "Rate": 14.11,
                "PlaceOfServices": ["01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "31", "32", "33", "34", "41", "42", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "60", "61", "62", "65", "71", "72", "81", "99"]
            }
        ]
    }, {
        "BillingCodeType": "CPT",
        "BillingCodeTypeVersion": "2023",
        "BillingCode": "0108T",
        "Rates": [{
                "Rate": 14.11,
                "PlaceOfServices": ["01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "31", "32", "33", "34", "41", "42", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "60", "61", "62", "65", "71", "72", "81", "99"]
            }
        ]
    }]
}

原始Python脚本 #

import pandas as pd
df = pd.read_json(r"C:\Users\Jay.Ramachandra\Documents\20230401-141250_1022_RI_MC01.json")
df = df.loc[["BillingCode", ["Rate"], "Rates"],"RateSchedules"].T
df.to_csv(r"C:\Users\Jay.Ramachandra\Documents\MrfJson.csv")

遇到的错误 #

1. Unicode转义错误

runfile('C:/Users/.spyder-py3/temp.py', wdir='C:/Users/.spyder-py3')
  File <unknown>:9
    df = pd.read_json("C:\Users\MC01.json")
                                                                                            ^
SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape

2. TypeError错误（添加r前缀修正路径后）

TypeError: unhashable type: 'list'

错误分析与修正方案 #

1. Unicode转义错误的原因与解决 #

Windows系统路径中的\会被Python识别为转义字符，导致无法正确解析路径。添加r前缀将路径转为原始字符串，此修正方法是正确的，无需再调整。

2. TypeError的原因 #

df.loc[["BillingCode", ["Rate"], "Rates"],"RateSchedules"].T存在两个问题：

• loc的行索引参数不能嵌套列表（["Rate"]是嵌套在大列表中的子列表），列表是不可哈希类型，无法作为索引使用。
• 直接用pd.read_json读取嵌套JSON时，RateSchedules会被解析为DataFrame的一列，其中每个元素是嵌套字典数组，无法通过loc直接提取内层的Rate字段。

正确的处理方法 #

需要先展开JSON的嵌套结构，提取目标字段，以下提供两种可行方案：

方案一：使用pd.json_normalize（推荐）

json_normalize是Pandas专门用于处理嵌套JSON的工具，可直接展开多层嵌套结构：

import pandas as pd
import json

# 读取JSON文件
with open(r"C:\Users\Jay.Ramachandra\Documents\20230401-141250_1022_RI_MC01.json", 'r') as f:
    data = json.load(f)

# 展开RateSchedules和Rates的嵌套结构，保留BillingCode和Rate字段
df = pd.json_normalize(
    data['RateSchedules'],
    record_path='Rates',  # 指定要展开的内层数组
    meta=['BillingCode']  # 指定需要保留的外层字段
)

# 筛选目标字段并保存为CSV
result_df = df[['BillingCode', 'Rate']]
result_df.to_csv(r"C:\Users\Jay.Ramachandra\Documents\MrfJson.csv", index=False)

方案二：手动遍历嵌套结构

通过循环遍历JSON的嵌套层级，收集目标字段：

import pandas as pd
import json

# 读取JSON文件
with open(r"C:\Users\Jay.Ramachandra\Documents\20230401-141250_1022_RI_MC01.json", 'r') as f:
    data = json.load(f)

# 初始化空列表存储数据
rows = []
for schedule in data['RateSchedules']:
    billing_code = schedule['BillingCode']
    # 遍历每个RateSchedules下的Rates数组
    for rate_item in schedule['Rates']:
        rows.append({
            'BillingCode': billing_code,
            'Rate': rate_item['Rate']
        })

# 转换为DataFrame并保存
df = pd.DataFrame(rows)
df.to_csv(r"C:\Users\Jay.Ramachandra\Documents\MrfJson.csv", index=False)

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