Select rows based on condition from each column

Question

I have data that I am analysing for a lab and I'm trying to use R for the first time.

I've been reading about selecting rows based on conditions but I can't seem to find the way to do it for my data.

I made a data frame and I didn't name the columns. Each column is a particular variant of a bacterial species that I am testing and its increasing values of OD/absorbance (in total 56 rows for each column) over a period of about 15 hours.

I want to select the rows with values ranging from 0.2 to 0.4 from EACH column.

So ideally I want something like:

   V1       V2
9  0.2100  7 0.2181
10 0.3017  8 0.3162
11 0.4079  9 0.4137

etc.

I guess I can select the rows manually from each column but there must be a quicker way.

I then plan to calculate the mean of each column of the subset.

Any help would be much appreciated, thanks!

Answer 1

in this example you get a list with a vector of values between 0.2 to 0.4 for each variable. Hope it helps

df=data.frame(V1=c(1,0.3,2,.1,.5,8,.1,.4,.35,.22,6),V2=c(0.2,0.3,3,.15,.32,5,.1,.45,.35,.3,6))
filteredColumns<-sapply(df,function(x) x[x>0.2&x<0.4])

Answer 2

This will do it:

findNearest3 <- function(x, y, z){
  temp <- sort(x[x > z[1] & x < z[2]])
  point <- which(abs(temp-y)==min(abs(temp-y)))
  return(temp[c(point-1, point, point+1)])
}

The function will look for the nearest value to y within vector x, constrained by limits z, and return this value plus the one before and after within the sorted vector.

Example:

set.seed(123)
df <- data.frame(x = rnorm(100), y = rnorm(100))

sapply(df, findNearest3, .3, c(.2, .4))
             x         y
[1,] 0.2533185 0.2982276
[2,] 0.3035286 0.3011534
[3,] 0.3317820 0.3104807

Now with

sapply(df, function(x) mean(findNearest3(x, .3, c(.2, .4))))

you'll get the means:

        x         y 
0.2962097 0.3032872 

Be aware that this will return NA if there are not enough values within the given constrains z:

df <- data.frame(x = c(.1, .23, .35, .5), y = c(.22, .24, .33, .48))

> sapply(df, findNearest3, .3, c(.2, .4))
        x    y
[1,] 0.23 0.24
[2,] 0.35 0.33
[3,]   NA   NA

> sapply(df, function(x) mean(findNearest3(x, .3, c(.2, .4)), na.rm = T))
    x     y 
0.290 0.285 

---

Edit: To return the row positions of the values instead of the values themselves, just change the last line of the code:

findNearest3.pos <- function(x, y){
  temp <- sort(x)
  point <- which(abs(temp-y)==min(abs(temp-y)))
  return(c(point-1, point, point+1))
}

---

Application: To use it on another dataframe of the same dimensions, first save the positions in a list:

myrows <- lapply(df, findNearest3.pos, y = .3)

and then subset the second dataframe:

set.seed(234)
df1 <- data.frame(x = rnorm(100), y = rnorm(100))

newsubset <- mapply(function(x, y) x[y], df1, myrows)
              x        y
[1,] -0.9581388 2.214151
[2,]  0.6280635 0.455070
[3,]  0.6625872 0.513053

Considering the other dataframe with only one column, you need to decide which column's row position you want to use.

set.seed(345)
df2 <- data.frame(x = rnorm(100))

You could access the row positions found in V1 (or, in this example x) with:

df2[myrows[[1]],]
[1]  0.2986353 -0.9917691 -0.6510206

and those found in V2 (here named y) with:

df2[myrows[[2]],]
[1] -0.3148442 -0.2491949  0.6854260