Brad Patrick Schneid
2010-Apr-01 17:35 UTC
[R] time series problem: time points don't match
Hi, I have a time series problem that I would like some help with if you have the time. I have many data from many sites that look like this: Site.1 date time level temp 2009/10/01 00:01:52.0 2.8797 18.401 2009/10/01 00:16:52.0 2.8769 18.382 2009/10/01 00:31:52.0 2.8708 18.309 2009/10/01 00:46:52.0 2.8728 18.285 2009/10/01 01:01:52.0 2.8716 18.245 2009/10/01 01:16:52.0 2.8710 18.190 Site.2 date time level temp 2009/10/01 00:11:06.0 2.9507 18.673 2009/10/01 00:26:06.0 2.9473 18.630 2009/10/01 00:41:06.0 2.9470 18.593 2009/10/01 00:56:06.0 2.9471 18.562 2009/10/01 01:11:06.0 2.9451 18.518 2009/10/01 01:26:06.0 2.9471 18.480 As you can see, the times do not match up. What I would like to do is be able to merge these two data sets to the nearest time stamp by creating a new time between the two; something like this: date new.time level.1 temp.1 level.2 temp.2 2009/10/01 00:01:52.0 2.8797 18.401 NA NA 2009/10/01 00:13:59.0 2.8769 18.382 2.9507 18.673 2009/10/01 00:28:59.0 2.8708 18.309 2.9473 18.630 2009/10/01 00:43:59.0 2.8728 18.285 2.9470 18.593 2009/10/01 00:59:59.0 2.8716 18.245 2.9471 18.562 2009/10/01 01:13:59.0 2.8710 18.190 2.9451 18.518 2009/10/01 01:26:06.0 NA NA 2.9471 18.480 Note that the sites may not match in the # of observations and a return of NA would be necessary, but a deletion of that time point all together for both sites would be preferred. A possibly easier alternative would be a way to assign generic times for each observation according to the time interval, so that the 1st observation for each day would have a time = 00:00:00 and each consecutive one would be 15 minutes later. Thanks for any suggestions. Brad -- View this message in context: http://n4.nabble.com/time-series-problem-time-points-don-t-match-tp1748387p1748387.html Sent from the R help mailing list archive at Nabble.com.
Perhaps something like this:
library(zoo)
library(chron)
# read in data
Lines1 <- "date time level temp
2009/10/01 00:01:52.0 2.8797 18.401
2009/10/01 00:16:52.0 2.8769 18.382
2009/10/01 00:31:52.0 2.8708 18.309
2009/10/01 00:46:52.0 2.8728 18.285
2009/10/01 01:01:52.0 2.8716 18.245
2009/10/01 01:16:52.0 2.8710 18.190"
Lines2 <- "date time level temp
2009/10/01 00:11:06.0 2.9507 18.673
2009/10/01 00:26:06.0 2.9473 18.630
2009/10/01 00:41:06.0 2.9470 18.593
2009/10/01 00:56:06.0 2.9471 18.562
2009/10/01 01:11:06.0 2.9451 18.518
2009/10/01 01:26:06.0 2.9471 18.480"
DF1 <- read.table(textConnection(Lines1), header = TRUE, as.is = TRUE)
DF2 <- read.table(textConnection(Lines2), header = TRUE, as.is = TRUE)
z1 <- zoo(DF1[3:4], chron(DF1[,1], DF1[,2], format=c("Y/M/D",
"H:M:S")))
z2 <- zoo(DF2[3:4], chron(DF2[,1], DF2[,2], format=c("Y/M/D",
"H:M:S")))
# process inputs z1 and z2
# aggregating into 15 minute intervals and merging
z1a <- aggregate(z1, trunc(time(z1), "00:15:00"), tail, n = 1)
z2a <- aggregate(z2, trunc(time(z2), "00:25:00"), tail, n = 1)
z <- merge(z1a, z2a)
On Thu, Apr 1, 2010 at 1:35 PM, Brad Patrick Schneid <bpschn01 at
gmail.com> wrote:>
> Hi,
> I have a time series problem that I would like some help with if you have
> the time. ?I have many data from many sites that look like this:
>
> Site.1
> date ? ? ? ? ? ?time ? ? ? ? ? ?level ? ? ? ? ? temp
> 2009/10/01 00:01:52.0 ? ? ?2.8797 ? ? ?18.401
> 2009/10/01 00:16:52.0 ? ? ?2.8769 ? ? ?18.382
> 2009/10/01 00:31:52.0 ? ? ?2.8708 ? ? ?18.309
> 2009/10/01 00:46:52.0 ? ? ?2.8728 ? ? ?18.285
> 2009/10/01 01:01:52.0 ? ? ?2.8716 ? ? ?18.245
> 2009/10/01 01:16:52.0 ? ? ?2.8710 ? ? ?18.190
>
> Site.2
> date ? ? ? ? ? ?time ? ? ? ? ? ?level ? ? ? ? ?temp
> 2009/10/01 00:11:06.0 ? ? ?2.9507 ? ? ?18.673
> 2009/10/01 00:26:06.0 ? ? ?2.9473 ? ? ?18.630
> 2009/10/01 00:41:06.0 ? ? ?2.9470 ? ? ?18.593
> 2009/10/01 00:56:06.0 ? ? ?2.9471 ? ? ?18.562
> 2009/10/01 01:11:06.0 ? ? ?2.9451 ? ? ?18.518
> 2009/10/01 01:26:06.0 ? ? ?2.9471 ? ? ?18.480
>
> As you can see, the times do not match up. ?What I would like to do is be
> able to merge these two data sets to the nearest time stamp by creating a
> new time between the two; something like this:
>
>
> date ? ? ? ? ? ?new.time ? ? ? ?level.1 ? ? ? temp.1 ? ?level.2 ? ? ? ?
temp.2
> 2009/10/01 00:01:52.0 ? ? ?2.8797 ? ? ?18.401 ? NA ? ? ? ? ? ? NA
> 2009/10/01 00:13:59.0 ? ? ?2.8769 ? ? ?18.382 ? ? ?2.9507 ? ? ?18.673
> 2009/10/01 00:28:59.0 ? ? ?2.8708 ? ? ?18.309 ? ? ?2.9473 ? ? ?18.630
> 2009/10/01 00:43:59.0 ? ? ?2.8728 ? ? ?18.285 ? ? ?2.9470 ? ? ?18.593
> 2009/10/01 00:59:59.0 ? ? ?2.8716 ? ? ?18.245 ? ? 2.9471 ? ? ?18.562
> 2009/10/01 01:13:59.0 ? ? ?2.8710 ? ? ?18.190 ? ? 2.9451 ? ? ?18.518
> 2009/10/01 01:26:06.0 ? ? ? NA ? ? ? ? ? ? ?NA ? ? ? ? ?2.9471 ? ? ?18.480
>
> Note that the sites may not match in the # of observations and a return of
> NA would be necessary, but a deletion of that time point all together for
> both sites would be preferred.
>
> A possibly easier alternative would be a way to assign generic times for
> each observation according to the time interval, so that the 1st
observation
> for each day would have a time = 00:00:00 and each consecutive one would be
> 15 minutes later.
>
> Thanks for any suggestions.
>
> Brad
>
> --
> View this message in context:
http://n4.nabble.com/time-series-problem-time-points-don-t-match-tp1748387p1748387.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.
>
Brad Patrick Schneid
2010-Apr-02 00:21 UTC
[R] time series problem: time points don't match
Gabor: That is not the ideal solution, but it definitely works to provide me with the "easier alternative". Thanks for the reply! -- View this message in context: http://n4.nabble.com/time-series-problem-time-points-don-t-match-tp1748387p1748706.html Sent from the R help mailing list archive at Nabble.com.