Pandas DataFrame groupBy and then COUNT DISTINCT

Code description

This code snippet shows you how to group a pandas DataFrame  via certain column and then do a distinct count of unique values in another column.

It is similar as COUNT DISTINCT aggregation functions in SQL. It also sort the values by the group by column.

Example output:

       category  value  user  group-count
    0         A      0     5            7
    80        A     80     4            7
    70        A     70     7            7
    60        A     60    10            7
    50        A     50     9            7
    ..      ...    ...   ...          ...
    29        J     29     9            7
    19        J     19     9            7
    9         J      9     9            7
    89        J     89     8            7
    99        J     99     7            7
    
    [100 rows x 4 columns]

Code snippet

    import pandas as pd
    import random
    
    categories = []
    users = []
    values = []
    for i in range(0, 100):
        categories.append(chr(i % 10+65))
        values.append(i)
        users.append(random.randint(1, 10))
    
    df = pd.DataFrame({'category': categories, 'value': values, 'user': users})
    print(df)
    
    df['group-count'] = df.groupby(by='category')['user'].transform('nunique')
    
    print(df.sort_values(by=['category']))