如何将带时间戳的Excel数据正确导入Pandas DataFrame

原始数据情况 #

有一份存储在Excel中的水文数据，日期格式为"m/d/Y"，原始数据如下：

data_id date            flowrate 
34606   4/30/2021 23:45 -1904.39
34607   4/30/2021 23:50 -1864.59
34608   4/30/2021 23:55 -1772.78
34609   1/5/2021 0:00   -1679.54
34610   1/5/2021 0:05   -1646.46
34611   1/5/2021 0:10   -1527.03

导入Pandas后的异常 #

使用pd.read_excel()导入DataFrame后，日期格式出现异常，且索引发生偏移：

import pandas as pd
DATA = pd.read_excel("DATA.xlsx")
DATA
                      date  flowrate_m3s
34557      4/30/2021 23:45      -1904.39
34558      4/30/2021 23:50      -1864.59
34559      4/30/2021 23:55      -1772.78
34560  2021-01-05 00:00:00      -1679.54
34561  2021-01-05 00:05:00      -1646.46
34562  2021-01-05 00:10:00      -1527.03

可见pd.read_excel()默认按"d/m/Y"解析日期，仅当月份数值大于12时才会例外。

尝试过的无效方法 #

• 读取Excel时指定日期格式：

FORMAT = "%m/%d/%Y %H:%M:%S"
DATA = pd.read_excel("DATA.xlsx",parse_dates=[2], date_format=FORMAT)

结果与之前完全一致。

• 手动将DATA.date转换为datetime类型，得到的日期依然错误。
• 修改Excel中的日期格式，无效果。

问题根源排查 #

最终发现问题出在Excel数据集本身：每月前12天的日期格式为m/d/Y，13号起自动切换为d/m/Y，格式混乱导致解析错误。

最终解决方案 #

改用原始CSV文件，通过以下代码解决日期解析问题：

DATA = pd.read_csv("DATA.csv")
FORMAT = "%m/%d/%Y %H:%M" # 对应格式：01/01/2021 00:00
DATA.insert(1, "date_num", pd.to_datetime(DATA.date, format = FORMAT), True)

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