r - 清理R中组内的数据

Question

我的调查数据存在拼写错误、缺失数据等问题。我需要填充/清理组内的数据，考虑到如果组内的变量有任何有效值(确保只有一个有效值)，它应该替换同一组中变量中的其他值。请参阅下面的模拟数据和预期结果。我正在阅读有关使用 group_by 填充、变异的内容，但仍然不知道如何做到这一点。

想法:如果值不在(“Unknown”、“NA、“N/A”、“null”)中，则将该值复制到组内的变量中

数据:

data <- data.frame(group = c("A1", "A1", "A1", "A1", "A2", "A2", "B4", "B4", "B4", "C1"),
                   number.persons = c("4",NA,NA, "N/A", "unknow", "2", "3", "3", NA,"1"),
                   own.rent = c("own", "own", NA, "N/A", "rent", NA, "own", "N/A", "own", "own"),
                   car      =c("yes", "yes", NA, "unkwon", "no", NA, "no", "no", "unknwon", "no"))

期望的结果:

new.data <- data.frame(group = c("A1", "A1", "A1", "A1", "A2", "A2", "B4", "B4", "B4", "C1"),
                       number.persons = c("4","4","4", "4", "2", "2", "3", "3", "3","1"),
                       own.rent = c("own", "own", "own", "own", "rent", "rent", "own", "own", "own", "own"),
                       car      =c("yes", "yes", "yes", "yes", "no", "no", "no", "no", "no", "no"))

我想到的一个解决方案:

data.pre <- data %>%
            mutate(flag.number.persons = ifelse(!number.persons %in% c("unknow", "unkwon","unknwon","null", "na", "n/a", "N/A", "NA", "") & !is.na(number.persons), 1, 0),
                   flag.own.rent = ifelse(!own.rent %in% c("unknow", "unkwon","null", "unknwon","na", "n/a", "N/A", "NA", "") & !is.na(own.rent), 1, 0),
                   flag.car      = ifelse(!car %in% c("unknow", "unkwon","null", "unknwon","na", "n/a", "N/A", "NA", "") & !is.na(car), 1, 0)) %>%
            group_by(group) %>%
            mutate(number.persons2 = ifelse(flag.number.persons==0, number.persons[flag.number.persons==1], number.persons),
                   own.rent2      = ifelse(flag.own.rent == 0, own.rent[flag.own.rent==1], own.rent),
                   car2            = ifelse(flag.car == 0 , car[flag.car==1], car))

使用新变量查看新数据，看看它如何在我的代码中工作:

子集(data.pre, select=c(group, number.persons2, own.rent2, car2))

到目前为止，我的解决方案有点乏味，因为我正在根据建立的条件创建新变量作为标记来标记“有效值”，然后用有效值进行变异替换(实际上我正在创建另一个变量只是为了测试代码)，但首先，创建如此多的变量既缓慢又困惑，其次我有大约 500 个变量，然后为每个变量创建标志没有任何意义。有没有更快/优雅的方法来做到这一点？也许是一个接收我想要清理的变量和无效值列表的函数？

版本

我尝试了收到的选项，但当组中只有一个观察值并且缺少任何变量时出现错误

数据已编辑

使用的代码来自 Rui Barradas

data %>%
 group_by(group) %>%
 mutate(across(everything(), ~ if_else(.x == "N/A", NA_character_, .x)),
 across(everything(), ~ if_else(grepl("^unk", .x), NA_character_, .x))) %>%
 mutate(across(everything(), na.locf))

错误

Answer 1

如果以“unk”开头的几个单词代表unknown”，那么就有几个错别字，下面的mutate就变得有点乱.但没什么可绝望的。

suppressPackageStartupMessages({
  library(dplyr)
  library(zoo)
})

data <- data.frame(group = c("A1", "A1", "A1", "A1", "A2", "A2", "B4", "B4", "B4", "C1"),
                   number.persons = c("4",NA,NA, "N/A", "unknow", "2", "3", "3", NA,"1"),
                   own.rent = c("own", "own", NA, "N/A", "rent", NA, "own", "N/A", "own", "own"),
                   car = c("yes", "yes", NA, "unkwon", "no", NA, "no", "no", "unknwon", "no"))

data %>%
  group_by(group) %>%
  mutate(across(everything(), ~ if_else(.x == "N/A", NA_character_, .x)),
         across(everything(), ~ if_else(grepl("^unk", .x), NA_character_, .x))) %>%
  mutate(across(everything(), na.locf))
#> # A tibble: 10 × 4
#> # Groups:   group [4]
#>    group number.persons own.rent car  
#>    <chr> <chr>          <chr>    <chr>
#>  1 A1    4              own      yes  
#>  2 A1    4              own      yes  
#>  3 A1    4              own      yes  
#>  4 A1    4              own      yes  
#>  5 A2    2              rent     no   
#>  6 A2    2              rent     no   
#>  7 B4    3              own      no   
#>  8 B4    3              own      no   
#>  9 B4    3              own      no   
#> 10 C1    1              own      no

^{由reprex package于2022年9月9日创建(v2.0.1)}

---

编辑

简化，来自 TarJae's answer .

pattern <- paste("N/A", "^unk", sep = "|")
data %>%
  group_by(group) %>%
  mutate(across(everything(), ~ if_else(grepl(pattern, .x), NA_character_, .x)),
         across(everything(), na.locf))

^{由reprex package于2022年9月9日创建(v2.0.1)}