# Asof merge in Julia
**URL:** https://discourse.julialang.org/t/asof-merge-in-julia/76948
**Category:** General Usage
**Tags:** dataframes, merge
**Created:** [February 23, 2022, 1:12am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948 "2022-02-23T01:12:08Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@ameresv](https://discourse.julialang.org/u/ameresv)
#### Post date: [February 23, 2022, 1:12am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/1 "2022-02-23T01:12:08Z")
Hi, I am trying to do an ‘as of’ merge in Julia with two large Dataframes, based on the time and the equipment\_id provided. As a manner of example, let me introduce the two df.head()
```julia
using DataFrames
df1 = DataFrame(
date = DateTime.(["2022-02-20T08:05:22", "2022-02-20T08:05:42",
"2022-02-20T08:05:52", "2022-02-20T08:07:32",
"2022-02-20T08:07:32", "2022-02-20T08:08:05"]),
equipment_id = 165,
loaded = "f",
percent_grade = [0,-1,-3,-1,0,-8]
)
df2 = DataFrame(
date = DateTime.(["2022-02-20T08:05:24","2022-02-20T08:05:29",
"2022-02-20T08:05:34","2022-02-20T08:05:39",
"2022-02-20T08:05:44","2022-02-20T08:05:49"]),
truck = "T67",
equipment_id = 165,
value = [149.85,55.85,0,61.9,81.8,0]
)
```
Where the expected result has to looks like this:
```julia
df3 = DataFrame(
date = DateTime.(["2022-02-20T08:05:24","2022-02-20T08:05:29",
"2022-02-20T08:05:34","2022-02-20T08:05:39",
"2022-02-20T08:05:44","2022-02-20T08:05:49"]),
truck = "T67",
equipment_id = 165,
loaded = "f",
value = [149.85,55.85,0,61.9,81.8,0],
percent_grade = [0,0,0,0,-1,-1]
)
```
As you noted, the “date” column present in both DataFrames aren’t able me to do an exact match join. That’s why I am trying to do an as of merge. The closest solution that I have found is [this](https://stackoverflow.com/questions/66629351/asof-join-with-julia-data-tools). However, the “double cursor” thing makes the problem even more complicated.
Hope you can help me with this.
Thanks in advance.
Regards
---
### Author:  [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)
#### Post date: [February 23, 2022, 8:24am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/2 "2022-02-23T08:24:33Z")
Check [this solution](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666/5) using the `SortMerge.jl` package, it should help.
---
### Author:  [@gcalderone](https://discourse.julialang.org/u/gcalderone)
#### Post date: [February 23, 2022, 9:30am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/3 "2022-02-23T09:30:49Z")
What is the threshold for a match on the date columns?
If multiple match occurs, how do you choose the best?
---
### Author:  [@ameresv](https://discourse.julialang.org/u/ameresv)
#### Post date: [February 24, 2022, 1:32am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/4 "2022-02-24T01:32:56Z")
Thanks for the suggestion, but it did not deliver the expected result. I do also tried to understand the code and the documentation provided, still is too far from what I want.
---
### Author:  [@Palli](https://discourse.julialang.org/u/Palli)
#### Post date: [February 24, 2022, 8:51am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/5 "2022-02-24T08:51:10Z")
FYI: There’s a feature request to add asof to DataFrames.jl and it was put on the 1. milestone:
[https://github.com/JuliaData/DataFrames.jl/issues/2738](https://github.com/JuliaData/DataFrames.jl/issues/2738)
You can also do it in Pandas in by calling with PythonCall.jl or PyCall.jl, or with the Pandas.jl wrapper I guess.
Also might be helpful (but others seem to have pointed to a package that would be easier?) [asof join with Julia data tools - Stack Overflow](https://stackoverflow.com/questions/66629351/asof-join-with-julia-data-tools)
---
### Author:  [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)
#### Post date: [February 24, 2022, 10:20am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/6 "2022-02-24T10:20:18Z")
@ameresv, if you could answer @gcalderone’s questions he might be able to provide a Julia solution with his package.
---
### Author:  [@tbeason](https://discourse.julialang.org/u/tbeason)
#### Post date: [February 24, 2022, 1:43pm UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/7 "2022-02-24T13:43:45Z")
I was not aware of this type of merge as a function in other languages, but seems it does exist. I have done something similar manually but it involved computing the minimum distance between the timestamps.
---
### Author:  [@ameresv](https://discourse.julialang.org/u/ameresv)
#### Post date: [March 2, 2022, 1:03am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/8 "2022-03-02T01:03:04Z")
You are right! In most cases, I use “xlookup” from excel; however, the amount of data used here makes it way difficult to handle for them.
---
### Author:  [@ameresv](https://discourse.julialang.org/u/ameresv)
#### Post date: [March 2, 2022, 1:05am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/9 "2022-03-02T01:05:57Z")
Thanks for the question:
I would rather use a 5 seconds threshold. And in case to have multiple matches, the next item has to be used in this case.
Regards
---
### Author:  [@ameresv](https://discourse.julialang.org/u/ameresv)
#### Post date: [March 2, 2022, 3:39pm UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/10 "2022-03-02T15:39:31Z")
I found [this](https://stackoverflow.com/questions/65886871/join-by-nearest-date-for-the-table-with-duplicate-records-in-bigquery) Which is pretty the same what I want to achieve.
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [March 2, 2022, 6:46pm UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/11 "2022-03-02T18:46:04Z")
I don’t get the result you expect, but I don’t know how else to interpret the conditions you described in a generic way.
Could something like this be right for you?
```julia
gdate1=groupby(rightjoin(df1,df2,on=:equipment_id, makeunique=true),:date_1)
subset(gdate1, [:date,:date_1]=>(x,y)->minimum(abs.(x-y)).==abs.(x-y))
julia> subset(gdate1, [:date,:date_1]=>(x,y)->minimum(abs.(x-y)).==abs.(x-y))
6×7 DataFrame
Row │ date equipment_id loaded percent_grade date_1 truck value
│ DateTime? Int64 String? Int64? DateTime String Float64
─────┼─────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 2022-02-20T08:05:22 165 f 0 2022-02-20T08:05:24 T67 149.85
2 │ 2022-02-20T08:05:22 165 f 0 2022-02-20T08:05:29 T67 55.85
3 │ 2022-02-20T08:05:42 165 f -1 2022-02-20T08:05:34 T67 0.0
4 │ 2022-02-20T08:05:42 165 f -1 2022-02-20T08:05:39 T67 61.9
5 │ 2022-02-20T08:05:42 165 f -1 2022-02-20T08:05:44 T67 81.8
6 │ 2022-02-20T08:05:52 165 f -3 2022-02-20T08:05:49 T67 0.0
```
or maybe that’s what you’re looking for
```julia
julia> subset(gdate1, [:date,:date_1]=>(x,y)->maximum(filter(<=(Second(0)),x-y)).==x-y)
6×7 DataFrame
Row │ date equipment_id loaded percent_grade date_1 truck value
│ DateTime? Int64 String? Int64? DateTime String Float64
─────┼─────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 2022-02-20T08:05:22 165 f 0 2022-02-20T08:05:24 T67 149.85
2 │ 2022-02-20T08:05:22 165 f 0 2022-02-20T08:05:29 T67 55.85
3 │ 2022-02-20T08:05:22 165 f 0 2022-02-20T08:05:34 T67 0.0
4 │ 2022-02-20T08:05:22 165 f 0 2022-02-20T08:05:39 T67 61.9
5 │ 2022-02-20T08:05:42 165 f -1 2022-02-20T08:05:44 T67 81.8
6 │ 2022-02-20T08:05:42 165 f -1 2022-02-20T08:05:49 T67 0.0
```
or this
```julia
subset(gdate1, [:date,:date_1]=>(x,y)->closestlower(x,y[1]).==x)
#where
closestlower(x,l)=reduce((c,sup)-> c< sup <= l ? sup : c ,x, init=typemin(l))
```
---
### Author:  [@ameresv](https://discourse.julialang.org/u/ameresv)
#### Post date: [March 18, 2022, 12:56pm UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/12 "2022-03-18T12:56:35Z")
> [@rocco\_sprmnt21](#):
>
> `closestlower(x,l)=reduce((c,sup)-> c< sup <= l ? sup : c ,x, init=typemin(l))`
Such a good expression. Thanks a lot for that and sorry for the delay, I am on the mine site with internet problems.
Regards
---
### Author:  [@DataFrames](https://discourse.julialang.org/u/DataFrames)
#### Post date: [March 23, 2022, 4:38am UTC](https://discourse.julialang.org/t/asof-merge-in-julia/76948/13 "2022-03-23T04:38:48Z")
use `closejoin` or `closejoin!` function in `InMemoryDatasets`
```julia
using InMemoryDatasets
df1 = Dataset(
date = DateTime.(["2022-02-20T08:05:22", "2022-02-20T08:05:42",
"2022-02-20T08:05:52", "2022-02-20T08:07:32",
"2022-02-20T08:07:32", "2022-02-20T08:08:05"]),
equipment_id = 165,
loaded = "f",
percent_grade = [0,-1,-3,-1,0,-8]
)
df2 = Dataset(
date = DateTime.(["2022-02-20T08:05:24","2022-02-20T08:05:29",
"2022-02-20T08:05:34","2022-02-20T08:05:39",
"2022-02-20T08:05:44","2022-02-20T08:05:49"]),
truck = "T67",
equipment_id = 165,
value = [149.85,55.85,0,61.9,81.8,0]
)
df3 = Dataset(
date = DateTime.(["2022-02-20T08:05:24","2022-02-20T08:05:29",
"2022-02-20T08:05:34","2022-02-20T08:05:39",
"2022-02-20T08:05:44","2022-02-20T08:05:49"]),
truck = "T67",
equipment_id = 165,
loaded = "f",
value = [149.85,55.85,0,61.9,81.8,0],
percent_grade = [0,0,0,0,-1,-1]
)
closejoin(df2, df1, on = [:equipment_id => :equipment_id, :date => :date])
```