duplicate row when case_when

user_12403550 0
Viewed 190

Here is my dummy data:

df <- tibble(col1 = c(runif(5, min = 0, max = 5)),
       col2 = c("a", "b", "c", "d", "e"),
       col3 = c("blue", "blue", "blue", "red", "orange"))
  
   col1 col2  col3  
  <dbl> <chr> <chr> 
1 0.200 a     blue  
2 1.99  b     blue  
3 0.663 c     blue  
4 1.79  d     red   
5 3.82  e     orange

My aim is to add unique identifiers using conditional formatting however, I often have multiple identifiers for the same condition, so they will just overwrite on another.

df %>% mutate(col4 = case_when(
    col2 %in% "b" & col3 %in% "blue" ~ "id1",
    col2 %in% "b" & col3 %in% "blue" ~ "id2",
    TRUE ~ NA_character_
  ))

   col1 col2  col3   col4 
  <dbl> <chr> <chr>  <chr>
1 0.345 a     blue   NA   
2 0.250 b     blue   id1  
3 1.37  c     blue   NA   
4 3.99  d     red    NA   
5 2.14  e     orange NA

My desired output simply duplicates the row!

   col1 col2  col3   col4 
  <dbl> <chr> <chr>  <chr>
1 0.345 a     blue   NA   
2 0.250 b     blue   id1  
3 0.250 b     blue   id2  
3 1.37  c     blue   NA   
4 3.99  d     red    NA   
5 2.14  e     orange NA
dplyr r
2 Answers

We could replicate the rows first and then do the case_when

library(dplyr)
library(tidyr)
library(data.table)
library(stringr)
df %>% 
  mutate(i1 = col2 == 'b' & col3 == 'blue') %>%
  uncount(case_when(i1~ 2, TRUE ~ 1)) %>% 
  mutate(col4 = case_when(i1 ~ str_c('id', rowid(col2, col3))), i1 = NULL)

-output

# A tibble: 6 x 4
#   col1 col2  col3   col4 
#  <dbl> <chr> <chr>  <chr>
#1 2.43  a     blue   <NA> 
#2 1.23  b     blue   id1  
#3 1.23  b     blue   id2  
#4 1.40  c     blue   <NA> 
#5 0.650 d     red    <NA> 
#6 3.65  e     orange <NA>

Or can also wrap it in a list and then unnest

df %>%
    mutate(col4 = case_when(col2 == 'b' & col3 == 'blue' 
       ~ list(str_c('id', 1:2)), TRUE ~ list(NA_character_))) %>% 
    unnest(col4)
user_37322710

Here is a data.table option

setDT(df)[
  df[, id := .I][
    ,
    .(col4 = if (col2 == "b" & col3 == "blue") paste0("id", 1:2) else NA_character_), id
  ],
  on = "id"
][
  ,
  id := NULL
][]

which gives

       col1 col2   col3 col4
1: 2.580672    a   blue <NA>
2: 1.593007    b   blue  id1
3: 1.593007    b   blue  id2
4: 4.990018    c   blue <NA>
5: 2.105550    d    red <NA>
6: 4.271286    e orange <NA>
user_121587570
Related