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

Question

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	x3
12/31/2019	1.0	0.8	1.0
12/31/2020	0.4	0.7	1.5

Dataframe c

From	To
x1	B
x2	A
x3	A

Required result

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

Answer 1

Use merge before subtracting:

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

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

Answer 2

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