A quick guide to turning a spreadsheet into an interactive map

Online visualization tools make it easy to create a map from a spreadsheet. Here’s a brief overview of current tools.

Why Spreadsheets?

A short overview of the importance of spreadsheets

Basic Aggregation with Pivot Tables

Pivot tables are an effective tool for quickly summarizing the facts from a mass of records

How (and why) to use a spreadsheet to turn a beautiful interactive earthquake map of into a bar chart

A walkthrough of basic spreadsheet and pivot table usage (Part 1 of 3) We have a lot of ways to easily create beautiful, elaborate visualizations. But let’s see what we can do when we prioritize the story of the data over its visual presentation.

Spreadsheets and Earthquake Data (Part 2 of 3)

A walkthrough of managing earthquake data and doing basic visualizations with spreadsheets (Part 2 of 3)

Pivot Tables and Earthquake Data (Part 3 of 3)

A walkthrough of using Pivot Tables to summarize earthquake data across two dimensions, allowing for even more insightful histograms (Part 3 of 3)

SELECTing rows FROM data tables

The syntax for retrieving and displaying data from a SQLite tables

LIMIT and ORDER BY in SQL Queries

How to specify the quantity and arrangement of data rows returned by the SQL database.

Using the WHERE clause to filter data in SQL

How to retrieve rows based on whether they match specified values.

More Boolean Expressions to Filter SQL Queries

How to filter SQL data using comparison operators, such as “greater than” and “not equal to”. Mostly, this is a review of how tricky logical expressions can be.

Using LIKE, IN, BETWEEN, and wildcards to match multiple values in SQL

Real-world data is often messy, so we need messy ways of matching values, because matching only on exact values can unintentionally filter out relevant data.

Functions for transforming text and numbers in SQL

Think of these as spreadsheet functions.

Aliasing Columns and Tables in SQL Databases

A short lesson on how to give human-readable names for otherwise messily-named values and identifiers.

Using GROUP BY to create aggregates in SQL

With the use of the GROUP BY clause, we gain the ability to aggregate our data based on values in a given column or columns. At the very least, this let’s us count the number of unique values in that column.

Aggregate functions in SQL

How to calculate sum, average, and other aggregates with the GROUP BY clause.

Using GROUP BY and aggregate functions in SQL (Unfinished)

With GROUP BY, we can specify the groups of data for which we want to sum, count, and average.

Some warmup baby queries

Getting started with baby names.

Creating Tables and Importing Data for Baby Names

How to create SQL tables and import raw data using the DB Browser for SQLite client.

An introduction to joins and INNER JOINs

An overview of the importance and syntax of JOIN queries and how to use them to find commonalities between different tables.

Using LEFT JOINs to find what's missing from one table to another

Sometimes when we compare two tables, we care more about their differences.

Joining to virtual tables with self-joins and nested joins

By using table aliases, we can join a table to a subset of itself, or to the results of another query.

image Using PostGIS, SQL, and CartoDB to identify schools at risk from Oklahoma's earthquakes
Using PostGIS, SQL, and CartoDB to identify schools at risk from Oklahoma's earthquakes

A tutorial on using geospatial analysis, shapefiles and datasets from the U.S. Geological Survey, Census, and Department of Education to visualize the impact of Oklahoma’s earthquakes and explore possible investigative projects.

image Mapping NYPD precincts with CartoDB
Mapping NYPD precincts with CartoDB

How to map simple geographic shapefiles with CartoDB and do some basic customization of visual features.

How to map both the quantity and change of NYPD precinct-level crime data

Did 2009 statewide reforms change how the NYPD polices misdemeanor drug crimes? How to go beyond the CartoDB map wizard and write our own CartoCSS to show greater depth of data.

image Creating a multi-layered CartoDB map of Census household incomes and Starbucks locations in NYC
Creating a multi-layered CartoDB map of Census household incomes and Starbucks locations in NYC

How to combine a point data with shapefile data on the same CartoDB map (tutorial in progress)

Exploring the Wall Street Journal's Pulitzer-Winning Medicare Investigation with SQL

How to use SQL to learn about Medicare, contemporary issues in Medicare billing practices, the math and evidence behind the WSJ’s “Medicare Unmasked” project, and the general problems with real-world datasets.

Looking for more tutorials?

I haven't ported all the ones from the 2014 session; check them out at fall2014.padjo.org/tutorials