如何基于两列值查找两个DataFrame的共同行并计算占比？

实现思路 #

• 指定需要匹配的目标列，将每行的列值转为元组，实现多列组合的匹配判断
• 筛选出两个DataFrame中满足匹配条件的行
• 计算共同行在原DataFrame中的占比

完整代码示例 #

import pandas as pd
data1 = {
    "first_column": ["id1", "id2", "id3"],
    "second_column": ["1", "2", "2"],
    "third_column": ["1", "2", "2"],
    "fourth_column": ["1", "2", "1"],
}
df1 = pd.DataFrame(data1)
data2 = {
    "first_column": ["id1", "id2", "id3", "id4"],
    "second_column": ["3", "4", "2", "2"],
    "third_column": ["1", "2", "2", "2"],
    "fourth_column": ["1", "2", "2", "2"],
}
df2 = pd.DataFrame(data2)

# 指定用于匹配的两列
match_cols = ["second_column", "third_column"]

# 生成两列值的元组集合，提升匹配效率
df1_match_tuples = set(df1[match_cols].apply(tuple, axis=1))
df2_match_tuples = set(df2[match_cols].apply(tuple, axis=1))

# 筛选df1中的共同行
df1_common = df1[df1[match_cols].apply(tuple, axis=1).isin(df2_match_tuples)]
# 筛选df2中的共同行
df2_common = df2[df2[match_cols].apply(tuple, axis=1).isin(df1_match_tuples)]

# 计算占比
df1_common_ratio = len(df1_common) / len(df1)
df2_common_ratio = len(df2_common) / len(df2)

# 输出结果
print("=== df1中的共同行 ===")
print(df1_common)
print(f"\ndf1共同行占比：{df1_common_ratio:.2%}")

print("\n=== df2中的共同行 ===")
print(df2_common)
print(f"\ndf2共同行占比：{df2_common_ratio:.2%}")

# 可选：合并两个DataFrame的共同行，保留双方所有列
merged_common = pd.merge(df1, df2, on=match_cols, suffixes=('_df1', '_df2'))
print("\n=== 合并后的共同行（含双方所有列） ===")
print(merged_common)

运行结果 #

=== df1中的共同行 ===
  first_column second_column third_column fourth_column
2          id3             2            2             1

df1共同行占比：33.33%

=== df2中的共同行 ===
  first_column second_column third_column fourth_column
2          id3             2            2             2
3          id4             2            2             2

df2共同行占比：50.00%

=== 合并后的共同行（含双方所有列） ===
  first_column_df1 second_column third_column fourth_column_df1 first_column_df2 fourth_column_df2
0              id3             2            2                 1              id3                 2
1              id3             2            2                 1              id4                 2

说明 #

• apply(tuple, axis=1)将每行的目标列转为元组，结合集合的isin方法实现高效的多列组合匹配
• 若需要保留两个DataFrame的全量列信息，使用pd.merge默认的inner连接即可得到合并后的共同行
• 占比计算直接用共同行数量除以原DataFrame总行数，格式化为百分比更直观

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