1 Goal

The goal of this tutorial is to read dates in the POSIXct format. In addition we will learn how to create labels for different uses.

2 Read the Data

# First we load the libraries
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
# In this tutorial we are going to use Date and time data
Time_table <- read.csv("timetable.csv", stringsAsFactors = FALSE)
str(Time_table)
## 'data.frame':    34584 obs. of  2 variables:
##  $ Date: chr  "16/12/06" "16/12/06" "16/12/06" "16/12/06" ...
##  $ Time: chr  "18:00:00" "19:00:00" "20:00:00" "21:00:00" ...
# First we are going to create a new column containing the full date_time
Time_table <- mutate(Time_table, DateTime = paste(Date, Time))
str(Time_table)
## 'data.frame':    34584 obs. of  3 variables:
##  $ Date    : chr  "16/12/06" "16/12/06" "16/12/06" "16/12/06" ...
##  $ Time    : chr  "18:00:00" "19:00:00" "20:00:00" "21:00:00" ...
##  $ DateTime: chr  "16/12/06 18:00:00" "16/12/06 19:00:00" "16/12/06 20:00:00" "16/12/06 21:00:00" ...
# Now we transform from text to date format variable
Time_table$DateTime <- strptime(Time_table$DateTime, "%d/%m/%y %H:%M:%S")
str(Time_table$DateTime)
##  POSIXlt[1:34584], format: "2006-12-16 18:00:00" "2006-12-16 19:00:00" ...
# Finally we transform the time to POSIXct 
Time_table$DateTime <- as.POSIXct(Time_table$DateTime)
str(Time_table)
## 'data.frame':    34584 obs. of  3 variables:
##  $ Date    : chr  "16/12/06" "16/12/06" "16/12/06" "16/12/06" ...
##  $ Time    : chr  "18:00:00" "19:00:00" "20:00:00" "21:00:00" ...
##  $ DateTime: POSIXct, format: "2006-12-16 18:00:00" "2006-12-16 19:00:00" ...

3 Time tags using format function

3.1 Time tag by month

# We can create time tags to filter by month, year, week, etc. 
# Instead of working with lubridate, we are going to format the date in the way we want

Time_table <- mutate(Time_table, month_tag = format(Time_table$DateTime, "%m/%Y"))
unique(Time_table$month_tag)
##  [1] "12/2006" "01/2007" "02/2007" "03/2007" "04/2007" "05/2007" "06/2007"
##  [8] "07/2007" "08/2007" "09/2007" "10/2007" "11/2007" "12/2007" "01/2008"
## [15] "02/2008" "03/2008" "04/2008" "05/2008" "06/2008" "07/2008" "08/2008"
## [22] "09/2008" "10/2008" "11/2008" "12/2008" "01/2009" "02/2009" "03/2009"
## [29] "04/2009" "05/2009" "06/2009" "07/2009" "08/2009" "09/2009" "10/2009"
## [36] "11/2009" "12/2009" "01/2010" "02/2010" "03/2010" "04/2010" "05/2010"
## [43] "06/2010" "07/2010" "08/2010" "09/2010" "10/2010" "11/2010"
# However we could like to create an average month from all the periods we have
# In this case we would not add the year in order to mix months from different years
Time_table <- mutate(Time_table, month_tag = format(Time_table$DateTime, "%m"))
unique(Time_table$month_tag)
##  [1] "12" "01" "02" "03" "04" "05" "06" "07" "08" "09" "10" "11"

3.2 Time tag by year

# We can create a time tag for every year

Time_table <- mutate(Time_table, year_tag = format(Time_table$DateTime, "%Y"))
unique(Time_table$year_tag)
## [1] "2006" "2007" "2008" "2009" "2010"

3.3 Time tag by day

# We can create a time tags for every day of every year

Time_table <- mutate(Time_table, day_tag = format(Time_table$DateTime, "%d/%m/%Y"))
head(unique(Time_table$day_tag))
## [1] "16/12/2006" "17/12/2006" "18/12/2006" "19/12/2006" "20/12/2006"
## [6] "21/12/2006"
# Or we can create 365 tags and create a standard day of the month

Time_table <- mutate(Time_table, day_tag = format(Time_table$DateTime, "%d"))
head(unique(Time_table$day_tag))
## [1] "16" "17" "18" "19" "20" "21"

4 Time tags using the lubridate library

# Lubridate is a library that allows us to work with dates and times in a very effective way
# Now we will be able to create more precise tags
#First we load the library
library(lubridate)
## 
## Attaching package: 'lubridate'
## The following object is masked from 'package:base':
## 
##     date

4.1 Time tag by week day

# We can ask the day of the week of a certain date and create tags for monday, tuesday, etc
# Remember that weeks start on sunday in America: 01 == Sunday, 02 == Monday, etc
# This can allow us to filter by work day or weekend

Time_table <- mutate(Time_table, week_day = format(wday(Time_table$DateTime)))
unique(Time_table$week_day)
## [1] "7" "1" "2" "3" "4" "5" "6"

4.2 Other time filters

# We can use several functions from lubridate to filter the date
unique(year(Time_table$DateTime))
## [1] 2006 2007 2008 2009 2010
unique(month(Time_table$DateTime))
##  [1] 12  1  2  3  4  5  6  7  8  9 10 11
unique(day(Time_table$DateTime))
##  [1] 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31  1  2  3  4  5  6  7
## [24]  8  9 10 11 12 13 14 15
unique(hour(Time_table$DateTime))
##  [1] 18 19 20 21 22 23  0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16
## [24] 17
#unique(minute(Time_table$DateTime))
#unique(second(Time_table$DateTime))

5 Conclusion

In this tutorial we have learnt how to read dates and times and format them in the best way. In addition we have learnt how to create tags to aggregate or group_by depending on our specific needs.