I am attempting to create a new column with the MAX Value from a Range of Months in the same dataframe

I would like the new column in 'Max Value' with max value from next 12 month (if month is 'Oct-20' start find max value in 'Nov-20') and fill '0' if the data has less than 4 month as below:

or there are another solution on mySQL?

I have tried to create 'Max Value' columns

max_v = 0
llist = []
max_values = []

for j in range(len(df_uid)):
    try:
        for i in range(len(df_uid)):
            val = (df_uid['DPD_BCK'].iloc[i 1])
            if val > max_v:
                max_v = val
                llist.append(max_v)
    except IndexError:
        pass
    max_values.append(max(llist))

Output:

max_values = [5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5]

but I want max_values = [5, 5, 5, 5, 5, 5, 5, 5, 0, 0, 0, 0]

CodePudding user response：

I assumed you are using a Pandas DataFrame called df_uid. This code loops through the DataFrame. If there are more than 4 items available it searches for the max value within the next 12 items, unless there are not as much available, then it finds it in the last available items.

Because in the table in the question it was not so clear to see, I added a random initialized list as 'DPD_BCK' items.

import numpy as np
import pandas as pd

np.random.seed(32)

df_uid = pd.DataFrame({'Month': 
                       ['Oct-20', 'Nov-20', 'Dec-20', 'Jan-21', 'Feb-21', 'Mar-21',
                        'Apr-21', 'May-21', 'June-21', 'July-21', 'Aug-21', 'Sep-21']})

df_uid['DPD_BCK'] = np.random.randint(0,20,size=len(df_uid))

max_values = []
for j in range(len(df_uid)):
    if j > len(df_uid)-4:
        max_values.append(0)
    else:
        try:
            max_values.append(max(df_uid['DPD_BCK'][j:j 12]))
        except ValueError:
            max_values.append(max(df_uid['DPD_BCK'][j:]))
df_uid['Max Value'] = max_values

df_uid