i have to columns in a pandas dataframe format and want the output in the C D column as below
A B C D
1 2 1*2 1*2
3 4 (1+3)*4 (1*2)+(3*4)
5 6 (1+3+5)*6 (1*2)+(3*4)+(5*6)
7 8 (1+3+5+7)*8 (1*2)+(3*4)+(5*6)+(7*8)
9 10 .... .....
Here i am trying to code excel formula in a python way ,Can someone throw code for this
a) python code for top to bottom calculation
Excel Formulas for the final outcome:
C1=IFERROR($B2*SUM(A2:$A$2)-SUMPRODUCT($B2:B$2,$A2:A$2),0)
C2=IFERROR($B3*SUM(A$2:$A3)-SUMPRODUCT($B$2:B3,$A$2:A3),0)
.....
....
C14=IFERROR($B14*SUM(A$2:$A14)-SUMPRODUCT($B$2:B14,$A$2:A14),0)
b) python code for bottom to top calculation from bottom
e1==IFERROR(SUMPRODUCT($B2:B$14,$C2:C$14)-$B2*SUM($C2:C$14),0)
E2=IFERROR(SUMPRODUCT($B3:B$14,$C3:C$14)-$B3*SUM($C3:C$14),0)
e4=IFERROR(SUMPRODUCT($B4:B$14,$C4:C$14)-$B4*SUM($C4:C$14),0)
.....
.....
.....
e14=IFERROR(SUMPRODUCT($B14:B$14,$C14:C$14)-$B14*SUM($C14:C$14),0)
Use cumsum with multiple by mul:
df['C'] = df['A'].cumsum().mul(df['B'])
df['D'] = df[['A', 'B']].prod(1).cumsum()
Or:
df = df.assign(C=df['A'].cumsum().mul(df['B']),
D=df['A'].mul(df['B']).cumsum())
print (df)
A B C D
0 1 2 2 2
1 3 4 16 14
2 5 6 54 44
3 7 8 128 100
4 9 10 250 190
EDIT:
For reverse values is simpliest use iloc[::-1]:
df = df.assign(C=df['A'].iloc[::-1].cumsum().mul(df['B']),
D=df[['A', 'B']].iloc[::-1].prod(1).cumsum())
print (df)
A B C D
0 1 2 50 190
1 3 4 96 188
2 5 6 126 176
3 7 8 128 146
4 9 10 90 90
EDIT1:
df = pd.DataFrame({'A':[1,3,5,7,9], 'B':[2,4,6,8,10]})
df['C']=df['A'].iloc[::-1].cumsum().mul(df['B'])
df['D']=df[['A', 'B']].iloc[::-1].prod(1).cumsum()
print (df)
A B C D
0 1 2 50 190
1 3 4 96 188
2 5 6 126 176
3 7 8 128 146
4 9 10 90 90
#create index by column A
x = df.set_index('A')
print (x)
B C D
A
1 2 50 190
3 4 96 188
5 6 126 176
7 8 128 146
9 10 90 90
#get index of minimal value by D column
print (x['D'].idxmin())
9