如何在Python中不依赖shift等固定间隔函数计算周环比差异

问题背景 #

手头有一个包含每小时交易时间戳和小时销售额的DataFrame，需要按小时和工作日计算周环比差异。数据存在小时缺失的情况，使用shift(168)这类固定行间隔的方法不可靠（缺失行导致间隔错位）。此前在SQL中通过自连接实现了需求，现在需要在Python/Pandas中找到稳妥的替代方案。

数据快照 #

transaction_time     |   total_sale
2024-01-05 00:00:00  |      3500
2024-01-05 02:00:00  |      4000
...
2024-01-12 00:00:00  |      3400
2024-01-12 01:00:00  |      3200
2024-01-12 02:00:00  |      4100
...
2024-01-19 00:00:00  |      5000
2024-01-19 01:00:00  |      4200

原SQL实现逻辑 #

通过自连接匹配同一小时且时间差7天的记录，计算周环比：

with
base_tbl AS 
(
select 
   transaction_time,
   total_sale
from table
where transaction_time between <desired timeframe>
)

 select
    t.transaction_time
    , SAFE_SUBTRACT(SAFE_DIVIDE(t.total_sale, c.total_sale), 1) AS wow_percent_change
  FROM
    base_tbl AS t
  FULL OUTER JOIN
    base_tbl AS c
  ON EXTRACT(HOUR FROM t.transaction_time) = EXTRACT(HOUR FROM c.transaction_time)
  AND TIMESTAMP_DIFF(t.transaction_time, c.transaction_time, DAY)=7 

Pandas解决方案 #

直接复刻SQL的自连接逻辑，通过时间精准匹配替代固定行偏移，完美适配缺失数据场景：

步骤1：预处理时间字段 #

先将时间戳转为Pandas datetime类型，并生成辅助字段：

import pandas as pd

# 加载数据（示例）
data = [
    ("2024-01-05 00:00:00", 3500),
    ("2024-01-05 02:00:00", 4000),
    ("2024-01-12 00:00:00", 3400),
    ("2024-01-12 01:00:00", 3200),
    ("2024-01-12 02:00:00", 4100),
    ("2024-01-19 00:00:00", 5000),
    ("2024-01-19 01:00:00", 4200)
]
df = pd.DataFrame(data, columns=["transaction_time", "total_sale"])

# 转换时间格式
df["transaction_time"] = pd.to_datetime(df["transaction_time"])
# 计算上周同一时间点（当前时间减7天）
df["prev_week_time"] = df["transaction_time"] - pd.Timedelta(days=7)

步骤2：自连接匹配上周数据 #

通过merge实现SQL的自连接，精准匹配上周同一时间点的记录：

# 自连接：将原表与自身按"上周同一时间"匹配
merged_df = pd.merge(
    df,
    # 重命名原表的时间和销售额字段，用于匹配上周数据
    df[["transaction_time", "total_sale"]].rename(
        columns={"transaction_time": "prev_week_time", "total_sale": "prev_total_sale"}
    ),
    on="prev_week_time",
    how="left"  # 左连接保留所有当前时间记录，无匹配则为NaN
)

步骤3：计算周环比差异 #

根据需求计算绝对值差异或百分比差异：

# 计算周环比绝对值差异（与期望输出一致）
merged_df["wow_change"] = merged_df["total_sale"] - merged_df["prev_total_sale"]

# 如需百分比差异，可使用以下代码（对应SQL中的逻辑）
# merged_df["wow_percent_change"] = (merged_df["total_sale"] / merged_df["prev_total_sale"]) - 1

步骤4：整理输出结果 #

保留需要的字段并排序：

# 筛选并排序结果
result = merged_df[["transaction_time", "wow_change"]].sort_values("transaction_time")
print(result)

输出结果 #

transaction_time  wow_change
0 2024-01-05 00:00:00         NaN
1 2024-01-05 02:00:00         NaN
2 2024-01-12 00:00:00      -100.0
3 2024-01-12 01:00:00         NaN
4 2024-01-12 02:00:00       100.0
5 2024-01-19 00:00:00      1600.0
6 2024-01-19 01:00:00       1000.0

方案优势 #

• 不受数据缺失影响：通过时间精准匹配替代固定行偏移，即使有小时缺失也能正确找到上周对应数据
• 逻辑与SQL完全对齐：降低跨语言迁移的理解成本
• 灵活扩展：可轻松切换绝对值/百分比差异，或添加其他维度的匹配条件

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