This assignment builds on the lectures, in-class assignments and homework throughout the semester. You will build a new database with the FBI and Census data using SQL and cleaning the data with Excel. You will export it to Tableau, generate data visualizations and maps. And then you will write a story with your findings, incorporating the graphics. Story and graphics are due 11:59 pm Thursday, May 4, as a blog post.

We will use the remaining class periods to work through these problems and I will answer questions and lead discussions on the difficult parts.

The goal this week is to make sure everyone builds a database that contains the county and city demographic and FBI “National Incident-Based Reporting System” crime data for 2015, which is far more sophisticated than what we have been using.

You will then repeat the process with data for 2012.

Census Data:

Crime Data 2015

Crime Data 2012

Census Data 2012

This involves data cleaning.

Helpful Hints:

Database construction: It might be faster to simply join the data in SQL and then do the calculations in Excel. That way you don’t have to label dozens of columns on the import.

Look at the overlapping jurisdictions that share the same name for city and county.

Did your join capture Cities with more than one name?

Tip on Data Cleaning – Remove Spaces in Hypthenated Names

Filter the trimmed place names (Data | Filter)
In the filter search box, press the space bar once to filter all of the hyphenated names
Then do Find, Replace:
Find – press the spacebar once
Replace – nothing.
That should strip out all of the spaces!

Using Excel, SQL and Tableau, you will analyze the crime and demographic databases for 2010 and 2014.

Calculate violent crime rates per capita (use the population figures in the FBI data, not the employment population from Census)

Calculate property crime rates per capita

Calculate the unemployment rates

Median violent crime rates for the years, per capita

Median incomes for the years

Median unemployment rates for both years

List of large towns with above average crime rates

List of towns with highest unemployment and highest crime rates

List of large towns with below average crime rates

List of small towns with below average crime rates

List of universities and crime rates

—What are the patterns and trends from 2010 to 2014?

Write a story pitch and graphic for your final project

Part 3: Write and complete your crime story. May 4.

Story is due 11:59 pm Thursday, May 4, as a blog post. I need to see the following:

1. Blog post story, minimum 600 words, with embedded graphics
2. Both joined Excel sheets for 2015 and 2012
3. One final Tableau workbook (suggestion: upload this large file to Google docs and share the link with me)

Rubric:

Rubric Final Project Crime and Demographics

Clean Tables to Join

—Simplify headers

—Cut out needless columns

—Align the matching column format

—Store as .csv

—Put all of this in one folder

—We will match cities later

—Trim spaces on columns you intend to match.

–Copy and paste values of the trimmed columns

Data Should Look Like This:

Create the Database

—Remember, simple DB names

CREATE TABLE “Census” (“Counties” TEXT, “Counties2” TEXT, “Subject” TEXT, “Pop_Adult” REAL, “Labor_force” REAL, “Employed” REAL, “Unemployed” REAL, “Median_household_inc” REAL, “Avg_household_inc” REAL)

CREATE TABLE “Crime” (“Place” TEXT, “Place2” TEXT, “Full_Place_Name” TEXT, “State” TEXT, “Months” NUMERIC, “Population” REAL, “Violent_crime_total” REAL, “Murder” REAL, “Revised_rape” REAL, “Robbery” REAL, “Aggr_assault” REAL, “Property_Crime_Total” REAL, “Burglary” REAL, “Larceny-theft” REAL, “Motor_vehicle_theft” REAL, “Violent_Crime_Rate” REAL, “Murder_Rate” REAL, “Property_Crime_Rate” REAL)

Join the Data

SELECT *

FROM Census

INNER JOIN Crime

ON Census.Counties2 = Crime.Place2 ;