0

I'm trying to do a full outer join in pandas with one of the conditions being a date match. The SQL code would be like the following:

SELECT *
    FROM applications apps
    FULL OUTER JOIN order_data orders on apps.account = orders.account_order
                                    and  orders.[Order date] <=   apps.time_stamp;

How could I achieve this considering apps and order_data are two pandas dataframes?

I tried using pysql but full outer joins are not supported.

Thank you

Daniel Martinez
  • 397
  • 4
  • 20
  • I don't get why is this duplicate, the answer @coldspeed pointed me to doesn't explain the date <= filter – Daniel Martinez Jan 15 '19 at 04:43
  • Sorry, the post has been reopened. But FYI, inequality based clauses are not supported, joining can only be done where column values are equal. You will need an additional `query` or boolean indexing step to do it. – cs95 Jan 15 '19 at 05:38

1 Answers1

0

The Pandas .join method lets you use outer joins: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.join.html

Simply load up your two data frames and

a.join(b, how='outer')

will do it.

dtanabe
  • 1,611
  • 9
  • 18
  • How do I specify the date filer in the join? – Daniel Martinez Jan 15 '19 at 04:41
  • Unfortunately I don't think you can—joins in pandas only operate on values that are equal to each other. You'll have to apply your date filter "after the fact"; do the JOIN first, and then knock out any rows that won't match. – dtanabe Jan 15 '19 at 04:43
  • But that will knock out rows and then I won't have a full outer join, right? – Daniel Martinez Jan 15 '19 at 04:44
  • Are `account` and `account_order` unique on their respective tables? – dtanabe Jan 15 '19 at 05:00
  • No, each table has a unique ID (account_id and order_id), but the account and account_order can be repeated – Daniel Martinez Jan 15 '19 at 05:02
  • I think it's going to be rough trying to do this in Pandas. You're probably better off either just writing some custom Python code or using a database that supports what you want to do. – dtanabe Jan 15 '19 at 05:05