How can I subtract two dataframes based on a column mapping?-CodePudding

I am trying to subtract df A from df B where the column mapping is based on a 3rd mapping data frame. In this example, B should be subtracted from x1 and A should be subtracted from x2.

This can be done with loops and some other dirty methods, but I was wondering if there is a more concise way to do that.

Dataframe a

date	A	B
12/31/2019	0.1	0.4
12/31/2020	0.3	0.6

Dataframe b

date	x1	x2
12/31/2019	1.0	0.8
12/31/2020	0.4	0.7

Dataframe c

From	To
x1	B
x2	A

Required result

date	x1	x2
12/31/2019	0.6	0.7
12/31/2020	-0.2	0.4

CodePudding user response：

You can use rename to temporary rename the column and subtract. Assuming the date is your index:

a - b.rename(columns=c.set_index('From')['To'])

Output:

               A    B
date
12/31/2019  -0.7 -0.6
12/31/2020  -0.4  0.2

CodePudding user response：

Use merge before subtracting:

tmp = pd.merge(dfa, dfb, on='date')
dfb[['x1', 'x2']] = tmp[dfc['From']].values - tmp[dfc['To']].values

print(dfb)

# Output:
         date   x1   x2
0  12/31/2019  0.6  0.7
1  12/31/2020 -0.2  0.4