How to subtract rows based on matching column in Pandas?

Question

If I have two dataframes, like these in the example created with:

df1 = pd.DataFrame({'A': randint(1,11,10), 'B': randint(10,100,10), 'C': randint(100,1000,10)})
df2 = pd.DataFrame({'A': randint(1,11,10), 'B': randint(10,100,10), 'C': randint(100,1000,10)})
df2 = df2.drop_duplicates(subset=['A'])

df1

    A   B   C
0   2   96  826
1   1   64  601
2   1   27  343
3   5   65  600
4   10  68  658
5   6   81  895
6   5   73  440
7   4   54  865
8   1   24  597
9   10  66  928

df2

    A   B   C
0   2   87  669
1   5   99  594
2   6   50  989
3   10  46  767
4   3   56  828
5   4   83  415
6   1   41  332

How can I subtract columns B (df['B'] - df2['B']) only if the values from column A are matching? So I can get a new column in df1 like:

9
23
-14
-34
22
31
-26
-29
-17
20

Answer 1

To get the values you want to subtract, take df1['A'] and map the values of df2['B'] to it by indexing df2['B'] with df2['A']:

df1['new'] = df1['B'] - df1['A'].map(df2.set_index('A')['B'])

The resulting output:

    A   B    C  new
0   2  96  826    9
1   1  64  601   23
2   1  27  343  -14
3   5  65  600  -34
4  10  68  658   22
5   6  81  895   31
6   5  73  440  -26
7   4  54  865  -29
8   1  24  597  -17
9  10  66  928   20

---

Edit

For smaller datasets, it may be slightly faster to supply a dictionary to map.

Timings on the example dataset:

%timeit df1.B - df1.A.map(df2.set_index('A').B)
%timeit df1.B - df1.A.map(dict(zip(df2.A, df2.B)))
%timeit df1.B - df1.A.map(dict(zip(df2.A.values, df2.B.values)))

1000 loops, best of 3: 718 µs per loop
1000 loops, best of 3: 492 µs per loop
1000 loops, best of 3: 459 µs per loop

For larger datasets, using the index method appears to be faster.

Larger dataset setup:

rows, a_max, b_max, c_max = 10**6, 5*10**4, 10**5, 10**5
df1 = pd.DataFrame({'A': randint(1, a_max, rows), 'B': randint(10, b_max, rows), 'C': randint(100, c_max, rows)})
df2 = pd.DataFrame({'A': randint(1, a_max, rows), 'B': randint(10, b_max, rows), 'C': randint(100, c_max, rows)})
df2 = df2.drop_duplicates(subset=['A'])

Timings on the larger dataset:

%timeit df1.B - df1.A.map(df2.set_index('A').B)
%timeit df1.B - df1.A.map(dict(zip(df2.A, df2.B)))
%timeit df1.B - df1.A.map(dict(zip(df2.A.values, df2.B.values)))

10 loops, best of 3: 114 ms per loop
10 loops, best of 3: 359 ms per loop
10 loops, best of 3: 354 ms per loop

Answer 2

Try this:

In [61]: df1['new'] = df1.drop('C',1).merge(df2.drop('C',1), on='A', 
                                            how='left', suffixes=['','2']) \
                         .eval("new=B-B2", inplace=False)['new']

In [62]: df1
Out[62]:
    A   B    C  new
0   2  96  826    9
1   1  64  601   23
2   1  27  343  -14
3   5  65  600  -34
4  10  68  658   22
5   6  81  895   31
6   5  73  440  -26
7   4  54  865  -29
8   1  24  597  -17
9  10  66  928   20