Using Statistics Canada

CANSIM and Fathom/Excel: Firearms and Robberies - An Example

Follow the following steps to look for a relationship between firearms offenses and robberies.

  1. Go to
  2. Choose English
  3. In the blue “Browse by” box, click on “Crime and Justice”
  4. Now , click “Crimes and Offences” under Subtopics
  5. Choose “Detailed tables from CANSIM” to see the actual data
  6. Choose Table252-0051, Incident-based crime statistics, by detailed violations.
  7. This has WAY more information than we want to graph. Let’s select just the Ontario statistics on firearms and on robberies

Click the “Add/Remove Data” tab

Step 1: Under Geography, uncheck All and select onlyOntario

Step 2: Under Violations, uncheck Total and select

Total firearms; use of… and Total robbery separately(2 different checkmarks)

Step 3: Under “Statistics” select only “Rate per 100,000 population”

Rate is often a better indicator of trends: some numbers might increase only because the overall population has increased

Step 4: Change the first date to 1998and the last date to the last year available

Step 5: Select the HTML table time as rows option

Step 6: click Apply

Up should pop a table of data, three columns, many rows, beginning:

Geography3=Ontario7
Statistics=Rate per 100,000 population(rate)

Incident-based crime statistics, by detailed violations, annual
Violations / Total firearms; use of, discharge, pointing / Total robbery18
Footnotes
1998 / 2.05 / 92.03
1999 / 6.37 / 86.49
2000 / 7.25 / 81.73

etc.
To paste data into Fathom:

  1. Open Fathom from Start/Programs/Curriculum or wherever it is located in your workspace
  2. Move your cursor on top of the little tan box (New Collection) in the top left corner and drag the box into the workspace (the huge white area)
  3. On the CANSIM website, highlight the three columns, twelve rows (or more) of data. Do not highlight any words, headings or the source line at the bottom. Fathom is expecting only numbers. If you are copying from another web site, it may be helpful to copy your data to Excel first. In Excel you can remove any dollar signs ($), commas, footnotes or other formatting before you paste to Fathom. Fathom expects only numbers.
  4. Once the numbers in rows and columns are highlighted in blue, either press Ctrl and C at the same time, or press the right mouse button and select Copy
  5. Open Excel – paste the data into Excel (Ctrl V), then highlight all the data and copy it again (Ctrl C) this removes the HTML formatting
  6. Go back to Fathom, click on the “Collection 1” box you just created and either press Ctrl V or right click and select “Paste Cases”. Now the data is in Fathom.
  7. To see this wonderful information, drag the Table (“New Case Table”) from the top left corner into the white workspace. The headings aren’t terribly descriptive. Let’s fix this.
  8. Double click on “Collection 1” and rename to “Ontario Crime Rates (per 100,000 people)”. OK
  9. Double click on “Attr1” – change this to “Year”
  10. Double click “Attr2” and make this “Total firearms” – we got this from the top of the CANSIM webpage
  11. Notice that other heading from the webpage is “Total robbery”. Make this the heading for “Attr3”

To create graphs in Fathom:

You can create nice Scatter Plots in Fathom from your table

  1. Put your cursor on the next button along the top, labelled “Graph” and drag it to an empty spot in the workspace
  2. Now, in your table, put your cursor on top of the title “Year” and drag it with your left mouse button to the horizontal (x-axis) fo your graph and drop the data. Independent data should go on the x-axis. Some points should appear.

Now, put your cursor on the Total_firearms heading and drag that to the vertical or y-axis of the graph and drop the data. Now the points should go to their correct spots. This is a one variable graph.

For a two variable graph

  1. Drag in a new Graph from the top
  2. Then drag Total_firearms to the x-axis and Total_robbery to the y-axis
  3. Right-click the graph and select “Least Squares Line” to add a line of best fit. It may surprise some to see that in those years when there were more firearms crimes, there were less robberies

To copy a graph or table into Word:

  1. Select the graph or table
  2. Choose Edit/Copy as Picture
  3. In Word, click the place to paste the Picture
  4. Press Ctrl V or right click and choose Paste

Using Excel Instead of Fathom For Graphs

  1. Copy and paste the DATA (titles and numbers) into an Excel Document.
  2. Statistics Canada sometimes adds footnotes – remove them.

(Ex. remove “18” from Total robbery18”)

  1. Widen the columns on the top to fix the table so it shows your numbers and titles.In Excel, you may find large numbers changed into #####if the column is too narrow.
  1. We have an extra row for the “Footnotes” label. Left click on the “3” (the row number) beside “Footnotes” and right click and select “Delete”
  1. Highlight everything from the top left corner to the bottom of the Firearms column. Go to Insert and ScatterChart. choose the simple Scatterplot
  1. Under the Chart Tools menu on the top, select the Design tab and add a these Chart Elements: Chart Title and Axis Titles. (Horizontal – “Year” Vertical – “Firearms Charges”)

Voila - a 1 Variable Graph showing how the RATE of firearms charges have changed through the years. Other1 Variable graphs could show results for players/teams/countries rather than years

  1. Highlight (select)the firearms and robbery data. Go to Insert and ScatterChart. choose the simple Scatter option again
  1. Right-click the chart and choose Select Data. Under Legend Series click Edit and under Name: insert “Ontario Crime Rates”. “OK” and “OK”
  1. Add a title and axis (see step 7 Horizontal – “FirearmsCharges” Vertical – “Robberies”)
  1. Under Add Chart Element… / Axes / More Axis Options click the AXIS OPTIONS arrow and select Vertical (Value) Axis. Change the Minimum Bound to 70 (minimum robberies)
  1. Now rightclick on the graph (near the dots). Scroll down to Add Trendline…
  1. Type Linear should be highlighted, select “Display Equation on Chart” and “Display R-Squared Value on chart” Click Close. Ensure your Graph is big so that you can see this Line of Best Fit. If the text for the trendline equation is on top of the graph, you can move it.

This is a 2 Variable Graph showing the relationship between Firearms Charges and Robberies

  1. Copy the table and the graphs to Word so you can write up a brilliant analysis. Select the table and hit Ctrl-C then Ctrl-V in Word. Do the same for the graph.