# Conditional left join 2 dataframes when none of the columns are common

**URL:** <https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401>\
**Category:** General Usage\
**Tags:** dataframes, flexijoins\
**Created:** [May 21, 2022, 12:05am UTC](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401 "2022-05-21T00:05:19Z")\
**Posts on this page:** 7\
**Page:** 2

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [May 24, 2022, 7:03pm UTC](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401/22 "2022-05-24T19:03:13Z")

</div>

Nice catch! I only used closed intervals myself and didn’t even think of testing with open/halfopen. Indeed, they don’t work yet, but easy to add - will likely do it in a few days.

---

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [May 24, 2022, 7:25pm UTC](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401/23 "2022-05-24T19:25:09Z")

</div>

Please post when your code has been updated. I like the flexijoin solution the best so far and don’t want to retort to other solutions.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [May 24, 2022, 8:20pm UTC](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401/24 "2022-05-24T20:20:06Z")

</div>

In the meantime, you may want to try this expression

```julia
leftjoin((tbl1, tbl2), by_pred(:id, ∈, r -> Interval(r.id1, r.id2-eps(r.id2/1.))))

# or if r.id2 are integers

leftjoin((tbl1, tbl2), by_pred(:id, ∈, r -> Interval(r.id1, r.id2-1)))

```

---

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [May 24, 2022, 11:00pm UTC](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401/25 "2022-05-24T23:00:53Z")

</div>

My actual data are ZonedDateTime in miliseconds and moving them back and forth by a bit can screw things up. I doubt it will be correct.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [May 25, 2022, 9:38am UTC](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401/26 "2022-05-25T09:38:10Z")

</div>

So (apart from units of measure) your numbers are integers !?  
therefore `{x € Z: x <n} = {x € Z: x <= n-1}`, or not?

---

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [May 26, 2022, 2:21am UTC](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401/27 "2022-05-26T02:21:04Z")

</div>

I understand but I am not willing to take that chance because I have been burnt in the past with ZonedDateTimes in c# and R and other languages as well where subtracting TimeSpan from a date which would lead to the time being after daylight savings was applied v/s before giving me incorrect answers. The corner cases are quite a lot and I do not wish to introduce another set of issues when dealing with a big data set containing millions/billions of rows.

here is an example in c# that has burnt me badly in the past: [c# - Why doesn’t subtracting two local DateTime values appear to account for Daylight Saving Time? - Stack Overflow](https://stackoverflow.com/questions/43644252/why-doesnt-subtracting-two-local-datetime-values-appear-to-account-for-daylight)

You would think the `DateTime` object in c# would understand time zones when doing math but that’s not the case.

not worth the risk for me to try and subtract and then spend days figuring how did I screw things up

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [May 26, 2022, 6:28pm UTC](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401/28 "2022-05-26T18:28:31Z")

</div>

> [@aplavin](#):
>
> Nice catch! I only used closed intervals myself and didn’t even think of testing with open/halfopen. Indeed, they don’t work yet, but easy to add - will likely do it in a few days.

@vtomar: added in `FlexiJoins@0.1.4`, this version should already be available for installation.

[Previous page](https://discourse.julialang.org/t/conditional-left-join-2-dataframes-when-none-of-the-columns-are-common/81401.md?page=1)