filtering out rows across many columns in R

user_12742992 0
Viewed 42

I have data with about 250 columns. A sample looks a bit like below:

shop1_freq shop1_enj shop2_freq shop2_enj
0 9 3 6
999 9 2 1
3 2 999 2
4 1 3 4

I want to remove rows (which represent participants) if they have "999" scored in any column because this represents an incorrect response. I have tried some simple code below, but this does not work.

data %>%
  filter_all(!= 999)

A score of 999 is only possible from about column 67 to 167, but I thought it would be easier to just specify in the code across all columns in the data.

Are there any better approaches to this?

r
2 Answers

We can use if_all

library(dplyr)
data %>% 
  filter(if_all(everything(), ~ . != 999))
#    shop1_freq shop1_enj shop2_freq shop2_enj
#1          0         9          3         6 
#2          4         1          3         4

Or use if_any to check whether there are '999' in any of the column and negate (!)

data %>% 
   filter(!if_any(everything(), ~ . == 999))

Or using the OP's approach (which is kind of deprecated)

data %>%
  filter_all(all_vars(.!= 999))
#  shop1_freq shop1_enj shop2_freq shop2_enj
#1          0         9          3         6
#2          4         1          3         4

Or using base R with rowSums to construct a logical vector

data[rowSums(data == 999) == 0,]

Or replace the 999 with NA and use na.omit

na.omit(replace(data, data == 999, NA))

data

data <- structure(list(shop1_freq = c(0L, 999L, 3L, 4L), shop1_enj = c(9L, 
9L, 2L, 1L), shop2_freq = c(3L, 2L, 999L, 3L), shop2_enj = c(6L, 
1L, 2L, 4L)), class = "data.frame", row.names = c(NA, -4L))
user_37322710

With dplyr:

df %>% 
  rowwise() %>% 
  filter(!any(c_across(everything()) == 999))

In base R:

df[-which(apply(df, 1, function(x) any(x == 999))), ]

or, even simpler:

df[!rowSums(df == 999) > 0,]
user_80399780
Related