<jupyter_start><jupyter_text>Joining on Non-Equi Operators<jupyter_code>import pandas as pd
import janitor
import numpy as np
#https://stackoverflow.com/q/61948103/7175713 
df1 = pd.DataFrame({'id': [1,1,1,2,2,3], 
                    'value_1': [2,5,7,1,3,4]})

df2 = pd.DataFrame({'id': [1,1,1,1,2,2,2,3], 
                    'value_2A': [0,3,7,12,0,2,3,1], 
                    'value_2B': [1,5,9,15,1,4,6,3]})
df1
df2<jupyter_output><empty_output><jupyter_text>Join on equi and non-equi operators is possible:<jupyter_code>df1.conditional_join(
        df2,
        ('id', 'id', '=='),
        ('value_1', 'value_2A', '>='),
        ('value_1', 'value_2B', '<='),
        sort_by_appearance = True
    )<jupyter_output><empty_output><jupyter_text>The default join is inner. left and right joins are supported as well:<jupyter_code>df1.conditional_join(
        df2,
        ('id', 'id', '=='),
        ('value_1', 'value_2A', '>='),
        ('value_1', 'value_2B', '<='),
        how='left',
        sort_by_appearance = True
    )
df1.conditional_join(
        df2,
        ('id', 'id', '=='),
        ('value_1', 'value_2A', '>='),
        ('value_1', 'value_2B', '<='),
        how='right',
        sort_by_appearance = True
    )<jupyter_output><empty_output><jupyter_text>Join on just the non-equi joins is also possible:<jupyter_code>df1.conditional_join(
        df2,
        ('value_1', 'value_2A', '>'),
        ('value_1', 'value_2B', '<'),
        how='inner',
        sort_by_appearance = True
    )<jupyter_output><empty_output><jupyter_text>Join on not equal -> !=<jupyter_code>df1.conditional_join(
        df2,
        ('id', 'id', "!=")
    )<jupyter_output><empty_output><jupyter_text>If the columns from both dataframes have nothing in common, a single indexed column is returned:<jupyter_code>(df1.select_columns('value_1')
    .conditional_join(
        df2.select_columns('val*'),
        ('value_1', 'value_2A', '>'),
        ('value_1', 'value_2B', '<'),
    )
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18406729341909944, 'fraction_numerical': 0.04601682335477486, 'mean_word_length': 3.221294363256785, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 31, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '47474', 'license': 'MIT', 'n_tokens_mistral': 813, 'n_tokens_neox': 780, 'n_words': 126, 'path': 'examples/notebooks/conditional_join.ipynb', 'repo_name': 'thatlittleboy/pyjanitor', 'size': '2021'}