suppose i have a dataframe
df = pd.DataFrame({"age" : [0, 5, 10, 15, 20], "income": [5, 13, 23, 18, 12]})
age income 0 0 5 1 5 13 2 10 23 3 15 18 4 20 12
i want to iterate through df["income"] and calculate the sumproduct as follows (example for age 15): 18+23*(15-10)+13*(15-5)+5*(15-0) = 338.
more generic: income[3] + income[2] * ( age[3] - age[2] ) + income[1] * ( age[3] - age[1] ) + income[0] * (age[3] - age[0] )
I am struggling to formulate the age relative to the current iteration of age ( age[x] - age[y] ) in a generic way to use in a list comprehension or formula.
edit: the actual operation I want to apply is
income[3 ] + income[2]* interest ** ( age[3] - age[2] ) + income[1]*interest ** (age[3] - age[1] ...
exampe from above: 18+23*1.03 ** (15-10)+13*1.03 ** (15-5)+5*1.03 **(15-0) = 69,92
interest = 1.03
ANSWERED thanks to jezrael & mozway
You can rewrite 18+(18+23+13+5)*15-(23+13+5)*(10+5+0) to be 18+(18+23+13+5)*15-(23+13+5)*(10+5+0):
The general formula is thus:
sumproduct(n) = (income
+ (n-1)*sum(age[:n-1]*income[:n-1])
- sum(age[:n-1]*income[:n-1])
)
As code:
df['sumproduct'] = (df['income']
.add(df['age'].mul(df['income'].cumsum().shift(fill_value=0)))
.sub(df['age'].mul(df['income']).cumsum().shift(fill_value=0))
)
output:
age income sumproduct
0 0 5 5
1 5 13 38
2 10 23 138
3 15 18 338
4 20 12 627
powers are more complex as you cannot directly factorize, you can however rewrite the operation with expanding:
df['sumproduct'] = (df['age'].expanding()
.apply(lambda x: sum(df.loc[:x.index[-1], 'income'] * interest**(x.iloc[-1]-x)))
)
Output:
age income sumproduct
0 0 5 5.000000
1 5 13 18.796370
2 10 23 44.790145
3 15 18 69.924054
4 20 12 93.061143