Suppose I have two data frames such like:
set.seed(123)
df1<-data.frame(bmi=rnorm(20, 25, 5),
date1=sample(seq.Date(as.Date("2014-01-01"),
as.Date("2014-02-28"),by="day"), 20))
df2<-data.frame(epi=1:5,
date2=as.Date(c("2014-1-8", "2014-1-15", "2014-1-28",
"2014-2-05", "2014-2-24")))
My question is how to match bmi
with epi
where date1 are nearest before or on the date2
? A result such like this:
epi date2 bmi date1
1 1 2014-01-08 33.58 2014-01-08
2 2 2014-01-15 22.64 2014-01-15
3 3 2014-01-28 22.22 2014-01-26
4 4 2014-02-05 15.17 2014-02-01
5 5 2014-02-24 27.49 2014-02-15
One way is to use the roll=Inf
feature from the data.table
package as follows:
require(data.table) ## >= 1.9.2
setDT(df1) ## convert to data.table by reference
setDT(df2) ## same
df1[, date := date1] ## create a duplicate of 'date1'
setkey(df1, date1) ## set the column to perform the join on
setkey(df2, date2) ## same as above
ans = df1[df2, roll=Inf] ## perform rolling join
## change names and set column order as required, by reference
setnames(ans, c('date','date1'), c('date1','date2'))
setcolorder(ans, c('epi', 'date1', 'bmi', 'date2'))
> ans
# epi date1 bmi date2
#1: 1 2014-01-08 33.57532 2014-01-08
#2: 2 2014-01-15 22.63604 2014-01-15
#3: 3 2014-01-26 22.22079 2014-01-28
#4: 4 2014-02-01 15.16691 2014-02-05
#5: 5 2014-02-15 27.48925 2014-02-24
Here a way with base R
# get time differences
temp <- outer(df2$date2, df1$date1, "-")
# remove where date1 are after date2
temp[temp < 0] <- NA
# find index of minimum
ind <- apply(temp, 1, function(i) which.min(i))
# output
df2 <- cbind(df2, df1[ind,])
An alternative based on finding the index of the closest date
library(tidyverse)
# Function to get the index specifying closest or after
Ind_closest_or_after <- function(d1, d2){
which.min(ifelse(d1 - d2 < 0, Inf, d1 - d2))
}
# Calculate the indices
closest_or_after_ind <- map_int(.x = df2$date2, .f = Ind_closest_or_after, d2 = df1$date1)
# Add index columns to the data frames and join
df1 <- df1 %>%
mutate(ind = 1:nrow(df1))
df2 <- df2 %>%
mutate(ind = closest_or_after_ind)
left_join(df2, df1, by = 'ind')
Also check survival::neardate
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With