Grouping by multiple columns to find duplicate rows pandas

Question

I have a df

id    val1     val2
 1     1.1      2.2
 1     1.1      2.2
 2     2.1      5.5
 3     8.8      6.2
 4     1.1      2.2
 5     8.8      6.2

I want to group by val1 and val2 and get similar dataframe only with rows which has multiple occurrence of same val1 and val2 combination.

Final df:

id    val1     val2
 1     1.1      2.2
 4     1.1      2.2
 3     8.8      6.2
 5     8.8      6.2

score 67 · Accepted Answer · answered Oct 09 '17 at 07:33

67

You need duplicated with parameter subset for specify columns for check with keep=False for all duplicates for mask and filter by boolean indexing:

df = df[df.duplicated(subset=['val1','val2'], keep=False)]
print (df)
   id  val1  val2
0   1   1.1   2.2
1   1   1.1   2.2
3   3   8.8   6.2
4   4   1.1   2.2
5   5   8.8   6.2

Detail:

print (df.duplicated(subset=['val1','val2'], keep=False))
0     True
1     True
2    False
3     True
4     True
5     True
dtype: bool

answered Oct 09 '17 at 07:33

jezrael

822,522
95
1,334
1,252

How can I find group these? I need to group ids [0,1,4] as they have same data. – Venkata Gogu Jun 25 '18 at 15:24
@VenkataGogu - Use `df.groupby('val1')` – jezrael Jun 25 '18 at 15:25
@jezrael do you mean to `groupby` before or after the `duplicated`? Can you please add this to your answer? – KLaz Jul 02 '20 at 13:08
2

@KLaz - Do you need `df.groupby(['val1','val2'])['id'].agg(list).reset_index(name='new')` ? – jezrael Jul 02 '20 at 13:11
@Klaz @VenkataGogu I'd directly use this: ``` df = df[df['val1', 'val2'].duplicated() == True].reset_index(drop=True) ``` if I want to create a dataframe having them grouped by – Anna Christiane Kolandjian Feb 03 '23 at 07:56

score 0 · Answer 2 · answered Mar 18 '23 at 06:25

0

Another method is to compute the size of groups and only keep the rows whose group is larger than 1.

msk = df.groupby(['val1', 'val2'])['val1'].transform('size') > 1
df1 = df[msk]

answered Mar 18 '23 at 06:25

cottontail

10,268
18
50
51

Grouping by multiple columns to find duplicate rows pandas

2 Answers2

Linked

Related