Creating a new DataFrame using the values from value_counts grouped by Date

Question

I have a dataframe which looks like:

df

Date        Month   Prediction
2020-04-22  Apr     A
2020-04-22  Apr     A
2020-06-12  Jun     B
2020-06-12  Jun     C
2020-06-17  Jun     C

Now, I need to create a dataframe which groups based on Date, counts the Prediction and have that values as a different column, something like below:

Date        Month   A   B   C
2020-04-22  Apr     2   0   0
2020-06-12  Jun     0   1   1
2020-06-17  Jun     0   0   1

I can group the values but not sure how to achieve the above.

df.groupby('Date')['Prediction'].value_counts()

Date        Prediction                                              
2020-04-22  A                  2
2020-06-12  B                  1
            C                  1
2020-06-12  C                  1

Answer 1

Use:

df = pd.crosstab([df['Date'], df['Month']], df.Prediction).reset_index().rename_axis(None, axis=1)
print (df)
            Date       Month  A  B  C
0           2020-04-22   Apr  2  0  0
1           2020-06-12   Jun  0  1  1
2           2020-06-17   Jun  0  0  1