How to create a time series of hourly data?

Question

I have data in an hourly values.

SNo Date       Hour     X
1   2006-12-17 00:00:00 1.8824667
2   2006-12-17 01:00:00 3.3494000
3   2006-12-17 02:00:00 1.5872667
4   2006-12-17 03:00:00 1.6622000
5   2006-12-17 04:00:00 2.2157667
6   2006-12-17 05:00:00 1.9967333
7   2006-12-17 06:00:00 1.3033000
8   2006-12-17 07:00:00 1.6200333
9   2006-12-17 08:00:00 1.8905667
10  2006-12-17 09:00:00 2.5490667
11  2006-12-17 10:00:00 3.6289000

How would I create a time series out of this? What would be the frequency and start/end parameters?

The last date & time is

2010-11-26 21:00:00

Liquefacient

Strictly speaking, you already have a time serie. You could check package xts. – Histolysis

@Pascal: they're likely referring to a ts object, which has a constructor that has start, end, and frequency arguments. – Rootless

@JoshuaUlrich Yes, sure, of course. – Histolysis

you could coerce your date and time columns into a POSIXct object data$dateTime = as.POSIXct(paste(data$Date,data$Hour), format = "%Y-%m-%d %H:%M:%S") then you can create a time series object with something like this: data$ts = ts(ts = data$dateTime, start = data$dateTime[1], end = data$dateTime[nrow(data)]) – Malo

@ColinCharles What would the frequency arg be in the ts function you've given? These are hourly electric power values, spread over 4 years. I want to observe seasonality, trend etc so would decompose work without freq argument? – Liquefacient

@Liquefacient I think you should just be able to leave your frequency as a value of 1 since you're entering hourly data – Malo

Answer 1

Here's how to use the ts() function in base R (assuming your data X are contained in the data frame dat). You'll need to specify the first year and hour for start (you don't need end), and frequency will be the number of hours in a year.

firstHour <- 24*(as.Date("2006-12-17 00:00:00")-as.Date("2006-1-1 00:00:00"))
tt <- ts(dat$X,start=c(2006,firstHour),frequency=24*365)

Spiderwort

This does not handle leap years. How to do so see here – Accepter

Answer 2

library(lubridate)
NoOfHours <- as.numeric(ymd_hms("2010-11-26 21:00:00") - ymd_hms("2006-12-01 00:00:00"))*24 
ymd_hms("2006-12-01 00:00:00") + hours(0:NoOfHours)

Interblend

Answer 3

Step 1: You need to concatenate Date and Hour columns in POSIXct format:

df$Date <- as.POSIXct(paste(df$Date, df$Time))

Step 2: As this data is hourly time series, you should convert it in xts object as xts handles hourly data better than ts. order.by is the value of your column that has time observations.

df <- as.xts(df, order.by = df$Date)

Your hourly time series data df is now ready

Footing

Answer 4

I would use zoo package and specialy handy function read.zoo to create the time series.

library(zoo)
## if you have a file input replace text= by filename
x.zoo <- read.zoo(text="SNo Date   Hour     X 
1   2006-12-17 00:00:00 1.8824667
2   2006-12-17 01:00:00 3.3494000
3   2006-12-17 02:00:00 1.5872667
4   2006-12-17 03:00:00 1.6622000
5   2006-12-17 04:00:00 2.2157667
6   2006-12-17 05:00:00 1.9967333
7   2006-12-17 06:00:00 1.3033000
8   2006-12-17 07:00:00 1.6200333
9   2006-12-17 08:00:00 1.8905667
10  2006-12-17 09:00:00 2.5490667
11  2006-12-17 10:00:00 3.6289000",index=c(2,3),tz="",
header=TRUE)

Then it is easy to coerce it a ts object:

as.ts(x.zoo)
Time Series:
Start = 1166310000 
End = 1166346000 
Frequency = 0.000277777777777778 

Mallemuck

Answer 5

How about this:

df <- data.frame(Date = rep("2006-12-01", 10),
                 Time = paste0(1:10, ":00:00"),
                 x = rnorm(10))

library(zoo)
df$Date <- as.POSIXct(paste(df$Date, df$Time), "GMT")
as.zoo(df[, c("Date", "x")])

# Date                x         
# 1  2006-12-01 01:00:00 -0.1386150
# 2  2006-12-01 02:00:00  1.8828398
# 3  2006-12-01 03:00:00  0.8736687
# 4  2006-12-01 04:00:00 -0.9145971
# 5  2006-12-01 05:00:00 -1.2449176
# 6  2006-12-01 06:00:00 -0.3599822
# 7  2006-12-01 07:00:00  1.3287747
# 8  2006-12-01 08:00:00  0.2926791
# 9  2006-12-01 09:00:00 -0.7015052
# 10 2006-12-01 10:00:00  0.8822346

Fae

I am getting this in R Date x 1 1164934800 -1.8954203 2 1164938400 -1.4847575 3 1164942000 1.1840722 What are those values in the date column? Are they number of seconds since 1970 (the default date in R or something like that)? – Liquefacient

I edited the answer, now I am using zoo and as.zoo to transform the data.frame to a time-series object. Apparently, ts doesn't like POSIXct-objects as dates... weird. – Fae

Answer 6

Some of the answers do not consider leap years, including the most upvoted. See Rob Hyndman on how to define hourly time series in R (here and here):

firstHour <- 24*(as.Date("2006-12-17 00:00:00")-as.Date("2006-1-1 00:00:00")) # As suggested by Mark S
tt <- ts(dat$X,start=c(2006,firstHour),frequency=24*365.25)

So the frquency should be 365.25*24 not 365*24. This is true if we talk about yearly periods. We could also be talking about hourly time series within weeks which would change the frequency to 7*24. We can handle multiple seasonal periods with msts:

library(forecast)
msts(x, seasonal.periods= c(7*24, 365.25*24))

Accepter

Answer 7

The most upvoted answer using ts() seems outdated. I would use xts or zoo to plot hourly data, the primary reason being lack of clarity around the argument 'start' in ts().

Here's the steps you should follow:

1. Stop wasting time trying to plot it with ts()

2. For xts/zoo, add a new variable called "datetime". Here's how you could do it:

    data$DateTime <- as.POSIXct(paste(data$Date,data$Time),format = "%Y-%m-%d %H:%M")
   

Here, data refers to your dataframe.

3. Create an xts/zoo object:

   data_xts <- xts(data$X, order.by = data$DateTime)

4. Plot the series

   plot(data_xts, xlab = "Time", ylab = "Traffic", main = "Hourly Traffic Time Series")

Awry