Pandas长列数据格式转换：日期字符串重格式化及后缀代码保留

Great question! When working with large DataFrames (like your 200k-row example), using apply() with a lambda function can be surprisingly slow—it runs row-by-row in Python, which doesn't leverage pandas' optimized C-level operations. Let's switch to vectorized methods that'll handle this task much faster.

Step 1: Split Date and Suffix (Vectorized) #

First, we'll split the combined string into separate date and suffix columns using pandas' built-in str.split()—this is fully vectorized, so it processes the entire column at once instead of row-by-row.

df[['raw_date', 'suffix']] = df['A'].str.split(' ', expand=True)

Step 2: Convert Date Format #

Next, convert the raw date string to a datetime object (again, vectorized) and format it to your desired string format. For example, if you want to switch from YYYY-MM-DD to YYYYMMDD:

df['formatted_date'] = pd.to_datetime(df['raw_date']).dt.strftime('%Y%m%d')

You can replace '%Y%m%d' with any valid date format string (like '%d/%m/%Y' for day-first format).

Step 3: Combine Back into Original Column #

Merge the formatted date and suffix back into column A, then clean up the temporary columns if you don't need them:

df['A'] = df['formatted_date'] + ' ' + df['suffix']
df = df.drop(['raw_date', 'suffix', 'formatted_date'], axis=1)

Bonus: Different Formats per Suffix #

If your requirement is to use different date formats for each suffix (e.g., A uses DD-MM-YYYY, B uses YYYY/MM/DD), we can still keep this fully vectorized with a format mapping and conditional assignment:

# Define your suffix-to-format mapping
suffix_format_map = {
    'A': '%d-%m-%Y',
    'B': '%Y/%m/%d',
    'X': '%m/%d/%Y'
}

# Split and convert to datetime
df[['raw_date', 'suffix']] = df['A'].str.split(' ', expand=True)
df['date_obj'] = pd.to_datetime(df['raw_date'])

# Apply format based on suffix (no row-by-row apply!)
for suffix, fmt in suffix_format_map.items():
    mask = df['suffix'] == suffix
    df.loc[mask, 'A'] = df.loc[mask, 'date_obj'].dt.strftime(fmt) + ' ' + suffix

# Clean up temp columns
df = df.drop(['raw_date', 'suffix', 'date_obj'], axis=1)

Why This Is Better Than apply() #

For your 200k-row dataset, these vectorized operations will be 10–100x faster than using apply(lambda...). They avoid Python-level loops and tap into pandas' optimized backend, which is designed for bulk data processing.

Full Example Code #

import pandas as pd

# Your sample data
data = ['2012-06-29 A','2012-08-29 B','2012-10-29 X','2012-10-15 A']*50000
data.sort()
df = pd.DataFrame({'A':data})

# Run the vectorized processing
df[['raw_date', 'suffix']] = df['A'].str.split(' ', expand=True)
df['formatted_date'] = pd.to_datetime(df['raw_date']).dt.strftime('%Y%m%d')
df['A'] = df['formatted_date'] + ' ' + df['suffix']
df = df.drop(['raw_date', 'suffix', 'formatted_date'], axis=1)

# Check the result
print(df.head())

Output:

A
0  20120629 A
1  20120629 A
2  20120629 A
3  20120629 A
4  20120629 A

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