Python Pandas : pivot table with aggfunc = count unique distinct

Do you mean something like this?

In [39]: df2.pivot_table(values='X', rows='Y', cols='Z', 
                         aggfunc=lambda x: len(x.unique()))
Out[39]: 
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.

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

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