create variable with case_when/if_any across multiple variables

Question

I have a dataset with names from some variables corresponding to each month (e.g. var1_M1, var1_M2 var2_M1 ...) and I need to summarize by each month (after conditional) and identify the first case when I find a value > 90 and finally indicate the corresponding month.

library(dplyr)

ID <- c("Dave", "Joe", "Steve")
var1_M1 <- c(10, 10, 90)
var1_M2 <- c(30, 90, 95)
var1_M3 <- c(90, 100, 95)
var2_M1 <- c(10, 90, 20)
var2_M2 <- c(33, 10, 100)
var2_M3 <- c(90, 10, 50)

data <- tibble(ID, var1_M1, var1_M2, var1_M3, var2_M1, var2_M2, var2_M3)

# A tibble: 3 x 7
  ID    var1_M1 var1_M2 var1_M3 var2_M1 var2_M2 var2_M3
  <chr>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
1 Dave       10      30      90      10      33      90
2 Joe        10      90     100      90      10      10
3 Steve      90      95      95      20     100      50

data %>% 
  mutate_at(vars(matches(c("M1","M2","M3"))),
            list(~ ifelse(. > 90, .,0))) #%>% 
  #mutate() using map_dfc and if_any ??

Expected output

# A tibble: 3 x 5
  ID       M1    M2    M3 output
  <chr> <dbl> <dbl> <dbl>  <dbl>
1 Dave      0     0     0      0
2 Joe       0     0   100      3
3 Steve     0   195    95      2

akrun · Accepted Answer · 2023-01-30 05:19:07Z

2

We may reshape to 'long' format with pivot_longer, grouped by 'ID', get the sum across the columns, where the value is greater than 90, and create the output of the first column index value not 0 with max.col

library(tidyr)
library(dplyr)
pivot_longer(data, cols = -ID, names_to = c(".value"), 
   names_pattern = ".*_(.*)") %>%
   group_by(ID) %>% 
   summarise(across(everything(), ~ sum(.x[.x > 90]))) %>% 
   mutate(output = max.col(across(starts_with("M")) >0, "first") * 
     if_any(starts_with("M"), ~ .x > 0))

-output

# A tibble: 3 × 5
  ID       M1    M2    M3 output
  <chr> <dbl> <dbl> <dbl>  <int>
1 Dave      0     0     0      0
2 Joe       0     0   100      3
3 Steve     0   195    95      2

With pivot_longer, we specified a regex in names_pattern to capture the characters after the _ as a group ((...)) in column names i.e. M1, M2, M3) using .* - to match zero or more characters, and specify the names_to as .value so that it reshape to long format with the column names as M1, M2, M3 combining all the var_ columns.

After we summarise with the sum, use max.col to find the first index of column values greater than 0. In the first row, i.e. for Dave id, all the values are 0, thus max.col returns the index 1. In order to make it 0, multiply with TRUE/FALSE output from if_any (TRUE -> 1, FALSE -> 0). i.e. it may be better to create a column to understand it better

pivot_longer(data, cols = -ID, names_to = c(".value"), 
   names_pattern = ".*_(.*)") %>%
   group_by(ID) %>% 
   summarise(across(everything(), ~ sum(.x[.x > 90]))) %>%
   mutate(if_any_ind = if_any(starts_with("M"), ~ .x > 0),
           output_ind =max.col(across(starts_with("M")) >0, "first"),
           output = if_any_ind * output_ind)

-output

# A tibble: 3 × 7
  ID       M1    M2    M3 if_any_ind output_ind output
  <chr> <dbl> <dbl> <dbl> <lgl>           <int>  <int>
1 Dave      0     0     0 FALSE               1      0
2 Joe       0     0   100 TRUE                3      3
3 Steve     0   195    95 TRUE                2      2

ie. any value x 0 -> 0 and value x 1 -> 1

edited Jan 30, 2023 at 5:19

answered Jan 30, 2023 at 4:39

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user20168262 Over a year ago

ty so much akrun :D can u explain me pls how to use pivot_longer(names_pattern = ".*_(.*)") and max.col()*if_any()?

user20168262 Over a year ago

yeah, it works! :D So, I'd like to know how max.col()*if_any() works

Collectives™ on Stack Overflow

create variable with case_when/if_any across multiple variables

Expected output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Expected output

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related