Q:

Pivoting data pandas

I'm trying to pivot this data:
         ID
UserID
1        a1
1        a2
2        a1
2        a3

into a dataframe like:
UserID   a1   a2   a3
1        1    1    0
2        1    0    1

I've tried doing the following df = pd.pivot_table(df, index='UserID', columns='ID', but it gives me a DataError: No numeric types to aggregate error. What can I do?

A:

First column is index, so is necessary change index='UserID' to index=df.index.
Also aggregate function is GroupBy.size
df = pd.pivot_table(df, index=df.index, columns=df['ID'], aggfunc='size', fill_value=0)
print (df)
ID      a1  a2  a3
UserID            
1        1   1   0
2        1   0   1

Solution with crosstab:
df = pd.crosstab(df.index,df['ID'])
print (df)
ID     a1  a2  a3
row_0            
1       1   1   0
2       1   0   1

Or (pandas 0.20.1+) solution - groupby by index and column together, aggregate size and reshape by unstack:
df = df.groupby(['UserID','ID']).size().unstack(fill_value=0)
print (df)
ID      a1  a2  a3
UserID            
1        1   1   0
2        1   0   1

pandas bellow 0.20.1 solution - convert index to column by reset_index:
df = df.reset_index().groupby(['UserID','ID']).size().unstack(fill_value=0)
print (df)
ID      a1  a2  a3
UserID            
1        1   1   0
2        1   0   1

EDIT:
It seems index can be selected by index name too (not sure if it works bellow 0.20.1):
df = pd.pivot_table(df, index='UserID', columns='ID', aggfunc='size', fill_value=0)
print (df)
ID      a1  a2  a3
UserID            
1        1   1   0
2        1   0   1

