How to aggregate unique count with pandas pivot_table

Question

This code:

df2 = (
    pd.DataFrame({
        'X' : ['X1', 'X1', 'X1', 'X1'], 
        'Y' : ['Y2', 'Y1', 'Y1', 'Y1'], 
        'Z' : ['Z3', 'Z1', 'Z1', 'Z2']
    })
)
g = df2.groupby('X')
pd.pivot_table(g, values='X', rows='Y', cols='Z', margins=False, aggfunc='count')

returns the following error:

Traceback (most recent call last): ... 
AttributeError: 'Index' object has no attribute 'index'

How do I get a Pivot Table with counts of unique values of one DataFrame column for two other columns?
Is there aggfunc for count unique? Should I be using np.bincount()?

NB. I am aware of pandas.Series.values_counts() however I need a pivot table.

---

EDIT: The output should be:

Z   Z1  Z2  Z3
Y             
Y1   1   1 NaN
Y2 NaN NaN   1

Mayweed

Answer 1

Do you mean something like this?

>>> df2.pivot_table(values='X', index='Y', columns='Z', aggfunc=lambda x: len(x.unique()))

Z   Z1  Z2  Z3
Y             
Y1   1   1 NaN
Y2 NaN NaN   1

Note that using len assumes you don't have NAs in your DataFrame. You can do x.value_counts().count() or len(x.dropna().unique()) otherwise.

Quit

Answer 2

This is a good way of counting entries within .pivot_table:

>>> df2.pivot_table(values='X', index=['Y','Z'], columns='X', aggfunc='count')

        X1  X2
Y   Z       
Y1  Z1   1   1
    Z2   1  NaN
Y2  Z3   1  NaN

Replacement

This does exactly what is required without an obscure lambda. – Jessejessee

Note: Pandas no longer accepts rows/cols as parameters. pandas.pydata.org/pandas-docs/stable/generated/… – Nata

@Jessejessee that's incorrect. Using 'count' will count all instances, not only unique ones. This reply does not answer the question. – Dola

Answer 3

Since at least version 0.16 of pandas, it does not take the parameter "rows"

As of 0.23, the solution would be:

df2.pivot_table(values='X', index='Y', columns='Z', aggfunc=pd.Series.nunique)

which returns:

Z    Z1   Z2   Z3
Y                
Y1  1.0  1.0  NaN
Y2  NaN  NaN  1.0

Plasia

Answer 4

aggfunc=pd.Series.nunique provides distinct count. Full code is following:

df2.pivot_table(values='X', rows='Y', cols='Z', aggfunc=pd.Series.nunique)

Credit to @hume for this solution (see comment under the accepted answer). Adding as an answer here for better discoverability.

Nicolenicolea

Answer 5

• The aggfunc parameter in pandas.DataFrame.pivot_table will take 'nunique' as a string, or in a list
  • pandas.Series.nunique or pandas.core.groupby.DataFrameGroupBy.nunique
• Tested in pandas 1.5.0

out = df2.pivot_table(values='X', index='Y', columns='Z', aggfunc=['nunique', 'count', lambda x: len(x.unique()), len])

[out]:
             nunique           count           <lambda>            len          
Z       Z1   Z2   Z3    Z1   Z2   Z3       Z1   Z2   Z3   Z1   Z2   Z3
Y                                                                     
Y1     1.0  1.0  NaN   2.0  1.0  NaN      1.0  1.0  NaN  2.0  1.0  NaN
Y2     NaN  NaN  1.0   NaN  NaN  1.0      NaN  NaN  1.0  NaN  NaN  1.0


out = df2.pivot_table(values='X', index='Y', columns='Z', aggfunc='nunique')

[out]:
Z    Z1   Z2   Z3
Y                
Y1  1.0  1.0  NaN
Y2  NaN  NaN  1.0

out = df2.pivot_table(values='X', index='Y', columns='Z', aggfunc=['nunique'])

[out]:
             nunique          
Z       Z1   Z2   Z3
Y                   
Y1     1.0  1.0  NaN
Y2     NaN  NaN  1.0

Claycomb

Answer 6

You can construct a pivot table for each distinct value of X. In this case,

for xval, xgroup in g:
    ptable = pd.pivot_table(xgroup, rows='Y', cols='Z', 
        margins=False, aggfunc=numpy.size)

will construct a pivot table for each value of X. You may want to index ptable using the xvalue. With this code, I get (for X1)

     X        
Z   Z1  Z2  Z3
Y             
Y1   2   1 NaN
Y2 NaN NaN   1

Ezekiel

Thank you. However I am not counting the number of occurrences of each distinct value of X, I am counting the number of distinct values in X for Y and Z. – Mayweed

Answer 7

Since none of the answers are up to date with the last version of Pandas, I am writing another solution for this problem:

import pandas as pd

# Set example
df2 = (
    pd.DataFrame({
        'X' : ['X1', 'X1', 'X1', 'X1'], 
        'Y' : ['Y2', 'Y1', 'Y1', 'Y1'], 
        'Z' : ['Z3', 'Z1', 'Z1', 'Z2']
    })
)

# Pivot
pd.crosstab(index=df2['Y'], columns=df2['Z'], values=df2['X'], aggfunc=pd.Series.nunique)

which returns:

Z   Z1  Z2  Z3
Y           
Y1  1.0 1.0 NaN
Y2  NaN NaN 1.0

Barstow

Actually, for count the frequency, pd.crosstab is preferable than pivot table. – Wray

Answer 8

aggfunc=pd.Series.nunique will only count unique values for a series - in this case count the unique values for a column. But this doesn't quite reflect as an alternative to aggfunc='count'

For simple counting, it better to use aggfunc=pd.Series.count

Whitethroat

Answer 9

For best performance I recommend doing DataFrame.drop_duplicates followed up aggfunc='count'.

Others are correct that aggfunc=pd.Series.nunique will work. This can be slow, however, if the number of index groups you have is large (>1000).

So instead of (to quote @Javier)

df2.pivot_table('X', 'Y', 'Z', aggfunc=pd.Series.nunique)

I suggest

df2.drop_duplicates(['X', 'Y', 'Z']).pivot_table('X', 'Y', 'Z', aggfunc='count')

This works because it guarantees that every subgroup (each combination of ('Y', 'Z')) will have unique (non-duplicate) values of 'X'.

Heterogynous

Answer 10

res = df2.pivot_table(index = 'Y', columns = 'Z', values = 'X', aggfunc = "nunique")
print(res)

the output is:

Z    Z1   Z2   Z3
Y                
Y1  1.0  1.0  NaN
Y2  NaN  NaN  1.0

This answer is almost the same as @Javier’s. The only difference is the use of the string name ‘nunique’ for aggfunc.

Moonstruck