2/23: Dataviz Class 2 Notes


I. Excel
First we’ll go through some basic features of Excel and how to create formulas.

  • Adding information as data versus text.
  • When to save as XLS and CSV, and the difference.
  • Columns and rows.
  • Formula: using the equal sign for functions. Basic math.
  • Sum columns or rows.
  • Format cells to change cell type (text, number).
  • Making simple charts in Excel.
  • Adding formulas. Common formulas:  adding, subtracting, dividing, multiplying, summing.
  • Sorting and the importance of noting your list has headers.
  • Filtering.

II. Sorting and filtering NYS bridge data

  • Download and unzip this data set of 51,000 bridges in New York State. bridges_blanksremoved.csv.
  • Open it in Excel. Choose File > Save As > XLS.
  • Scroll right until you find the column “critfrac” (column DL) which stands for critical fracture. A y12 or y24 means outdated design, so a single solid hit can bring the entire bridge down.
  • Next, find the column “suffrtno” in column FC, which stands for Sufficiency Rating. Anything under 50 is considered dangerous.
  • Also note the “totlcost” (total cost to fix in thousands of dollars) in column DV, and “avdayno” (average daily traffic) in column AK.
  • Questions:
  1. How many bridges are in danger of collapsing due to critical fracture?
  2. How many bridges have an inadequate sufficiency rating?
  3. How many have both bad critical fracture and sufficiency rating numbers?
  4. How much traffic goes over the bridges with both bad critfrac and suffrtno ratings?
  5. How much will it cost to fix the bridges with both bad critfrac and suffrtno ratings?Go through this yourself, then let’s review the answers and how to get them.

III. Acquiring Data

  • New York State public data.
  • Getting data: formats to look for (CSV, JSON).
  • What if you get a big, fancy Excel document? How to dumb it down to a CSV.
  • Copying and pasting data as values versus as formulas.
  • Copying data from HTML tables.

IV. Sorting and Filtering Data

  • Show how to sort and filtering data in Excel.

V. Sorting and Filtering Exercise

Assignment 2: Find some interesting data from the New York State open data site. Use sorting and filtering to hone in on some interesting and easily digestible data points that could be used in a story. Create a graph of the data you find in Infogr.am. Create a blog post that includes a link to the raw data from the NYS site, explain how you filtered it, and embed the Infogr.am chart into the post. Due Monday morning.