我在R中编写了一个嵌套的for循环,但是循环花费的时间太长,无法运行。我有两个大数据集。对于dfA中的每一行和dfB中的每一行,循环应查看dfA中的日期是否在dfB中的日期间隔内。如果是这样,则两个数据集应在该行的给定列上合并。我不确定我编写的代码是否可以正常工作,因为循环仍在运行。
任何见识将不胜感激。
dfA:
Common a Date
1 20141331123 1 2005-01-01
2 20141331123 2 2005-01-02
3 20141331123 3 2005-01-03
4 20141331123 4 2005-01-04
5 20141331123 5 2005-01-05
6 20141331123 6 2005-01-06
dfB:
cDate bDate common
1 2005-01-01 2005-06-13 20141331123
dfB$Interval <- interval(ymd(dfB$cDate), ymd(dfB$bDate))
library(lubridate)
for (i in 1:nrow(dfA)) {
for (i in 1:nrow(dfB)) {
if (dfA$Date[i] %within% dfB$Interval[i] == TRUE) {
merged <- merge(dfA, dfB, by.x = c("common"), by.y = c("Common"))
}
}
return(merged)
}
SQL本身和data.table
R内都支持非相等联接。基本R和tidyverse
函数都不在本地支持它[1]。
library(data.table)
setDT(dfA)
setDT(dfB)
dfB[dfA, on = .(common == Common, cDate <= Date, bDate >= Date)]
# cDate bDate common a
# 1: 2005-01-01 2005-01-01 20141331123 1
# 2: 2005-01-02 2005-01-02 20141331123 2
# 3: 2005-01-03 2005-01-03 20141331123 3
# 4: 2005-01-04 2005-01-04 20141331123 4
# 5: 2005-01-05 2005-01-05 20141331123 5
# 6: 2005-01-06 2005-01-06 20141331123 6
样本数据有点无趣,因为所有数据都适合在单个时间间隔内,但这也许可以用于您更多样化的数据。
[1]:由于SQL支持它,它在支持dbplyr
使用sql_on
。
数据:
dfA <- structure(list(Common = c("20141331123", "20141331123", "20141331123", "20141331123", "20141331123", "20141331123"), a = 1:6, Date = structure(c(12784, 12785, 12786, 12787, 12788, 12789), class = "Date")), row.names = c(NA, -6L), class = "data.frame")
dfB <- structure(list(cDate = structure(12784, class = "Date"), bDate = structure(12947, class = "Date"), common = "20141331123"), row.names = c(NA, -1L), class = "data.frame")
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句