如何快速计算pandas DataFrame不同网站子集的访客ID重叠量

你原有方案的核心问题是每次计算一组组合都要重新遍历全表筛选、做 merge，1600组组合就要重复1600次全表扫描和大数据量合并，耗时必然很高。下面提供两种高效实现思路：

方案1：稀疏矩阵一次性计算全量组合（最优） #

只需要扫描1次全表，通过矩阵运算一次性得到所有两两组合的重叠数，效率最高，适合组合量大的场景：

import pandas as pd
import scipy.sparse as sp

# 第一步：先去重，剔除同一用户访问同一网站的重复记录，大幅降低数据量
df_unique = df.drop_duplicates(["project_name", "ID"]).reset_index(drop=True)

# 第二步：将字符串类型的ID、网站名转成整数编码，降低内存占用、提升运算效率
df_unique["user_code"] = df_unique["ID"].astype("category").cat.codes
df_unique["site_code"] = df_unique["project_name"].astype("category").cat.codes

# 第三步：构建稀疏用户-网站访问矩阵（行对应用户，列对应网站，值为1表示该用户访问过对应网站）
user_count = df_unique["user_code"].nunique()
site_count = df_unique["site_code"].nunique()
access_mat = sp.csr_matrix(
    ([1] * len(df_unique), (df_unique["user_code"], df_unique["site_code"])),
    shape=(user_count, site_count)
)

# 第四步：矩阵转置后相乘，直接得到所有两两网站的共同访客数，结果[i][j]就是第i个和第j个网站的重叠用户数
overlap_mat = access_mat.T.dot(access_mat)

# 第五步：把结果映射回网站名，直接查询即可
site_names = df_unique["project_name"].astype("category").cat.categories.tolist()
overlap_df = pd.DataFrame(overlap_mat.todense(), index=site_names, columns=site_names)

# 示例：查询Website A和Website B的重叠用户数
both = overlap_df.loc["Website A", "Website B"]
# 单个网站总访客数直接取对角值即可
one_total = overlap_df.loc["Website A", "Website A"]
two_total = overlap_df.loc["Website B", "Website B"]
# 计算重叠比例
both_pc = round(both / (one_total + two_total - both), 4)

方案2：预存用户集合，按需计算（实现最简单） #

如果组合计算不需要一次性出全量结果，只想按需查询指定组合，用这个方案实现最简便：

# 一次性分组，把每个网站的用户ID存为集合，后续查询直接用集合交集计算
site_user_map = df.drop_duplicates(["project_name", "ID"]).groupby("project_name")["ID"].agg(set).to_dict()

# 示例：计算Website A和Website B的重叠数
both = len(site_user_map["Website A"] & site_user_map["Website B"])
one_total = len(site_user_map["Website A"])
two_total = len(site_user_map["Website B"])
both_pc = round(both / (one_total + two_total - both), 4)

优化注意事项 #

• 优先做数据去重：同一用户访问同一网站的重复记录对统计无意义，提前去重可以降低30%~70%的数据量
• 字符串ID优先转整数编码：不管是集合运算还是矩阵运算，整数的运算效率和内存占用都远优于字符串
• 所有统计的前置计算（分组、矩阵构建）都只需要做一次，不需要每次计算组合都重复执行

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