How to loop through multiple variables and filter data to produce multiple dataframes?

Question

I have a dataframe df with about 1000 rows with the following columns:

df$ID<- c("ab11", "ab12" ...) #about 1000 rows
df$ID1<-numbers ranging from 1 to 20k # for all intense and purposes this can be treated as class 'factor'
df$Acol<- #numbers ranging from 1 to 1000
df$Bcol<- #numbers ranging from 0 to 1

The following lists gives me 12 values in each list:

A<- seq(50,600,by=50)
B<- seq(0.2,1,by=0.75)

I am trying to do 2 things:

1. I would like to create dataframes by filtering the original dataset with various combinations of lists A and B. So 144 dataframes.
2. Once I have those dataframes I would like to combine 3 dataframes at a time and see if the frequency of the IDs match a master dataframe x and if they do, get the combinations information for the matching dataframe.

So for 1, this is my approach:

df_50_0.2<-subset(df, df$Acol>=50 & df$Bcol>=0.2)

I can't write that out 144 times- I need a loop. I tried nested loop but that doesn't give me every combination of A and B so I tried a while loop.

Here is my code:

i<-50
while (i<550) {
   for (j in B) {
      assign(paste("df","_",as.character(i),"_",as.character(j)), df %>% 
      filter (Acol>=i) %>% 
      filter(Bcol>=j),envir=.GlobalEnv
   i<-i+50
  }}   

That give me the desired result except it doesn't split the dataframe according to B. So the output is similar to what I would have if I had just filtered the data with values of A.

For the second part I need to loop through all possible combinations of three data frames at a time. Here is my code:

df.final<-rbind (df_50_0.2,df_100_0.25,df_150_0.5)
tmp<-subset(table(df.final$ID),!(table(df.final$ID) %in% table(x$ID))

I would like the above to be in a loop. If tmp has any values, I don't want it to be an output. If it is 0, that is, it is a perfect match to the frequency of IDs in the master dataframe x, I want that to be written. So something like the following in a loop? I want all possible combinations checked iteratively to come up with the combinations that match the master dataframe x ID frequency perfectly:

if tmp = NULL
tmp
else rm(tmp)

Any help is much appreciated. A python solution is also welcome!

A solution available in the following link but modified for two columns could be helpful Filter loop to create multiple data frames

Answer 1

This is not a complete answer, but it represents a different way to think about your problem. First we need a reproducible sample. I'm leaving out your first two columns, but it would not be difficult to modify the example to include them.

set.seed(42)
df <- data.frame(A=runif(200) * 1000 + 1, B=runif(200))
Aint <- seq(50, 600, by=50)
Bint <- seq(0.2, 1, by=0.05)
comb <- expand.grid(Aint=Aint, Bint=Bint)

This gives you a data frame of 200 observations and columns A and B. Then we create the intervals (called Aint and Bint so we don't confuse them with df$A and df$B. The next line creates all possible combinations of those intervals - 204 (not 144 since Bint has 17 values).

Now we need to define your groups and generate the label for each one:

groups <- apply(comb, 1, function(x)   df$A > x[1] & df$B > x[2])
labels <- paste("df", comb[, 1], comb[, 2], sep="_")

groups is a logical matrix 200 x 204 so each column defines one of your comparisons. The first group is defined as df[groups[, 1], ] and the second as df[groups[, 2], ]. The labels for those comparisons are labels[1] and labels[2].

Since your groups are overlapping, the next step will create multiple copies of your data in each group of three. The number of combinations of 204 groups taken 3 at a time is 1,394,204. The following generates the code to create the different combinations of 3 data frames:

allcombos <- combn(204, 3, simplify=TRUE)
for (i in 1:5) {
    dfno <- allcombos[, i]
    df.final <- rbind(df[groups[, dfno[1]], ], df[groups[, dfno[2]], ], df[groups[, dfno[3]], ])
    lbl <- labels[dfno]
    
}

The next step involves subset(table(df.final$ID),!(table(df.final$ID) %in% table(x$ID)) but that line does not do what you suggest. It creates a frequency table of the number of times each ID appears in df.final and a table for a master data frame x. The %in% statement processes each frequency in df.final and checks to see if it matches the frequency of ANY ID in master. So I have not included that logical statement or code that would write the current df.final to a list.