Блог

У меня есть эти два фрейма данных.

Данные 1

Данные 2

Это мой желаемый результат

Я уже пробовал пару левых/полных соединений, но не могу заставить их работать должным образом. Данные в формате кода

 data1 <- data.frame(
  race_eth = c("White", "Black", "Asian", "Native American", "Pacific Islander", "Hispanic","White", "Black", "Asian", "Native American", "Pacific Islander", "Hispanic"),
  joined = c("Yes","Yes","Yes","Yes","Yes","Yes","No","No","No","No","No","No"))
  


data2 <- data.frame(
  race_eth = c("White", "Black", "Asian", "Native American","White", "Black", "Asian", "Native American"),
  joined = c("Yes","Yes","Yes","Yes", "No","No","No","No"),
  count = c(10,2,3,5,1,3,5,6))





 

Мы могли бы использовать left_join , а затем заменить NA на 0`

 library(dplyr)
library(tidyr)
left_join(data1, data2) %>%
     mutate(count = replace_na(count, 0))
 

-выход

              race_eth joined count
1             White    Yes    10
2             Black    Yes     2
3             Asian    Yes     3
4   Native American    Yes     5
5  Pacific Islander    Yes     0
6          Hispanic    Yes     0
7             White     No     1
8             Black     No     3
9             Asian     No     5
10  Native American     No     6
11 Pacific Islander     No     0
12         Hispanic     No     0
 

Вы можете попробовать опцию base R, используя merge replace

 transform(
  merge(data1, data2, all.x = TRUE),
  count = replace(count, is.na(count), 0)
)
 

что дает

            race_eth joined count
1             Asian     No     5
2             Asian    Yes     3
3             Black     No     3
4             Black    Yes     2
5          Hispanic     No     0
6          Hispanic    Yes     0
7   Native American     No     6
8   Native American    Yes     5
9  Pacific Islander     No     0
10 Pacific Islander    Yes     0
11            White     No     1
12            White    Yes    10
 

Один data.table из вариантов

 setDT(data2)[setDT(data1), on = .(race_eth, joined)][, count := nafill(count, fill = 0)][]
 

дает

             race_eth joined count
 1:            White    Yes    10
 2:            Black    Yes     2
 3:            Asian    Yes     3
 4:  Native American    Yes     5
 5: Pacific Islander    Yes     0
 6:         Hispanic    Yes     0
 7:            White     No     1
 8:            Black     No     3
 9:            Asian     No     5
10:  Native American     No     6
11: Pacific Islander     No     0
12:         Hispanic     No     0
 

Альтернативный подход заключается не в объединении, а в complete уровнях race_eth .

 library(tidyr)
data2 %>%
   complete(race_eth = c("Asian","Black","Hispanic","Native American","Pacific Islander","White"),
            joined = c("Yes","No"),
            fill = list(count = 0))
## A tibble: 12 x 3
#   race_eth         joined count
#   <chr>            <chr>  <dbl>
# 1 Asian            No         5
# 2 Asian            Yes        3
# 3 Black            No         3
# 4 Black            Yes        2
# 5 Hispanic         No         0
# 6 Hispanic         Yes        0
# 7 Native American  No         6
# 8 Native American  Yes        5
# 9 Pacific Islander No         0
#10 Pacific Islander Yes        0
#11 White            No         1
#12 White            Yes       10
 

Это также может быть достигнуто с использованием уникальных значений data1 :

 data2 %>% 
   complete(race_eth = unique(data1$race_eth),
            joined = c("Yes","No"), fill = list(count = 0))