I have a dataframe that I am trying to do some calculations on and add a few columns.

Here is an example of the input dataframe:

df1:

Index    Type    Product    Late or On Time
0         A       X              On Time
1         B       Y              Late
2         C       Y              On Time
3         C       X              On Time
4         C       X              Late
5         B       X              Late
6         A       Y              Late
7         C       Y              On Time
8         B       Y              Late
9         B       X              On Time

I am trying to create a new dataframe that has 3 new columns and gets rid of the “Late or On Time” column. The first new column will be a Product/Type combo count that counts how many times each product/type combo appears in the dataframe. The second column will count how many of each product/type combo are late, and the third column will count how many are on time. Here is an example of the output I am going for:

Index    Type    Product     Product/Type Count    Late Count    On Time Count
0         A       X              1                     0                1
1         A       Y              1                     1                0
2         B       X              2                     1                1
3         B       Y              2                     2                0
4         C       X              2                     1                1
5         C       Y              2                     0                2

I am able to get the Product/Type Count using this line of code, but I do not know how to get the other two columns:

df2 = df1.groupby(['Type', 'Product']).size().reset_index().rename(columns={0: 'Product/Type Count'})