Find duplicates in pandas with condition (Python)

Question

I would like to get the number of duplicates for article_id for each merchant_id, where the zip_code is not identical. Please see example below:

Table

merchant_id     article_id   zip_code 
1               4555         1000
1               4555         1003
1               4555         1002
1               3029         1000
2               7539         1005
2               7539         1005
2               7539         1002
2               1232         1006
3               5555         1000
3               5555         1001
3               5555         1002
3               5555         1003

Output Table

merchant_id     count_duplicate
1                3
2                2
3                4

So far I was able to return all duplicate rows - see code below:

df[df.duplicated('product_id',keep=False)==True

Erfan · Accepted Answer · 2020-05-18 15:36:14Z

4

We can use groupby with nunique and then filter (query) only the groups > 1. nunique stands for "number of unique values".

dfn = (
    df.groupby(['merchant_id', 'article_id'])['zip_code'].nunique()
    .reset_index(name='count_duplicate')
    .query('count_duplicate > 1')
)

   merchant_id  article_id  count_duplicate
1            1        4555                3
3            2        7539                2
4            3        5555                4

answered May 18, 2020 at 15:36

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user12625679 Over a year ago

Thanks it works! Do you have an idea how to count duplicates where the zip_code is identical. I.e. counting for each zip_code and merchant_id the number of product_id duplicates?

Erfan Over a year ago

Glad it works. Regarding your second question, I see that as a new question, and this one as answered. Feel free to post a new question and I will have look at it! Btw dont forget to accept this one if it helped you :)

Collectives™ on Stack Overflow

Find duplicates in pandas with condition (Python)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related