Ответ 1
Присоедините data.table к своему подмножеству по группам, чтобы получить значения из строк, соответствующих неравным критериям.
Резюме:
-
Ниже я показываю 5 рабочих решений
data.tableкоторые были кандидатами на тестирование производительности в сравнении с фактическим набором данных OP (data.tableзаписей). -
Все 5 решений используют "неравные" объединения (используя неравенство для сравнения столбцов для объединения) в предложении
on. -
Каждое решение - это всего лишь небольшое постепенное изменение кода, поэтому следует легко следить за ним, сравнивая различные параметры
data.tableи синтаксиса.
Подход
Для работы с синтаксисом data.table для этого я разбил его на следующие шаги для проблемы OP:
- Присоедините dt к своему подмножеству (или к другой таблице данных).
- Выберите (и переименуйте) нужные столбцы из dt или из подмножества.
- Определите критерии объединения на основе столбцов из dt по сравнению со столбцами в подмножестве, в том числе с использованием "неравных" (неравных) сравнений.
- При желании можно указать, будет ли выбрано первое или последнее совпадение, если в подмножестве найдено несколько совпадающих записей.
Решение 1:
# Add row numbers to all records in dt (only because you
# have criteria based on comparing sequential rows):
dt[, row := .I]
# Compute result columns ( then standard assignment into dt using <- )
dt$found_date <-
dt[code=='p'][dt, # join dt to the data.table matching your criteria, in this case dt[code=='p']
.( x.date_up ), # columns to select, x. prefix means columns from dt[code=='p']
on = .(id==id, row > row, date_up > date_down), # join criteria: dt[code=='p'] fields on LHS, main dt fields on RHS
mult = "first"] # get only the first match if multiple matches
Обратите внимание на выражения объединения выше:
-
iв этом случае ваш главный дт. Таким образом, вы получите все записи из вашего основного data.table. -
x- это подмножество (или любой другой data.table), из которого вы хотите найти совпадающие значения.
Результат соответствует запрошенному выводу:
dt
id code date_down date_up row found_date
1: 1 p 2019-01-01 2019-01-02 1 <NA>
2: 1 f 2019-01-02 2019-01-03 2 <NA>
3: 2 f 2019-01-02 2019-01-02 3 <NA>
4: 2 p 2019-01-03 <NA> 4 <NA>
5: 3 p 2019-01-04 <NA> 5 <NA>
6: 4 <NA> 2019-01-05 2019-01-05 6 <NA>
7: 5 f 2019-01-07 2019-01-08 7 2019-01-08
8: 5 p 2019-01-07 2019-01-08 8 2019-01-09
9: 5 p 2019-01-09 2019-01-09 9 <NA>
10: 6 f 2019-01-10 2019-01-10 10 2019-01-11
11: 6 p 2019-01-10 2019-01-10 11 2019-01-11
12: 6 p 2019-01-10 2019-01-11 12 <NA>
Примечание. Вы можете удалить столбец row, выполнив команду dt[, row := NULL] если хотите.
Решение 2:
Идентичная логика, как и выше, используется для объединения и поиска столбцов результата, но теперь используется "назначение по ссылке" := для создания found_date в dt:
dt[, row := .I] # add row numbers (as in all the solutions)
# Compute result columns ( then assign by reference into dt using :=
# dt$found_date <-
dt[, found_date := # assign by reference to dt$found_date
dt[code=='p'][dt,
.( x.date_up ),
on = .(id==id, row > row, date_up > date_down),
mult = "first"]]
В решении 2 небольшое изменение для назначения наших результатов "по ссылке" в dt должно быть более эффективным, чем в решении 1. В решении 1 результаты вычисляются точно так же - единственное отличие состоит в том, что в решении 1 используется стандартное присвоение <- для создания dt$found_date (менее эффективны).
Решение 3:
Как и в решении 2, но теперь используется .(.SD) вместо dt для ссылки на исходный dt без непосредственного присвоения ему имени.
dt[, row := .I] # add row numbers (as in all the solutions)
setkey(dt, id, row, date_down) #set key for dt
# For all rows of dt, create found_date by reference :=
dt[, found_date :=
# dt[code=='p'][dt,
dt[code=='p'][.(.SD), # our subset (or another data.table), joined to .SD (referring to original dt)
.( x.date_up ),
on = .(id==id, row > row, date_up > date_down),
mult = "first"] ]
.SD выше ссылается на исходный dt, который мы назначаем обратно. Это соответствует подмножеству data.table, которое содержит строки, выбранные в первом dt[, который является всеми строками, потому что мы не фильтровали его.
Примечание. В решении 3 я использовал setkey() для установки ключа. Я должен был сделать это в Solution 1 и Solution 2 - однако я не хотел менять эти решения после того, как @OllieB успешно их протестировал.
Решение 4:
Как и в решении 3, но с использованием .SD еще раз, чем ранее. Наше основное имя data.table dt теперь появляется только один раз во всем нашем выражении!
# add row column and setkey() as previous solutions
dt[, found_date :=
# dt[code=='p'][.(.SD),
.SD[code=='p'][.SD, # .SD in place of dt at left! Also, removed .() at right (not sure on this second change)
.(found_date = x.date_up),
on = .(id==id, row > row, date_up > date_down),
mult = "first"]]
С изменением выше наше имя data.table dt появляется только один раз. Мне это очень нравится, потому что это позволяет легко копировать, адаптировать и использовать в других местах.
Также обратите внимание: где я раньше использовал .(SD) Я теперь удалил.() Вокруг .SD потому что это, кажется, не требует. Однако для этого изменения я не уверен, имеет ли он какое-либо преимущество в производительности или предпочтительный синтаксис data.table. Я был бы признателен, если кто-нибудь может добавить комментарий, чтобы проконсультировать по этому вопросу.
Решение 5:
Подобно предыдущим решениям, но с использованием by для явной группировки подмножеств по операциям при объединении
# add row column and setkey() as previous solutions
dt[, found_date :=
.SD[code=='p'][.SD,
.(found_date = x.date_up),
# on = .(id==id, row > row, date_up > date_down),
on = .(row > row, date_up > date_down), # removed the id column from here
mult = "first"]
, by = id] # added by = id to group the .SD subsets
В этом последнем решении я изменил его, чтобы использовать предложение by для явной группировки подмножеств .SD по id.
Примечание. Решение 5 не показало хороших результатов по сравнению с фактическими данными OllieB по сравнению с решениями 1-4. Однако, проверяя мои собственные фиктивные данные, я обнаружил, что решение 5 может работать хорошо, когда число уникальных групп в столбце id было низким:
- Имея всего 6 групп по 1,5 млн. Записей, это решение работало так же быстро, как и другие.
- С 40К группами в 1,5М записях я видел такую же низкую производительность, как и ОллиБ.
Результаты
Решения 1 - 4 показали хорошие результаты:
-
Для 1,45M записей в фактических данных OllieB каждое из решений с 1 по 4 составляло "истекшее" время 2,42 секунды или менее согласно обратной связи OllieB. Решение 3, похоже, работает быстрее всего для OllieB, имеющего "elapsed = 1.22" секунды.
-
Я лично предпочитаю Решение 4 из-за более простого синтаксиса.
Решение 5
- Решение 5 ( с использованием
byп) малоэффективное с 577 секунд для тестирования OllieB на его реальных данных.
Используемые версии
версия data.table: 1.12.0
Версия 3.5.3 (2019-03-11)
Возможные дальнейшие улучшения:
- Изменение полей даты на целое может помочь объединить более эффективно. Смотрите as.IDate(), чтобы преобразовать даты в целое число в data.tables.
- Шаг SetKey() могут больше не нужны пчелы: Как объяснено здесь @Arun из - за
onenvoking [часто] более эффективных вторичных indicies и автоматической индексации.
Ссылки на data.table
В рамках вашего вопроса вы задали "любые хорошие ссылки на data.table". Я нашел следующее полезное:
-
data.table Начало работы Вики на GitHub - это место для начала.
-
В частности, для этой проблемы стоит прочитать:
Важно отметить, что ответ @Arun, объясняющий "причину реализации аргумента on =", предполагает, что больше нет необходимости устанавливать ключи:
Поэтому важно выяснить, стоит ли время, затрачиваемое на переупорядочение всей таблицы данных, эффективным для кеширования объединения/агрегирования. Обычно, если нет повторяющихся операций группировки/объединения, выполняемых с одним и тем же ключом data.table, заметного различия не должно быть.
Поэтому в большинстве случаев больше не нужно устанавливать ключи. Мы рекомендуем использовать on = везде, где это возможно, если только клавиша настройки не приводит к значительному улучшению производительности, которую вы хотели бы использовать.
-
Этот вопрос SO, кажется, является центром информации о различных соединениях
data.table: Как объединить (объединить) фреймы данных (внутренний, внешний, левый, правый)? -
И, наконец, шпаргалка data.table - отличная ссылка (по ссылке, найденной на data.table. Начало работы с Wiki на GitHub).
Как всегда, я благодарен, если у кого-то есть предложения, возможно, это можно улучшить в дальнейшем.
Пожалуйста, не стесняйтесь комментировать, исправлять или публиковать другие решения, если вы можете добавить что-нибудь.