Home > database >  Using Python for loop to take rolling sum product
Using Python for loop to take rolling sum product

Time:01-02

I am trying to use a for loop to calculate the trailing sum product of a list of values, and a reverse counter. That is, at each iteration of the loop, the current value would be multiplied by 1, the previous value would be multiplied by 2, and so on back to n values, where n is the number of values between the start of the list and the current iteration.

The below screenshot shows how I would do this in Excel using SUMPRODUCT:

SUMPRODUCT in Excel

In Python, I am able to use cumsum from Numpy to return the rolling cumulative sum of the list of values:

import numpy as np
import pandas as pd

data = [8,6,7,5,9]
df=pd.DataFrame(data=data,columns=['values'])
values=df['values']

test=[]
for x, index in enumerate(values):
    test.append(np.cumsum(values)[x])

I am also able to use the below to get a reversed counter, but I'm not sure how to incorporate this into the first for loop, and I'm not sure how to get the reverse counter to reset to 1 at each iteration:

for i in reversed(range(len(values))):
    print(i 1)

What is the most straightforward way to get the counter to reset at each iteration of the loop, and to incorporate it into a trailing sumproduct?

Thank you in advance.

CodePudding user response:

As @JonClements suggested you may need simple

(df['values'] * range(len(df), 0, -1)).sum()

without any for-loop

if you need partial result then you can get part of rows ie. sub_df = df[:3]

sub_df = df[:3]
(sub_df['values'] * range(len(sub_df), 0, -1)).sum()

Minimal working example

import pandas as pd

data = [8, 6, 7, 5, 9]

df = pd.DataFrame(data, columns=['values'])

size = 3
sub_df = df[:size]
#result = (sub_df['values'] * range(len(sub_df), 0, -1)).sum()
result = (sub_df['values'] * range(size, 0, -1)).sum()
print(f'result [{size}]: {result}')

size = 4
sub_df = df[:size]
#result = (sub_df['values'] * range(len(sub_df), 0, -1)).sum()
result = (sub_df['values'] * range(size, 0, -1)).sum()
print(f'result [{size}]: {result}')

Result:

result [3]: 43
result [4]: 69

EDIT:

If you want to put all in DataFrame

import pandas as pd

data = [8, 6, 7, 5, 9]

df = pd.DataFrame(data, columns=['values'])

# ---

size = 3
sub_df = df[:size]
result = (sub_df['values'] * range(size, 0, -1)).sum()
print(f'result [{size}]: {result}')

df['counter C'] = ''  # default value
df.loc[:size-1, 'counter C'] = range(size, 0, -1)
df['result C'] = ''  # default value
df.loc[size-1,'result C'] = result
   
# ---

size = 4
sub_df = df[:size]
result = (sub_df['values'] * range(size, 0, -1)).sum()
print(f'result [{size}]: {result}')

df['counter D'] = ''  # default value
df.loc[:size-1, 'counter D'] = range(size, 0, -1)
df['result D'] = ''  # default value
df.loc[size-1, 'result D'] = result

print(df)

Result:

   values counter C result C counter D result D
0       8         3                  4         
1       6         2                  3         
2       7         1       43         2         
3       5                            1       69
4       9  
  • Related