Pandas - group by id and drop duplicate with threshold

Question

I have the following data: ``` userid itemid 1 1 1 1 1 3 1 4 2 1 2 2 2 3 ``` I want to drop userIDs who has viewed the same itemID more than or equal to twice. For example, userid=1 has viewed itemid=1 twice, and thus I want to drop the entire record of userid=1. However, since userid=2 hasn't viewed the same item twice, I will leave userid=2 as it is. So I want my data to be like the following: ``` userid itemid 2 1 2 2 2 3 ``` Can someone help me? ``` import pandas as pd df = pd.DataFrame({'userid':[1,1,1,1, 2,2,2], 'itemid':[1,1,3,4, 1,2,3] }) ```

Accepted Answer

You can use `duplicated` to determine the row level duplicates, then perform a `groupby` on 'userid' to determine 'userid' level duplicates, then drop accordingly.

To drop without a threshold:

```
df = df[~df.duplicated(['userid', 'itemid']).groupby(df['userid']).transform('any')]
```

To drop with a threshold, use `keep=False` in `duplicated`, and sum over the Boolean column and compare against your threshold. For example, with a threshold of 3:

```
df = df[~df.duplicated(['userid', 'itemid'], keep=False).groupby(df['userid']).transform('sum').ge(3)]
```

The resulting output for no threshold:

```
   userid  itemid
4       2       1
5       2       2
6       2       3
```

Pandas - group by id and drop duplicate with threshold

Solution

Problem