Calculate a new variable based on matched condition from another variable in R

Viewed 28

I have two data frames, test1, test2. I need to make a new column in dataframe test1 which will calculate a difference in numbers to all rows in test2 with matching ID and where time difference meets some condition

I can do this by looping through test1 (e.g. by subsetting test1 by ID and then calculating time and number difference, but I am looking to do avoid loops. Below is an example using a loop.

test1 <- data.frame(ID=c("A","B","C"),Time=as.Date(c("2022-01-02","2022-02-02","2022-03-02")),Number=c(1,2,3))
test2 <- data.frame(ID=c("A","A","C","C","D"),Time=as.Date(c("2022-04-01","2022-04-03","2022-05-01","2022-06-01","2022-07-01")),Number=c(2,3,4,5,7))

test1$Difference <- NA


id <- unique(test1$ID)

for (i in id){
  
  temp <- test1[which(test1$ID==i),]
  temp2 <- as.numeric(difftime(temp$Time,test2[test2$ID==i,]$Time,units=c("weeks")))
  temp3 <- test2$Number[which(temp2<24)]
  
  test1$Difference[which(test1$ID==i)] <- min(temp$Number-temp3)
  

}

0 Answers
Related