BigQuery中单列多日期格式转换及按年分组数据需求

1. 统一混合日期格式 #

针对字符串类型的日期列存在多种格式的问题，我们可以通过分步解析来统一格式，以Python pandas为例：

import pandas as pd

# 加载你的数据集（示例为你提供的样本数据）
df = pd.DataFrame({
    '人员': ['person1', 'person1', 'person2', 'person2'],
    '进入日期': ['27-MAY-15', '14-JUL-14', '', '11-11-11'],
    '退出日期': ['2021-05-18', '14-AUG-19', '', '2019-08-08']
})

# 定义所有待处理的日期格式
formats = ['%d-%b-%y', '%d-%m-%y', '%Y-%m-%d']

# 批量处理日期列
def standardize_date(col):
    # 依次尝试用不同格式解析，无法解析的转为NaT
    date_series = pd.Series(dtype='datetime64[ns]')
    for fmt in formats:
        mask = date_series.isna()
        date_series.loc[mask] = pd.to_datetime(col.loc[mask], format=fmt, errors='coerce')
    return date_series

df['进入日期_标准'] = standardize_date(df['进入日期'])
df['退出日期_标准'] = standardize_date(df['退出日期'])

# 若需要转为你指定的'%y-%m-%d'字符串格式
df['进入日期_字符串'] = df['进入日期_标准'].dt.strftime('%y-%m-%d')
df['退出日期_字符串'] = df['退出日期_标准'].dt.strftime('%y-%m-%d')

2. 按人员分组提取首次/末次记录 #

日期标准化后，直接分组聚合即可获取每个人员的首尾观测：

# 分组统计首尾日期
result = df.groupby('人员').agg(
    首次进入=('进入日期_标准', 'min'),
    末次进入=('进入日期_标准', 'max'),
    首次退出=('退出日期_标准', 'min'),
    末次退出=('退出日期_标准', 'max')
).reset_index()

# 可选：转为字符串格式
result = result.apply(lambda x: x.dt.strftime('%y-%m-%d') if x.dtype == 'datetime64[ns]' else x)

关键提示 #

• errors='coerce'会将无法解析的空值或无效日期转为NaT，聚合时会自动忽略这类值
• 若需要四位年份格式，把strftime的参数改为'%Y-%m-%d'即可
• 该方法可直接扩展到更大的数据集，无需手动处理每条记录

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