如何用pd.to_datetime转换DEC-11/JUNE-11格式为日期时间列？

Hey there! I see you're stuck converting those mixed-format dates (like DEC-11 and JUNE-11) to datetime objects in pandas. Let's break down why your previous attempts failed and fix this step by step.

Why Your Earlier Tries Didn't Work #

The core issue is your date strings use two different month formats: 3-letter abbreviations (e.g., DEC) and full month names (e.g., JUNE). When you used pd.to_datetime with a single format parameter, it only matched one type of month string—leaving the other to fail (and errors='coerce' turned all those failures into NaT).

Solution 1: Custom Date Parser Function #

The most reliable way to handle mixed formats is to write a small custom function that tries both parsing patterns. Here's how:

import pandas as pd
from datetime import datetime

def parse_mixed_dates(date_str):
    # First try parsing 3-letter month + 2-digit year
    try:
        return datetime.strptime(date_str, '%b-%y')
    except ValueError:
        # If that fails, try full month name + 2-digit year
        return datetime.strptime(date_str, '%B-%y')

# Apply the function to your column
df['identity_d'] = df['identity_d'].apply(parse_mixed_dates)

Quick Format Explanation:

• %b: Matches 3-letter month abbreviations (e.g., DEC, JAN)
• %B: Matches full month names (e.g., JUNE, SEPTEMBER)
• %y: Matches 2-digit years (pandas will auto-convert 11 to 2011, 99 to 1999 by default)

Solution 2: Normalize Strings First (Optional) #

If your dates have inconsistent capitalization (e.g., dec-11, June-11), normalize them to uppercase/lowercase first to avoid parsing issues:

# Convert all date strings to uppercase
df['identity_d'] = df['identity_d'].str.upper()

# Now use the custom parser or try inferring the format (less reliable for mixed cases)
df['identity_d'] = pd.to_datetime(df['identity_d'], infer_datetime_format=True)

Verifying & Using the Converted Dates #

Once converted, you can easily filter by year or month using pandas' datetime accessor (dt):

• Filter for 2011 data:

  df_2011 = df[df['identity_d'].dt.year == 2011]
  

• Filter for June data (month number 6):

  df_june = df[df['identity_d'].dt.month == 6]
  

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