Extract a List of values Filtered by criteria

1.2k views Asked by At

I have a list of data some rows need to be filtered.

I have some criteria to extract those rows which I called them crit. For each crit that qualifies data, I want to get sub-set of data as output.

Sometimes there is a set of functions that can extract a group of data from a larger data set based on specific criteria that you set. I think one of the best option would be dplyr. Although, I have watched some of dplyr package videos they are mainly focusing on sorting and selecting of rows on some simple examples. Sometimes, though, we need to be able to pull a set of criteria dynamically that change.

Thus, I need an expert consideration to dplyr functionality to my data.frame.

here is the reproducible example of my data

set.seed(1) 
data.list <- lapply(1:3, function(x) {
    nrep <- 3
    time <- rep(seq(90,54000,length.out=12),times=nrep) 
    Mx <- c(replicate(nrep,sort(runif(12,-0.014,0.012),decreasing=TRUE)))
    My <- c(replicate(nrep,sort(runif(12,-0.02,0.02),decreasing=TRUE)))
    Mz <- c(replicate(nrep,sort(runif(12,-1,1),decreasing=TRUE)))
    df <- data.frame(time,Mx,My,Mz,set_nbr=x)
})

from inside of this data.list I want to extract some unique groups that matches a condition.

Matching condition is defined from crit output.

> crit

    time         Mz       set_nbr
1 24594.55 -0.04729751    1
2 29495.45 -0.50902297    1
3 24594.55 -0.04376393    1
4 39297.27 -0.22218980    2
5 24594.55 -0.36407263    2
6 34396.36 -0.38341534    2
7 19693.64 -0.34597255    3
8 14792.73 -0.01480776    3
9 29495.45 -0.00999671    3

I find the first observation of negative Mz value inside of each data.list group. Here group means the values in between 90:54000 in time column that is one group. so each data.list[[1]] 3 group, data.list[[2]] 3 group data.list[[3]] 3 group.

I want to:

  1. Find min, and max time value of Mz grouped_by set_nbr in the crit output.

UPDATE

with the answer of @akrun this task is done by the following code

  min_time<- crit %>% 
  group_by(set_nbr) %>% 
  filter(time==min(time))

  max_time<- crit %>% 
  group_by(set_nbr) %>% 
  filter(time==max(time))
  1. Filter out these cases inside of data.list within the groups.

As for example, inside of data.list[[2]] if we want to extract min time value of Mz as concluded in crit output

 > data.list[[2]]
        time      Mx            My           Mz          set_nbr
1     90.000  0.0113804381  0.0145817980  0.887449637       2
2   4990.909  0.0100259362  0.0098679308  0.772901887       2
3   9891.818  0.0050266053  0.0091723849  0.754115086       2
4  14792.727  0.0046047177  0.0045857989  0.516206105       2
5  19693.636  0.0026426272  0.0022863816  0.448997785       2
6  24594.545  0.0015677851  0.0000176389  0.423487735       2
7  29495.455 -0.0023966069 -0.0018747422  0.095293174       2
8  34396.364 -0.0027816840 -0.0018971667  0.006678971       2
9  39297.273 -0.0047251003 -0.0068489072 -0.222189800       2
10 44198.182 -0.0101464994 -0.0127653456 -0.412539690       2
11 49099.091 -0.0113172099 -0.0129949293 -0.617479780       2
12 54000.000 -0.0136599830 -0.0158004944 -0.621612755       2
13    90.000  0.0117878041  0.0158037641  0.854604177       2
14  4990.909  0.0056253446  0.0152247614  0.681014064       2
15  9891.818  0.0014885119  0.0111993956  0.565702674       2
16 14792.727  0.0009466772  0.0011852241  0.181146318       2
17 19693.636 -0.0007095856 -0.0021505871  0.033593673       2
18 24594.545 -0.0011145670 -0.0034750316 -0.364072631       2
19 29495.455 -0.0014069124 -0.0065805003 -0.433534999       2
20 34396.364 -0.0021987173 -0.0086083808 -0.462098816       2
21 39297.273 -0.0080548883 -0.0088897627 -0.464983585       2
22 44198.182 -0.0086038271 -0.0114920192 -0.562709430       2
23 49099.091 -0.0094904993 -0.0169889702 -0.779278790       2
24 54000.000 -0.0119963261 -0.0174476608 -0.798253748       2
25    90.000  0.0116124758  0.0161232645  0.922819873       2
26  4990.909  0.0101439952  0.0158178170  0.895932709       2
27  9891.818  0.0037524900  0.0142452666  0.637269377       2
28 14792.727  0.0027126828  0.0136245822  0.526445379       2
29 19693.636  0.0016400717  0.0096431459  0.435870552       2
30 24594.545  0.0015504030  0.0089490379  0.125565872       2
31 29495.455  0.0005834194  0.0057726305  0.037152275       2
32 34396.364 -0.0003232792  0.0052165649 -0.383415339       2
33 39297.273 -0.0008013126  0.0042121379 -0.487264792       2
34 44198.182 -0.0072876859 -0.0043456288 -0.637663345       2
35 49099.091 -0.0077894144 -0.0047802446 -0.741686291       2
36 54000.000 -0.0130759449 -0.0064953867 -0.799718307       2

we would find the output like below:

 > min_setnbr 2
13    90.000  0.0117878041  0.0158037641  0.854604177       2
14  4990.909  0.0056253446  0.0152247614  0.681014064       2
15  9891.818  0.0014885119  0.0111993956  0.565702674       2
16 14792.727  0.0009466772  0.0011852241  0.181146318       2
17 19693.636 -0.0007095856 -0.0021505871  0.033593673       2
18 24594.545 -0.0011145670 -0.0034750316 -0.364072631       2
19 29495.455 -0.0014069124 -0.0065805003 -0.433534999       2
20 34396.364 -0.0021987173 -0.0086083808 -0.462098816       2
21 39297.273 -0.0080548883 -0.0088897627 -0.464983585       2
22 44198.182 -0.0086038271 -0.0114920192 -0.562709430       2
23 49099.091 -0.0094904993 -0.0169889702 -0.779278790       2
24 54000.000 -0.0119963261 -0.0174476608 -0.798253748       2

finally, can we bind res outputs with ordering set_nbr=1 group_min-> group_max , set_nbr=2 group_min-> group_max ..... and so on

         time   Mx            My            Mz           set_nbr   group_min
##1     90.000  0.0105615570  0.0128378518  0.92123599       1         1
##2   4990.909  0.0096134025  0.0117695944  0.78439667       1         1
##3   9891.818  0.0093581318  0.0115742493  0.72867894       1         1
##4  14792.727  0.0031807426  0.0113173105  0.55464140       1         1
##5  19693.636  0.0023569651  0.0089484378  0.42502936       1         1
##6  24594.545  0.0008941874  0.0058824078 -0.04729751       1         1
##7  29495.455 -0.0043247786  0.0021214525 -0.13068103       1         1
##8  34396.364 -0.0070967748  0.0011887832 -0.20001126       1         1
##9  39297.273 -0.0086446611 -0.0009107974 -0.22002091       1         1
##10 44198.182 -0.0087562698 -0.0035490228 -0.30663302       1         1
##11 49099.091 -0.0094095244 -0.0156822550 -0.33245014       1         1
##12 54000.000 -0.0123935570 -0.0190667519 -0.34929570       1         1

        time          Mx            My          Mz         set_nbr group_max
##13     90.000  0.0105615570  0.0128378518  0.92123599       1         3
##14   4990.909  0.0096134025  0.0117695944  0.78439667       1         3
##15   9891.818  0.0093581318  0.0115742493  0.72867894       1         3
##16  14792.727  0.0031807426  0.0113173105  0.55464140       1         3
##17  19693.636  0.0023569651  0.0089484378  0.42502936       1         3
##18  24594.545  0.0008941874  0.0058824078  0.04729751       1         3
##19  29495.455 -0.0043247786  0.0021214525  0.13068103       1         3
##20  34396.364 -0.0070967748  0.0011887832 -0.20001126       1         3
##21  39297.273 -0.0086446611 -0.0009107974 -0.22002091       1         3
##22  44198.182 -0.0087562698 -0.0035490228 -0.30663302       1         3
##23  49099.091 -0.0094095244 -0.0156822550 -0.33245014       1         3
##24  54000.000 -0.0123935570 -0.0190667519 -0.34929570       1         3


 > set_nbr 2   group_min
               group_max       
 > set_nbr 3   group_min
               group_max       
    ..

UPDATE

in addition to @akrun answer, it is useful to use

  Rows <- x[ceiling(x$Mz-y$Mz)==0,]

in case of you have different length of datasets.

1

There are 1 answers

16
akrun On BEST ANSWER

Try

lst <- lapply(data.list, function(x) {
      x$group <- cumsum(x$time==90)
      x})
lst1 <- split(as.data.frame(min_time), min_time$set_nbr)
res <- Map(function(x, y) {
          val <- mean(y$Mz)
          Rows <- x[ceiling(x$time-y$time)==0,]
          val1 <- Rows$Mz-val
          subset(x, group==Rows$group[which.min(val1)])},
             lst, lst1)