I have a data that looks like this:

I would like to separate the column with the dates into separate columns according to ",". Something like this:

I tried

test <- data %>% separate(sep = ",") 

this doesnt seem to separate all the dates but only the first one. Could you please help me out

CodePudding user response：

You can use extract:

library(tidyr)
df %>%
  extract(col = J123,
          into = paste0("V", 1:3),
          regex = '"([0-9-] )","([0-9-] )","([0-9-] )"') %>%
  cbind(df$V1, .)
   df$V1         V1         V2         V3
1 112233 1990-03-29 2008-01-15 1986-07-28

Alternatively use separate:

library(tidyr)
df %>% 
  mutate(J123= str_remove(J123, "[c)(]")) %>% 
  separate(J123, 
           into = paste0("V", 1:3),
           sep = ",")

CodePudding user response：

Kindly let me know if this is what you were anticipating.

df %>% 
  unnest(J123) %>% 
  group_by(V1) %>% 
  mutate(key = row_number()) %>% 
  spread(key, J123)

CodePudding user response：

Using tidyr::separate just as you did:

Data:

df <- data.frame(V1 = 112233,
                 J123 = 'c("1990-03-29","2008-01-15","1986-07-28")')

      V1                                     J123
1 112233 c("1990-03-29","2008-01-15","1986-07-28")

Code:

df %>% 
  mutate(J123= str_remove(J123, "c")) %>% 
  mutate(J123= str_remove(J123, "\\(")) %>% 
  mutate(J123= str_remove(J123, "\\)")) %>% 
  mutate(J123= str_remove_all(J123, '\\"')) %>% 
tidyr::separate(J123, sep = ",", into = c("V2","V3","V4"))

Output:

      V1         V2         V3         V4
1 112233 1990-03-29 2008-01-15 1986-07-28