Replace all partial string entries with NA

user_16441312 0
Viewed 66

I have a data frame similar to:

df<-as.data.frame(cbind(rep("Canada",6), 
                          c(rep("Alberta",3), rep("Manitoba",2),rep("Unknown_province",1)), 
                          c("Edmonton", "Unknown_city","Unknown_city","Brandon","Unknown_city","Unknown_city")))

colnames(df)<- c("Country","Province","City")

I would like to substitute all entries that contain "Unknown" with NA.

I have tried using grepl, but it removes all entries for that variable if one entry matches, I would like to only replace individual cells.

df[grepl("Unknown", df, ignore.case=TRUE)] <- NA

r
5 Answers
df1 <- df # This is to ensure that we can refert back to df incase there is an issue

Then you could use any of the following:

is.na(df1) <- array(grepl('Unknown', as.matrix(df1)), dim(df1))
df1
  Country Province     City
1  Canada  Alberta Edmonton
2  Canada  Alberta     <NA>
3  Canada  Alberta     <NA>
4  Canada Manitoba  Brandon
5  Canada Manitoba     <NA>
6  Canada     <NA>     <NA>

or even:

df1[] <- sub("Unknown.*", NA, as.matrix(df1), ignore.case = TRUE)
df1
  Country Province     City
1  Canada  Alberta Edmonton
2  Canada  Alberta     <NA>
3  Canada  Alberta     <NA>
4  Canada Manitoba  Brandon
5  Canada Manitoba     <NA>
6  Canada     <NA>     <NA>

Note that grepl and even sub are vectorized hence no need to use the *aply family or even for loops

user_83802720

Here is one possible way to solve your problem:

df[] <- lapply(df, function(x) ifelse(grepl("Unknown", x, TRUE), NA, x))
df

#   Country Province     City
# 1  Canada  Alberta Edmonton
# 2  Canada  Alberta     <NA>
# 3  Canada  Alberta     <NA>
# 4  Canada Manitoba  Brandon
# 5  Canada Manitoba     <NA>
# 6  Canada     <NA>     <NA>
user_108488980

Using dplyr

library(dplyr)
library(stringr)
df %>% 
    mutate(across(everything(),
    ~ case_when(str_detect(., 'Unknown', negate = TRUE) ~  .)))
  Country Province     City
1  Canada  Alberta Edmonton
2  Canada  Alberta     <NA>
3  Canada  Alberta     <NA>
4  Canada Manitoba  Brandon
5  Canada Manitoba     <NA>
6  Canada     <NA>     <NA>
user_37322710

I like to use replace() in such cases in which values in a vector are replaced or left as is, depending on a condition :

library(dplyr)
library(stringr)

df%>%mutate(across(everything(), ~replace(.x, str_detect(.x, 'Unknown'), NA)))

  Country Province     City
1  Canada  Alberta Edmonton
2  Canada  Alberta     <NA>
3  Canada  Alberta     <NA>
4  Canada Manitoba  Brandon
5  Canada Manitoba     <NA>
6  Canada     <NA>     <NA>
user_139723330 
df[]<- lapply(df, gsub, pattern = "Unknown", replacement = NA, fixed = TRUE)
user_60397560
Related