Use group_by and case_when to produce single value per group

user_3215940 0
Viewed 369

I have a data.frame with id and stage. stage is character but corresponds to an ordered process (A -> B -> C -> ...).

expand.grid(id=c(1:5), stage = LETTERS[1:4]) %>%
 arrange(id) %>%
 mutate(flag = case_when(str_detect(stage, "D") ~ "Dance",
                         str_detect(stage, "C") ~ "Climb",
                         str_detect(stage, "B") ~ "Biceps",
                         str_detect(stage, "A") ~ "Aerobic"))

This produces what I would expect, a vectorized case by case flag column. But what I want is the output of my case_when to be unique for the whole group of id. So I tried adding group_by(id)

expand.grid(id=c(1:5), stage = LETTERS[1:4]) %>%
 arrange(id) %>%
 group_by(id) %>%
 mutate(flag = case_when(str_detect(stage, "D") ~ "Dance",
                         str_detect(stage, "C") ~ "Climb",
                         str_detect(stage, "B") ~ "Biceps",
                         str_detect(stage, "A") ~ "Aerobic"))

But this doesn't change the nature of the result. Changing mutate into summarize does not produce the "summarizing" effect I was hoping for either. It's likely that I don't fully understand the functioning of case_when() and group_by() and thus I'm not able to write the proper command to get the summary I'm looking for.

My expected output should have id, stage being the last stage in the order and flag according to that stage. 

     id stage flag 
  <int> <fct> <chr>
1     1 D     Dance
2     2 D     Dance
3     3 D     Dance
4     4 D     Dance
5     5 D     Dance

To be more clear, let's assume id 1 and 2 are in stage A, 3 in B, 4 in C, and 5 in D. 

toy <- expand.grid(id=c(1:5), stage = LETTERS[1:4]) %>%
 arrange(id) %>%
 group_by(id) %>%
 mutate(flag = case_when(str_detect(stage, "D") ~ "Dance",
                         str_detect(stage, "C") ~ "Climb",
                         str_detect(stage, "B") ~ "Biceps",
                         str_detect(stage, "A") ~ "Aerobic"))
# grabbing only some of them
toy <- toy[c(1, 5, 10, 15, 20),]

Output should look like this: 

     id stage flag   
  <int> <fct> <chr>  
1     1 A     Aerobic
2     2 A     Aerobic
3     3 B     Biceps 
4     4 C     Climb  
5     5 D     Dance

I'm fine with repeated id, I can summarize from there.

dplyr r
1 Answers

You can use any (and the order of operations within case_when to get the flag that you want, within a summarize. We need to add a little in order to preserve stage.

expand.grid(id=c(1:5), stage = LETTERS[1:4]) %>%
  arrange(id) %>%
  group_by(id) %>%
  summarize(
   stage = intersect(c("D","C","B","A"), stage)[1],
   flag = case_when(any(str_detect(stage, "D")) ~ "Dance",
                    any(str_detect(stage, "C")) ~ "Climb",
                    any(str_detect(stage, "B")) ~ "Biceps",
                    any(str_detect(stage, "A")) ~ "Aerobic")
  )
# # A tibble: 5 x 3
#      id stage flag 
#   <int> <chr> <chr>
# 1     1 D     Dance
# 2     2 D     Dance
# 3     3 D     Dance
# 4     4 D     Dance
# 5     5 D     Dance

However, an alternative using left_join that avoids the piece-wise operation of case_when.

acronyms <- data.frame(
  prio  = 1:4,
  stage = c("D", "C", "B", "A"),
  flag  = c("Dance", "Climb", "Biceps", "Aerobic")
)

expand.grid(id=c(1:5), stage = LETTERS[1:4]) %>%
  left_join(acronyms) %>%
  arrange(id, prio) %>%
  group_by(id) %>%
  slice(1) %>%
  select(-prio) %>%
  ungroup()

The basic stage/flag association in acronyms is mostly sufficient; I added prio for a deterministic and simple way of always getting the most-important for each id. There may be other useful tricks here depending on your ultimate need.

user_33582720
Related