Pandas get total of unique counts on pivot_table

user_6413199 0
Viewed 105

I have the following dataframe:-

data = {
    "code": ["A1", "A1", "A2", "A1"],
    "pid" : [1, 2, 2, 1],
    "reg" : ["ac", "ao", "ao", "ao"]
}
df = pd.DataFrame(data)

By doing a pivot on the above DataFrame like this:-

piv = df.pivot_table(index="reg", columns="code", values="pid", aggfunc=pd.Series.nunique)

I get the following Output:-

code    A1  A2
reg     
ac      1.0 NaN
ao      2.0 1.0

To this DataFrame I want to add a column Unique_Total which will be like:-

code    A1  A2   unique_total
reg     
ac      1.0 NaN  1
ao      2.0 1.0  2

The unique_total of ao = 2 because it has only 2 unique values that are 2 & 1

pandas python
1 Answers

Add margins and margins_name and then remove last row by indexing:

piv = df.pivot_table(index="reg", 
                     columns="code", 
                     values="pid", 
                     aggfunc=pd.Series.nunique, 
                     margins=True, 
                     margins_name='unique_total').iloc[:-1]
print (piv)
code   A1   A2  unique_total
reg                         
ac    1.0  NaN             1
ao    2.0  1.0             2
user_29010020
Related