Geek Answers Handbook

How can I correlate data with time intervals?

This is a problem that often occurs when I import raw data from data loggers. The temperature recorder is set to record the temperature every ten minutes, and a separate gas recorder is set to record the gas used during the last ten-minute interval. I want to combine the data from these two registrars into a single data frame for construction and analysis, but the times are not exactly aligned. I want to have one row in the data frame for every ten minute period, with a date showing the beginning of the time period.

The temperature logger data is as follows:

datetime temperature 2010-09-30 06:58:53 78.996 2010-09-30 07:08:53 78.645 2010-09-30 07:18:53 78.514 2010-09-30 07:28:53 79.173 2010-09-30 07:38:53 78.602

The data of the gas logger are as follows:

  datetime gas 2010-09-30 13:45:00 0 2010-09-30 13:55:00 1 2010-09-30 14:05:00 0 2010-09-30 14:15:00 4 2010-09-30 14:25:00 2

I want to combine two data frames at ten minute intervals so that the combined data looks like this:

  datetime temperature gas 2010-09-30 13:40:00 NA 0 2010-09-30 13:50:00 78.996 1 2010-09-30 14:00:00 78.645 0 2010-09-30 14:10:00 78.514 4 2010-09-30 14:20:00 79.173 2 2010-09-30 07:38:53 78.602 NA

Here is the code to get these two data frames:

 temps <- data.frame(datetime=c("2010-09-30 06:58:53", "2010-09-30 07:08:53","2010-09-30 07:18:53", "2010-09-30 07:28:53","2010-09-30 07:38:53"), temperature=c(78.996,78.645,78.514,79.173,78.602), stringsAsFactors=FALSE) temps$datetime <- strptime(temps$datetime, format="%Y-%m-%d %H:%M:%S") gas <- data.frame(datetime=c("2010-09-30 13:45:00", "2010-09-30 13:55:00","2010-09-30 14:05:00", "2010-09-30 14:15:00","2010-09-30 14:25:00"), gas=c(0,1,0,4,2),stringsAsFactors=FALSE) gas$datetime <- strptime(temps$datetime, format="%Y-%m-%d %H:%M:%S")
+4
source share
2 answers

Use align.time in xts .

 library(xts) xTemps <- align.time(xts(temps[,2],as.POSIXct(temps[,1])), n=600) xGas <- align.time(xts(gas[,2],as.POSIXct(gas[,1])), n=600) merge(xTemps,xGas)
+5
source

Use one of zoo or xts to store your data - the merge() function will do it for you. Here you can see the previous answers. The zoo documentation also has many related examples.

Here is an example layout:

 > set.seed(42) > temps <- zoo(78+rnorm(5), + order.by=ISOdatetime(2010,9,30,6,58,53)+cumsum(60*runif(5))) > gas <- zoo(sample(1:5,5), + order.by=ISOdatetime(2010,9,30,6,58,53)+cumsum(60*runif(5))) > merge(temps,gas) temps gas 2010-09-30 06:59:47 78.048 NA 2010-09-30 06:59:49 NA 5 2010-09-30 07:00:44 76.895 NA 2010-09-30 07:00:48 NA 1 2010-09-30 07:00:55 NA 3 2010-09-30 07:01:01 78.539 NA 2010-09-30 07:01:23 NA 2 2010-09-30 07:01:51 78.580 NA 2010-09-30 07:01:57 NA 4 2010-09-30 07:02:29 77.342 NA > na.locf(merge(temps,gas)) temps gas 2010-09-30 06:59:49 78.048 5 2010-09-30 07:00:44 76.895 5 2010-09-30 07:00:48 76.895 1 2010-09-30 07:00:55 76.895 3 2010-09-30 07:01:01 78.539 3 2010-09-30 07:01:23 78.539 2 2010-09-30 07:01:51 78.580 2 2010-09-30 07:01:57 78.580 4 2010-09-30 07:02:29 77.342 4 >
+2
source

More articles:


All Articles