I have a pandas DataFrame (df_A) with this basic form:
JavaScript
x
8
1
|id| alt| a | b | c | d | e |
2
|--|----|-----|-----|-----|---|---|
3
| 0| ICV| 0.2 | 1.0 | 0.2 | 0 | 1 |
4
| 1| ICV| 1.0 | 1.0 | 0.2 | 0 | 0 |
5
| 2| BEV| 3.2 | 1.0 | 0.2 | 1 | 0 |
6
| 3| ICV| 2.0 | 1.0 | 0.2 | 0 | 0 |
7
| 4| BEV| 2.0 | 1.0 | 0.2 | 1 | 1 |
8
Furthermore I have another DataFrame (df_B):
JavaScript
1
5
1
|id| alt| a | b | c |
2
|--|----|-----|-----|-----|
3
| 0| ICV| 0.1 | 0.3 | 0.5 |
4
| 1| BEV| 0.2 | 0.4 | 0.6 |
5
What I want to do is multiply the values of the second DataFrame with the values of the first, where the alt value is the same. I also do not want the d or e columns to be involved in the multiplication. So I want a DataFrame (df_C) like this:
JavaScript
1
8
1
|id| alt| a | b | c | d | e |
2
|--|----|---------|---------|---------|---|---|
3
| 0| ICV| 0.2*0.1 | 1.0*0.3 | 0.2*0.5 | 0 | 1 |
4
| 1| ICV| 1.0*0.1 | 1.0*0.3 | 0.2*0.5 | 0 | 0 |
5
| 2| BEV| 3.2*0.2 | 1.0*0.4 | 0.2*0.6 | 1 | 0 |
6
| 3| ICV| 2.0*0.1 | 1.0*0.3 | 0.2*0.5 | 0 | 0 |
7
| 4| BEV| 2.0*0.2 | 1.0*0.4 | 0.2*0.6 | 1 | 1 |
8
I have tried a few thing unsuccessfully:
JavaScript
1
5
1
list = ["a","b","c"]
2
df_C = df_A.copy()
3
for i in ["BEV","ICV"]:
4
df_C[list].loc[df_C["alt"]==i] = df_A[list+["alt"]].loc[df_A["alt"]==i].drop(["alt"],axis=1).mul(df_B[df_B["alt"]==i].drop(["alt"],axis=1).to_numpy(),axis=1)
5
This just returns the original DataFrame (df_A). I know my approach is deeply flawed but I cannot think of any way to do this.
Advertisement
Answer
You can use a merge
and in place multiplication:
JavaScript
1
3
1
cols = ['a', 'b', 'c']
2
df_A[cols] *= df_A[['alt']].merge(df_B, how='left')[cols]
3
output:
JavaScript
1
7
1
id alt a b c d e
2
0 0 ICV 0.02 0.3 0.10 0 1
3
1 1 ICV 0.10 0.3 0.10 0 0
4
2 2 BEV 0.64 0.4 0.12 1 0
5
3 3 ICV 0.20 0.3 0.10 0 0
6
4 4 BEV 0.40 0.4 0.12 1 1
7
intermediate:
JavaScript
1
9
1
df_A[['alt']].merge(df_B, how='left')[cols]
2
3
a b c
4
0 0.1 0.3 0.5
5
1 0.1 0.3 0.5
6
2 0.2 0.4 0.6
7
3 0.1 0.3 0.5
8
4 0.2 0.4 0.6
9