Python Pandas : pivot table with aggfunc = count unique distinct
PythonPandasPivot TablePython Problem Overview
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
Python Solutions
Solution 1 - Python
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 NA
s in your DataFrame. You can do x.value_counts().count()
or len(x.dropna().unique())
otherwise.
Solution 2 - Python
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
Solution 3 - Python
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
Solution 4 - Python
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.
Solution 5 - Python
- The
aggfunc
parameter inpandas.DataFrame.pivot_table
will take'nunique'
as astring
, or in alist
- Tested in
pandas 1.3.1
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
Solution 6 - Python
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
Solution 7 - Python
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
Solution 8 - Python
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
Solution 9 - Python
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'
.