Скользящее соединение на data.table с дублирующимися ключами
Я пытаюсь понять rolling joins в data.table, Данные для воспроизведения приведены в конце.
Имеется таблица данных транзакций в аэропорту, в данное время:
> dt
t_id airport thisTime
1: 1 a 5.1
2: 3 a 5.1
3: 2 a 6.2
(нота t_ids 1 и 3 имеют одинаковый аэропорт и время)
и справочная таблица рейсов, вылетающих из аэропортов:
> dt_lookup
f_id airport thisTime
1: 1 a 6
2: 2 a 6
3: 1 b 7
4: 1 c 8
5: 2 d 7
6: 1 d 9
7: 2 e 8
> tables()
NAME NROW NCOL MB COLS KEY
[1,] dt 3 3 1 t_id,airport,thisTime airport,thisTime
[2,] dt_lookup 7 3 1 f_id,airport,thisTime airport,thisTime
Я хотел бы сопоставить все транзакции со всеми возможными рейсами, вылетающими из этого аэропорта, чтобы получить:
t_id airport thisTime f_id
1 a 6 1
1 a 6 2
3 a 6 1
3 a 6 2
Поэтому я подумал, что это будет работать:
> dt[dt_lookup, nomatch=0,roll=Inf]
t_id airport thisTime f_id
1: 3 a 6 1
2: 3 a 6 2
Но это не вернуло транзакции t_id == 1,
Из документации написано:
Обычно в ключе x не должно быть дубликатов,...
Тем не менее, у меня есть дубликаты в моем "х ключе" (а именно airport & thisTime), и не совсем понимаю / понимаю, что это значит t_id = 1 удаляется с выхода.
Может кто-нибудь пролить свет на то, почему t_id = 1 не возвращается, и как я могу получить объединение для работы, когда у меня есть дубликаты?
Данные
library(data.table)
dt <- data.table(t_id = seq(1:3),
airport = c("a","a","a"),
thisTime = c(5.1,6.2, 5.1), key=c( "airport","thisTime"))
dt_lookup <- data.table(f_id = c(rep(1,4),rep(2,3)),
airport = c("a","b","c","d",
"a","d","e"),
thisTime = c(6,7,8,9,
6,7,8), key=c("airport","thisTime"))
1 ответ
Причина того, что t_id = 1 не отображается в выходных данных потому, что скользящее соединение занимает строку, в которой комбинация клавиш встречается последней. Из документации (выделено мое):
Применяется к последнему столбцу соединения, обычно к дате, но может быть любой упорядоченной переменной, нерегулярной и включающей пробелы. Если roll=TRUE и строка i совпадает со всеми, кроме последнего столбца x x join, и его значение в последнем столбце i i join попадает в пробел (в том числе после последнего наблюдения в x для этой группы), то преобладающее значение в x равно накатился вперед. Эта операция особенно быстро выполняется с использованием модифицированного бинарного поиска. Операция также известна как последнее перенесенное наблюдение (LOCF).
Давайте рассмотрим несколько большие наборы данных:
> DT
t_id airport thisTime
1: 1 a 5.1
2: 4 a 5.1
3: 3 a 5.1
4: 2 d 6.2
5: 5 d 6.2
> DT_LU
f_id airport thisTime
1: 1 a 6
2: 2 a 6
3: 2 a 8
4: 1 b 7
5: 1 c 8
6: 2 d 7
7: 1 d 9
Когда вы выполняете скользящее соединение, как в вашем вопросе:
DT[DT_LU, nomatch=0, roll=Inf]
ты получаешь:
t_id airport thisTime f_id
1: 3 a 6 1
2: 3 a 6 2
3: 3 a 8 2
4: 5 d 7 2
5: 5 d 9 1
Как вы можете видеть, как из комбинации клавиш a, 5.1 а также d, 6.2 последняя строка используется для объединенных данных. Потому что вы используете Inf в качестве значения крена все будущие значения включаются в результирующую таблицу данных. Когда вы используете:
DT[DT_LU, nomatch=0, roll=1]
Вы видите, что включено только первое значение в будущем:
t_id airport thisTime f_id
1: 3 a 6 1
2: 3 a 6 2
3: 5 d 7 2
Если вы хотите f_idдля всех комбинаций airport & thisTime где DT$thisTime ниже чем DT_LU$thisTimeВы можете достичь этого, создав новую переменную (или заменив существующую thisTimeс помощью ceiling функция. Пример, где я создаю новую переменную thisTime2 а затем сделать нормальное соединение с DT_LU:
DT[, thisTime2 := ceiling(thisTime)]
setkey(DT, airport, thisTime2)[DT_LU, nomatch=0]
который дает:
t_id airport thisTime thisTime2 f_id
1: 1 a 5.1 6 1
2: 4 a 5.1 6 1
3: 3 a 5.1 6 1
4: 1 a 5.1 6 2
5: 4 a 5.1 6 2
6: 3 a 5.1 6 2
7: 2 d 6.2 7 2
8: 5 d 6.2 7 2
Применительно к предоставленным вами данным:
> dt[, thisTime2 := ceiling(thisTime)]
> setkey(dt, airport, thisTime2)[dt_lookup, nomatch=0]
t_id airport thisTime thisTime2 f_id
1: 1 a 5.1 6 1
2: 3 a 5.1 6 1
3: 1 a 5.1 6 2
4: 3 a 5.1 6 2
Если вы хотите включить все будущие значения вместо только первого, вам нужен несколько иной подход, для которого вам понадобится i.col функциональность (которая еще не документирована):
1: сначала установите ключ только на airport колонки:
setkey(DT, airport)
setkey(DT_LU, airport)
2: Используйте i.col функциональность (которая пока не задокументирована) в j чтобы получить то, что вы хотите, следующим образом:
DT1 <- DT_LU[DT, .(tid = i.t_id,
tTime = i.thisTime,
fTime = thisTime[i.thisTime < thisTime],
fid = f_id[i.thisTime < thisTime]),
by=.EACHI]
это дает вам:
> DT1
airport tid tTime fTime fid
1: a 1 5.1 6 1
2: a 1 5.1 6 2
3: a 1 5.1 8 2
4: a 4 5.1 6 1
5: a 4 5.1 6 2
6: a 4 5.1 8 2
7: a 3 5.1 6 1
8: a 3 5.1 6 2
9: a 3 5.1 8 2
10: d 2 6.2 7 2
11: d 2 6.2 9 1
12: d 5 6.2 7 2
13: d 5 6.2 9 1
Некоторое объяснение: если вы объединяете две таблицы данных, в которых используются одинаковые имена столбцов, вы можете ссылаться на столбцы таблицы данных в i предшествуя именам столбцов i., Теперь можно сравнивать thisTime от DT с thisTime от DT_LU, С by = .EACHI Вы гарантируете, что все комбинации для с удержанием условия включены в результирующую таблицу данных.
Кроме того, вы можете добиться того же с:
DT2 <- DT_LU[DT, .(airport=i.airport,
tid=i.t_id,
tTime=i.thisTime,
fTime=thisTime[i.thisTime < thisTime],
fid=f_id[i.thisTime < thisTime]),
allow.cartesian=TRUE]
который дает тот же результат:
> identical(DT1, DT2)
[1] TRUE
Если вы хотите включить только будущие значения в определенную границу, вы можете использовать:
DT1 <- DT_LU[DT,
{
idx = i.thisTime < thisTime & thisTime - i.thisTime < 2
.(tid = i.t_id,
tTime = i.thisTime,
fTime = thisTime[idx],
fid = f_id[idx])
},
by=.EACHI]
который дает:
> DT1
airport tid tTime fTime fid
1: a 1 5.1 6 1
2: a 1 5.1 6 2
3: a 4 5.1 6 1
4: a 4 5.1 6 2
5: a 3 5.1 6 1
6: a 3 5.1 6 2
7: d 2 6.2 7 2
8: d 5 6.2 7 2
Когда вы сравните это с предыдущим результатом, вы увидите, что теперь строки 3, 6, 9, 10 и 12 удалены.
Данные:
DT <- data.table(t_id = c(1,4,2,3,5),
airport = c("a","a","d","a","d"),
thisTime = c(5.1, 5.1, 6.2, 5.1, 6.2),
key=c("airport","thisTime"))
DT_LU <- data.table(f_id = c(rep(1,4),rep(2,3)),
airport = c("a","b","c","d","a","d","e"),
thisTime = c(6,7,8,9,6,7,8),
key=c("airport","thisTime"))