Friday 26 August 2022

Apply func on dataframe columns that require values from previous rows and previous column

I need to apply a function on several rows of my table. The tricky part is that this function will use values of the previous row and also the value of the previous column.

In this example, the three columns that start with 'perf' are the ones that need to be updated with calcPerf function. So col "perf 60-40" will use the On/Off value in col "60-40" and "perf 30-30" the will use those of "30-30".

In reality I have 3000 columns to be updated instead of 3 in this example, so the idea would be to apply the function on the different columns dynamically.

The very first row should remain 0 by default as calcPerf function would not work on the first row.

For a better understanding I have two highlighted examples (blue and green). This shows what cells are used by the calcPerf function when on a specific cell.


import pandas as pd
df = {
    'open': ['4001','4010','4043','3924','4000'],
    'close': ['4002','4030','3901','3970','4009'],
    '50-20': ['On','On','On','On','On'],
    'perf 50-20':[0,0,0,0,0],
    '60-40': ['Off','Off','On','On','On'],
    'perf 60-40':[0,0,0,0,0],
    '30-30': ['On','Off','On','On','Off'],
    'perf 30-30':[0,0,0,0,0]
}
df = pd.DataFrame(df)
df

def calcPerf(currClose, prevClose, currOnOff,prevOnOff, lastCloseWhenOn, prevPerf, currOpen):
    if(currOnOff == "On" and prevOnOff =='On'):
        return ((currClose/prevClose)-1)*100
    if(currOnOff == "On" and prevOnOff =='Off'):
        return (( currOpen / lastCloseWhenOn )-1)*100
    if(currOnOff == "Off" and prevOnOff =='On'):
        return (( currOpen / prevClose )-1)*100
    if(currOnOff == "Off" and prevOnOff =='Off'):
        return 0

Below is the expected output along with the two examples mentioned above (blue and green)

enter image description here



from Apply func on dataframe columns that require values from previous rows and previous column

No comments:

Post a Comment