create pivot table in pandas using count of occurrence of value [duplicate]

Question

I'm attempting to create cross-table / pivot table in pandas:

import pandas as pd
import numpy as np

df = pd.DataFrame({'foo': ['one', 'one', 'one', 'two', 'two','two'],'bar': ['A', 'A', 'C', 'B', 'B', 'C']})

however my attempts come up as:

pd.pivot(df, index='foo', columns='bar')

TypeError: pivot_simple() got multiple values for argument 'index'

and the second:

pd.pivot_table(data = df, values = 'bar', index = 'bar', columns = 'foo', aggfunc=lambda x: np.count_nonzero(x))

foo one two
bar     
A   4.0 NaN
B   NaN 4.0
C   2.0 2.0

The output I'm looking to achieve is the below:

        A   B   C
one     2       1
two        2    1

Answer 1

Switch the column and index and use len as aggfunc:

df.pivot_table(index='foo', columns='bar', values='foo', aggfunc=len, fill_value=0)

#bar  A  B  C
#foo         
#one  2  0  1
#two  0  2  1

Or just use pd.crosstab:

pd.crosstab(df.foo, df.bar)

#bar  A  B  C
#foo         
#one  2  0  1
#two  0  2  1