Best way to do rolling window operations on Pandas Dataframe outside of built in "rolling"?

user_13956296 0
Viewed 127

I don't think Pandas has a built in function for what I want to do. Let's say I have 2 dfs each with 1000 rows. I want to:

  1. Calculate the rank of the first 5 rows for column 0 of df1
  2. Calculate the rank of the first 5 rows for column 0 of df2
  3. Find the correlation of the two columns
  4. Put this value in a 3rd df

I would like to do this for all rolling windows of 5 and for all pairs of columns.

In my past, I have tried to build custom rolling windows, by doing for loops. I would create a smaller df, do the operation on it, then create another small df, do the operation it, etc... My code was EXTREMELY slow.

For example, I would have something like:

df11 = data
df22 = data    

for i in range(df.shape[0] - 4):
        for j in range(df.shape[1]):
            df1 = df11[i:i+5, j].rank()
            df2 = df22[i:i+5, j].rank()
            df3[i + 4, j] = corr(df1, df2)

I know the code above is not right syntax wise but you get the point.

But looping through the dataframe was extremely slow. And having 2 loops, would be even slower. I'd also have to keep track of the original dfs, and the two smaller dfs, through time. I wonder if there is a faster way...

rolling-computation pandas python-3.x python loops
1 Answers

If you want to correlate ranks over sets of 5 columns into a df, I see it as simple as below.

a = np.array(np.meshgrid([2018], [1,2,3,4,5,6], 
                     [f"Stock {i+1}" for i in range(600)],
                    )).reshape(3,-1)
a = [a[0], a[1], a[2], [round(random.uniform(-1,2.5),1) for e in a[0]]]
df1= pd.DataFrame({"Sharpe":a[3], "Year":a[0], "Month":a[1], "Stock":a[2], })

a = [a[0], a[1], a[2], [round(random.uniform(-1,2.5),1) for e in a[0]]]
df2= pd.DataFrame({"Sharpe":a[3], "Year":a[0], "Month":a[1], "Stock":a[2], })

df1["Sharpe"] = df1["Sharpe"].astype(float)
df2["Sharpe"] = df2["Sharpe"].astype(float)

df3 = pd.DataFrame(
    [[i, i+5, df1.iloc[i:i+5,0].rank().corr(df2.iloc[i:i+5,0].rank())] 
     for i in range(0, len(df1), 5)]
    ,columns=["start_row","end_row","corr"])

print(f"{df3[:10].to_string(index=False)}\n{df3[-10:].to_string(index=False)}")

output

 start_row  end_row      corr
         0        5  0.394737
         5       10  0.300000
        10       15  0.921053
        15       20  0.700000
        20       25 -0.410391
        25       30  0.105263
        30       35 -0.300000
        35       40  0.872082
        40       45  0.200000
        45       50  0.461690
 start_row  end_row      corr
      3550     3555 -0.153897
      3555     3560  0.718185
      3560     3565 -0.948683
      3565     3570  0.100000
      3570     3575 -0.820783
      3575     3580 -0.670820
      3580     3585 -0.131579
      3585     3590  0.102598
      3590     3595  0.872082
      3595     3600  0.872082

all columns

df3 = pd.DataFrame(
    {
        **{"start_row":[i for i in range(0, len(df1), 5)],
           "end_row":[i+5 for i in range(0, len(df1), 5)]
          },
        **{df1.columns[j]+"_corr":
             [df1.iloc[i:i+5,j].rank().corr(df2.iloc[i:i+5,j].rank()) 
                for i in range(0, len(df1), 5)]
                for j in range(len(df1.columns))
        }
    }
)
user_94414040
Related