["python","pandas","pivot-table"]
How to aggregate unique count with pandas pivot_table

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
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.