# Asof join support in DataFrames.jl

**URL:** <https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445>\
**Category:** Data\
**Tags:** dataframes\
**Created:** [October 7, 2022, 8:24pm UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445 "2022-10-07T20:24:25Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [October 7, 2022, 8:24pm UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/1 "2022-10-07T20:24:25Z")

</div>

Is merging data frames by range supported now?

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [October 8, 2022, 5:38am UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/2 "2022-10-08T05:38:40Z")

</div>

Do you mean [asofjoin](https://questdb.io/docs/reference/sql/join/#asof-join)?

If yes then it is not implemented yet. We are internally discussing with @nalimilan about the best API design for this feature. I expect that it will be implemented in `main` branch this year.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [October 8, 2022, 2:24pm UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/3 "2022-10-08T14:24:21Z")

</div>

For example, dataframe A has two columns id and date, and dataframe B has three columns id, date1, and date2. merge A and B based on (id = id, date1 \< date \< date2)

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [October 8, 2022, 2:36pm UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/4 "2022-10-08T14:36:06Z")

</div>

> [@Yifan\_Liu](#):
>
> date1 \< date \< date2

do you want all rows from the second table that meet this condition or only the closest one?

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [October 8, 2022, 2:37pm UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/5 "2022-10-08T14:37:07Z")

</div>

all rows.

---

<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:** [October 8, 2022, 4:25pm UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/6 "2022-10-08T16:25:42Z")

</div>

[FlexiJoins.jl](https://discourse.julialang.org/t/ann-flexijoins-jl-fresh-take-on-joining-datasets/79655) is the most general package for joining datasets, to my knowledge. It does efficient joins by predicates such as the one you need, and also supports DataFrames

> merge A and B based on (id = id, date1 \< date \< date2)

```julia
using FlexiJoins, IntervalSets
innerjoin((A, B), by_key(:id) & by_pred(:date, ∈, x -> x.date1..x.date2))

```

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [October 9, 2022, 12:30am UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/7 "2022-10-09T00:30:42Z")

</div>

x → x.date1…x.date2  
Does this range include date1 and date2?  
Thanks for this package. Is it as fast as DataFrames?

---

<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:** [October 9, 2022, 2:42pm UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/8 "2022-10-09T14:42:02Z")

</div>

> [@Yifan\_Liu](#):
>
> x → x.date1…x.date2  
> Does this range include date1 and date2?

Yes, this is a closed interval from [IntervalSets.jl](https://github.com/JuliaMath/IntervalSets.jl). See their documentation for other kinds of intervals - open and halfopen. They all are supported in `FlexiJoins`.

> [@Yifan\_Liu](#):
>
> Thanks for this package. Is it as fast as DataFrames?

I believe DataFrames efficiently optimize more cases on equijoins (join by key), but in simple cases the performance seems similar - see some (not documented) [benchmarks](https://aplavin.github.io/FlexiJoins.jl/test/benchmarks.html).  
As for nonequijoins, like the range join discussed here, only FlexiJoins support it directly. And this should be much faster than looping over all L-R pairs, filtering them.

---

<div class="post-metadata">

**Author:** ![Kia\_Kia](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kia_kia/32/35863_2.png) [@Kia\_Kia](https://discourse.julialang.org/u/Kia_Kia)\
**Post date:** [October 11, 2022, 9:02pm UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/9 "2022-10-11T21:02:39Z")

</div>

In package InMemoryDataSets, the function innerjoin performs exactly the same. In my experience, amongst other packages, it is the fastest and the most efficient one.

---

<div class="post-metadata">

**Author:** ![haberdashPI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/haberdashpi/32/26337_2.png) [@haberdashPI](https://discourse.julialang.org/u/haberdashPI)\
**Post date:** [October 13, 2022, 3:06am UTC](https://discourse.julialang.org/t/asof-join-support-in-dataframes-jl/88445/10 "2022-10-13T03:06:30Z")

</div>

[DataFrameIntervals.jl](https://github.com/beacon-biosignals/DataFrameIntervals.jl) is another option for this.