Search code examples
pandasrowshiftrolling-computation

calculate value based on other column values with some step for rows of other columns

total beginner here. If my question is irrelevant, apologies in advance, I'll remove it. So, I have a question : using pandas, I want to calculate an evolution ratio for a week data compared with the previous rolling 4 weeks mean data.

df['rolling_mean_fourweeks'] = df.rolling(4).mean().round(decimals=1)

shape of my df random data

from here I wanna create a new column for the evolution ratio based on the week data compared with the row of the rolling mean at the previous week. what is the best way to go here? (I don't have big data) I have tried unsuccessfully with .shift() but am very foreign to .shift()... I should get NAN for week 3 (fourth week) and ~47% for fifth week. Any suggestion for retrieving the value at row with step -1?

Thanks and have a good day!

Solution

  • Your idea about using shift can perfectly work. The shift(x) function simply shifts a series (a full column in your case) of x steps.

    A simple way to check if the rolling_mean_fourweeks is a good predictor can be to shift Column1 and then check how it differs from rolling_mean_fourweeks:

    df['column1_shifted'] = df['Column1'].shift(-1)
df['rolling_accuracy'] = ((df['column1_shifted']-df['rolling_mean_fourweeks'])
                            /df['rolling_mean_fourweeks'])

    resulting in:

    enter image description here