Week 7: Data Wrangling

• Guest speaker: Stian Haklev
• Data wrangling: An introduction (Bodong)
• Demo: Data Wrangling in R (Ethan)
• Hands-on: Data Science Studio (Bodong)

• Video
• Connect (if baby says yes…)

• Data is everywhere!
• Qualitative vs. Quantitative
• Levels of measure (e.g., nominal, ordinal, interval, and ratio)
• Unstructured vs. Structured
• Human-readable vs. Machine-readable
• Scattered vs. Linked
• …

Gathering, scraping, organizing, and mapping disparate datasets; converting, transforming, formatting, and visualizing data; managing and storing data; and more…

• A significant chunck of data scientists' job
• Data accessiblity and data sensemaking (Hirst)
• Exploratory
• Chit-chat with data
• …

(Data Wrangler – a sexy job :))

Data accessiblity and data sensemaking

• Clean
• Shape
• Augment
• Look

(Slides)

Pipelines of Data Analysis

The iterative process of wrangling and analysis (paper)

(Jeff Leek, The Elements of Data Analytic Style)

• Each variable you measure should be in one column
• Eachdifferentobservationofthatvariableshouldbein a different row
• There should be one table for each “kind” of variable
• If you have multiple tables, they should include a column in the table that allows them to be linked

(Jeff Leek, The Elements of Data Analytic Style)

1. The raw data.
2. A tidy data set.
3. A code book describing each variable and its values in the tidy data set.
4. An explicit and exact recipe you used to go from 1 to 2 and 3.

by Ethan Brown

Data Science Studio

by Bodong Chen

“Data Science Studio (DSS) is a software platform that aggregates all the steps and big data tools necessary to get from raw data to production ready applications.”

Key concepts in DSS

• Datasets
• Scripts
• Recipe
• Flow

The Toronto District School Board (TDSB) uses a Learning Opportunity Index (LOI) to “rank each school based on measures of external challenges affecting student success; the school with the greatest level of external challenges is ranked number one and is described as highest on the index.” TDSB recalculates LOI every two years.

Some questions to start with…

• Are schools progressing over the years?
• Do schools of different types–e.g., English vs. French, elementary vs. secondary, bigger vs. smaller, from different areas–progress differently?

• TDSB Learning Opportunity Index (in PDF)
  • School name, ward, LOI scores and ranks for 2009, 2011, and 2014
• Ontario public school contact information (in Excel/CSV)
  • School name, school id, school level, school language, postal code
• Ontario public schools enrolment (in Excel/CSV)
  • School id, school enrollment
• Canadian Postal Codes (in CSV)
  • Postal code, latitude, langitude, city

• cleaning/cleansing (to an analyzable form)
• joining (linking and merging data together)
• augmenting (geopoints)

• New project: LOI
• Import your first dataset: LOI

The data is in PDF!

• Tabular: http://tabula.technology/
• PDF tables: https://pdftables.com/
• Adobe Acrobat

Using Adobe Acrobat!

• New project: LOI
• Import your first dataset: LOI (finally)
• Overview of the “Explore” interface
  • column indicator
  • filter
• Create preparation script
  • remove rows
  • rename cols (do not use white spaces!)
  • copy across cols
  • change data type

• Save as a Recipe
• View Flow

• Analyze a col
• Bar graph
  • Ward and Score
  • Add Rank
• Scatterplot
  • 2014 and 2009, circles by School_Name
  • Add filter

• Generate new variables: ScoreChange, RankChange
• Change type: Ward, School_ID
• Update data

• Bar graph: Ward and Scorechange, add RankChange
• Sort

• Contact Info
• Enrollment
• Postal

Cleansing

• Fix Enrollment col: <10
• Change col names in Postal

• Fuzzy join: LOI & Contact
• Join: Enrollment & Contact
• Join: Enrollment & Postal (failed)
• Join: LOI & Enrollment

• Bar graph
  • x: Ward
  • y: ScoreChange
  • Color: Level
• Scatterplot
  • SchoolSize
  • ScoreChange

• Clean
• Shape
• Augment
• Look

• http://www.state.mn.us/opendata/data.html
• http://cascw.umn.edu/community-engagement-2/minn-link/minn-link-datasets/
• http://catalog.opendata.city/dataset/minnesota-education-indicators
• http://www.ciser.cornell.edu/ASPs/datasource.asp