Suppose I have this data frame:
times vals 1 1 2 2 3 4 3 7 6
set up with
foo <- data.frame(times=c(1,3,7), vals=c(2,4,6))
and I want this one:
times vals 1 1 2 2 2 2 3 3 4 4 4 4 5 5 4 6 6 4 7 7 6
That is, I want to fill in all the times from 1 to 7, and fill in the vals from the latest time that is not greater than the given time.
I have some code to do it using dplyr, but it is ugly. Suggestions for better?
library(dplyr) foo <- merge(foo, data.frame(times=1:max(foo$times)), all.y=TRUE) foo2 <- merge(foo, foo, by=c(), suffixes=c('', '.1')) foo2 <- foo2 %>% filter(is.na(vals) & !is.na(vals.1) & times.1 <= times) %>% group_by(times) %>% arrange(-times.1) %>% mutate(rn = row_number()) %>% filter(rn == 1) %>% mutate(vals = vals.1, rn = NULL, vals.1 = NULL, times.1 = NULL) foo <- merge(foo, foo2, by=c('times'), all.x=TRUE, suffixes=c('', '.2')) foo <- mutate(foo, vals = ifelse(is.na(vals), vals.2, vals), vals.2 = NULL)
Answer
This is a standard rolling join problem:
library(data.table)
setDT(foo)[.(1:7), on = 'times', roll = T]
# times vals
#1: 1 2
#2: 2 2
#3: 3 4
#4: 4 4
#5: 5 4
#6: 6 4
#7: 7 6
The above is for devel version (1.9.7+), which is smarter about column matching during joins. For 1.9.6 you still need to specify column name for the inner table:
setDT(foo)[.(times = 1:7), on = 'times', roll = T]
Attribution
Source : Link , Question Author : dfrankow , Answer Author : eddi