Python Pandas: pivot table with aggfunc = count unique distinct

Question

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

    X   Y Z

0  X1 Y2  Z3

1  X1 Y1  Z1

2  X1 Y1  Z1

3  X1 Y1  Z2

g=df2.groupby('X')

pd.pivot_table(g, values='X', rows='Y', cols='Z', margins=False, aggfunc='count')

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 'Series' values_counts() however I need a pivot table.

The output should be:

Z   Z1 Z2  Z3

Y             

Y1   1 1 NaN

Y2 NaN NaN   1

Answer 1

 As of 0.23 version of Pandas, 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

If you wish to learn what is python and want to know the python applications then visit this Data Science with Python Course.

For more information, kindly refer to our Python Certification course.