CODEWITHSUNDEEP
rstringstringi
Eric Chang
Eric Chang

Reputation: 2753

Convert a large scale characters to date-format-like characters in r

I have a data frame df with 10 million rows. I want to convert the character format of "birthday" column from "xxxxxxxx" to "xxxx-xx-xx". eg. from "20051023" to "2005-10-23". I can use df$birthday <- lapply(df$birthday, as.Date, "%Y%m%d") to do that, but it wastes a lot of memory and computing time for data transforming. However, I just want to convert it to date-like character, but not date type. Therefore I use stringi package because it is written by C language. Unfortunately, df$birthday <- stri_join(stri_sub(df$birthday, from=c(1,5,7), to=c(4,6,8)), collapse = "-") doesn't work, cuz the function doesn't support vector input. Is there any way to solve this problem? Thanks a lot.

Upvotes: 0

Views: 213

Answers (2)

Thierry
Thierry

Reputation: 18487

as.Date works on vectors

 df$birthday <- format(as.Date(df$birthday, "%Y%m%d"), "%Y-%m-%d)

A vectorised function is much faster than apply

library(microbenchmark)
n <- 1e3
df <- data.frame(birthday = rep("20051023", n))
microbenchmark(
  lapply(df$birthday, as.Date, "%Y%m%d"),
  sapply(df$birthday, as.Date, "%Y%m%d"),
  as.Date(df$birthday, "%Y%m%d")
)

 Unit: microseconds
                                   expr       min        lq       mean    median        uq       max neval cld
 lapply(df$birthday, as.Date, "%Y%m%d") 22833.624 25340.118 29064.7188 28406.154 32346.245 58522.360   100   b
 sapply(df$birthday, as.Date, "%Y%m%d") 24048.493 26252.660 29797.9074 28437.156 33119.381 47966.133   100   b
         as.Date(df$birthday, "%Y%m%d")   431.469   447.719   481.5221   461.189   475.086  1984.158   100  a

A regular expression is off-course even faster.

microbenchmark(
  as.character(as.Date(df$birthday, "%Y%m%d")),
  format(as.Date(df$birthday, "%Y%m%d"), "%Y-%m%-d"),
  sub("^(\\d{4})(\\d{2})(\\d{2})$", "\\1-\\2-\\3", df$birthday)
)

Unit: microseconds
                                                                      expr      min       lq     mean
                              as.character(as.Date(df$birthday, "%Y%m%d")) 4923.189 5057.462 5390.313
                        format(as.Date(df$birthday, "%Y%m%d"), "%Y-%m%-d") 3428.657 3553.736 3697.660
 sub("^(\\\\d{4})(\\\\d{2})(\\\\d{2})$", "\\\\1-\\\\2-\\\\3", df$birthday)  713.699  739.997  815.737
    median        uq      max neval cld
 5150.0420 5394.4265 8225.270   100   c
 3594.7875 3665.9865 5753.200   100  b 
  763.0885  783.1865 2433.585   100 a

sub() works on matrices, but not on data.frames. Hence the as.matrix

df <- as.data.frame(matrix("20051023", ncol = 3, nrow = 3))
df$ID <- seq_len(nrow(df))
df[, 1:3] <- sub("^(\\d{4})(\\d{2})(\\d{2})$", "\\1-\\2-\\3", as.matrix(df[, 1:3]))

The matrix solution is faster than the for loop. The difference increases with the number of columns you need to loop over.

df <- as.data.frame(matrix("20051023", ncol = 20, nrow = 3))
df$ID <- seq_len(nrow(df))
library(microbenchmark)
microbenchmark(
  matrix = df[, seq_len(ncol(df) - 1)] <- sub("^(\\d{4})(\\d{2})(\\d{2})$", "\\1-\\2-\\3", as.matrix(df[, seq_len(ncol(df) - 1)])),
  forloop = {
    for(i in seq_len(ncol(df) - 1)){
      df[, i] <- sub("^(\\d{4})(\\d{2})(\\d{2})$", "\\1-\\2-\\3", df[, i])
    }
  }
)

Unit: microseconds
    expr      min       lq      mean    median       uq      max neval cld
  matrix  460.555  476.805  504.3012  494.1235  507.594 1122.522   100  a 
 forloop 1554.425 1590.774 1677.3038 1625.8390 1670.312 3563.845   100   b

Upvotes: 1

Avinash Raj
Avinash Raj

Reputation: 174756

Go with sub.

date <- c("20051023", "20151023")
sub("^(\\d{4})(\\d{2})(\\d{2})$", "\\1-\\2-\\3", date)
# [1] "2005-10-23" "2015-10-23"

Upvotes: 1

Related Questions