A Case for ALL CAPS

A review of the data

This data was downloaded from the Washington State Department of Social and Health Services website filtered for Nursing facilities in King County. When the search results are returned by the website, it is sorted by facility name as one would expect, where the case does not matter, though it would be nice if the names could have been consistently formatted and makes me wonder about how the data gets to the state. Does one data entry person enter names in ALL CAPS or do the providers enter their names in ALL CAPS? All other data fields across facilities appears “properly” Title Cased, even Admin names.

library(here)
library(knitr)
library(tidyverse)

data <- read_csv(here("static", "data", "NFListing.csv")) %>%
  select(nf_loc_city,nf_name) %>%
  rename(city = nf_loc_city, name = nf_name) 

#TeamTidyverse > Take 1

In the #Tidyverse, one can arrange data by multiple fields and see if the case matters. The first set of mixed case city and names occurs for Federal Way. I am including all 51 rows of data to show all wrinkles in sorting for Redmond, Renton and Seattle facilities.

data %>%
  arrange(city, name) %>%
  kable()

Observation: Title Case city names precede ALL CAPS city names per AVALON CARE CENTER - FEDERAL WAY, LLC in FEDERAL WAY. Also true for REDMOND CARE AND REHABILITATION CENTER in REDMOND.

Data.Table > Take 1

My colleague who developed the allocation algorithm is on #TeamData.Table for zippy processing. One way to order data in a data.table is through setorder.

library(data.table)
dt_data  <- fread(here("static", "data", "NFListing.csv"))
da_table <- dt_data[,.(city = nf_loc_city, name = nf_name)]
setorder(da_table, city, name)
da_table %>%
  kable()

Observation: ALL CAPS city names precede Title Case city names as seen for Seattle facilities. Things get even weirder for Redmand and Renton where even the cities aren’t grouped in order.

What to do?

As a consultant, when discussing data quality, I would advocate for non-shouty data if source system flexibility makes that possible and doesn’t impact prior data reporting. But sometimes when you are downstream from data, you may find yourself getting inconsistently formatted data and you have to make the decision on how much you care about maddening cases and determine if you even have the time to invest in attempting to format names, which is often a tricky business.

#Tidyverse > Take 2

Rather than tackle facility names which may have initials and acronyms in ALL CAPS, formatting the city names will at least reveal how dplyr::arrange sorts data using city_formmatted in place of city.

library(stringr)
data %>%
  mutate(city_formatted = str_to_title(city)) %>%
  arrange(city_formatted, name) %>%
  select(city, city_formatted, name) %>%
  kable()

Verdict: WINNER

With my city data now formatted as Title Case, the secondary sorting of names is returned as hoped for both Federal Way, Redmond and Seattle facilities.

Data.Table > Take 2

The next test is to see how using the formatted city name impacts sort of facilities by setorder.

da_table <- dt_data[,.(city = nf_loc_city, name = nf_name)]
da_table[,city_formatted := str_to_title(city)]

setorder(da_table, city_formatted, name)
da_table[,.(city, city_formatted, name)] %>%
  kable()

Verdict: BETTER

Secondary sort on mixed case names results in facility names starting with a given letter in ALL CAPS preceding other Title Cased names of same letter. The data looks pretty good until seeing where Ballad Center and Park West Care Center show up under Seattle facilities.

Data.Table > Take 3

When reading all of the documentation, it notes that -

data.table always reorders in “C-locale”. As a consequence, the ordering may be different to that obtained by base::order.

For final test, will try base::order on the data.table and sort by city_formatted and name.

da_table <- dt_data[,.(city = nf_loc_city, name = nf_name)]
da_table[,city_formatted := str_to_title(city)]

da_table[base::order(city_formatted, name),.(city, city_formatted, name)] %>%
  kable

Verdict:: WINNER

Specifying base::order makes the data.table sorting experience consistent with dplyr and more importantly, sort more human-sensibly in spite of case differences across facility names.

A Sorted Affair

As often is the case, thinking that a simple task to sort data won’t take more than 5 minutes can quickly lead one down a rabbit hole when data is inconsistent. Standardizing data formats for character values would not only be easier on human eyes, but also result in more expected sorting behavior.