Join two DataFrames on common columns only if the difference in a separate column is within range [-n, +n]

Question

I have two data frames df1 and df2 as shown below:

df1

Date        BillNo.     Amount
10/08/2020  ABBCSQ1ZA   878
10/09/2020  AADC9C1Z5   11
10/12/2020  AC928Q1ZS   3998
10/14/2020  AC9268RE3   198
10/16/2020  AA171E1Z0   5490
10/19/2020  BU073C1ZW   3432

df2

Date        BillNo.     Amount
10/08/2020  ABBCSQ1ZA   876
10/11/2020  ATRC95REW   115
10/14/2020  AC9268RE3   212
10/16/2020  AA171E1Z0   5491
10/25/2020  BPO66W2LO   344

My final answer should be:

final

Date        BillNo.     Amount
10/08/2020  ABBCSQ1ZA   876
10/16/2020  AA171E1Z0   5491

How do I find common rows from both the data frame using Date BillNo. Amount when the difference in value range is between [-5,5]?

I know how to find common rows by using:

df_all = df1.merge(df2.drop_duplicates(), on=['Date', 'BillNo.', 'Amount'], 
                   how='outer', indicator=True)

However, this doesn't give the rows which are in range. Anyone who could help?

Edit: We can see in df1: 10/14/2020,AC9268RE3,198 and df2: 10/14/2020,AC9268RE3,212 the difference is 14, hence this should not be included in common rows

Answer 1

We can merge, then perform a query to drop rows not within the range:

(df1.merge(df2, on=['Date', 'BillNo.'])
    .query('abs(Amount_x - Amount_y) <= 5')
    .drop('Amount_x', axis=1))

         Date    BillNo.  Amount_y
0  10/08/2020  ABBCSQ1ZA       876
1  10/16/2020  AA171E1Z0      5491

This works well as long as there is only one row that corresponds to a specific (Date, BillNo) combination in each frame.

Answer 2

You could use merge_asof:

udf2 = df2.drop_duplicates().sort_values('Amount')
res = pd.merge_asof(udf2, df1.sort_values('Amount').assign(indicator=1), on='Amount', by=['Date', 'BillNo.'],
                    direction='nearest', tolerance=5)
res = res.dropna().drop('indicator', 1)

print(res)

Output

         Date    BillNo.  Amount
2  10/08/2020  ABBCSQ1ZA     876
3  10/16/2020  AA171E1Z0    5491

Answer 3

We can set Date and BillNo. as index as subtract both the dataframe and filter out only values b/w -5 to 5.

d1 = df1.set_index(['Date', 'BillNo.'])
d2 = df2.set_index(['Date', 'BillNo.'])

idx = (d1-d2).query('Amount>=-5 & Amount<=5').index

d1.loc[idx].reset_index()
         Date    BillNo.  Amount
0  10/08/2020  ABBCSQ1ZA     878
1  10/16/2020  AA171E1Z0    5490

d2.loc[idx].reset_index()
         Date    BillNo.  Amount
0  10/08/2020  ABBCSQ1ZA     876
1  10/16/2020  AA171E1Z0    5491

---

To make it more generic to work with any n.

n = 5
idx = (d1-d2).query('Amount>=-@n & Amount<=@n').index

Or

lower_limit = -2 # Example, can be anything
upper_limit = 5  # Example, can be anything
idx = (d1-d2).query('Amount>=@lower_limit & Amount<=@upper_limit').index