[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