Split a column into 2 columns with "." separator R

user_12800405 0
Viewed 3292

I have the following dataset:

the_data <- data.frame(the_col = "a.1","b.2","c.3","d.4")

I try to split it into 2 columns. It seems that is a replicated question but what makes it different is the separator I want (the dot). I tried:

the_data %>% separate(the_col, into = c("alfa","beta"), sep = ".")

But I receive a warning and not what I want:

 alfa beta X.b.2. X.c.3. X.d.4.
1              b.2    c.3    d.4

what I want is:

alfa   beta
a      1
b      2 
c      3
d      4

Could you please help me? Thank you.

tidyr r
2 Answers

We can get the data in long format and then use separate

library(dplyr)
library(tidyr)

pivot_longer(the_data, cols = everything()) %>%
  separate(value, into = c('alpha', 'beta'), sep = "\\.") %>%
  select(-name)

# A tibble: 4 x 2
#  alpha beta 
#  <chr> <chr>
#1 a     1    
#2 b     2    
#3 c     3    
#4 d     4

Using base R, we can split the unlisted string on ".", convert it into two column dataframe and add names to it. 

setNames(do.call(rbind.data.frame, strsplit(unlist(the_data), '\\.')), 
         c('alpha', 'beta'))
user_39629140 
the_data <- data.frame(the_col = c("a.1","b.2","c.3","d.4")) %>% 
  separate("the_col", c("alpha", "beta"), sep = "\\.")
# R> the_data 
#   alpha beta
# 1     a    1
# 2     b    2
# 3     c    3
# 4     d    4

I think your problem was that

  1. separate takes a regex as its 2nd argument, and . matches any character in regex, unless you escape it with \\
  2. The values which you intended to go into column the_col should have been in a vector.
user_60040160
Related