Author: drpachec@syr.edu

3/21/17 Lecture notes: Sortable tables

12:30: Housekeeping:

  • Who’s finished Assignment 4? Let’s see.
  • Sundance event on Thursday/Friday!!! It will be very cool, you should come.
  • Special invite for VR Journalism workshop: 5-7 p.m. Thursday in Innovation Lab for journalists and VR creators. Please RSVP here if you plan to come so I can get enough pizza:

12:45: Sortable data tables

  • Tabletop, what I used in the first class. Displays days from Google spreadsheets.
  • See how one past student used it in his final project, near the bottom.
  • Some coders, like Chris Keller, have modified it for journalism. Here’s an example of a sortable, searchable table.
  • Where do you get the code? Github of course!
  • Download the code. Then …

1-1:30: How to make it work

  • Make a Google spreadsheet.
  • Give it a name with no spaces. Change the tab label to the same name, also with no spaces.
  • Share it, and make it public to the web.
  • Publish it (File > publish to the web.) Yes you must do both of these to make the data available.
  • Take the URL and save it somewhere.
  • Download the Tabletop for Data Tables code and unzip it. Note the files you need is in a second zip inside the first one.
  • Open the Scripts folder, then open tabletop-feed.js in Sublime Text.
  • At the very top, find initializeTabletopObject and put your URL into the place after it, being careful not to remove any necessary syntax.
  • Remove everything except for the “key” which is the long list of characters near the end, before /pubhtml.
  • Scroll down to what you see in var tableColumns (around line 28).
  • Look at your spreadsheet column names. Copy and paste them into the field after “sTitle”. This is what will be displayed in the column on your site.
  • Then, in the field next to mDataProp, paste the same name, but remove all capitals and spaces. This is the variable that pulls from the Google sheet.
  • Finally, you will need to remove some logic at the end that makes the last column display a web site link. We will go over how to do this, and what the code means, in class.
  • Edit the top of the HTML file to remove the sample text, then preview the HTML. You should see a searchable sortable table.
  • The last step? Upload into the class FTP and embed.

1:30-1:50. Phew, that was a lot!

So now work on it and try and fail and try and fail and try again until you can get a table to appear. I will go around to help you 🙂

Feel free to use data from previous exercises or assignments, such as New York State bridges or any data from the NY State open data site.

 

 

3/9 Lecture Notes: High Charts Part 2

12:30-1:15 High Charts, part 2

  • Downloading and modifying locally.
  • Previewing and preparing for upload.
  • Embedding after upload.
  • See if you can recreate this chart on Business Insider.

1:15-1:50 Lab

3/7 Lecture Notes: High Charts

12:30-12:50: Assignment 3 review

Remember: you need to embed the code from timeline.knightlab.com, not the Google Spreadsheet.

A few good ones:

Who rocked it with code?

12:50-1:20: High Charts

  •  Highcharts demonstration.
  • Downloading code
  • Finding a chart you like
  • Customizing the data
  • Load locally
  • Upload to FTP
  • Embed in a blog post

1:20-1:50: Make a sample chart

Reminder of how to FTP and embed:
http://journovationsu.org/class-ftp/

3/2 Lecture Notes: Timelines and StoryMap

SAD UPDATE: CartoDB seems to have gone through a significant upgrade that removes some of the most useful features that work for journalistic stories, so we are (sadly) removing it from the schedule completely. It’s too bad because it used to be a really great mapping tool.

If you made a map using their tutorials, send me a link and I will give you extra credit. Meawhile, please see the revised class schedule.

Moving on ….

12:30-12:45: Let’s look at timelines!

12:45-1: StoryMap

1-1:20: Another type of Timeline, this one vertical using open source code.

1:20-1:50: Work on your timeline or story map for assignment 3.

  • Assignment 3 is to make a timeline or storymap about a story you are interested in. Embed it in the class blog.
  • For extra credit, created a vertical timeline, FTP it into the class server and embed that into your post instead.

 

2/28 Lecture Notes: No-Code Dataviz tools, CartoDB

12:30: Housekeeping

12:31-12:50: Guest: Jodi Upton, Knight Chair in Data and Explanatory Journalism.

12:50-1: Your Infogr.ams (assignment 2).

1-1:40: Other no-code dataviz tools

But first, HTML! You can go through these three self-guided HTML teaching tools on your own.

Simple No-Code Dataviz Tools

Here are the ones we will go over today:

1) Infogr.am (http://infogr.am)

Infogr.am is an easy way to create simple charts and graphs, as well as scrolling infographics that you may notice people posting in places like Facebook and Tumblr. For journalism I think the graphs and charts work best, because you can embed them directly into stories to visually explain something you are reporting in text.

Take note of the Graphs and Charts tab at the top. Click the Charts tab to see all the different types of charts you can use.  Choose your visualization type, double click diferent parts of the interface to edit them, and copy and paste your data in. If you have trouble copying data from a web site, try starting from a summary sheet you make in Excel.

Click “Share” and copy the iFrame code at the bottom to embed into the blog. If you find that your chart is too wide for the blog post, you have two choices.  You can manually change the width and height variables in the HTML code you copied, being very careful not to change anything other than those numbers, or you can try the “responsive” code which will make your chart shrink or stretch based on the width of the page where it’s embedded. The second option is good if you think your chart will be viewed by people on mobile devices, but be sure to test it out from a mobile device to be sure.

Here’s an example of a chart I made in Infogr.am using data from a previous exercise.

Cardiac Deaths in Central New York Hospitals in 2010 | Create infographics

 

 

 

2) Easel.ly (http://easel.ly)

Think of Easel.ly as a quick infographic creator. You find a template you like, then start to manually edit it and add graphics from a built-in library. Charts can also be added and edited as spreadsheets, similar to Infogr.am.

 

3) Google Fusion Tables (http://fusiontables.google.com)

Google Fusion Tables  turns columns and rows of numbers in spreadsheets into visualizations. Once signed in, import a spreadsheet in .xls or .csv formats (not not .xlsx, which is Microsoft’s proprietary format). Make sure your spreadsheet has column headers, or it won’t work.

Sometimes Google will add a tab that it calls a “card” that is the best choice for your data — for example, a “Map of latitude” will appear if your data includes geocoordinates. If you see a card that works for you click it and see how it appears. If not, click the + sign to the right of that tab and choose Add a Chart. Click Done when your chart is set up the way you want it.

To publish your chart, you have to do two things:

1. Click the Share button at the very upper right of the browser, then “Change” next to Private under “Who can Access.” Select “Public on the Web” and then Save and Done.

2. Go to the Tools menu and choose Publish. You will see iframe tags here that you can embed in your blog post.

Here’s an example of a chart from Google Fusion Tables:

 

4) StoryMap (https://storymap.knightlab.com/)

From the Knight Lab at Medill, StoryMap lets you tell a story that’s broken up by points on a map. You can also use it to tell a story that moves across something that isn’t a map at all, such as a very detailed painting. Think of it like a timeline that takes place on a gigantic picture.

 

5) Google Maps (http://maps.google.com)

Quick overview of how Google Maps work if you don’t know already. Sign into Google, then choose the three lines at upper left and choose Your Places. Click Maps, then Create a Map. To embed it, you must first click the Share button in edit mode and change the access to “Public on the Web.” Then you can choose Embed.

 

6) CartoDB – Prepare for Thursday (http://cartodb.com)

On Thursday we will crack open CartoDB, which is a powerful mapping service that lets you tweak some of the interface and mess with a code a little. You can think of it as the gateway drug to other dataviz tools we will use that do require you to mess around with code. You can get a head start on how to use CartoDB through these free video tutorials on their web site.

Today I will show how we can create a map of the bridges in New York State that are in need of repair.

 

2/23: Dataviz Class 2 Notes

Housekeeping

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.

Assignment 2: Sorting and Filtering in Excel

In this exercise we will sort and filter data to understand it, then interviewing the data to gain insights and, hopefully, tell simple stories.

Data Viz – Sorting and Filtering Exercise
Using New York State Cardiac Arrests

New York State has an open-data policy. You can get a lot of data from New York State-funded programs at https://data.ny.gov/.

In this exercise, we will be using data on salary information for New York State authorities: https://data.ny.gov/Transparency/Salary-Information-for-State-Authorities/unag-2p27

Click the Export button and choose CSV for Excel, then save to your desktop.

Let’s make a list of the biggest fat cats in state government. Go to the Data menu and choose Sort.

  • Make sure the box labeled “My list has headers” is checked!
  • Click in “Sort By” and choose “Base Annual Salary” (column L).  Under “Order” choose “Largest to Smallest,” then click OK.
  • Who’s making the big money?
  • Change the sorting to smallest to largest. Who’s making the least?
  • Do you see any trends in the types of people who make the most and least amount of money?
  • Do you see any problems with missing data?

As you may notice, there are some records that have $0 for salaries, so we need to filter those out.

  • Go to the Data menu and choose Filter. You should see downward-facing arrows above each column header.
  • Click the arrow in the Base Annual Salary column.
  • Uncheck the box next to $0.

Now, let’s remove all salaries that are below the Federal poverty line of $20,000. Create a Sort, and sort on Base Annual Salary from smallest to largest. Select all of the rows under $20,000 and choose Edit > Delete Row.

Finally, let’s use a combination of filtering, sorting and formulas to find the average salary of people in the “Managerial” group (column H).

Now you should be familiar with sorting and filtering. What trends do you notice in the data? Do you see anything that may suggest a potential story? Or did you find a good story?

Now let’s shift to a topic that’s in the news a lot lately: how much money U.S. presidential candidates are getting. The Federal Election Commission makes this available in spreadsheet form here: http://www.fec.gov/disclosurep/PDownload.do

Download the data from the state where you’re from (or if not from the U.S. you can just choose New York). Use Sorting and Filtering to create a list of donations to two candidates you follow. When you create each list, use the Excel SUM feature to tally up how much money the candidate has received.

As a final step, create a simple bar graph in Infogr.am showing how much they’ve received. In class I will show you how to embed this in a blog post.

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.

 

 

Assignment 1: Class survey and blog registration

Assignment 1 is to complete the registration for the class blog and fill out this survey. Both are due by Monday morning.

2/16 Lecture Notes

12:30-1:20: Book presentations

  • Josue Muñoz: Ten Faces of Innovation
  • James Groh: The Innovator’s Dilemma
  • Max Jakubowski: The Four Hour Work Week
  • Logan Ratick: The End of Big
  • Kelly O’Neill

1:30-1:50: Final product plans.

  • The list of what you need to do is in the syllabus. The format can be similar to a PRD, but without the detailed requirements, as the audience is the board of directors of a media company, a grant-making foundation or investors. The goal is to convince them to a) let you run with your project, and/or b) fund it. Typical sections to include are:
    • Executive summary
    • Competitive overview
    • Written walkthrough of the user experience
    • Wireframes
    • Business model description and graphs
    • Team
    • Execution plan

A few recent examples:

You have a month to complete these, not including your spring break 🙂  Due date is Friday, March. 24.

2/14/2017 Lecture Notes

12:30-12:45: Business models critique

12:45-1: My example of a 10-20-30 pitch

  • The pitch
  • Critque of my own pitch!

1-1:30: Business book presentations

5 minutes presentation, 5 minutes Q&A.

  • Elena DeLuccia: The Filter Bubble
  • Tony Yao: The Lean Startup
  • Bridget Chavez: A Whole New Mind

Thursday:

  • Josue Muñoz: Ten Faces of Innovation
  • James Groh: The Innovator’s Dilemma
  • Max Jakubowski: The Four Hour Work Week
  • Logan Ratick: The End of Big
  • Kelly O’Neill: ?

1:30-1:50: PRD and final projects

Work on your PRD, and brainstorm with me on your final project idea.