Python JSON转CSV函数无法正确保存数据，如何修复？

运行Python将特定JSON转CSV的代码时，仅输出列名，且将单词"data"拆分为单个字母输出，示例如下：

id,uuid,position,created_at,created_meta,updated_at,updated_meta,meta,indicator,indicator_description,geo_type,geo_name,geo_id,variable,time_period,time_period_label,value,footnote
d,a,t,a

原代码如下：

import json
import csv

# Open the JSON file and load the data
with open('main_dataset copy.json') as f:
    data = json.load(f)

# Create a CSV file and write the headers
with open('suicide_deaths.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow(
        ['id', 'uuid', 'position', 'created_at', 'created_meta', 'updated_at', 'updated_meta', 'meta', 'indicator',
         'indicator_description', 'geo_type', 'geo_name', 'geo_id', 'variable', 'time_period', 'time_period_label',
         'value', 'footnote'])

    # Write each row of data to the CSV file
    for row in data:
        writer.writerow(row)

JSON文件结构（简化）：

{
  "data" : [ 
    [ "row-sj5k-g243-v6cb", "00000000-0000-0000-ED82-9AA08DFD0A9A", 0, 1623083971, null, 1623083971, null, "{ }", "Death rates for suicide", "Deaths per 100,000 resident population, age-adjusted", "1", "Total", "0", "All persons", "0", "1950", "1", "All ages", "0", "13.2", null ],
    [ "row-ejmi~wx6s-9cxn", "00000000-0000-0000-6815-4FD0CEFCA3F6", 0, 1623083971, null, 1623083971, null, "{ }", "Death rates for suicide", "Deaths per 100,000 resident population, age-adjusted", "1", "Total", "0", "All persons", "0", "1960", "2", "All ages", "0", "12.5", null ]
  ]
}

问题原因 #

json.load(f)加载的是整个JSON对象（Python字典），代码中for row in data遍历的是字典的键（即字符串"data"），字符串在循环中会被拆分为单个字符，因此输出了d,a,t,a这一行。

修正代码 #

需要遍历JSON对象中data键对应的二维数组，同时注意表头字段数与每行元素数的匹配（原表头18个字段，JSON每行有20个元素，这里示例截取前18个元素，可根据实际需求调整）：

import json
import csv

# 加载JSON数据，获取实际的行集合
with open('main_dataset copy.json') as f:
    json_data = json.load(f)
    rows = json_data["data"]  # 取"data"键对应的数组

# 写入CSV文件
with open('suicide_deaths.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    # 写入表头
    headers = ['id', 'uuid', 'position', 'created_at', 'created_meta', 'updated_at', 'updated_meta', 'meta', 
               'indicator', 'indicator_description', 'geo_type', 'geo_name', 'geo_id', 'variable', 
               'time_period', 'time_period_label', 'value', 'footnote']
    writer.writerow(headers)
    
    # 遍历每行数据，匹配表头字段数写入
    for row in rows:
        # 截取前18个元素和表头对应，若需要保留所有字段请调整表头
        writer.writerow(row[:len(headers)])

关键说明 #

1. 必须通过json_data["data"]获取存储实际数据的数组，而非直接遍历整个JSON对象
2. 若JSON每行元素数与表头字段数不一致，需调整表头或对行数据做截取/补全处理，避免CSV列错位

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