Replace values outside range with NA using replace_with_na function

user_13349937 0
Viewed 818

I have the following dataset

structure(list(a = c(2, 1, 9, 2, 9, 8), b = c(4, 5, 1, 9, 12, 
NA), c = c(50, 34, 77, 88, 33, 60)), class = "data.frame", row.names = c(NA, 
-6L))

  a  b  c
1 2  4 50
2 1  5 34
3 9  1 77
4 2  9 88
5 9 12 33
6 8 NA 60

From column b I only want values between 4-9. Column c between 50-80. Replacing the values outside the range with NA, resulting in 

structure(list(a = c(2, 1, 9, 2, 9, 8), b = c(4, 5, NA, 9, NA, 
NA), c = c(50, NA, 77, NA, NA, 60)), class = "data.frame", row.names = c(NA, 
-6L))

  a  b  c
1 2  4 50
2 1  5 NA
3 9 NA 77
4 2  9 NA
5 9 NA NA
6 8 NA 60

I've tried several things with replace_with_na_at function where this seemed most logical:

test <- replace_with_na_at(data = test, .vars="c",
                          condition = ~.x < 2 & ~.x > 2)

However, nothing I tried works. Does somebody know why? Thanks in advance! :)

data-cleaning na dataframe r replace
5 Answers

You can subset with a logical vector testing your conditions.

x$b[x$b < 4 | x$b > 9] <- NA
x$c[x$c < 50 | x$c > 80] <- NA
x
#  a  b  c
#1 2  4 50
#2 1  5 NA
#3 9 NA 77
#4 2  9 NA
#5 9 NA NA
#6 8 NA 60

Data:

x <- structure(list(a = c(2, 1, 9, 2, 9, 8), b = c(4, 5, 1, 9, 12, 
NA), c = c(50, 34, 77, 88, 33, 60)), class = "data.frame", row.names = c(NA, 
-6L))
user_104885040

Yet another base R solution, this time with function is.na<-

is.na(test$b) <- with(test, b < 4 | b > 9)
is.na(test$c) <- with(test, c < 50 | c > 80)

A package naniar solution with a pipe could be

library(naniar)
library(magrittr)

test %>%
  replace_with_na_at(
    .vars = 'b',
    condition = ~(.x < 4 | .x > 9)
  ) %>%
  replace_with_na_at(
    .vars = 'c',
    condition = ~(.x < 50 | .x > 80)
  )
user_82454060

You simply could use Map to replace your values with NA.

dat[2:3] <- Map(function(x, y) {x[!x %in% y] <- NA;x}, dat[2:3], list(4:9, 50:80))
dat
#   a  b  c
# 1 2  4 50
# 2 1  5 NA
# 3 9 NA 77
# 4 2  9 NA
# 5 9 NA NA
# 6 8 NA 60

Data:

dat <- structure(list(a = c(2, 1, 9, 2, 9, 8), b = c(4, 5, 1, 9, 12, 
NA), c = c(50, 34, 77, 88, 33, 60)), class = "data.frame", row.names = c(NA, 
-6L))
user_65740380

You should mention the packages you are using. From googling, i'm guessing you are using naniar. The problem appears to be that you did not properly specify the condition, but the following should work: 

library(naniar)

test <- structure(list(a = c(2, 1, 9, 2, 9, 8), 
                    b = c(4, 5, 1, 9, 12, NA),
                    c = c(50, 34, 77, 88, 33, 60)), 
               class = "data.frame", 
               row.names = c(NA, -6L)) 

replace_with_na_at(test, "c", ~.x < 50 | .x > 80)
#>   a  b  c
#> 1 2  4 50
#> 2 1  5 NA
#> 3 9  1 77
#> 4 2  9 NA
#> 5 9 12 NA
#> 6 8 NA 60

Created on 2020-06-02 by the reprex package (v0.3.0)

user_119329360

We can use map2

library(purrr)
library(dplyr)
df1[c('b', 'c')] <- map2(df1 %>% 
       select(b, c), list(c(4, 9), c(50,80)), ~ 
           replace(.x, .x < .y[1]|.x > .y[2], NA))
user_37322710
Related