cleaning data within groups in R

Question

I have data from survey that comes with typos, missing data and others. I need to populate/clean data within groups, considering that if there is any valid value (making sure there is just one valid value) for the variable within the group, it should replace the other values in variable in same group. See below my mock data and the result expected. I am being reading about fill, mutate using group_by but still have no idea on how to do it.

Idea: if value is not in ("Unknown", "NA, "N/A", "null"), then copy that value in the variable within the group

Data:

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"))

Desired result:

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"))

One solution I came up:

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))

Looking at the new data with new variables to see how it worked my code:

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

My solution so far is kind of tedious since I am making new variables as flags to tag the "valid value" according conditions stablished and then mutate replacing with the valid values ( I am actually making another variables just to test code), but first, making so many variables is slow and a mess, and second I have like 500 variables then making flags for each one doesn't make any sense. Is there a faster/elegant way to do this? Maybe a function that receive the variables I want to clean and the list of no-valid values?

Edition

I've trying the options received and getting errors when there is only one observation in the group and any variable is missing

Data edited

Code used from 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))

Error

Jon Spring · Answer

Here's a shorthand approach where I specify the column(s) that should be numbers, and another with text where I specify the allowable vocabulary. Any other answers are made NA and then I fill within groups.

You could make this as specific as to which columns have what vocabulary as you like. I combined the own/rent and yes/no answer into one allowable vocabulary, but those could be separate.

In the example data, all the wrong non-NA's have "unk" in them, but I wouldn't be surprised to see also "not known", "none", "not avail", "na", "don't have" etc which would fail that filter. For a colorful example, someone on Twitter noted that the public data for federal PPP Loans included 57 spellings of "Philadelphia": https://twitter.com/dataeditor/status/1280278987797942272

I expect that for this type of data, where there are many fields with free text with few plausible results, it will often be easier to specify a whitelist than to imagine a comprehensive blacklist. (But there's nothing preventing you from using both approaches; if some fields have a wide potential permitted vocabulary, blacklisting may make more sense. Mix and match!)

library(tidyverse)
data %>%
  mutate(across(number.persons, ~parse_number(.x)),
         across(own.rent:car, ~if_else(.x %in% c("own", "rent", "yes", "no"),
                                       .x, NA))) %>%
  group_by(group) %>%
  fill(-group, .direction = "downup") %>%
  ungroup()


   group number.persons own.rent car  
                  
 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

Rui Barradas · Answer

If the several words beginning with "unk" represent unknown", then there are several typos and the mutate below becomes a bit messy. But nothing to despair.

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  
#>                   
#>  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

^{Created on 2022-09-09 by the reprex package (v2.0.1)}

Edit

Simplification, from 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))

^{Created on 2022-09-09 by the reprex package (v2.0.1)}

TarJae · Answer

Here is an alternative approach similar to @Jon Spring's answer:

library(dplyr)
library(stringr)
library(tidyr)

pattern <- paste(c("unknow", "N/A", "unkwon", "unknwon"), collapse = "|")

data %>% 
  group_by(group) %>% 
  mutate(across(everything(), ~ifelse(str_detect(., pattern), NA, .))) %>% 
  fill(-group, .direction = "downup")

   group number.persons own.rent car  
                  
 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

cleaning data within groups in R

Edition

3 Answers

Edit