Is there a faster method to split string into substrings of a given length?

Question

I have some weather data with fixed width in columns, but length depends on variable (See below, data from GHCN, http://www1.ncdc.noaa.gov/pub/data/ghcn/daily/readme.txt).

I would like to split them into a data.frame and have wrote some codes following the suggestion of @GSee (How to split a string into substrings of a given length?). But it took about 4.3 s to process 6000 rows.

Is there a faster method to process this dataset?

Thanks for any suggestions.

temp <- readLines(textConnection("NO000050550193801TMAX   53  I   51  I   10  I   22  I   56  I   31  I   30  I   24  I   38  I   25  I    2  I   32  I   75  I   71  I   98  I   96  I   57  I   55  I   54  I   60  I   91  I   75  I   94  I   82  I   89  I   46  I   26  I   68  I   62  I   46  I   37  I
NO000050550193801TMIN   25  I   -6  I  -27  I    0  I    3  I  -14  I   -8  I   11  I   10  I  -11  I  -30  I  -23  I   22  I   38  I   47  I   33  I   13  I    5  I   10  I   29  I   42  I   45  I   51  I   44  I   35  I    5  I  -16  I  -20  I    5  I    2  I    5  I
NO000050550193802TMAX   69  I   58  I   71  I   90  I   77  I   70  I   56  I   46  I   58  I   32  I   32  I   22  I   25  I   30  I   29  I   29  I   34  I   88  I   58  I   50  I   45  I   62  I   38  I   40  I   59  I  112  I   92  I   77  I-9999   -9999   -9999   
NO000050550193802TMIN   11  I   26  I   16  I   35  I   44  I   21  I   19  I   22  I   20  I    6  I    6  I  -16  I  -22  I  -39  I  -28  I  -35  I  -33  I  -21  I  -13  I   15  I   26  I   17  I   -1  I    9  I   18  I   38  I   58  I   28  I-9999   -9999   -9999   
NO000050550193803TMAX   81  I   84  I   89  I   86  I   86  I   74  I   54  I   74  I   83  I   64  I   75  I   77  I   66  I   91  I   82  I   84  I   89  I   84  I   94  I   85  I   82  I   89  I   74  I   84  I   81  I   58  I   72  I   58  I   86  I   84  I   89  I
NO000050550193803TMIN   31  I   25  I   29  I   45  I   61  I   20  I    9  I    8  I   38  I   31  I    9  I   39  I   27  I   56  I   48  I   65  I   45  I   54  I   46  I   42  I   43  I   36  I   56  I   61  I   15  I   -2  I  -11  I   -2  I   12  I   30  I   24  I"))

temp <- rep(temp, 1000)
system.time({

out <- strsplit(temp, '')
out <- as.matrix(do.call(rbind, out))
pos_matrix <- matrix(c(12, 16, 18, seq(0, 30) * 8 + 22,
    15, 17, 21, seq(0, 30) * 8 + 26), ncol = 2)
out <- apply(out, 1, function(x)
    {
        apply(pos_matrix, 1, function(y) 
            paste(x[y[1]:y[2]], collapse = ''))
    })
})

user  system elapsed 
4.46    0.01    4.52

EDIT WITH Ananda Mahto comment:

system.time({
pos_matrix <- matrix(c(12, 16, 18, seq(0, 30) * 8 + 22,
    15, 17, 21, seq(0, 30) * 8 + 26), ncol = 2)
pos_matrix <- lapply(seq(1, nrow(pos_matrix)), function(x) 
    {
        sprintf('substr(V1, %s, %s) f%s',
            pos_matrix[x,1], pos_matrix[x,2], x)
    })
pos_matrix <- paste(pos_matrix, collapse = ', ')
out <- data.frame(V1 = temp)

out <- sqldf(sprintf('select %s from out', pos_matrix))
})

user  system elapsed 
 0.4     0.0     0.4

EDIT WITH jlhoward suggestion:

system.time({
pos_matrix <- matrix(c(12, 16, 18, seq(0, 30) * 8 + 22,
    15, 17, 21, seq(0, 30) * 8 + 26), ncol = 2)
out <- apply(pos_matrix, 1, function(x)
    {
        substr(temp, x[1], x[2])
    })
})
user  system elapsed 
0.04    0.00    0.04

Use `sqldf` and `substr` as explained in [Example 6f here](http://code.google.com/p/sqldf/)? — A5C1D2H2I1M1N2O1R2T1, Dec 18 '13 at 19:53
sqldf and substr is much faster. It only took 0.4 s for the same dataset. You man add your comments into answer, then I can accept it. — Bangyou, Dec 18 '13 at 20:38

jlhoward · Accepted Answer · 2013-12-18T21:13:07.503

2

Profiling your code (?Rprof) shows that 2/3 of the execution time was spent in paste(...), which is not surprising. It looks like you are breaking up the input into individual characters and then reassembling them based on pos_matrix(...). It might be more efficient to use substr(...) with a matrix that has starting position and length.

Edit: Adding code to implement suggestion above

vec <- as.vector(temp)
pos_matrix <- matrix(c(12, 16, 18, seq(0, 30) * 8 + 22,
                       15, 17, 21, seq(0, 30) * 8 + 26), ncol = 2)
pos <- t(pos_matrix)
system.time(
out <- do.call(rbind,list(apply(pos,2,function(x){substr(vec,x[1],x[2])})))
)
#   user  system elapsed 
#   0.09    0.00    0.09

edited Dec 18 '13 at 21:13

answered Dec 18 '13 at 20:40

jlhoward

58,004
7
97
140

Thanks for your suggestion. It only took 0.4 s for the same dataset and similar speed with sqldf (but don't need load sqldf package). – Bangyou Dec 18 '13 at 20:45
Glad it worked for you. I've added code above, but it looks like you've already got it figured out. – jlhoward Dec 18 '13 at 21:13

IRTFM · Answer 2 · 2013-12-18T20:56:59.517

1

There is a fixed-width read function in the utils package (loaded by default):

m <- matrix(c(12, 16, 18, seq(0, 30) * 8 + 22,
     15, 17, 21, seq(0, 30) * 8 + 26), ncol = 2)
read.fwf(textConnection(temp), c(11,             # which you are apparently ignoring
                                 m[,2]-m[,1]+1)  )

But for me at least, 6000 such records took 9 seconds.

edited Dec 18 '13 at 20:56

answered Dec 18 '13 at 20:02

IRTFM

258,963
21
364
487

score 0 · Answer 3 · answered Dec 18 '13 at 21:27

scan -- which works with files or connections. It is possible to modify your code to work with temp as given above but more conveniently:

writeLines(temp, "temp.txt")
scan("temp.txt", what=""))
# and now convert it to a matrix of appropriate size

Not sure if it is faster than sqldf-based solution but it looks more straightforward to me.

[[note]] Ok you asked about "substrings of a given length" so technically my answer is about something else. But it looks like this might actually be helpful with a file like in this example.

Is there a faster method to split string into substrings of a given length?

3 Answers3

Linked