如何基于指定日期与更新日期范围生成年份标识变量？

核心思路 #

1. 先将文本格式的日期转换为可计算的日期类型，重点处理两位年份的世纪归属（比如97对应1997，12对应2012）。
2. 确定数据覆盖的所有年份范围，生成对应的年份列（如yr2000、yr2001）。
3. 对每一行数据，判断每个年份是否落在指定日期与最后更新日期的年份区间内，符合条件标记为1，否则为0。

方法一：Python Pandas实现 #

步骤1：加载并转换数据

import pandas as pd

# 示例数据
data = {
    'designation': ['11/2/12', '5/15/02', '11/6/12', '12/15/20', '10/4/22', '7/14/97'],
    'designationupdate': ['9/10/21', '6/29/12', '7/7/21', '9/22/22', '10/4/22', '2/4/10'],
    'fipscode': ['10001', '10005', '10005', '10005', '1001', '1001']
}

df = pd.DataFrame(data)

# 转换日期列，自动识别两位年份的世纪
df['designation'] = pd.to_datetime(df['designation'], format='%m/%d/%y')
df['designationupdate'] = pd.to_datetime(df['designationupdate'], format='%m/%d/%y')

# 确保取早的日期为起始，晚的为结束（避免日期顺序颠倒的情况）
df['start_date'] = df[['designation', 'designationupdate']].min(axis=1)
df['end_date'] = df[['designation', 'designationupdate']].max(axis=1)

步骤2：生成年份标记列

# 获取数据覆盖的年份范围
min_year = df['start_date'].dt.year.min()
max_year = df['end_date'].dt.year.max()

# 为每个年份创建标记列
for year in range(min_year, max_year + 1):
    col_name = f'yr{year}'
    df[col_name] = df.apply(lambda row: 1 if (row['start_date'].year <= year <= row['end_date'].year) else 0, axis=1)

# 整理输出结果
result = df[['designation', 'designationupdate', 'fipscode'] + [f'yr{y}' for y in range(min_year, max_year+1)]]
print(result.to_string(index=False))

方法二：SQL实现（以MySQL为例） #

假设数据表名为date_data，字段为designation(字符串)、designationupdate(字符串)、fipscode(字符串)。

固定年份范围实现

SELECT 
    STR_TO_DATE(designation, '%m/%d/%y') AS designation,
    STR_TO_DATE(designationupdate, '%m/%d/%y') AS designationupdate,
    fipscode,
    -- 逐个判断年份是否在区间内
    CASE WHEN YEAR(LEAST(STR_TO_DATE(designation, '%m/%d/%y'), STR_TO_DATE(designationupdate, '%m/%d/%y'))) <= 1997 
         AND YEAR(GREATEST(STR_TO_DATE(designation, '%m/%d/%y'), STR_TO_DATE(designationupdate, '%m/%d/%y'))) >= 1997 THEN 1 ELSE 0 END AS yr1997,
    CASE WHEN YEAR(LEAST(STR_TO_DATE(designation, '%m/%d/%y'), STR_TO_DATE(designationupdate, '%m/%d/%y'))) <= 2000 
         AND YEAR(GREATEST(STR_TO_DATE(designation, '%m/%d/%y'), STR_TO_DATE(designationupdate, '%m/%d/%y'))) >= 2000 THEN 1 ELSE 0 END AS yr2000,
    CASE WHEN YEAR(LEAST(STR_TO_DATE(designation, '%m/%d/%y'), STR_TO_DATE(designationupdate, '%m/%d/%y'))) <= 2022 
         AND YEAR(GREATEST(STR_TO_DATE(designation, '%m/%d/%y'), STR_TO_DATE(designationupdate, '%m/%d/%y'))) >= 2022 THEN 1 ELSE 0 END AS yr2022
    -- 按需添加更多年份列
FROM date_data;

方法三：SAS实现 #

/* 读取示例数据 */
data date_data;
    input designation $ designationupdate $ fipscode $;
    datalines;
11/2/12            9/10/21                      10001
5/15/02            6/29/12                      10005
11/6/12            7/7/21                       10005
12/15/20           9/22/22                      10005
10/4/22            10/4/22                      1001
7/14/97            2/4/10                       1001
;
run;

/* 转换日期格式并计算年份区间 */
data date_converted;
    set date_data;
    designation_date = input(designation, mmddyy10.);
    update_date = input(designationupdate, mmddyy10.);
    start_year = year(min(designation_date, update_date));
    end_year = year(max(designation_date, update_date));
    format designation_date update_date mmddyy10.;
run;

/* 生成年份标记列 */
data result;
    set date_converted;
    yr1997 = ifn(start_year <= 1997 <= end_year, 1, 0);
    yr2000 = ifn(start_year <= 2000 <= end_year, 1, 0);
    yr2010 = ifn(start_year <= 2010 <= end_year, 1, 0);
    yr2022 = ifn(start_year <= 2022 <= end_year, 1, 0);
    -- 按需添加更多年份列
    keep designation designationupdate fipscode yr1997 yr2000 yr2010 yr2022;
run;

/* 查看结果 */
proc print data=result;
run;

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