Pandas summation of neighboring rows (rolling sum)

April 15, 2023

I would like to know is there any quick method to sum up the besides row of an dataframe. I can do that with python for loop but it’s slow, so I would like to know is there any method to do the same thing while running faster.

Here’s a code example of what I’m trying to do.

import pandas as pd

dictionary = {
    'In':[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]
}

df = pd.DataFrame(dictionary)

forecast = 1
temp = []
for i in range(len(df["1"])):
    temp = temp + [sum(df["In"][i - forecast : i + forecast + 1])]

df["Out"] = temp

df["Out"][:forecast] = None
df["Out"][-forecast:] = None

print(df)


#Output:
    In   Out
0    1   NaN
1    2   6.0     #(1+2+3)
2    3   9.0     #(2+3+4)
3    4  12.0      ...
4    5  15.0
5    6  18.0
6    7  21.0
7    8  24.0
8    9  27.0
9   10  30.0
10  11  33.0
11  12  36.0
12  13  39.0
13  14  42.0
14  15   NaN

>Solution :

Yes, there is a faster way to achieve the same result as your for loop by using the pandas rolling() method. The rolling() method can create a rolling window object that can then be used to compute functions over a rolling window of data.

Here’s an example of how you can modify your code to use the rolling() method instead of the for loop:

import pandas as pd

dictionary = {
    '1':[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]
}

df = pd.DataFrame(dictionary)

forecast = 1

# create a rolling window object with a window size of 2*forecast + 1
rolling_window = df["1"].rolling(window=2*forecast+1, center=True)

# compute the rolling sum over the rolling window object
df["2"] = rolling_window.sum()

# set NaN values at the beginning and end of the rolling window
df["2"][:forecast] = None
df["2"][-forecast:] = None

print(df)

This code produces the same output as your original code but runs faster by using the rolling() method. The rolling window object is created with a window size of 2*forecast+1 to ensure that the sum is computed over the correct number of values. The center=True argument centers the window on each value so that each value in the window has an equal weight in the sum.

Note that the rolling() method can also be used to compute other functions besides sum, such as mean, min, max, etc.