[R] Merging on Datetime Column

[Rui Barradas]

Hello,

Check the structure of what you have, df and newdf. You will see that in 
df dateTime is of class POSIXlt and in newDf newDateTime is of class 
POSIXct.

Solution:

[...]
df$dateTime <- strptime(df$dateTime,"%m/%d/%Y %H:%M")
df$dateTime <- as.POSIXct(df$dateTime)
[...]

Hope this helps,

Rui Barradas

Em 13-07-2012 10:24, vioravis escreveu:
> I have the following dataframe with the first column being of type datetime:
>
> dateTime <- c("10/01/2005 0:00",
>                "10/01/2005 0:20",
>                "10/01/2005 0:40",
>                "10/01/2005 1:00",
>                "10/01/2005 1:20")
> var1 <- c(1,2,3,4,5)
> var2 <- c(10,20,30,40,50)
> df <- data.frame(dateTime = dateTime, var1 = var1, var2 = var2)
> df$dateTime <- strptime(df$dateTime,"%m/%d/%Y %H:%M")
>
> I want to create 10 minute interval data as follows:
>
> minTime <- min(df$dateTime)
> maxTime <- max(df$dateTime)
> newTime <- seq(minTime,maxTime,600)
> newDf <- data.frame(newDateTime = newTime)
> newDf <- merge(newDf,df,by.x = "newDateTime",by.y = "dateTime",all.x = TRUE)
>
> The objective here is to create a data frame with values from df for the
> datetime in df and NA for the missing ones. However, I am getting the
> following data frame with both Var1 and Var2 having all NAs.
>
>> newDf
>            newDateTime var1 var2
> 1 2005-10-01 00:00:00   NA   NA
> 2 2005-10-01 00:10:00   NA   NA
> 3 2005-10-01 00:20:00   NA   NA
> 4 2005-10-01 00:30:00   NA   NA
> 5 2005-10-01 00:40:00   NA   NA
> 6 2005-10-01 00:50:00   NA   NA
> 7 2005-10-01 01:00:00   NA   NA
> 8 2005-10-01 01:10:00   NA   NA
> 9 2005-10-01 01:20:00   NA   NA
>
> Can someone help me on how to do the merge based on the two datetime
> columns?
>
> Thank you.
>
> Ravi
>
>
>
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/Merging-on-Datetime-Column-tp4636417.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>