Introduction to Data Analytics

layout:true

<div class="header" style="display:block; text-align: left; color:gray; font-size:1em; position: fixed; top: 0px; left: 0px; height: 30px;vertical-align:middle;margin:7px 0 0 0;width:100%;background: #fcfcfc;
background: -moz-linear-gradient(top, #fcfcfc 0%, #d1d1d1 100%); 
background: -webkit-linear-gradient(top, #fcfcfc 0%,#d1d1d1 100%);
background: linear-gradient(to bottom, #fcfcfc 0%,#d1d1d1 100%);
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#fcfcfc', endColorstr='#d1d1d1',GradientType=0 );">
  <img src="images/datapolitan_transparent_small.png" style="position: fixed;top: 2px; left: 5px; width: 14%;">
  <p style="display:block;text-align:left;color:gray;font-size:1em; position: fixed; top: 0px; left: 155px; height: 30px;vertical-align:middle;margin:7px 0 0 0; ">Introduction to Data Analytics - NYC Parks and Recreation</p>
</div>
<div class="footer" style="display:block;color:gray;position:fixed;bottom: 0px;left:0px;height:30px;vertical-align:middle;margin:0 0 0 0;width:100%;background: #fcfcfc;background: -moz-linear-gradient(top, #fcfcfc 0%, #d1d1d1 100%);background: -webkit-linear-gradient(top, #fcfcfc 0%,#d1d1d1 100%);background: linear-gradient(to bottom, #fcfcfc 0%,#d1d1d1 100%);sfilter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#fcfcfc', endColorstr='#d1d1d1',GradientType=0 );">
  <p class="footer">
    <span xmlns:dct="http://purl.org/dc/terms/" property="dct:title">Introduction to Data Analytics</span> by <a xmlns:cc="http://creativecommons.org/ns#" href="http://www.datapolitan.com" target="_blank" property="cc:attributionName" rel="cc:attributionURL">Richard Dunks</a> and <a xmlns:cc="http://creativecommons.org/ns#" href="http://www.tinypanther.pizza" property="cc:attributionName" rel="cc:attributionURL">Julia Marden</a> is licensed under a <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">Creative Commons Attribution-ShareAlike 4.0 International License</a>.<a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/"><img alt="Creative-Commons-License" style="border-width:0" src="https://i.creativecommons.org/l/by-sa/4.0/80x15.png" /></a>
  </p>
</div>

class:center, middle
![img-center-50](images/datapolitan.png)

# Introduction to Data Analytics<br>City of New York Parks and Recreation

- - -

## Instructors: Elizabeth DiLuzio + Gemma Duffee

### Follow along at: http://bit.ly/parks-intro-analytics

---

class:center,middle
# Welcome

## [Click to Skip to Afternoon Session](#lunch)

---

exclude:true
# Data Driven Culture
![img-center-95](images/ddc_compass.png)

???
+ A placeholder for a later discussion on data driven culture
+ Establishes the "ideological" orientation of the class

---

# A Few Ground Rules
???
+ Facilitators establish the intention we have for the culture of the classroom
--

+ Step up, step back
--

+ Be curious and ask questions!
--

+ Assume noble regard and positive intent
--

+ Respect multiple perspectives 
--

+ Listen deeply
--

+ Be present (phone, email, social media, etc.)

---

# Introduce Yourself to Your Neighbor
+ Who are you?
+ Where do you work?
+ What has been the proudest moment in your job?
+ What's your favorite park?

???
+ An opportunity for participants to get to know each other and settle into the class
+ We bring them into a positive space related to their work
+ Facilitator reminds them the data isn't the end but the means to achieve the actions that make us feel good about our jobs

---

# What to Expect Today
+ 9:40 – Data Analytics 101
+ 10:00 - Introduction to Problem Ideation
+ 10:30 – 15 min break
+ 10:45 – Process Mapping
+ 12:00 – Lunch
+ 1:00 – Process Mapping Part 2
+ 2:30 – 15 min break
+ 2:45 – Data Analytics Exercise
+ 4:30 – Dismissal

???
+ Outline of activities for participants awareness

---

# Goals for This Class
--
???
+ Sets expectations for the students
+ Light comedy is intended to relax participants concerned about the content
--

+ Explore the elements of a data-driven culture
--

+ Learn about different types of analysis
--

+ Practice mapping out the analytics process
--

+ Get hands-on practice with Parks data
--

+ Helpful demonstration of key skills in Excel
--

+ Some great graphics (courtesy of [Julia Marden](http://tinypanther.com/) and the Internet)
--

+ A good, worthwhile day geeking out about data

---

# Key Things to Keep in Mind
???
+ Makes clear the key outcomes for the class and intentions for the project
+ Manage expectations for a heavily techical class
--

+ Our hope is to introduce important concepts and allow us to discuss them as a class

--
+ Our focus today is process over product
--

+ This is a chance to develop experience collaborating together on problems
--

+ Our ultimate goal is to help you leverage data to improve operations in your areas of responsibility

---

# Housekeeping
???
+ Facilitator sets expectations with the students
+ Establishes the "contract" for the class
--

+ We’ll have one 15 minute break in the morning
--

+ We’ll have an hour for lunch
--

+ We’ll have a 15 minute break in the afternoon
--

+ Class will start promptly after breaks
--

+ Feel free to use the bathroom if you need during class
--

+ Please take any phone conversations into the hall to not disrupt the class

---

exclude:true
# Data Driven Decisions Require Humans

![img-center-50](images/iphone_intel.jpg)

.caption[Image Credit: 100 lion, [CC BY-SA 4.0](http://creativecommons.org/licenses/by-sa/4.0), via [Wikimedia Commons](https://commons.wikimedia.org/wiki/File%3AIphone-62.jpg)]

## [How Intel lost out on the contract of a lifetime](http://appleinsider.com/articles/13/05/16/intel-chips-could-have-powered-first-iphone-ceo-otelini-says)

???
+ Facilitator reinforces point about human mental intermediation in using data

---

class:center, middle
# Let's talk data.

---

# The Value of Data
???
+ Facilitator prompts participants to reflect on the value of data in their work
+ Helpful to ask "how" when presented with reasons like "it tells us what happened" in order to get them thinking about the more intrinsic value of data
+ Also prompt to think about how they would manage something without data (experience, anecdote, etc.), particularly some process, office, effort with lots of elements or moving parts

+ Data tells a story about something that's happened
--

+ Can describe what happened directly or indirectly
--

![img-center-80](images/311data_parks.png)
???
+ Ex: "are the parks worst 10-10:59am? Why or why not?"
---

class: center,middle
# Are All Data Points Created Equal?
???
+ Facilitator prompts participants to think about data quality issues they experience and how it impacts their work
+ Also any other data issues they feel important to mention
---

class:middle
> Facts do not "speak for themselves." They speak for or against competing theories. Facts divorced from theory or visions are mere isolated curiosities.

## -Thomas Sowell *A Conflict of Visions*
???
+ Facilitator has participant read the quote and invites the class to reflect
+ Some will take this as talking about manipulation of data. It's more about the manipulation of context. The numbers aren't wrong, but are misused or not applied in the right context
---

exclude:true
# Being data-driven doesn't mean blindly following data

![img-center-80](images/blindlyfollowdata.jpg)

.caption[Image Credit: zhouxuan12345678, [CC BY-SA 2.0](https://creativecommons.org/licenses/by/2.0/), via [Flickr](https://www.flickr.com/photos/53921113@NO2/5453212152)]

---

exclude:true
class:middle
> Data is only as valuable as the decisions it enables.

## -[Ion Stoica](https://twitter.com/databricks/status/810190036624875520)

---

# What is Analysis?
???
+ Facilitator prompts participants to reflect on a definition for analysis
+ Facilitator reinforces the essential role of storytelling in good analysis
--

>“Analysis is simply the pursuit of understanding, usually through detailed inspection or comparison”

## - Carter Hewgley, Director of Analytics, [Center for Government Excellence](https://govex.jhu.edu/)

---

class:center, middle
# Data Analysis Should Drive Decision Making
#  
##

---

# Data Analysis Should Drive Decision Making

![img-center-80](images/datadriven.png)
.caption[Image Credit: Data Driven School (https://www.slideshare.net/moshiiit/data-driven-school-mis-case-study)]

---

>“We move from data to information to knowledge to wisdom. And separating one from the other… knowing the limitations and the danger of exercising one without the others, while respecting each category of intelligence, is generally what serious education is about.”

## - Toni Morrison, author [The Source of Self-Regard](https://www.powells.com/book/-9780525521037?partnerID=44711)

---

class:center, middle
# Data Analysis Should Drive Decision Making
# This is what it means to be "data driven"
##

---

class:center, middle
# Data Analysis Should Drive Decision Making
# This is what it means to be "data driven"
## And good analysis should lead to good decisions

---
name:avp
# But It's Just One Part of a Process

![img-center-80](images/valuechain.png)

???
+ Facilitator introduces the idea of the Analytics Value Chain

---

class: middle
> "If you do not know how to ask the right question, you discover nothing."

## - W. Edward Deming
???
+ Sequeway to the brainstorming exercise (i.e. how do we learn to ask the right question)
---

name:brainstorm

# Our Method For Generating Ideas
???
+ Facilitator introduces the key method for brainstorming
--

![img-right-30](images/ideate.png)
+ **Ideate** - On your own, generate at least 3 ideas (ideally more), each on their own Post-It Note

####  
![img-center-30](images/postit_ex.jpg)
---

# Our Method For Generating Ideas
![img-right-30](images/discuss.png)
+ **Ideate** - On your own, generate at least 3 ideas (ideally more), each on their own Post-It Note
+ **Discuss** - Review the ideas generated

---

# Our Method For Generating Ideas
![img-right-30](images/ideate_discuss_decide_crop.png)
+ **Ideate** - On your own, generate at least 3 ideas (ideally more), each on their own Post-It Note
+ **Discuss** - Review the ideas generated
+ **Decide** - Come to a consensus as a group

---

# Group Exercise - [NYC Vision Zero](http://www1.nyc.gov/site/visionzero/index.page)
???
## Task: Students practice developing key questions around an analytical task unrelated to NYC Parks to encourage broad participation and engagement
## Outcome: Students gain experience formulating and defining an analytical question to be explored in data not directly connected to the work they generally perform
## Format: Individual, small group, large group facilitated ideation, co-creation, and discussion
## Start by asking participants: what do you know about vision zero?
## Being reading slides by setting the scene: As the best analysts in NYC, we've all been recruited by the mayor into the vision zero task force.
--

+ Roughly 4,000 New Yorkers are seriously injured and more than 250 are killed each year in traffic crashes
--

+ On average, vehicles seriously injure or kill a New Yorker every two hours
--

+ There are over 6,000 linear street miles and over 19,000 total lane miles in New York City
--

+ .red[What analytic questions would you want answered to reduce and eliminate traffic fatalities in NYC?]
![img-center-40](images/ideate_discuss_decide_horiz_crop.png)
???
+ At conclusion of exercise or during the break, facilitator attempts to develop key themes out of the ideation along the lines of [bundled ideas](http://www.designkit.org/methods/30)
---

exclude:true
class:center,middle
# Let's Look at Some Data
## [Click to Download Motor Vehicle Collisions<br>1 January 2017 - 1 July 2017](data/20170101_20170701_NYPD_Motor_Vehicle_Collisions.xlsx)

---

class:center,middle
# Wrap-Up
![img-center-100](images/imp_guide.png)
???
+ Facilitator concludes the exercise soliciting questions and introduces the implementation guide at the back of their workbook
---

class:center,middle
# 15 MIN BREAK

---

# Process Mapping
--

+ Allows you to identify and strategize for key steps in your analysis
--

+ Helps sequence tasks and identify gaps in understanding
--

+ Provides a basis for documenting work
---

# Process Mapping

![img-center-100](images/Makebreakfast.gif)
.caption[By Scottsm1991 (Own work) [CC BY-SA 3.0](http://creativecommons.org/licenses/by-sa/3.0), via Wikimedia Commons]

???
+ Facilitator does the "I do" phase by describing a past project that was mapped out

---

# Process Mapping (Our method)

![img-center-50](images/pm_ex.jpg)

---

exclude:true
# Process Map

![img-center-100](images/process_map_ex.jpg)
.caption[Analyzing pet licensing compliance in San Jose. Image Credit: Datapolitan [CC BY-SA 4.0](http://creativecommons.org/licenses/by-sa/4.0/)]

---

# How to Create a Process Map
???
+ Facilitator introduces the method for creating a process map. The language is still in flux. Find the language that is most authentic to your understanding that resonates with the students
--

![img-right-30](images/ideate_discuss_decide_crop.png)
+ Identify the key challenge
--

+ Identify the outcome
--

+ Identify key way to validate the outcome (outputs)<br>
--
_**How do we know we've got it right?**_

---

# Outcomes vs Outputs
--

+ Outcomes are the larger benefits and/or achievements you're trying to realize<br>
--
 (happiness, health, well-being, etc.)
--

+ Outputs are the tangible parts of your outcome<br>
--
 (survey responses, measured results, etc.)
--

+ Outputs enable us to find outcomes
--

+ Without outcomes, there is no need for outputs

---
name:process-map-comp
# How to Create a Process Map
![img-right-30](images/ideate_discuss_decide_crop.png)
+ Identify the key challenge
+ Identify the outcome
+ Identify key way to validate the outcome (outputs)<br>_**How do we know we've got it right?**_
--

+ Identify the key inputs (data, partners, supplies, finances, etc.)
--

+ Sequence the key questions to turn inputs into outputs

---

# Key Tips
--

![img-right-30](images/ideate_discuss_decide_crop.png)
+ Place each step on a Post-It Note

####  
![img-center-30](images/postit_ex.jpg)

---

# Key Tips
![img-right-30](images/ideate_discuss_decide_crop.png)
+ Place each step on a Post-It Note
+ Order and reorder as necessary
--

+ Some steps will need to be broken down

???
+ Facilitator identifies the key parts of the process based on the graphic presented
+ Faciliator reminds participants to follow the process that works best for them

---

exclude:true
# Building the Best Banana Split
![img-right-30](images/banana_split.png)
+ Identify the key result
+ Identify how to validate the result
+ Identify the key inputs
+ Brainstorm key steps to get inputs to the output

![img-center-45](images/ideate_discuss_decide_horiz_crop.png)

####  
.caption[Top <a href="http://creativecommons.org/licenses/by-sa/3.0/" title="Creative Commons Attribution-Share Alike 3.0">CC BY-SA 3.0</a>, <a href="https://commons.wikimedia.org/w/index.php?curid=964364">Link</a>; Middle by <a href="https://en.wikipedia.org/wiki/User:2candle" class="extiw" title="wikipedia:User:2candle">2candle</a> at <a href="https://en.wikipedia.org/wiki/" class="extiw" title="wikipedia:">English Wikipedia</a>, <a href="http://creativecommons.org/licenses/by-sa/3.0/" title="Creative Commons Attribution-Share Alike 3.0">CC BY-SA 3.0</a>, <a href="https://commons.wikimedia.org/w/index.php?curid=6698038">Link</a>; Bottom by [Flickr user Kristin Ausk](https://www.flickr.com/photos/kristinausk/), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/), [Link](https://www.flickr.com/photos/kristinausk/6830445704/in/photolist-bpzPxf-nBDSBJ-nkbey1-awHNhS-nBDRNa-nBrAVL-nDs5g6-nkaBE4-nzCd8L-d5iYiE-TopM85-fFNjqk-nBn71t-nDrddF-7Up5jm-9z2RTb-cWbBBE-cWbwoS-956Wb4-2ghxXH-7KHKAH-fDiXMm-2gmZ7Q-nDra2F-nBn8bz-nkbhjj-nBEWaV-nBnXSc-gmDdW2-7KMHC3-cWbCm9-Sdbg5R-ig33dG-cWbEbq-8PHvG6-cWbqdY-dfNyUw-cWbADG-8dBA6U-8gGzb3-9f99Mr-cWbDoC-51uMvp-cWbuZA-obXW3z-51uMoF-51uNy4-ebAhe-51z23U-51uN5K)]

???
+ A simple "We do" example to get the participants started with the process
+ Participants verbalize with facilitator the steps as necessary to achieve the result
+ Facilitator models the steps where necessary to demonstrate the desired results

---

# Vision Zero
[![img-right-35](images/traffic.jpg)](https://www.flickr.com/photos/danichro/8034920810/)
--

+ Identify the key challenge
--

+ Identify the outcome
--

+ Identify the outcome measures (outputs)
--

+ Identify the key inputs
--

+ Sequence the key questions to turn inputs into outputs

.right-caption[[Image](https://www.flickr.com/photos/danichro/8034920810/) by [Flickr user Damianos Chronakis](https://www.flickr.com/photos/danichro/) [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

![img-center-35](images/ideate_discuss_decide_horiz_crop.png)

???
+ The more involved "We do" phase where we do this together

---

# The Data Analytics Value Chain

![img-center-80](images/valuechain.png)

---

# What did you notice about this process?
???
+ Facilitator reflects on the exercise and what participants are taking away from the experience
--

+ Knowing the problem and sequencing the steps can be harder than working with data
--

+ Having these answers can make the analysis much easier
--

+ This is a process you can do with any challenge, no matter how big
--

+ Everyone has something to add, no matter how technical (or non-technical) they are

---

class:center,middle
# LUNCH

---
name:lunch
class:center, middle
# WELCOME BACK!

---

class:center,middle
# Check-In
## What questions do you have?

---

# Thinking About NYC Parks
[![img-center-80](images/park.jpg)](https://www.flickr.com/photos/p-davidson/4483159007/)
.caption[Image Credit: [Beamish417](https://www.flickr.com/photos/p-davidson/), used under [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/) license]

???
+ Facilitator shifts the frame of thinking to NYC Parks
---

class:center,middle
# Process Mapping with Parks Data

---
exclude:true
# Getting Started
+ Identify the project manager
+ Identify a recorder

???
+ Facilitator has groups identify key roles for working on the projects
+ Data driver should be the least experienced data person in the group to help facilitate learning

---

# Step 1 - Defining the Challenge
???
+ Students employ the same problem definition approach they used with the Vision Zero exercise to focus on an NYC Parks-specific problem
+ The intention is for them to utilize the skills we've modeled
+ Facilitator will seed questions for them to potentially think about
--

![img-right-30](images/ideate_discuss_decide_crop.png)
--

+ Think about a key challenge or issue in NYC Parks, that, if solved, could have an impact
--

+ Find something that interests you 
--

+ .green[**Don't let the available data limit your exploration of the challenge**]

---

# Step 2 - Defining the Outcome
--

+ What will come out of your exploration of this problem?
--

+ How does it relate to your question/challenge?
--

+ How will the output benefit decision-makers?

---

# Step 3 - Define the Outputs
--

+ How will we know we've made an impact?
--

+ How will we tell the story of what we've done?

---

# Step 4 - Defining the Inputs
--

+ What sources of information can you use for this problem?
--

+ What other inputs might you need (buy-in, interviews, etc)
--

+ What is the relative priority of these to your project?

---

exclude:true
# Step 1b - Deciding the Type of Analysis
![img-center-60](images/nolytics_type_stacked.png)

+ "I want to know X so I can do Y"

---

exclude:true
# Step 2 - Collection
+ Identify data you'd want to work with
+ What would you expect your problem to look like in data?

---

exclude:true
# Step 3 - Cleaning
+ What do you think you'll have to do with the data?
+ How will you get this done?

---

exclude:true
# Step 4 - Test Hypothesis
+ What are your assumptions about the data?
+ How will you know if these are true?
+ How will you identify things you don't know?

---

exclude:true
# Step 5 - Verify Results
+ How will you check what you have?
+ How will you know if you're right?

---

exclude:true
# Step 6 - Visualize and Communicate
+ How will you tell the story of what you've found?
+ How will they best understand it?

---

exclude:true
+ Take the problem you've developed in your group and come up with a map for how to answer it
+ Acknowledge the knowns and unknowns
+ Develop an overall approach to answering the question
+ Don't be afraid to scale back your question if it seems too big

???
+ Students employ the same process mapping skills we've modeled with Vision Zero to their Parks-specific problem
+ The intention is to get them to think big and then scale back for time and ability with the data

---

class:center,middle
# Wrap-Up
???
+ Facilitator reflects with groups on what they've come up with so far and moves them into the computer lab, if appropriate

---

class:center,middle
# 15 MIN BREAK

---
exclude:true
class:center,middle
# Context for the data we're looking at today
???
+ Facilitator provides review of Parks-specific data
+ Often this is best done by asking if anyone is familiar with the data and having them share the details. Otherwise necessary details are listed on the slides

---

exclude:true
# Service Level Agreements (SLAs)
+ Minimum number of times a property must be serviced weekly
+ A: 5–7 visits
+ B: 3–5 visits
+ C: 1 visit
+ SLA level designated by borough

---

exclude:true
# Daily Tasks
+ Labor booking for routine litter and cleaning

![img-center-90](images/dt_web.png)

---
exclude:true

# Supervisor Inspections
+ All sites should be inspected following Parks Inspection Program guidelines a minimum of once a month
+ Sites receive an Overall rating for all elements 
+ Sites also receive a Cleanliness rating 
+ Cleanliness rating only addresses the features addressed by the staff entering work in Daily Tasks

---

class:center,middle
# Let's Get Some Data

---
exclude:true
# Definition of Open Data
> Open data is data that can be freely used, shared and built-on by anyone, anywhere, for any purpose

## - [Open Knowledge International](http://blog.okfn.org/2013/10/03/defining-open-data/)

---

exclude:true
# Key Features of Open Data
+ Availability and access
+ Reuse and redistribution
+ Universal participation

---

exclude:true
# Open Data Benefits
+ Transparency
+ Releasing social and commercial value
+ Participation and engagement

---

exclude:true
# Keeping NYC Accountable on Parking Tickets

![img-center-75](images/nyc_parking_ticket.jpg)
.caption[Image Credit: Parking Violator by [Atomische * Tom Giebel](https://www.flickr.com/photos/atomische/2299948817/), [CC BY-NC-ND 2.0 ](https://creativecommons.org/licenses/by-nc-nd/2.0/)]

Source: http://iquantny.tumblr.com/post/87573867759/success-how-nyc-open-data-and-reddit-saved-new

---

exclude:true
# Open Data Concerns
+ Privacy (Personally identifiable information (PII), Mosaic Effect)
+ Confidentiality
+ Security

---

exclude:true
# When Good Data Goes Bad

![img-center-70](images/nyctaxi.jpeg)
.caption[Image Credit: Kenny Louie, [CC BY 2.0](http://creativecommons.org/licenses/by/2.0), via [Wikimedia Commons](https://commons.wikimedia.org/wiki/File%3ANYC_TAXI_(7038011669).jpg)]

[Gawker matches Taxi and Limousine Data with Paparazzi Photos](http://gawker.com/the-public-nyc-taxicab-database-that-accidentally-track-1646724546)

---

class: center,middle
# We're going to be using data from the<br> <a href="https://opendata.cityofnewyork.us/" target="_blank">NYC Open Data Portal</a> for the first part of today's data exercise

## We'll be using [Vehicle Collision Data](data/20160401_0630_NYPD_Motor_Vehicle_Collisions.xlsx)
???
+ Facilitator introduces the NYC Open Data Portal as appropriate for the class and the time available. 
---
exclude:true
# Data for Exercise
![img-center-90](images/dt_download_rev.png)

#### [Click to download if you have problems](data/20170601_20170630_DailyTasks_Work_GISPROPNUM_NotNull.csv)
???
+ If necessary, downloading the data straight from the link will help save time.
+ Note the data filters to remove the blank fields that mess up the data. Note this with the participants
---

exclude:true
class:center,middle
# Overview of Excel

---

exclude:true
# Benefits of Excel
+ Easy to use
+ Very visual
+ Lots of features and functions
+ Easy to make charts
+ Does a lot of formatting for you

???
+ Poll class for their key challenges working with Excel
+ Discuss the ways in which Excel is beneficial in daily work

---

exclude:true
# Drawbacks of Excel
+ Not very intuitive
+ Hard to find what you’re looking for (and they keep moving things around)
+ Lots of features and functions
+ Easy to make (bad) charts
+ Does a lot of formatting for you

???
+ Poll class for their key challenges working with Excel
+ Discuss the ways in which Excel is challenging in their daily work

---

exclude:true
# To Err is Human

> Since 1995, 88% of the 113 spreadsheets audited in 7 studies ... have contained errors.

## - Raymond Panko, "[What We Know About Spreadsheet Errors](http://panko.shidler.hawaii.edu/My%20Publications/Whatknow.htm)"

---

exclude:true
# Spreadsheet Errors Have Consequences

![img-center-80](images/athensprotest.jpg)
.caption[Image Credit: [http://underclassrising.net](https://www.flickr.com/photos/0742/4163565280), [CC BY-SA 2.0](https://creativecommons.org/licenses/by-sa/2.0/)]

---

exclude:true
# Finding Context

![img-center-80](images/context.png)

---

exclude:true
# Exploratory Data Analysis
+ Goal -> Discover patterns in the data
+ Understand the context
+ Summarize fields
+ Use graphical representations of the data
+ Explore outliers

####Tukey, J.W. (1977). Exploratory data analysis. Reading, MA: Addison-Wesley

---

exclude:true
# Steps to Prepare This Data
+ Filter for "Work" as the `activity`
+ Summarize by Park
+ Find the Borough for Each Site
+ Visualize Results

???
+ Task: Students follow a guided exercise in key data manipulation tasks using data related to the challenge, including 
+ Formatting
+ Sorting/filtering
+ Aggregating (PivotTables)
+ Cleaning/manipulating
+ Visualizing
+ Outcome: Students are practiced in key data analytics task with Parks-specific data following the general outlines of the problem ideation and process mapping work they performed in the morning
+ Format: Guided exercise and facilitated discussion
+ Outline
+ Introduction to key analytical questions
+ Guided task demonstration
+ Wrap-up

---

exclude:true
# Filter the Activity Column

![img-center-80](images/filter1_rev.png)

---
exclude:true

# Filter the Activity Column

![img-right-40](images/filter2_rev_trim.png)
+ Uncheck "Select All"
+ Check "Work"
+ The color of row labels will change to show you're filtering rows

![img-left-50](images/filter3_box_rev.png)

---

class:center,middle
# Looking at this Data

## What Questions Do We Want to Ask?
???
+ Facilitator prompts participants to begin generating ideas to be motivate learning with the dataset
---

# PivotTables
--

+ A data summarization and comparison tool for quickly understanding and displaying the data you’re analyzing
--

+ Find it on the Insert ribbon
--

![img-center-60](images/pivottable1.png)

---

# PivotTables
![img-right-50](images/pivottable2.png)
+ Select the range and destination
--

+ Should default to all your data and a new sheet

---

# PivotTables
![img-right-30](images/pivottable3_border.png)
--

+ Click and drag the column headers into the boxes
--

+ **Filters** -> allows you to filter on the values in that field
--

+ **Rows** -> puts the values of that field in the rows of the pivottable
--

+ **Columns** -> puts the values of that field into the columns of the pivottable
--

+ **Values** -> determines how the values of the rows and columns are counted

---
exclude:true
# Summarize by Zone
+ How would we summarize the hours worked by Zone?

<br>

![img-center-40](images/filter_zone.png)

## What are we telling Excel to do?

---

exclude:true 
+ What are some of the questions you'd like to know by property?

![img-center-70](images/hours_worked_borough.png)

???
+ A short brainstorming exercise for the different operations that can be performed on the data

---
exclude:true

# Finding the Borough for Each Site
+ What if we want to aggregate by borough?
+ Can we do that easily with this data?

---

# Functions in Excel
--

+ Allow you to do calculations in Excel
--

+ Also allow you to manipulate text
--

+ And a whole lot more...
---

name:cat-function
exclude:true

# Some Categories of Functions in Excel
+ Database functions
+ Date and time functions
+ Financial functions
+ Logical functions
+ Lookup and reference functions
+ Math and trigonometry functions
+ Statistical functions
+ Text functions
+ 13 categories in total
+ [Click for a list of some useful functions](#excel-guide)

---

exclude:true
# Extracting the Borough
+ Insert a new column after `district`
+ Call it `Borough`
+ Insert the following formula into cell .blue[I2]

```
=LEFT(H2,1)
```

+ Copy the formula down through all the cells
+ More information on the [LEFT function](https://support.office.com/en-us/article/LEFT-LEFTB-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)

---

exclude:true
# Extracting the Borough
![img-right-35](images/pivottable4_refresh.png)
+ Be sure to refresh your pivottable
+ Filter on activity `Work` and sum by number of hours

![img-center-30](images/pivottable5_box.png)

+ Filter out the empty value

---

exclude:true
# Extracting the Borough
+ You can retype entries in a pivottable
+ For example, you can change the boroughs just by typing over the letters

![img-center-100](images/pivottable6_rev.png)

---

exclude:true
# Extracting the Borough
+ You could also write a formula

```
=IF(LEFT(H2,1)="M","Manhattan",
	IF(LEFT(H2,1)="X","Bronx",
	IF(LEFT(H2,1)="B","Brooklyn",
	IF(LEFT(H2,1)="Q","Queens",
	IF(LEFT(H2,1)="R","Staten Island","")))))
```
???
+ This isn't necessary and should only be taught if appropriate for the understanding of the class and the time available for instruction

#### [Sheet with formulas](data/20160601_20160630_Daily_Tasks_Park_Cleaning_Records_formulas.xlsx)

---

exclude:true
class:center,middle
# Supervisor Inspections

## You can find this on the Intranet:

<p style="font-size:1.55em">
    <a href="http://wss.parks.nycnet/IPM/analytics/Lists/Analytics%20Training/AllItems.aspx" target="_blank">http://wss.parks.nycnet/IPM/analytics/Lists/Analytics%20Training/AllItems.aspx
  </a>
</p>

???
+ This for reference only
---
name:final-ex
exclude:false

# Your Turn
+ Take the problem you defined
+ Follow your map
+ We'll be here to help
+ Make sure you're documenting your steps and save your work
+ Someone is going to come behind you to look at and explain your work
+ Feel free to use the [list of useful functions](#excel-guide)

???
+ Task: Students work as a group to develop key insights and tell the story as it relates to the problem defined in the morning session, documenting their work as they go for presentation to their fellow students
+ Outcome: Students have implemented the process they outlined in morning session and documented their steps for review and feedback from their peers
+ Format: Mentored small group work
+ Outline
+ Introduction to task
+ Small group work

---

exclude:true
# Verification
+ Review the documentation the group provided
+ Rerun the analysis (without losing their work)
+ Be ready to present their work to the class

???
+ Task: As a group, review the work of another group, reviewing documentation and verifying analytical steps for accuracy, completeness, and ability to answer the initial question
+ Outcome: Students practice reviewing documentation and communicating findings to an audience, as well as giving and receiving constructive feedback in a supportive environment
+ Format: Small group presentations and facilitated discussion
+ Outline
+ Students as a group review the work of another group and verifying results
+ Students present the work of the group they reviewed to the class for brief discussion and feedback

---

exclude:true
# Links to Other Open Data Portals
+ New York State - https://data.ny.gov
+ US Federal Government - https://www.data.gov/

---

exclude:true
# Value of Good Operational Analysis

![img-center-55](images/cincinnatilatefees.jpg)
.caption[Image Credit: Money by Andrew Magill, [CC BY 2.0](https://creativecommons.org/licenses/by/2.0/), via Flickr]

## City of Cincinnati saved $130,000 in late fees to utility company
[Learn more](http://www.charlotteobserver.com/news/business/article32617293.html)

---

exclude:true
# Tracking Hurricane Sandy Relief Funds

![img-center-70](images/sandytracker.png)
.caption[Image Credit: The City of New York, via [NYC.Gov](http://www1.nyc.gov/sandytracker/#2891665754)]

---
exclude:true
# A Word on Performance Metrics
+ If it doesn't change your behavior, it's a bad metric

![img-center-40](images/badmetric.png)
.caption[Image Credit: Jurgen Appelo, [CC BY 2.0](https://creativecommons.org/licenses/by/2.0/), via [Flickr](https://www.flickr.com/photos/jurgenappelo/8485492756)]

---

exclude:true
# Goodhart's Law

>“When a measure becomes a target, it ceases to be a good measure.”

## - Charles Goodhart, Economist

---

class:center,middle
# WRAP-UP

---

# What We've Covered
--

+ Problem definition
--

+ Process mapping
--

+ What else?

---

class:center,middle
# Some Key Ideas We Want to Leave You With

---

name:ddc
# Data Driven Culture
![img-center-95](images/ddc_compass.png)

???
+ A quick tag to go over the Data Driven Culture concept and give them the Arsenault quote based [on the work of Carl Anderson](https://www.slideshare.net/CarlAnderson4/ddo-overview)

---

# Data Driven Culture
![img-center-95](images/ddc_compass_goals.png)
???
+ Facilitator emphasizes the importance of having goals in being a data driven organization

---

# Data Driven Culture
![img-center-95](images/ddc_compass_data_literacy.png)
???
+ Facilitator discusses the role of data literacy to creating a data driven organization
---

# Data Driven Culture
![img-center-95](images/ddc_compass_sharing.png)
???
+ Facilitator discusses the role of having an open sharing culture to creating a data driven organization
---

# Data Driven Culture
![img-center-95](images/ddc_compass_leadership.png)
???
+ Facilitator describes the role of the data evangelist and their role in creating a data driven organization
---

# Data Driven Culture
![img-center-95](images/ddc_compass_questions.png)
???
+ Facilitator describes the role of an inquisitive & questioning culture
---

# Data Driven Culture
![img-center-95](images/ddc_compass_learning.png)
???
+ Facilitator describes the value of learning to creating a data driven organization
---

# Data Driven Culture
![img-center-95](images/ddc_compass_testing.png)
???
+ Facilitator describes the value of testing assumptions and gathering data to either confirm or deny what we believe to be true
---

# Data Driven Culture
![img-center-95](images/ddc_compass_self_service.png)
???
+ Facilitator describes the value of allowing users to access data directly in their work
---

# Data Driven Culture
![img-center-95](images/ddc_compass_culture.png)
???
+ Facilitator brings each component back to building the overall data driven culture
---

# Data Driven Culture
![img-center-95](images/ddc_compass.png)

---
exclude:true
# Reports don't make you data driven

![img-center-45](images/datadriven_reports.png)
.caption[Image Credit: RRZEicons, [CC BY-SA 3.0](http://creativecommons.org/licenses/by-sa/3.0), via [Wikimedia Commons](https://commons.wikimedia.org/wiki/File:Report.svg)]

---
exclude:true
# Dashboards don't make you data driven

![img-center-60](images/datadriven_dashboard.png)
.caption[Image Credit: Chabe01, [CC BY-SA 4.0](http://creativecommons.org/licenses/by-sa/4.0), via [Wikimedia Commons](https://commons.wikimedia.org/wiki/File%3ADashboard_OS_X.svg)]

---
exclude:true
# Alerts don't make you data driven

![img-center-60](images/datadriven_alert.png)
.caption[Image Credit: Tokyoship, [CC BY-SA 3.0](http://creativecommons.org/licenses/by-sa/3.0), via [Wikimedia Commons](https://commons.wikimedia.org/wiki/File%3ASimple_Alert.svg)]

---

# Data Driven Culture
> “Do you have data to back that up?” should be a question that no one is afraid to ask (and everyone is prepared to answer)

## - Julie Arsenault

[Source](https://www.pagerduty.com/blog/how-to-create-a-data-driven-culture/)
???
+ Facilitator offers the quote and invites reflection
+ Optionally, facilitator can introduce the HiPPO idea (Highest Paid Person's Opinion) and the value of being anti-HiPPO to be data-driven, as well as the difficulty of doing that in government
---

name:final
# Final Thoughts
--

+ Don't try to boil the ocean
--

+ Data can tell a story, but doesn't speak for itself
--

+ Good analysis is about telling a true and compelling story
--

+ Be good to your data and it will be good to you
--

+ Try to focus on outcomes, not just outputs

---

# Resources
--

+ [Microsoft Office Support](http://office.microsoft.com/en-us/support/) - Documentation on various MS Office products
--

+ [Microsoft Office Community](https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel?sort=LastReplyDate&dir=Desc&tab=All&status=all&mod=&modAge=&advFil=&postedAfter=&postedBefore=&threadType=All&isFilterExpanded=false&page=1) - Community Q&A Board
--

+ [IDEO Design Kit](http://www.designkit.org/) - Toolbox for Design Thinking techniques
--

+ [Data Science Cheatsheet](https://github.com/govex/Data-Science) - Includes various terms and concepts related to data science
--

+ [Copy of today's handout](workbook.pdf)
--

+ [Datapolitan training classes](http://training.datapolitan.com/)

---

# Classes in This Series
+ Introduction to Data Analytics (this class)
+ [Excel for Data Analysis I](http://training.datapolitan.com/parks-excel-analysis-i/)
+ [Excel for Data Analysis II](http://training.datapolitan.com/parks-excel-analysis-ii/)
+ [Advanced Analysis with SQL](http://bit.ly/advanced-analysis-sql)
+ [Advanced Analysis with GIS](http://bit.ly/advanced-analysis-gis)

---

# .center[Contact Information]

<!-- ## Richard Dunks
<ul>
    <li style="font-size:35px">
        Email: richard[at]datapolitan[dot]com
    </li>
    <li style="font-size:35px">
        Website: <a href="http://www.datapolitan.com">http://www.datapolitan.com</a>
    </li>
    <li style="font-size:35px">
        Twitter: <a href="https://twitter.com/datapolitan">@datapolitan</a>
    </li>
</ul>

### Elana Shneyer 
<ul>
    <li style="font-size:35px">
        Email: elana[at]datapolitan[dot]com
    </li>
</ul> -->

## Elizabeth DiLuzio
+ Email: elizabeth[at]datapolitan[dot]com
+ Website: <a href="http://www.evallearn.com">http://www.evallearn.com</a>
+ Twitter: <a href="https://twitter.com/lizdiluzio">@lizdiluzio</a>

## Gemma Duffee
<ul>
    <li style="font-size:35px">
        Email: duffg290[at]newschool[dot]edu

<!-- ## Sarah Kontos
<ul>
    <li style="font-size:35px">
        Email: kontoss[at]gmail[dot]com

## Eric Brelsford
<ul>
    <li style="font-size:35px">
      Email: ebrelsford[at]gmail[dot]com]
    </li>
    <li style="font-size:35px">
      Twitter: [@ebrelsford](https://twitter.com/ebrelsford)
    </li>
</ul>
 -->

<!-- ## Contact Information

## Julia Marden
+ Email: julia[at]tinypanther[dot]com
+ Website: http://tinypanther.com
+ Twitter: [@juliaem](https://twitter.com/juliaem)

## Elana Shneyer
+ Email: elana[at]datapolitan[dot]com -->

---

class:center, middle
# THANK YOU!

---

# Step 5 - Outline the Process
???
+ If participants haven't already sequenced their questions into a process for their project, they would do that now in order to have an outline of the work they would do with the data available
--

+ What are the steps to creating our outputs?
--

+ What is the best order of steps?
--

+ How granular do we need to break this down for clarity?

## .center[.green[Collection<br>Verification<br>Description<br>Sensemaking<br>Communication/Visualization]]

---

# What did you notice about this process?
???
+ Facilitator reflects on the protocol with the participants for their feedback
+ May be omitted if not necessary
--

+ Knowing the problem and sequencing the steps can be harder than working with data
--

+ Having these answers can make the analysis much easier
--

+ This is a process you can do with any challenge, no matter how big
--

+ Everyone has something to add, no matter how technical (or non-technical) they are

---

name:nola
class:center,middle
# Types of Analysis
[![img-center-90](images/nolytics_header.png)](https://datadriven.nola.gov/home/)
[![img-center-90](images/nolytics_footer.png)](https://datadriven.nola.gov/home/)

Based on the work of the [City of New Orleans, Office of Performance and Accountability](https://datadriven.nola.gov/home/)

???
+ Facilitator goes through the types of analysis with participants to introduce key ideas
+ Facilitator connects each type to either the process map they've created or to the their own work

---

class:center,middle
![img-center-90](images/nolytics_type_stacked.png "Copyright: Harvard University Ash Center https://ash.harvard.edu and used with permission")

---

# Finding a Needle in Haystack
![img-right-15](images/needle1_rev.png)
--

+ Challenge: Targets are difficult to identify or locate within a broader population
--

+ Opportunity: Data analysis and predictive modeling to identify targets based on existing data

---

# New Orleans Distributes Smoke Alarms

![img-center-40](images/neworleansfire.jpeg)
.caption[Image Credit: Michael Barnett [CC BY-SA 2.5](http://creativecommons.org/licenses/by-sa/2.5), via Wikimedia Commons]

---

# Targeted Outreach Saves Lives

![img-center-90](images/nolasmokealarm.png)
.caption[Image Credit: City of New Orleans, via [nola.gov](http://nola.gov/performance-and-accountability/nolalytics/files/full-report-on-analytics-informed-smoke-alarm-outr/)]

---

# Targeted Outreach Saves Lives

![img-center-90](images/nolaimpact.png)
.caption[Image Credit: City of New Orleans, via [nola.gov](http://nola.gov/performance-and-accountability/nolalytics/files/full-report-on-analytics-informed-smoke-alarm-outr/)]

---

# Prioritizing Work for Impact
![img-right-15](images/prior1.png)
--

+ Challenge: Services do not categorize high-priority cases early
--

+ Opportunity: Data analysis and predictive modeling to prioritize cases
---

# NYC Restaurant Inspectors Save Time

![img-center-65](images/greasetrap_csessums.jpg)

.caption[Image Credit: Grease Trap Cover Asphalt by [Christopher Sessums](https://www.flickr.com/photos/csessums/4740455231), [CC BY 2.0](https://creativecommons.org/licenses/by/2.0/)]

.center[Source: http://www.nyc.gov/html/dep/html/press_releases/12-71pr.shtml#.W6v2nRNKhZo]

---

# Early Warning Tools
![img-right-15](images/early1.png)
--

+ Challenge: Resources overly focused on reactive services
--

+ Opportunity: Developing tools to predict need based on historic patterns

---

# Using Data to Fight Fires

![img-center-90](images/fires.png)
.caption[Image Credit: [NYC Mayor's Office of Data Analytics](http://www1.nyc.gov/site/analytics/index.page), [2013 Annual Report](https://assets.documentcloud.org/documents/1173791/moda-annual-report-2013.pdf)]

---

# Better, Quicker Decisions
![img-right-15](images/decisions1.png)
--

+ Challenge: Repeated decisions are made without access to all relevant information
--

+ Opportunity: Developing recommendation tools for operational decisions

---

# Cincinnati Targets Urban Blight

![img-center-40](images/cincinnatiurbanblight.jpg)
.caption[Image Credit: Wholtone, via [Wikimedia Commons](https://commons.wikimedia.org/wiki/File:Abandoned-Over-the-Rhine-building.jpg)]

[Learn more](https://dssg.uchicago.edu/2015/08/20/blight-prevention-building-strong-and-healthy-neighborhoods-in-cincinnati/)

---

# Optimizing Resource Allocation
![img-right-15](images/optimize1.png)
--

+ Challenge: Assets are scheduled or deployed without input of latest service data
--

+ Opportunity: Use data to drive decisions on the deployment of resources

---

# Shortening Public School Bus Rides
![img-center-80](images/boston-public-bus.jpg)

Image Credit: [Patrick Hudepohl](https://www.flickr.com/photos/patrick-hudepohl/), used under [Creative Commons BY-NC-SA 2.0 license](https://creativecommons.org/licenses/by-nc-sa/2.0/).

[Source](http://www.datakind.org/blog/datakind-and-sas-to-help-boston-public-schools-shorten-school-bus-rides-lower-costs)
---

# Experimenting for What Works
![img-right-15](images/experiment1.png)
--

+ Challenge: Services have not been assessed for impact
--

+ Opportunity: Experimental testing and improvement of service options

---

# Redesign of NYC Summonses
![img-center-65](images/summons.png)

Image credit [City of New York](https://www1.nyc.gov/site/criminaljustice/work/summons_reform.page). [Click for more information](http://www.ideas42.org/blog/new-behaviorally-informed-nyc-summonses-hit-streets/)

---

class:center,middle
# It's important to know what type of analysis to use for the right problem

![img-center-80](images/wrong_tool.jpg)
Source: Justin Baeder, [link to original](https://www.flickr.com/photos/justinbaeder/5317820857)

---

name:excel-guide
# Key Excel Functions for Summarizing Data
+ [`=SUM()`](https://support.office.com/en-us/article/SUM-function-043e1c7d-7726-4e80-8f32-07b23e057f89): Calculates the sum for a range of numbers
+ [`=COUNT()`](https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c): Counts the number of cells containing numbers in a range
+ [`=COUNTA()`](https://support.office.com/en-us/article/COUNTA-function-7dc98875-d5c1-46f1-9a82-53f3219e2509): Counts the number of non-blank cells in a range
+ [`=HOUR()`](https://support.office.com/en-us/article/HOUR-function-a3afa879-86cb-4339-b1b5-2dd2d7310ac7): Extracts the hour from a timestamp
+ [`=WEEKDAY()`](https://support.office.com/en-us/article/WEEKDAY-function-60e44483-2ed1-439f-8bd0-e404c190949a): Extracts the day of the week from a timestamp
+ [`=CHOOSE()`](https://support.office.com/en-us/article/CHOOSE-function-FC5C184F-CB62-4EC7-A46E-38653B98F5BC): Uses an index number to return a result from an ordered list of values

---

# Key Excel Functions for Summarizing Data
+ [`=MID()`](https://support.office.com/en-us/article/MID-MIDB-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028): Select a specified number of characters from a text string
+ [`=LEFT()`](https://support.office.com/en-us/article/LEFT-LEFTB-functions-9203d2d2-7960-479b-84c6-1ea52b99640c): Select a specified number of characters from the beginning of a text string
+ [`=RIGHT()`](https://support.office.com/en-us/article/RIGHT-RIGHTB-functions-240267ee-9afa-4639-a02b-f19e1786cf2f): Select a specified number of characters from the end of a text string
+ [`=FIND()`](https://support.office.com/en-us/article/FIND-FINDB-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628): Find the location of a given character in a text string

#### [Link back to Categories of Functions](#cat-function)
#### [Link back to Final Exercise](#final-ex)

---

# Key Excel Functions for Summarizing Data
+ [`=CONCATENATE()`](https://support.office.com/en-us/article/CONCATENATE-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d): Combine characters together into a text string
+ [`=VLOOKUP()`](https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1): An operation to look up a value in another location based on an index value 
+ [`=IF()`](https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2): A function for logical comparison between values to return a desired result given a particular condition

#### [Link back to Categories of Functions](#cat-function)
#### [Link back to Final Exercise](#final-ex)

---
exclude:true
name:vlookup
class:center,middle
# How often do you need to combine data from different spreadsheets?

---
exclude:true

# [VLOOKUP](https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)
+ Useful for referencing data from another sheet (reference tables)
+ Basic Syntax
```
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
```
+ Example
```
=VLOOKUP(A2,SHEET2$A1:$D500,4,FALSE)
```
+ Lookup the value in cell A2 in the range A1:D500 on SHEET2 and return whatever you find in column 4 (D)

---
exclude:true

# VLOOKUP
+ We're going to write a formula to bring in the Sites information to the Features table
+ Insert a column next to `inspection_id`
+ This will be our key lookup value
+ Call the column "district"
+ Use this formula

```
=VLOOKUP(A2,AMPSInsExtract_Jun2016_Sites_20170613.csv!$A$1:$F$4841,2,FALSE)
```
+ Do the same for "propid" and extract the borough with a formula
+ Add "inspection_date" if that looks interesting to you