范围连接data.frames - 具有日期范围/间隔的特定日期列在R中 [英] Range join data.frames - specific date column with date ranges/intervals in R
问题描述
虽然这些的细节，当然，应用程序的具体，在SO精神，我试图保持这一般尽可能！基本问题是如何在一个data.frame具有特定日期并且另一个具有日期范围时通过日期合并data.frames。其次，问题问如何处理给定变量的多重观察，以及如何将它们包括在最终输出数据中。我相信有些是标准的，但一个相当完整的搜索显示很少。
Although the details of this are, of course, app specific, in the SO spirit I'm trying to keep this as general as possible! The basic problem is how to merge data.frames by date when one data.frame has specific dates and the other has a date-range. Secondly, the question asks how to deal with multiple observations of a given variable, and how to include these in a final output data.frame. I'm sure some of this is standard, but an pretty full search has revealed little.
我想要合并的mre对象如下。
The mre objects I'm trying to merge are below.
# 'Speeches' data.frame
structure(list(Name = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("BBB",
"AAA"), class = "factor"), Date = structure(c(12543, 12404, 12404,
12404, 12373, 12362, 12345, 12320, 12207, 15450, 15449, 15449,
15449, 15449, 15449, 15449, 15449, 15448, 15448, 15448), class = "Date")), .Names = c("Name",
"Date"), row.names = c("1", "1.1", "1.2", "1.3", "1.4", "1.5",
"1.6", "1.7", "1.8", "2", "2.1", "2.2", "2.3", "2.4", "2.5",
"2.6", "2.7", "2.8", "2.9", "2.10"), class = "data.frame")
# 'History' data.frame
structure(list(Name = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 1L,
1L, 1L, 1L, 1L, 1L, 1L), .Label = c("BBB", "AAA"), class = "factor"),
Role = structure(c(1L, 2L, 3L, 3L, 3L, 4L, 1L, 2L, 3L, 3L,
3L, 3L, 4L), .Label = c("Political groups", "National parties",
"Member", "Substitute", "Vice-Chair", "Chair", "Vice-President",
"Quaestor", "President", "Co-President"), class = "factor"),
Value = structure(c(10L, 12L, 6L, 3L, 8L, 4L, 9L, 11L, 1L,
7L, 1L, 2L, 5L), .Label = c("a", "b", "c", "d", "e", "f",
"g", "h", "i", "j", "k", "l", "m", "n", "o"), class = "factor"),
Role.Start = structure(c(12149, 12149, 12150, 12150, 12152,
12150, 14439, 14439, 14441, 14503, 15358, 15411, 14441), class = "Date"),
Role.End = structure(c(12618, 12618, 12618, 12618, 12538,
12618, 15507, 15507, 15357, 15507, 15410, 15507, 15357), class = "Date")), .Names = c("Name",
"Role", "Value", "Role.Start", "Role.End"), row.names = c(NA,
13L), class = "data.frame")
面对。
1）尽管在演讲和历史数据中都有日期信息，但在第一个我有每个条目的具体日期，而在第二个日期，范围。理想情况下，我希望能够合并，以便每个语音条目都与讲话人（'姓名'）和演讲日期落入的历史记录条目匹配。
1) Although there is date information in both the speeches and history data, in the first I have specific dates for each entry, and in the second there is a date-range. Ideally, I would like to be able to merge so that each speech entry is matched with both the speaker ('Name') and the history entry into which the speech date falls.
2）期望的输出是具有data.frame或data.table，其行等于在语音data.frame中的观察值，以及Name，Date和每个角色的列（将由值填充）。但是，某些角色在给定的日期会出现多次，因此我需要为这些实例创建多个列。
2) The desired output is to have a data.frame or data.table with rows equal to the observations in the speeches data.frame, and columns for Name, Date, and each of the Roles (which will be populated by value). However, some Roles appear multiple times for a given speaker, on a given date, and thus I need to be able to create multiple columns for these instances.
下面的对象给出这个输出，但是使用一个可怕的脆弱和非常慢的for循环构造：
The object below gives this output, but was constructed using a horribly fragile and very slow for-loop:
structure(list(Name = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("BBB",
"AAA"), class = "factor"), Date = structure(c(12543, 12404, 12404,
12404, 12373, 12362, 12345, 12320, 12207, 15450, 15449, 15449,
15449, 15449, 15449, 15449, 15449, 15448, 15448, 15448), class = "Date"),
`Political groups` = structure(c(2L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("i",
"j"), class = "factor"), `National parties` = structure(c(2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L), .Label = c("k", "l"), class = "factor"),
Member.1 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("f",
"g"), class = "factor"), Member.2 = structure(c(2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L), .Label = c("b", "c"), class = "factor"), Member.3 = structure(c(NA,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA), .Label = "h", class = "factor"), Substitute = structure(c(1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA), .Label = "d", class = "factor")), .Names = c("Name",
"Date", "Political groups", "National parties", "Member.1", "Member.2",
"Member.3", "Substitute"), row.names = c("1", "1.1", "1.2", "1.3",
"1.4", "1.5", "1.6", "1.7", "1.8", "2", "2.1", "2.2", "2.3",
"2.4", "2.5", "2.6", "2.7", "2.8", "2.9", "2.10"), class = "data.frame")
欢迎任何帮助和/或如何改进此问题的意见！ / p>
Any help and/or comments on how to improve this question would be welcome!
推荐答案
更新：在v1.9.3 +中，现在实施。这是一种特殊情况，其中开始和结束 Date 在 Speeches 中相同。我们可以使用 foverlaps（），如下所示：
Update: In v1.9.3+, now overlap joins are implemented. This is a special case where start and end Date are identical in Speeches. We can accomplish this using foverlaps() as follows:
require(data.table) ## 1.9.3+
setDT(Speeches)
setDT(History)
Speeches[, `:=`(Date2 = Date, id = .I)]
setkey(History, Name, Role.Start, Role.End)
ans = foverlaps(Speeches, History, by.x=c("Name", "Date", "Date2"))[, Date2 := NULL]
ans = ans[order(id, Value)][, N := 1:.N, by=list(Name, Date, Role, id)]
ans = dcast.data.table(ans, id+Name+Date ~ Role+N, value.var="Value")
这是范围/间隔连接的情况。
This is a case for range/interval join.
这是 data.table 方式。它使用两个滚动连接。
Here's the data.table way. It uses two rolling joins.
require(data.table) ## 1.9.2+
dt1 = as.data.table(Speeches)
dt2 = as.data.table(History)
# first rolling join - to get end indices
setkey(dt2, Name, Role.Start)
tmp1 = dt2[dt1, roll=Inf, which=TRUE]
# second rolling join - to get start indices
setkey(dt2, Name, Role.End)
tmp2 = dt2[dt1, roll=-Inf, which=TRUE]
# generate dt1's and dt2's corresponding row indices
idx = tmp1-tmp2+1L
idx1 = rep(seq_len(nrow(dt1)), idx)
idx2 = data.table:::vecseq(tmp2, idx, sum(idx))
dt1[, id := 1:.N] ## needed for casting later
# subset using idx1 and idx2 and bind them colwise
ans = cbind(dt1[idx1], dt2[idx2, -1L, with=FALSE])
# a little reordering to get the output correctly (factors are a pain!)
ans = ans[order(id,Value)][, N := 1:.N, by=list(Name, Date, Role, id)]
# finally cast them.
f_ans = dcast.data.table(ans, id+Name+Date ~ Role+N, value.var="Value")
以下是输出结果：
id Name Date Political groups_1 National parties_1 Member_1 Member_2 Member_3 Substitute_1
1: 1 AAA 2004-05-05 j l c f NA d
2: 2 AAA 2003-12-18 j l c f h d
3: 3 AAA 2003-12-18 j l c f h d
4: 4 AAA 2003-12-18 j l c f h d
5: 5 AAA 2003-11-17 j l c f h d
6: 6 AAA 2003-11-06 j l c f h d
7: 7 AAA 2003-10-20 j l c f h d
8: 8 AAA 2003-09-25 j l c f h d
9: 9 AAA 2003-06-04 j l c f h d
10: 10 BBB 2012-04-20 i k b g NA NA
11: 11 BBB 2012-04-19 i k b g NA NA
12: 12 BBB 2012-04-19 i k b g NA NA
13: 13 BBB 2012-04-19 i k b g NA NA
14: 14 BBB 2012-04-19 i k b g NA NA
15: 15 BBB 2012-04-19 i k b g NA NA
16: 16 BBB 2012-04-19 i k b g NA NA
17: 17 BBB 2012-04-19 i k b g NA NA
18: 18 BBB 2012-04-18 i k b g NA NA
19: 19 BBB 2012-04-18 i k b g NA NA
20: 20 BBB 2012-04-18 i k b g NA NA
使用来自bioconductor的 GenomicRanges 包，它处理Ranges相当不错，特别是当你需要一个额外的列来加入（ Name ）。您可以从此处安装。
Alternatively you can also accomplish this using GenomicRanges package from bioconductor, which deals with Ranges quite nicely, especially when you require an additional column to join by (Name) in addition to the ranges. You can install it from here.
require(GenomicRanges)
require(data.table)
dt1 <- as.data.table(Speeches)
dt2 <- as.data.table(History)
gr1 = GRanges(Rle(dt1$Name), IRanges(as.numeric(dt1$Date), as.numeric(dt1$Date)))
gr2 = GRanges(Rle(dt2$Name), IRanges(as.numeric(dt2$Role.Start), as.numeric(dt2$Role.End)))
olaps = findOverlaps(gr1, gr2, type="within")
idx1 = queryHits(olaps)
idx2 = subjectHits(olaps)
# from here, you can do exactly as above
dt1[, id := 1:.N]
...
...
dcast.data.table(ans, id+Name+Date ~ Role+N, value.var="Value")
得到与上述相同的结果。
Gives the same result as above.
这篇关于范围连接data.frames - 具有日期范围/间隔的特定日期列在R中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
