Asof merge in Julia

General Usage
,
11

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

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))
2 Likes