Мне было интересно, можно ли решить проблему островов и пробелов в 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);