left_join (dplyr) using a function

Question

I would like to dplyr::left_join using a function and rename a variable.

This is the data:

library(dplyr)
t<-tibble(Product=rep(c('A','B','C'),each=15),
      Date=rep(seq(as.Date("2010-01-01"),by="month",length.out=15),times=3),
      Qty=round(rnorm(45,100,10),1))

This is the function I would like to use:

# increment 'startdate' by 'shift'-months
library(lubridate)
monthinc<-function(startdate,shift) {
  y<-year(startdate); m<-month(startdate); d<-day(startdate)
  y<-y+(m+shift-1) %/% 12
  m<-ifelse(((m+shift) %% 12)==0,12,(m+shift) %% 12)
  as.Date(paste0(y,"-",m,"-",d))
}

This is how far I got on my own:

left_join(t,t,by=c("Product","Date"))

# left_join should have the effect of this SQL-statement:
# -------------------------------------------------------
# select d1.*,d2.Qty As Lag1_Qty
# from t d1
# left join t d2
#   on d1.Product=d2.Product
#     AND d1.Date=monthinc(d2.Date,-1)

How can I reproduce the SQL-statement from above using left_join?

AFAIK this feature is not supported by any of the *_join functions. — nrussell
I think I've got it. Thanks. my.lag<-1 t.new<-left_join(t, transmute(t, Product, Date=monthinc(Date,my.lag), Qty_Lag=Qty), by=c("Product","Date")) View(t.new) — r.user.05apr

yeedle yeedle · Accepted Answer · 2017-03-19T06:14:41

Should be simple if you just add it as a column before left joining

t <- t %>% mutate(monthinc = monthinc(Date,-1)) 

left_join(t,t,by=c("Product","Date"="monthinc"))

left_join (dplyr) using a function

1 Answers