如何用dplyr实现Left Join并保留按Country分组的唯一League值

数据框定义 #

t1 <- structure(list(Country = c("France", "Spain", "England")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -3L))

t2 <- structure(list(Country = c("France", "France", "France", "France", 
"France", "France", "Spain", "Spain", "Spain", "Spain", "Spain", 
"Spain", "Spain", "England", "England", "England", "England", 
"England", "England", "England", "England"), League = c("Ligue 1", 
"Ligue 1", "Ligue 1", "Ligue 2", "Ligue 2", "Ligue 2", "Liga", 
"Liga", "Liga", "Liga 2", "Liga 2", "Liga 2", "Liga 2", "PL1", 
"PL1", "PL1", "PL1", "PL2", "PL2", "PL2", "PL2"), ID = c("EUR", 
"EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", 
"EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", "EUR", 
"EUR", "EUR")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-21L))

需求说明 #

基于Country字段对t1和t2执行左连接，最终得到每个国家对应的所有唯一League值，期望输出如下：

# A tibble: 6 × 2
  Country League 
  <chr>   <chr>  
1 France  Ligue 1
2 France  Ligue 2
3 Spain   Liga   
4 Spain   Liga 2 
5 England PL1    
6 England PL2

现有问题 #

当前使用以下代码可得到结果，但会抛出警告信息，需要更符合dplyr风格的实现方式：

dtl <- dtl %>% 
  left_join(select(t2,Country,League),by=c("Country")) %>% 
  dplyr::distinct(Country,League,.keep_all = T)

解决方案 #

核心思路是先对t2预处理去重，再与t1左连接，避免连接后产生大量重复行再去重的冗余操作，同时消除警告：

分步实现

library(dplyr)

# 提取t2中每个Country对应的唯一League值
t2_unique <- t2 %>% 
  distinct(Country, League)

# 与t1执行左连接
result <- t1 %>% 
  left_join(t2_unique, by = "Country")

合并为单管道操作

result <- t1 %>% 
  left_join(t2 %>% distinct(Country, League), by = "Country")

上述两种方式均可得到符合预期的结果，且无警告信息，完全符合dplyr的操作逻辑。

内容的提问来源于stack exchange，提问作者M.O