Выберите строки, которые не существуют в других df
У меня есть df с идентификатором поездки, идентификатором остановки, отметкой времени и скоростью.
trip_id stop_id speed timestamp
1 1 1 5 1
2 1 1 0 2
3 1 1 0 3
4 1 1 5 4
5 1 2 2 101
6 1 2 2 102
7 1 2 2 103
8 1 2 2 104
9 1 3 4 201
10 1 3 0 202
Я сохранил первую и последнюю строки, где скорость равна нулю для групп, где trip_id и stop_id одинаковы.
df_departure_z <- sqldf("SELECT trip_id, stop_id, MAX(timestamp) FROM df WHERE speed = 0 GROUP BY trip_id,stop_id")
df_arrival_z <- sqldf("SELECT trip_id, stop_id, MIN(timestamp) FROM df WHERE speed = 0 GROUP BY trip_id,stop_id")
Который дал результаты:
df_departure_z:
trip_id stop_id MAX(timestamp)
1 1 1 3
2 1 3 203
df_arrival_z:
trip_id stop_id MIN(timestamp)
1 1 1 2
2 1 3 202
Моя проблема: есть одна остановка (остановка 2), где скорость никогда не равна нулю, и поэтому я хочу найти способ сохранить одну временную метку для остановок, где скорость никогда не равна нулю. Я попробовал это:
df_arr_dep <- sqldf("SELECT trip_id, stop_id, MIN(timestamp) FROM df GROUP BY trip_id, stop_id EXCEPT SELECT trip_id, stop_id FROM df_arrival_z ")
Но это дает мне ошибку, так как я пытаюсь сохранить три столбца на основе значений в двух столбцах в другой df. По сути, я хочу снова просмотреть свой df и найти те комбинации trip_id и stop_id, которых нет в df_departure_z или df_arrival_z. Если я попытаюсь с помощью SELECT *, я получу все строки, которые не были сохранены, что также неверно.
2 ответа
Можете ли вы использовать другие библиотеки, кроме sqldf? Я думаю, что следующее выполняет то, что вы ищете, используя dplyr:
library(dplyr)
dat %>%
group_by(trip_id, stop_id) %>%
filter(speed == 0 | sum(speed == 0) == 0) %>%
summarize(min_time = min(timestamp),
max_time = if_else(sum(speed == 0) == 0,
NA_real_,
max(timestamp)))
# A tibble: 3 x 4
# Groups: trip_id [?]
trip_id stop_id min_time max_time
<int> <int> <dbl> <dbl>
1 1 1 2 3
2 1 2 101 NA
3 1 3 202 202
данные
dat <- structure(list(trip_id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L),
stop_id = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L),
speed = c(5L, 0L, 0L, 5L, 2L, 2L, 2L, 2L, 4L, 0L),
timestamp = c(1L, 2L, 3L, 4L, 101L, 102L, 103L, 104L, 201L, 202L)),
.Names = c("trip_id", "stop_id", "speed", "timestamp"),
row.names = c(NA, -10L),
class = "data.frame")
Если я правильно понимаю каждую поездку и остановку, вы хотите, чтобы в строке была максимальная временная метка, для которой скорость равна нулю, или если такой строки нет, то в строке максимальной временной отметки для строк, имеющих скорость, отличную от 0, в этой группе. Далее мы сделаем альтернативное предположение, что в случае отсутствия скоростных рядов в группе просто используйте NA. После этого мы обсуждаем запрос EXCEPT в вопросе.
В первом случае выше, сгруппируйте по отключению, остановке и скорости == 0. Это даст 2 строки за отключение и остановку, если есть скорости 0 и не 0, и даст 1 отключение и остановку по ряду, если есть только не -0 скоростей. В каждой группе мы берем строку для скорости == 0 является наибольшим. Так как TRUE > FALSE, тогда, если есть две строки, он будет принимать строку, для которой скорость равна 0, а в противном случае он будет принимать одну ненулевую строку скорости.
sqldf("SELECT trip_id, stop_id, timestamp, MAX(speed0) speed0
FROM
(SELECT trip_id, stop_id, MAX(timestamp) timestamp, speed == 0 speed0
FROM df
GROUP BY 1, 2, 4)
GROUP BY 1, 2")
давая:
trip_id stop_id timestamp speed0
1 1 1 3 1
2 1 2 104 0
3 1 3 202 1
1 в строке 1 для speed0 означает, что строка скорости == 0 была найдена для этой группы, поэтому для этой группы использовалась максимальная временная метка, равная только строке скорости == 0. Аналогично, в строке 2 значение 0 для speed0 означает, что для этой группы не было найдено строки speed == 0, поэтому она использовала максимальную временную метку для строк, отличных от 0 в этой группе.
Если вам не нужен четвертый столбец, просто добавьте [-4] после окончания) .
Альтернативная интерпретация
Если вы хотели, чтобы в этих строках был NA, для которого нет скорости == 0, то просто замените первую строку в sql выше, как показано здесь:
sqldf("SELECT trip_id, stop_id, NULLIF(MAX(speed0) * timestamp, 0) timestamp
FROM
(SELECT trip_id, stop_id, MAX(timestamp) timestamp, speed == 0 speed0
FROM df
GROUP BY 1, 2, 4)
GROUP BY 1, 2")
давая:
trip_id stop_id timestamp
1 1 1 3
2 1 2 NA
3 1 3 202
Другой способ сделать это - левое соединение, дающее тот же результат:
sqldf("WITH a(trip_id, stop_id) AS (
SELECT distinct trip_id, stop_id
FROM df),
b(trip_id, stop_id, timestamp) AS (
SELECT trip_id, stop_id, MAX(timestamp) timestamp
FROM df
WHERE speed == 0
GROUP BY 1, 2)
SELECT *
FROM a LEFT JOIN b
USING (trip_id, stop_id)")
ИСКЛЮЧИТЬ против НЕ СУЩЕСТВУЕТ
Что касается последней строки кода в вопросе, касающейся ИСКЛЮЧЕНИЯ, это будет сделано с использованием следующего коррелированного подзапроса, показанного с НЕ СУЩЕСТВУЮЩИМ, как это:
sqldf("SELECT a.trip_id, a.stop_id, MIN(a.timestamp) timestamp
FROM df a
WHERE NOT EXISTS (
SELECT *
FROM df b
WHERE speed == 0 AND a.trip_id = b.trip_id AND a.stop_id = b.stop_id)
GROUP by 1, 2")
давая:
trip_id stop_id timestamp
1 1 2 101
Заметка
Мы предполагаем, что этот вход показан в воспроизводимой форме:
Lines <- "
trip_id stop_id speed timestamp
1 1 1 5 1
2 1 1 0 2
3 1 1 0 3
4 1 1 5 4
5 1 2 2 101
6 1 2 2 102
7 1 2 2 103
8 1 2 2 104
9 1 3 4 201
10 1 3 0 202"
df <- read.table(text = Lines)