本文详细阐述了如何利用pandas库高效地计算dataframe中指定指标的历史同期值，并进一步分析其绝对变化量和百分比变化率。通过构建可复用的函数，我们能够灵活地获取任意前n个月的数据，并将其与当前数据进行合并，为时间序列分析提供强大的数据支持。
在数据分析领域，特别是对时间序列数据进行分析时，经常需要将当前数据与历史同期数据进行比较，以评估增长、下降趋势或季节性影响。例如，我们可能需要将本月销售额与上月或去年同月销售额进行对比。Pandas的pct_change()方法虽然可以计算百分比变化，但它通常用于计算连续周期（如上一行）的变化，且直接获取精确的历史同期值并不直接。本教程将介绍一种基于pd.DateOffset和merge操作的通用方法，以精确获取任意历史周期的值及其变化。
解决此问题的核心在于以下两步：
首先，我们需要一个包含日期和相关指标的DataFrame。以下是一个示例数据集，我们将用它来演示。
import pandas as pd
import io
# 示例输入数据
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
"""
# 读取CSV数据
df = pd.read_csv(io.StringIO(INPUT_CSV))
# 定义常量，方便管理
INITIAL_COL_REORDER = ['URL', 'Date', 'Organic Keywords', 'Organic Traffic']
METRIC_COLS = ['Organic Keywords', 'Organic Traffic']
DIMENSION_COLS = ['URL'] # 如果有多个维度，可以添加
DATE_COL = 'Date'
# 预处理：重排、转换日期格式、按日期降序排序
df = df[INITIAL_COL_REORDER]
df[DATE_COL] = pd.to_datetime(df[DATE_COL], format='%Y%m%d')
df = df.sort_values(by=DATE_COL, ascending=False)
print("原始数据（部分）：")
print(df.head())这个函数是核心，它接收DataFrame、回溯月份数以及指标和维度列，并返回一个包含历史同期值的新DataFrame。
def get_last_period_values(df, months_prior, metric_cols, dimension_cols, date_col):
df_copy = df.copy() # 避免修改原始DataFrame
# 1. 计算目标历史日期
# 为当前日期创建一个对应的历史日期列
df_copy[f'{date_col}_Prior'] = df_copy[date_col] - pd.DateOffset(months=months_prior)
# 2. 合并历史数据
# 将原始DataFrame与自身进行左连接，根据计算出的历史日期和维度列进行匹配
# suffixes 参数用于区分合并后的同名列，例如 'Organic Keywords' 会变成 'Organic Keywords_1mo_Prior'
df_copy = df_copy.merge(
df_copy[[date_col] + dimension_cols + metric_cols],
left_on=[f'{date_col}_Prior'] + dimension_cols, # 连接键：历史日期 + 维度列
right_on=[date_col] + dimension_cols,
how='left', # 左连接保留所有当前行，没有匹配的历史数据则为NaN
suffixes=('', f'_{months_prior}mo_Prior')
)
# 清理：删除临时创建的历史日期列和合并时产生的多余维度列
df_copy = df_copy.drop(columns=[f'{date_col}_Prior'] + [col + f'_{months_prior}mo_Prior' for col in dimension_cols])
# 3. 计算绝对变化量和百分比变化率
for metric in metric_cols:
# 绝对变化 = 当前值 - 历史值
df_copy[f'{metric}_{months_prior}mo_Abs_Change'] = df_copy[metric] - df_copy[f'{metric}_{months_prior}mo_Prior']
# 百分比变化 = (当前值 / 历史值) - 1
df_copy[f'{metric}_{months_prior}mo_Pct_Change'] = df_copy[metric] / df_copy[f'{metric}_{months_prior}mo_Prior'] - 1
# 对百分比变化进行四舍五入
df_copy[f'{metric}_{months_prior}mo_Pct_Change'] = df_copy[f'{metric}_{months_prior}mo_Pct_Change'].round(2)
return df_copy函数详解：
为了方便地计算多个历史周期的值，我们可以再封装一个函数 get_period_values。
def get_period_values(df, periods, metric_cols, dimension_cols, date_col):
df_copy = df.copy()
for period in periods:
df_copy = get_last_period_values(df_copy, period, metric_cols, dimension_cols, date_col)
return df_copy这个函数接收一个periods列表（例如[1, 3, 12]），然后循环调用get_last_period_values函数，将不同历史周期的数据逐步添加到DataFrame中。
将上述所有部分整合，形成一个完整的、可运行的Python脚本。
import pandas as pd
import io
## 常量定义
INITIAL_COL_REORDER = ['URL', 'Date', 'Organic Keywords', 'Organic Traffic']
METRIC_COLS = ['Organic Keywords', 'Organic Traffic']
DIMENSION_COLS = ['URL']
DATE_COL = 'Date'
PERIODS = [1, 3, 12] # 需要计算的历史周期（月）
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
"""
## 辅助函数 - 获取指定历史周期的值及其变化
def get_last_period_values(df, months_prior, metric_cols, dimension_cols, date_col):
df_copy = df.copy()
df_copy[f'{date_col}_Prior'] = df_copy[date_col] - pd.DateOffset(months=months_prior)
df_copy = df_copy.merge(
df_copy[[date_col] + dimension_cols + metric_cols],
left_on=[f'{date_col}_Prior'] + dimension_cols,
right_on=[date_col] + dimension_cols,
how='left',
suffixes=('', f'_{months_prior}mo_Prior')
)
df_copy = df_copy.drop(columns=[f'{date_col}_Prior'] + [col + f'_{months_prior}mo_Prior' for col in dimension_cols])
for metric in metric_cols:
df_copy[f'{metric}_{months_prior}mo_Abs_Change'] = df_copy[metric] - df_copy[f'{metric}_{months_prior}mo_Prior']
df_copy[f'{metric}_{months_prior}mo_Pct_Change'] = df_copy[metric] / df_copy[f'{metric}_{months_prior}mo_Prior'] - 1
df_copy[f'{metric}_{months_prior}mo_Pct_Change'] = df_copy[f'{metric}_{months_prior}mo_Pct_Change'].round(2)
return df_copy
## 辅助函数 - 迭代计算多个历史周期的值
def get_period_values(df, periods, metric_cols, dimension_cols, date_col):
df_copy = df.copy()
for period in periods:
df_copy = get_last_period_values(df_copy, period, metric_cols, dimension_cols, date_col)
return df_copy
## 主脚本
if __name__ == '__main__':
# 1. 读取数据
df = pd.read_csv(io.StringIO(INPUT_CSV))
# 2. 数据预处理
df = df[INITIAL_COL_REORDER]
df[DATE_COL] = pd.to_datetime(df[DATE_COL], format='%Y%m%d')
df = df.sort_values(by=DATE_COL, ascending=False) # 按日期降序排序
# 3. 计算历史同期值及变化
df_final = get_period_values(df, PERIODS, METRIC_COLS, DIMENSION_COLS, DATE_COL)
# 4. 显示结果
print("\n最终结果 DataFrame:")
print(df_final.to_string()) # 使用 to_string() 避免截断显示输出示例（部分）：
最终结果 DataFrame:
URL Date Organic Keywords Organic Traffic Organic Keywords_1mo_Prior Organic Traffic_1mo_Prior Organic Keywords_1mo_Abs_Change Organic Traffic_1mo_Abs_Change Organic Keywords_1mo_Pct_Change Organic Traffic_1mo_Pct_Change Organic Keywords_3mo_Prior Organic Traffic_3mo_Prior Organic Keywords_3mo_Abs_Change Organic Traffic_3mo_Abs_Change Organic Keywords_3mo_Pct_Change Organic Traffic_3mo_Pct_Change Organic Keywords_12mo_Prior Organic Traffic_12mo_Prior Organic Keywords_12mo_Abs_Change Organic Traffic_12mo_Abs_Change Organic Keywords_12mo_Pct_Change Organic Traffic_12mo_Pct_Change
0 https://www.example-url.com/ 2023-11-15 1315 11345 1183 5646 132 5699 0.11 1.01 869 5095 446 6250 0.51 1.23 156 1981 1159 9364 7.43 4.73
1 https://www.example-url.com/ 2023-10-15 1183 5646 869 5095 314 551 0.36 0.11 925 4574 258 1072 0.28 0.23 166 2059 1017 3587 6.13 1.74
...
22 https://www.example-url.com/ 2022-01-15 141 746 129 1076 12 -330 0.09 -0.31 NaN NaN NaN NaN NaN NaN 141 746 0 0 0.00 0.00
23 https://www.example-url.com/ 2021-12-15 129 1076 NaN NaN NaN NaN NaN以上就是使用Pandas计算历史同期值及变化率的通用方法的详细内容，更多请关注php中文网其它相关文章！
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
945
收藏
