Create "Yesterday's Value" variable for multiple time series

Question

I am working on a project in R, and I am a bit stuck. I have four time series in this format:

x <- data.frame(Id = rep(c(1,2,3,4),2), 
                Date = c(rep("1980-01-01",4), rep("1980-01-02",4)),
                Freq = c(2,3,1,2,4,5,2,3))

ID        Date        Freq
1   1980 - 01 - 01      2
2   1980 - 01 - 01      3
3   1980 - 01 - 01      1
4   1980 - 01 - 01      2
1   1980 - 01 - 02      4
2   1980 - 01 - 02      5  
3   1980 - 01 - 02      2
4   1980 - 01 - 02      3

My goal is to make a new variable that is simply yesterday's freq value for that group.

ID        Date        Freq   YestFreq
1   1980 - 01 - 01      2       NA
2   1980 - 01 - 01      3       NA
3   1980 - 01 - 01      1       NA
4   1980 - 01 - 01      2       NA 
1   1980 - 01 - 02      4       2
2   1980 - 01 - 02      5       3
3   1980 - 01 - 02      2       1
4   1980 - 01 - 02      3       2

My attempted solution is:

x$DateID = paste(x$ID, x$Date)
x$yesterday = as.Date(x$Date) - 1
x$YesterdayDateID = paste(x$ID, x$yesterday)

result = numeric(nrow(x))
for(i in 1:nrow(x)){
  answer = x$Freq[which(x$DateID == x$yesterdayDateID[i])]
  if(length(answer) != 0){result[i] = answer} else{result[i] = NA}
}
x = cbind(x, result)

My actual data set has ~ 600000 rows, (~300 Id and ~ 2000 unique dates) so my above solution takes a solid 2 hours to run. Any help would be greatly appreciated.

Answer 1

To take into account the possible yesterday gaps. I use match to identify the previous day. From that index then subset the target column by Id:

data.table

library(data.table)
setDT(x)[, Date := as.IDate(Date)][
, YestFreq := Freq[match(Date-1L, Date)], by=Id][]
#   Id       Date Freq YestFreq
# 1:  1 1980-01-01    2       NA
# 2:  2 1980-01-01    3       NA
# 3:  3 1980-01-01    1       NA
# 4:  4 1980-01-01    2       NA
# 5:  1 1980-01-02    4        2
# 6:  2 1980-01-02    5        3
# 7:  3 1980-01-02    2        1
# 8:  4 1980-01-02    3        2

dplyr

library(dplyr)
x$Date <- as.Date(x$Date)
x %>% group_by(Id) %>% mutate(YestFreq = Freq[match(Date - 1L, Date)])
#   Id       Date Freq YestFreq
# 1  1 1980-01-01    2       NA
# 2  2 1980-01-01    3       NA
# 3  3 1980-01-01    1       NA
# 4  4 1980-01-01    2       NA
# 5  1 1980-01-02    4        2
# 6  2 1980-01-02    5        3
# 7  3 1980-01-02    2        1
# 8  4 1980-01-02    3        2