How do I perform rolling division with several columns in Pandas?

Question

I'm having problems with pd.rolling() method that returns several outputs even though the function returns a single value.

My objective is to:

Calculate the absolute percentage difference between two DataFrames with 3 columns in each df.
Sum all values

I can do this using pd.iterrows(). But working with larger datasets makes this method ineffective.

This is the test data im working with:

#import libraries
import pandas as pd
import numpy as np 

#create two dataframes
values = {'column1': [7,2,3,1,3,2,5,3,2,4,6,8,1,3,7,3,7,2,6,3,8],
        'column2': [1,5,2,4,1,5,5,3,1,5,3,5,8,1,6,4,2,3,9,1,4],
        "column3" : [3,6,3,9,7,1,2,3,7,5,4,1,4,2,9,6,5,1,4,1,3]
        }

df1 = pd.DataFrame(values)
df2 = pd.DataFrame([[2,3,4],[3,4,1],[3,6,1]])
print(df1)
print(df2)

    column1  column2  column3
0         7        1        3
1         2        5        6
2         3        2        3
3         1        4        9
4         3        1        7
5         2        5        1
6         5        5        2
7         3        3        3
8         2        1        7
9         4        5        5
10        6        3        4
11        8        5        1
12        1        8        4
13        3        1        2
14        7        6        9
15        3        4        6
16        7        2        5
17        2        3        1
18        6        9        4
19        3        1        1
20        8        4        3
   0  1  2
0  2  3  4
1  3  4  1
2  3  6  1

This method produces the output I want by using pd.iterrows()

RunningSum = []
for index, rows in df1.iterrows():
    if index > 3:
        Div = abs((((df2 / df1.iloc[index-3+1:index+1].reset_index(drop="True").values)-1)*100))
        Average = Div.sum(axis=0)
        SumOfAverages = np.sum(Average)
        RunningSum.append(SumOfAverages)
        
        
            
        #printing my desired output values
        print(RunningSum)
[991.2698412698413,
 636.2698412698412,
 456.19047619047626,
 616.6666666666667,
 935.7142857142858,
 627.3809523809524,
 592.8571428571429,
 350.8333333333333,
 449.1666666666667,
 1290.0,
 658.531746031746,
 646.031746031746,
 597.4603174603175,
 478.80952380952385,
 383.0952380952381,
 980.5555555555555,
 612.5]

Finally, below is my attemt to use pd.rolling() so that I dont need to loop through each row.

def SumOfAverageFunction(vals):
    Div = abs((((df2.values / vals.reset_index(drop="True").values)-1)*100))
    Average = Div.sum()
    SumOfAverages = np.sum(Average)
    return SumOfAverages

RunningSums = df1.rolling(window=3,axis=0).apply(SumOfAverageFunction)

Here is my problem because printing RunningSums from above outputs several values and is not close to the results I'm getting using iterrows method. How do I solve this?

print(RunningSums)

        column1      column2      column3
0           NaN          NaN          NaN
1           NaN          NaN          NaN
2    702.380952   780.000000   283.333333
3    533.333333   640.000000   533.333333
4   1200.000000   475.000000   403.174603
5    833.333333  1280.000000   625.396825
6    563.333333   760.000000  1385.714286
7    346.666667   386.666667  1016.666667
8    473.333333   573.333333   447.619048
9    533.333333  1213.333333   327.619048
10   375.000000   746.666667   415.714286
11   408.333333   453.333333   515.000000
12   604.166667   338.333333  1250.000000
13  1366.666667   577.500000   775.000000
14   847.619048  1400.000000   683.333333
15   314.285714   733.333333   455.555556
16   533.333333   441.666667   474.444444
17   347.619048   616.666667   546.666667
18   735.714286   466.666667  1290.000000
19   350.000000   488.888889   875.000000
20   525.000000  1361.111111  1266.666667

user_4718350 · Answer

It's just the way rolling behaves, it's going to window around all of the columns and I don't know that there is a way around it. One solution is to apply rolling to a single column, and use the indexes from those windows to slice the dataframe inside your function. Still expensive, but probably not as bad as what you're doing.

Also the output of your first method looks wrong. You're actually starting your calculations a few rows too late.

import numpy as np

def SumOfAverageFunction(vals):
    return (abs(np.divide(df2.values, df1.loc[vals.index].values)-1)*100).sum()

vals = df1.column1.rolling(3)
vals.apply(SumOfAverageFunction, raw=False)

How do I perform rolling division with several columns in Pandas?

1 Answers