Perform date transformation before multple groupbys in Pandas-CodePudding

I wish to groupby date country and type and convert any date that has same month value to the first of that month.

Data

country     type    date        energy  
US          aa      8/5/2022    10  
US          aa      8/25/2022   1   
US          aa      8/2/2022    11  
US          bb      8/5/2022    55  
US          bb      8/15/2022   25  
AUSTRALIA   bb      9/15/2022   5

Desired

step 1: convert all dates to 1st of the month values
step 2: groupby country type and date sum energy 

        


country     type    date         energy 
US          aa      8/1/2022    22  
US          bb      8/1/2022    80  
AUSTRALIA   bb      9/1/2022    5

Doing

df.date - pd.offsets.MonthBegin(1)
df.groupby(['country','type','date'], as_index=False).agg({'energy': sum})

Any suggestion is appreciated.

CodePudding user response：

Try this using pd.Grouper:

df.groupby(['country', 
            'type', 
            pd.Grouper(key='date', freq='MS')], sort=False)['energy'].sum().reset_index()

Output:

     country type       date  energy
0         US   aa 2022-08-01      22
1         US   bb 2022-08-01      80
2  AUSTRALIA   bb 2022-09-01       5