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

user_1444483 0
Viewed 263

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
pandas dataframe python
3 Answers

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
user_42384080

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
user_152399510

Set the date index on the data frames, create a Series from the third dataframe; map df1's columns with the Series map and finally do the subtraction:

df1 = df1.set_index('date')

df2 = df2.set_index('date')

df3 = df3.set_index('To').From

df1.columns = df1.columns.map(df3)

df2 - df1

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