Выберите строки, которых нет в других df

Можно ли использовать другие библиотеки, кроме 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")

Если я правильно понимаю, для каждой поездки и остановки вам нужна строка с максимальной отметкой времени, для которой скорость равна нулю, или, если такой строки нет, то строка с максимальной отметкой времени для строк, имеющих ненулевую скорость в этой группе. Ниже мы делаем альтернативное предположение, что в случае отсутствия строк с нулевой скоростью в группе просто используйте NA. После этого мы обсуждаем запрос EXCEPT в вопросе.

В первом случае выше сгруппируйте по отключению, остановке и скорости == 0. Это даст 2 строки за поездку и остановку, если есть как 0, так и отличные от 0 скорости, и даст 1 строку отключения и остановки, если есть только ненулевые скорости. -0 скоростей. В каждой группе мы берем строку для скорости == 0, которая является самой большой. Поскольку ИСТИНА > ЛОЖЬ, то при наличии двух строк будет выбрана строка, для которой скорость равна 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 для скорости 0 означает, что для этой группы была найдена строка скорости == 0, поэтому для этой группы использовалась максимальная метка времени только для строк скорости == 0. Точно так же в строке 2 0 для скорости 0 означает, что для этой группы не было найдено строки скорости == 0, поэтому использовалась максимальная временная метка для строк, отличных от 0, в этой группе.

Если вам не нужен 4-й столбец, просто добавьте [-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)")

ЗА ИСКЛЮЧЕНИЕМ или НЕ СУЩЕСТВУЕТ

Что касается последней строки кода в вопросе, включающем EXCEPT, это будет сделано с использованием следующего коррелированного подзапроса, показанного с NOT EXISTS, например:

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)