如何用Pandas计算Excel文件中用户消费的同比变化？

我有一份Excel文件，每行代表一个月份，每列为一个user_ID，单元格内容为该用户当月的消费金额。文件每月更新数次，有时上月数据（如2024年6月）要到次月月底（如7月底）才能获取。需求是：

• 用Pandas计算所有用户总消费的同比变化，需排除上年同期无消费或当期无数据的用户（例如计算2024年5月同比时仅包含用户2、3、4、5，计算第二季度同比时仅包含用户2、3、5）
• 评估将数据导入PostgreSQL（用psycopg2）处理是否更简便

Pandas计算同比变化的最优方法 #

核心思路是先把宽格式数据转成窄格式，再匹配上年同期数据，筛选有效用户后计算总消费同比，具体步骤如下：

1. 数据读取与格式规整
   读取Excel后，将日期列转为datetime类型，确保后续日期处理准确；同时把以user_ID为列的宽表，转成以用户、月份为维度的窄表，方便后续聚合操作。

2. 关联上年同期数据
   为每条记录生成上年同期的日期标识，通过合并操作把当期和上年同期的消费数据关联到同一行，便于对比。

3. 筛选有效用户
   过滤掉上年同期消费为0/空，或当期无消费数据的用户，保证同比计算的样本有效性。

4. 计算总消费同比
   按月度/季度分组，分别算出当期和上年同期的总消费，再计算同比增长率：同比增长率 = (当期总消费 - 上年总消费) / 上年总消费 * 100%

关于导入PostgreSQL处理的可行性 #

是否更简便要看具体场景：

• 如果数据量小、仅需单次或少量分析，Pandas足够灵活高效，不用额外搭建数据库，成本更低。
• 如果数据量大、需要频繁更新维护、多用户协作，或需复杂的多表关联/历史数据查询，导入PostgreSQL更合适：
  • 数据库可持久化存储，增量更新数据更方便；
  • SQL的窗口函数、分组聚合语法能轻松实现同比计算；
  • 支持并发访问和复杂查询逻辑，适合长期数据管理。

修正后的代码示例 #

结合你的数据格式，以下是可直接运行的实现代码：

import pandas as pd

def calculate_yoy(path):
    # 1. 读取Excel数据，调整列名（假设第一列为日期，其余为user_ID）
    df = pd.read_excel(path, skiprows=53, nrows=140, usecols="M:CCL")
    # 重命名第一列为日期列
    df.rename(columns={df.columns[0]: "month"}, inplace=True)
    # 将日期转为datetime类型
    df["month"] = pd.to_datetime(df["month"], format="%Y-%m-%d")
    
    # 2. 宽表转长表：user_ID作为列，消费金额作为值
    df_long = df.melt(
        id_vars=["month"],
        var_name="user_id",
        value_name="spend"
    )
    # 处理空值：空的消费金额设为0（可根据实际情况调整）
    df_long["spend"] = df_long["spend"].fillna(0)
    
    # 3. 生成上年同期的月份标识，用于合并
    df_long["prev_year_month"] = df_long["month"] - pd.DateOffset(years=1)
    
    # 4. 合并当期与上年同期数据
    df_yoy = pd.merge(
        df_long,
        df_long.rename(columns={"month": "prev_year_month", "spend": "prev_spend"}),
        on=["user_id", "prev_year_month"],
        how="inner"
    )
    
    # 5. 筛选有效用户：当期消费>0 且 上年同期消费>0
    df_valid = df_yoy[(df_yoy["spend"] > 0) & (df_yoy["prev_spend"] > 0)]
    
    # 6. 计算月度总消费同比
    monthly_yoy = df_valid.groupby("month").agg(
        current_total=("spend", "sum"),
        prev_total=("prev_spend", "sum")
    ).assign(
        yoy_growth=lambda x: (x["current_total"] - x["prev_total"]) / x["prev_total"] * 100
    )
    
    # 计算季度总消费同比（按季度分组）
    df_valid["quarter"] = df_valid["month"].dt.to_period("Q")
    quarterly_yoy = df_valid.groupby("quarter").agg(
        current_total=("spend", "sum"),
        prev_total=("prev_spend", "sum")
    ).assign(
        yoy_growth=lambda x: (x["current_total"] - x["prev_total"]) / x["prev_total"] * 100
    )
    
    return monthly_yoy, quarterly_yoy

# 调用示例
monthly_result, quarterly_result = calculate_yoy("your_excel_path.xlsx")
print("月度同比结果：")
print(monthly_result)
print("\n季度同比结果：")
print(quarterly_result)

代码说明 #

• 用melt实现宽表转长表，简化后续分组和合并操作；
• 通过pd.DateOffset快速生成上年同期日期，避免手动拼接字符串；
• 合并后筛选有效用户，确保同比计算的样本一致性；
• 同时支持月度和季度同比计算，可根据需求调整分组维度。

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