Range join data.frames - конкретная колонка даты с диапазонами дат/интервалами в R
Хотя детали этого, конечно же, специфичны для приложения, в духе SO я стараюсь держать это как можно более общим! Основная проблема заключается в том, как объединить data.frames по дате, когда один data.frame имеет определенные даты, а другой имеет диапазон дат. Во-вторых, вопрос задает вопрос о том, как обращаться с несколькими наблюдениями за данной переменной и как их включать в окончательный выходной файл. Я уверен, что некоторые из них являются стандартными, но довольно полный поиск показал мало.
Объекты mre, которые я пытаюсь объединить, находятся ниже.
# '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) Несмотря на то, что в речах и исторических данных есть информация о дате, во первых у меня есть конкретные даты для каждой записи, а во второй - диапазон дат. В идеале я хотел бы объединиться, чтобы каждая речевая запись соответствовала как динамику ( "Имя" ), так и записи истории, в которые падает речевая дата.
2). Желаемый результат состоит в том, чтобы иметь data.frame или data.table со строками, равными наблюдениям в речах data.frame и столбцам для Name, Date и каждой из Ролей (которые будут заполнены стоимость). Однако некоторые роли появляются несколько раз для данного динамика в заданную дату, и поэтому мне нужно иметь возможность создавать несколько столбцов для этих экземпляров.
Объект ниже дает этот вывод, но был построен с использованием ужасно хрупкого и очень медленного цикла for:
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")
Любая помощь и/или комментарии о том, как улучшить этот вопрос, приветствуются!
Ответы
Ответ 1
Обновление: В версии v.1.9.3 + теперь реализованы перекрывающиеся соединения. Это особый случай, когда начало и конец Date идентичны в Speeches. Мы можем выполнить это, используя foverlaps() следующим образом:
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")
Это случай объединения диапазонов/интервалов.
Вот путь data.table. Он использует два катящихся соединения.
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 and dt2 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
В качестве альтернативы вы также можете выполнить это с помощью пакета GenomicRanges от bioconductor, который имеет дело с диапазонами довольно хорошо, особенно если вам требуется дополнительный столбец для объединения (Name) в дополнение к диапазонам. Вы можете установить его из здесь.
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")
Дает тот же результат, что и выше.
Ответ 2
Здесь используется метод sqldf(...) из пакета sqldf. Это приводит к вашему результату со следующими исключениями:
- Столбцы
Member.n содержат значения в алфавитном порядке, а не порядок, в котором они отображаются в кадре данных History. Таким образом, Member.1 будет содержать c, а Member.2 будет содержать f, а не наоборот.
- В вашем наборе результатов все столбцы, связанные с ролью, являются факторами, тогда как этот набор результатов имеет их как характер. Если это важно, что можно легко изменить.
Обратите внимание, что Speeches и History используются для фреймов входных данных, и я использую ваш Output dataframe, чтобы получить только порядок столбцов.
library(sqldf) # for sqldf(...)
library(reshape2) # for dcast(...)
colnames(History)[4:5] <- c("Start","End") # sqldf doesn't like "." in colnames
Speeches$id <- rownames(Speeches) # need unique id column
result <- sqldf("select a.id, a.Name, a.Date, b.Role, b.Value
from Speeches a, History b
where a.Name=b.Name and a.Date between b.Start and b.End")
Roles <- aggregate(Role~Name+Date+id,result,function(x)
ifelse(x=="Member",paste(x,1:length(x),sep="."),as.character(x)))$Role
result$Roles <- unlist(Roles)
result <- dcast(result,Name+Date+id~Roles,value.var="Value")
result <- result[order(result$id),] # re-order the rows
result <- result[,colnames(Output)] # re-order the columns
Объяснение
- Во-первых, нам нужен столбец id в
Speeches, чтобы различать реплицированные столбцы в результате. Поэтому мы используем имена строк для этого.
- Во-вторых, мы используем
sqldf(...) для объединения таблиц Speeches и History на основе ваших критериев. Поскольку вы хотите, чтобы даты соответствовали диапазону, это может быть лучшим подходом.
- В-третьих, нам нужно преобразовать несколько экземпляров "Member" в "Member.1", "Member.2" и т.д. Мы делаем это с помощью
aggregate(...) и paste(...).
- В-четвертых, нам нужно преобразовать результат sql, который находится в "длинном" формате (все значения в одном столбце, отличающиеся вторым столбцом), в "широкий" формат, значения для каждой роли в разных столбцах, Мы делаем это с помощью
dcast(...).
- Наконец, мы переупорядочиваем строки и столбцы, чтобы они соответствовали вашему результату.
