python之删除出现次数超过 N 次的重复值

我有一个数据框，在“lid”列中有重复值。我想使用 Pandas 删除其在“lid”列中的值被计数超过 2 次的行。 这是原始表格:

entity  pnb head#   state   lid 
ABB001  A03 3   DOWN    A 
ABB001  A03 3   DOWN    A 
ABB001  A03 3   DOWN    A 
ABB002  A02 4   DOWN    B 
ABB002  A02 4   DOWN    B 
ABB002  A02 2   DOWN    C 
ABB002  A02 4   DOWN    D 
ABB002  A02 4   DOWN    E 
ABB002  A02 4   DOWN    E 
ABB002  A02 4   DOWN    E 

结果如下:

entity  pnb head#   state   lid 
ABB002  A02 4   DOWN    B 
ABB002  A02 4   DOWN    B 
ABB002  A02 2   DOWN    C 
ABB002  A02 4   DOWN    D 

请您参考如下方法:

选项 0
使用 value_counts 和 isin

df[~df.lid.isin(df.lid.value_counts().loc[lambda x: x > 2].index)] 
 
   entity  pnb  head# state lid 
3  ABB002  A02      4  DOWN   B 
4  ABB002  A02      4  DOWN   B 
5  ABB002  A02      2  DOWN   C 
6  ABB002  A02      4  DOWN   D 

---

选项 1
最好用 np.in1d 和 pd.factorize

实现

lids = df.lid.values 
f, u = pd.factorize(df.lid.values) 
df[np.in1d(lids, u[np.bincount(f) <= 2])] 
 
   entity  pnb  head# state lid 
3  ABB002  A02      4  DOWN   B 
4  ABB002  A02      4  DOWN   B 
5  ABB002  A02      2  DOWN   C 
6  ABB002  A02      4  DOWN   D 

---

选项 2
使用 np.bincount 和 pd.factorize

f, u = pd.factorize(df.lid) 
df[np.bincount(f)[f] <= 2] 
 
   entity  pnb  head# state lid 
3  ABB002  A02      4  DOWN   B 
4  ABB002  A02      4  DOWN   B 
5  ABB002  A02      2  DOWN   C 
6  ABB002  A02      4  DOWN   D 

---

为了有趣的演示，突出@cᴏʟᴅsᴘᴇᴇᴅ 和我在评论中谈论的内容。

Love the bincount one. There should be a np.unique one too, somewhere. – cᴏʟᴅsᴘᴇᴇᴅ

Yes there is. However, I don't use np.unique because @Jeff informed me that np.unique sorts when you grab counts or index or inverse. pd.factorize does not and is O(n). I've since validated that information. – piRSquared

时间测试

def bincount_factorize(df): 
    f, u = pd.factorize(df.lid.values) 
    return df[np.bincount(f)[f] <= 2] 
 
def bincount_unique(df): 
    u, f = np.unique(df.lid.values, return_inverse=True) 
    return df[np.bincount(f)[f] <= 2] 
 
def in1d_factorize(df): 
    lids = df.lid.values 
    f, u = pd.factorize(df.lid.values) 
    return df[np.in1d(lids, u[np.bincount(f) <= 2])] 
 
def transform(df): 
    return df[df.groupby('lid')['lid'].transform('size') <= 2] 
 
res = pd.DataFrame( 
    index=[10, 30, 100, 300, 1000, 3000, 10000, 
           30000, 100000, 300000, 1000000], 
    columns=['bincount_factorize', 'bincount_unique', 
             'in1d_factorize', 'transform'], 
    dtype=float 
) 
 
for i in res.index: 
    d = pd.concat([df] * i, ignore_index=True) 
    for j in res.columns: 
        stmt = f'{j}(d)' 
        setp = f'from __main__ import d, {j}' 
        res.at[i, j] = timeit(stmt, setp, number=100) 
 
res.div(res.min(1), 0) 
 
         bincount_factorize  bincount_unique  in1d_factorize  transform 
10                 1.421827         1.000000        1.119577   3.751167 
30                 1.008412         1.037297        1.000000   3.072631 
100                1.000000         1.531300        1.028267   3.304560 
300                1.000000         2.666583        1.182812   3.637235 
1000               1.065213         5.563098        1.000000   2.556469 
3000               1.024658        10.480027        1.000000   2.238765 
10000              1.073403        14.716801        1.000000   1.574780 
30000              1.000000        16.387130        1.053180   1.494161 
100000             1.000000        18.533078        1.003031   1.369867 
300000             1.078129        20.183122        1.000000   1.530698 
1000000            1.166800        24.571463        1.000000   1.670423 

---

res.plot(loglog=True)