如何使用Pandas筛选同一年连续登录≥3周的用户

现有如下用户表：

USERID  Week_Number  Year
0       fb          5.0  2021
1  twitter          1.0  2021
2  twitter          2.0  2021
3  twitter          3.0  2021
4  twitter          1.0  2022
5  twitter          2.0  2022
6  twitter          3.0  2022
7  twitter         15.0  2022
8  twitter          NaN   NaN
9    human         21.0  2022

需求是找出同一年中连续登录≥3周的用户（每周编号在每年内唯一），期望输出：

USERID        Year
twitter       2021
twitter       2022

可通过以下代码创建示例表：

import pandas as pd
import numpy as np

data = pd.DataFrame({"USERID": ["fb", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "human"],
                     "Week_Number": [5, 1, 2, 3, 1, 2, 3, 15, np.nan, 21],
                     "Year": ["2021", "2021","2021","2021", "2022", "2022", "2022", "2022", np.nan, "2022"]})

尝试的代码片段（未得到正确结果）：

for ix, group in data.groupby([data.USERID, data.Year]):
    group = group.sort_values("Week_Number")
    group["Diff"] = (group.Week_Number - group.Week_Number.shift(1)).fillna(1)
    break

解决方案 #

这里提供两种实现方式，都能精准筛选出符合要求的用户：

方式一：分步清晰版 #

适合理解每一步逻辑：

import pandas as pd
import numpy as np

# 创建示例数据
data = pd.DataFrame({"USERID": ["fb", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "human"],
                     "Week_Number": [5, 1, 2, 3, 1, 2, 3, 15, np.nan, 21],
                     "Year": ["2021", "2021","2021","2021", "2022", "2022", "2022", "2022", np.nan, "2022"]})

# 第一步：清理无效数据，去掉Week_Number或Year为空的行
clean_df = data.dropna(subset=["Week_Number", "Year"]).copy()
clean_df["Week_Number"] = clean_df["Week_Number"].astype(int)  # 转成整数方便计算

# 第二步：按用户+年份分组，每组内按周数排序
grouped = clean_df.groupby(["USERID", "Year"])["Week_Number"].apply(sorted).reset_index()

# 第三步：判断每组是否存在≥3周的连续登录
def check_consecutive(weeks_list):
    if len(weeks_list) <3:
        return False
    # 计算相邻周的差值
    diffs = np.diff(weeks_list)
    current_streak =1
    max_streak =1
    for d in diffs:
        if d ==1:
            current_streak +=1
            max_streak = max(max_streak, current_streak)
            if max_streak >=3:
                return True
        else:
            current_streak =1
    return max_streak >=3

# 筛选符合条件的记录
result = grouped[grouped["Week_Number"].apply(check_consecutive)][["USERID", "Year"]]
print(result)

方式二：Pandas链式操作版 #

更简洁高效，适合日常使用：

import pandas as pd
import numpy as np

data = pd.DataFrame({"USERID": ["fb", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "twitter", "human"],
                     "Week_Number": [5, 1, 2, 3, 1, 2, 3, 15, np.nan, 21],
                     "Year": ["2021", "2021","2021","2021", "2022", "2022", "2022", "2022", np.nan, "2022"]})

result = (
    data.dropna(subset=["Week_Number", "Year"])
    .assign(Week_Number=lambda x: x["Week_Number"].astype(int))
    .sort_values(["USERID", "Year", "Week_Number"])
    .groupby(["USERID", "Year"])
    .apply(
        lambda g: (g["Week_Number"].diff() != 1).cumsum()  # 标记非连续的位置，生成分组ID
        .value_counts()  # 统计每个连续分组的长度
        .max() >=3  # 判断是否有长度≥3的分组
    )
    .reset_index(name="is_qualified")
    .query("is_qualified")
    .drop("is_qualified", axis=1)
)

print(result)

两种方式运行后都会输出：

USERID  Year
1  twitter  2021
2  twitter  2022

关键逻辑说明：

• 先清理空值，避免无效数据干扰计算
• 按用户和年份分组后，通过计算相邻周的差值判断连续性
• 统计连续登录的最长周期，筛选出周期≥3的用户-年份组合

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