Add a value from another column based on the last negative value found for the current row-CodePudding

I have the following Dataframe:

data = {'Column A':[400, 522, 633, 744, 150, 140, 119, 744, 150, 400, 390, 315, 744, 150, 400, 522, 500, 633, 744, 204, 215, 150],
    'Column B':[400, 0, 0, 0, -150, 0, 0, 744, 0, -400, 0, 0, -744, 0, -400, 0, 0, -633, 0, 0, 0, 150]}
df = pd.DataFrame(data)

What I want to do is tell the program to go row by row and compare the absolute value of the last negative value in Column B with the Column A value for the current row and if it is 20% lower copy it to Column B. This is how the Dataframe with the modified column B would look like:

data = {'Column A':[400, 522, 633, 744, 150, 140, 119, 744, 150, 400, 390, 315, 744, 150, 400, 522, 500, 633, 744, 204, 215, 150],
    'Column B':[400, 0, 0, 0, -150, 0, 119, 744, 0, -400, 0, 315, -744, 150, -400, 0, 0, -633, 0, 204, 215, 150],
    }

So for example in the 6th row the last negative value in column B is -150 so its absolute will be 150 when you compare this value with the value of 140 in column A, then the value is not copied since 140 is still inside the 20% threshold, however in the 7th row 119 is more than 20% lower than 150 so is copied to column B. On the 10th row the last negative becomes -400 so it will begin using this value to compare, then again skips 390 but copies 315.

I hope it's clear enough and thanks in advance for your answers.

CodePudding user response：

Try:

df['New Column B'] = np.where(df['Column B'].ge(0), np.nan, df['Column B'])
df['New Column B'].ffill(inplace=True)
condition = df['Column A'] < abs(df['New Column B'] - (df['New Column B']*0.2))
df['New Column B'].mask(condition, df['Column A'], inplace=True)
condition = df['Column B'].ne(df['New Column B']) & df['New Column B'].ne(df['Column A'])
df['New Column B'].mask(condition, df['Column B'], inplace=True)
df['New Column B'] = df['New Column B'].astype(int)

CodePudding user response：

You can try something like that :

last_value = 0
for i, n in df.iterrows():
   if n["Column B"] < 0:
       last_value = abs(n["Column B"])
   if n["Column A"] < last_value*0.8:
       df.loc[df.index == i, 'Column B'] = n["Column A"]