String-transform vector-elements in data.frame

Question

I have a huge data frame df, with in one column a 'year-month' value as follows: "YYYYMM". Currently the data type is a number. Snapshot:

> df[[1]][1:10]
[1] 201001 201001 201001 201001 201001 201001 201001 201001 201001 201001
> str(df)
'data.frame':   2982393 obs. of  11 variables:
 $ YearMonth    : int  201001 201001 201001 201001 201001 201001 201001 201001 201001 201001 ...
 $ ...

What I want is to transform this value to a string (eventually to a factor) in the form "YYYY-MM", to be able to compare this with other data frames.

I'm struggling to find an easy way to transform the value.

I tried using as.Date and the format function. But as the values do not have any days, it didn't work for Strings. With Numerics (same with dataframe column) I even got other problems.

> as.Date("201001", format = "%Y%m")
 [1] NA

> as.Date(201001, format = "%Y%m")
 Error in as.Date.numeric(201001, format = "%Y%m") : 
    'origin' must be supplied
> as.Date(df[[1]], format = "%Y%m")
 Error in as.Date.numeric(df[[1]], format = "%Y%m") : 
    'origin' must be supplied

I'm able to transform just one value, using subset and concatenation of strings. I wrote the formula below, to handle one element:

transformString <- function( x ) { # x = value
    return ( paste(cbind(substring(x, 1, 4),"-",substring(x,5,6)), collapse = '') )
}

Problem: I didn't find an easy way to apply that function to a whole column of an data.frame, other than just traversing all elements:

transformStringVector <- function( x ) { # x = vector
    for(i in 1:length(x)) {
       x[i]<-transformString(x[i])
    }
    return ( x )
}

This is far from elegant and bad for performance. I tried to use apply (see below) and stuff like that, but was confronted with errors... (I admit I do not really get the apply function)

> temp <- apply(df[[1]], 1, transformString )
Error in apply(df[[1]], 1, transformString ) : 
  dim(X) must have a positive length

Does anybody have an alternative for this transformation within a huge data.frame? Or more in general; an easy way to apply string-like-transformations to elements within a data.frame?

Answer 1

The reason why

> as.Date("201001", format = "%Y%m")
 [1] NA

doesn't work, is that an R date needs a day component. Since your date doesn't provide one, you get a missing value. To circumvent this, just add a day component:

R> x = c("201001","201102")
R> x = paste(x, "01", sep="")

So I've made all the dates the first of the month:

R> y = as.Date(x, "%Y%m%d")
[1] "2010-01-01" "2011-02-01"

You can then use format to get what you want:

R> format(y, "%Y-%m")
[1] "2010-01" "2011-02"

Answer 2

If you're just looking to transform the column values into a string in the specified format and don't care about having the date format, substr() and paste() both take vectors as arguments:

xx<-c(201011,201003,201002,201010,201009,201005,201001,201001,201001,201001)

paste(substr(xx,1,4),substr(xx,5,6),sep="-")
# [1] "2010-11" "2010-03" "2010-02" "2010-10" "2010-09" "2010-05" "2010-01"
# [8] "2010-01" "2010-01" "2010-01"

In this way, you don't have to use apply()

Answer 3

To answer your question about applying this to a data.frame specifically, you could access the column using the $ operator. So you could use either of the functions offered here (I would have gone with the substr variant) to do it. If you're planning to convert to a factor, I'd do that first.

> df <- data.frame(a=1:5,b=5:1,d=200101:200105)
> df
  a b      d
1 1 5 200101
2 2 4 200102
3 3 3 200103
4 4 2 200104
5 5 1 200105
> #Convert to a factor now for performance reasons.
> df$d <- as.factor(df$d)
> df$d <- paste(substr(df$d, 1, 4), "-", substr(df$d, 5,6), sep="")
> df
  a b       d
1 1 5 2001-01
2 2 4 2001-02
3 3 3 2001-03
4 4 2 2001-04
5 5 1 2001-05

> typeof(df$d)
[1] "character"
> df$d <- as.factor(df$d)
> df
  a b       d
1 1 5 2001-01
2 2 4 2001-02
3 3 3 2001-03
4 4 2 2001-04
5 5 1 2001-05
> typeof(df$d)
[1] "integer"

Note that depending on how "huge" your data.frame is, you might get better performance by converting to a factor first, then just converting the levels to hyphenated dates.

> df <- data.frame(a=rep(1:5,1000000),b=rep(5:1,1000000),d=rep(200101:200105, 1000000))
> nrow(df)
 [1] 5000000
> # Hyphenate first
> system.time(df$d <- paste(substr(df$d, 1, 4), "-", substr(df$d, 5,6), sep="")) + system.time(df$d <- as.factor(df$d))
  user  system elapsed 
  9.65    0.61   10.31 
>
> #Factor first
> system.time(df$d <- as.factor(df$d)) + system.time(levels(df$d) <- paste(substr(levels(df$d), 1, 4), "-", substr(levels(df$d), 5,6), sep=""))
 user  system elapsed 
 0.68    0.25    0.93 

So, depending on the properties of your data.frame, you may be able to improve performance 10X by doing the factoring first.

P.S. If you really care about performance, you might be able to get better properties on your factoring code (the slowest part of the fast solution) by using a hash-backed factor.