Advanced Analysis of NYC 311 Data

layout:true

Advanced Analysis of NYC 311 Data by <a xmlns:cc="http://creativecommons.org/ns#" href="http://www.datapolitan.com" property="cc:attributionName" rel="cc:attributionURL">Richard Dunks</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>

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

# Advanced Analysis of NYC 311 Data
### Or How I Learned to Stop Worrying and Love Spreadsheets

- - -

## Instructor: Richard Dunks
### Follow along at: http://bit.ly/advanced-spreadsheet

#### See the code at: http://bit.ly/advanced-spreadsheet-code

---

class:center,middle
# Welcome

---

# Introduce Yourself to Your Neighbor
+ Who you are
+ Where you work
+ What you've made a 311 complaint about

---

name:assumptions
# Assumptions
--

+ You've got some experience with analyzing data
--

+ You understand the fundamentals of spreadsheets
--

+ You're all at different levels
--

+ We're probably not all working with Excel or the same version of Excel

---

# What to Expect Today
--

+ A discussion of data and the practice of analysis
--

+ A good conversaton about open data
--

+ Work with open data in its natural (dirty) state
--

+ Progressively more complex exercises with data
--

+ Some fun along the way

---

# Questions to Explore
--

+ What are the patterns to 311 Service Requests?
--

+ How does that look when we take into account population?
--

+ What are the patterns to how 311 Service Requests are closed? (If we have time)
--

+ Is there a correlation between factors such as median income to how long a 311 Service Request stays open? (If we have time)

---

# The Value of Data
--

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

+ Can describe the what happened directly or indirectly
--

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

---

class: center,middle
# Are All Data Points Created Equal?

---

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*

---

# What is Analysis?
--

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

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

---

# 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/)

---

# Key Features of Open Data
--

+ Availability and access
--

+ Reuse and redistribution
--

+ Universal participation

---

# Open Data Benefits
--

+ Transparency
--

+ Releasing social and commercial value
--

+ Participation and engagement

---

# Open Data Concerns
--

+ Accuracy
--

+ Privacy (Personally identifiable information (PII), Mosaic Effect)
--

+ Security

---

class:center,middle
# Links to NYC Open Data Portal for Exercise
## <a href="https://opendata.cityofnewyork.us/" target="_blank">NYC Open Data Portal</a>

---

# Data for Exercise
![img-center-95](images/portal_exercise.png)
.caption[[Click to download the data](https://s3.amazonaws.com/datapolitan-training-files/20170101_20170917_311_Noise.csv)]
---

# 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

---

# Key Tasks in Doing EDA
--

+ Sorting
--

+ Filtering
--

+ Aggregating
--

+ Manipulating
--

+ Visualizing

---

class:center,middle
# Looking at Noise in New York City

---

# Filtering
![img-center-100](images/filtering1.png)

---

# Filtering
![img-center-40](images/filtering2.png)

---

# Conditional Formatting
![img-center-65](images/cf1.png)

---

# Conditional Formatting
![img-center-80](images/cf2.png)

---

# PivotTables
--

+ A data summarization tool
--

+ Useful to quickly understand data
--

+ Can use to graph data totals
--

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

---

# Creating a PivotTable
![img-center-60](images/pt2.png)
--

+ Should default to all your data .red[unless you have any cells selected]
--

+ Should default to a new worksheet

---

# Creating a PivotTable
![img-right-40](images/pt3.png)
## Drag and drop fields to visualize
+ Row labels 
+ Values
+ Filter
+ Column Labels

---

# Creating a PivotTable
![img-center-100](images/pt4.png)

---

# Your Turn
--

+ Working in groups, explore the data
--

+ Identify a key insight you gained
--

+ This can be a trend or an outlier
--

+ It can also be a data quality issue you noticed
--

+ Be prepared to describe what you found to the whole class

---

class:center,middle
# Lunch

---

# Remember - What is Analysis?
--

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

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

---

class:center,middle
# Should we compare the CDs directly?
![img-center-50](images/cd_map.png)
## Why or why not?

---

class:center,middle
# Your Task

![img-center-50](https://media.giphy.com/media/FM0uwEP45RUly/giphy.gif)

# Compare the number of noise-related 311 Service Requests by population

---

class:center,middle
# [Population Data from the NYC Department of City Planning](https://www1.nyc.gov/site/planning/data-maps/nyc-population/census-2010.page)

[![img-center-80](images/pop_data.png)](https://www1.nyc.gov/site/planning/data-maps/nyc-population/census-2010.page)

---

class:center,middle
# Data Cleaning
## How do we make this data usable?
![img-center-70](images/cd_pop_table.png)

.caption[[Click to download](https://s3.amazonaws.com/datapolitan-training-files/2010_NYC_CD_population.xlsx) the cleaned data]
---

class:center,middle
# Now how do we join it with the 311 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
# 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)

---

# [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)

---

# VLOOKUP
+ We're going to use a VLOOKUP to pull the CD population into the counts of noise-related 311 Service Requests

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

# Preparing the Population Sheet
--

+ We need to create the exact same unique identifier in the population sheet
--

+ How do we do this? (Give it a try)
--

+ Hint: Look at the [CONCATENATE function](https://support.office.com/en-us/article/CONCATENATE-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d)
--

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

.caption[Check out the [Formulas section](#formulas) if you have any trouble]
---

# VLOOKUP
--

+ Create a count of the 311 Service Requests by CD
--

+ Try to write the VLOOKUP to pull in the population
--

+ Verify your result
--

+ Work together to figure it out
--

+ Try applying some conditional formatting

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

.caption[Check out the [Formulas section](#formulas) if you have any trouble]
---

class: center,middle
# Let's look at the time 311 Service Requests are open

---

# Steps
--

+ Subtract the `Closed Date` from the `Created Date`
--

+ The result is the fraction of a day that a complaint was open
--

+ Calculate the hours open by multiplying by 24
--

+ You'll have to do some formatting on the column
--

+ Look through the data to verify [the formula](#formulas)

---

# Your Turn
--

+ Look through the `Hours Open` and see if you can see any trends
--

+ Identify any data concerns and what you might do about them
--

+ Be prepared to discuss with the group
--

+ Remember you can change the values in your pivot table to calculate averages
![img-right-60](images/value_field.png)
--

+ .red[And don't forget to refresh your pivot table]

---

# What were the Data Issues You Noticed?
--

+ Incidents without `Closed Dates`
--

+ Incidents with `Closed Dates` before the `Created Date`
--

+ Anything else?

# What do we do about these?

---
class:center,middle
# How might factors like median income affect how long a 311 Service Request stays open?

---

# Steps to analyze
+ Get [median income data from DCP](https://www1.nyc.gov/site/planning/data-maps/nyc-population/american-community-survey.page)

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

---

# Steps to analyze
+ Get [median income data from DCP](https://www1.nyc.gov/site/planning/data-maps/nyc-population/american-community-survey.page)
--

+ Clean the data 
--
(or just use [the data I've already cleaned](https://s3.amazonaws.com/datapolitan-training-files/2015_CD_Median_HH_Income.xlsx))
--

+ Find average `Hours Open` by Community District in the 311 Service Request data
--

+ Make sure you've dealt with the issues of the `Closed Date` field (missing or in the past)
--

+ Create a unique identifier (like in our population sheet) in the HH Income 
--

+ Do a VLOOKUP on the identifier
--

+ Calculate the correlation (using the [CORREL function](https://support.office.com/en-us/article/CORREL-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92))
---

name:wrap-up
class:center,middle
# Wrap-Up

---

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/)

---

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

---

name:final
# Final Thoughts
--

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

+ Analysis is the search for understanding and where we learn to tell that story
--

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

---

# Building a 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/)

---

# Data Sources
--

+ [NYC Open Data Portal](https://opendata.cityofnewyork.us)
--

+ [Bytes of the Big Apple](https://www1.nyc.gov/site/planning/data-maps/open-data.page)

---

# Technical Support
--

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

+ [Excel Tips](http://excel.tips.net/) - Various tips and tricks for using Excel
--

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

---

# Resources
--

+ [Data Driven New Orleans - Nolalytics](https://datadriven.nola.gov/home/)
--

+ [Harvard Government Performance Lab](https://govlab.hks.harvard.edu/) [Results-Driven Contracting](https://govlab.hks.harvard.edu/results-driven-contracting)
--

+ Carl Anderson [_Creating a Data-Driven Organization_](http://shop.oreilly.com/product/0636920035848.do)
--

+ DJ Patil & Hilary Mason [_Data Driven: Creating a Data Culture_](https://www.oreilly.com/ideas/data-driven)
--

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

---

# Contact Information
## Richard Dunks
+ Email: richard[at]datapolitan[dot]com
+ Website: http://www.datapolitan.com
+ Twitter: [@datapolitan](https://twitter.com/datapolitan)

---

class:center, middle
#THANK YOU!

---
name:formulas
# Formulas (Cells and sheet names will differ)
+ Concatenating the Borough and Community District Number `=CONCATENATE(IF(LEN(B2)<2,"0",""),B2," ",UPPER(A2))`
+ VLOOKUP for population data `=VLOOKUP(A4,[2010_NYC_CD_population.xlsx]A!$C$1:$E$60,3,FALSE)`
+ Calculating the hours open `=(C2-B2)*24`

---

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

---

# 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