# \[ANN\] FlexiJoins.jl: fresh take on joining datasets

**URL:** <https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655>\
**Category:** Package Announcements\
**Created:** [April 18, 2022, 8:37pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655 "2022-04-18T20:37:29Z")\
**Posts on this page:** 14\
**Page:** 2

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [July 8, 2024, 11:04am UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/22 "2024-07-08T11:04:45Z")

</div>

Thank you, it works now 🙂  
Upgraded the MNWE above to a MWE.

---

<div class="post-metadata">

**Author:** ![Gregstrq](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/gregstrq/32/20620_2.png) [@Gregstrq](https://discourse.julialang.org/u/Gregstrq)\
**Post date:** [July 8, 2024, 1:07pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/23 "2024-07-08T13:07:53Z")

</div>

The problem is that the values in both dataframes can be missing, not only in the second one.

---

<div class="post-metadata">

**Author:** ![Gregstrq](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/gregstrq/32/20620_2.png) [@Gregstrq](https://discourse.julialang.org/u/Gregstrq)\
**Post date:** [July 8, 2024, 3:34pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/24 "2024-07-08T15:34:07Z")

</div>

By the way, is it possible to see those rows, that break the cardinality assumption, in the error message?

---

<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:** [July 8, 2024, 3:48pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/25 "2024-07-08T15:48:44Z")

</div>

> [@Gregstrq](#):
>
> The problem is that the values in both dataframes can be missing, not only in the second one.

Yes, I see… You can use `by_pred(f_L, !isdisjoint, f_R)` where `f_L` and `f_R` create these 0-or-Inf intervals.  
This isn’t as efficient as possible with a dedicated implementation, but should work already.

The ideal interface seems to be `by_pred(:col_L, isequal_with_missing_wildcard, :col_R)`, probably with a nicer name (: This could directly generalize to `isequal_with_wildcard(nothing)` or `isequal_with_wildcard(NaN)`…  
Btw, is a function such as `isequal_with_missing_wildcard` already defined somewhere?

---

<div class="post-metadata">

**Author:** ![Gregstrq](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/gregstrq/32/20620_2.png) [@Gregstrq](https://discourse.julialang.org/u/Gregstrq)\
**Post date:** [July 8, 2024, 3:57pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/26 "2024-07-08T15:57:52Z")

</div>

Not that I know about. I myself defined a function

```julia
equal_missing(x,y) = any(ismissing, (x,y)) ? true : x==y

```

And used it inside the predicate.

Ideal would be to add some keyword that would modify the behaviour of all `by_key` to do this kind of check. Writing up `by_pred` for every variable with missing values that you want to join on is too cumbersome.

---

<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:** [July 8, 2024, 9:27pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/27 "2024-07-08T21:27:09Z")

</div>

> [@Gregstrq](#):
>
> Ideal would be to add some keyword that would modify the behaviour of all `by_key` to do this kind of check. Writing up `by_pred` for every variable with missing values that you want to join on is too cumbersome.

I agree, maybe `by_key(f_L, f_R, isequal=isequal_with_wildcard)`…

---

<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:** [July 8, 2024, 9:27pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/28 "2024-07-08T21:27:36Z")

</div>

> [@Gregstrq](#):
>
> By the way, is it possible to see those rows, that break the cardinality assumption, in the error message?

It should be, for convenience – just isn’t implemented.  
What I typically do after getting a cardinality error in

```julia
innerjoin((;L, R), by_..., cardinality=(1, 1))

```

, is run

```julia
@p outerjoin((;L, R), by_..., groupby=:L) |> filter(length(_.R) != 1)

```

(+ same in the other direction, `groupby=:R`)  
and explore the result.

---

<div class="post-metadata">

**Author:** ![junder873](https://avatars.discourse-cdn.com/v4/letter/j/e95f7d/32.png) [@junder873](https://discourse.julialang.org/u/junder873)\
**Post date:** [April 1, 2025, 12:20am UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/29 "2025-04-01T00:20:34Z")

</div>

Is it possible to do asof joins with maximum distance? For example, I want the most recent event that occurred at most 3 months ago. This is possible after the fact, but can it occur as part of the join?

For example, with the data:

```julia
d1 = DataFrame(event=[Date(2020), Date(2021)])
d2 = DataFrame(time=[Date(2019, 12), Date(2020, 6), Date(2019, 11)])

```

You can run

```julia
leftjoin(
    (d1, d2),
    by_pred(:event, >, :time),
    multi=(identity, closest,)
)
2×2 DataFrame
 Row │ event time       
     │ Date Date       
─────┼────────────────────────
   1 │ 2020-01-01 2019-12-01
   2 │ 2021-01-01 2020-06-01 # this is past the 3 month mark

```

Or you can make sure it is within the 3 month mark but then it is not for sure only 1 observation:

```julia
leftjoin(
    (d1, d2),
    by_pred(x -> x.event - Month(3) .. x.event, ∋, :time),
)
3×2 DataFrame
 Row │ event time       
     │ Date Date?      
─────┼────────────────────────
   1 │ 2020-01-01 2019-11-01
   2 │ 2020-01-01 2019-12-01
   3 │ 2021-01-01 missing  

```

---

<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:** [April 1, 2025, 8:28pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/30 "2025-04-01T20:28:41Z")

</div>

`multi=closest` should be supported for `> < >= <=` as you noticed, and for `by_distance` – e.g. `by_distance(:event, :time, Euclidean(), <=(3))`.  
But I don’t think there’s anything out of the box for one-sided `closest` with a restriction on the max distance… It’s not more difficult, just not clear how the user-facing interface should look. For `by_pred(x -> x.event - Month(3) .. x.event, ∋, :time)`, it’s not clear what “closest” would mean.

If you have suggestions for a natural interface, please share 🙂  
But also just filtering after join should work and be basically as efficient.

---

<div class="post-metadata">

**Author:** ![junder873](https://avatars.discourse-cdn.com/v4/letter/j/e95f7d/32.png) [@junder873](https://discourse.julialang.org/u/junder873)\
**Post date:** [April 1, 2025, 9:17pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/31 "2025-04-01T21:17:34Z")

</div>

My thought on an interface would be something like:

```julia
leftjoin(
    (d1, d2),
    by_pred(:event, >, :time),
    multi=(identity, closest(:event, :time))
)

```

I actually tried that when I was first looking at the package since it matches the `by_key(...)` interface. As an additional piece, it would be interesting if it accepted custom functions (`closest(:event, fun, :time)`). I think this could also allow items that are not already in the conditions to matter for picking the closest (e.g., find the closest time to an event without a restriction on how far away that time is).

An alternative would be moving the `closest` out of a keyword argument from the parent function and into the `by_pred` or `by_distance`:

```julia
leftjoin(
    (d1, d2),
    by_pred(:event, >, :time; closest=true)
    # or closest(by_pred(:event, >, :time))
)

```

To me, this clarifies what closest is being run on for the user.

A final alternative would be to combine those two:

```julia
leftjoin(
    (d1, d2),
    by_pred(:event, >, :time) & closest(:event, :time)
)

```

I am not sure about the feasibility of those options, but all of those to me fit in what is already done within the package.

---

<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:** [April 2, 2025, 2:54am UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/32 "2025-04-02T02:54:44Z")

</div>

Thanks for the suggestions! I agree that this functionality should be added as it’s clearly useful.

The main issue with putting `closest` into the condition itself, no matter how exactly it’s done, is that it doesn’t specify which side the “closest” correspond to. Currently,

```julia
innerjoin((;A, B), ..., multi=(;B=closest))

```

means “for each `a in A`, take the single closest `b from B`”. The A/B distinction is important as “for each `b in B`, take the single closest `a from A`” would give a different result.

---

<div class="post-metadata">

**Author:** ![junder873](https://avatars.discourse-cdn.com/v4/letter/j/e95f7d/32.png) [@junder873](https://discourse.julialang.org/u/junder873)\
**Post date:** [April 2, 2025, 7:44pm UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/33 "2025-04-02T19:44:32Z")

</div>

I guess I did not understand that from the docs.

So as a keyword argument, I think you could still do:

```julia
leftjoin(
    (; A=d1, B=d2),
    by_pred(:event, >, :time),
    multi=(; B=closest(:event, :time))
)

```

Or `by_pred` and `by_distance` could accept keyword arguments and do something like:

```julia
leftjoin(
    (; A=d1, B=d2),
    by_pred(:event, >, :time; mutli=(;B=closest))
)

```

This would be the most consistent with the current code.

Another thought would be to borrow the left/right terminology, (e.g., `leftclosest` and `rightclosest` or `left_closest`), so something like:

```julia
leftjoin(
    (d1, d2),
    by_pred(:event, >, :time) & leftclosest(:event, :time)
)

```

---

<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:** [April 3, 2025, 1:31am UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/34 "2025-04-03T01:31:11Z")

</div>

Regarding keyword vs positional arg – the idea is that one can use names anywhere, if desired. Both named and unnamed should be supported everywhere if datasets are passed as named args (`leftjoin((; A, B), ...)`):

- `by_key(:key_in_A, :key_in_B)` or `by_key(A=:key_in_A, B=:key_in_B)`
- `multi=(identity, closest)` or `multi=(A=identity, B=closest)` or `multi=(;B=closest)`

I think I like the clarity of putting `multi` into the condition itself, as in `by_pred(..., multi=(;B=closest))` or `by_key(..., multi=(;B=first))` etc. I’ll try seeing how well it fits with other pieces…  
In particular, probably there should be a check that only one `multi` argument is provided in case of several conditions `... & ... & ...`.

---

<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:** [September 2, 2025, 3:10am UTC](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655/35 "2025-09-02T03:10:54Z")

</div>

# FlexiJoins.jl: Stable, Growing Through Integrations

It’s been about three years since the last update in this thread (v0.1.28), and FlexiJoins has continued to evolve steadily. The current version is 0.1.38, and I’m happy to report that the package has remained stable with no breaking changes since its initial release.

FlexiJoins.jl has proven remarkably low-maintenance over these three years. Most changes have been minor optimizations and bug fixes.  
Docs were improved recently!

What I’m happy to highlight is the growing variety of packages that integrate with FlexiJoins, each providing specialized join types while maintaining the same familiar interface.

Current integrations include:

- SkyCoords.jl for astronomy catalog matching by coordinates _(extension included in FlexiJoins)_
- GeometryOps.jl for spatial joins
- Uncertain.jl for join-by-uncertainty, effectively different distance thresholds for each object

* * *

For those interested in learning more, I recently presented FlexiJoins.jl at JuliaCon’25. The [talk was recorded](https://www.youtube.com/live/33rxNE4e50A?si=a2BIhKyDwdJCAFam&t=2070), and it covers both the user-facing interface and internal implementation details.  
[![image](https://global.discourse-cdn.com/julialang/original/3X/0/1/016bb540ddc28225ca34308017f492504154c268.jpeg)](https://www.youtube.com/live/33rxNE4e50A?si=a2BIhKyDwdJCAFam&t=2070)

[Previous page](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655.md?page=1)