如何在Pandas分组聚合中统计无事件的唯一用户数？

原数据与初始代码 #

定义DataFrame #

import pandas as pd

value = [["time1", "client1", None, "username1"], ["time2", "client1", "event1", "username1"], ["time3", "client1", None, "username2"], ["time4", "client2", None, "username3"], ["time5", "client2", "event2", "username4"], ["time6", "client3", None, "username5"]]
columns = ["timestamp", "clients", "events", "usernames"]
df = pd.DataFrame(value, columns=columns)

数据结构 #

timestamp  clients events  usernames
0     time1  client1   None  username1
1     time2  client1  event1  username1
2     time3  client1   None  username2
3     time4  client2   None  username3
4     time5  client2  event2  username4
5     time6  client3   None  username5

初始聚合代码 #

df.groupby("clients").agg(**{
    "total_records": ("timestamp", "size"),
    "unique_users": ("usernames", "nunique"),
    "without_events_records": ("events", lambda x: x.notnull().mean()),
    # "without_events_users":  add calculation without events per unique username
})

需求实现 #

根据业务场景的不同，提供两种without_events_users的统计方案：

场景1：统计至少有一条无事件记录的唯一用户名数量 #

只要用户在当前client分组中存在任意一条events为None的记录，就被计入统计。

修改后的聚合代码：

df.groupby("clients").agg(**{
    "total_records": ("timestamp", "size"),
    "unique_users": ("usernames", "nunique"),
    "without_events_records": ("events", lambda x: x.isnull().mean()),  # 修正原代码逻辑，统计无事件记录占比
    "without_events_users": ("usernames", lambda x: x[df.loc[x.index, "events"].isnull()].nunique())
})

运行结果：

total_records  unique_users  without_events_records  without_events_users
clients                                                                            
client1               3             2                0.666667                     2
client2               2             2                0.500000                     2
client3               1             1                1.000000                     1

场景2：统计所有记录均无事件的唯一用户名数量 #

仅当用户在当前client分组中的全部记录events都为None时，才被计入统计。

修改后的聚合代码：

df.groupby("clients").agg(**{
    "total_records": ("timestamp", "size"),
    "unique_users": ("usernames", "nunique"),
    "without_events_records": ("events", lambda x: x.isnull().mean()),
    "without_events_users": ("usernames", lambda x: x.groupby(x).filter(lambda y: df.loc[y.index, "events"].isnull().all()).nunique())
})

运行结果：

total_records  unique_users  without_events_records  without_events_users
clients                                                                            
client1               3             2                0.666667                     1
client2               2             2                0.500000                     1
client3               1             1                1.000000                     1

说明 #

• 原代码中without_events_records的逻辑有误，x.notnull().mean()统计的是有事件记录的占比，若需统计无事件记录占比，应改为x.isnull().mean()。
• 可根据实际业务需求选择对应场景的实现方式。

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