Introduction

I’ve geotagged a wide range of historic Toronto photos, but I have no idea of the breakdown of the dates across the decades. I thought a quick bar chart would give me insight into the data I have. I suspect the 1950’s will have the most photographs - lots of construction going on in Toronto, including the new subway, and photography was getting easier all the time. (I authored this and my other R cheat sheets directly in R Markdown1 which automatically displays the R code, runs the code, then includes the output in the HTML.)

Extract data from the MySQL database

I ran a simple MySQL SELECT statment to generate a CSV file with the data I’ll need:

SELECT code, counter, dateyear, datemonth, title, location FROM histPhotos
    WHERE picType = "historic" AND city = "Toronto"

Load data using the read.table() R function

Once we have the CSV data file from the MySQL SELECT statement, we can load it into a R data table using read.table() and take a look at the first few rows with head() .

photoDetailsDT <- read.table("historic-photo-details.csv", 
                       header = T, # Our CSV file has column header information in the first line
                       sep=";", # Our CSV file uses a comma to separate each column
                       # col.names = c("dateTime", "neighbourhood") # Reset column names                       
)
head(photoDetailsDT)
##     code counter dateyear datemonth
## 1 TORLIB       2     1906         7
## 2 TORLIB       4     1913         7
## 3 TORLIB       5     1952        12
## 4 TORLIB       6     1908         6
## 5 TORLIB      22     1920         0
## 6 TORLIB       7     1955         1
##                                                                  title
## 1                                                   York Station, 1906
## 2                                    Danforth and Guest Avenue in 1913
## 3                                             Chandlers Garage in 1952
## 4                           Town and Fire Hall - East Toronto in 1908 
## 5 Eglinton Hunt Club, water tower, and James Pears and Son brick yards
## 6                                                     Leaside Airfield
##        location
## 1  The Danforth
## 2  The Danforth
## 3  The Danforth
## 4   Upper Beach
## 5 North Toronto
## 6       Leaside

Quick look at the data we have loaded

The first column (code) corresponds to the original source archive. We can easily list and count the archives with levels(photoDetailsDT$code) and length(levels(photoDetailsDT$code)) which shows us the 20 sources as: BPATHE, COLLAT, FLICKR, GETSTOCK, HTPLAQUE, LACAN, ONTARCHV, ONTGOV, OURROOTS, RHPL, SART, TORARCHV, TORLIB, TORONTO, UER, UTORONTO, WEB, WIKIPED, YORKU, YOUTUBE

Add a new column with decade information

But we want to consider the decade for each photo, so we’ll add a new column to the data table and use gsub() to replace the last digit of the year with a 0 in all cases.

photoDetailsDT$decade <- gsub('.{1}$', '0', photoDetailsDT$dateyear)

Prepare the data we need for the graph

To create a bar graph we need two values - the set of decades, and the count for each of those decades.

Generate decade list

First we’ll generate the unique list of decades and assign it to the variable decadeList:

decadeList <- sort(unique(photoDetailsDT$decade))
cat("List of decades: ", decadeList, "\n")
## List of decades:  1800 1820 1830 1840 1850 1860 1870 1880 1890 1900 1910 1920 1930 1940 1950 1960 1970 1980 1990 2000 2010

Generate counts for each decade

Then we’ll use the table() and unlist() functions to generate a list of the counts for each and store that in a decadeCount variable:

decadeCount <- table(unlist(photoDetailsDT$decade))
cat("Decade count:" , decadeCount, "\n")
## Decade count: 1 1 8 4 29 27 39 68 125 244 550 430 318 186 236 81 57 47 10 6 15

Create the graph

We have the two sets of data, so lets create our bar chart!

par(las=2) # Want the titles to be horizontal
barplot( decadeCount, 
         horiz=TRUE, 
         names.arg=decadeList,
         main="Historic photo count by decade",
         xlab="number of photos"
         )

Verifying the results

I’m suprised that the most popular decade was 1910 - a good reminder that we should verify these result. It is a simple process to run some SQL COUNT statements against the original data for a few different decades and compare that to our R calculations:

SELECT COUNT(dateyear) FROM histPhotos 
    WHERE dateyear LIKE '191%' 
        AND picType = "historic"
        AND city = "Toronto"

This returns a count of 550, and we’ll repeat this with a few more decades and create a table to verify that the SQL count and R count are the same!

Decade SQL LIKE string SQL count R count from decadeCount[]
1860 LIKE '186%' 27 27
1910 LIKE '191%' 550 550
1970 LIKE '197%' 57 57

References

  1. More information on the version of R Markdown I’m using is available on theRStudio website. They describe it as “an authoring format that enables easy creation of dynamic documents, presentations, and reports from R. It combines the core syntax of markdown (an easy-to-write plain text format) with embedded R code chunks that are run so their output can be included in the final document.” Here’s a useful R Markdown cheat sheet that I’ve used often.↩