Homework 5: Filtering Terry Stops

The goals of this homework are to demonstrate your ability to :

• Employ sorting and filtering techniques
• Extract unique values in a range.
• Apply advanced filters
• Build a table with slicers
• Use a total row in a table.

Instructions

1. Please start a word document.
   • you will be adding screen shots of your work as you go along.
   • Place the word document in landscape mode.
   • Insert your name and other identifying matter at the top of this document. (class, homework 5, ...)
2. Download the raw data file
   • Rename the worksheet to be Raw Data
   • Right now save it as an excel file.
   • Call it terry.xlsx
3. This is a large data set, so you will probably want to navigate with
   • ctrl-end takes you to the end of the data set
   • ctrl-home takes you to cell a1
   • From home, ctrl-shift-end will select the entire data set.
   • Try these commands right now.
4. Download this workbook
   • You will need to merge the two workbooks.
   • The easiest way to do this is to have the two workbooks open.
   • Grab the about tab in the TerryAbout workbook and drag it to the terry workbook.
   • 
   • 
   • When you finish, you should have two worksheets in a single workbook.
   • Save this again, just to be sure.
   • In this case there are 26,043 records so I don't want to make a copy. We will work in the RawData tab.
5. For now, we are not interested in the following fields, so hide them.
   • Subject ID, Terry Stop ID, GO/SC Num
   • Reported Data, Reported Time
   • Initial Call Type, Final Call Type
   • In your word document, insert a screen shot of the field labels and the first few rows of data after hiding these fields.
   • Label this screen shot appropriately. (For this and all further entries)
     • Include the homework item number (5)
     • Include a description of the screen shot (The data after hiding several fields)
6. Investigating the Subject Age Group field.
   • Looking at the top data in this field, there appears to be a problem.
   • Sort the table (A-Z) on the Subject Age Group filed.
   • In your word document
     • Insert a screen shot of the first few rows of data.
     • Describe the contents of the screen shot.
     • Discuss any immediate concern with the first few rows of data relating to the Subject Age Group.
7. Create a new worksheet
   • Call this worksheet Subject Age
   • Create a list of the unique Subject Age Group values starting in cell A1 of this page.
   • 
   • In your word document
     • Insert a screen shot of this new list
     • Describe the screen shot.
     • Discuss any concerns you have with the values in this list.
     • For the values that are incorrect, propose a plan to discover the correct value for the field.
       • Remember our discussion of dates.
       • Does 1/17/2019 somehow fit into the other dates?
       • What could a "-" represent?
         • Is this a valid value? Why or why not.
         • Do you think you might want to replace this with some other value? Why or why not?
     • Go to the original data set here.
       • Use the tool to aid your investigation
       • Sort the data
       • Page down several pages to see if Jan 1 is there.
     • Discuss your findings in the word document you are creating.
8. Find the extent that each field is represented.
   • Create a table out of the dataset
   • Add a total row.
   • Add a count field for Subject Age Group to the total row.
   • Filter the data based on the different values of the Subject Age Group
   • Add a screen shot for each displaying the totals. There should be seven different screen shots.
     • We will find a much more efficient way to perform these totals soon.
     • Example:
     • Document each screen shot appropriately.
9. Slicers
   • Insert a slicer for the following fields
     • Subject Age Group
     • Weapon Type
   • I your word document answer the following questions. Include a screen shot of the slicers.
     • How many stops for people 56 and above involved a hand gun?
     • How many stops for people between the ages of 36 and 55 involved a Handgun or a Firearm of any type
10. Working with a subset of the data
    • You have decided you want to create a subset of the data for white officers born between 1980 and 1990
    • Insert a new worksheet called Study Group.
    • On this worksheet, using the Advanced Filter Function, extract all data that matches the above criteria.
11. Officer YOB
    • Explore the Officer YOB field.
      • Unhide the "Reported Date" field
      • Insert two columns to the right of this field.
      • In the first, compute the year of the report.
        • Use the =year() function on the Reported Data field.
      • Calculate the officer's age when the arrest occurred
        • Year of report - Office YOB
      • Label each column appropriately.
      • 
    • Identify and discuss any values which you feel may be problematic
    • In your document
      • Explain why you identify these fields are problematic
      • Explain how you what you might do about these problematic fields.
      • Provide screen shots to support your discussion
12. Reported Time
    • Explore the Reported Time field.
    • Identify and discuss any values which you feel may be problematic
    • In your document
      • Explain why you identify these fields are problematic
      • Explain how you what you might do about these problematic fields.
      • Provide screen shots to support your discussion

Submit