Как решить пробелы и проблемы на острове в R и производительности по сравнению с SQL?
Мне было интересно, можно ли решить проблему островов и пробелов в R, подобно SQL. У меня есть следующие данные, если мы рассмотрим один ID:
ID StartDate StartTime EndDate EndTime
1 19-05-2014 19:00 19-05-2014 20:00
1 19-05-2014 19:30 19-05-2014 23:30
1 19-05-2014 16:00 19-05-2014 18:00
1 20-05-2014 20:00 20-05-2014 20:30
Обратите внимание, что первые две строки перекрываются, что я хотел бы сделать, заключается в объединении перекрывающихся строк, в результате чего:
ID StartDate StartTime EndDate EndTime
1 19-05-2014 19:00 19-05-2014 23:30
1 19-05-2014 16:00 19-05-2014 18:00
1 20-05-2014 20:00 20-05-2014 20:30
Есть ли способ сделать это в R?
Мне хорошо известно, что это делается в SQL, но поскольку мои данные уже находятся в R, я предпочитаю делать это в R. Во-вторых, у меня есть некоторые вопросы относительно производительности поиска пробелов и островов, я знаю, что SQL очень быстро в этом, но мне интересно, быстрее ли R из-за всех данных, находящихся в памяти.
Я хотел бы использовать data.table для этого, но я не знаю, как это сделать.
ОБНОВЛЕНИЕ - ответ на Arun
Я создал следующий тестовый пример, содержащий все возможные интервальные ориентации.
dat <- structure(
list(ID = c(1L, 1L, 1L, 1L, 1L, 1L),
stime = structure(c(as.POSIXct("2014-01-15 08:00:00"),
as.POSIXct("2014-01-15 10:00:00"),
as.POSIXct("2014-01-15 08:30:00"),
as.POSIXct("2014-01-15 09:00:00"),
as.POSIXct("2014-01-15 11:30:00"),
as.POSIXct("2014-01-15 12:00:00")),
class = c("POSIXct", "POSIXt"), tzone = ""),
etime = structure(c(as.POSIXct("2014-01-15 09:30:00"),
as.POSIXct("2014-01-15 11:00:00"),
as.POSIXct("2014-01-15 10:00:00"),
as.POSIXct("2014-01-15 09:30:00"),
as.POSIXct("2014-01-15 12:30:00"),
as.POSIXct("2014-01-15 13:00:00")),
class = c("POSIXct", "POSIXt"), tzone = "")
),
.Names = c("ID", "stime", "etime"),
sorted = c("ID", "stime", "etime"),
class = c("data.table", "data.frame"),
row.names = c(NA,-6L)
)
Я бы ожидал, что интервал с 8:30 до 10:00 будет "склеен" с 10:00 до 11:00, но это было не так. В результате получилось:
idx ID stime etime
1: 4 1 2014-01-15 08:00:00 2014-01-15 10:00:00
2: 3 1 2014-01-15 10:00:00 2014-01-15 11:00:00
3: 6 1 2014-01-15 11:30:00 2014-01-15 13:00:00
Следующий набор данных обеспечивает более тщательное тестирование:
# The numbers represent seconds from 1970-01-01 01:00:01
dat <- structure(
list(ID = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L),
stime = structure(c(as.POSIXct("2014-01-15 08:00:00"),
as.POSIXct("2014-01-15 10:00:00"),
as.POSIXct("2014-01-15 08:30:00"),
as.POSIXct("2014-01-15 09:00:00"),
as.POSIXct("2014-01-15 11:30:00"),
as.POSIXct("2014-01-15 12:00:00"),
as.POSIXct("2014-01-15 07:30:00"),
as.POSIXct("2014-01-15 08:00:00"),
as.POSIXct("2014-01-15 08:30:00"),
as.POSIXct("2014-01-15 09:00:00"),
as.POSIXct("2014-01-15 09:00:00"),
as.POSIXct("2014-01-15 09:30:00"),
as.POSIXct("2014-01-15 10:00:00")
),
class = c("POSIXct", "POSIXt"), tzone = ""),
etime = structure(c(as.POSIXct("2014-01-15 09:30:00"),
as.POSIXct("2014-01-15 11:00:00"),
as.POSIXct("2014-01-15 10:00:00"),
as.POSIXct("2014-01-15 09:30:00"),
as.POSIXct("2014-01-15 12:30:00"),
as.POSIXct("2014-01-15 13:00:00"),
as.POSIXct("2014-01-15 08:30:00"),
as.POSIXct("2014-01-15 09:00:00"),
as.POSIXct("2014-01-15 09:30:00"),
as.POSIXct("2014-01-15 10:00:00"),
as.POSIXct("2014-01-15 10:00:00"),
as.POSIXct("2014-01-15 10:30:00"),
as.POSIXct("2014-01-15 11:00:00")
),
class = c("POSIXct", "POSIXt"), tzone = "")
),
.Names = c("ID", "stime", "etime"),
sorted = c("ID", "stime", "etime"),
class = c("data.table", "data.frame"),
row.names = c(NA,-6L)
)
Итак, наш результат:
idx ID stime etime
1: 4 1 2014-01-15 08:00:00 2014-01-15 10:00:00
2: 3 1 2014-01-15 10:00:00 2014-01-15 11:00:00
3: 6 1 2014-01-15 11:30:00 2014-01-15 13:00:00
4: 12 2 2014-01-15 07:30:00 2014-01-15 09:30:00
5: 13 2 2014-01-15 09:00:00 2014-01-15 11:00:00
Теперь для респондента с ID = 2 мы видим, что интервалы перекрываются, но не сообщаются как один интервал. Правильное решение было бы:
idx ID stime etime
1: ? 1 2014-01-15 08:00:00 2014-01-15 11:00:00
3: ? 1 2014-01-15 11:30:00 2014-01-15 13:00:00
4: ?? 2 2014-01-15 07:30:00 2014-01-15 11:00:00
Обновление - тесты и тестирование и большие наборы данных
У меня есть следующий набор данных с примерно 1000 пользователями, каждый из которых имеет 500 длительностей, давая 0,5 миллиона строк. Вы можете загрузить набор данных на моем Google Диске, включая решение на Google Диске.
SQL Server 2014 на ноутбуке 8 ГБ оперативной памяти, 64-битном, i5-4210U CPU @1.70Ghz - 2.39Ghz занимает около 5 секунд, чтобы сделать это, используя решение, предоставленное Itzik Ben-Gan в SQL. 5 секунд исключают процесс создания функции. Кроме того, индексы для любой таблицы не создаются.
PS: Я использую library(lubridate);
Ответы
Ответ 1
Вот очень простая идея. Закажите по времени начала, затем найдите кумулятивный максимум конечного времени. После того, как вы это сделали, группы перекрытия - это просто те, где следующее время начала еще меньше или равно текущему кумулятивному максимальному времени окончания (все делается по идентификатору):
setorder(dat, ID, stime) # ordering by ID is unnecessary, it just prettier
dat[, etime.max := as.POSIXct(cummax(as.numeric(etime)), origin = '1970-01-01'), by = ID]
# find the grouping of intervals (1:.N hack is to avoid warnings when .N=1)
dat[, grp := cumsum(c(FALSE, stime[2:.N] > etime.max[1:(.N-1)]))[1:.N], by = ID]
dat[, .(stime = min(stime), etime = max(etime)), by = .(ID, grp)][, grp := NULL][]
# ID stime etime
#1: 1 2014-01-15 08:00:00 2014-01-15 11:00:00
#2: 1 2014-01-15 11:30:00 2014-01-15 13:00:00
#3: 2 2014-01-15 07:30:00 2014-01-15 11:00:00
Так как для этого не требуется найти все возможные перекрытия, это очень быстро. В симулированном наборе данных, который примерно соответствует описанию ОП, он мгновенно для меня (< 0,2 с).
