如何使用pandas统计各机构独有的客户数量

实现思路 #

先筛选出仅在单一机构出现过的客户，再按机构统计这类客户的去重数量即可。

完整代码实现 #

import pandas as pd

# 构造测试DataFrame
data = {
    "month": ["01", "01", "01", "02", "03", "06", "04", "03", "04", "04"],
    "organization": ["ABC", "ABC", "ABC", "XYZ", "XYZ", "XYZ", "XYZ", "DEF", "DEF", "MNO"],
    "customer_id": ["001", "001", "002", "003", "002", "006", "007", "004", "005", "004"]
}
df = pd.DataFrame(data)

# 1. 统计每个客户关联的不同机构数量
cust_org_count = df.groupby("customer_id")["organization"].nunique().reset_index(name="org_count")

# 2. 筛选出仅在1家机构出现的独有客户
only_one_org_cust = cust_org_count[cust_org_count["org_count"] == 1]["customer_id"]

# 3. 按机构统计独有客户的去重数量
result = df[df["customer_id"].isin(only_one_org_cust)] \
    .drop_duplicates(["organization", "customer_id"]) \
    .groupby("organization", as_index=False)["customer_id"] \
    .count() \
    .rename(columns={"customer_id": "unique_customer_count"})

print(result)

输出结果 #

organization  unique_customer_count
0          ABC                      1
1          DEF                      1
2          XYZ                      3

和你给出的预期结果完全一致。如果需要保留无独有客户的机构（比如示例中的MNO，统计值为0），在分组后补充重索引逻辑即可。

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