problem with hour interval in time series data in r

Question

I am new at using R and I am encountering a problem with historical hourly electric load data that I have downloaded.My goal is to make a load forecast based on an ARIMA model and/or Artificial Neural Networks.

The problem is that the data is in the following Date-time (hourly) format:

#>      DateTime                              Day_ahead_Load Actual_Load
#> [1,] "01.01.2015 00:00 - 01.01.2015 01:00" "6552"         "6100"     
#> [2,] "01.01.2015 01:00 - 01.01.2015 02:00" "6140"         "5713"     
#> [3,] "01.01.2015 02:00 - 01.01.2015 03:00" "5950"         "5553"

I have tried to make a POSIXct object but it didn't work:

as.Date.POSIXct(DateTime, format = "%d-%m-%Y %H:%M:%S", tz="EET", usetz=TRUE)

The message I get is that it is not in an unambiguous format. I would really appreciate your feedback on this. Thank you in advance.

Best Regards,

Iro

Hi Iro, this is a question and answer site which focuses on answering **specific** programming questions. It is not a tutorial site. That said, some users may choose to answer a question this broad. For the best chance of receiving an answer, please provide a link to the data you downloaded and clarify exactly what your goal is. See [How to make a reproducible example](https://stackoverflow.com/questions/5963269/) for more info. — Ian Campbell, Jun 23 '20 at 12:27
Thank you for your guidelines, I provided a copy of the first 3 lines of my data. The problem is that I cannot make a POSIXct object as the date/time format is not recognised. — Iro, Jun 24 '20 at 13:03

score 0 · Accepted Answer · answered Jun 24 '20 at 13:28

You have 2 major problems. First, your DateTime column contains two dates, so you need to split that column into two. Second, your format argument has - characters but your date has . characters.

We can use separate from tidyr and mutate with across to change the columns to POSIXct.

 library(dplyr)
 library(tidyr)
 data %>% 
   separate(DateTime, c("StartDateTime","EndDateTime"), " - ") %>%
   mutate(across(c("StartDateTime","EndDateTime"),
                 ~ as.POSIXct(., format = "%d.%m.%Y %H:%M",
                              tz="EET", usetz=TRUE)))
        StartDateTime         EndDateTime Day_ahead_Load Actual_Load
1 2015-01-01 00:00:00 2015-01-01 01:00:00           6552        6100
2 2015-01-01 01:00:00 2015-01-01 02:00:00           6140        5713
3 2015-01-01 02:00:00 2015-01-01 03:00:00           5950        5553

problem with hour interval in time series data in r

1 Answers1