conditional while loop to sum subsequent rows of data based on column values in Python

N.B. The following will work only if each month per unique Code value has only one associated date. E.g. it won't work, if you could have both '01/30/2021' and '01/31/2021' for 123.

Code

import pandas as pd

data = {'Dates': {0: '01/31/2021', 1: '03/31/2021', 2: '09/30/2021',
                  3: '12/31/2021', 4: '02/28/2021', 5: '05/31/2021', 
                  6: '09/30/2021'},
        'Value': {0: 1, 1: 2, 2: 3, 3: 4, 4: 1, 5: 2, 6: 5},
        'Code': {0: 123, 1: 123, 2: 123, 3: 123, 4: 345, 5: 345, 6: 345}}

df = pd.DataFrame(data)

# turn col `Dates` into datetime
df['Dates'] = pd.to_datetime(df['Dates'])

# set `Dates` as index
df.set_index('Dates', drop=True, inplace=True)

def roll(x):
    # flip x (a `pd.Series` for col `Value`), apply rolling, and sum
    # window == 279D (i.e. 9*31)
    # min_periods == 1 (actually, the default)
    return x[::-1].rolling('279D', min_periods=1).sum()

# first turn all dates into `EoM`, groupby `Code`, select col `Value`
# call func `roll` inside `transform` to ensure result respects 
# order of orig index; copy only `to_numpy`, since index will not match,
# (but it will be the same order)
df['Value_rolling_9months'] = df.to_period('M').to_timestamp('M')\
    .groupby('Code')['Value'].transform(roll).to_numpy()

print(df)

            Value  Code  Value_rolling_9months
Dates                                         
2021-01-31      1   123                    6.0
2021-03-31      2   123                    9.0
2021-09-30      3   123                    7.0
2021-12-31      4   123                    4.0
2021-02-28      1   345                    8.0
2021-05-31      2   345                    7.0
2021-09-30      5   345                    5.0

If you 100% sure that your dates are always already EoM (as in your example), then you could simply use:

df['Value_rolling_9months'] = df.groupby('Code')['Value'].transform(roll)

Simplified example

We are flipping the frame for each group in df.Code. E.g.:

print(df.loc[df.Code == 345, 'Value'][::-1])

Dates
2021-09-30    5
2021-05-31    2
2021-02-28    1
Name: Value, dtype: int64

Now, we want to apply .rolling with a window of "9 months"; unfortunately, if you try to roll on one or more months, you will receive an error. E.g.

df.loc[df.Code == 345, 'Value'][::-1].rolling('9M')
# ValueError: <9 * MonthEnds> is a non-fixed frequency

For the reason, see this SO post. To circumvent this issue, we can roll by dates. Since we have only EoM values, we can do this safely; there will always be a considerable gap between two consecutive dates.

To see what is actually happening with rolling, we can list the procedure. E.g.:

list(df.loc[df.Code == 345, 'Value'][::-1].rolling('279D', min_periods=1))

[Dates
 2021-09-30    5
 Name: Value, dtype: int64,
 Dates
 2021-09-30    5
 2021-05-31    2
 Name: Value, dtype: int64,
 Dates
 2021-09-30    5
 2021-05-31    2
 2021-02-28    1
 Name: Value, dtype: int64]

For each of these windows, we are asking the sum. The parameter min_periods=1 means that we allow summing at a minimum of 1 observation. E.g., suppose we would set it to 2, then we would get a NaN value returned for the first window.

So, here the sum will get us 5, 7, 8. Note that these values would be in the incorrect order, we actually need 8, 7, 5. This is where DataFrameGroupBy.transform comes in: it ensures that the result is returned in the same order as the original index.

Here's one idea: Basically, reindex each group into monthly intervals (fill the extra rows with 0s), compute the rolling sum with a window of 10 months, then merge the result with the original dataframe so only the desired rows remain.

import pandas as pd
from pandas import Timestamp

# setup
df = pd.DataFrame({'Dates': [Timestamp('2021-01-31 00:00:00'),
                             Timestamp('2021-03-31 00:00:00'),
                             Timestamp('2021-09-30 00:00:00'),
                             Timestamp('2021-12-31 00:00:00'),
                             Timestamp('2021-02-28 00:00:00'),
                             Timestamp('2021-05-31 00:00:00'),
                             Timestamp('2021-09-30 00:00:00')],
                             'Value': [1, 2, 3, 4, 1, 2, 5],
                             'Code': [123, 123, 123, 123, 345, 345, 345]})

def get_rolling_sum(frame):
    
    # set the index to monthly values and fill NaNs with 0s
    frame = frame.reindex(
        pd.date_range(frame.index.min(), frame.index.max(), freq="M")
    ).fillna(0)

    # compute the rolling sum of 10-month-long intervals on the reverse
    return frame[::-1].rolling(window=10, min_periods=1).sum()

df_tmp = (df
          .set_index("Dates") # set Dates as index for the reindexing
          .groupby("Code")
          .Value.apply(get_rolling_sum)
          .reset_index() # turn the Dates and Code index into regular columns again
          .rename(columns={"level_1": "Dates", "Value": "Value9M"})
         )

Finally, merge the result with the original dataframe:

>>> df.merge(df_tmp, on=["Dates", "Code"])
       Dates  Value  Code  Value9M
0 2021-01-31      1   123      6.0
1 2021-03-31      2   123      9.0
2 2021-09-30      3   123      7.0
3 2021-12-31      4   123      4.0
4 2021-02-28      1   345      8.0
5 2021-05-31      2   345      7.0
6 2021-09-30      5   345      5.0