# Functions to merge DataFrames by nearest date that is earlier?

**URL:** <https://discourse.julialang.org/t/functions-to-merge-dataframes-by-nearest-date-that-is-earlier/73195>\
**Category:** Data\
**Tags:** dataframes\
**Created:** [December 16, 2021, 10:54am UTC](https://discourse.julialang.org/t/functions-to-merge-dataframes-by-nearest-date-that-is-earlier/73195 "2021-12-16T10:54:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [December 16, 2021, 10:54am UTC](https://discourse.julialang.org/t/functions-to-merge-dataframes-by-nearest-date-that-is-earlier/73195/1 "2021-12-16T10:54:20Z")

</div>

Say I have two dataframes but I wish to join them such that `val2` from Table 2 is merge on to Table 1 if and only if Table2.date \< Table1.date and Table2.date is the closest to Table1.date

**Table 1**

```julia
date, val1
2021-12-16, 1
2021-12-16, 2
2021-12-16, 3

```

**Table 2**

```julia
date, val2
2021-12-14, 1
2021-12-15, 2
2021-12-16, 3

```

In the above the result table after the merge will be

**Result**

```julia
date, val1, val2
2021-12-16, 1, 2
2021-12-16, 2, 2
2021-12-16, 3, 2

```

This type of join is quite tricky, so I am wondering if a package exists for this already.

---

<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:** [December 16, 2021, 12:13pm UTC](https://discourse.julialang.org/t/functions-to-merge-dataframes-by-nearest-date-that-is-earlier/73195/2 "2021-12-16T12:13:55Z")

</div>

One way:  
_(edited function inputs including dataframe columns as symbols)_

```julia
using DataFrames, Dates

function joinclosestolder(df1, df2, date1::Symbol, date2::Symbol, val2::Symbol)
    if any(df1.date > df2.date)
        d2 = [maximum(df2[:,date1][df2[:,date2] .< d1]) for d1 in df1[:,date1]]
        df1[!,val2] = [df2[:,val2][argmax(d .== df2[:,date2])] for d in d2]
    end
    return df1
end

df1 = DataFrame(date = Date.(["2021-12-16","2021-12-16","2021-12-16"]), val1 = [1,2,3])
df2 = DataFrame(date = Date.(["2021-12-14","2021-12-15","2021-12-16"]), val2 = [1,2,3])
joinclosestolder(df1, df2, :date, :date, :val2)

3×3 DataFrame
 Row │ date val1 val2  
     │ Date Int64 Int64
─────┼──────────────────────────
   1 │ 2021-12-16 1 2
   2 │ 2021-12-16 2 2
   3 │ 2021-12-16 3 2

```

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [December 16, 2021, 1:36pm UTC](https://discourse.julialang.org/t/functions-to-merge-dataframes-by-nearest-date-that-is-earlier/73195/3 "2021-12-16T13:36:46Z")

</div>

Similar previous discussion here:

> [@Fuzzy inexact merge](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666/4):
>
> This is not apparent to me from the code — sorry if I missed something.

---

<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:** [December 19, 2021, 11:12pm UTC](https://discourse.julialang.org/t/functions-to-merge-dataframes-by-nearest-date-that-is-earlier/73195/4 "2021-12-19T23:12:10Z")

</div>

It is not completely clear to me how the logic of the join is in the most geneal case of several different dates between df1 and df2.  
Does something like the following make your logic?

```julia
using DataFrames, Dates

df1=DataFrame(date=Date.(["2021-12-16","2021-12-16","2021-12-16","2021-12-17","2021-12-17","2021-12-17"]), 
              val1=[1,2,3,1,2,3])

df2=DataFrame(date=Date.(["2021-12-14","2021-12-15","2021-12-16"]), val2=[1,2,3])

g=groupby(df1,:date)

transform(g, :date=>(d->df2[df2.date .== maximum(filter(x-> x < d[1], df2.date)),:].val2[1])=>:val2)

#### or ####

transform(g, :date=>(d->only(df2[df2.date .== maximum(filter(x-> x < d[1], df2.date)),:].val2))=>:val2)

transform(g, :date=>(d->only(df2[df2.date .== maximum(filter(x-> x < d[1], df2.date)),:]))=>[:d2,:val2])

transform(g, :date=>(d->values(only(df2[df2.date .== maximum(filter(x-> x < d[1], df2.date)),:])))=>:t_d_v)

transform(g, :date=>(d->df2[argmax(filter(x-> x < d[1], df2.date)),:val2])=>:v2)

```

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 20, 2021, 3:58am UTC](https://discourse.julialang.org/t/functions-to-merge-dataframes-by-nearest-date-that-is-earlier/73195/5 "2021-12-20T03:58:52Z")

</div>

What you’re trying to do sounds like an “as of” join, which is used very commonly when working with financial time series. Normally it’s not implemented with strict inequality as you described, but a simple shift of the time series will change that.

It can be implemented quite efficiently when the 2nd table is sorted. There is already an open issue for Dataframes.jl and I’m sure they’d be interested in a pull request:  
[https://github.com/JuliaData/DataFrames.jl/issues/2738](https://github.com/JuliaData/DataFrames.jl/issues/2738)

There seems to be an implementation already for IndexedTables, but I’ve not used it:  
[https://juliadb.juliadata.org/latest/api/#IndexedTables.asofjoin-Tuple{NDSparse,NDSparse}](https://juliadb.juliadata.org/latest/api/#IndexedTables.asofjoin-Tuple%7BNDSparse,NDSparse%7D)