如何基于两列关联值数量对Pandas DataFrame进行子集筛选？

示例数据重建代码 #

import pandas as pd

df = pd.DataFrame({
    "": [0,1,2,3,4,5,6,7,8],
    "c1": ["ABC", "ABC", "dfg", "dfg", "dfg","dfg","ghj","ghj","ghj"], 
    "c2": ["delta", "delta", "alpha", "bravo", "alpha","bravo","bravo","delta","alpha"], 
    "c3": [1, 2, 2, 3, 5,6,3,3,3], 
    "col4": [786, 787, 777, 775, 767,715,772,712,712], 
    "col5": [10, 11, 13, 12, 13,12,14,12,12], 
    "col6": [1,2,4, 3, 4,3, 5, 8,8]
})

原始DataFrame #

c1     c2  c3  col4  col5  col6
0  ABC  delta   1   786    10     1
1  ABC  delta   2   787    11     2
2  dfg  alpha   2   777    13     4
3  dfg  bravo   3   775    12     3
4  dfg  alpha   5   767    13     4
5  dfg  bravo   6   715    12     3
6  ghj  bravo   3   772    14     5
7  ghj  delta   3   712    12     8
8  ghj  alpha   3   712    12     8

一、筛选c1每个值仅对应c2中2个不同值的行 #

通过groupby+transform计算每个c1分组下c2的唯一值数量，再用布尔索引筛选目标行：

# 计算每个c1分组对应的c2唯一值数量
unique_c2_count = df.groupby('c1')['c2'].transform('nunique')
# 筛选数量等于2的行
finaldf1 = df[unique_c2_count == 2]

筛选结果 #

c1     c2  c3  col4  col5  col6
2  dfg  alpha   2   777    13     4
3  dfg  bravo   3   775    12     3
4  dfg  alpha   5   767    13     4
5  dfg  bravo   6   715    12     3

二、筛选c1每个值对应c2中2个或3个不同值的行 #

沿用相同的分组计算逻辑，将筛选条件改为匹配2或3的数量范围：

# 计算每个c1分组对应的c2唯一值数量
unique_c2_count = df.groupby('c1')['c2'].transform('nunique')
# 筛选数量为2或3的行
finaldf2 = df[unique_c2_count.isin([2, 3])]

筛选结果 #

c1     c2  c3  col4  col5  col6
2  dfg  alpha   2   777    13     4
3  dfg  bravo   3   775    12     3
4  dfg  alpha   5   767    13     4
5  dfg  bravo   6   715    12     3
6  ghj  bravo   3   772    14     5
7  ghj  delta   3   712    12     8
8  ghj  alpha   3   712    12     8

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