在Pandas apply中处理日期转换的nan值异常并收集nan行

我写了一个基于正则表达式的日期格式转换函数conv_date，用于在Pandas的apply方法中转换日期。但数据里存在NaN值，我希望在主逻辑中处理这些值，把所有包含NaN值的行添加到一个空列表里。之前实现过类似的行收集操作，但没结合异常处理做，想要实现类似SQL中case when的分支逻辑来处理代码里的异常。

当前函数代码：

from datetime import datetime, date
import re

def conv_date(dte: str) -> date:
    acceptable_mappings = {
        "\\d{4}-\\d{2}-\\d{2}": "%Y-%m-%d",
        "\\d{2}-\\d{2}-\\d{4}": "%d-%m-%Y",
        "\\d{4}/\\d{2}/\\d{2}": "%Y/%m/%d",
        "\\d{2}/\\d{2}/\\d{4}": "%d/%m/%Y",
        "\\d{8}": '%d%m%Y',
        "\\d{2}\\s\\d{2}\\s\\d{4}": '%d %m %Y',
        "\\d{4}-\\d{2}-\\d{2}\\s\\d{2}\\:\\d{2}\\:\\d{2}": "%Y-%m-%d %H:%M:%S",
        "\\d{4}-\\d{2}-\\d{2}\\s\\d{2}\\D{1}\\d{2}\\D{1}\\d{2}\\s\\w{3}": "%Y-%m-%d %H:%M:%S %Z",

    }
    # 遍历允许的格式映射，匹配则返回转换后日期，否则抛出异常
    for regex in acceptable_mappings.keys():
        if re.fullmatch(regex, dte):
            return datetime.strptime(dte, acceptable_mappings[regex]).date()
    raise Exception(f"Expected date is not in one of the supported formats, got ***{dte}***")

测试代码：

from x import conv_date
import pytest
from datetime import datetime, date
import pandas as pd

def test_mock_dict(self):
    # 测试数据，name和role字段仅为可读性添加
    mock_dict = [
        {"name": "dz", "role": "legend", "date": "2023-07-26"},
        {"name": "mc", "role": "sounds like a dj", "date": "26-07-2023"},
        {"name": "xc", "role": "loves xcom", "date": "2023/07/26"},
        {"name": "lz", "role": "likes to fly", "date": "26/07/2023"},
        {"name": "wc", "role": "has a small bladder", "date": "26072023"},
        {"name": "aa", "role": "warrior of the crystal", "date": "26 07 2023"},
        {"name": "xx", "role": "loves only-fans", "date": "2023-07-26 12:46:21"},
        {"name": "jm", "role": "is stack overflow", "date": "2023-10-26 12:46:21 UTC"},
        {"name": "ee", "role": "enjoys nan bread", "date": "nan"},
    ]

    df = pd.DataFrame(mock_dict)

    print(df)
    df['date_clean'] = df['date'].apply(lambda x: conv_date(x))
    print(df)

核心思路 #

1. 识别NaN值：区分Pandas原生的NaN（float类型）和字符串形式的"nan"，统一处理
2. 异常捕获：捕获日期转换失败的异常，将对应行收集到列表
3. 分支逻辑：通过类似case when的分层判断，先处理NaN，再尝试转换，失败则收集

修改后的代码 #

主处理逻辑（测试代码修改）

from x import conv_date
import pandas as pd
from datetime import datetime, date

def test_mock_dict():
    mock_dict = [
        {"name": "dz", "role": "legend", "date": "2023-07-26"},
        {"name": "mc", "role": "sounds like a dj", "date": "26-07-2023"},
        {"name": "xc", "role": "loves xcom", "date": "2023/07/26"},
        {"name": "lz", "role": "likes to fly", "date": "26/07/2023"},
        {"name": "wc", "role": "has a small bladder", "date": "26072023"},
        {"name": "aa", "role": "warrior of the crystal", "date": "26 07 2023"},
        {"name": "xx", "role": "loves only-fans", "date": "2023-07-26 12:46:21"},
        {"name": "jm", "role": "is stack overflow", "date": "2023-10-26 12:46:21 UTC"},
        {"name": "ee", "role": "enjoys nan bread", "date": "nan"},
        # 新增格式错误的测试项，验证异常捕获
        {"name": "bb", "role": "bad date", "date": "invalid-date-2023"},
    ]

    df = pd.DataFrame(mock_dict)
    # 用于收集无效行的空列表
    invalid_rows = []

    def process_date(row):
        dte = row['date']
        # 处理NaN（包括原生NaN和字符串"nan"）
        if pd.isna(dte) or str(dte).strip().lower() == 'nan':
            invalid_rows.append(row.to_dict())
            return pd.NaT
        # 尝试转换日期，捕获异常
        try:
            return conv_date(dte)
        except Exception:
            invalid_rows.append(row.to_dict())
            return pd.NaT

    # 按行处理，实现分支逻辑
    df['date_clean'] = df.apply(process_date, axis=1)

    print("处理后的数据：")
    print(df)
    print("\n收集的无效行：")
    for row in invalid_rows:
        print(row)

if __name__ == "__main__":
    test_mock_dict()

说明 #

• NaN处理：通过pd.isna()判断原生NaN，同时将输入转为字符串后判断是否为"nan"，覆盖两种常见NaN场景
• 异常捕获：用try-except包裹conv_date调用，捕获所有转换失败的情况，将对应行加入invalid_rows
• 分支逻辑：先判断是否为NaN，再尝试正常转换，最后处理转换失败，完全对应SQL中case when的分层判断逻辑
• 返回值：无效行返回pd.NaT，保证date_clean列的日期类型一致性

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