# Fuzzy inexact merge
**URL:** https://discourse.julialang.org/t/fuzzy-inexact-merge/43666
**Category:** General Usage
**Created:** [July 25, 2020, 12:57pm UTC](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666 "2020-07-25T12:57:34Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@bernhard](https://discourse.julialang.org/u/bernhard)
#### Post date: [July 25, 2020, 12:57pm UTC](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666/1 "2020-07-25T12:57:35Z")
I am looking for a tool/approach to perform an inexact merge. I have two (or more) dataframes with datetime columns (and value columns) that do not correspond.
I imagine specifying a tolerance value (say 5 minutes) for the merge.
Is there a package (or a best practice approach) that can help me with this?
I realize that this needs some assumptions/simplifications (about missing data, about which element to pick if several are are “close” or equally close, etc.)
Background: I want to visualize homeassistant data from multiple sensors (table „states“) in grafana. I understand that grafana can only access a single table for one graph.
EDIT: added MWE below.
```julia
using DataFrames
using CSV
url="""https://gist.githubusercontent.com/kafisatz/ed1f42fb4b5fd1ce1cf3a8a12b8e80b6/raw/70df7040c550bbdedd168c90a78caf4ee2808881/hadata.csv"""
fi = download(url)
df0 = CSV.File(fi) |> DataFrame
df1 = filter(x->x.entity_id == "kitchen_temperature",df0)
df2 = filter(x->x.entity_id == "buero_temperature",df0)
#how to merge these on datetime
#I have an approach, but it is very verbose (and feels somewhat complex)
?leftjoin
```
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [July 25, 2020, 1:52pm UTC](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666/2 "2020-07-25T13:52:03Z")
The quick & dirty solution would be binning to 5-minute blocks then. Of course you lose some matches that span block boundaries.
As a more involved but still quite easy solution, I could also imagine
1. taking all `unique` timestamps from one dataframe,
2. sorting them, call this `ts`
3. for each timestamp in the _other_ dataframe, look up the closest one in `ts` and replace it with that if the distance is \<5min, otherwise leave as is
4. then join
---
### Author:  [@bernhard](https://discourse.julialang.org/u/bernhard)
#### Post date: [July 25, 2020, 2:05pm UTC](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666/3 "2020-07-25T14:05:13Z")
Thanks. 1 to 4 is pretty much what I sketched already.
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [July 25, 2020, 2:23pm UTC](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666/4 "2020-07-25T14:23:47Z")
This is not apparent to me from the code — sorry if I missed something.
---
### Author:  [@gcalderone](https://discourse.julialang.org/u/gcalderone)
#### Post date: [July 29, 2020, 9:11am UTC](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666/5 "2020-07-29T09:11:16Z")
The [SortMerge.jl](https://github.com/gcalderone/SortMerge.jl) package is designed exactly for this purpose:
```julia
using DataFrames, CSV, SortMerge, Dates
url="""https://gist.githubusercontent.com/kafisatz/ed1f42fb4b5fd1ce1cf3a8a12b8e80b6/raw/70df7040c550bbdedd168c90a78caf4ee2808881/hadata.csv"""
fi = download(url)
df0 = CSV.File(fi) |> DataFrame
df1 = filter(x->x.entity_id == "kitchen_temperature",df0)
df2 = filter(x->x.entity_id == "buero_temperature",df0)
j = sortmerge(df1.datetime, df2.datetime, Minute(5),
sd=(v1, v2, i1, i2, threshold) -> begin
diff = v1[i1] - v2[i2]
(abs(diff) > threshold) && (return sign(diff))
return 0
end)
# Rows common to both dataframes (i.e., inner join)
tmp = hcat(df1[j[1], :], df2[j[2], :], makeunique=true)
# Print maximum clock difference between matched rows
println(Minute(maximum(abs.(df1[j[1], :datetime] .- df2[j[2], :datetime]))))
# Rows from first dataframe without matching rows in the second one:
tmp = df1[countmatch(j, 1) .== 0, :]
# Rows from second dataframe without matching rows in the first one:
tmp = df2[countmatch(j, 2) .== 0, :]
```
---
### Author:  [@bernhard](https://discourse.julialang.org/u/bernhard)
#### Post date: [July 29, 2020, 11:04am UTC](https://discourse.julialang.org/t/fuzzy-inexact-merge/43666/6 "2020-07-29T11:04:38Z")
> [@gcalderone](#):
>
> ```julia
> df1 = filter(x->x.entity_id == "kitchen_temperature",df0)
> df2 = filter(x->x.entity_id == "buero_tempera
>
> ```
ah thank you.
After running into [GitHub - giordano/StarWarsArrays.jl: Arrays indexed as the order of Star Wars movies](https://github.com/giordano/StarWarsArrays.jl) the other day, I am rather convinced that there is a Julia package for everything 🙂