Adding rows that are in the same quarter of dates-CodePudding

I am currently working on python to add rows quarter by quarter. The dataframe that I'm working with looks like below:

df = [['A','2021-03',1,9,17,25], ['A','2021-06',2,10,18,26], ['A','2021-09',3,11,19,27], ['A','2021-12',4,12,20,28],
         ['B','2021-03',5,13,21,29], ['B','2021-06',6,14,22,30], ['B','2022-03',7,15,23,31], ['B','2022-06',8,16,24,32]]
df_fin = pd.DataFrame(df, columns=['ID','Date','Value_1','Value_2','Value_3','Value_4'])

The Dataframe has 'ID', 'Date' column and three columns that are subjected for summation.

The 'Date' is in the form of 20XX-03, 20XX-06, 20XX-09, 20XX-12. Within the same 'ID' value, I want to add the rows to make it to biannual dates. In other words, I want to add March with June, and add September with December

The final df will look like below:

ID	Date	Value_1	Value_2	Value_3	Value_4
A	2021-06	3	19	35	51
A	2021-12	7	23	39	55
B	2021-06	11	26	42	59
B	2022-06	15	31	47	63

CodePudding user response：

you can use groupby

df_fin['temp'] = df_fin['Date'].replace({'-03': '-06', '-09':'-12'}, regex=True)
df_fin.groupby(['ID', 'temp']).sum().reset_index().rename(columns={'temp': 'Date'})

    ID  Date    Value_1 Value_2 Value_3 Value_4
0   A   2021-06 3   19  35  51
1   A   2021-12 7   23  39  55
2   B   2021-06 11  27  43  59
3   B   2022-12 15  31  47  63