Find groups that match the conditions or contain the conditions

Question

Here is my sample data frame. The actual data frame has a lot more groups and 9 conditions in each group.

df <- data.frame(
          Group = c('A','A','B','B','B','C','C','C','D','D','D','D'),
          Condition = c('cond2', 'cond3','cond1','cond2','cond3','cond1','cond2','cond3', 'cond1','cond2','cond3','cond4'),
          Value = c(0,0,0,1,0,0,0,1,0,1,0,0)
        )

> df
   Group Condition Value
1      A     cond2     0
2      A     cond3     0
3      B     cond1     0
4      B     cond2     1
5      B     cond3     0
6      C     cond1     0
7      C     cond2     0
8      C     cond3     1
9      D     cond1     0
10     D     cond2     1
11     D     cond3     0
12     D     cond4     0

Question I: groups match the conditions

Get the groups that exactly have cond1 == 0, cond2 == 1, and cond3 == 0 (in this case, group B meets the criteria).

The desired output:

  Group Condition Value
1     B     cond1     0
2     B     cond2     1
3     B     cond3     0

Question II: groups contain the condtions

Get the groups that contain cond1 == 0 and cond2 == 1, other conds could be 1 or 0 (in this case, group B and group D should be selected. Please note that group C doesn't meet the criterion because it has cond2 == 0).

   Group Condition Value
1      B     cond1     0
2      B     cond2     1
3      B     cond3     0
4      D     cond1     0
5      D     cond2     1
6      D     cond3     0
7      D     cond4     0

Does this answer your question? [Select rows based on condition from each column](https://stackoverflow.com/questions/48423417/select-rows-based-on-condition-from-each-column) — John Polo, Nov 16 '22 at 02:46
@onyambu the linked question and this question both ask for conditions from each column. I realize you came up with a different way to do it than what's in the linked answer, but they're asking for the same thing. — John Polo, Nov 16 '22 at 03:27
@JohnPolo Notice that the linked question has the issue of different columns. Here we are interested in the rows. ie a group contains a row with cond 1, 0 then cond2 1, then cond 3 0. So we are dealing with rows and not columns — Onyambu, Nov 16 '22 at 03:31
Re Q2, you should be able to do `df %>% group_by(Group) %>% filter(any(Condition == "cond1" & Value == 0) && any(Condition == "cond2" & Value == 1)) %>% ungroup()`. — Ritchie Sacramento, Nov 17 '22 at 07:38

score 1 · Answer 1 · answered Nov 16 '22 at 03:16

1

In Base R:

v <- c('cond1==0', 'cond2==1','cond3==0')

subset(df, ave(paste(Condition, Value, sep = '==')%in% v, Group, FUN = all))

  Group Condition Value
3     B     cond1     0
4     B     cond2     1
5     B     cond3     0

answered Nov 16 '22 at 03:16

Onyambu

67,392
3
24
53

Shawn Hemelstrand · Answer 2 · 2022-11-17T07:28:02.767

0

Question 1

Getting the answer for the first question is simple, given that filtering for Group B gives you the exact output you want already.

#### Load Library ####
library(dplyr)

#### Filter ####
df %>% 
  filter(Group == "B")

As seen here:

  Group Condition Value
1     B     cond1     0
2     B     cond2     1
3     B     cond3     0

Question 2

For the second question, your logic doesn't entirely make sense because Groups B, C, and D meet the conditions you are trying to find. A doesn't ever meet the assumption of either filter. As an example, if you unite the conditions and values, then filter for the conditions you mentioned:

#### Unite and Filter ####
df %>% 
  unite("Merged_Group",
        Condition,
        Value) %>% 
  filter(Merged_Group %in% c("cond1_0",
                             "cond2_1"))

You will get this output:

 Group Merged_Group
1     B      cond1_0
2     B      cond2_1
3     C      cond1_0
4     D      cond1_0
5     D      cond2_1

Edit 1

Going off what you said in the comments, this is a way to be more explicit about Question 1.

df %>% 
  unite("Merged_Group",
      Condition,
      Value) %>% 
  filter(Merged_Group %in% c("cond1_0",
                             "cond2_1",
                             "cond3_0"),
         Group == "B")

Giving you this:

 Group Merged_Group
1     B      cond1_0
2     B      cond2_1
3     B      cond3_0

Edit 2

Based off your last edit, if you only want the condition 1 = 0 and condition 2 = 1 criterion, you could do so here:

df %>% 
  unite("Merged_Group",
        Condition,
        Value) %>% 
  filter(Merged_Group %in% c("cond1_0",
                             "cond2_1"))

But this would technically give you Group C as well, as it meets this condition.

  Group Merged_Group
1     B      cond1_0
2     B      cond2_1
3     C      cond1_0
4     D      cond1_0
5     D      cond2_1

edited Nov 17 '22 at 07:28

answered Nov 16 '22 at 08:34

Shawn Hemelstrand

2,676
4
17
30

Thank you! For question 1, how could I use the conditions (cond1 == 0, cond2 == 1, and cond3 == 0) to get group `B`? – Kevin Nov 16 '22 at 08:58
1

I'm not sure if I'm correct about your assumption, but I have edited my answer to try to answer that better. – Shawn Hemelstrand Nov 16 '22 at 09:02
I think I mis-explained my question. @onyambu solved my question I. But I still don't know how to solve question II. – Kevin Nov 17 '22 at 07:02
In what way does my answer not solve your problem exactly? The way you have edited your question makes less sense, because not all the rows of B fit your criterion. – Shawn Hemelstrand Nov 17 '22 at 07:07
Yes, my mistake. I corrected my questions. Thank you so much. – Kevin Nov 17 '22 at 07:08
I've looked at your edit. Your criterion still doesn't make sense because technically Group C also meets your conditions, not just B and D. – Shawn Hemelstrand Nov 17 '22 at 07:26
I've edited my answer to show you what I mean. – Shawn Hemelstrand Nov 17 '22 at 07:28
Thanks. Group `C` is not what I want because it has cond2 == 0. I think this is the trouble I have. – Kevin Nov 17 '22 at 07:31
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249688/discussion-between-kevin-and-shawn-hemelstrand). – Kevin Nov 17 '22 at 07:34