4

I have a dataset with 70 columns.

I would like to subset entire rows of the dataset where a value in any column 5 through 70 is greater than the value 7.

I have tried the following code, however, I do not want TRUE/FALSE values. I would just like the rows that do not meet the criteria eliminated from the data frame

subset <- (data[, 5:70] > 7)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
user1977802
  • 313
  • 6
  • 10
  • 18

3 Answers3

5

We can use rowSums

data[rowSums(data[5:70] > 7) > 0, ]

Or with subset

subset(data, rowSums(data[5:70] > 7) > 0)

We can also use filter_at from dplyr with any_vars

library(dplyr)
data %>% filter_at(vars(5:70), any_vars(. > 7))

Using reproducible data from mtcars (stealing idea from @Maurits Evers)

mtcars[rowSums(mtcars[3:11] > 300) > 0, ]

#                     mpg cyl disp  hp drat    wt  qsec vs am gear carb
#Hornet Sportabout   18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
#Duster 360          14.3   8  360 245 3.21 3.570 15.84  0  0    3    4
#Cadillac Fleetwood  10.4   8  472 205 2.93 5.250 17.98  0  0    3    4
#Lincoln Continental 10.4   8  460 215 3.00 5.424 17.82  0  0    3    4
#Chrysler Imperial   14.7   8  440 230 3.23 5.345 17.42  0  0    3    4
#Dodge Challenger    15.5   8  318 150 2.76 3.520 16.87  0  0    3    2
#AMC Javelin         15.2   8  304 150 3.15 3.435 17.30  0  0    3    2
#Camaro Z28          13.3   8  350 245 3.73 3.840 15.41  0  0    3    4
#Pontiac Firebird    19.2   8  400 175 3.08 3.845 17.05  0  0    3    2
#Ford Pantera L      15.8   8  351 264 4.22 3.170 14.50  0  1    5    4
#Maserati Bora       15.0   8  301 335 3.54 3.570 14.60  0  1    5    8

Using filter_at also gives the same output

mtcars %>% filter_at(vars(3:11), any_vars(. > 300))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
3

say this is your data:

dat <- data.frame(X=sample(1:10, 10, T), 
                  Y = sample(1:10, 10, T), 
                  stringsAsFactors = F)

you can use the subset command to extract what you want:

sub <- subset(dat, X > 7 | Y > 7)
morgan121
  • 2,213
  • 1
  • 15
  • 33
  • 1
    As mentioned by OP they have to check this for columns 5 to 70. This would not be feasible to type each column name and check if any of the column has value greater than 7. – Ronak Shah Apr 09 '19 at 02:34
  • ah sorry yeah, it was for fewer columns when I first posted this but you're right it's not feasible for 70 – morgan121 Apr 09 '19 at 02:56
1

You can use a combination of apply with MARGIN = 1 and any.

Reproducible example:

mtcars[apply(mtcars, 1, function(x) any(x > 300)), ]
#                     mpg cyl disp  hp drat    wt  qsec vs am gear carb
#Hornet Sportabout   18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
#Duster 360          14.3   8  360 245 3.21 3.570 15.84  0  0    3    4
#Cadillac Fleetwood  10.4   8  472 205 2.93 5.250 17.98  0  0    3    4
#Lincoln Continental 10.4   8  460 215 3.00 5.424 17.82  0  0    3    4
#Chrysler Imperial   14.7   8  440 230 3.23 5.345 17.42  0  0    3    4
#Dodge Challenger    15.5   8  318 150 2.76 3.520 16.87  0  0    3    2
#AMC Javelin         15.2   8  304 150 3.15 3.435 17.30  0  0    3    2
#Camaro Z28          13.3   8  350 245 3.73 3.840 15.41  0  0    3    4
#Pontiac Firebird    19.2   8  400 175 3.08 3.845 17.05  0  0    3    2
#Ford Pantera L      15.8   8  351 264 4.22 3.170 14.50  0  1    5    4
#Maserati Bora       15.0   8  301 335 3.54 3.570 14.60  0  1    5    8

Or in your case

data[apply(data[5:70], 1, function(x) any(x > 7)), ]

It's better (faster) to use direct [ indexing instead of subset, see e.g. Faster way to subset on rows of a data frame in R?

Maurits Evers
  • 49,617
  • 4
  • 47
  • 68