splitting a column delimiter R

Question

I have a dataframe as below. I want to split the last column into 2. Splitting needs to be done based upon the only first : and rest of the columns dont matter.

In the new dataframe, there will be 4 columns. 3 rd column will be (a,b,d) while 4th column will be (1,2:3,3:4:4)

any suggestions? 4th line of my code doesnt work :(. I am okay with completely new solution or corrections to the line 4

employee <- c('John Doe','Peter Gynn','Jolie Hope')
salary <- c(3, 2, 1)
df <- data.frame(employee, salary, originalColumn = c("a :1", "b :2:3", "d: 3:4:4"))

as.data.frame(do.call(rbind, strsplit(df,":")))

--------------------update1

Below solutions work well. But i need a modified solution as I just realized that some of the cells in column 3 wont have ":". In such case i want text in that cell to appear in only 1st column after splitting that column

employee <- c('John Doe','Peter Gynn','Jolie Hope')
salary <- c(3, 2, 1)
df <- data.frame(employee, salary, originalColumn = c("a :1", "b", "d: 3:4:4"))

possible duplicate of [Using strsplit with data frames to split label columns into multiple](http://stackoverflow.com/questions/4350440/using-strsplit-with-data-frames-to-split-label-columns-into-multiple) — Stedy, Oct 29 '14 at 18:25
I dont think that. I tried the command: str_split_fixed(df$originalcolumn, ":", 2) but it didnt work :( — user2543622, Oct 29 '14 at 18:29
It is pretty much a dupe, you were just missing `as.character(df$originalcolumn)`. But anyway `stringr::str_split` obviates that and also adds the handy `n=2` argument. See my solution. — smci, Oct 29 '14 at 18:42

Rich Scriven · Accepted Answer · 2014-10-29T19:42:37.420

5

You could use cSplit. On your updated data frame,

library(splitstackshape)
cSplit(df, "originalColumn", sep = ":{1}")
#      employee salary originalColumn_1 originalColumn_2
# 1:   John Doe      3                a                1
# 2: Peter Gynn      2                b               NA
# 3: Jolie Hope      1                d            3:4:4

And on your original data frame,

df1 <- data.frame(employee, salary,   
                  originalColumn = c("a :1", "b :2:3", "d: 3:4:4"))
cSplit(df1, "originalColumn", sep = ":{1}")
#      employee salary originalColumn_1 originalColumn_2
# 1:   John Doe      3                a                1
# 2: Peter Gynn      2                b              2:3
# 3: Jolie Hope      1                d            3:4:4

Note: I'm using splitstackshape version 1.4.2. I believe the sep argument has been changed from version 1.4.0

edited Oct 29 '14 at 19:42

answered Oct 29 '14 at 19:09

Rich Scriven

97,041
11
181
245

+1 nice way. But, for some reason this is not working for me. I use `splitstackshape_1.4.0` – akrun Oct 29 '14 at 19:21
I'm on 1.4.2. I think the `sep` arg was changed – Rich Scriven Oct 29 '14 at 19:22
That's neat, didn't know of that library. – smci Oct 29 '14 at 19:42
+1. Yes, there was a change in how the `sep` argument behaved between the two versions. – A5C1D2H2I1M1N2O1R2T1 Oct 30 '14 at 03:35

akrun · Answer 2 · 2014-10-30T17:08:58.127

You could use extract from tidyr to split the originalColumn in to two columns. In the below code, I am creating 3 columns and removing one of the unwanted columns from the result.

 library(tidyr)
 pat  <- "([^ :])( ?:|: ?|)(.*)"
 extract(df, originalColumn, c("Col1", "ColN", "Col2"), pat)[,-4]
 #     employee salary Col1  Col2
 #1   John Doe      3    a     1
 #2 Peter Gynn      2    b   2:3
 #3 Jolie Hope      1    d 3:4:4

Using the updated df, (for better identification - df1)

 extract(df1, originalColumn, c("Col1", "ColN", "Col2"), pat)[,-4]
 #    employee salary Col1  Col2
 #1   John Doe      3    a     1
 #2 Peter Gynn      2    b      
 #3 Jolie Hope      1    d 3:4:4

Or without creating a new column in df

 extract(df, originalColumn, c("Col1", "Col2"), "(.)[ :](.*)") %>%
                                  mutate(Col2= gsub("^\\:", "", Col2))

 #   employee salary Col1   Col2
 #1   John Doe      3    a      1
 #2 Peter Gynn      2    b    2:3
 #3 Jolie Hope      1    d  3:4:4

Based on the pattern in df, the below code also works. Here, the regex used to extract the first column is (.). A dot is a single element at the beginning of the string inside the parentheses will be extracted for the Col1. Then .{2} two elements following the first are discarded and the rest within the parentheses (.*) forms the Col2.

extract(df, originalColumn, c("Col1", "Col2"), "(.).{2}(.*)")
#    employee salary Col1  Col2
#1   John Doe      3    a     1
#2 Peter Gynn      2    b   2:3
#3 Jolie Hope      1    d 3:4:4

or using strsplit

as.data.frame(do.call(rbind, strsplit(as.character(df$originalColumn), " :|: ")))
#   V1    V2
#1  a     1
#2  b   2:3
#3  d 3:4:4

For df1, here is a solution using strsplit

  lst <- strsplit(as.character(df1$originalColumn), " :|: ")
  as.data.frame(do.call(rbind,lapply(lst,  
          `length<-`, max(sapply(lst, length)))) )
  #  V1    V2
 #1  a     1
 #2  b  <NA>
 #3  d 3:4:4

@user2543622 You just need `tidyr` if the second option works. — akrun, Oct 29 '14 at 18:36
please explain functioning of the 4th parameter. I looked into function definition/help in R, but couldnt 4th parameter. I am talking about command : extract(df, originalColumn, c("Col1", "Col2"), "(.).{2}(.*)") — user2543622, Oct 29 '14 at 18:44

smci · Answer 3 · 2014-10-29T19:29:56.700

1

You were close, here's a solution:

library(stringr)

df[, c('Col1','Col2')] <- do.call(rbind, str_split_fixed(df$originalColumn,":",n=2))
df$originalColumn <- NULL

    employee salary Col1 Col2
1   John Doe      3  a       1
2 Peter Gynn      2  b     2:3
3 Jolie Hope      1   d  3:4:4

Notes:

stringr::str_split() is better than base::strsplit() because you don't have to do as.character(), also it has the n=2 argument you want to limit to only split on the first ':'

edited Oct 29 '14 at 19:29

answered Oct 29 '14 at 18:37

smci

32,567
20
113
146

Then use `str_split_fixed()`. Works fine. – smci Oct 29 '14 at 19:29

splitting a column delimiter R

3 Answers3

Linked