将DataFrame转换为指定格式JSON时遇Timestamp类型错误

我有如下结构的DataFrame：

2023-06-30      2022-06-30      2021-06-30      2020-06-30
Ordinary Shares Number                              7432000000.0    7464000000.0    7519000000.0    7571000000.0
Share Issued                                        7432000000.0    7464000000.0    7519000000.0    7571000000.0
Net Debt                                           12533000000.0   35850000000.0   43922000000.0   49751000000.0
Total Debt                                         59965000000.0   61270000000.0   67775000000.0   70998000000.0
Tangible Book Value                               128971000000.0   87720000000.0   84477000000.0   67915000000.0
...                                                          ...             ...             ...             ...
Cash Cash Equivalents And Short Term Investments  111262000000.0  104757000000.0  130334000000.0  136527000000.0
Other Short Term Investments                       76558000000.0   90826000000.0  116110000000.0  122951000000.0
Cash And Cash Equivalents                          34704000000.0   13931000000.0   14224000000.0   13576000000.0
Cash Equivalents                                   26226000000.0    5673000000.0    6952000000.0             NaN
Cash Financial                                      8478000000.0    8258000000.0    7272000000.0             NaN

[73 rows x 4 columns]

想要转换成如下格式的JSON：

{
    "2023-06-30": {
        "Ordinary Shares Number": "7432000000.0",
        ...
    },
    "2022-06-30": {
        "Ordinary Shares Number": "7464000000.0",
        ...
    },
    "2021-06-30": {
        "Ordinary Shares Number": "7519000000.0",
        ...
    },
    "2020-06-30": {
        "Ordinary Shares Number": "7571000000.0",
        ...
    }
}

但转换时要么格式不符合预期，要么出现Timestamp类型错误。比如执行以下代码：

out = json.dumps({c: dict(zip(balance.index, balance[c])) for c in balance.columns}, indent=4)
print(out)

得到报错：

Traceback (most recent call last):
  File "/usr/lib/python3.8/runpy.py", line 194, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/jesse_b/tools/stonk-db/stonkdb/__main__.py", line 39, in <module>
    main()
  File "/home/jesse_b/tools/stonk-db/stonkdb/__main__.py", line 32, in main
    out = json.dumps({c: dict(zip(balance.index, balance[c])) for c in balance.columns}, indent=4)
  File "/usr/lib/python3.8/json/__init__.py", line 234, in dumps
    return cls(
  File "/usr/lib/python3.8/json/encoder.py", line 201, in encode
    chunks = list(chunks)
  File "/usr/lib/python3.8/json/encoder.py", line 431, in _iterencode
    yield from _iterencode_dict(o, _current_indent_level)
  File "/usr/lib/python3.8/json/encoder.py", line 376, in _iterencode_dict
    raise TypeError(f'keys must be str, int, float, bool or None, '
TypeError: keys must be str, int, float, bool or None, not Timestamp

报错核心原因是DataFrame的列是Timestamp类型，JSON不支持将非基础数据类型作为键，必须先把列名转成字符串格式。以下是两种高效可行的方案：

方案一：利用pandas内置方法快速转换 #

这是最简洁的方式，通过转置DataFrame+指定JSON输出方向直接匹配目标格式：

import json
import pandas as pd

# 转置DataFrame，将原列转为索引，原索引转为列
transposed_df = balance.T
# 将Timestamp类型的索引转为YYYY-MM-DD格式的字符串
transposed_df.index = transposed_df.index.strftime('%Y-%m-%d')
# 用orient='index'参数生成外层为日期键、内层为指标键值对的JSON
out = transposed_df.to_json(orient='index', indent=4)
print(out)

如果需要把数值强制转为字符串类型，可以先对转置后的DataFrame做类型转换：

transposed_df = balance.T.astype(str)
transposed_df.index = transposed_df.index.strftime('%Y-%m-%d')
out = transposed_df.to_json(orient='index', indent=4)

方案二：手动构造目标字典（更灵活） #

如果需要自定义NaN值处理等逻辑，可以手动遍历构造字典：

import json
import pandas as pd

# 将所有列名转为字符串
str_columns = [col.strftime('%Y-%m-%d') if isinstance(col, pd.Timestamp) else str(col) for col in balance.columns]

result = {}
for col_str, col in zip(str_columns, balance.columns):
    item_dict = {}
    for idx, val in zip(balance.index, balance[col]):
        # 自定义处理NaN，比如转为null或空字符串
        item_dict[idx] = str(val) if pd.notna(val) else None
    result[col_str] = item_dict

out = json.dumps(result, indent=4)
print(out)

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