Census data is catnip for data journalists. There’s a lot of neat visualizations you can make with it.
We have these nice interactive maps made by CP with population figures for every census tract at major metropolitan areas. It will be updated with more data as new census figures come out throughout the year.
I wanted to look even deeper, at the block level. StatsCan also has good data for census dissemination areas (DA), which is a group of one or more neighbouring blocks with a population of at least 400. But instead of just mapping the raw data, I wanted to see how each area’s population density compared with the rest of the city and the country.
Step 1: Get the data
StatsCan conveniently offers geographic boundary files for almost all hierarchies of census zones, embedded with the census data. To make the Fusion Table map above, I used the Cartographic Boundaries File in the ArcGIS (.shp) format at the Dissemination Area level.
But this file covers all of Canada. I want to isolate the Census Metropolitan Area of Montreal, which includes Laval, North Shore, and South Shore. For this, I need to use a GIS software. QGIS is a free open-source tool popular among thrifty reporters.
Step 2: Isolate the data
I won’t go over the basics of QGIS and shapefiles here. To become familiar with the software, read this excellent tutorial.
Open a new vector layer and load the .shp file of the shapefile package. You should see a map of Canada cut up along dissemination areas. Click on Layer > Open Attribute Table. The underlying data of the shapefile will come up. This is the data in the .dbf file of the shapefile package.
There’s a search field at the bottom to filter the data. Search for “Montréal” in the CMANAME field (red box in screenshot below). It will select the 6,261 DAs in metro Montreal. I want to do some calculations with this data, so I copied it to an Excel spreadsheet by clicking the button “Copy selected rows to Clipboard” (green box).
After pasting it in Excel, I zoomed into the area on the map by clicking the magnifying glass button (blue box).
With all the Montreal DAs selected, go to Layer > Save Selection as vector file. Save as ESRI shapefile or as Keyhole Markup Language (KML). Fusion Tables accepts either format.
3. Add value to the data
To rank each DA by density, you have to go to Excel. That’s why we copied the data to a spreadsheet.
The population density is in the column named POP_DN. Sorting this from highest to lowest gives you the order. In a new column, type in 1 in the first row of data, 2 on the second, and 3 on the third. Do an auto-fill to complete the pattern to all 6,261 rows. You have the ranks for Montreal.
To find the ranking for all of Canada, you need to copy the entire national data in a new Excel sheet, and again sort from highest to lowest, and create a new column with the rankings. Before copying the national ranking to the Montreal sheet, make sure the DAs in both sheets are sorted in the same order. Sort both by DAUID, the unique DA indentifier number.
Now you have to join the ranking data you created with the shapefile. There are two ways to do this:
A. Do a join in QGIS using DAUID as the common column between the datasets. This will add the ranking columns to your shapefile. Detailed instructions here. To reduce clutter and data size, delete all columns except DAUID and your ranking columns in the Excel sheet you created.
B. Upload the shapefile to Fusion Tables, via Shape to FT if using .shp or directly if using KML. Upload your ranking speadsheet as a separate table then merge the two into a new table using DAUID as the common column.
3. Create the map
This is the easy part, and needs little explanation. It’s a matter of shading each area according to the density figure in a way that looks good and looks clear. By playing around with different options in Fusion Tables, one quickly arrives at a satisfying look.