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

user_12814680 0
Viewed 120

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

pandas dataframe python
3 Answers

If you want a vectorial solution, you can use numpy.select to handle your various conditions. Using a function you can apply it to each column (in a vectorial way):

# ensure numeric values
df['open'] = pd.to_numeric(df['open'])
df['close'] = pd.to_numeric(df['close'])

def calcPerf(s):
    m1 = s.eq('On')
    m2 = s.eq('On').shift()
    m3 = s.eq('Off').shift()

    return np.select([m1&m2, m1&m3, m2&~m1],
                     [df['close'].div(df['close'].shift()).sub(1).mul(100),
                      df['open'].div(df['close'].shift().where(m2).ffill()).sub(1).mul(100),
                      df['open'].div(df['close'].shift()).sub(1).mul(100)
                      ], 0
                      )

# apply to each column and update
df.update(df[['50-20', '60-40', '30-30']].apply(calcPerf).add_prefix('perf '))
df

output:

   open  close 50-20  perf 50-20 60-40  perf 60-40 30-30  perf 30-30
0  4001   4002    On    0.000000   Off    0.000000    On    0.000000
1  4010   4030    On    0.699650   Off    0.000000   Off    0.199900
2  4043   3901    On   -3.200993    On    0.000000    On    1.024488
3  3924   3970    On    1.768777    On    1.768777    On    1.768777
4  4000   4009    On    0.982368    On    0.982368   Off    0.755668
user_163434640

try this frst we use the shift function to compare each value with the previous one:

list_shifted_column=["close","50-20","60-40","30-30"]
for column in list_shifted_column:
    df["prev"+column]=df[column].shift(1)

the we apply this function:

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

on each column:

target_columns=['50-20','60-40','30-30']
for name in target_columns:
    df["perf "+name]=df.apply(lambda x:calcPerf(x["close"],x["prev_close"],x[column],x["prev_"+column],x["open"]),axis=1)
user_183173910

I would do it in two steps:

  1. First remove any inter-row dependencies. Use shift method as other mentioned to create a separate column with 4 possible values: on2on, on2off, off2on, off2off. Similarly, add columns with lastCloseWhenOn etc. If there is a recurring pattern for multiple columns, you can use apply with axis=1 to accomplish it.

  2. Then simply apply function with the logic in calcPerf along rows. In the function you can again use apply along columns.

Cosmetic remark: in python you rather write last_close_when_on than lastCloseWhenOn.

user_87889600
Related