如何将DataFrame转JSON时保留列表类型而非转为字符串？

我尝试将Pandas DataFrame保存为JSON文件，示例数据如下：

import pandas as pd
import json

df
    Metric          Value
0   Line1           10% off
1   Line2           15% off
2   Line3           20% off
3   Line4           25% off
4   Line5           30% off
5   revenueXaxis    ['Week 1', 'Week 2', 'Week 3', 'Week 4', 'Week 5', 'Week 6', 'Week 7', 'Week 8']
6   Revenuedata1    [30, 30, 30, 30, 30, 30, 30, 30]
7   Revenuedata2    [25, 25, 25, 20, 25, 25, 25, 25]
8   Revenuedata3    [15, 15, 15, 15, 15, 15, 15, 15]
9   Revenuedata4    [15, 10, 10, 10, 10, 10, 10, 10]
10  Revenuedata5    [10, 10, 10, 10, 10, 10, 10, 10]

执行dict(zip(df.iloc[:,0], df.iloc[:,1]))转字典时，列表值被强制转为字符串：

dict(zip(df.iloc[:,0], df.iloc[:,1]))

{'Line1': '10% off',
 'Line2': '15% off',
 'Line3': '20% off',
 'Line4': '25% off',
 'Line5': '30% off',
 'revenueXaxis': "['Week 1', 'Week 2', 'Week 3', 'Week 4', 'Week 5', 'Week 6', 'Week 7', 'Week 8']",
 'Revenuedata1': '[30, 30, 30, 30, 30, 30, 30, 30]',
 'Revenuedata2': '[25, 25, 25, 20, 25, 25, 25, 25]',
 'Revenuedata3': '[15, 15, 15, 15, 15, 15, 15, 15]',
 'Revenuedata4': '[15, 10, 10, 10, 10, 10, 10, 10]',
 'Revenuedata5': '[10, 10, 10, 10, 10, 10, 10, 10]'}

用json.dump写入文件后，输出的列表仍为字符串格式：

"ExpectedRevenue": {
        "Line1": "10% off",
        "Line2": "15% off",
        "Line3": "20% off",
        "Line4": "25% off",
        "Line5": "30% off",
        "revenueXaxis": "['Week 1', 'Week 2', 'Week 3', 'Week 4', 'Week 5', 'Week 6', 'Week 7', 'Week 8']",
        "Revenuedata1": "[50, 110, 180, 260, 350, 450, 550, 650]",
        "Revenuedata2": "[20, 45, 75, 110, 150, 195, 245, 300]",
        "Revenuedata3": "[5, 15, 28, 43, 60, 78, 98, 120]",
        "Revenuedata4": "[4, 10, 17, 2, 35, 46, 58, 72]",
        "Revenuedata5": "[3, 8, 13.5, 19.5, 26.5, 34.5, 44, 54]"
    },

需要保留整数/字符串列表的原生类型，期望输出：

"ExpectedRevenue": [{
    "Line1": "10% off",
    "Line2": "15% off",
    "Line3": "20% off",
    "Line4": "25% off",
    "Line5": "30% off",
    "revenueXaxis": ["Week 1", "Week 2", "Week 3", "Week 4", "Week 5", "Week 6", "Week 7", "Week 8"],
    "Revenuedata1": [50, 110, 180, 260, 350, 450, 550, 650],
    "Revenuedata2": [20, 45, 75, 110, 150, 195, 245, 300],
    "Revenuedata3": [5, 15, 28, 43, 60, 78, 98, 120],
    "Revenuedata4": [4, 10, 17, 2, 35, 46, 58, 72],
    "Revenuedata5": [3, 8, 13.5, 19.5, 26.5, 34.5, 44, 54]
}]

方法1：解析字符串格式的列表为原生类型 #

问题核心是DataFrame中看似列表的值实际存储为字符串，需要先将其解析为真实的列表/原生类型，推荐用ast.literal_eval处理：

import pandas as pd
import json
import ast

# 定义解析函数：尝试把字符串转成原生类型，失败则返回原内容
def parse_value(s):
    try:
        return ast.literal_eval(s)
    except (ValueError, SyntaxError):
        return s

# 处理Value列，转换字符串格式的列表
df['Value'] = df['Value'].apply(parse_value)

# 生成目标字典
result_dict = dict(zip(df['Metric'], df['Value']))

# 构造期望的JSON结构
output = {"ExpectedRevenue": [result_dict]}

# 写入JSON文件（indent参数用于格式化输出）
with open('output.json', 'w') as f:
    json.dump(output, f, indent=4)

方法2：用Pandas转置+to_dict直接生成目标结构 #

如果DataFrame中的列表本身就是原生list类型（而非字符串），可以直接转置DataFrame再生成字典：

import pandas as pd
import json

# 将Metric设为索引，转置后让Metric成为列名
df_transposed = df.set_index('Metric').T

# 用orient='records'生成列表格式的字典，直接匹配期望结构
result = {"ExpectedRevenue": df_transposed.to_dict('records')}

# 写入文件
with open('output.json', 'w') as f:
    json.dump(result, f, indent=4)

注意事项 #

• 如果DataFrame中的“列表”是字符串格式（比如读取数据时自动转换），必须先用方法1解析，否则方法2无法识别；
• json.dump的indent参数用于美化输出格式，可根据需求调整。

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