How do I split this data file into three columns?

Question

I have a data.frame that looks like:

chr1:29250635-29582124
chr1:46026531-46214183
chr1:46554517-46718374
chr1:51008171-51235816
chr1:63862069-64092146
chr1:78052717-78289590
chr1:85066633-85177704
chr1:94639336-94839130
chr1:97229888-97509581
chr1:98053562-98445381
chr1:114057822-114381300
chr1:115116433-115378793

I would like to split the frame into three columns like:

CHR     BP1        BP2
chr1    29250635   29582124
chr1    46026531   46214183
chr1    46554517   46718374
chr1    51008171   51235816
chr1    63862069   64092146

how can I do this?

or even this [Splitting a dataframe string column into multiple different columns](http://stackoverflow.com/questions/18641951/splitting-a-dataframe-string-column-into-multiple-different-columns) — Ronak Shah, Sep 09 '16 at 05:23

akrun · Answer 1 · 2016-09-09T05:17:20.883

We can do this with separate

library(tidyr)
separate(df1, V1, into = c("CHR", "BP1", "BP2"))
#    CHR       BP1       BP2
#1  chr1  29250635  29582124
#2  chr1  46026531  46214183
#3  chr1  46554517  46718374
#4  chr1  51008171  51235816
#5  chr1  63862069  64092146
#6  chr1  78052717  78289590
#7  chr1  85066633  85177704
#8  chr1  94639336  94839130
#9  chr1  97229888  97509581
#10 chr1  98053562  98445381
#11 chr1 114057822 114381300
#12 chr1 115116433 115378793

Or using cSplit

library(splitstackshape)
setnames(cSplit(df1, "V1", "-|:", fixed = FALSE), c("CHR", "BP1", "BP2"))[]

Or a base R approach with read.table after changing the delimiter to a single one with sub.

read.table(text=sub(":", "-", df1$V1), sep="-", header=FALSE,
           col.names = c("CHR", "BP1", "BP2"), stringsAsFactors=FALSE)
#   CHR       BP1       BP2
#1  chr1  29250635  29582124
#2  chr1  46026531  46214183
#3  chr1  46554517  46718374
#4  chr1  51008171  51235816
#5  chr1  63862069  64092146
#6  chr1  78052717  78289590
#7  chr1  85066633  85177704
#8  chr1  94639336  94839130
#9  chr1  97229888  97509581
#10 chr1  98053562  98445381
#11 chr1 114057822 114381300
#12 chr1 115116433 115378793

data

df1 <- structure(list(V1 = c("chr1:29250635-29582124", "chr1:46026531-46214183", 
"chr1:46554517-46718374", "chr1:51008171-51235816", "chr1:63862069-64092146", 
"chr1:78052717-78289590", "chr1:85066633-85177704", "chr1:94639336-94839130", 
"chr1:97229888-97509581", "chr1:98053562-98445381", "chr1:114057822-114381300", 
"chr1:115116433-115378793")), .Names = "V1", class = "data.frame", row.names = c(NA, 
-12L))

How do I split this data file into three columns?

1 Answers1

data