Search code examples
rdplyrcase-whenacross

General way of uniting column with subsequent column inside case_when inside mutate(across()) in R


I have a df in R, this is a simplified version:

ID <- c("AA1", "AA2","AA3","AA4","AA5")
Pop <- c("AA","AA","AA","AA","AA")
abc08 <- c(2,1,2,0,2)
...4 <- c(3,4,4,0,3)
abc11 <- c(2,2,2,2,1)
...5 <- c(3,4,4,4,3)
df <- data.frame(ID, Pop, abc08, ...4, abc11, ...5)
df

I want to unite columns that start with "abc" with their subsequent column, making the df look like this:

ID <- c("AA1", "AA2","AA3","AA4","AA5")
Pop <- c("AA","AA","AA","AA","AA")
abc08 <- c("2-3","1-4","2-4",NA,"2-3")
abc11 <- c("2-3","2-4","2-4","2-4","1-3")
df <- data.frame(ID, Pop, abc08, abc11)
df

My original df have more columns, so I'm, searching for a general way of doing it. The code I have so far looks like this:

  df %>% mutate(across(starts_with("abc"),
                ~ case_when(. > 0 ~ paste(., "content of subsequent column", sep = "-"),
                            . == 0 ~ NA_character_
                )))%>%
  select(!starts_with("..."))

where "content of subsequent column" obviously needs to be something that identifies the column following ´.´. I can't be the first who has had this problem, but I've searched for hours now without getting anywhere...


Solution

  • Try the following:

    library(tidyverse)
    
    df %>%
      mutate(across(starts_with("abc"), 
                    ~ paste0(., "-", get(colnames(df)[which(colnames(df) == cur_column()) + 1]) ))) %>%
      select(!starts_with("..."))
    

    Output:

       ID Pop abc08 abc11
    1 AA1  AA   2-3   2-3
    2 AA2  AA   1-4   2-4
    3 AA3  AA   2-4   2-4
    4 AA4  AA   0-0   2-4
    5 AA5  AA   2-3   1-3
    

    This works without case_when because paste0 is vectorised. The long phrase starting with get dynamically retrieves the value of whatever column is after the target "abc" column.