Working with dates and timesAni Ruhil1 / 39

Agenda

Understanding how to work with dates and times using

base R
lubridate

2 / 39

 base R3 / 39

Start by creating some dates

as.Date("1970-1-1") -> basedate 
Sys.Date() -> today 
as.Date("2017-12-18") -> tomorrow 
"12/16/2017" -> yesterday1 
"12-16-17" -> yesterday2 
"12 16 17" -> yesterday3

How did R read these?

str(list(basedate, today, tomorrow, yesterday1, yesterday2, yesterday3))

## List of 6
##  $ : Date[1:1], format: "1970-01-01"
##  $ : Date[1:1], format: "2020-01-01"
##  $ : Date[1:1], format: "2017-12-18"
##  $ : chr "12/16/2017"
##  $ : chr "12-16-17"
##  $ : chr "12 16 17"

Some are read-in as dates but some are read-in as characters (chr)

4 / 39

We can flip the characters into dates so long as we specify the format that characterizes the month, day, and year in each chr()

as.Date(yesterday1, format = "%m/%d/%Y") -> yesterday1d 
as.Date(yesterday2, format = "%m-%d-%y") -> yesterday2d 
as.Date(yesterday3, format = "%m %d %y") -> yesterday3d 
yesterday1d; yesterday2d; yesterday3d

## [1] "2017-12-16"

## [1] "2017-12-16"

## [1] "2017-12-16"

5 / 39

There are special code switches that indicate date components when you format = ""

Code	Code represents ...	For example ...
%a	Day spelled out (abbreviated)	Mon
%A	Day spelled out	Monday
%d	Day of the month	16
%m	Month	12
%b	Month (abbreviated)	Dec
%B	Month (fully spelled out)	December
%y	Year (2-digits)	17
%Y	Year (4-digits)	2017

6 / 39

So if you ever run into a date field that needs formatting, what we have covered thus far should help you convert it into a proper date formatted variable. Of course, there will always be special cases that need more work.

Once you convert them to the date format, you can extract other quantities.

weekdays(yesterday1d)

## [1] "Saturday"

months(today)

## [1] "January"

quarters(today)

## [1] "Q1"

7 / 39

Sometimes you will have a number representing the date. This number represents the number of days since/before some date of origin. If the number you see is positive, then you are seeing the number of days since the data of origin. If the number is negativem, then you are seeing the number of days before the data of origin. R uses 1970-01-01 but other software packages may (and some do) use different days. Thus, for example, if the dates are listed as

c(17651, -2345, 19760) -> x

Then these numbers represent the following dates:

as.Date(x, origin = "1970-01-01") -> x.dates 
x.dates

## [1] "2018-04-30" "1963-08-01" "2024-02-07"

You can also convert dates into the numbers representing dates.

julian(x.dates, origin = as.POSIXct("1970-01-01", tz = "UCT") ) -> x.julian 
x.julian

## [1] 17651 -2345 19760
## attr(,"origin")
## [1] "1970-01-01 UTC"

Julian?

8 / 39

What if I wanted to create a sequence of dates, starting with Feb 8, 2018 but increasing by 1 day at a time or by every 5th day?

as.Date(as.character("2018-02-08"), format = "%Y-%m-%d") -> start.date 
seq(start.date, by = 1, length.out = 7) -> date.seq1 
date.seq1

## [1] "2018-02-08" "2018-02-09" "2018-02-10" "2018-02-11" "2018-02-12" "2018-02-13"
## [7] "2018-02-14"

seq(start.date, by = 5, length.out = 3) -> date.seq5 
date.seq5

## [1] "2018-02-08" "2018-02-13" "2018-02-18"

Note that length.out = is specifying how many you dates want to create.

9 / 39

Now, what if I want to know the date 30 days from today? 19 days ago?

today + 30 -> date.30 
today - 19 -> date.19

How many days lapsed between two dates?

as.Date("2017-04-28") -> date1 
Sys.Date() -> date2 
date2 - date1 -> lapsed.time; lapsed.time

## Time difference of 978 days

Say I want to create a vector of dates that starts and ends on specific dates, and the step function is 1 day, a week, 4 months, etc. The step is indicated with the by = "" command

seq(from = as.Date("2017-12-17"), to = as.Date("2018-12-16"), by = "day") -> my.dates1  
seq(from = as.Date("2017-12-17"), to = as.Date("2018-12-16"), by = "week") -> my.dates2 
seq(from = as.Date("2017-12-17"), to = as.Date("2018-12-16"), by = "month") -> my.dates3  
seq(from = as.Date("2017-12-17"), to = as.Date("2018-12-16"), by = "3 days") -> my.dates4  
seq(from = as.Date("2017-12-17"), to = as.Date("2018-12-16"), by = "2 weeks") -> my.dates5   
seq(from = as.Date("2017-12-17"), to = as.Date("2018-12-16"), by = "4 months") -> my.dates6  
seq(from = as.Date("2017-12-17"), to = as.Date("2019-12-16"), by = "year") -> my.dates7  
seq(from = as.Date("2017-12-17"), to = as.Date("2022-12-16"), by = "2 years") -> my.dates8

10 / 39

lubridate

11 / 39

This package makes working with dates and times quite easy because it does what base R does but in more intuitive ways and perhaps more flexibly. Let us start with some date fields.

today1 = "20171217"
today2 = "2017-12-17"
today3 = "2017 December 17"
today4 = "20171217143241"
today5 = "2017 December 17 14:32:41"
today6 = "December 17 2017 14:32:41"
today7 = "17-Dec, 2017 14:32:41"

The formats are quite varied but lubridate deals with them quite seamlessly so long as you pay attention to the order -- is year first or last? What about month? day? Is time given in hours, minutes and seconds?.

12 / 39

library(lubridate)
ymd(today1)

## [1] "2017-12-17"
ymd(today2)

## [1] "2017-12-17"
ymd(today3)

## [1] "2017-12-17"
ymd_hms(today4)

## [1] "2017-12-17 14:32:41 UTC"
ymd_hms(today5)

## [1] "2017-12-17 14:32:41 UTC"
mdy_hms(today6)

## [1] "2017-12-17 14:32:41 UTC"
dmy_hms(today7)

## [1] "2017-12-17 14:32:41 UTC"
13 / 39

If I need to extract data/time elements, I can do that as well.

dmy_hms(today7) -> today; today # a date and time set in today7

## [1] "2017-12-17 14:32:41 UTC"

year(today) -> today.y; today.y # The year

## [1] 2017

month(today) -> today.m1; today.m1 # the month, as a number

## [1] 12

month(today, label = TRUE, abbr = TRUE) -> today.m2; today.m2 # labeling the month but with an abbreviation

## [1] Dec
## Levels: Jan < Feb < Mar < Apr < May < Jun < Jul < Aug < Sep < Oct < Nov < Dec

month(today, label = TRUE, abbr = FALSE) -> today.m3; today.m3# fully labelling the month

## [1] December
## 12 Levels: January < February < March < April < May < June < July < ... < December

14 / 39

week(today) -> today.w 
today.w # what week of the year is it?

## [1] 51
yday(today) -> today.doy 
today.doy # what day of the year is it?

## [1] 351
mday(today) -> today.dom 
today.dom # what day of the month is it?

## [1] 17
wday(today) -> today.dow1 
today.dow1 # what day of the week is it, as a number?

## [1] 1
wday(today, label = TRUE, abbr = TRUE) -> today.dow2 
today.dow2 #  day of the week, abbreviated label

## [1] Sun
## Levels: Sun < Mon < Tue < Wed < Thu < Fri < Sat
wday(today, label = TRUE, abbr = FALSE) -> today.dow3 
today.dow3 # day of the week fully labelled

## [1] Sunday
## Levels: Sunday < Monday < Tuesday < Wednesday < Thursday < Friday < Saturday
15 / 39

hour(today) -> today.h 
today.h # what hour is it?

## [1] 14

minute(today) -> today.m 
today.m # what minute is it?

## [1] 32

second(today) -> today.s 
today.s # what second is it?

## [1] 41

tz(today) -> today.tz 
today.tz # what time zone is it?

## [1] "UTC"

16 / 39

Measuring intervals, durations, and periods of time 17 / 39

Calculating time lapsed between dates is tricky because you have to take into account daylight savings time¹, leap years², and leap seconds³. These render the length of months, weeks, days, hours, and minutes to be relative units of time but seconds tends to be exact units of time. As a result, and by design, lubridate differentiates between intervals, durations, and periods, each measuring time spans in different ways.

[1] Daylight Saving Time (DST) is the practice of setting the clocks forward 1 hour from standard time during the summer months, and back again in the fall, in order to make better use of natural daylight. Source

[2] Leap years are needed to keep our modern day Gregorian calendar in alignment with the Earth's revolutions around the sun. It takes the Earth approximately 365.242189 days – or 365 days, 5 hours, 48 minutes, and 45 seconds – to circle once around the Sun. This is called a tropical year, and is measured from the March equinox. However, the Gregorian calendar has only 365 days in a year, so if we didn't add a leap day on February 29 nearly every four years, we would lose almost six hours off our calendar every year. After only 100 years, our calendar would be off by around 24 days! Source

[3] Two components are used to determine UTC (Coordinated Universal Time): (a) International Atomic Time (TAI): A time scale that combines the output of some 200 highly precise atomic clocks worldwide, and which provides the exact speed for our clocks to tick, and (b) Universal Time (UT1), also known as Astronomical Time, which refers to the Earth's rotation around its own axis, which determines the length of a day. Before the difference between UTC and UT1 reaches 0.9 seconds, a leap second is added to UTC and to clocks worldwide. By adding an additional second to the time count, our clocks are effectively stopped for that second to give Earth the opportunity to catch up. Source.

18 / 39

Let us start with a duration, the simplest measure of lapsed time since it measures the passing of time in seconds. Say I pick two dates and times ... 05:00am on March 9, 2019 and 05:00am on March 10, 2019.

ymd_hms("2019-03-09 05:00:00", tz = "US/Eastern") -> date1 
ymd_hms("2019-03-10 05:00:00", tz = "US/Eastern") -> date2 
interval(date1, date2) -> timint 
timint

## [1] 2019-03-09 05:00:00 EST--2019-03-10 05:00:00 EDT

How much time has lapsed between date1 and date2? This we calculate with as.duration()

as.duration(timint) -> timelapsed.d 
timelapsed.d

## [1] "82800s (~23 hours)"

Why is it 23 hours and not 24 hours? Because daylight savings kicked in at 2:00 AM on March 10, and since the clocks kicked forward by one hour, only 23 hours have passed and not 24 as we might naively expect. Go back and look at the output from timeint; see the EST versus EDT? In contrast to duration, if I ask for the period of time, what do I get?

as.period(timint) -> timelapsed.p 
timelapsed.p

## [1] "1d 0H 0M 0S"

Aha! as.period() is imprecise because it tells me 1 day has passed.

19 / 39

Now, we can use the duration of time lapsed in whatever units we want, but if we want accuracy, we better work with durations, as shown below.

time_length(timelapsed.d, unit = "second")

## [1] 82800

time_length(timelapsed.d, unit = "minute")

## [1] 1380

time_length(timelapsed.d, unit = "hour")

## [1] 23

time_length(timelapsed.d, unit = "day")

## [1] 0.9583333

time_length(timelapsed.d, unit = "week")

## [1] 0.1369048

time_length(timelapsed.d, unit = "month")

## [1] 0.03150685

time_length(timelapsed.d, unit = "year")

## [1] 0.002625571

duration() is always estimated in seconds but you can change the reporting unit

20 / 39

If, unfortunately, we rely on periods, well then we have inherited a problem!

time_length(timelapsed.p, unit = "second")

## [1] 86400

time_length(timelapsed.p, unit = "minute")

## [1] 1440

time_length(timelapsed.p, unit = "hour")

## [1] 24

time_length(timelapsed.p, unit = "day")

## [1] 1

time_length(timelapsed.p, unit = "week")

## [1] 0.1428571

time_length(timelapsed.p, unit = "month")

## [1] 0.03287671

time_length(timelapsed.p, unit = "year")

## [1] 0.002739726

only the seconds are correct and nothing else

21 / 39

 Calculating & working with time in the flights data22 / 39

Let us load the CMH flights data we used earlier to learn dplyr.

load("data/cmhflights2017.RData")
library(janitor)
cmhflights2017 %>%
  clean_names() -> cmh.df

Say we had to construct a date field from year, month, and day_of_week. This could be done with unite(), as shown below.

library(tidyverse)
cmh.df %>%
  unite("fltdate", c("year", "month", "day_of_week"), sep = "-", remove = FALSE) -> cmh.df 
cmh.df %>%
  select(fltdate) %>% 
  glimpse()

## Observations: 47,787
## Variables: 1
## $ fltdate <chr> "2017-1-1", "2017-1-1", "2017-1-2", "2017-1-2", "2017-1-2", "2017-1-2",…

23 / 39

What if I wanted to see which month, and then which day of the week has the most number of flights?

cmh.df %>%
  mutate(
    monthnamed = month(fltdate, label = TRUE, abbr = TRUE),
    weekday = wday(fltdate, label = TRUE, abbr = TRUE)
    ) -> cmh.df 
table(cmh.df$monthnamed)

## 
##  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec 
## 3757 3413 4101 4123 4098 4138 4295 4279 3789 4027 3951 3816

table(cmh.df$weekday)

## 
##  Sun  Mon  Tue  Wed  Thu  Fri  Sat 
## 6920 6747 6805 6795 6756 6808 6956

24 / 39

Visualizing the same thing ...

library(hrbrthemes)
library(viridis)
cmh.df %>% 
  group_by(monthnamed) %>%
  summarise(nflights = n()) %>%
  ggplot(aes(x = monthnamed,
             y = nflights,
             fill = nflights)) +
  geom_bar(stat = "identity") +
  labs(x = "Flight Month",
       y = "Number of flights",
       fill = "") +
  theme_ipsum_rc() +
  scale_fill_viridis(option = "viridis",
                     direction = -1) + 
  theme(legend.position = "bottom")

25 / 39

What about days of the week instead of months?

cmh.df %>% 
  group_by(weekday) %>%
  summarise(nflights = n()) %>%
  ggplot(aes(x = weekday,
             y = nflights,
             fill = nflights)) +
  geom_bar(stat = "identity") +
  labs(x = "Flight Day of the Week",
       y = "Number of flights",
       fill = "") +
  theme_ipsum_rc() +
  scale_fill_viridis(option = "viridis",
                     direction = -1) + 
  theme(legend.position = "bottom")

26 / 39

Hmm, I'd like the week to start on Monday so I can better show the obvious -- most flights occur over the weekend.

cmh.df %>% 
  mutate(weekday.f = ordered(weekday,
      levels = c("Mon", "Tue", "Wed",
                 "Thu", "Fri", "Sat", "Sun"))
         ) %>%
  group_by(weekday.f) %>%
  summarise(nflights = n()) %>%
  ggplot(aes(x = weekday.f,
             y = nflights,
             fill = nflights)) +
  geom_bar(stat = "identity") +
  labs(x = "Flight Day of the Week",
       y = "Number of flights",
       fill = "") +
  theme_ipsum_rc() +
  scale_fill_viridis(option = "viridis",
                     direction = -1) + 
  theme(legend.position = "bottom")

27 / 39

What time of the day do most/least flights depart? Let us start by creating a date-time field. You will see the code below first splits dep_time into hours and minutes, then combines them with a : separating the two elements, then unites date with hourmin before flipping it into deptime stored in ymd_hm() format.

cmh.df %>%
  separate(crs_dep_time, c("hour", "minute"), sep = 2, remove = FALSE) %>%
  unite(hourmin, c("hour", "minute"), sep = ":", remove = FALSE) %>% 
  unite(depdatetime, c("fltdate", "hourmin"), sep = " ", remove = FALSE) %>%
  mutate(deptime = ymd_hm(depdatetime)) -> cmh.df 
cmh.df %>%
  select(deptime) %>%
  glimpse()

## Observations: 47,787
## Variables: 1
## $ deptime <dttm> 2017-01-01 08:00:00, 2017-01-01 07:00:00, 2017-01-02 17:50:00, 2017-01…

28 / 39

Now I want the number of flights by the minute of the hour when the flight departed. .

cmh.df %>%
  mutate(fltminute = minute(deptime)) %>%
  group_by(fltminute) %>%
  summarise(nflights = n()) %>%
  ggplot(aes(x = fltminute,
             y = nflights,
             fill = nflights)) +
  geom_bar(stat = "identity") +
  labs(x = "Flight Minute",
       y = "Number of flights",
       fill = "") +
  theme_ipsum_rc() +
  scale_fill_viridis(option = "viridis",
                     direction = -1) + 
  theme(legend.position = "bottom")

29 / 39

Quite clearly, most flights depart 25 minutes after the hour, followed by 55 minutes after the hour, then at 30 minutes after the hour, and then at the hour. Fair enough, but does this differ by the day of the week? All days are not the same!

cmh.df %>%
  mutate(weekday.f = ordered(weekday,
   levels = c("Mon", "Tue", "Wed",
              "Thu", "Fri", "Sat", "Sun"))
         ) %>%
  mutate(fltminute = minute(deptime)) %>%
  group_by(weekday.f, fltminute) %>%
  summarise(nflights = n()) %>%
  ggplot(aes(x = fltminute,
             y = nflights,
             fill = nflights)) +
  geom_bar(stat = "identity") +
  labs(x = "Flight Minute",
       y = "Number of flights",
       fill = "") +
  theme_ipsum_rc() +
  scale_fill_gradient(low = "white",
                      high = "red") + 
  theme(legend.position = "bottom") + 
  facet_wrap(~ weekday.f, ncol = 2)

30 / 39

But maybe what we really want is what departure minute is associated with higher/lower average arrival delays (in minutes). Let us explore that.

cmh.df %>%
  mutate(fltminute = minute(deptime)) %>%
  group_by(fltminute) %>%
  summarise(delay = mean(arr_delay, na.rm = TRUE),
            nflights = n()) %>%
  ggplot(aes(x = fltminute,
             y = delay,
             color = nflights)) +
  geom_line() +
  labs(x = "Flight Minute",
       y = "Mean Arrival Delay",
       color = "Number of Flights in the Mean") +
  theme_ft_rc() +
  scale_color_gradient(low = "white",
                       high = "red") + 
  theme(legend.position = "bottom")

31 / 39

Does this vary by airline?

cmh.df %>%
  separate(crs_dep_time, c("hour", "minute"),
           sep = 2, remove = FALSE) %>%
  unite(hourmin, c("hour", "minute"),
        sep= ":", remove = FALSE) %>% 
  unite(depdatetime, c("fltdate", "hourmin"),
        sep = " ", remove = FALSE) %>%
  mutate(deptime = ymd_hm(depdatetime)) %>%
  mutate(fltminute = minute(deptime)) %>%
  filter(dest == "CMH") %>% 
  group_by(fltminute, reporting_airline) %>%
  summarise(delay = mean(dep_delay, na.rm = TRUE),
            nflights = n()) %>%
  filter(nflights >= 30) %>%
  ggplot(aes(x = fltminute,
             y = delay,
             color = nflights)) +
  geom_line() +
  labs(x = "Scheduled Flight Minute",
       y = "Mean Departure Delay",
       color = "Number of Flights in the Mean") +
  theme_ft_rc() +
  scale_color_gradient(low = "white",
                       high = "red") + 
  theme(legend.position = "bottom") + 
  facet_wrap(~ reporting_airline)

32 / 39

And then here are arrival delays by airlines flying to Columbus.

cmh.df %>%
  separate(crs_dep_time, c("hour", "minute"),
           sep = 2, remove = FALSE) %>%
  unite(hourmin, c("hour", "minute"),
        sep= ":", remove = FALSE) %>% 
  unite(depdatetime, c("fltdate", "hourmin"),
        sep = " ", remove = FALSE) %>%
  mutate(deptime = ymd_hm(depdatetime)) %>%
  mutate(fltminute = minute(deptime)) %>%
  filter(dest == "CMH") %>% 
  group_by(fltminute, reporting_airline) %>%
  summarise(delay = mean(arr_delay, na.rm = TRUE),
            nflights = n()) %>%
  filter(nflights >= 30) %>%
  ggplot(aes(x = fltminute, y = delay,
             color = nflights)) +
  geom_line() +
  labs(x = "Scheduled Flight Minute",
       y = "Mean Arrival Delay",
       color = "Number of Flights in the Mean") +
  theme_ft_rc() +
  scale_color_gradient(low = "white", high = "red") + 
  theme(legend.position = "bottom") + 
  facet_wrap(~ reporting_airline)

33 / 39

One final query. Say we are curious about how often a particular aircraft flies. How might we calculate this time span?

cmh.df %>%
  filter(!is.na(tail_number)) %>% 
  group_by(tail_number) %>%
  arrange(deptime) %>%
  mutate(nflew = row_number()) %>%
  select(deptime, tail_number, nflew) %>%
  arrange(tail_number, nflew) -> cmh.df2

The preceding code has basically flagged each aircraft's flight sequence (basically when it was first seen, seen for the second time, and so on). Let us now calculate the time lapsed between each flight to/from CMH of each aircraft.

cmh.df2 %>%
  ungroup() %>%
  arrange(tail_number) %>% 
  group_by(tail_number) %>%
  mutate(tspan = interval(lag(deptime), deptime), 
    tspan.minutes = as.duration(tspan)/dminutes(1),
    tspan.hours = as.duration(tspan)/dhours(1),
    tspan.days = as.duration(tspan)/ddays(1),
    tspan.weeks = as.duration(tspan)/dweeks(1)
         ) -> cmh.df2

34 / 39

mean(cmh.df2$tspan.minutes, na.rm = TRUE)

## [1] 15834.02

median(cmh.df2$tspan.minutes, na.rm = TRUE)

## [1] 612

mean(cmh.df2$tspan.days, na.rm = TRUE)

## [1] 10.99585

median(cmh.df2$tspan.days, na.rm = TRUE)

## [1] 0.425

35 / 39

If you look at the data you realize that the overwhelming majority of aircraft are flying to/from CMH within a median span of 609 minutes (a shade over 10 hours). This might differ by airline, or does it? Sure it does!

cmh.df %>%
  arrange(tail_number, deptime) %>% 
  group_by(tail_number, reporting_airline) %>%
  select(deptime, tail_number, reporting_airline) %>% 
  mutate(tspan = interval(lag(deptime), deptime), 
         tspan.minutes = as.duration(tspan)/dminutes(1),
         tspan.days = as.duration(tspan)/ddays(1)
         ) %>% 
  group_by(reporting_airline) %>%
  summarise(md.tspan = median(tspan.minutes, na.rm = TRUE)) %>%
  arrange(md.tspan) -> cmh.ts

36 / 39

knitr::kable(cmh.ts, booktabs = TRUE, "html", 
  caption = "Median number of minutes between 
  aircraft's flight in databse", 
  col.names = c("Airline", "Median Minutes"),
  digits = c(0, 0)) %>%
  kableExtra::kable_styling("striped",
                            full_width = FALSE)

Median number of minutes between aircraft's flight in databse
Airline	Median Minutes
OO	243
EV	263
WN	585
DL	686
F9	735
UA	738
AA	765

ggplot(cmh.ts, aes(y = md.tspan,
x = fct_inorder(reporting_airline), fill = md.tspan)) +
geom_col() + coord_flip() +
scale_fill_viridis(option = "inferno", direction = 1) + 
theme_ft_rc() + theme(legend.position = "bottom") + 
labs(x = "Airline",
y = "Median Gap between Aircraft's Flight (in minutes)",
fill = "")

37 / 39

Some other learning resources

See the book chapter for more advanced work with dates and times and some practice exercises.

38 / 39

Find me at...

@aruhil
aniruhil.org
ruhil@ohio.edu

39 / 39

Help

Keyboard shortcuts

↑, ←, Pg Up, k

Go to previous slide

↓, →, Pg Dn, Space, j

Go to next slide

Home

Go to first slide

End

Go to last slide

Number + Return

Go to specific slide

b / m / f

Toggle blackout / mirrored / fullscreen mode

Clone slideshow

Toggle presenter mode

Restart the presentation timer

?, h

Toggle this help