Announcements

• Deadline Policy Reminder
   
• Project Deliverables [tonight]
  • Dataset Forms ✏️
  • Interview Forms ✏️
  • Pitch!
• Upcoming Project Deliverables
  • [Part 2] - Reporting Check-In (around 9/16)
  • [Part 3] - Final Draft (around 9/21)
     
• Reminder about emojis that require a response or rewrite... (⛔️, ❓, 🤯)

Project Timeline

• Upcoming Project Deliverables
  • [Part 2] 10/16 Reporting Check-In / Data Viz
  • Aim for Rough Draft around 10/18-10/19
  • [Part 3] - Final Draft (around 10/21) 

<--- Rough Draft ---->

<--- Final Draft ---->

<- Respond to Edits ->

<--- Reporting / Data Analysis ---->

Pitch Approval

Interviews

• Interviews are an important part of data reporting
  • Domain experts
  • Experts in your dataset
  • People impacted by the issue
  • etc...
• They help you know where to look

Merging Data

 

 

1. Working with relational data from one source

2. Merging data from different sources

raw-polls.csv

but relational!

 

https://docs.google.com/spreadsheets/d/1Zx2R85BvYVg0VmIfNW60WdGyaOaWS06HQ9LwfpShp8E/edit#gid=1124900583

Poll

• poll_id
• start_date
• end_date
• pollster_id 🔑
• partisan 
• ...

 

 

 

Race

• race_id
• election_date
• location

Answer

• answer_id
• candidate_id 🔑
• percent

Candidate

• candidate_id
• name
• party
• gender
• birthday...

Types of Relations

 

one-to-one

one-to-many

many-to-many

 

Poll

• poll_id
• start_date
• end_date
• pollster_id 🔑
• partisan 
• ...

 

 

 

Race

• race_id
• election_date
• location

Answer

• answer_id
• candidate_id 🔑
• percent

Candidate

• candidate_id
• name
• party
• biography_id🔑

Biography

• biography_id
• candidate_id🔑
• gender
• birthday

Poll

• poll_id
• start_date
• end_date
• pollster_id 🔑
• partisan 
• ...

 

 

 

Race

• race_id
• election_date
• location

Answer

• answer_id
• candidate_id 🔑
• percent

Candidate

• candidate_id
• name
• party
• biography_id🔑

Biography

• biography_id
• candidate_id🔑
• gender
• birthday

Poll

• poll_id
• start_date
• end_date
• pollster_id 🔑
• partisan 
• ...

 

 

 

Race

• race_id
• election_date
• location

Answer

• answer_id
• candidate_id 🔑
• percent

Candidate

• candidate_id
• name
• party
• biography_id🔑

Biography

• biography_id
• candidate_id🔑
• gender
• birthday

Poll

• poll_id
• start_date
• end_date
• pollster_id 🔑
• partisan 
• ...

 

 

 

Race

• race_id
• election_date
• location

Answer

• answer_id
• candidate_id 🔑
• percent

Candidate

• candidate_id
• name
• party
• biography_id🔑

Biography

• biography_id
• candidate_id🔑
• gender
• birthday

Poll

• poll_id
• start_date
• end_date
• pollster_id 🔑
• partisan 
• ...

 

 

 

Race

• race_id
• election_date
• location

Answer

• answer_id
• candidate_id 🔑
• percent

Candidate

• candidate_id
• name
• party
• biography_id🔑

Biography

• biography_id
• candidate_id🔑
• gender
• birthday

Referencing Relations in Google Sheets / Excel...

 

Documentation:

https://support.google.com/docs/answer/3093318?hl=en

 

VLOOKUP(search_key, range, index, [is_sorted])

 

Bonus Learning Objective

- [ ✔️ ] Reading technical documentation

But what about many-to-many relations?

 

 

 

 

 

 

 

 

https://stackoverflow.com/questions/35913549/how-to-join-two-tables-with-many-to-many-relation-using-vlookup-in-excel

 

Bonus Learning Objective

- [ ✔️ ] Reading Technical Blog Posts (Like StackOverflow)

 

(╯°□°)╯︵ ┻━┻

Joins

 

 

• Left Join
• Right Join
• Inner Join
• Outer Join

Left Outer Join

https://docs.microsoft.com/en-us/power-query/merge-queries-left-outer

Excel / Google Sheets

=VLOOKUP(B1, 'Right Table!B2:C4, 2 ,FALSE)"

SQL

SELECT * FROM left_table lt LEFT JOIN right_table rt ON lt.CountryID = rt.ID

Python / Pandas

left_table.merge(right_table, how='left', left_on='CountryID', right_on='ID')

R / TidyR

left_table %>% inner_join(right_table, by = c("CountryId" = "Country")

 

Left Outer Join

Right Outer Join

https://docs.microsoft.com/en-us/power-query/merge-queries-right-outer

Inner Join

 

 

https://docs.microsoft.com/en-us/power-query/merge-queries-inner

Full Outer Join

 

https://docs.microsoft.com/en-us/power-query/merge-queries-full-outer

Merging Data

Standardized Identifers

Within a Dataset

• race_id
• pollster_id
• etc...

 

 

Across Datasets

• Bioguide
• Fips Code
• Congressional District

Did Democratic House Members Spend More Than Republican House Members On Office Expenses in 2021 Q2?

 

• Assignment 3
  • House Expenditures
• Biographical Data
  • Congress Legislators

Cities Spend Millions On Police Misconduct Every Year. Here’s Why It’s So Difficult to Hold Departments Accountable.
 

Story

https://fivethirtyeight.com/features/police-misconduct-costs-cities-millions-every-year-but-thats-where-the-accountability-ends/

 

 

Data + Caveats ⚠️

https://github.com/fivethirtyeight/police-settlements/

Non-Standard Identifiers

Atlanta

From Laura Bronner's slides about quant editing

What the heck is Atlanta?

Standardized Identifers

Within a Dataset

• race_id
• pollster_id
• etc...

 

 

Across Datasets

• Bioguide
• Fips Code
• Congressional District ⚠️

Other things to think about when merging data...

• What is the "universe" of each dataset?
   
• Are there null values after merging?
   
• Did I drop any observations? (always count)

tidylog (R), pandas-log (python)

This is NOT

~~Assignment 4~~

because there is no Assignment 4....

 

https://docs.google.com/spreadsheets/d/1Zx2R85BvYVg0VmIfNW60WdGyaOaWS06HQ9LwfpShp8E/edit#gid=828318338​

Exploratory Data Visualization 📊

 

• There are lots of observations
   
• You want to see the "shape" of a variable
   
• There are multiple variables you want to compare and a pivot table just isn't doing the job...

When to do exploratory viz?

When it's worth it...for me, almost always.

Types of Data

https://towardsdatascience.com/data-types-in-statistics-347e152e8bee

Types of Plots

https://www.datawrapper.de/charts

Chart Taxonomy

ft.com/vocabulary

Type of Data

Which one do I need?

Type of Plot

Inspect Columns

• Do I know what each column means?
  • write down the unanswered questions
• How was it collected?
• What kinds of errors should I be looking out for? 👀
• Are there any blanks? Null values? Repeated values?

Inspect Columns

• What are the types of data in each column?
  • Categorical Columns
    • What are the categories?
    • Could they have been grouped differently?
       
  • Continuous Columns
    • range (min / max)
    • distribution (plot)
    • central tendency (mean / median)
    • variability (stdev / IQR)

Overall Description

• Categorical variables
  • frequencies per category
     
• Continuous variables
  • Summary stas
  • Distributions

Asking Questions

• Does the bias vary by election type?
• Are polls more or less biased over time?
• Are polls more biased in some years than others?

Plotting 1 column

• What are the types of data in each column?
  • Categorical Columns
    • pivot table w/ count
    • bar chart
  • Continuous Columns
    • dot plot
    • histogram
    • box and whiskers

Plotting 2 columns

• What are the types of data in each column?
  • Categorical vs Categorical
    • pivot table
    • XY heatmap
       
  • Continuous vs Continuous
    • scatter plot (with trendline?)
       
  • Continuous vs Categorical
    • ​categorical variable on axis
    • categorical variable on facet

Plotting many columns

• X axis
• Y axis
• color
• fill
• size
• facet
• facet_x, facet_y
• shape
• opacity

Do you need a model?

Is it regression time?

• Continuous variables

 

 

• Categorical variables

?

Reusing Visual Encodings

• X axis
• Y axis
• color
• fill
• size
• facet
  • facet_x
  • facet_y
• shape
• opacity

Annotations

• Part to whole
• Split into buckets

 

 

Annotate with text labels OR

 

• color
• fill
• size
• facet
• shape
• opacity
• etc...

Inspect Columns

• What are the types of data in each column?
  • Categorical Columns
    • "bar" in a bar chart
    • "facets" in a small multiples chart
    • colors
    •  
  • • range (min / max)
    • distribution (plot)
    • central tendency (mean / median)
    • variability (stdev / IQR)

https://xkcd.com/1138/

• GeoSpatial data
  • does distance / size matter?