CODEWITHSUNDEEP
rdplyrleft-join
r.user.05apr
r.user.05apr

Reputation: 5456

left_join (dplyr) using a function

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?

Upvotes: 1

Views: 2632

Answers (1)

yeedle
yeedle

Reputation: 5008

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

Upvotes: 1

Related Questions