R: How to split string on certain column and move each substring to next line?

Question

I have the following data:

>Data
#  X        Y
#  1   123 234 345 456 
#  2   222 333 444 555 666

and I want the following result:

>Data
#  X     Y
#  1    123 
#  1    234 
#  1    345 
#  1    456 
#  2    222 
#  2    333 
#  2    444
#  2    555 
#  2    666

Please help!

could you post `dput` of your `Data`? It's hard to see if column `Y` is a string/character vector or a list. — Arun, Jun 10 '13 at 12:10

score 2 · Answer 1 · edited May 23 '17 at 12:33

2

This really doesn't build very much on your earlier question. Please try to do some homework on your own, or at least show what you've tried so far!

Data1 <- cbind(X = Data$X, 
               read.table(text = as.character(Data$Y), 
                          fill = TRUE, header = FALSE))
data.frame(X = Data1[, 1], stack(Data1[-1]))
#    X values ind
# 1  1    123  V1
# 2  2    222  V1
# 3  1    234  V2
# 4  2    333  V2
# 5  1    345  V3
# 6  2    444  V3
# 7  1    456  V4
# 8  2    555  V4
# 9  1     NA  V5
# 10 2    666  V5

edited May 23 '17 at 12:33

Community

1
1

answered Jun 10 '13 at 12:16

A5C1D2H2I1M1N2O1R2T1

190,393
28
405
485

@Ananada Mahto, I try your method in my actual data, but show error Error in data.frame(..., check.names = FALSE) : arguments imply differing number of rows: 1496, 1502 Can you see what happens. I know I have a lot of similar questions because I newer to R. Thank you! – Dryad Jun 10 '13 at 12:43

Arun · Accepted Answer · 2013-06-10T14:54:58.050

2

If your column Y is a character vector, then this should do:

out <- stack(setNames(strsplit(df$Y, " "), df$X))
out$values <- as.numeric(out$values)

Benchmarking all answers on a 20e4 row data.frame:

Creating data:

df <- read.table(header=TRUE, text="X        Y
    1   '123 234 345 456'
    2   '222 333 444 555 666'")
# thanks to @MatthewPlourde for the suggestion to use replicate
df <- do.call(rbind, replicate(10000, df, simplify = FALSE))

dim(df)
# [1] 20000     2

sapply(df, class)
        X         Y 
"integer"  "factor"

Functions:

# Arun's function
Arun <- function(df) {
    out <- stack(setNames(strsplit(as.character(df$Y), " "), df$X))
    out$values <- as.numeric(out$values)
    out
}

# Ananda's function
Ananda <- function(Data) {
    Data1 <- cbind(X = Data$X, 
           read.table(text = as.character(Data$Y), 
                      fill = TRUE, header = FALSE))
    data.frame(X = Data1[, 1], stack(Data1[-1]))
}

# Matthew's solution
Matthew <- function(d) {
    stack(by(d$Y, d$X, function(x) 
            as.numeric(scan(text=as.character(x), 
            what='', quiet=TRUE))))
}

Benchmarking:

require(microbenchmark)
microbenchmark(a1 <- Arun(df), a2 <- Ananda(df), a3 <- Matthew(df), times = 5)
Unit: milliseconds
              expr       min        lq    median        uq       max neval
    a1 <- Arun(df)  235.6945  258.8485  264.4166  329.2974  392.9559     5
  a2 <- Ananda(df) 6661.8461 6972.2823 7825.3701 8210.9970 9454.5762     5
 a3 <- Matthew(df) 3589.1784 3691.3826 3787.4163 4020.4895 5034.6580     5

edited Jun 10 '13 at 14:54

answered Jun 10 '13 at 12:17

Arun

116,683
26
284
387

Y is not character, but factor – Dryad Jun 10 '13 at 12:51
replace `df$Y` with `as.character(df$Y)` – Arun Jun 10 '13 at 12:53
+1 Thanks for the benchmark. Interesting to see that `strsplit` is faster. However, OP indicated the column was `factor`, not `character`, and if you modify your function to take this into account, the difference is less dramatic. – Matthew Plourde Jun 10 '13 at 14:13
1

Not to nitpick, but `df <- do.call(rbind, replicate(10000, df))` might be a nicer way to generate the big data.frame. – Matthew Plourde Jun 10 '13 at 14:26
@MatthewPlourde, I've edited the benchmarking results with Y as `factor`. Doesn't seem to make a difference... Have I got something wrong? And yes indeed it's a neater way of generating df. I'll edit. – Arun Jun 10 '13 at 14:27
@Arun I see that. Funny, the run times came out much closer on my end. – Matthew Plourde Jun 10 '13 at 14:31
@MatthewPlourde, I see. I had to change the `do.call(rbind, ...)` back to the old one as it doesn't bind properly. – Arun Jun 10 '13 at 14:36
1

@Arun oops, `replicate` needs `simplify=FALSE`. – Matthew Plourde Jun 10 '13 at 14:41
@MatthewPlourde, awesome! made the edit. – Arun Jun 10 '13 at 14:55

score 2 · Answer 3 · answered Jun 10 '13 at 12:36

2

There are already good answers here, but this is another way with by and scan.

stack(by(d$Y, d$X, 
         function(x) as.numeric(scan(text=as.character(x), what='', quiet=TRUE))))

answered Jun 10 '13 at 12:36

Matthew Plourde

43,932
7
96
113

2

@Dryad, now, the trick is to try to figure out *why*.... – A5C1D2H2I1M1N2O1R2T1 Jun 10 '13 at 13:02
This is about 10 times slower than using `strsplit` on a 20e4*2 data.frame. – Arun Jun 10 '13 at 13:55

R: How to split string on certain column and move each substring to next line?

3 Answers3

Creating data:

Functions:

Benchmarking:

Related