Homework 14, The Streets of San Francisco

This assignment is due Nov 18 for the day class and Nov 25 for the night class.

Please note, I have a tendency to transpose and mix up cell numbers. The pictures are correct. If you are confused, please ask.

1. This week we will analyze data related to arrests in San Francisco.
   • Save this file.
     • Right click on the link and select save link as.
     • This is a csv file, and it is quite large.
   • The data is from this site which is the clearing house for data for the city and county of San Francisco
   • This data set contains: SFPD Incidents - Previous Three Months Incidents derived from SFPD Crime Incident Reporting system. Previous month. Updated daily.
   • We would like to investigate when crimes occur.
2. Start excel.
3. Open the file you just saved.
   • Since it is a csv (comma separated value, a common data exchange format) you will need to change the type to be Text Files (.prn; *.txt; *.csv)
   • 
4. We will not need the location information, so delete columns J,K and L
5. Move The Category Column to after the PdDistrict.
6. Move the Description Column after the Category Column
7. 
8. Start by sorting the table
   • Sort first by Date, oldest to newest
   • Then sort by PdDistrict
9. Then sort by Category.
10. Add some subtotals
    • Make sure that the above sorts are performed BEFORE you begin this step.
    • Be patient with these computations, they may take some time.
    • Add a subtotal to count the incidents, grouped by date.
    • Add a second subtotal to count incidents, further grouped by police district.
11. Start Word
    • Put your name in the header
    • Insert a heading Crime Totals for 8/4/2014
    • Use copy and paste to insert a copy of the totals for 8/4/2014 (I used 8/3/2014)
      • Copy the data from excel.
      • Remember, the totals are at the end of the data.
      • 
      • When pasting, use the Use Destination Styles choice.
      • 
    • 
12. Remove all subtotals. (This may take a moment)
13. Filter the data so that only incidents involving Embezzlement and Gambling for the Bayview and Richmond police districts is visible.
    • Add a heading to the word document below the previous table, Gambling and Embezzlement in Bayview and Richmond.
    • Copy the Date, Time, PdDistrict, Category, Description and Resolution fields of all of the matching reports and past them into the word document.
    • Note, the big blue dot should not be in your document, it is in mine to preserve the integrity of the exercise.
    • 
14. Clear all filters and remove all filter handles.
15. Create a pivot table from the data
16. Build tables to show
    • The number of incidents, Rows: By Category, Columns: Resolution
      • Only show resolutions related to Juvenile categories.
      • Display Grand totals.
      • Provide a column chart to show this table.
      • (see below for notes on this screen shot)
      • 
    • The number of incidents, Rows: PdDistrict, Columns: Category
      • Only display the following categories: Arson, Assault, Bad Checks, Burglary, Warrants and Weapon Laws.
      • Do not display Grand Totals.
      • Build a pivot chart to accompany this table.
      • (see below for notes on this screen shot)
      • 
        • The number of incidents Rows: Time (by hour) Columns: PdDistrict.
      • For each table above
        • I am using Tabular Form from the Report Layout dropdown in the Layout section of the Design tab.
        • Insert a copy of the table into the word document.
        • Add an accompanying pivot chart if requested.
        • Add a heading
        • All items relating to a table should be on a single page.
17. When you are finished email the word document, as an attachment, to your instructor.