How to change a dataframe to count the variables

Question

I have a dataframe like this:

df = pd.DataFrame({"X":['a', 'b', 'c', 'b', 'b', 'a'],
                   "Y":['A', 'B', 'A', 'C', 'A', 'A']})

Which method can I use to count the similar values and change it like this:

   a  b  c
A  2 1 1  
B  0 1 0
C  0 1 0

Answer 1

May be you can try crosstab (documentation):

pd.crosstab(df.Y, df.X)

Result:

X  a  b  c
Y         
A  2  1  1
B  0  1  0
C  0  1  0

Answer 2

You can use pivot_table method, the first param is the data source, I would think columns parameter, index parameter and fill_value parameter don't need explanation however, I want to clarify something aggfunc parameter: 'size' will include nan values, if you set this parameter as 'count', then only will count no-nan values. You can read more about that in this answer.

pd.pivot_table(df, columns='X', index='Y', fill_value=0, aggfunc='size')

Output:

X  a  b  c
Y         
A  2  1  1
B  0  1  0
C  0  1  0