修复Python自定义get_last_period_values()函数问题

需要构建get_last_period_values()函数，获取指定前置周期（如1个月前、12个月前）的精确数值，原脚本存在逻辑错误，无法生成符合预期的输出，且pct_change()方法无法满足精确值获取需求。

原代码问题分析 #

• 合并时后缀参数顺序错误，导致列名颠倒
• 未保留前置周期的日期列
• 多次重复合并原数据，造成列冗余
• 列名含空格，与期望输出格式不符
• 日期格式未转换为目标格式

修复后的完整代码 #

import pandas as pd
import io 

## CONSTANTS ##
MONTHS_1 = 1
MONTHS_12 = 12

## CSV ##
INPUT_CSV = """
URL,Organic Keywords,Organic Traffic,Date
https://www.example-url.com/,1315,11345,20231115
https://www.example-url.com/,1183,5646,20231015
https://www.example-url.com/,869,5095,20230915
https://www.example-url.com/,925,4574,20230815
https://www.example-url.com/,899,4580,20230715
https://www.example-url.com/,1382,5720,20230615
https://www.example-url.com/,1171,5544,20230515
https://www.example-url.com/,1079,5041,20230415
https://www.example-url.com/,734,3855,20230315
https://www.example-url.com/,853,3455,20230215
https://www.example-url.com/,840,2343,20230115
https://www.example-url.com/,325,2318,20221215
https://www.example-url.com/,156,1981,20221115
https://www.example-url.com/,166,2059,20221015
https://www.example-url.com/,124,1977,20220915
https://www.example-url.com/,98,1919,20220815
https://www.example-url.com/,167,1796,20220715
https://www.example-url.com/,140,1596,20220615
https://www.example-url.com/,168,1493,20220515
https://www.example-url.com/,171,1058,20220415
https://www.example-url.com/,141,1735,20220315
https://www.example-url.com/,129,1836,20220215
https://www.example-url.com/,141,746,20220115
https://www.example-url.com/,129,1076,20211215
"""

## HELPERS ##
def get_last_period_values(df, months_prior):
    # 复制原数据的日期、关键词、流量列用于匹配前置数据
    prior_df = df[['Date', 'Organic Keywords', 'Organic Traffic']].copy()
    # 为前置数据列添加后缀，区分原数据
    suffix = f'_{months_prior}mo_Prior'
    prior_df = prior_df.rename(columns={
        'Date': f'Date{suffix}',
        'Organic Keywords': f'Organic_Keywords{suffix}',
        'Organic Traffic': f'Organic_Traffic{suffix}'
    })
    # 计算每个日期对应的前置周期日期
    df_with_prior = df.copy()
    df_with_prior[f'Date{suffix}'] = df_with_prior['Date'] - pd.DateOffset(months=months_prior)
    # 合并前置数据，左连接确保原数据行完整
    df_with_prior = df_with_prior.merge(prior_df, on=f'Date{suffix}', how='left')
    return df_with_prior

## MAIN ##

# 读取CSV数据
df = pd.read_csv(io.StringIO(INPUT_CSV))

# 转换日期列格式为datetime
df['Date'] = pd.to_datetime(df['Date'], format='%Y%m%d')

# 按日期降序排序
df = df.sort_values(by='Date', ascending=False)

# 添加1个月前置周期数据
df = get_last_period_values(df, MONTHS_1)

# 添加12个月前置周期数据
df = get_last_period_values(df, MONTHS_12)

# 重命名原数据列，替换空格为下划线，匹配期望输出格式
df = df.rename(columns={
    'Organic Keywords': 'Organic_Keywords',
    'Organic Traffic': 'Organic_Traffic'
})

# 调整列顺序，与期望输出一致
desired_columns = [
    'URL', 'Date', 'Organic_Keywords', 'Organic_Traffic',
    'Date_12mo_Prior', 'Organic_Keywords_12mo_Prior', 'Organic_Traffic_12mo_Prior',
    'Date_1mo_Prior', 'Organic_Keywords_1mo_Prior', 'Organic_Traffic_1mo_Prior'
]
df = df[desired_columns]

# 将日期列格式转换为MM/DD/YYYY
date_columns = [col for col in df.columns if 'Date' in col]
df[date_columns] = df[date_columns].apply(lambda x: x.dt.strftime('%m/%d/%Y'))

# 输出CSV格式结果，空值显示为空字符串
print(df.to_csv(index=False, na_rep=''))

修复说明 #

1. 修正后缀逻辑：将前置数据列添加后缀，原数据列名保持不变，避免列名颠倒
2. 保留前置日期列：新增Date_{months_prior}mo_Prior列，展示前置周期的日期
3. 避免重复合并：直接在原DataFrame上逐步添加前置数据，消除冗余列和数据混乱
4. 统一列名格式：将原列名中的空格替换为下划线，与期望输出一致
5. 转换日期格式：把日期统一格式化为MM/DD/YYYY，匹配期望输出的日期显示样式

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