Combining irregular H:M:S time stamp data into hourly intervals in R

apologies if there is already an answer to a similar query but I can't seem to find it! I'm a newbie to R but determined not to revert back to VBA for this...

My question is about preparing data ready for forecasting with ses. I have a set of ticket data (~25,000 entries) with time stamps that I've imported from Excel:

      Number             Created        Category  Priority `Incident state` `Reassignment count` Urgency  Impact
   <dbl>              <dttm>           <chr>     <chr>            <chr>                <dbl>   <chr>   <chr>
1      1 2014-07-01 19:16:00 Software/System 5 - Minor           Closed                    0 3 - Low 3 - Low
2      2 2014-07-02 15:27:00 Software/System 5 - Minor           Closed                    0 3 - Low 3 - Low
3      3 2014-07-02 15:27:00 Software/System 5 - Minor           Closed                    0 3 - Low 3 - Low
4      4 2014-07-02 15:27:00 Software/System 5 - Minor           Closed                    0 3 - Low 3 - Low
5      5 2014-07-02 15:28:00 Software/System 5 - Minor           Closed                    0 3 - Low 3 - Low
6      6 2014-07-02 15:29:00 Software/System 5 - Minor           Closed                    0 3 - Low 3 - Low

The data is not regularly spaced out as no tickets are raised outside of working hours so I can't specify a seq(). I need to subset the Created column into hourly blocks ahead of converting into a time series that I can forecast from. I tried rounding the Created column to hours:

modelling_messy$Created <- as.POSIXct(modelling_messy$Created,format="%Y/%m/%d %H:%M:%S", tz = "GMT")
modelling_messy$Created <- as.POSIXct(round(modelling_messy$Created, units = "hours"))

This made my data look the way I wanted, and allowed me to aggregate() all entries with the same hourly time stamp, but it goes all squinty when I use ts()

# A tibble: 2 x 8
  Number             Created        Category Priority `Incident state` `Reassignment count` Urgency  Impact
   <dbl>              <dttm>           <chr>    <dbl>            <chr>                <dbl>   <chr>   <chr>
1      1 2014-07-01 19:00:00 Software/System        5           Closed                    0 3 - Low 3 - Low
2      2 2014-07-02 15:00:00 Software/System        5           Closed                    0 3 - Low 3 - Low

> myts <- ts(modelling_clean[,1:2], start = c(2014-07-01, 1), freq = 1)
> head(myts)
Time Series:
Start = 2006 
End = 2011 
Frequency = 1 
        Group.1 Number
2006 1404241200      1
2007 1404313200      5
2008 1404316800      1
2009 1404907200      8
2010 1404910800     28
2011 1404914400      1

I know that I've messed up ts() somehow but I can't find how to fix it! I want the time data to remain as "%Y-%m-%d %H:00:00" or other useful date/hour combination (I'm only covering 2014 - 2017 by the way).

Any and all help is greatly appreciated.

Ta muchly.

EDIT Thanks for the advice - I think this will solve the problem of conversion to the time series but I'm unsure of how to take the data for df$Created from my current Tibble (too much data to manually code in!) I attempted the following but threw an error:

> df = data.frame(Created = modelling_messy$Created),stringsAsFactors = F)
Error: unexpected ',' in "df = data.frame(Created = modelling_messy$Created),"
> df$id = seq_along(nrow(df))
Error in df$id = seq_along(nrow(df)) :

object of type 'closure' is not subsettable

Thanks in advance!

Solution

You could create hourly timeseries with the xts package as follows:

library(xts)

# sample data
df = data.frame(Created = c("2014-07-01 19:16:00","2014-07-02 15:27:00","2014-07-02 15:27:00","2014-07-02 15:27:00",
                "2014-07-02 15:28:00","2014-07-02 15:29:00"),stringsAsFactors = F)
df$id = seq_along(nrow(df))

# Round dates to hours
df$Created <- as.POSIXct(df$Created,format="%Y-%m-%d %H", tz = "GMT")


# Let's aggregate and create hourly data
df = aggregate(id ~ Created, df,length)
time_series = data.frame(Created= seq( min(df$Created), max(df$Created),by='1 hour'))
time_series = merge(time_series,df,by="Created",all.x=TRUE)
time_series$id[is.na(time_series$id)]=0

# create timeseries object
library(xts)
myxts = xts(time_series$id, order.by = time_series$Created)

Output:

                    [,1]
2014-07-01 19:00:00    1
2014-07-01 20:00:00    0
2014-07-01 21:00:00    0
2014-07-01 22:00:00    0
2014-07-01 23:00:00    0
2014-07-02 00:00:00    0
2014-07-02 01:00:00    0
2014-07-02 02:00:00    0
2014-07-02 03:00:00    0
2014-07-02 04:00:00    0
2014-07-02 05:00:00    0
2014-07-02 06:00:00    0
2014-07-02 07:00:00    0
2014-07-02 08:00:00    0
2014-07-02 09:00:00    0
2014-07-02 10:00:00    0
2014-07-02 11:00:00    0
2014-07-02 12:00:00    0
2014-07-02 13:00:00    0
2014-07-02 14:00:00    0
2014-07-02 15:00:00    5

It's working!

Disclaimer: This is my first time playing with time series in R, so there may be other (i.e. better) ways to achieve this.