Count unique values that are grouped by in Python

Question

I am using a python database and am using pandas. Currently my database shows something like this:

Employer        Account_Num
AAA             123
BBB             456
AAA             789
AAA             123
BBB             101
CCC             112

I am able to put it into a table that counts all the Account_Num, which looks like this:

Employer   Account_Num
AAA        3
BBB        2
CCC        1

I used this code to achieve the above:

bigdata.groupby(['Employer'])[['Account_Num']].count()

But I only need the unique Account_Num's counted. Which should look something like this:

Employer   Account_Num
AAA        2
BBB        2
CCC        1

What is the best way I can achieve this? Thank you!

miradulo · Accepted Answer · 2017-03-04 01:35:01Z

3

You're looking for nunique().

df.groupby('Employer').Account_Num.nunique()

Demo

>>> df.groupby('Employer').Account_Num.nunique()

Employer
AAA    2
BBB    2
CCC    1
Name: Account_Num, dtype: int64

answered Mar 4, 2017 at 1:25

miradulo

29.8k7 gold badges86 silver badges97 bronze badges

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

1 Answer 1