Section 6.2 Temporal data wrangling
Temporal data means that we can perform all sorts of exciting operations in our data wrangling processes. Just how we learned about spatial operations with spatial data, there are some things we can do only with temporal data. In this section we will introduce some of these.
A key R package that will help with temporal data wrangling is
lubridate. Date-time data can be frustrating to work with and many base R commands for date-times can be unintuitive. The package lubridate was created to address such issues, and make it easier to do the things R does with date-times.
For crime data, we will be using crime data from New York City made available by the R package
crimedata ([Ashby, 2019]). You can refer to these citations to learn more about acquiring data from this fantastic resource. However for now, let’s make use of the data provided with the book, where we selected a specific subset of aggravated assault in New York for a period of five years.
agassault_ny <- read_csv("data/agassault.csv")
When you read the data into R, you will see that there is a column for date called date_single. Let’s have a look at the first value in this column:
agassault_ny %>% dplyr::select(date_single) %>% head(1)
## # A tibble: 1 × 1 ## date_single ## <dttm> ## 1 2014-01-01 00:03:00
We can see that the date is stored in the following format: year-month-day hour-minute-second. So the first date on there you can see is 2014-01-01 00:03:00. What kind of variable is this?
class(agassault_ny$date_single)
## [1] "POSIXct" "POSIXt"
Our date and time variables are of class
POSIXct and POSIXt. These are the two basic classes of date/times. Class "POSIXct" represents the (signed) number of seconds since the beginning of 1970 as a numeric vector. Class "POSIXt" is a named list of vectors representing seconds (0–61), minutes (0–59), hours (0–23), day of the month (1–31), months after the first of the year (0–11), years since 1900, day of the week, starting on Sunday (0–6), and a flag for whether it is daylight savings time or not (positive if in force, zero if not, negative if unknown). Let’s plot this data:
ggplot(agassault_ny, aes(date_single)) +
geom_freqpoly(binwidth = 7*24*60*60) # 7 days in seconds
Notice what
geom_freqpoly() is doing. We have a dataframe with rows for each case. The data is not aggregated in any form. But this function counts on the fly the number of cases (rows) for each of the bins as we define them. It is, thus, a convenient function that saves us from having to first do that aggregation ourselves when we want to plot it.
An alternative approach to plotting individual components is to round the date to a nearby unit of time, with
floor_date(), round_date(), and ceiling_date(). These functions live inside the lubridate package. Each function takes a vector of dates to adjust and then the name of the unit round down (floor), round up (ceiling), or round to. So to aggregate per month, we will code as:
agassault_ny %>%
count(month = floor_date(date_single, "month")) %>% # use floor date function
ggplot(aes(month, n)) +
geom_line()
What if we ask the question: which year had the most aggravated assaults? Or what if we want to know whether aggravated assaults happen more in the weekday, when people are at work, or in the weekends, maybe when people are away for a holiday? You have the date, so you should be able to answer these questions, right?
Well you need to be able to have the right variables to answer these questions. To know what year saw the most aggravated assaults, you need to have a variable for year. To know what day of the week has the most aggravated assaults, you need to have a variable for day of the week. So how can we extract these variables from your date column? Well, luckily the
lubridate package can help us do this. We can use the year(), month(), day(), and wday() to extract these components of a date-time.
agassault_ny <- agassault_ny %>%
mutate(year = year(date_single), # extract year
month = month(date_single, label = TRUE, abbr=FALSE), # month
day = day(date_single), # extract day
wday = wday(date_single, label = TRUE, abbr=FALSE)) # extract day of week
We have now created a set of additional variables that have extracted information from your original time of occurrence variable. Let’s consider distribution of events per day of the week.
ggplot(agassault_ny, aes(x = wday)) + geom_bar()
In order to extract such date-time information from variables, we need these to be date-time objects. We saw above that in this case this assumption was met. However if it is not, you can turn text column of dates into a date-time object using
lubridate’s functions. For example if you have a data set of day-month-year separated with "/", you can use the dmy() function (stands for day, month, year) to parse it as a date-time object. On the other hand, if you have some US data, and it is actually written as month-day-year, separated by "/", you can simply shuffle the order of the letters in the function, and use the function mdy(). They will translate into the same item. See for yourself:
dmy("15/3/2021") == mdy("3/15/2021")
## [1] TRUE
In fact,
lubridate is so good, it can even parse text representations of months. Look at this for example:
dmy("15/3/2021") == mdy("March/15/2021")
## [1] TRUE
This is amazing stuff, which will definitely come in handy, especially if you might be a crime analyst working with some messy data. The
lubridate package should most definitely form part of your data wrangling toolkit in this case.
