Retrieving multi-sheet XLS/XLSX resources

Source: vignettes/multisheet_resources.Rmd

Excel files (XLS and XLSX) are a common form of data on the City of Toronto Open Data Portal.

In cases where the file only contains one sheet, the resource is returned as a tibble. For example, this data set on TTC Ridership Analysis from 1985 to 2018:

When the file contains multiple sheets, the resource is returned as a named list, where the names are the names of the sheets, as in the dataset on Wellbeing Toronto Demographics:

To access the relevant sheet, pull out the list element:

There are also cases where the file contains multiple sheets and it would be helpful to have them all together as a single data set. For example, the 2019 TTC Bus Delay Data:

ttc_bus_delays_2019 <- search_packages("TTC Bus Delay Data") %>%
  list_package_resources() %>%
  filter(name == "ttc-bus-delay-data-2019") %>%
  get_resource()

The result of is a list with an element for every month of data, each of which is a tibble:

Note that the data for for the element Apr 2019 has one more variable than the rest (11 versus 10):

sapply(ttc_bus_delays_2019, colnames)
#> $`Jan 2019`
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Min Delay"   "Min Gap"     "Direction"   "Vehicle"    
#> 
#> $`Feb 2019 `
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Min Delay"   "Min Gap"     "Direction"   "Vehicle"    
#> 
#> $`Mar 2019 `
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Min Delay"   "Min Gap"     "Direction"   "Vehicle"    
#> 
#> $`Apr 2019`
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident ID" "Incident"    "Delay"       "Gap"         "Direction"  
#> [11] "Vehicle"    
#> 
#> $`May 2019 `
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Min Delay"   "Min Gap"     "Direction"   "Vehicle"    
#> 
#> $`June 2019`
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Delay"       "Gap"         "Direction"   "Vehicle"    
#> 
#> $`July 2019`
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Min Delay"   "Min Gap"     "Direction"   "Vehicle"    
#> 
#> $`Aug, 2019 `
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Min Delay"   "Min Gap"     "Direction"   "Vehicle"    
#> 
#> $`Sept 2019`
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Min Delay"   "Min Gap"     "Direction"   "Vehicle"    
#> 
#> $`Oct 2019 `
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Min Delay"   "Min Gap"     "Direction"   "Vehicle"    
#> 
#> $`Nov 2019`
#>  [1] "Report Date" "Route"       "Time"        "Day"         "Location"   
#>  [6] "Incident"    "Delay"       "Gap"         "Direction"   "Vehicle"

It seems that the Apr 2019 data has gained a variable Incident ID, and that the variables Min Gap and Min Delay, present in all the other months, have been renamed to Gap and Delay, respectively.

We can rename these two variables:

and combine all of the elements into a single tibble using dplyr::bind_rows():

Unfortunately, it looks like the Time variable got Excel™ed, and will need some data cleaning.

For interests sake, it appears that Incident ID is a lookup ID for the type of incident – only present in the Apr 2019 data, but interesting nonetheless!