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...
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.