subsetting data frame in R

Question

I have a dataframe like this:

GENE    a1  a2  a3  b1  b2  b3
G1  862 817 923 1096    997 946
G2  443 410 421 448 417 388
G3  396 348 372 428 351 361
G4  350 319 310 394 295 311
G5  350 332 341 412 303 316
G6  377 369 397 462 330 351
G7  362 348 399 437 378 376
G8  332 312 398 387 372 332
G9  511 473 564 496 533 441
G10 42  54  48  24  19  17
G11 346 308 343 279 349 259
G12 273 255 265 199 270 206
G13 26  19  18  14  19  19
G14 17  9   10  8   9   11
G15 12  8   6   9   5   21

The first row is the header. I want to filter this dataframe such that I end up with those rows, where at least 3 columns have counts > 30 each

I did something like this

data <- read.table("test.txt",header=TRUE,sep="\t",row.names=1)
data <- data[rowSums(data) > 30,]

But this sums up the columns 1 through 6 and see whether the sum is >30. I want to do for each column > 30 and then out of those, 3 or greater number of columns have count 30. SO the output of my data frame should be

GENE    a1  a2  a3  b1  b2  b3
G1  862 817 923 1096    997 946
G2  443 410 421 448 417 388
G3  396 348 372 428 351 361
G4  350 319 310 394 295 311
G5  350 332 341 412 303 316
G6  377 369 397 462 330 351
G7  362 348 399 437 378 376
G8  332 312 398 387 372 332
G9  511 473 564 496 533 441
G10 42  54  48  24  19  17
G11 346 308 343 279 349 259
G12 273 255 265 199 270 206

How can I do this?

Thanks

score 4 · Answer 1 · answered May 06 '19 at 22:02

Consider your data.frame is DF

> rowSelect <- rowSums(DF[,-1]>30)>= 3
> DF[rowSelect, ]
   GENE  a1  a2  a3   b1  b2  b3
1    G1 862 817 923 1096 997 946
2    G2 443 410 421  448 417 388
3    G3 396 348 372  428 351 361
4    G4 350 319 310  394 295 311
5    G5 350 332 341  412 303 316
6    G6 377 369 397  462 330 351
7    G7 362 348 399  437 378 376
8    G8 332 312 398  387 372 332
9    G9 511 473 564  496 533 441
10  G10  42  54  48   24  19  17
11  G11 346 308 343  279 349 259
12  G12 273 255 265  199 270 206

A Little explanation:

DF[,-1]>30 tests which values for numerical columns are greater than 30

rowSums(.)>= 3 indicates which rows meet your condition: at least 3 cols have counts > 30 each

DF[rowSelect, ] selects those rows you asked for

Neat, didn't know about `DF[,-1]` – user3138373 May 06 '19 at 22:10 — user3138373, May 06 '19 at 22:10

score 1 · Accepted Answer · answered May 06 '19 at 22:08

Supposing your data is called dat, you can get the desired rows like this (assuming GENE has the row names, and is not an actual column):

dat[apply(dat, 1, function(x) sum(x > 30) >= 3), ]

If GENE is an actual column of the data frame (and is the first column), you can remove it from the apply calculation safely by using dat[, -1] instead of dat in the row selection:

dat[apply(dat[, -1], function(x) sum(x > 30) >= 3), ]

subsetting data frame in R

2 Answers2