将Excel读取至Python时时间格式异常的原因与解决方法

1. 问题产生的原因 #

• Excel目标列格式不统一：部分单元格是标准时间格式，部分是文本、自定义格式（掺杂非时间字符），或存在空值、错误值，导致pandas无法统一解析
• Excel时间存储特性：Excel将时间以浮点数形式存储（整数部分为日期，小数部分为时间），若单元格内容不是有效浮点数或符合时间格式的文本，pandas读取时会识别为object类型而非datetime类型
• 时间格式与pandas默认解析规则不匹配：比如区域格式为DD/MM/YYYY但pandas默认按MM/DD/YYYY解析，导致日期错位；或时间包含特殊符号、隐藏字符，被识别为纯文本

2. 处理与标准化时间数据的方法 #

步骤1：排查异常情况 #

先查看目标列的数据类型和样本值，明确异常类型：

import pandas as pd

data = pd.read_excel('1.xlsx')
# 查看各列数据类型
print(data.dtypes)
# 查看目标列的前20行样本值
print(data[['in_time', 'call_time', 'process_in_time', 'out_time']].head(20))

步骤2：统一解析为datetime类型 #

使用pd.to_datetime()强制解析，通过errors='coerce'将无法解析的值转为NaT（缺失时间值）：

time_cols = ['in_time', 'call_time', 'process_in_time', 'out_time']
for col in time_cols:
    # 通用解析，自动识别常见格式
    data[col] = pd.to_datetime(data[col], errors='coerce')

若已知固定时间格式，指定format参数可提高解析效率和准确性，比如：

• 格式为YYYY-MM-DD HH:MM:SS：

data[col] = pd.to_datetime(data[col], format='%Y-%m-%d %H:%M:%S', errors='coerce')

• 格式为2024年5月10日 14:30：

data[col] = pd.to_datetime(data[col], format='%Y年%m月%d日 %H:%M', errors='coerce')

步骤3：处理缺失时间值（NaT） #

根据业务需求选择处理方式：

# 方式1：删除包含NaT的行
data = data.dropna(subset=time_cols)
# 方式2：用前一行的有效时间值填充
data[time_cols] = data[time_cols].fillna(method='ffill')
# 方式3：用指定默认时间填充
data[time_cols] = data[time_cols].fillna(pd.Timestamp('2000-01-01 00:00:00'))

步骤4：标准化时间格式 #

若需要将时间转为统一的字符串格式（方便导出或展示）：

for col in time_cols:
    data[col] = data[col].dt.strftime('%Y-%m-%d %H:%M:%S')

若需保留datetime类型以便后续时间运算（比如计算时长），则无需转换，直接使用即可。

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