Pandas: cross join with multiple conditions-CodePudding

Consider the following query:

SELECT *
    FROM
      table_1
    CROSS JOIN
      table_2
    WHERE
      table_1.f1 >= table_2.f1001 
      AND (
        table_1.f1 < table_2.f1002
        OR table_2.f1002 IS NULL
      )

Is it possible to implement this using Pandas, for example with pd.merge(how='cross')? Suppose we have two dataframes table_1 and table_1, and we need to do a cross join according to the conditions below:

table_1.f1 >= table_2.f1001 AND (table_1.f1 < table_2.f1002 OR table_2.f1002 IS NULL)

CodePudding user response：

You can do like

out = pd.merge(table_1, table_2,how='cross')
out = out[out['f1'].ge(out['f1001']) & (out['f1'].lt(out['f1001']) | table_2['f1002'].isna())]

CodePudding user response：

Use merge and query:

out = pd.merge(table_1, table_2, how='cross') \
        .query("(f1 >= f1001) & ((f1 < f1002) | f1002.isna())")

CodePudding user response：

df.merge(df, on='cid', suffixes=('1','2')).query('qid1 < qid2')

something like that , use query after merge