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.
Asked
Active
Viewed 208 times
0
Eric Chang
- 2,580
- 4
- 19
- 19
-
`as.character(as.Date(df$birthday, "%Y%m%d"))` ought to do it – Rich Scriven Oct 09 '15 at 16:44
2 Answers
1
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"
Avinash Raj
- 172,303
- 28
- 230
- 274
1
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
Thierry
- 18,049
- 5
- 48
- 66
-
that's true. But format() is faster than as.character(). See the benchmark. – Thierry Oct 09 '15 at 16:50
-
Thanks, Thierry. If I have two or more columns to convert, eg. df[, c(3,5,7)], how can I do that without apply? Is there any way as fast as as.Date() or regular expression? – Eric Chang Oct 09 '15 at 17:07
-
for(i in c(3,5,7)){ df[,i] <- sub("^(\\d{4})(\\d{2})(\\d{2})$", "\\1-\\2-\\3", y[,i])} is the only way? – Eric Chang Oct 09 '15 at 17:16
-
Thanks for teaching me this skill. It's really helpful to me to improve the coding skill and concept. Thank you very much, Thierry. – Eric Chang Oct 12 '15 at 11:13