Conditional join

[samukweku]

Brief Description

I would like to propose conditional join, also known as non-equi joins, which is obtainable in SQL and R's datatable.

Example API

df1 = pd.DataFrame({'col_a': [1,2,3], 'col_b': ["A", "B", "C"]})
	col_a	col_b
0	1	A
1	2	B
2	3	C

df2 = pd.DataFrame({'col_a': [0, 2, 3], 'col_c': ["Z", "X", "Y"]})
     col_a	col_c
0	0	Z
1	2	X
2	3	Y

Please modify the example API below to illustrate your proposed API, and then delete this sentence.

# the join can be >, < or !=. I guess as we get a stable function, we can add others, and combine with equi-joins
df1.conditional_join(df2, left_column = "col_a", right_column = "col_a", join_operator = ">")
    col_a   col_b   col_c
0	1	A	Z
1	2	B	Z
2	3	C	Z
3	3	C	X

[ericmjl]

@samukweku, in your example, do you instead mean this?

df.conditional_join(df1, left_column = "col_a", right_column = "col_a", join_operator = "<")

I'm wondering, how is this different from an outer join? Maybe I'm missing a clarifying example that distinguishes outer joins from conditional joins. Could you help me understand the difference?

[samukweku]

An outer join checks for equality, if df1.col_a == df2.col_a.

# result of outer join 
df1.merge(df2, how='outer', on ='col_a')
     col_a	col_b	col_c
0	1	A	NaN
1	2	B	X
2	3	C	Y
3	0	NaN	Z

The conditional join/non-equi join looks at each value and compares to see which is greater. For this case, we have 1 > 0, 2 > 0, 3 > 0 and 3 > 2 for df.col_a > df1.col_a

[samukweku]

@szuckerman @hectormz I would also love your thoughts on this as well.

[samukweku]

Since there is no interest in this, it is safe to close this issue.

[ericmjl]

@samukweku I think this one is worth leaving open. Let me tag them accordingly.

[samukweku]

just some examples on Stack Overflow, and on pandas github issues page, regarding conditional joins:

Stack Overflow

• https://stackoverflow.com/questions/44204726/preserving-left-right-join-logic-in-pandas-conditional-join
• https://stackoverflow.com/questions/61948103/conditional-merge-join-of-two-large-pandas-dataframes-with-duplicated-keys-bas
• https://stackoverflow.com/questions/23508351/how-to-do-workaround-a-conditional-join-in-python-pandas
• https://stackoverflow.com/questions/44080248/pandas-join-dataframe-with-condition
• https://stackoverflow.com/questions/40924217/how-to-combine-two-pandas-dataframes-with-a-conditional
• https://stackoverflow.com/questions/37289405/dplyr-left-join-by-less-than-greater-than-condition

Links to issues on Pandas github page

• Conditional Join (merge) in pandas pandas-dev/pandas#7480
• ENH: Allow join based on conditional statement pandas-dev/pandas#34543
• Enhancement: 'Joiner'-Function for pd.merge/DataFrame.join pandas-dev/pandas#8962