Set certain values to NA with dplyr

by

You can use replace which is a bit faster than ifelse:

dat <-  dat %>% mutate(x = replace(x, x<0, NA))

You can speed it up a bit more by supplying an index to replace using which:

dat <- dat %>% mutate(x = replace(x, which(x<0L), NA))

On my machine, this cut the time to a third, see below.

Here’s a little comparison of the different answers, which is only indicative of course:

set.seed(24)
dat <- data.frame(x=rnorm(1e6))
system.time(dat %>% mutate(x = replace(x, x<0, NA)))
       User      System     elapsed
       0.03        0.00        0.03 
system.time(dat %>% mutate(x=ifelse(x<0,NA,x)))
       User      System     elapsed
       0.30        0.00        0.29 
system.time(setDT(dat)[x<0,x:=NA])
       User      System     elapsed
       0.01        0.00        0.02 
system.time(dat$x[dat$x<0] <- NA)
       User      System     elapsed
       0.03        0.00        0.03 
system.time(dat %>% mutate(x = "is.na<-"(x, x < 0)))
       User      System     elapsed
       0.05        0.00        0.05 
system.time(dat %>% mutate(x = NA ^ (x < 0) * x))
       User      System     elapsed
       0.01        0.00        0.02 
system.time(dat %>% mutate(x = replace(x, which(x<0), NA)))
       User      System     elapsed
       0.01        0.00        0.01

(I’m using dplyr_0.3.0.2 and data.table_1.9.4)

Since we’re always very interested in benchmarking, especially in the course of data.table-vs-dplyr discussions I provide another benchmark of 3 of the answers using microbenchmark and the data by akrun. Note that I modified dplyr1 to be the updated version of my answer:

set.seed(285)
dat1 <- dat <- data.frame(x=sample(-5:5, 1e8, replace=TRUE), y=rnorm(1e8))
dtbl1 <- function() {setDT(dat)[x<0,x:=NA]}
dplr1 <- function() {dat1 %>% mutate(x = replace(x, which(x<0L), NA))}
dplr2 <- function() {dat1 %>% mutate(x = NA ^ (x < 0) * x)}
microbenchmark(dtbl1(), dplr1(), dplr2(), unit="relative", times=20L)
#Unit: relative
#    expr      min       lq   median       uq      max neval
# dtbl1() 1.091208 4.319863 4.194086 4.162326 4.252482    20
# dplr1() 1.000000 1.000000 1.000000 1.000000 1.000000    20
# dplr2() 6.251354 5.529948 5.344294 5.311595 5.190192    20

More Related Contents:

  1. Gather multiple sets of columns
  2. How to get summary statistics by group
  3. Is there a dplyr equivalent to data.table::rleid?
  4. Replace NA with previous or next value, by group, using dplyr
  5. Filter rows which contain a certain string
  6. Calculate group mean, sum, or other summary stats. and assign column to original data
  7. Rolling mean (moving average) by group/id with dplyr
  8. Programming with dplyr using string as input
  9. Count number of rows by group using dplyr
  10. How to strsplit different number of strings in certain column by do function
  11. Pass a vector of variable names to arrange() in dplyr
  12. Efficient way to filter one data frame by ranges in another
  13. R – add column that counts sequentially within groups but repeats for duplicates
  14. Categorize numeric variable with mutate
  15. Means multiple columns by multiple groups [duplicate]
  16. Select columns based on string match – dplyr::select
  17. Delete columns/rows with more than x% missing
  18. How to aggregate a dataframe by week?
  19. adding default values to item x group pairs that don’t have a value (df %>% spread %>% gather seems strange)
  20. dplyr issues when using group_by(multiple variables)
  21. dplyr piping data – difference between `.` and `.x`
  22. dplyr mutate rowwise max of range of columns
  23. dplyr: lead() and lag() wrong when used with group_by()
  24. Reorder rows using custom order
  25. Difference between rbind() and bind_rows() in R
  26. Spreading a two column data frame with tidyr
  27. R dplyr rowwise mean or min and other methods?
  28. Run a custom function on a data frame in R, by group
  29. Dealing with spaces and “weird” characters in column names with dplyr::rename()
  30. Filtering rows in R unexpectedly removes NAs when using subset or dplyr::filter

Leave a Comment