Filter rows in data.frame based on the length of values in all groups

user_10148569 0
Viewed 231

I have a data.frame like this:

df<-data.frame( Id = paste0("g",1:6),
a= c(6:11),
b = c(10:13,NA,NA),
c = c(7:10,NA,10),
d = c(NA,7:9,NA,13),
e= c(NA,6:10),
f= c(NA,NA,NA,4:5,NA))
colnames(df)=c("ID",rep("normal",3),rep("patient",3))

> df
  ID normal normal normal patient patient patient
1 g1      6     10      7      NA      NA      NA
2 g2      7     11      8       7       6      NA
3 g3      8     12      9       8       7      NA
4 g4      9     13     10       9       8       4
5 g5     10     NA     NA      NA       9       5
6 g6     11     NA     10      13      10      NA

this df contains data for two groups (normal and patient).I am going to perform some analysis for all rows, therefore all groups in each rows must have at least two values.I used the following codes to filter the rows that all groups have not at least two values.

    fx=function(x){length(x[!is.na(x)])>=2}
    f1=apply(df[,2:4], 1,fx)#filter based on group normal
    f2=apply(df[,5:7], 1,fx)#filter based on group patient
    df=subset(df,f1&f2)
> df
  ID normal normal.1 normal.2 patient patient.1 patient.2
2 g2      7       11        8       7         6        NA
3 g3      8       12        9       8         7        NA
4 g4      9       13       10       9         8         4
6 g6     11       NA       10      13        10        NA

but these codes are useful for a data with limited groups. my main data have 100 groups(and all groups have 3 replicates),colnames(df)=paste0("grp",sort(rep(1:100,3))) therefore I need some simple codes to filter the rows in a data.frame with 100 groups.

my goal: delete the rows that have not at least two values in each groups.

reshape dataframe r
3 Answers

Could do:

library(dplyr)

names(df) <- paste0(names(df), 1:ncol(df))

df %>%
  filter(
    rowSums(!is.na(select(., contains("normal")))) >= 2 &
      rowSums(!is.na(select(., contains("patient")))) >= 2
  )
user_83890030

We could differentiate "normal" and "patient" columns and select the rows using rowSums

normal_cols <- grep("normal", names(df))
patient_cols <- grep("patient", names(df))
df[rowSums(!is.na(df[normal_cols])) >= 2 & rowSums(!is.na(df[patient_cols])) >= 2,]

#  ID normal normal normal patient patient patient
#2 g2      7     11      8       7       6      NA
#3 g3      8     12      9       8       7      NA
#4 g4      9     13     10       9       8       4
#6 g6     11     NA     10      13      10      NA

Or using the fx function you have defined we can use apply twice on both set of columns and select the rows using subset.

fx = function(x) {length(x[!is.na(x)])>=2}
subset(df, apply(df[normal_cols], 1,fx) & apply(df[patient_cols], 1,fx))
user_39629140

We may use reshape to get a long format and look at the colSums.

First rule in such matters are appRopriate column names, i.e. <chr_prefix>.<num_suffix>.

names(df) <- c("ID", paste(rep(c("normal", "patient"), each=3), 1:3, sep="."))

Now we reshape into long format and split by "ID". We only want those IDs where all colSums are > 2, we store this in a vector s with which we may subset the data frame df.

r <- reshape(df, idvar="ID", direction="long", varying=list(2:4, 5:7), times=1:3)
s <- by(r[-1], r$ID, function(i) all(colSums(i, na.rm=TRUE) > 2))
df[s, ]
#   ID normal normal normal patient patient patient
# 2 g2      7     11      8       7       6      NA
# 3 g3      8     12      9       8       7      NA
# 4 g4      9     13     10       9       8       4
# 6 g6     11     NA     10      13      10      NA

Data

df <- structure(list(Id = structure(1:6, .Label = c("g1", "g2", "g3", 
"g4", "g5", "g6"), class = "factor"), a = 6:11, b = c(10L, 11L, 
12L, 13L, NA, NA), c = c(7, 8, 9, 10, NA, 10), d = c(NA, 7, 8, 
9, NA, 13), e = c(NA, 6L, 7L, 8L, 9L, 10L), f = c(NA, NA, NA, 
4L, 5L, NA)), class = "data.frame", row.names = c(NA, -6L))
user_65740380
Related