11  Date Manipulation

The base package, one of R’s core packages, provides two types of date and time classes:

1. Date: This is a simple representation of a calendar date following the ISO 8601 international standard format (or the Gregorian calendar format) using the YYYY-m-d date format. Each date object has a numeric value of the number of days since the origin point (the default setting is 1970-01-01). It will make sense to use this format when the frequency of the data is daily or lower (for example, monthly, quarterly, and so on) and the time of the day doesn’t matter.

2. POSIXct/POSIXlt: Also known as the DateTime classes as they represent both date and time, these are two POSIX date/time classes that represent the calendar date, the time of the day, and the time zone using the ISO 8601 international standard format of YYYY-m-d H:M:S. The main difference between the two is the form in which the values are stored internally. The POSIXct class, similar to the Date class, represents the (signed) number of seconds since the origin point (1970-01-01, UTC time zone) as a numeric vector. On the other hand, the POSIXlt class stores each one of the date and time elements as a list.

   It makes sense to use a time object (either POSIXct or POSIXlt objects) only if the series frequency is higher than daily (such as hourly, half-hourly, by minute, or by second). Otherwise, for simplicity reasons, it is recommended that you use the date object. Let’s review the differences between date and time objects.

11.1 Date and Time Classes

You can use the Sys.Date and Sys.time functions to pull date and time objects respectively:

(date <- Sys.Date())

[1] "2024-10-15"

class(date)

[1] "Date"

(time_ct <- Sys.time())

[1] "2024-10-15 20:45:44 CEST"

class(time_ct)

[1] "POSIXct" "POSIXt" 

By default, the Sys.time function returns an object of the POSIXct, POSIXt, or POSIXct class. We can use the as.POSIXlt function to convert the object to a POSIXlt object:

(time_lt <- as.POSIXlt(time_ct))

[1] "2024-10-15 20:45:44 CEST"

class(time_lt)

[1] "POSIXlt" "POSIXt" 

While both the POSIXct and POSIXlt objects have the same representation, the key difference between the two is in the method in which each object is stored internally in the time details. The POSIXct object stored the numeric distance of the time object from the origin point. On the other hand, POSIXlt returned a detailed list with the different time components:

unclass(time_ct)

[1] 1729017945

unclass(time_lt)

$sec
[1] 44.55685

$min
[1] 45

$hour
[1] 20

$mday
[1] 15

$mon
[1] 9

$year
[1] 124

$wday
[1] 2

$yday
[1] 288

$isdst
[1] 1

$zone
[1] "CEST"

$gmtoff
[1] 7200

attr(,"tzone")
[1] ""     "CET"  "CEST"
attr(,"balanced")
[1] TRUE

# You can pull the day of the year
unclass(time_lt)$yday

[1] 288

11.2 Creating Date and Time Objects

Creating a new object or converting the existing object to Date, POSIXlt, or POSIXct can be done with as.Date, as.POSIXlt, or as.POSIXct respectively.

The built-in R function as.Date() can handle only dates but not time. For instance, we can convert the “2014-5-12” string into a Date object using the as.Date function:

Example:

class("2014-5-12")

[1] "character"

class(as.Date("1970-01-01"))

[1] "Date"

# You can convert the Date object into a number
class(as.numeric(as.Date("1970-01-01")))

[1] "numeric"

Note that if the format of the input object is different from the ISO 8601 standard, using the as.Date function without declaring the object format structure would return incorrect results (and, in some instances, an error). For example, let’s try to convert the 31-01-2018 date to Date format without reformatting it:

Example:

as.Date("31-01-2018")

[1] "0031-01-20"

One way to solve this issue is by adding the format argument in the as.Date function in order to map the different components of the input object to the structure of the Date object. The mapping should be according to the date components order of the input object:

as.Date("31-01-2018", format = "%d-%m-%Y")

[1] "2018-01-31"

You can find a summary of the main arguments for date objects using ?strptime.

The as.POSIXct or as.POSIXlt function works in a similar way:

(time_ct <- as.POSIXct("2014-5-12 20:05:35", tz = "EST"))

[1] "2014-05-12 20:05:35 EST"

class(time_ct)

[1] "POSIXct" "POSIXt" 

11.3 Reformatting and Converting Date Objects

Reformatting or converting date objects is the process of transforming a non-date (or POSIXct/lt) object such as character or numeric to a Date format (or POSIXct/lt).

Example:

url <- "https://github.com/PacktPublishing/Hands-On-Time-Series-Analysis-with-R/raw/refs/heads/master/Chapter02/dates_formats.csv"
dates_df <- read.csv(url, stringsAsFactors = FALSE)
str(dates_df)

'data.frame':   22 obs. of  7 variables:
 $ Japanese_format     : chr  "2017/1/20" "2017/1/21" "2017/1/22" "2017/1/23" ...
 $ US_format           : chr  "1/20/2017" "1/21/2017" "1/22/2017" "1/23/2017" ...
 $ US_long_format      : chr  "Friday, January 20, 2017" "Saturday, January 21, 2017" "Sunday, January 22, 2017" "Monday, January 23, 2017" ...
 $ CA_mix_format       : chr  "January 20, 2017" "January 21, 2017" "January 22, 2017" "January 23, 2017" ...
 $ SA_mix_format       : chr  "20 January 2017" "21 January 2017" "22 January 2017" "23 January 2017" ...
 $ NZ_format           : chr  "20/01/2017" "21/01/2017" "22/01/2017" "23/01/2017" ...
 $ Excel_Numeric_Format: int  42755 42756 42757 42758 42759 42760 42761 42762 42763 42764 ...

The first six columns are character objects and the seventh object is numeric. Let’s convert each one of the columns to a date object by identifying the date structure and reformat it accordingly.

# Convert 1st column to Date format (ISO 8601 format)
dates_df$Japanese_format_new <- as.Date(dates_df$Japanese_format)
class(dates_df$Japanese_format)

[1] "character"

class(dates_df$Japanese_format_new)

[1] "Date"

# Convert the 2nd column (no ISO 8601 format)
dates_df$US_format_new <- as.Date(dates_df$US_format, format = "%m/%d/%Y")

The same logic applies to the remaining columns:

# Convert the 3rd column
dates_df$US_long_format_new <- as.Date(dates_df$US_long_format, format = "%A, %B %d, %Y")
# Convert the 4th column
dates_df$CA_mix_format_new <- as.Date(dates_df$CA_mix_format, format = "%B %d, %Y")
# Convert the 5th column
dates_df$SA_mix_format_new <- as.Date(dates_df$SA_mix_format, format = "%d %B %Y")
# Convert the 6th column
dates_df$NZ_format_new <- as.Date(dates_df$NZ_format, format = "%d/%m/%Y")

In Excel, the numeric value of the origin point (that is January 1st, 1900) set to 1, as opposed to other programming languages such as R which define the origin point as 0. Therefore, when importing a date or time objects from Excel, in order to align to R origin point definition, the origin point should be set as December 31st, 1899 (which equivalent to 0 numeric value). Therefore, since Excel is using a different origin point than R (December 30, 1899 versus January 1, 1970), we will have to add the origin argument and specify the original date that is used to generate the data:

Example:

head(dates_df$Excel_Numeric_Format)

[1] 42755 42756 42757 42758 42759 42760

dates_df$Excel_Numeric_Format_new <- as.Date(dates_df$Excel_Numeric_Format, origin = as.Date("1899-12-30"))
head(dates_df$Excel_Numeric_Format_new)

[1] "2017-01-20" "2017-01-21" "2017-01-22" "2017-01-23" "2017-01-24"
[6] "2017-01-25"

11.4 Reformatting and Converting Time Objects

Similar to the as.Date function, the as.POSIXct or as.POSIXlt functions are the base package applications for reformatting and conversion of any time input to a POSIXct or POSIXlt objects, respectively.

The POSIX classes are an extension of the Date class, with the addition of four elements (in addition to the date elements): hours, minutes, seconds, and time zone. The mapping will now include seven elements instead of four elements, as was the case with Date class.

# Input follows the ISO 8601 standard
time_str <- "2018-12-31 23:59:59"
class(time_str)

[1] "character"

time_posix_ct1 <- as.POSIXct(time_str)
class(time_posix_ct1)

[1] "POSIXct" "POSIXt" 

Let’s convert now the number 1546318799 to a time object using the as.POSIXct function with the origin argument:

Example:

time_numeric <- 1546318799
class(time_numeric)

[1] "numeric"

time_posix_ct2 <- as.POSIXct(time_numeric, origin = "1970-01-01")
print(c(time_posix_ct1, time_posix_ct2))

[1] "2018-12-31 23:59:59 CET" "2019-01-01 05:59:59 CET"

However, whenever the format of the input object does not follow a YYYY-m-d H:M:S structure, you will have to use the format argument to map the object’s elements.

Example:

# Monday, December 31, 2018 11:59:59 PM.
time_US_str <- "Monday, December 31, 2018 11:59:59 PM"
(time_posix_ct3 <- as.POSIXct(time_US_str, format = "%A, %B %d, %Y %I:%M:%S %p"))

[1] NA

The full arguments list can also be found using ?strptime.

11.5 Time Zone Setting

The time zone is the seventh element of the POSIX classes and can be set by either the tz argument of the as.POSIXct/as.POSIXlt functions or by the format argument.

Example:

Sys.timezone()

[1] "Europe/Madrid"

time_str <- "2024-09-30 11:25:49"
time_default_tz <- as.POSIXct(time_str)

# Load a date object from different time zone
time_assign_tz <- as.POSIXct(time_str, tz = "GMT")

print(c(time_default_tz, time_assign_tz))

[1] "2024-09-30 11:25:49 CEST" "2024-09-30 13:25:49 CEST"

A full list of the 592 time zones available in R (both location and abbreviation formats) can be found in the OlsonNames function:

head(OlsonNames(), 20)

 [1] "Africa/Abidjan"       "Africa/Accra"         "Africa/Addis_Ababa"  
 [4] "Africa/Algiers"       "Africa/Asmara"        "Africa/Asmera"       
 [7] "Africa/Bamako"        "Africa/Bangui"        "Africa/Banjul"       
[10] "Africa/Bissau"        "Africa/Blantyre"      "Africa/Brazzaville"  
[13] "Africa/Bujumbura"     "Africa/Cairo"         "Africa/Casablanca"   
[16] "Africa/Ceuta"         "Africa/Conakry"       "Africa/Dakar"        
[19] "Africa/Dar_es_Salaam" "Africa/Djibouti"     

11.6 Creating a Date or Time Index Vector

The base package provides two pairs of functions, seq.Date and seq.POSIXt, to create a time index vector with Date or POSIX objects respectively. The main difference between the two functions (besides the class of the output) is the units of the time interval.

It will make sense to use the seq.Date function to generate a time sequence with daily frequency or lower (for example, weekly, monthly, and so on) and as.POSIXt in other instances (for higher frequencies than daily, such as hourly, half-hourly, or by minutes).

daily_index <- seq.Date(from = as.Date("2016-01-01"),
                        to = as.Date("2018-12-31"), 
                        by = "day")
head(daily_index)

[1] "2016-01-01" "2016-01-02" "2016-01-03" "2016-01-04" "2016-01-05"
[6] "2016-01-06"

daily_3_index <- seq.Date(from = as.Date("2016-01-01"),
                          to = as.Date("2018-12-31"),
                          by = "3 days")
head(daily_3_index)

[1] "2016-01-01" "2016-01-04" "2016-01-07" "2016-01-10" "2016-01-13"
[6] "2016-01-16"

For example, let’s create an hourly sequence with a length of 48 hours, using the seq.POSIXt function:

hourly_index <- seq.POSIXt(from = as.POSIXct("2018-06-01"), 
                           by = "hours",
                           length.out = 48)
str(hourly_index)

 POSIXct[1:48], format: "2018-06-01 00:00:00" "2018-06-01 01:00:00" "2018-06-01 02:00:00" ...

11.7 lubridate Package

The lubridate package offers alternative tools and applications for reformatting, converting, and handling date and time objects.

11.7.1 Reformatting date and time objects

To see how simple it is to reformat date and time objects with the lubridate package,let’s go back to the complex time object (Monday, December 31, 2018 11:59:59 PM) we converted earlier to a POSIXct class:

Example:

time_US_str <- "Monday, December 31, 2018 11:59:59 PM"
class(time_US_str)

[1] "character"

Now let’s use the ymd_hms (which stands for a year, month, day, hour, minute, and second) family of conversion functions from the lubridate package to convert the object to a POSIXct object:

Example:

library(lubridate)
time_lubridate <- mdy_hms(time_US_str, tz = "EST")
class(time_lubridate)

[1] "POSIXct" "POSIXt" 

time_lubridate

[1] "2018-12-31 23:59:59 EST"

This is much simpler than the as.POSIXct conversion. The ymd_hms function is able to automatically map the different time components of the input object, even when some components are redundant (such as the full weekday name in the example previously).

The conversion with ymd functions of the previous date_df example (https://github.com/PacktPublishing/Hands-On-Time-Series-Analysis-with-R/raw/refs/heads/master/Chapter02/dates_formats.csv) is as follows:

Example:

dates_df$Japanese_format_new <- ymd(dates_df$Japanese_format)
dates_df$US_format_new <- mdy(dates_df$US_format)
dates_df$US_long_format_new <- mdy(dates_df$US_long_format)
dates_df$CA_mix_format_new <- mdy(dates_df$CA_mix_format)
dates_df$SA_mix_format_new <- dmy(dates_df$SA_mix_format)
dates_df$NZ_format_new <- dmy(dates_df$NZ_format)

The ymd function easily handles the different types of date formats; however, it is not designed to convert numeric values of date objects. This type of conversion can be done with the as_date function for date objects (or as_datetime for time objects), which works in the same manner as as.Date (or the as.POSIXct/as.POSIXlt functions) works with date numeric values:

Example:

dates_df$Excel_Numeric_Format_new <- as_date(dates_df$Excel_Numeric_Format,
        origin = ymd("1899-12-30"))

Note that you will get the same results with the ymd and the as_date functions as the results with as.Date.

However, those functions (both ymd and ymd_hms) are not able to handle rare or extreme cases where one of the separators is a double string (such as a double apostrophe, ““) or some of the formats are not written well in a specific format of year, month, and day, such as 201811-01, 201811-1, or 111-2018 (as opposed to 2018-11-01 or 01-11-2018).

11.7.2 Extraction functionality

• This extracts the day of the year:

  yday(time_obj)

• This extracts the day of the quarter:

  qday(time_obj)

• This extracts the day of the month:

  day(time_obj)

• This extracts the day of the week:

  wday(time_obj, label = TRUE)

11.7.3 Modification functionality

• This modifies the hour of the time object:

  hour(time_obj) <- 11

• This rounds the time object by the minute, hour, and day:

  # Origin: 2018-12-31 23:59:59 CET
  
  round_date(as.POSIXct("2018-12-31 23:59:59 CET"), unit = "minute")

  [1] "2019-01-01 CET"

  floor_date(as.POSIXct("2018-12-31 23:59:59 CET"), unit = "hour")

  [1] "2018-12-31 23:00:00 CET"

  ceiling_date(as.POSIXct("2018-12-31 23:59:59 CET"), unit = "day")

  [1] "2019-01-01 CET"

Those sets of functions are very useful when extracting mainly time objects from a different type of format.