Does pandas dataframe merge work with greater or less?

Question

Now i need to merge two dataframe with the condition greater than(>=). But merge only support equal. Is there any way to deal with it? Thanks!

Answer 1

I don't know how to achieve the following with similar merge and join syntax in pandas,

SELECT * 
FROM a 
INNER JOIN b 
ON a.column1 >= b.column1 AND a.column1 <= b.column2 

But the query above can also be written implicitly as;

SELECT * 
FROM a, b 
WHERE a.column1 >= b.column1 AND a.column1 <= b.column2 

Which is basically the old syntax and should do exactly same (performance wise). It takes the cartesian product of 2 tables (or cross join) and then select from that using the WHERE condition, which could be easily implemented in pandas. This could be a little heavy on memory, but should be fast.

First the FROM a, b clause (we temporarily assign a column with same values in all rows, so we can cross join over it);

df = pd.merge(a.assign(key=0), b.assign(key=0), on='key').drop('key', axis=1)

and then use boolean indexing (our WHERE clause) to slice the frame;

df[(df["column1_x"] >= df["column1_y"]) & (df["column1_x"] <= df["column2_y"])]

If you don't want the cartesian product and only want to compare the rows on same index of both tables, you can merge on index like this;

df = a.merge(b, left_index = True, right_index = True)

or concat on axis 1 if they are same length;

df = pd.concat([a, b], axis=1)

And use boolean indexing again to eliminate results;

df[(df["column1_x"] >= df["column1_y"]) & (df["column1_x"] <= df["column2_y"])]