Pandas data matrix for counting

Question

This should be obvious, but I could not find an easy solution.

I have a pandas DataFrame like this:

actual | predicted ------ + --------- Apple | Apple Apple | Apple Apple | Banana Banana | Orange Orange | Apple 

I want it:

  | Apple | Banana | Orange ------ + ------- + ------- + ------- Apple | 2 | 1 | 0 Banana | 0 | 0 | 1 Orange | 1 | 0 | 0 

Answer 1

You can use groupby with aggregation size and unstack MultiIndex :

 df = df.groupby(['actual','predicted']).size().unstack(fill_value=0) print (df) predicted Apple Banana Orange actual Apple 2 1 0 Banana 0 0 1 Orange 1 0 0 

Another solution with crosstab :

 df = pd.crosstab(df.actual, df.predicted) print (df) predicted Apple Banana Orange actual Apple 2 1 0 Banana 0 0 1 Orange 1 0 0 

Answer 2

You can use pandas.pivot_table

 >>> df.pivot_table(index='actual', columns='predicted', aggfunc=len).fillna(0).astype('int') predicted Apple Banana Orange actual Apple 2 1 0 Banana 0 0 1 Orange 1 0 0 

Answer 3

A little shot in the dark, but I think you're looking for confusion

 from sklearn.metrics import confusion_matrix print confusion_matrix(df['actual'], df['predicted'])