r - 我如何使用 tidyverse 插入指定范围内且不存在于感兴趣的列中的值

Question

我有大量分组数据。每组包括 12 行。 tibble 的第三列的值必须介于 1 和 12 之间。大多数行都填充了第 3 列中的所有行，但有些行没有。对于那些缺少数据 (NA) 的行，我喜欢使用 dplyr 插入正确的值。我的挑战是第 3 列中的值不一定按数字顺序排列，因此我必须找到其中不存在的值，然后相应地插入。

我准备了一个 reprex 来演示我要描述的内容。在我的示例中，B 组缺少第 3 列中的最后两个值。第 3 列中唯一不存在的值是 4 和 7。因此，我想找到一种方法来确定可以插入哪些值，然后使用mutate 将 NA 值更新为未使用的值。这是我为满足 mlogit 数据框的数据要求所做的全部努力。

library(tidyverse)

df_BEFORE <- tibble( key = c("A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B"),y=c(2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2), z=c(1,2,3,4,5,6,7,8,9,10,11,12,2,11,3,6,12,8,9,10,1,5,NA,NA))

print(df_BEFORE, n=24)
#> # A tibble: 24 x 3
#>    key       y     z
#>    <chr> <dbl> <dbl>
#>  1 A         2     1
#>  2 A         2     2
#>  3 A         2     3
#>  4 A         2     4
#>  5 A         2     5
#>  6 A         2     6
#>  7 A         2     7
#>  8 A         2     8
#>  9 A         2     9
#> 10 A         2    10
#> 11 A         2    11
#> 12 A         2    12
#> 13 B         2     2
#> 14 B         2    11
#> 15 B         2     3
#> 16 B         2     6
#> 17 B         2    12
#> 18 B         2     8
#> 19 B         2     9
#> 20 B         2    10
#> 21 B         2     1
#> 22 B         2     5
#> 23 B         2    NA
#> 24 B         2    NA

df_AFTER <- df_BEFORE %>% 
  group_by(key) 


target_df  <- tibble( key = c("A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B"),y=c(2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2), z=c(1,2,3,4,5,6,7,8,9,10,11,12,2,11,3,6,12,8,9,10,1,5,4,7))

print(target_df, n=24)
#> # A tibble: 24 x 3
#>    key       y     z
#>    <chr> <dbl> <dbl>
#>  1 A         2     1
#>  2 A         2     2
#>  3 A         2     3
#>  4 A         2     4
#>  5 A         2     5
#>  6 A         2     6
#>  7 A         2     7
#>  8 A         2     8
#>  9 A         2     9
#> 10 A         2    10
#> 11 A         2    11
#> 12 A         2    12
#> 13 B         2     2
#> 14 B         2    11
#> 15 B         2     3
#> 16 B         2     6
#> 17 B         2    12
#> 18 B         2     8
#> 19 B         2     9
#> 20 B         2    10
#> 21 B         2     1
#> 22 B         2     5
#> 23 B         2     4
#> 24 B         2     7

---

更新:我有一个可行的解决方案，但它丑陋的呼噜声可能会有所帮助

---

library(tidyverse)

df_BEFORE <- tibble( key = c("A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B"),y=c(2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2), z=c(1,2,3,4,5,6,7,8,9,10,11,12,2,11,3,6,12,8,9,10,1,5,NA,NA))

print(df_BEFORE, n=24)
#> # A tibble: 24 x 3
#>    key       y     z
#>    <chr> <dbl> <dbl>
#>  1 A         2     1
#>  2 A         2     2
#>  3 A         2     3
#>  4 A         2     4
#>  5 A         2     5
#>  6 A         2     6
#>  7 A         2     7
#>  8 A         2     8
#>  9 A         2     9
#> 10 A         2    10
#> 11 A         2    11
#> 12 A         2    12
#> 13 B         2     2
#> 14 B         2    11
#> 15 B         2     3
#> 16 B         2     6
#> 17 B         2    12
#> 18 B         2     8
#> 19 B         2     9
#> 20 B         2    10
#> 21 B         2     1
#> 22 B         2     5
#> 23 B         2    NA
#> 24 B         2    NA

get_index <- function(c,j){
  case_when(
    is.na(j) ~ 0,
    j == 0 ~ 0,
    j ==1 ~ setdiff(1:12,c)[1],
    j ==2 ~ setdiff(1:12,c)[2],
    j ==3 ~ setdiff(1:12,c)[3],
    j ==4 ~ setdiff(1:12,c)[4],
    j ==5 ~ setdiff(1:12,c)[5],
    j ==6 ~ setdiff(1:12,c)[6],
    j ==7 ~ setdiff(1:12,c)[7],
    j ==8 ~ setdiff(1:12,c)[8],
    j ==9 ~ setdiff(1:12,c)[9],
    j ==10 ~ setdiff(1:12,c)[10],
    j ==11 ~ setdiff(1:12,c)[11]
  )
}


df_BEFORE %>% 
  group_by(key) %>% 
  mutate(seed = 1) %>% 
  mutate(flag = if_else(!is.na(z),0,seed)) %>% 
  mutate(j = flag + lag(flag)) %>%
  mutate(temp = z) %>%
  mutate(new_z = if_else(j==1,setdiff(1:12,temp)[1],
                 if_else(j==2,setdiff(1:12,temp)[2],
                 if_else(j==3,setdiff(1:12,temp)[3],
                 if_else(j==4,setdiff(1:12,temp)[4],
                 if_else(j==2,setdiff(1:12,temp)[5],
                 if_else(j==2,setdiff(1:12,temp)[6],
                 if_else(j==2,setdiff(1:12,temp)[7],
                 if_else(j==2,setdiff(1:12,temp)[8],
                 if_else(j==2,setdiff(1:12,temp)[9],
                 if_else(j==2,setdiff(1:12,temp)[2],0L
                                 ))))))))))) %>%
  mutate(z= if_else(is.na(z),as.numeric(new_z),z)) %>%
  select(-j, -new_z,-temp, -flag, -seed) %>% 
  print(n=24)
#> # A tibble: 24 x 3
#> # Groups:   key [2]
#>    key       y     z
#>    <chr> <dbl> <dbl>
#>  1 A         2     1
#>  2 A         2     2
#>  3 A         2     3
#>  4 A         2     4
#>  5 A         2     5
#>  6 A         2     6
#>  7 A         2     7
#>  8 A         2     8
#>  9 A         2     9
#> 10 A         2    10
#> 11 A         2    11
#> 12 A         2    12
#> 13 B         2     2
#> 14 B         2    11
#> 15 B         2     3
#> 16 B         2     6
#> 17 B         2    12
#> 18 B         2     8
#> 19 B         2     9
#> 20 B         2    10
#> 21 B         2     1
#> 22 B         2     5
#> 23 B         2     4
#> 24 B         2     7

^{由 reprex package 创建于 2020-05-03 (v0.3.0)}

Answer 1

您在评论中的想法是正确的，但您可以使用 replace() 而不是 if_else():

df_BEFORE %>% 
  group_by(key) %>% 
  mutate(z = replace(z, is.na(z), setdiff(1:12, z)))