CODEWITHSUNDEEP
rsplit
kelvinfrog
kelvinfrog

Reputation: 467

R: splitting string that has format like this "xxx; yyy; zzz;"

The raw data I got is like this and they are all in one column

John;Peter;Eric;
Susan;Mary;Kate;

But I want to split them to three separate columns

John  Peter  Eric
Susan Mary   Kate

Can anyone show me how to do it in R? Thanks in advance!

Upvotes: 2

Views: 1387

Answers (3)

Metrics
Metrics

Reputation: 15458

base R: 

  matrix(regmatches(x,gregexpr("([aA-zZ]+)",x,perl=TRUE))[[1]],ncol=3,byrow=T)
     [,1]    [,2]    [,3]  
[1,] "John"  "Peter" "Eric"
[2,] "Susan" "Mary"  "Kate"

Upvotes: 1

Rich Scriven
Rich Scriven

Reputation: 99351

Adding fread() to the lot

x <- "John;Peter;Eric;
Susan;Mary;Kate;"

data.table::fread(x, header = FALSE, drop = 4)
#       V1    V2   V3
# 1:  John Peter Eric
# 2: Susan  Mary Kate

And for directly returning a data frame, 

data.table::fread(x, header = FALSE, drop = 4, data.table = FALSE)
#      V1    V2   V3
# 1  John Peter Eric
# 2 Susan  Mary Kate

And for a fast matrix that you can convert to data frame,

library(stringi)
stri_split_fixed(stri_split_lines1(x), ";", omit = TRUE, simplify = TRUE)
#      [,1]    [,2]    [,3]  
# [1,] "John"  "Peter" "Eric"
# [2,] "Susan" "Mary"  "Kate"

Upvotes: 3

akrun
akrun

Reputation: 887501

You could try cSplit

library(splitstackshape)
cSplit(df1, 'col1', ';')
#    col1_1 col1_2 col1_3
#1:   John  Peter   Eric
#2:  Susan   Mary   Kate

Or 

library(tidyr)
separate(df1, col1, into=paste0('col', 1:4), ';')[-4]
#    col1  col2 col3
#1  John Peter Eric
#2 Susan  Mary Kate

Or

 extract(df1, col1, into=paste0('col', 1:3), '([^;]+);([^;]+);([^;]+)')
 #   col1  col2 col3
 #1  John Peter Eric
 #2 Susan  Mary Kate

Or using base R

 as.data.frame(do.call(rbind,strsplit(df1$col1, ';')))

data

df1 <- structure(list(col1 = c("John;Peter;Eric;", "Susan;Mary;Kate;"
 )), .Names = "col1", class = "data.frame", row.names = c(NA, -2L))

Upvotes: 4

Related Questions