R help - Jul 2012 - Merging on Datetime Column

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.

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