Объединить две таблицы точно и нечетко

У меня есть две таблицы, которые я хотел бы объединить на основе точного совпадения одной переменной и нечеткого совпадения другой.

Рассмотрим две таблицы ниже. Для каждого id1 в dt1 я хотел бы найти id2 в dt2, который точно совпадает по размеру и где значение даты в dt2 равно или позже, чем поле даты в dt1. Если есть несколько совпадений, я бы хотел, чтобы одно было выбрано случайным образом.

dt1 <- data.table(c("A", "B"), c(2, 3), as.Date(c("2013-03-27", "2014-05-08"), format = '%Y-%m-%d'))
setnames(dt1, c("V1", "V2", "V3"),
c("id1", "size", "date"))

dt2 <- data.table(1:10, c(2, 4, 3, 2, 2, 2, 3, 2, 4, 4), as.Date(c("2014-02-25", "2011-08-02", "2014-06-21", "2013-11-29", "2012-02-21", "2011-12-02",
"2014-04-22", "2011-03-05", "2014-04-21", "2014-10-29"), format = '%Y-%m-%d'))
setnames(dt2, c("V1", "V2", "V3"),
c("id2", "size", "date"))

В результате таблица может выглядеть так:

   id1 size       date  id2
1:   A    2 2013-03-27    1
2:   B    3 2014-05-08    3

или вот так (в зависимости от случайного выбора)

   id1 size       date  id2
1:   A    2 2013-03-27    4
2:   B    3 2014-05-08    3

r merge data.table exact-match fuzzy-logic
person user1389960    schedule 20.11.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Чтобы присоединиться по размеру и выбрать подходящие записи даты, мы можем использовать неэквивалентное соединение:

> # Rename the date columns to make the join step clear:
> setnames(dt1, "date", "date1")
> setnames(dt2, "date", "date2")

> # Non equi-join will give all entries in dt2 matching on size where
> # date2 >= date1:
> dt2[dt1, on=.(size, date2 >= date1)]
   id2 size      date2 id1
1:   4    2 2013-03-27   A
2:   1    2 2013-03-27   A
3:   3    3 2014-05-08   B

Я не смог найти надежный способ выполнения шага случайного выбора в сочетании с соединением. В качестве хакерского решения мы можем добавить новый столбец в таблицу выше, содержащую перетасованные номера строк, а затем выбрать строку с наибольшим перетасованным номером строки на id1:

> joined <- dt2[dt1, on=.(size, date2 >= date1)]
> joined[, selection_column := sample(.I, .N)] 
> filtered <- joined[,.SD[which.max(selection_column)], by=id1]
> filtered[, selection_column := NULL]
> filtered
   id1 id2 size      date2
1:   A   1    2 2013-03-27
2:   B   3    3 2014-05-08

В качестве альтернативы мы можем использовать dplyr для выполнения шага случайного выбора:

> library(dplyr)
> dt2[dt1, on=.(size, date2 >= date1)] %>% 
+   group_by(id1) %>% 
+   sample_n(1) %>% 
+   as.data.table()  
   id2 size      date2 id1
1:   4    2 2013-03-27   A
2:   3    3 2014-05-08   B
person Scott Ritchie    schedule 21.11.2017
comment
А как насчет dt2[dt1, on=.(size, date >= date), .(id1, id2 = sample(id2,1)), by=.EACHI] ? - person thelatemail; 21.11.2017
comment
Это работает, если вы не хотите сохранять столбцы размера и даты, но я не видел способа сохранить несколько столбцов, используя этот подход. - person Scott Ritchie; 21.11.2017
comment
@ScottRitchie Я не вижу пропущенных столбцов в ответе thelatemail. Кроме того, если вы хотите сделать случайный выбор после, это тривиально сделать - один из способов: dt2[dt1, on=.(size, date >= date)][, .SD[sample(.N, 1)], by = id1]. Также обратите внимание, что вам не нужно переименовывать столбцы. - person eddi; 21.11.2017
comment
@eddi похоже, ты прав. Я не уверен, почему я не мог заставить эти подходы работать вчера. - person Scott Ritchie; 22.11.2017

arrow_upward
1
arrow_downward

Я не уверен, что большинство людей обычно имеют в виду это, когда говорят о «нечетком сопоставлении» — вы хотите объединить две таблицы, а затем сделать что-то случайное с результатом совпадения, например:

library(data.table)
library(tidyverse)

set.seed(1234)
dt1 <- data.table(c("A", "B"), c(2, 3), as.Date(c("2013-03-27", "2014-05-08"), format = '%Y-%m-%d'))
setnames(dt1, c("V1", "V2", "V3"),
         c("id1", "size", "date"))

dt2 <- data.table(1:10, c(2, 4, 3, 2, 2, 2, 3, 2, 4, 4), as.Date(c("2014-02-25", "2011-08-02", "2014-06-21", "2013-11-29", "2012-02-21", "2011-12-02",
                                                                   "2014-04-22", "2011-03-05", "2014-04-21", "2014-10-29"), format = '%Y-%m-%d'))
setnames(dt2, c("V1", "V2", "V3"),
         c("id2", "size", "date"))

dt <- full_join(dt1, dt2, by = "size") %>% 
  filter(date.y >= date.x) %>% 
  group_by(size) %>%
  sample_n(size = 1)
person edavidaja    schedule 21.11.2017