CODEWITHSUNDEEP
r
QwerL
QwerL

Reputation: 31

How to get the value if a date from one dataset is within a period of time in another dataset for each id in R?

Suppose I have two datasets, A and B. For dataset A, it has ID, date and Interest. For dataset B, it has ID, date_1, date_2, Int. If date in dataset A is within the range of date_1 and date_2 in dataset B; then I want to extract the value Int in B to the column Interest in A. Here is the sample code that I run. But got error message of 

"Error in if (subset_A[j, ]$date >= subset_B[k, ]$date_1 & subset_A[j,  : 
  argument is of length zero"

.

A <- data.frame("ID" = c(1,1,1,2,2,3), "date" = c("1900-01-01","1900-11-01","1902-01-01","1903-01-01","1905-01-01","1900-01-01"), "Interest" = c(NA,NA,NA,NA,NA,NA), stringsAsFactors = FALSE)
A$date<-as.Date(A$date)
B <- data.frame("ID" = c(1,1,2,2,2,5), 
                "date_1" = c("1900-01-01","1900-02-01","1900-01-01","1901-02-01","1901-03-01","1900-01-01"),
                "date_2" = c("1900-01-03","1903-01-01","1901-01-01","1901-03-01","1904-03-01","1903-01-01"),
                "Int" = c(1,2,1,3,3,1))
B$date_1 <- as.Date(B$date_1)
B$date_2 <- as.Date(B$date_2)

In R:

IDlist = unique(A$ID)
Table=NULL
for (i in 1:length(IDlist)){
  subset_B <-subset(B, ID == IDlist[i])
  subset_A <-subset(A, ID == IDlist[i])
  for (j in 1:nrow(subset_A)){
    for (k in 1:nrow(subset_B)){
      if(subset_A[j,]$date >=  subset_B[k,]$date_1&
         subset_A[j,]$date <=  subset_B[k,]$date_2&
         !is.na(subset_B[k,]$date_1) & 
         !is.na(subset_B[k,]$date_2))
        subset_A[j,]$Interest <- subset_B[k,]$Int
      Table=rbind(Table,
                  subset_A)
    }
  } 
}

I want to get the data frame A with last column inputed as c(1,2,2,3,NA,NA). Not sure why the for loop is not working.Thank you!

Upvotes: 2

Views: 86

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 269654

1) Using SQL this can be expressed directly:

library(sqldf)
sqldf("select A.*, B.Int from A 
  left join B on A.ID = B.ID and A.date between B.date_1 and B.date_2")

giving:

  ID       date Interest Int
1  1 1900-01-01       NA   1
2  1 1900-11-01       NA   2
3  1 1902-01-01       NA   2
4  2 1903-01-01       NA   3
5  2 1905-01-01       NA  NA
6  3 1900-01-01       NA  NA

2) If you really want to use a loop then loop through the rows of A and for each one grab the corresponding element in B:

Table <- A
for(i in 1:nrow(A)) {
  ix <- which(A$ID[i] == B$ID & A$date[i] >= B$date_1 & A$date[i] <= B$date_2)[1]
  Table$Int[i] <- B$Int[ix]
}
Table

giving:

  ID       date Interest Int
1  1 1900-01-01       NA   1
2  1 1900-11-01       NA   2
3  1 1902-01-01       NA   2
4  2 1903-01-01       NA   3
5  2 1905-01-01       NA  NA
6  3 1900-01-01       NA  NA

Upvotes: 3

Uwe
Uwe

Reputation: 42544

With data.table's non-equi join and update in a join this becomes

library(data.table)
setDT(A)[, Interest := NULL][
  setDT(B), on = .(ID, date >= date_1, date <= date_2), Interest := Int][]

   ID       date Interest
1:  1 1900-01-01        1
2:  1 1900-11-01        2
3:  1 1902-01-01        2
4:  2 1903-01-01        3
5:  2 1905-01-01       NA
6:  3 1900-01-01       NA

Note that the Interest column had to be removed from A before the update join because it was initialized with NA which is of type logical while the replacement values are of type double and a vector column can hold data of one type only.

Upvotes: 5

akrun
akrun

Reputation: 887153

We can use fuzzyjoin

library(fuzzyjoin)
library(dplyr)
fuzzy_left_join(A, B, by = c('ID', 'date' = 'date_1', 'date' = 'date_2'),
           match_fun = list(`==`, `>=`, `<=`)) %>%
   transmute(ID = ID.x, date, Interest = Int)
#   ID       date Interest
#1  1 1900-01-01        1
#2  1 1900-11-01        2
#3  1 1902-01-01        2
#4  2 1903-01-01        3
#5  2 1905-01-01       NA
#6  3 1900-01-01       NA

Upvotes: 2

Related Questions