How to aggregate unique count with pandas pivot_table

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.