Appending to a dataframe by moving the grouped month by -1

Question

I want to have an output as shown above. The code loops through the unique months and then assigns a value to the age and drops the most recent month. For eg first it goes from 2021-06 to 2021-05, and the aging of 2021-05 starts from 1 instead of 2.It takes 2021-05 and 2021-04 and adds it original dataframe. and so on

Answer 1

While the original dataframe is not empty, remove the most recent month, and using only the month (df.Month.dt.month) from the remaining dataframe adjust the Age column. Append the modified dataframe into a list, and proceed to the remaining rows of the dataframe. After the loop is complete, use pd.concat to create a single dataframe with the desired number of rows and Age values.

import pandas as pd

df = pd.read_csv('sample.csv')
df['Month'] = pd.to_datetime(df['Month'])

group = df.groupby(df['Month'].dt.month)

df_list = [df]
while not df.empty:
    df = df.drop(group.get_group(df.Month.dt.month.max()).index)
    df.Age = df.Age - 1
    df_list.append(df)

df_out = pd.concat(df_list)
print(df_out)

Output from df_out

       Month  Country Product  Age
0 2021-06-01  Germany     abx  1.0
1 2021-06-01   France     khk  1.0
2 2021-05-01   Lisbon      iu  2.0
3 2021-05-01    Paris    kguy  2.0
4 2021-05-01  Germany     kjb  2.0
5 2021-04-01    China    jhvu  3.0
6 2021-04-01    Hawai     hju  3.0
2 2021-05-01   Lisbon      iu  1.0
3 2021-05-01    Paris    kguy  1.0
4 2021-05-01  Germany     kjb  1.0
5 2021-04-01    China    jhvu  2.0
6 2021-04-01    Hawai     hju  2.0
5 2021-04-01    China    jhvu  1.0
6 2021-04-01    Hawai     hju  1.0