Search code examples
pythonpandascountgroup-byunique

Count unique values that are grouped by in Python

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!

Solution

  • 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