=(NOTE: I will not open or grade any attachments posted. ALL work should be placed in the Discussion box. Any first post that is a period or blank will result in a 0. Please don't do it).

How does your community area rank on crime?

We are going to use two Excel files on this discussion board (both are posted below) and the GIS map. The idea is to learn about the level of crime in your community area relative to the city and to drill down and learn about crime in your specific community area by using real data from 2023. This will give you an understanding of the levels and types of crime, deterrence through arrest, and the level of domestic violence as well.

1. Using this Excel file CHICAGO CODEBOOK-3.xlsx Download CHICAGO CODEBOOK-3.xlsx, please do the following:
   1. Find the column titled CRIME1. This column was the crime in your community area for 2014. Insert a column next to it, called CRIMERATE. You will need to calculate the crime rate for each community area in Chicago by making a formula (see videos from last module if you can't recall). The crime rate formula = CRIMES / (Population/100,000). USE CRIME1 for the crime total and POP1 for the population total.
   2. What was the city crime rate in 2014?
   3. What was the crime rate of your community area in 2014?
   4. Providing the stats you used (mean, difference, z-score) to determine where your community area ranked in terms of crime in 2014 relative to the city and the other community areas. Is crime very low, somewhat low, near average, somewhat high or very high?
2. Using the same file, please do the following:
   1. Find the column titled ARREST1. These are the number of arrests/the number of crimes reported, or the clearance rate through arrest. We would hope for high levels of arrest for crimes for the sake of deterrence. Calculate and present the mean and standard deviation for the entire sample.
   2. Calculate and present the z-score of your community area for arrest percentage. Is it very low, somewhat low, near average, somewhat high or very high? Cite the stats you used to make the decision.
3. Using the same file, please go to the last column in the data, CRIMEOUT7. This is a z-score of the domestic crime rate in each community area as of 2022. Please find your community area, present the z-score and interpret what that means. Is domestic crime very low, somewhat low, near average, somewhat high or very high?
4. Now, switch over to the Chicago crimes datafile (CHICAGO CRIMES 2023-2.xlsx Download CHICAGO CRIMES 2023-2.xlsx) for the second part of this assignment, in which you will use other Excel skills to learn about recent crime in your area to compare to the results from 2014.
   1. Run a pivot table of all crimes by community area. Copy and paste these results in the second tab of the file, where the population is already presented (copy the total crimes column created). Follow the steps from #1 and calculate the city crime rate as well as the crime rate in your community area. Present that. Run a z-score of the sample and present your community area. How does it compare with 2014?
   2. Run a pivot table of arrests, and copy and paste that total in the second tab of the file. Calculate the arrest percentage for each community area and compare your community area to the city. Also compare whether it is higher in 2023 than it was in 2014.
   3. Run a pivot table for domestic crimes, and copy and paste that total into the second tab of the file. Calculate, using the population column, the domestic crime rate in every community area. Then, run z-scores and see how your community area compares to the city as well as itself back in 2014.
   4. Add a filter (by community area) to your pivot table and filter out only your community area from the rest of the file. Run three pivot tables on the following columns to better understand the type of crime in your community area:
      1. PRIMARY TYPE: look at whether your community area is highly violent or more property crime. Look at all the offenses and see if you can group them in some way to understand the types of crimes occurring)
      2. DESCRIPTION: look into the details of crimes to see if you can see any trend or pattern in your specific community area (i.e. are guns used a lot, are sexual crimes high, as examples). This takes some analysis and thought.
      3. LOCATION: look at categorizing these somehow (public/private, outside/inside, residential/non-residential) to understand where crime occurs in your community area. Present and discuss these findings.
5. Go the GIS map and turn on the Shotspotter layer (you can also find the Excel file here SHOTSPOTTER RECENT DATA.xlsx Download SHOTSPOTTER RECENT DATA.xlsx).
   1. 1. Take two screenshots of the GIS map with Shotspotter layer turned on to see (a) the level (or lack) of gunfire in the city as a whole and (b) in your specific community area zoomed in.
      2. Use the Excel file to present how many total alerts and total rounds have been fired in your community since Shotspotter went live in Chicago 5 years ago. Use any stats you want to show how that compares to the city.
6. Take ALL of the results together and give me the picture of crime in your community area (what occurs, how much, at what level compared to the city, where, if deterrence exists, are more in the home, public). Don't just repeat the findings----integrate them all together to give us an overall picture of criminal behavior in your community area from a variety of interesting perspectives.