I have a data frame like this and I would like to multiply the rows by its references value in another data frame using pandas. After that I would like to add all products from those rows and store it in a column called Pro_Sum. I know Python doesn't pass things by reference, but I can name-bind in here so I'm trying to create a dictionary to multiply the values according to its name, but I haven't been successful.
df = pd.read_excel (C:/"dummy")
d = {"C1": 2, "C2": 5,"C3":4, "C4":1}
df.mul(pd.Series(d), axis=1)
prod = d.keys()
df[prod] = df[prod].mul(pd.Series(d), axis=1)[prod]
to_sum_t = list(df)
#.sum ignores the none numeric values.
df['Pro_sum'] = df[to_sum_t].sum(axis=1)
Input
ID AU HP Name1 Value1 CHC1 Name2 Value2 CHC2 Name3 Value3 CHC3
1 4 3 C1 10 100 0 0 0 0 0 0
2 6 2 C2 20 95 C1 6 5 0 0 0
3 2 7 C3 4 40 C4 6 60 0 0 0
4 8 9 C1 8 100 0 0 0 0 0 0
5 2 6 C1 6 10 C2 15 86 C4 1 4
Reference column
Names Values
C1 2
C2 5
C3 4
C4 1
Example row 5 Pro_Sum = 6*2 + 15*5 + 1*1 = 88
Minimal Output
ID AU HP Name1 Value1 CHC1 Name2 Value2 CHC2 Name3 Value3 CHC3 Pro_Sum
1 4 3 C1 10 100 0 0 0 0 0 0 20
2 6 2 C2 20 95 C1 6 5 0 0 0 112
5 2 6 C1 6 10 C2 15 86 C4 1 4 88
I think need filter columns by names, replace values by dictionary and convert to numpy arrays by values, multiple, sum and assign to new column:
d = {"C1": 2, "C2": 5,"C3":4, "C4":1}
a = df.filter(like='Name').replace(d).astype(int).values
b = df.filter(like='Value').values
df['Pro_Sum'] = (a * b).sum(axis=1)
print (df)
ID AU HP Name1 Value1 CHC1 Name2 Value2 CHC2 Name3 Value3 CHC3 \
0 1 4 3 C1 10 100 0 0 0 0 0 0
1 2 6 2 C2 20 95 C1 6 5 0 0 0
2 3 2 7 C3 4 40 C4 6 60 0 0 0
3 4 8 9 C1 8 100 0 0 0 0 0 0
4 5 2 6 C1 6 10 C2 15 86 C4 1 4
Pro_Sum
0 20
1 112
2 22
3 16
4 88