如何将含多层嵌套JSON列的CSV解析为指定结构的DataFrame？

问题 #

我有一个包含data列的CSV文件，该列存储着多层嵌套的JSON数据。需求是将configuration中的name作为新列名，提取detectors中对应id的readings里的count值作为行数据。参考过相关方案，但无法针对这种深层嵌套结构提取对应id和值。

原始数据 #

system;datetime;data;
ACD;19.10.2021 11:24:00,000;{"id":"device1","type":"dataValues","payload":{"deviceId":"device1","start":"2021-10-19 11:23:00.000","end":"2021-10-19 11:24:00.000","configuration":[{"name":"DD51","id":1},{"name":"VV22","id":2}],"detectors":[{"readings":[{"reading":{"count":8}}],"id":1},{"readings":[{"reading":{"count":2}}],"id":2}]}};
ACD;19.10.2021 11:25:00,000;{"id":"device1","type":"dataValues","payload":{"deviceId":"device1","start":"2021-10-19 11:24:00.000","end":"2021-10-19 11:25:00.000","configuration":[{"name":"DD51","id":1},{"name":"VV22","id":2}],"detectors":[{"readings":[{"reading":{"count":7}}],"id":1},{"readings":[{"reading":{"count":4}}],"id":2}]}};

期望结果 #

system;datetime;DD51;VV22;
ACD;19.10.2021 11:24:00,000;8;2;
ACD;19.10.2021 11:25:00,000;7;4;

解决方案 #

方法一：Python（Pandas） #

通过Pandas结合JSON解析实现，步骤清晰易扩展：

• 读取CSV文件并指定分隔符为;
• 解析data列的嵌套JSON
• 建立configuration中id到name的映射
• 提取detectors对应id的count值，匹配映射列名
• 合并原始字段与新生成字段后输出

代码示例：

import pandas as pd
import json

# 读取CSV，忽略最后一个空列
df = pd.read_csv('input.csv', sep=';', usecols=['system', 'datetime', 'data'])

def parse_row(row):
    data = json.loads(row['data'])
    payload = data['payload']
    
    # 构建id到列名的映射字典
    id_name_map = {cfg['id']: cfg['name'] for cfg in payload['configuration']}
    
    # 提取每个检测器的count值
    count_data = {}
    for det in payload['detectors']:
        count = det['readings'][0]['reading']['count']
        count_data[id_name_map[det['id']]] = count
    
    # 返回整合后的行数据
    return pd.Series({
        'system': row['system'],
        'datetime': row['datetime'],
        **count_data
    })

# 处理所有行并输出结果
result_df = df.apply(parse_row, axis=1)
result_df.to_csv('output.csv', sep=';', index=False)

方法二：jq命令行工具 #

适合无编程基础的命令行场景，直接解析并输出：

• 提取CSV每行的system、datetime和data字段
• 用jq解析嵌套JSON，建立id-name映射并提取对应count
• 格式化输出为目标CSV格式

通用版命令（适配动态变化的配置名称）：

# 先写入表头
echo "system;datetime;DD51;VV22;" > output.csv

# 处理数据行并追加到结果文件
cat input.csv | tail -n +2 | jq -R '
    split(";") as $cols
    | $cols[2] | fromjson as $data
    | # 构建id到name的映射
      $data.payload.configuration | reduce .[] as $item ({}; .[$item.id] = $item.name) as $id_map
    | # 提取每个检测器的count值并匹配列名
      $data.payload.detectors | reduce .[] as $det ({}; .[$id_map[$det.id]] = $det.readings[0].reading.count)
    | # 合并原始字段与count数据
      {system: $cols[0], datetime: $cols[1]} + .
' | jq -r '@csv' | sed 's/,/;/g' >> output.csv

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