Count unique value for each group and subtotal

November 16, 2023

I have a simple dataframe as below:

import pandas as pd
import numpy as np
df = pd.DataFrame({'BR_NM': ['HN', 'HN', 'HP'], 
                   'CUS_ID': ['12345', '12345', '12345'],
                   'ACC_ID': ['12345_1', '12345_2', '12345_3'],
                   'REGION': ['North', 'North', 'North'], 
                   'CUS_TYPE': ['Individual', 'Individual', 'Individual']})
df
BR_NM   CUS_ID  ACC_ID  REGION  CUS_TYPE
HN      12345   12345_1 North   Individual
HN      12345   12345_2 North   Individual
HP      12345   12345_3 North   Individual

I want to count unique CUS_ID based on BR_NM then sum it based on REGION. In my case, it’s just one customer with three account but I want to count it as two customer. Below is my desired Ouput:

REGION  CUS_TYPE    North
0      Individual   2

If I used pivot_table and aggfunc = pd.Series.nunique it just count as 1.

df2 = pd.pivot_table(df, values='CUS_ID', columns='REGION', index='CUS_TYPE', aggfunc=pd.Series.nunique).reset_index()

Thank you.

>Solution :

it’s just one customer with three account but I want to count it as two customer.

Use drop_duplicates based on BR_NM and CUS_ID columns to get unique records:

>>> (df.drop_duplicates(['BR_NM', 'CUS_ID'])
       .value_counts(['REGION', 'CUS_TYPE'])
       .unstack('REGION').reset_index())

REGION    CUS_TYPE  North
0       Individual      2

About drop_duplicates:

>>> df.drop_duplicates(['BR_NM', 'CUS_ID'])

  BR_NM CUS_ID   ACC_ID REGION    CUS_TYPE
0    HN  12345  12345_1  North  Individual
2    HP  12345  12345_3  North  Individual