How to assign group by sum results to new columns in Pandas

Question

Using sample data:

Calendar    SKU   Quantity
2017-10-01  1001     10 
2017-10-01  1002     20
2017-10-01  1003     30
2017-11-01  1001     40
2017-11-01  1002     50
2017-11-01  1003     60
2018-11-01  1001     70
2018-11-01  1002     80
2018-03-01  1001     90

I want to create two columns for sum of quantity from 2017 and 2018 without changing the current data structures as below:

Expected outcome:

 Calendar    SKU    Quantity  Year_2017_Quantity   Year_2018_Quantity
2017-10-01   1001      10           50                    160
2017-10-01   1002      20           70                     80  
2017-10-01   1003      30           90                      0
2017-11-01   1001      40           50                    160
2017-11-01   1002      50           70                     80
2017-11-01   1003      60           90                      0
2018-11-01   1001      70           50                    160
2018-11-01   1002      80           70                     80
2018-03-01   1001      90           50                    160

Both Year_2017_Quantity and Year_2018_Quantity columns are results of sum operation which is group by SKU and respective calendar year.

Thank you.

Answer 1

We do pivot here I am using crosstab then merge

s=pd.crosstab(df.SKU,df.Calendar.dt.year,df.Quantity,aggfunc='sum').fillna(0).add_prefix('Year_Quantity_').reset_index()
df=df.merge(s,how='left')
    Calendar   SKU  Quantity  Year_Quantity_2017  Year_Quantity_2018
0 2017-10-01  1001        10                50.0               160.0
1 2017-10-01  1002        20                70.0                80.0
2 2017-10-01  1003        30                90.0                 0.0
3 2017-11-01  1001        40                50.0               160.0
4 2017-11-01  1002        50                70.0                80.0
5 2017-11-01  1003        60                90.0                 0.0
6 2018-11-01  1001        70                50.0               160.0
7 2018-11-01  1002        80                70.0                80.0
8 2018-03-01  1001        90                50.0               160.0