假设我有两个数据集,A 和 B。对于数据集 A,它具有 ID、日期和兴趣。对于数据集 B,它具有 ID、date_1、date_2、Int。如果数据集 A 中的日期在数据集 B 中的date_1和date_2范围内;然后我想将 B 中的值 Int 提取到对 A 的兴趣列中。这是我运行的示例代码。 但收到错误消息
"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)
在 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)
}
}
}
我想获取最后一列输入为 c(1,2,2,3,NA,NA( 的数据框 A。不知道为什么 for 循环不起作用。谢谢!
随着data.table
的非等值连接并在连接中更新,这将成为
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
请注意,在更新联接之前,必须从A
中删除Interest
列,因为它是使用逻辑类型的NA
初始化的,而替换值是双精度类型,并且向量列只能保存一种类型的数据。
1(使用SQL,可以直接表示:
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")
给:
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(如果你真的想使用循环,那么循环遍历A的行,对于每一行,在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
给:
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
我们可以使用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