Блог

Я хочу выборочно соединить два кадра данных слева на основе совместного столбца и состояния строк.

Я видел несколько похожих сообщений с использованием fuzzyjoin и sqldf, но предыдущие примеры, которые я нашел, не совсем похожи на мои.

Пример dfs:

 df1 <- data.frame(id = c("1", "2", "3"),
              zipcode = c("11111", "44444", "33333"),
              exp.id = c("0", "0", "1"))
df2 <- data_frame(zipcode = c("11111", "22222", "33333", "44444", "55555"),
              pct = c("0.1", "0.5", "0.9", "0.7", "0.8"))
 

В принципе, я хочу присоединиться к столбцу «pct» в df2 к df1 с помощью почтового индекса, но только там, где «exp.id» = «0»

Результат, которого я ожидаю, должен выглядеть следующим образом:

   id    zipcode exp.id pct  
 <chr> <chr>   <chr>  <chr>
1 1     11111   0      0.1  
2 2     44444   0      0.7  
3 3     33333   1      NA  
 

Заранее спасибо.

1) Это левое соединение df1 с df2 on zipcode , но объединяет только строки, для которых exp.id равно 0. Для других строк pct -NA, как в ожидаемом результате, показанном в вопросе. Обратите внимание, что точка является оператором SQL, поэтому мы заключаем exp.id ее в квадратные скобки, чтобы избежать имени.

 library(sqldf)

sqldf("select a.id, a.zipcode, b.pct
  from df1 a 
  left join df2 b on a.zipcode = b.zipcode and [exp.id] = 0")
##   id zipcode  pct
## 1  1   11111  0.1
## 2  2   44444  0.7
## 3  3   33333 <NA>
 

2) Это похоже на (1), но возвращает только exp.id строки, которые равны нулю. Это отличается от того, о чем говорится в вопросе, но комментарий предполагает, что это представляет интерес.

Разница между приведенным здесь кодом и (1) иллюстрирует тонкую разницу между включением условия в on и в where . Поскольку у нас есть простое условие, в этом случае мы можем использовать using предложение вместо on . using приводит к единому zipcode , поэтому нам не нужно различать a.zipcode и b.zipcode .

 sqldf("select a.id, zipcode, b.pct
  from df1 a left join df2 b using(zipcode)
  where [exp.id] = 0")
##   id zipcode pct
## 1  1   11111 0.1
## 2  2   44444 0.7
 

Обратите внимание, что механизм SQL внутренне создает план запроса для оптимизации вычислений при сохранении тех же выходных данных. Он не обязательно выполняет операции в написанном порядке, т. Е. он не обязательно выполняет объединение, а затем уменьшает результат, но может сначала уменьшить df1 для повышения производительности, поскольку это дает тот же результат. Мы отображаем информацию в плане запроса ниже и видим, что он действительно сканирует df1 первым.

 sqldf("explain query plan select a.id, zipcode, b.pct
      from df1 a left join df2 b using(zipcode)
      where [exp.id] = 0")
##   id parent notused                                                           detail
## 1  3      0       0                                              SCAN TABLE df1 AS a
## 2 16      0       0 SEARCH TABLE df2 AS b USING AUTOMATIC COVERING INDEX (zipcode=?)
 

Соедините данные и измените pct значение на NA «где exp.id != 0 «.

 library(dplyr)

res <- df1 %>%
        left_join(df2, by = 'zipcode') %>%
        mutate(pct = replace(pct, exp.id != 0, NA))

res

#  id zipcode exp.id  pct
#1  1   11111      0  0.1
#2  2   44444      0  0.7
#3  3   33333      1 <NA>
 

В базе R —

 res <- transform(merge(df1, df2, by = 'zipcode', all.x = TRUE), 
                 pct = replace(pct, exp.id != 0, NA))
 

Также вы можете присоединиться только к тем значениям, где exp.id = 0 .

 df1 %>%
  filter(exp.id == 0) %>%
  left_join(df2, by = 'zipcode') %>%
  right_join(df1)