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() using DataFrames df1 = DataFrame( date = DateTime.(["2022-02-20T08:05:22", "2022-02-20T08:05:42", …

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? gdate1=groupby(rightjoin(df1,df2,on=:equipment_id, makeunique=true),:date_1) subset(gdate1, [:date,:date_1]=>(x,y)->minimum(abs.(x-…

What is the threshold for a match on the date columns? If multiple match occurs, how do you choose the best?

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.

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 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 othe…

@ameresv , if you could answer @gcalderone ’s questions he might be able to provide a Julia solution with his package.

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.

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.

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

I found this Which is pretty the same what I want to achieve.

[image] 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

Asof merge in Julia

General Usage

rafael.guerra February 23, 2022, 8:24am 2

Check this solution using the SortMerge.jl package, it should help.

Topic		Replies	Views
Functions to merge DataFrames by nearest date that is earlier? Data dataframes	4	1567	December 20, 2021
Fuzzy inexact merge General Usage	5	1586	July 29, 2020
Merge dataframes where one value is between two others General Usage question	7	11970	November 22, 2020
Join Dataframes on different IDs within date range New to Julia query , dataframes	1	523	October 4, 2020
Asof join support in DataFrames.jl Data dataframes	9	1006	October 13, 2022

Asof merge in Julia

Related topics