Pandas处理百万级多格式日期列的性能优化方案咨询

我有12个百万级规模的数据集，每个数据集都包含"Created Date"列，且该列存在多种日期格式。目前我通过重复调用pd.to_datetime处理每个数据集，但执行速度太慢，求可行的提速方案。

当前使用的代码：

Appilication1['Created Date'] = pd.to_datetime(Appilication1['Created Date'], dayfirst=True,errors = 'coerce')
Appilication2['Created Date'] = pd.to_datetime(Appilication2['Created Date'], dayfirst=True,errors = 'coerce')
Appilication3['Created Date'] = pd.to_datetime(Appilication3['Created Date'], dayfirst=True,errors = 'coerce')
Appilication4['Created Date'] = pd.to_datetime(Appilication4['Created Date'], dayfirst=True,errors = 'coerce')
Appilication5['Created Date'] = pd.to_datetime(Appilication5['Created Date'], dayfirst=True,errors = 'coerce')
Appilication6['Created Date'] = pd.to_datetime(Appilication6['Created Date'], dayfirst=True,errors = 'coerce')
Appilication7['Created Date'] = pd.to_datetime(Appilication7['Created Date'], dayfirst=True,errors = 'coerce')
Appilication8['Created Date'] = pd.to_datetime(Appilication8['Created Date'], dayfirst=True,errors = 'coerce')
Appilication9['Created Date'] = pd.to_datetime(Appilication9['Created Date'], dayfirst=True,errors = 'coerce')
Appilication10['Created Date'] = pd.to_datetime(Appilication10['Created Date'], dayfirst=True,errors = 'coerce')
Appilication11['Created Date'] = pd.to_datetime(Appilication11['Created Date'], dayfirst=True,errors = 'coerce')
Appilication12['Created Date'] = pd.to_datetime(Appilication12['Created Date'], dayfirst=True,errors = 'coerce')

优化方案 #

1. 指定已知日期格式，跳过自动推断 #

pd.to_datetime默认的格式推断是耗时的主要原因。如果能提前统计出所有存在的日期格式，直接传入format参数（支持列表），能大幅压缩处理时间：

# 替换成你实际统计出的日期格式
date_formats = ['%d/%m/%Y', '%Y-%m-%d', '%d-%m-%Y %H:%M:%S']

# 用循环简化重复代码
dataframes = [Appilication1, Appilication2, Appilication3, Appilication4,
              Appilication5, Appilication6, Appilication7, Appilication8,
              Appilication9, Appilication10, Appilication11, Appilication12]

for df in dataframes:
    df['Created Date'] = pd.to_datetime(
        df['Created Date'],
        dayfirst=True,
        errors='coerce',
        format=date_formats
    )

2. 合并数据集批量处理后拆分 #

把所有数据集的日期列合并成一个大Series处理，再拆分回原数据集。批量处理能最大化利用pandas的向量化优势，减少重复的初始化开销：

# 收集所有日期列
combined_dates = pd.concat([df['Created Date'] for df in dataframes], ignore_index=True)
# 批量转换
combined_dates = pd.to_datetime(combined_dates, dayfirst=True, errors='coerce')

# 拆分回原数据集
start_idx = 0
for df in dataframes:
    end_idx = start_idx + len(df)
    df['Created Date'] = combined_dates[start_idx:end_idx].reset_index(drop=True)
    start_idx = end_idx

3. 用C实现的第三方库加速解析 #

如果格式复杂难以枚举，试试ciso8601——用C编写的日期解析库，速度远快于pandas默认实现：

import ciso8601

def fast_parse_date(date_str):
    try:
        return ciso8601.parse_datetime(date_str)
    except:
        return pd.NaT

for df in dataframes:
    df['Created Date'] = df['Created Date'].apply(fast_parse_date)

注意：ciso8601对非主流格式支持有限，需先测试兼容性。

4. 多进程并行处理 #

利用多CPU核心并行处理不同数据集，适合数据集数量较多的场景：

from multiprocessing import Pool

def process_single_df(df):
    df['Created Date'] = pd.to_datetime(df['Created Date'], dayfirst=True, errors='coerce')
    return df

# 根据你的CPU核心数调整processes参数
with Pool(processes=4) as pool:
    processed_dfs = pool.map(process_single_df, dataframes)

# 把处理后的结果赋值回原变量
(Appilication1, Appilication2, Appilication3, Appilication4,
 Appilication5, Appilication6, Appilication7, Appilication8,
 Appilication9, Appilication10, Appilication11, Appilication12) = processed_dfs

5. 读取数据时直接处理日期 #

如果数据集是从文件读取的，直接在pd.read_csv/pd.read_excel中指定日期解析逻辑，避免后续单独处理的开销：

# 读取单个CSV文件的示例
df = pd.read_csv(
    'application1.csv',
    parse_dates=['Created Date'],
    date_parser=lambda x: pd.to_datetime(x, dayfirst=True, errors='coerce')
)

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