mutate identifier (week appearance)

library(dplyr)
# toy data
df <- tibble(
  id = c(11, 22, 33, 11, 44, 11, 22),
  job = c("A", "B", "C", "A", "D", "A", "B"),
  week = c("W1", "W1", "W1", "W2", "W2", "W3", "W3")
)
df
#> # A tibble: 7 x 3
#>      id job   week 
#>   <dbl> <chr> <chr>
#> 1    11 A     W1   
#> 2    22 B     W1   
#> 3    33 C     W1   
#> 4    11 A     W2   
#> 5    44 D     W2   
#> 6    11 A     W3   
#> 7    22 B     W3

How do I generate a variable, say wanted, which will identify the weeks for each id? For example, id 22 appears in weeks 1 and 3 . So, wanted variable will contain the string W1; W3.

> df_desired 
# A tibble: 4 x 3
     id job   wanted    
  <dbl> <chr> <chr>     
1    11 A     W1; W2; W3
2    22 B     W1; W3    
3    33 C     W1        
4    44 D     W4 

Created on 2021-11-25 by the reprex package (v2.0.1)

Maybe you could do this in a better way, but:

library(tidyverse)
df %>% 
  group_by(id, job) %>% 
  mutate(week = toString(week),
         week = str_replace_all(week, ",", ";")) %>% 
  ungroup() %>% 
  distinct(id, job, week)

# A tibble: 4 × 3
     id job   week      
  <dbl> <chr> <chr>     
1    11 A     W1; W2; W3
2    22 B     W1; W3    
3    33 C     W1        
4    44 D     W2  
1 Like

@williaml sorry for the late reply. Your code is what I was looking for! Many thanks!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.