[R] Merging on Datetime Column

Rui Barradas ruipbarradas at sapo.pt
Fri Jul 13 12:41:51 CEST 2012


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.
>



More information about the R-help mailing list