pandas无法将DataFrame的Unix时间戳索引转为datetime索引

问题背景 #

开发过程中无法将DataFrame中存储为Unix epoch time的索引转换为datetime类型索引，尝试多种实现方式均未生效。

原始代码与索引打印结果 #

# ...
for item in mongodb.find({"time": {"$gt": "2022-06-15 12:49:00"}}):
    if item["stock_price_onehour"] != "NaN":
        data = literal_eval(item["stock_price_onehour"])
        df = pd.DataFrame.from_dict(data)
        print(df.index)

>>> Index(['1655286600000', '1655286660000', '1655286720000', '1655286780000',
       '1655286840000', '1655286900000', '1655286960000', '1655287020000',
       '1655287080000', '1655287140000', '1655287200000', '1655287260000',
       '1655287320000', '1655287380000', '1655287440000', '1655287500000',
       '1655287560000', '1655287620000', '1655287680000', '1655287800000',
       '1655287860000', '1655287920000', '1655287980000', '1655288040000',
       '1655288100000', '1655288160000', '1655288220000', '1655288280000',
       '1655288340000', '1655288400000', '1655288460000', '1655288520000',
       '1655288580000', '1655288640000', '1655288700000', '1655288760000',
       '1655288820000', '1655288880000', '1655288940000', '1655289000000',
       '1655289060000', '1655289120000', '1655289209000'],
      dtype='object')

已尝试方案及报错 #

df.index = datetime.fromtimestamp(df.index).strftime("%Y-%m-%d %H:%M:%S")
>>> TypeError: an integer is required (got type Index)

df.index = pd.DatetimeIndex(df.index)
>>> TypeError: invalid string coercion to datetime
>>> During handling of the above exception, another exception occurred: OverflowError: signed integer is greater than maximum

df.index = pd.to_datetime(df.index)
>>> TypeError: invalid string coercion to datetime
>>> During handling of the above exception, another exception occurred: OverflowError: signed integer is greater than maximum

df.index = pd.to_datetime(df.index.astype(str), errors="coerce")
>>> # prints NaT instead of datetime as index

报错原因 #

索引存在两个核心特征导致之前的方案全部失效：

• 索引值是字符串格式，不是整数类型
• 索引值是毫秒级Unix时间戳，不是pandas默认解析的纳秒级、也不是标准库datetime默认支持的秒级

各方案失效的具体原因：

• datetime.fromtimestamp()仅支持传入单个整数时间戳，无法直接处理整个Index对象，且默认接收秒级时间戳
• 直接调用pd.DatetimeIndex/pd.to_datetime()不指定单位时，会先尝试把字符串按常规日期格式解析，失败后转整数时默认按纳秒单位处理，毫秒级数值远超出纳秒时间戳的合法范围，触发溢出报错
• 强转字符串后加errors="coerce"时，字符串不符合常规日期格式，全部解析失败返回NaT

修复方案 #

先将字符串类型的索引转为64位整数，再指定时间戳单位为毫秒做转换即可：

import pandas as pd

# 核心转换逻辑，得到DatetimeIndex类型索引，支持所有pandas时间序列操作
df.index = pd.to_datetime(df.index.astype("int64"), unit="ms")

如果业务需要字符串格式的索引而非datetime类型，可以在转换完成后再做格式化：

# 非必要不转字符串，datetime索引更方便做时间筛选、重采样等计算
df.index = df.index.strftime("%Y-%m-%d %H:%M:%S")

转换后正常的datetime索引打印结果参考：

DatetimeIndex(['2022-06-15 04:30:00', '2022-06-15 04:31:00',
               '2022-06-15 04:32:00', '2022-06-15 04:33:00',
               '2022-06-15 04:34:00', '2022-06-15 04:35:00',
               '2022-06-15 04:36:00', '2022-06-15 04:37:00',
               '2022-06-15 04:38:00', '2022-06-15 04:39:00',
               '2022-06-15 04:40:00', '2022-06-15 04:41:00',
               '2022-06-15 04:42:00', '2022-06-15 04:43:00',
               '2022-06-15 04:44:00', '2022-06-15 04:45:00',
               '2022-06-15 04:46:00', '2022-06-15 04:47:00',
               '2022-06-15 04:48:00', '2022-06-15 04:50:00',
               '2022-06-15 04:51:00', '2022-06-15 04:52:00',
               '2022-06-15 04:53:00', '2022-06-15 04:54:00',
               '2022-06-15 04:55:00', '2022-06-15 04:56:00',
               '2022-06-15 04:57:00', '2022-06-15 04:58:00',
               '2022-06-15 04:59:00', '2022-06-15 05:00:00',
               '2022-06-15 05:01:00', '2022-06-15 05:02:00',
               '2022-06-15 05:03:00', '2022-06-15 05:04:00',
               '2022-06-15 05:05:00', '2022-06-15 05:06:00',
               '2022-06-15 05:07:00', '2022-06-15 05:08:00',
               '2022-06-15 05:09:00', '2022-06-15 05:10:00',
               '2022-06-15 05:11:00', '2022-06-15 05:12:00',
               '2022-06-15 05:13:29'],
              dtype='datetime64[ns]', freq=None)

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