Dynamically Updating Date Labels in App

This post, my first in over year, improves upon the code in my last post. The crime-mapping app I posted in May 2017 included some kludgy, manual handling of dates, such that the categorical, rolling 12-month year labels were hard-coded, rather than dynamically updated with the data’s most recent date. This post presents a much smoother way to automatically assign those date labels, which obviates the need for a user to correctly, manually update that field when updating the data. 

To produce this post, I also updated the data for the app itself, so any blog readers who are more interested in the app itself than the coding will find an updated resource! Lastly, in response to user feedback about the app itself, I slightly modified the text descriptions of crime incidents – they now include the address of the incident.

The changes to the date code are all almost all contained in “Step 3. Categorical variable for rolling, 12-month year”. (Steps 1-2 and 5 remain unchanged and can be found in the previous post or in the GitHub code for this post.)

# 3. Categorical variable for rolling, 12-month year 
data$offense_date <- as.Date(data$offense_date)

First, ensure that the offense_date field is recognized as a date – we’ll need that formatting to extract the year() and month(), as well as subtracting varying numbers of years to obtain a year-over-year look at the data.

Now, check the date range for the data. The most recent date will probably be pretty recent, as this data is – delightfully – well-maintained and updated regularly. Should the diligent soul(s) responsible for this feat ever stumble upon my blog, please know that your hard work is appreciated. 

The goal with this code revision is to automatically generate text descriptions for the preceding, rolling, 12-month years, but the challenge is that unless a user is pulling this data in January of any given year, there will be one or more months of data in 2011 left over. We want to make sure these early 2011 months are labeled appropriately, so app users recognize that the lower number of incidents in those months are a function of the truncated date range.

We can do this by iterating backwards through the date range, creating the text labels as we go, but using different logic for the first year in the data (the last year in the iterative loop as we step backwards through the years).

# date range
range(raw.data$offense_date, na.rm=TRUE)
min.date <- range(raw.data$offense_date, na.rm=TRUE)[1]
max.date <- range(raw.data$offense_date, na.rm=TRUE)[2]

data$year_text <- NA
for (i in 0:(year(max.date)-year(min.date))) {

     # For each rolling, 12-month year, pull the two years that bookend the date range:
     this.year <- max.date-years(i)
     last.year <- max.date-years(i+1) 
     last.year <- last.year + days(1) # +1 day so the days defining the boundary between rolling, 12-month years are not counted twice

     # text labels defining the month and year with which the rolling, 12-month year began, 
     # but use different logic for the first year in the data:
     if (year(this.year) > year(min.date)) {
          text <- paste(month(last.year), day(last.year), sep="/")
          text <- paste(text, year(last.year), sep="/")
     } else if (year(this.year)==year(min.date)) {
          text <- paste(month(min.date), day(min.date), sep = "/")
          text <- paste(text, year(min.date), sep = "/")
     # Add text to the label indicating the month and year in which the rolling, 12-month year ended:
      text <- paste(text, as.character(month(max.date)), sep=" - ")
     text <- paste(text, day(this.year), sep="/")
     text <- paste(text, year(this.year), sep="/")
     # Assign individual crime incidents to their corresponding rolling, 12-month years.
     data$year_text[data$offense_date > max.date-years(i+1) & data$offense_date <= max.date-years(i)] <- text
     remove(this.year, last.year, text)
remove(min.date, max.date, i)

The following check confirms that the date ranges for the rolling, 12-month years are as expected.

# Check that the labels were assigned correctly
data %>%
     group_by(year_text) %>%
     summarize(numberOfRecords = n(),
               min = min(offense_date, na.rm=TRUE),
               max = max(offense_date, na.rm=TRUE))

In response to user feedback regarding easily translating the visual map results to specific buildings, make a simple adjustment to the crime incident labels to include the address where the incident took place:

# 4. Crime incident labels
data$longer_desc <- paste(data$geolocation_address, data$offense_desc, sep=" - ")
data$longer_desc <- paste(data$longer_desc, data$offense_date, sep=" ")

Lastly, add some additional code at the beginning of the app.R file, so the launched application dynamically selects the most recent, rolling, 12-month year of data for geo mapping.  Remember, the app.R file needs to be a distinct, self-contained file, unless you want all of the data prep to be performed every time a user uses the app.

# Select year text for most recent year
most.recent <- max(data$offense_date, na.rm=TRUE)
most.recent <- subset(data, offense_date == most.recent, select = "year_text")
most.recent <- most.recent$year_text[1]

# Subset data for past year
annual.data <- subset(data, year_text == most.recent)

# Remove observations with missing geocoordinates
annual.data <- subset(annual.data, !is.na(annual.data$latitude))
date.range <- range(annual.data$offense_date, na.rm=TRUE) # date range
date.header <- paste("Date Range = Rolling, 12-month year from", date.range[1], sep=" ")
date.header <- paste(date.header, "to", sep=" ")
date.header <- paste(date.header, date.range[2], sep=" ")