使用Pandas to_datetime转换CSV日期格式时遇ValueError问题

问题核心 #

你的CSV时间数据存在混合格式：部分是带秒和AM/PM的08-09-22 14:07:00 AM，部分是仅到分钟、缺失秒和AM/PM的08-09-22 14:07；同时标注小时为00-12格式，但数据里出现了14这类24小时制数值，导致格式匹配直接失败。

分步解决方案 #

1. 统一提取时间成分再转换

先拆分字符串提取所有时间元素，补全缺失的秒和AM/PM，再统一转换为datetime格式：

import pandas as pd

# 拆分日期和时间部分
df[['date_part', 'time_part']] = df['time'].str.split(' ', n=1, expand=True)
# 提取时、分、秒（缺省则为空）、AM/PM标识
df[['hour', 'minute', 'second', 'ampm']] = df['time_part'].str.extract(r'(\d{2}):(\d{2})(?::(\d{2}))? ?([APM]+)?', expand=True)

# 补全缺失值：秒默认填00，AM/PM根据小时数值判断
df['second'] = df['second'].fillna('00')
df['ampm'] = df.apply(lambda x: 'PM' if int(x['hour']) >=12 else 'AM' if pd.notna(x['hour']) else x['ampm'], axis=1)
# 24小时制转12小时制小时数
df['hour'] = df['hour'].apply(lambda x: str(int(x)-12) if int(x) >12 else x)

# 拼接成统一格式字符串后转换
df['time_unified'] = df['date_part'] + ' ' + df['hour'] + ':' + df['minute'] + ':' + df['second'] + ' ' + df['ampm']
df['time'] = pd.to_datetime(df['time_unified'], format='%d-%m-%y %I:%M:%S %p')

# 清理临时生成的列
df = df.drop(['date_part', 'time_part', 'hour', 'minute', 'second', 'ampm', 'time_unified'], axis=1)

2. 多格式批量匹配转换

Pandas 2.0及以上版本支持format参数传入格式列表，自动匹配对应格式转换：

# 列出所有可能的时间格式
formats = [
    '%d-%m-%y %H:%M:%S %p',  # 带秒和AM/PM的24小时制格式
    '%d-%m-%y %H:%M',         # 仅到分钟的24小时制格式
    '%d-%m-%y %I:%M:%S %p',   # 带秒和AM/PM的12小时制格式
    '%d-%m-%y %I:%M'          # 仅到分钟的12小时制格式
]

# 尝试多格式转换，转换失败的设为NaT后续单独处理
df['time'] = pd.to_datetime(df['time'], format=formats, errors='coerce')

3. 自定义函数逐个处理格式冲突

如果数据同时存在12小时制和24小时制混合情况，用自定义函数逐个尝试匹配：

def parse_time(s):
    try:
        # 先尝试12小时制带秒和AM/PM的格式
        return pd.to_datetime(s, format='%d-%m-%y %I:%M:%S %p')
    except ValueError:
        try:
            # 尝试12小时制仅到分钟的格式
            return pd.to_datetime(s, format='%d-%m-%y %I:%M')
        except ValueError:
            try:
                # 尝试24小时制带秒的格式
                return pd.to_datetime(s, format='%d-%m-%y %H:%M:%S')
            except ValueError:
                # 最后尝试24小时制仅到分钟的格式
                return pd.to_datetime(s, format='%d-%m-%y %H:%M')

df['time'] = df['time'].apply(parse_time)

关键注意点 #

• 你之前用%Y匹配四位年份，但数据里是两位的22，应该用%y；
• %H对应24小时制小时，%I对应12小时制小时，要严格和数据格式对应；
• 若有转换失败的行，可用errors='coerce'转为NaT，再筛选出来单独处理异常数据。

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