LA Restaurant Health Inspections - Tidyverse Recipe

Tidyverse Recipe

Often a classification schema is implicitly used and displayed in a large dataset, but the classification schema itself is unavailable in an easy-to-use form. This tidyverse recipe illustrates how to extract the classification data from a large dataset, identify all unique observed values and returned those unique values in a dataframe. In effect, we will use the dplyr package to perform these basic transformations.

An Example: LA Restaurant Health Inspections

A basic example is the set of restaurant health inspections collected for all restaurants in the City of Los Angeles by the Los Angeles Environmental Health Department. The department publishes this dataset as a csv file to Kaggle. The file is large and contains 58,872 rows and 20 columns. Moreover, file is approximately 14MB in size. This file will represent our large dataset for this recipe.

Moreover, certain columns are repetitive and clearly important for underlying the scope and complexity of the inspection assessment. These include pe_description (which means program element description) and program_element_pe (a 4 digit numeric code).

Examples of the pe_description field values are:

• pe_description: RESTAURANT (0-30) SEATS MODERATE RISK
• pe_description: RESTAURANT (61-150) SEATS HIGH RISK
• pe_description: FOOD MKT RETAIL (25-1,999 SF) LOW RISK

Examples of the program_element_pe values are:

• 1631
• 1630
• 1636

We decide to select two columns of the data file: pe_description and program_element_pe to define our classification schema.

The tidyverse recipe will then seek to report all unique 2-tuples of (pe_description, program_element_pe) in the data set.

We will also repeat the recipe to seek all unique 1-tuples of distinct (pe_description) and (program_element_pe)

We can therefore answer three questions with this recipe:

How many distinct pe_description values exist in the LA County Environmental Health classification schema?

For a given pe_description, do all restaurants get assigned the same program_element_pe during their inspection?

What are the distinct values for program_element_pe?

Data Source

inspections = read_csv("https://raw.githubusercontent.com/completegraph/DataStore/master/Tidyverse/restaurant-and-market-health-inspections.csv")

str(inspections)

## Classes 'spec_tbl_df', 'tbl_df', 'tbl' and 'data.frame': 58872 obs. of  20 variables:
##  $ serial_number      : chr  "DAJ00E07B" "DAQOKRFZB" "DASJI4LUR" "DAWVA0CY3" ...
##  $ activity_date      : POSIXct, format: "2017-12-29" "2017-12-29" ...
##  $ facility_name      : chr  "HABITAT COFFEE SHOP" "REILLY'S" "STREET CHURROS" "RIO GENTLEMANS CLUB" ...
##  $ score              : num  95 92 93 93 93 94 96 94 93 95 ...
##  $ grade              : chr  "A" "A" "A" "A" ...
##  $ service_code       : num  1 1 1 1 1 1 1 1 1 1 ...
##  $ service_description: chr  "ROUTINE INSPECTION" "ROUTINE INSPECTION" "ROUTINE INSPECTION" "ROUTINE INSPECTION" ...
##  $ employee_id        : chr  "EE0000923" "EE0000633" "EE0000835" "EE0000958" ...
##  $ facility_address   : chr  "3708 N EAGLE ROCK BLVD" "100 WORLD WAY # 120" "6801 HOLLYWOOD BLVD # 253" "13124 S FIGUEROA ST" ...
##  $ facility_city      : chr  "LOS ANGELES" "LOS ANGELES" "LOS ANGELES" "LOS ANGELES" ...
##  $ facility_id        : chr  "FA0170465" "FA0244690" "FA0224109" "FA0046462" ...
##  $ facility_state     : chr  "CA" "CA" "CA" "CA" ...
##  $ facility_zip       : chr  "90065" "90045" "90028" "90064" ...
##  $ owner_id           : chr  "OW0178123" "OW0208441" "OW0228670" "OW0011830" ...
##  $ owner_name         : chr  "GLASSELL COFFEE SHOP LLC" "AREAS SKYVIEW LAX JV, LLC" "STREETCHURROS, INC" "FIGUEROA GROUP INC" ...
##  $ pe_description     : chr  "RESTAURANT (0-30) SEATS MODERATE RISK" "RESTAURANT (0-30) SEATS MODERATE RISK" "RESTAURANT (0-30) SEATS LOW RISK" "RESTAURANT (61-150) SEATS LOW RISK" ...
##  $ program_element_pe : num  1631 1631 1630 1636 1638 ...
##  $ program_name       : chr  "HABITAT COFFEE SHOP" "REILLY'S" "STREET CHURROS" "RIO GENTLEMANS CLUB" ...
##  $ program_status     : chr  "ACTIVE" "ACTIVE" "ACTIVE" "ACTIVE" ...
##  $ record_id          : chr  "PR0160774" "PR0193026" "PR0179282" "PR0044776" ...
##  - attr(*, "spec")=
##   .. cols(
##   ..   serial_number = col_character(),
##   ..   activity_date = col_datetime(format = ""),
##   ..   facility_name = col_character(),
##   ..   score = col_double(),
##   ..   grade = col_character(),
##   ..   service_code = col_double(),
##   ..   service_description = col_character(),
##   ..   employee_id = col_character(),
##   ..   facility_address = col_character(),
##   ..   facility_city = col_character(),
##   ..   facility_id = col_character(),
##   ..   facility_state = col_character(),
##   ..   facility_zip = col_character(),
##   ..   owner_id = col_character(),
##   ..   owner_name = col_character(),
##   ..   pe_description = col_character(),
##   ..   program_element_pe = col_double(),
##   ..   program_name = col_character(),
##   ..   program_status = col_character(),
##   ..   record_id = col_character()
##   .. )

Using the distinct function in dplyr

The function distinct() accomplishes this goal. There are two ways to apply the function in this example. In the first way, we may choose to extract the subset of columns used to obtain the unique k-tuples. In this case, k=2 or k=1. The second way, a feature of distinct, allows use to obtain the unique k-tuples without extracting the subset of columns of interest. We start by illustrating the first approach.

inspections %>% select( pe_description, program_element_pe) -> trimmed_dataframe

trimmed_dataframe %>% distinct() %>% arrange(pe_description, program_element_pe) -> distinct_two_tuples

str(distinct_two_tuples)

## Classes 'spec_tbl_df', 'tbl_df', 'tbl' and 'data.frame': 18 obs. of  2 variables:
##  $ pe_description    : chr  "FOOD MKT RETAIL (2,000+ SF) HIGH RISK" "FOOD MKT RETAIL (2,000+ SF) LOW RISK" "FOOD MKT RETAIL (2,000+ SF) MODERATE RISK" "FOOD MKT RETAIL (25-1,999 SF) HIGH RISK" ...
##  $ program_element_pe: num  1615 1613 1614 1612 1610 ...

distinct_two_tuples %>% kable() %>% kable_styling(bootstrap_options = c("striped", "hover")) %>%
   scroll_box(width="85%",  height="200px")

inspections %>% select(pe_description) %>% distinct() %>% arrange(pe_description) -> distinct_pe_description
inspections %>% select(program_element_pe ) %>% distinct() %>% arrange(program_element_pe) -> distinct_program_element_pe

nrow(distinct_pe_description)

## [1] 18

nrow(distinct_program_element_pe)

## [1] 18

nrow(distinct_two_tuples)

## [1] 18

inspections %>% distinct( pe_description, program_element_pe) %>% 
    arrange(pe_description) %>% 
    kable() %>% 
    kable_styling(bootstrap_options = c("striped", "hover")) %>%
    scroll_box(width="90%", height="200px")

inspections %>% distinct( pe_description, .keep_all=TRUE) %>% 
    arrange(pe_description) %>% 
    select(pe_description, program_element_pe, everything()) %>%   # place the pe_description column first
    kable() %>% 
    kable_styling(bootstrap_options = c("striped", "hover")) %>%
    scroll_box(width="85%", height="200px" )

Conclusion

The ability to extract classification schema from a larger data set is a frequently required recipe. Outside of data science, one can easily do this process using Excel and a pivot table to get distinct values for a single column. It is even possible though non-trivial to obtain the unique 2-tuples. However, it is more difficult to get a representative row of an entire dataset in Excel using the pivot table approach for getting distinct values. Thus, the tidyverse recipe approach clearly shows more flexibility and power rather quickly.