Use dplyr::mutate and lubridate::force_tz based on arguments from data frame columns

Question

I am trying to use lubridate::force_tz to add timezone information to timestamps (date+time) formatted as strings (as.character()). Both are stored as two columns in a data frame:

require(lubridate)
require(dplyr)
row1<-c(as.character(now()),"Etc/UTC")
row2<-c(as.character(now()+5),"America/Chicago")
df<-as.data.frame(rbind(row1,row2))
names(df)<-c("dt","tz")

x<-force_tz(as.POSIXct(as.character(now())),"Etc/UTC") #works
df<-df%>%mutate(newDT=force_tz(as.POSIXct(dt),tz)) #fails

I get: Error in UseMethod("mutate_") : no applicable method for 'mutate_' applied to an object of class "c('matrix', 'character')"

Following Stibu's comments, I tried (an un-R like) approach with an iteration:

for (i in seq(from=1,to=length(df$dt))){
timestamp<-as.character(df[i,1])
tz<-as.character(df[i,2])
print(tz)
newdt<-force_tz(as.POSIXct(timestamp),tz)
df[i,3]<-newdt
print(attr(df[i,3],"tzone"))
df$timezone<-attr(df[i,3],"tzone")
}

This extracts the values correctly, but seems to get stuck with setting the value of the tz to the first value encountered - weirdly:

[1] "Etc/UTC"
[1] "Etc/UTC"
[1] "America/Chicago"
[1] "Etc/UTC"

I would have expected the last printout to result in "America/Chicago" The df then looks like:

 > df
               dt              tz               newDT timezone
 1 2016-04-13 23:07:45         Etc/UTC 2016-04-13 23:07:45  Etc/UTC
 2 2016-04-13 23:07:50 America/Chicago 2016-04-14 04:07:50  Etc/UTC

Answer 1

You have actually two issues in your code that I will discuss separately below.

dplyr works with data frames

Your df is a matrix, not a data frame. But mutate() (and functions from dplyr in general) works with data frames. The error message simply tells you that mutate() does not know what to do with a matrix.

You can solve this by converting df to a data frame:

df <- as.data.frame(df)
names(df)<-c("dt","tz")

A remark regarding names(): This function can be used to get/set the column names of a data frame. For matrices, the corresponding function is colnames(). You used names() on a matrix, which did not set the column names of the matrix. Therefore, the names of the data frame are also not set after conversion.

You could also create a data frame from the start as follows:

df <- data.frame(dt = as.character(c(now(), now() + 5)),
                 tz = c("Etc/UTC", "America/Chicago"),
                 stringsAsFactors = FALSE)

Note that you need to define the contents column-wise, not row-wise as you did.

If you use the data frame df, there will be no error from mutate().

One time zone per vector

Unfortunately, there is a second issue. What you want to do simply cannot be done. The reason is the following.

Let's convert the first column of df to POSIXct with time zone CET:

ts <- as.POSIXct(df$dt, tz = "CET")
ts
## [1] "2016-04-13 14:42:26 CEST" "2016-04-13 14:42:31 CEST"

Let's try to do the same with two time zones:

ts <- as.POSIXct(df$dt, tz = c("CET", "UTC"))
## Error in strptime(xx, f <- "%Y-%m-%d %H:%M:%OS", tz = tz) : 
##   invalid 'tz' value

This does not work. The reason is that there is a single time zone per vector and not a time zone per element in the vector. Look at the attributes of ts:

attributes(ts)
## $class
## [1] "POSIXct" "POSIXt" 
## 
## $tzone
## [1] "CET"

The time zone is set as an attribute of the entire vector and it is not a property of each element.