separate columns on condition in dataframe in R

user_12070334 0
Viewed 72

I think my image will probably convey 90% of the problem. My try is what I have posted below: (Separate only last occurrence of / when ColB contains "Sep")

df <- structure(list(ColA = c("abc/def", "bcd/efg", "def/ghj/yes", 
"fet/hjk/yes"), ColB = c("sep", "no", "sep", "no")), class = "data.frame", row.names = c(NA, 
-4L))

library(tidyverse)
df %>%
mutate(across(str_detect(ColB,"sep")~separate(ColA,into=c("A1","A2"),sep="/",
TRUE ~ NA))

Problem & desire output

dplyr tidyverse r
2 Answers

Using extract we can divide the data in different columns and turn the values to NA if colB != 'sep.

library(dplyr)  
library(tidyr)

df %>%
  extract(ColA, c('A1', 'A2'), '(.*)/(.*)', remove = FALSE) %>%
  mutate(across(A1:A2, ~replace(., ColB != 'sep', NA)))

#         ColA      A1   A2 ColB
#1     abc/def     abc  def  sep
#2     bcd/efg    <NA> <NA>   no
#3 def/ghj/yes def/ghj  yes  sep
#4 fet/hjk/yes    <NA> <NA>   no
user_39629140

We can do this in base R

df[paste0('A', 1:2)] <- read.csv(text = sub("/(\\w+)$",
          ",\\1", df$ColA), header = FALSE)
df[paste0("A", 1:2)] <- replace(df[paste0("A", 1:2)], df$Col1B != 'sep', NA)

Or we can use the OP's method with separate

library(dplyr)
library(tidyr)
df %>% 
     separate(ColA, into = c("A1", "A2"), remove = FALSE, sep="/(?=[^/]+$)") %>%
     mutate(across(A1:A2, ~ case_when(ColB == 'sep' ~ .)))
#         ColA      A1   A2 ColB
#1     abc/def     abc  def  sep
#2     bcd/efg    <NA> <NA>   no
#3 def/ghj/yes def/ghj  yes  sep
#4 fet/hjk/yes    <NA> <NA>   no

Or with str_split and unnest_wider

df %>% 
 mutate(new = str_split(case_when(ColB == 'sep' ~ ColA), "/(?=[^/]+$)")) %>% 
 unnest_wider(c(new))
user_37322710
Related