-1

question about joining time-series tables in R...

I have the following two dataframes; "arrivals" and "temps" (see below). "arrivals" describes the date/time/size of an arriving packet, and "temps" has minute-by-minute temperature data. What I would like to do is add a column in "arrivals", arrivals$tempAtArrivalTime, which lists the current temperature for a given arrival time. For instance, for the first arrival, at 8:39:04, I want the corresponding "tempAtArrivalTime" to be 76 (the most recently recorded temperature, at 8:39:00). Whats the easiest way to do this? Thanks!

>arrivals
           date     time size of arrival
2012 2013-09-09 08:39:04              15
2013 2013-09-09 08:43:15               3
2014 2013-09-09 08:47:50               7
2015 2013-09-09 08:54:47              10

> temps
      date     time temperature
2013-09-09 08:33:00          76
2013-09-09 08:34:00          75
2013-09-09 08:35:00          74
2013-09-09 08:36:00          75
2013-09-09 08:37:00          76
2013-09-09 08:38:00          76
2013-09-09 08:39:00          76
2013-09-09 08:40:00          76
2013-09-09 08:41:00          77
2013-09-09 08:42:00          78
2013-09-09 08:43:00          77
2013-09-09 08:44:00          78
2013-09-09 08:45:00          77
2013-09-09 08:46:00          77
2013-09-09 08:47:00          77
2013-09-09 08:48:00          77
2013-09-09 08:49:00          78
2013-09-09 08:50:00          79
2013-09-09 08:51:00          80
2013-09-09 08:52:00          80
2013-09-09 08:53:00          79
2013-09-09 08:54:00          78
Roman Luštrik
  • 64,404
  • 24
  • 143
  • 187
bigO6377
  • 1,094
  • 1
  • 11
  • 25
  • Duplicate of [the following question](http://stackoverflow.com/questions/7089444/r-merge-two-irregular-time-series-solved?rq=1). Downvote due to an obvious duplicate, which is trivial to locate – Oleg Sklyar Jan 09 '14 at 19:05

1 Answers1

1

Try this:

library(zoo)
arrivals.z <- read.zoo(arrivals, index = 1:2, tz = "")
temp.z <- read.zoo(temp, index = 1:2, tz = "")

na.locf(merge(arrivals.z, temp.z))[time(arrivals.z)]

which gives:

                    arrivals.z temp.z
2013-09-09 08:39:04         15     76
2013-09-09 08:43:15          3     77
2013-09-09 08:47:50          7     77
2013-09-09 08:54:47         10     78

Note: To get arrivals and temp we used this. (Next time please use dput to provide the inputs in reproducible form.)

Lines1 <- " date     time size of arrival
2012 2013-09-09 08:39:04              15
2013 2013-09-09 08:43:15               3
2014 2013-09-09 08:47:50               7
2015 2013-09-09 08:54:47              10
"

Lines2 <- "      date     time temperature
2013-09-09 08:33:00          76
2013-09-09 08:34:00          75
2013-09-09 08:35:00          74
2013-09-09 08:36:00          75
2013-09-09 08:37:00          76
2013-09-09 08:38:00          76
2013-09-09 08:39:00          76
2013-09-09 08:40:00          76
2013-09-09 08:41:00          77
2013-09-09 08:42:00          78
2013-09-09 08:43:00          77
2013-09-09 08:44:00          78
2013-09-09 08:45:00          77
2013-09-09 08:46:00          77
2013-09-09 08:47:00          77
2013-09-09 08:48:00          77
2013-09-09 08:49:00          78
2013-09-09 08:50:00          79
2013-09-09 08:51:00          80
2013-09-09 08:52:00          80
2013-09-09 08:53:00          79
2013-09-09 08:54:00          78
"

arrivals <- read.table(text = Lines1, skip = 1)[, -1]
temp <- read.table(text = Lines2, header = TRUE)
G. Grothendieck
  • 211,268
  • 15
  • 177
  • 297
  • Ah terrific, this was almost exactly what i was looking for! The only bug...Further down in my data sheets, I have repeated times but with different dates, yet i get the error "Error in merge.zoo(arrivals.z, temps.z) : series cannot be merged with non-unique index entries in a series". The tuples (date,time) are all unique though. Any clean resolution without having to fuse my variables into a "datetime" object? – bigO6377 Jan 09 '14 at 20:48
  • Putting the date and time together is the clean way of doing it. Keeping them separate is messy. – G. Grothendieck Jan 09 '14 at 20:55