如何基于近12个月数据计算Usage≥100的活跃用户？

现有代码原本统计的是唯一User ID出现的总月数，但实际需求是基于每个登录年月，计算该时间点往前推12个月内，Usage≥100的活跃用户数量。调整Usage和月份变量后仍存在统计偏差，以下是问题分析与修正方案。

原代码 #

import pandas as pd 
import numpy as ny
import sqldf as sqldf

#raw_data="data_python.xlsx"

df_raw = pd.read_excel(r"data_python.xlsx")

print (df_raw)

result = df_raw.groupby(["Usage","Month","Year"])["User ID"].nunique()

Active_Users = df_raw.groupby(["Usage","Month","Year"])["User ID"].apply(lambda x: frozenset(x.values))
Active_Users  = Active_Users.reset_index()

Active_Users  = pd.concat([Active_Users] + [Active_Users["Usage"].shift(i) for i in range(1, 12)], axis ="columns")
Active_Users.columns = ["User ID","Month","Year"] + [ f"shift_{i}" for i in range(12) ]

def count_unique(row):
    total_set = frozenset()
    columns = [ f"shift_{i}" for i in range(12) ]
    for col in columns:
        if row.get(col) and type(row.get(col)) == frozenset:
            total_set = total_set | row.get(col)
    return len(total_set)

Active_Users["T_Months_Usage"] = Active_Users.apply(count_unique, axis=1)
Active_Users

原代码核心问题 #

• 分组逻辑错误：按Usage分组会把不同Usage值的用户拆分开，而我们需要先过滤出Usage≥100的用户，再按年月统计，不需要把Usage作为分组维度。
• Shift对象错误：代码中shift的是Usage列，而非存储用户集合的列，导致滑动窗口拼接的不是用户数据。
• 列名重命名混乱：重置索引后原列名是Usage、Month、Year、User ID，但重命名后把用户集合列改成了User ID，后续逻辑完全错位。
• 年月未转为连续日期：直接用Month和Year作为分组，无法正确计算跨年度的12个月窗口（比如2023年1月到2022年2月是12个月，但直接按数字月份比较会出错）。

修正后的代码 #

import pandas as pd

# 读取数据
df_raw = pd.read_excel(r"data_python.xlsx")

# 1. 过滤出Usage≥100的记录
df_filtered = df_raw[df_raw["Usage"] >= 100].copy()

# 2. 将Year和Month合并为日期列，统一格式为每月第一天，方便时间窗口计算
df_filtered["login_date"] = pd.to_datetime(df_filtered[["Year", "Month"]].assign(Day=1))

# 3. 按月份分组，获取每个月的活跃用户集合
monthly_active = df_filtered.groupby("login_date")["User ID"].apply(frozenset).reset_index(name="active_users")

# 4. 按时间排序，确保滑动窗口顺序正确
monthly_active = monthly_active.sort_values("login_date").reset_index(drop=True)

# 5. 用滑动窗口统计近12个月的活跃用户：窗口大小12，取并集后计算数量
def count_rolling_active(window):
    # 合并窗口内所有用户集合
    all_users = frozenset().union(*window["active_users"])
    return len(all_users)

# 滑动窗口：每个行对应当前行及往前11行（共12个月）
monthly_active["rolling_12m_active_users"] = monthly_active.rolling(window=12, min_periods=1).apply(count_rolling_active, raw=False)

# 可选：拆分回Year和Month列
monthly_active["Year"] = monthly_active["login_date"].dt.year
monthly_active["Month"] = monthly_active["login_date"].dt.month

# 展示结果
print(monthly_active[["Year", "Month", "rolling_12m_active_users"]])

关键修正说明 #

• 先过滤再统计：直接筛选Usage≥100的记录，避免无关数据干扰。
• 统一日期格式：把Year+Month转为标准日期，确保跨年度的12个月窗口计算准确。
• 正确的滑动窗口：用pandas的rolling方法直接处理时间序列窗口，窗口内合并所有用户集合的并集，统计唯一用户数。
• 去除冗余分组：不再按Usage分组，聚焦于年月维度的活跃用户统计。

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