如何高效扁平化JSON数组？解决Pandas代码语法报错问题

需求说明 #

需要处理一份嵌套JSON文件，扁平化其中的数组数据，并提取以下字段：

• Tracking number
• Payment type (Prepaid/COD)
• Pickup Date Time
• Delivery Date Time
• All Out for Delivery (date/times)
• Shipment weight
• Pickup Pincode, City, State
• Drop Pincode, City, State

错误代码及报错 #

尝试的代码：

df = pd.DataFrame('json file path')

bn = pd.DataFrame(df.trackDetails.values.tolist())['trackingNumber'],['ancillaryDetails'['reasonDescription']],['datesOrTimes'['type':'ACTUAL_PICKUP', 'ACTUAL_DELIVERY', 'SHIP']],['shipmentWeight'],['statusDetail'['code':'DL'['locations']["city"],['stateOrProvinceCode']]]['shipperAddress'['city'],['stateOrProvinceCode']]]  

运行时出现语法警告：

SyntaxWarning: str indices must be integers or slices, not str; perhaps you missed a comma?                           

问题分析 #

这段代码存在多处核心错误：

1. 文件读取错误：pd.DataFrame('json file path')无法直接读取文件，需用pd.read_json()或pd.json_normalize()处理嵌套结构的JSON。
2. 嵌套访问语法错误：比如['ancillaryDetails'['reasonDescription']]是把字符串当字典调用，正确的嵌套访问需要先定位到字典对象再取键值。
3. 数组筛选逻辑错误：无法直接通过['datesOrTimes'['type':'ACTUAL_PICKUP']]筛选数组中指定类型的元素，需要结合循环或Pandas的apply方法处理。

正确实现代码 #

假设JSON符合常见物流数据的嵌套结构，以下是可运行的实现步骤：

1. 导入依赖并读取JSON文件

import pandas as pd
import json

# 读取本地JSON文件
with open('your_json_file.json', 'r') as f:
    raw_data = json.load(f)

# 用json_normalize扁平化嵌套结构，定位核心数组（trackDetails）
# 如果JSON顶层是数组，直接用pd.json_normalize(raw_data)即可
df = pd.json_normalize(raw_data, record_path=['trackDetails'])

2. 提取目标字段

# 1. Tracking number
df['Tracking number'] = df['trackingNumber']

# 2. Payment type（需根据JSON实际字段调整，示例假设字段为paymentType）
df['Payment type (Prepaid/COD)'] = df['paymentType'].map({'PREPAID': 'Prepaid', 'COD': 'COD'}).fillna('Unknown')

# 3. 从datesOrTimes数组提取指定类型的日期
def get_target_datetime(dates_list, target_type):
    for item in dates_list:
        if item.get('type') == target_type:
            return item.get('dateTime')
    return None

df['Pickup Date Time'] = df['datesOrTimes'].apply(lambda x: get_target_datetime(x, 'ACTUAL_PICKUP'))
df['Delivery Date Time'] = df['datesOrTimes'].apply(lambda x: get_target_datetime(x, 'ACTUAL_DELIVERY'))
df['All Out for Delivery (date/times)'] = df['datesOrTimes'].apply(lambda x: get_target_datetime(x, 'SHIP'))

# 4. Shipment weight（示例假设字段为shipmentWeight.value）
df['Shipment weight'] = df['shipmentWeight.value']

# 5. 提取Pickup（发货方）地址信息
df['Pickup Pincode'] = df['shipperAddress.postalCode']
df['Pickup City'] = df['shipperAddress.city']
df['Pickup State'] = df['shipperAddress.stateOrProvinceCode']

# 6. 提取Drop（收货方）地址信息
df['Drop Pincode'] = df['consigneeAddress.postalCode']
df['Drop City'] = df['consigneeAddress.city']
df['Drop State'] = df['consigneeAddress.stateOrProvinceCode']

# 保留需要的字段，生成最终结果
final_df = df[[
    'Tracking number', 'Payment type (Prepaid/COD)', 'Pickup Date Time',
    'Delivery Date Time', 'All Out for Delivery (date/times)', 'Shipment weight',
    'Pickup Pincode', 'Pickup City', 'Pickup State',
    'Drop Pincode', 'Drop City', 'Drop State'
]]

print(final_df.head())

关键提示 #

• 实际字段名需根据JSON真实结构调整，可以先通过print(df.columns)查看扁平化后的所有列名。
• 如果JSON顶层是单个对象而非数组，需要在pd.json_normalize中指定record_path定位到嵌套的数组节点（比如trackDetails）。
• 对于数组类型的列，用apply结合自定义函数筛选元素是最直接的处理方式。

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