Splitting rows into columns in R using tidyr

Question

I have a dataset which looks like this-

                                                                              col1

1 ATOM      1  N   ILE A  12      67.611  47.640  52.312  1.00 12.44           N  
2 ATOM      2  CA  ILE A  12      66.381  47.660  51.520  1.00 25.25           C

It has a single column called col1. I want to separate into 12 columns for which I'm using the following command-

try=separate(subset,col1,c("name","S.No","Atom Name","Residue Name","Symbol","Residue Number","X-cor","Y-cor","Z-cor","Uk1","Uk2","Symbol"), sep= " ")

But I keep on getting the following error, which I do not understand-

Warning message: Too many values at 3929 locations: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, ...

And it gives me the following output-

name S.No Atom Name Residue Name Symbol Residue Number X-cor Y-cor Z-cor Uk1 Uk2 Symbol

1 ATOM                                                       1           N            ILE

2 ATOM                                                       2          CA     ILE      A

Any help fixing this is highly appreciated. Thanks!

The obvious comment is if you imported your data into this one column format, redo it using read.table or whatever faster equivalent (with header=FALSE) and avoid the need to use separate at all. — thelatemail
– thelatemail, Commented Feb 7, 2016 at 23:43

dimitris_ps · Accepted Answer · 2016-02-07 23:59:43Z

4

There should be a more elegant solution with tidyr. But without that library this is what I have

data.frame(do.call(rbind, unlist(apply(subset, 1, function(x) strsplit(x, "\\s+")),recursive=FALSE)))

Logic

I am assuming your data set name is subset. For each row of the data.frame you split it up by the space(s), which is this part strsplit(x, "\\s+")). The rest is basically to have it in a data.frame.

Update

Just figured it out, in your code just replace sep= " " with sep= "\\s+". the \\s+ states at least on space, whereas your " " is exactly one space.

edited Feb 7, 2016 at 23:59

answered Feb 7, 2016 at 23:53

dimitris_ps

5,9513 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mridul Garg Over a year ago

Thank you @dimitris_ps - I had understood the error later on but could not figure out how to fix it.

Jeevan gona · Accepted Answer · 2017-08-19 16:19:53Z

0

I have faced the same problem

solution:- Dont use "sep" if you want to divide two characters(or anything) connected by "."

reference: check examples provided in documentation of separate()

> df <- data.frame(x = c(NA, "a.b", "a.d", "b.c"))
> df %>% separate(x, c("A", "B"))
  A    B
  1 <NA> <NA>
  2    a    b
  3    a    d
  4    b    c

#Reason for warning:

> x="Sepal.Width"
> strsplit(x,split=".")
[[1]]
[1] "" "" "" "" "" "" "" "" "" "" ""

> str_detect(x,".")
[1] TRUE
> str_replace(x,".","_")
[1] "_epal.Width"
> str_replace_all(x,".","_")
[1] "___________"

edited Aug 19, 2017 at 16:19

answered Aug 19, 2017 at 15:43

Jeevan gona

11 bronze badge

Collectives™ on Stack Overflow

Splitting rows into columns in R using tidyr

2 Answers 2

Logic

Update

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Logic

Update

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related