Pandas按字符类型通过字典映射替换日期字符串方法

处理金融衍生品合约代码（Symbol字段）生成日期列的场景中，初始文件无现成日期字段，通过正则按两位数字拆分Symbol字段拼接生成date列时出现格式不统一问题。

原有拆分实现代码 #

df[['Symbol', 'year', 'month']] = df['Symbol'].str.split("(\d\d)", n=1, expand=True)
df['year'] = '20' + df['year']
df['month'] = df['month'].str[:3]
df['date'] = df['month'] + '-' + df['year']

现存问题 #

• 生成的date列共三类格式值：
  • 三位数字开头：如304-2018、422-2019
  • D加两位数字开头：如D08-2019
  • 标准三位月份缩写开头：如JAN-2019、DEC-2020
• 调用pd.to_datetime(df['date'])时抛出错误：dateutil.parser._parser.ParserError: Unknown string format: D08-2019

替换规则与配置 #

现有映射字典：

sample_dict = {3 : 'MAR', 4 : 'APR', 5:'MAY', 'D': 'DEC'}

明确替换规则：

• 若日期前三位为数字，取首位数字匹配字典对应值，替换为「对应月份缩写-年份」格式，例：304-2018需转为MAR-2018
• 若日期首字符为D且后两位为数字，匹配字典中'D'对应值'DEC'，替换为「DEC-年份」格式，例：D08-2019需转为DEC-2019；若首字符为D但后续两位为非数字字符则不做替换
• 本身为标准三位月份缩写开头的日期保持原值不变

之前尝试使用df['date'].replace(to_replace='\d\d\d', ...)方法无法正确配置多维度替换规则，目标是得到统一的「月份缩写-年份」格式date列。

参考样本 #

原始Symbol字段样本 #

['NIFTY21JULFUT', 'BANKNIFTY19MAY31500CE', 'BANKNIFTY2131834800CE', 'DRREDDY20DEC5300CE', 'BANKNIFTY2090323800CE', 'BANKNIFTY2142231000PE', 'BANKNIFTY1931427800CE', 'BANKNIFTY2170134700PE', 'BANKNIFTY19SEP29800PE', 'BANKNIFTY20JUL22000CE', 'BANKNIFTY21JUN34700PE', 'BANKNIFTY20NOV29300CE', 'TCS20OCT2900CE', 'BANKNIFTY1911027500PE', 'BRITANNIA20JUL3900PE', 'NAUKRI22JAN5000PE', 'BANKNIFTY21APR33300CE', 'ICICIBANK21MAR580PE', 'BANKNIFTY1932029800CE', 'BANKNIFTY20JAN31200CE', 'HDFCBANK20NOV1320PE', 'BANKNIFTY1941130300CE', 'RELIANCE21AUG2060PE', 'NMDC21JUN190CE', 'BANKNIFTY20O2224600CE', 'BANKNIFTY2190235500PE', 'BANKNIFTY18D2026800PE', 'BANKNIFTY2070921000PE', 'ITC22JAN225CE', 'BANKNIFTY21APR33700CE', 'NESTLEIND22MAR17400CE', 'NIFTY19JUNFUT', 'BANKNIFTY1950928900CE', 'BANKNIFTY1911727600CE', 'BANKNIFTY20N0525700CE', 'TCS20OCT2750CE', 'SIEMENS22FEB2360CE', 'BANKNIFTY20JAN31000CE', 'BANKNIFTY1932029700PE', 'BANKNIFTY2031224200CE', 'BANKNIFTY1910327400CE', 'BANKNIFTY2131837000CE', 'BANKNIFTY2161736000CE', 'NIFTY21SEPFUT', 'BANKNIFTY20OCT25100CE', 'BANKNIFTY20FEBFUT', 'BANKNIFTY1940430500CE', 'AXISBANK20MAYFUT', 'NIFTY21DECFUT', 'PERSISTENT21OCT4000CE', 'BANKNIFTY19JAN27200PE', 'BANKNIFTY20D1730800PE', 'BANKNIFTY2051419000CE', 'KOTAKBANK21AUG1740CE', 'BANKNIFTY2070221300CE', 'BANKNIFTY20MAY16600PE', 'BANKNIFTY2131835000CE', 'MOTHERSUMI20MAR110PE', 'BANKNIFTY1950928900PE', 'BANKNIFTY20MAR20900CE', 'BRITANNIA20SEP3850CE', 'BANKNIFTY2150632900CE', 'BANKNIFTY20DEC28700PE', 'BANKNIFTY2081320800PE', 'RELIANCE21MAR2300CE', 'BANKNIFTY2061120600PE', 'BANKNIFTY2072323000PE', 'BANKNIFTY20O1523600CE', 'BANKNIFTY2061121000PE', 'BANKNIFTY19JUL29100CE', 'BANKNIFTY20O1523700CE', 'DRREDDY20DEC4750PE', 'BANKNIFTY19FEB26800CE', 'BANKNIFTY1940430400PE', 'BANKNIFTY2020630900PE', 'BANKNIFTY2030530000PE', 'BANKNIFTY1931428300PE', 'BANKNIFTY20D1029000PE', 'BANKNIFTY2050719500PE', 'BANKNIFTY1941129900PE', 'BANKNIFTY2091722500CE', 'MARUTI19FEB6900CE', 'BANKNIFTY2012331500CE', 'ICICIBANK21MAR650CE', 'BANKNIFTY21JANFUT', 'BANKNIFTY19O2429100PE', 'BANKNIFTY22MARFUT', 'BANKNIFTY20JAN30600CE', 'BANKNIFTY1962030400PE', 'RELIANCE22FEB2380CE', 'RELIANCE21JUN2100PE', 'BAJAJ-AUTO21NOVFUT', 'ICICIBANK21MAR560PE', 'BANKNIFTY19JAN27000CE', 'PNB19MAR75CE', 'BANKNIFTY1910327300CE', 'BANKNIFTY19D1932000PE', 'BANKNIFTY2091022600PE', 'BANKNIFTY19OCT30200CE', 'TCS21MAR3120CE']

生成的异常date列样本 #

date
304-2018
318-2018
422-2019
530-2019
JAN-2019
DEC-2020
D08-2019

预期输出date列 #

date
MAR-2018
MAR-2018
APR-2019
MAY-2019
JAN-2019
DEC-2020
DEC-2019

不要使用简单的replace正则匹配配置多规则，直接编写自定义格式化函数逐行处理date字段即可，逻辑完全贴合给定替换规则：

import pandas as pd

# 给定映射字典
sample_dict = {3 : 'MAR', 4 : 'APR', 5:'MAY', 'D': 'DEC'}

def format_date(date_str):
    prefix, year = date_str.split('-')
    # 处理D开头且后两位为数字的场景
    if prefix.startswith('D') and len(prefix)>=3 and prefix[1:3].isdigit():
        return f"{sample_dict['D']}-{year}"
    # 处理前三位全为数字的场景
    if prefix[:3].isdigit():
        first_num = int(prefix[0])
        return f"{sample_dict[first_num]}-{year}"
    # 标准月份缩写场景直接返回原值
    return date_str

# 应用格式化函数到date列
df['date'] = df['date'].apply(format_date)

# 处理完成后可正常转换为日期类型
df['date'] = pd.to_datetime(df['date'], format='%b-%Y')

逻辑说明：按优先级依次匹配两类需要替换的格式，不符合替换条件的标准格式值直接保留，不会出现误改；转换日期类型时指定%b-%Y匹配「月份缩写-年份」格式，可彻底解决解析报错问题。

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