使用Tidyverse Join更新/替换数据框中的值

Question

如何使用最有效的方法将主数据集中的NA值更新/替换为查找表中（正确的）值？ 这是一个非常常见的操作！类似的问题似乎没有简洁的解决方案。

限制条件： 1）请假设有大量的缺失值和比给出示例更大的查找表。因此，逐个替换操作将不切实际（没有case_when，if_else等）。 2）查找表没有主数据框中所有的值，仅有替换值。

推荐使用Tidyverse解决方案。类似问题似乎没有简洁的解决方案。

library(tidyverse)

### Main Dataframe ###
df1 <- tibble(
  state_abbrev = state.abb[1:10],
  state_name = c(state.name[1:5], rep(NA, 3), state.name[9:10]),
  value = sample(500:1200, 10, replace=TRUE)
)


#> # A tibble: 10 x 3
#>    state_abbrev state_name value
#>    <chr>        <chr>      <int>
#>  1 AL           Alabama      551
#>  2 AK           Alaska       765
#>  3 AZ           Arizona      508
#>  4 AR           Arkansas     756
#>  5 CA           California   741
#>  6 CO           <NA>        1100
#>  7 CT           <NA>         719
#>  8 DE           <NA>         874
#>  9 FL           Florida      749
#> 10 GA           Georgia      580


### Lookup Dataframe ###
lookup_df <- tibble(
  state_abbrev = state.abb[6:8],
  state_name = state.name[6:8]
)

#> # A tibble: 3 x 2
#>   state_abbrev state_name 
#>   <chr>        <chr>      
#> 1 CO           Colorado   
#> 2 CT           Connecticut
#> 3 DE           Delaware

理想情况下，left_join 应该有一个用于替换缺失值的选项。但是...

left_join(df1, lookup_df)
#> Joining, by = c("state_abbrev", "state_name")
#> # A tibble: 10 x 3
#>    state_abbrev state_name value
#>    <chr>        <chr>      <int>
#>  1 AL           Alabama      551
#>  2 AK           Alaska       765
#>  3 AZ           Arizona      508
#>  4 AR           Arkansas     756
#>  5 CA           California   741
#>  6 CO           <NA>        1100
#>  7 CT           <NA>         719
#>  8 DE           <NA>         874
#>  9 FL           Florida      749
#> 10 GA           Georgia      580

由 reprex包 (v0.2.0)于2018年07月28日创建。

- Nettle

---

如果查找表格超过两列，则为更新连接，这在dplyr中不存在（目前）。但是，基本功能已在coalesce中定义，如果您不想指定许多列名，则可以以编程方式应用它。 - alistaire

@Ronak：这个问题被标记为重复，但是链接并没有提供一个简洁的解决方案。 - Nettle

@alistaire：你的建议，我认为是这样的吧？left_join(df1, lookup_df, by = "state_abbrev") %>% mutate(merged.col = coalesce(df.merged$state_name.x, state_name.y) ) %>% select(state_abbrev, state_name= merged.col, value) - Nettle

差不多是这样。我写了一篇关于它的博客文章：https://alistaire.rbind.io/blog/coalescing-joins/ - alistaire

@alistaire：立马将这个加入到dplyr中！ - Nettle

Answer 1

将Alistaire和Nettle的建议采纳并转化为可行的解决方案

df1 %>% 
  left_join(lookup_df, by = "state_abbrev") %>% 
  mutate(state_name = coalesce(state_name.x, state_name.y)) %>% 
  select(-state_name.x, -state_name.y)

# A tibble: 10 x 3
   state_abbrev value state_name 
   <chr>        <int> <chr>      
 1 AL             671 Alabama    
 2 AK             501 Alaska     
 3 AZ            1030 Arizona    
 4 AR             694 Arkansas   
 5 CA             881 California 
 6 CO             821 Colorado   
 7 CT             742 Connecticut
 8 DE             665 Delaware   
 9 FL             948 Florida    
10 GA             790 Georgia

---

OP已经表明偏好"tidyverse"解决方案。不过，使用"data.table"包已经可以进行"update joins"了:

library(data.table)
setDT(df1)[setDT(lookup_df), on = "state_abbrev", state_name := i.state_name]
df1

    state_abbrev  state_name value
 1:           AL     Alabama  1103
 2:           AK      Alaska  1036
 3:           AZ     Arizona   811
 4:           AR    Arkansas   604
 5:           CA  California   868
 6:           CO    Colorado  1129
 7:           CT Connecticut   819
 8:           DE    Delaware  1194
 9:           FL     Florida   888
10:           GA     Georgia   501

基准测试

library(bench)
bm <- press(
  na_share = c(0.1, 0.5, 0.9),
  n_row = length(state.abb) * 2 * c(1, 100, 10000),
  {
    n_na <- na_share * length(state.abb)
    set.seed(1)
    na_idx <- sample(length(state.abb), n_na)
    tmp <- data.table(state_abbrev = state.abb, state_name = state.name)
    lookup_df <-tmp[na_idx] 
    tmp[na_idx, state_name := NA]
    df0 <- as_tibble(tmp[sample(length(state.abb), n_row, TRUE)])
    mark(
      dplyr = {
        df1 <- copy(df0)
        df1 <- df1 %>% 
          left_join(lookup_df, by = "state_abbrev") %>% 
          mutate(state_name = coalesce(state_name.x, state_name.y)) %>% 
          select(-state_name.x, -state_name.y)
        df1
      },
      upd_join = {
        df1 <- copy(df0)
        setDT(df1)[setDT(lookup_df), on = "state_abbrev", state_name := i.state_name]
        df1
      }
    )
  }
)
ggplot2::autoplot(bm)

data.table的更新连接总是更快（请注意对数时间刻度）。

由于更新连接修改数据对象，因此每个基准运行都使用新的副本。

- Uwe

Answer 2

通过 rows_update()，这里提供了一种单行解决方案：

df1 %>% 
  rows_update(lookup_df, by = "state_abbrev")

示例：

library(dplyr)

### Main Dataframe ###
df1 <- tibble(
  state_abbrev = state.abb[1:10],
  state_name = c(state.name[1:5], rep(NA, 3), state.name[9:10]),
  value = sample(500:1200, 10, replace=TRUE)
)

### Lookup Dataframe ###
lookup_df <- tibble(
  state_abbrev = state.abb[6:8],
  state_name = state.name[6:8]
)

df1 %>% 
  rows_update(lookup_df, by = "state_abbrev")
#> # A tibble: 10 x 3
#>    state_abbrev state_name  value
#>    <chr>        <chr>       <int>
#>  1 AL           Alabama       532
#>  2 AK           Alaska        640
#>  3 AZ           Arizona       521
#>  4 AR           Arkansas      523
#>  5 CA           California    970
#>  6 CO           Colorado      695
#>  7 CT           Connecticut   504
#>  8 DE           Delaware     1088
#>  9 FL           Florida       979
#> 10 GA           Georgia      1059

- HBat

---

太棒了！值得注意的是，如果键重复（例如具有状态缩写“CO”的多行），则rows_update()无法正常工作（但我希望它能够正常工作！）。这个数据没有问题，但我必须为我的问题找到另一个解决方案。 - Oliver

Answer 3

目前尚无一次性尝试合并多列的方法（可以通过在ifelse(is.na(value), ..., value)中使用查找表方法来实现），虽然曾经讨论过如何实现这种行为。目前，您可以手动构建它。如果您有很多列，则可以编程地coalesce，甚至将其放入函数中。

library(tidyverse)

df1 <- tibble(
    state_abbrev = state.abb[1:10],
    state_name = c(state.name[1:5], rep(NA, 3), state.name[9:10]),
    value = sample(500:1200, 10, replace=TRUE)
)

lookup_df <- tibble(
    state_abbrev = state.abb[6:8],
    state_name = state.name[6:8]
)

df1 %>% 
    full_join(lookup_df, by = 'state_abbrev') %>% 
    bind_cols(map_dfc(grep('.x', names(.), value = TRUE), function(x){
        set_names(
            list(coalesce(.[[x]], .[[gsub('.x', '.y', x)]])), 
            gsub('.x', '', x)
        )
    })) %>% 
    select(union(names(df1), names(lookup_df)))
#> # A tibble: 10 x 3
#>    state_abbrev state_name  value
#>    <chr>        <chr>       <int>
#>  1 AL           Alabama       877
#>  2 AK           Alaska       1048
#>  3 AZ           Arizona       973
#>  4 AR           Arkansas      860
#>  5 CA           California    938
#>  6 CO           Colorado      639
#>  7 CT           Connecticut   547
#>  8 DE           Delaware      672
#>  9 FL           Florida       667
#> 10 GA           Georgia      1142

- alistaire

---

“coalesce” 方法只适用于 NA 而不适用于其他类型的缺失值，比如空字符串吗？ - James

正确。在R中，空字符串是数据，就像整数值0一样。如果需要，可以使用na_if将它们转换为NA。 - alistaire

Answer 4

为了保留列的顺序：

df1 %>% 
  left_join(lookup_df, by = "state_abbrev") %>% 
  mutate(state_name.x = coalesce(state_name.x, state_name.y)) %>% 
  rename(state_name = state_name.x) %>%
  select(-state_name.y)

- gglee

Answer 5

如果缩写列完整且查找表完整，您可以删除state_name列，然后进行连接吗？

left_join(df1 %>% select(-state_name), lookup_df, by = 'state_abbrev') %>% 
  select(state_abbrev, state_name, value)

另一种选择是使用内置的州名和缩写列表，在一个 mutate 调用中使用 match 和 if_else。

df1 %>% 
  mutate(state_name = if_else(is.na(state_name), state.name[match(state_abbrev,state.abb)], state_name))

两者输出相同：

# A tibble: 10 x 3
   state_abbrev state_name  value
   <chr>        <chr>       <int>
 1 AL           Alabama       525
 2 AK           Alaska        719
 3 AZ           Arizona      1186
 4 AR           Arkansas     1051
 5 CA           California    888
 6 CO           Colorado      615
 7 CT           Connecticut   578
 8 DE           Delaware      894
 9 FL           Florida       536
10 GA           Georgia       599       

- sbha