Calculate column in Pandas Dataframe using adjacent rows without iterating through each row

Question

I would like to see if there is a way to calculate a column in a dataframe that uses something similar to a moving average without iterating through each row. Current working code:

def create_candles(ticks, instrument, time_slice):
    candlesticks = ticks.price.resample(time_slice, base=00).ohlc().bfill()
    volume = ticks.amount.resample(time_slice, base=00).sum()
    candlesticks['volume'] = volume
    candlesticks['instrument'] = instrument
    candlesticks['ttr'] = 0
    # candlesticks['vr_7'] = 0
    candlesticks['vr_10'] = 0
    candlesticks = calculate_indicators(candlesticks, instrument, time_slice)

    return candlesticks


def calculate_indicators(candlesticks, instrument):
    candlesticks.sort_index(inplace=True)
    # candlesticks['rsi_14'] = talib.RSI(candlesticks.close, timeperiod=14)
    candlesticks['lr_50'] = talib.LINEARREG(candlesticks.close, timeperiod=50)
    # candlesticks['lr_150'] = talib.LINEARREG(candlesticks.close, timeperiod=150)
    # candlesticks['ema_55'] = talib.EMA(candlesticks.close, timeperiod=55)
    # candlesticks['ema_28'] = talib.EMA(candlesticks.close, timeperiod=28)
    # candlesticks['ema_18'] = talib.EMA(candlesticks.close, timeperiod=18)
    # candlesticks['ema_9'] = talib.EMA(candlesticks.close, timeperiod=9)
    # candlesticks['wma_21'] = talib.WMA(candlesticks.close, timeperiod=21)
    # candlesticks['wma_12'] = talib.WMA(candlesticks.close, timeperiod=12)
    # candlesticks['wma_11'] = talib.WMA(candlesticks.close, timeperiod=11)
    # candlesticks['wma_5'] = talib.WMA(candlesticks.close, timeperiod=5)
    candlesticks['cmo_9'] = talib.CMO(candlesticks.close, timeperiod=9)

    for row in candlesticks.itertuples():
        current_index = candlesticks.index.get_loc(row.Index)
        if current_index >= 1:
            previous_close = candlesticks.iloc[current_index - 1, candlesticks.columns.get_loc('close')]
            candlesticks.iloc[current_index, candlesticks.columns.get_loc('ttr')] = max(
                row.high - row.low,
                abs(row.high - previous_close),
                abs(row.low - previous_close))

        if current_index > 10:
            candlesticks.iloc[current_index, candlesticks.columns.get_loc('vr_10')] = candlesticks.iloc[current_index, candlesticks.columns.get_loc('ttr')] / (
                max(candlesticks.high[current_index - 9: current_index].max(), candlesticks.close[current_index - 11]) -
                min(candlesticks.low[current_index - 9: current_index].min(), candlesticks.close[current_index - 11]))

    candlesticks['timestamp'] = pd.to_datetime(candlesticks.index)
    candlesticks['instrument'] = instrument
    candlesticks.fillna(0, inplace=True)
    return candlesticks

in the iteration, i am calculating the True Range ('TTR') and then the Volatility Ratio ('VR_10')

TTR is calculated on every row in the DF except for the first one. It uses the previous row's close column, and the current row's high and low column.

VR_10 is calculated on every row except for the first 10. it uses the high and low column of the previous 9 rows and the close of the 10th row back.

EDIT 2 I have tried many ways to add a text based data frame in this question, there just doesnt seem to be a solution with the width of my frame. there is no difference in the input and output dataframes other than the column TTR and VR_10 have all 0s in the input, and have non-zero values in the output. an example would be this dataframe:

Is there a way I can do this without iteration?

Answer 1

With the nudge from Andreas to use rolling, I came to an answer: first, I had to find out how to use rolling with multiple columns. found that here. I made a modification because I need to roll up, not down

def roll(df, w, **kwargs):
    df.sort_values(by='timestamp', ascending=0, inplace=True)
    v = df.values
    d0, d1 = v.shape
    s0, s1 = v.strides

    a = stride(v, (d0 - (w - 1), w, d1), (s0, s0, s1))

    rolled_df = pd.concat({
        row: pd.DataFrame(values, columns=df.columns)
        for row, values in zip(df.index, a)
    })

    return rolled_df.groupby(level=0, **kwargs)

after that, I created 2 functions:

def calculate_vr(window):
    return window.iloc[0].ttr / (max(window.high[1:9].max(), window.iloc[10].close) - min(window.low[1:9].min(), window.iloc[10].close))


def calculate_ttr(window):
    return max(window.iloc[0].high - window.iloc[0].low, abs(window.iloc[0].high - window.iloc[1].close), abs(window.iloc[0].low - window.iloc[1].close))

and called those functions like this:

    candlesticks['ttr'] = roll(candlesticks, 3).apply(calculate_ttr)
    candlesticks['vr_10'] = roll(candlesticks, 11).apply(calculate_vr)

added timers to both ways and this way is roughly 3X slower than iteration.