如何统一Excel中混杂日期格式并正确解析为Pandas DataFrame-Python教程-PHP中文网

如何统一Excel中混杂日期格式并正确解析为Pandas DataFrame

本文详解如何使用pandas的to_datetime(..., format='mixed', dayfirst=True)安全、鲁棒地处理SAP导出Excel中dd/mm/yyyy、mm/dd/yyyy、datetime对象及带时间字符串等多格式共存的日期列，实现全量统一为标准datetime类型。

本文详解如何使用pandas的to_datetime(..., format='mixed', dayfirst=true)安全、鲁棒地处理sap导出excel中dd/mm/yyyy、mm/dd/yyyy、datetime对象及带时间字符串等多格式共存的日期列，实现全量统一为标准datetime类型。

在实际数据处理中，尤其是从SAP等企业系统导出的Excel报表，常因导出逻辑或本地化设置导致同一日期列混杂多种格式：纯字符串（如'13/01/2025'）、Python原生datetime.datetime对象（如datetime(2025, 8, 1)）、带零时分秒的ISO格式（如'2025-08-01 00:00:00'），甚至存在dd.mm.yyyy或mm/dd/yyyy歧义格式。直接调用pd.to_datetime()默认行为易因格式冲突而报错或误解析（例如将01/09/2025识别为2025年1月9日而非9月1日），尤其当列中同时存在datetime对象和字符串时，旧版pandas可能静默失败。

推荐方案：使用 format='mixed' + dayfirst=True

自 pandas 2.0 起，to_datetime 新增 format='mixed' 参数，专为混合格式设计：它会自动检测每行数据的实际格式，并结合dayfirst参数（指定优先按“日/月/年”解析）进行智能推断，完美适配您描述的三种场景：

import pandas as pd

# 读取Excel（注意：避免header=1导致列名错位，建议先确认原始表头位置）
df = pd.read_excel("Reportes/Crudos/Reporte SAP.xlsx", header=0)  # 或根据实际调整header

# 关键步骤：统一解析日期列，支持datetime对象、dd/mm/yyyy、mm/dd/yyyy、ISO等混合输入
date_col = "Asiento contable (Fecha de contabilización)"
df[date_col] = pd.to_datetime(
    df[date_col], 
    format='mixed',     # 启用混合格式自动识别
    dayfirst=True,      # 明确声明"日优先"，解决dd/mm/yyyy vs mm/dd/yyyy歧义
    errors='coerce'     # 遇到无法解析的值转为NaT（可选，便于后续排查）
)

# 验证结果：全部转为标准datetime64[ns]类型，且日期语义正确
print(f"数据类型: {df[date_col].dtype}")
print(f"唯一值示例:\n{df[date_col].dt.strftime('%d/%m/%Y').unique()}")

✅ 为什么此方案可靠？

openclawkit-excel

Excel操作工具，支持读写、数据处理和报表生成。

下载

format='mixed' 内部调用更健壮的解析器，能区分 datetime 对象（直接转换）、字符串（按dayfirst规则尝试多种pattern匹配）；
dayfirst=True 确保所有含斜杠/点的字符串（如'13/01/2025'、'01.09.2025'）均被解释为“13日1月2025年”，避免1-12日被误判为月份；
不再需要手动正则清洗或分段处理，代码简洁且覆盖全面。

⚠️ 注意事项与最佳实践

避免提前导出中间CSV/XLSX：您的原始代码中将日期列单独导出为CSV/XLSX再读取，这会加剧格式混乱（Excel自动重格式化、CSV无类型信息）。应直接在原始DataFrame上处理日期列；
检查原始列名准确性：确保列名字符串完全匹配（含空格、括号），可用df.columns.tolist()确认；
处理缺失/异常值：添加errors='coerce'将非法值转为NaT，之后用df[date_col].isna().sum()检查清洗质量；
输出标准化格式（如需dd/mm/yyyy字符串）：解析为datetime后，用.dt.strftime('%d/%m/%Y')生成统一字符串，但强烈建议保留datetime类型用于计算（如排序、时间差）；
兼容旧版pandas（：若无法升级，需降级方案——先用apply配合dateutil.parser.parse（需安装python-dateutil）并显式传入dayfirst=True。