CJ 407 Module Two Assignment Guidelines and Rubric
Overview
Analysts must conduct several different types of analyses to capture a holistic representation of a situation using the data and tools they have. In addition to sociodemographic analysis, they must also examine spatiotemporal trends and insights. Spatiotemporal analysis seeks to uncover trends and insights related to where and when aspects of crimes are taking place. With crimes, these aspects are critical when used to inform decisions for criminological and/or data-driven approaches.
Prompt
Use the dataset you began working with in the previous module and the provided hot spot map to conduct a spatiotemporal analysis for burglary cases. Save a new version of the dataset by renaming and saving the one you worked with previously. This will allow you to revert to the previous version if need be. Remember to use the Excel desktop application to complete all Excel tasks in this course.
Specifically, you must address the following rubric criteria:
Spatial analysis: Create a pivot table with a “Clustered Column” pivot chart to identify spatial trends for burglary cases. Title this additional sheet “Spatial Analyses.”
Identify the distribution and measures of central tendency for burglary cases per street.
Click on your pivot chart prompt when it appears on the left side of the page. Then, select PivotChart Analyze, the third selection from the left on the horizontal menu at the top of the page. Arrange your variables so “Crime Types” are in the “Filters” box, “Street” is in the “Axis” box, and “Crime Types” are in the “Values” box.
Click on Crime Types on the box directly under the PivotChart Fields heading. You will see a drop-down menu box. Select Burglary and click OK.
Resize and reformat the pivot chart to easily display your outputs.
You will see two columns on the left. The first column (Column A) is labeled “Row Labels.” Click on the cell of column A3 and re-label the column Street Name.
In column B, there is a count of burglaries. Re-label the column heading in cell B3 Count of Crime Types: Burglary.
This is the street name column. At the bottom of column B, in the empty cells, enter the formulas for MEAN (average), MEDIAN, and MODE as follows:
=AVERAGE
=MEDIAN
=MODE
Highlight the values for the numbers for each measure of central tendency and click Enter. The numerical values will appear in column B in the empty cells. Be sure not to include the Grand Total Value.
Label the values for the Mean, Median, and Mode of the count for burglaries in column A.
Add an axis title, your name, and the date to the “Crime Types Count of Crime: Burglary” chart to the right of the “Count of Crime Types” field. To do this, go to the third menu item in the horizontal menu at the top of the spreadsheet, Insert. Select Text Box.
Insert the text box at the bottom of the chart.
Use the screenshot function on your computer and capture a screenshot of your chart after resizing and adjusting the Design settings so the chart clearly displays the outputs.
Make a note of the street with the highest frequency of burglaries. You will use this data in the next step.
Concisely summarize statistical relationships about where crimes are or aren’t taking place.
Identify the distribution and measures of central tendency for burglary cases that took place on the street with the most cases from the previous step. Use the dropdown filter to select just the street with the most cases on it or type the name into the search window.
Choose the street with the highest number of cases.
Remove Street from the “Axis” box.
Add “Street Number” to the “Axis” box.
Resize and reformat the pivot chart to easily display your outputs.
Adjust the ranges selected for your formulas to show updated measures of central tendency (outside the range where your pivot table and pivot chart will display information) for =AVERAGE, =MEDIAN, and =MODE functions for only the street(s) you’ve filtered.
Capture a screenshot of your new chart after resizing and adjusting the Design settings so the chart clearly displays the outputs.
Add axis titles to your visualizations, and title the bottom axis with your name and the date.
Concisely summarize relationships for statistical “hot spots.”
Compare the statistical hot spot findings with the Burglary Hot Spot Map
Temporal analysis – months: Create an additional pivot table with a “Clustered Bar” pivot chart to identify temporal trends for burglary cases. Title this additional sheet Temporal Analysis – Months.
Identify the distribution and measures of central tendency for burglary cases per month of the year.
Click INSERT.
Click PIVOT TABLE.
Add “Crime Types” to the “Filters” box, “Arrest Date/Months” to the “Axis” box, and “Crime Types” to the “Values” box.
Choose Layout 5 for your pivot chart to display the months with numerical values in boxes below the bars of your chart.
Click Pivot Chart Analyze and select the Clustered Chart option.
“Layout five” selected from the “Quick Layout” options for pivot tables.
Add cells (outside the range where your pivot table and pivot chart will display information) for =AVERAGE, =MEDIAN, and =MODE functions, and complete them to capture measures of central tendency for months of the year.
Insert the label, your name, and date using the Text feature at the bottom of your chart.
Capture a screenshot of your chart after resizing and adjusting the Design settings so the chart clearly displays the outputs.
Label your chart as you did for the earlier step and take a screenshot.
Make a note of the two months with the highest number of frequencies. You will use this information in the next step.
Concisely summarize statistical relationships for months.
Identify the distribution and measures of central tendency for arrest times during the two months with the most burglary cases.
Note the two most frequent months for burglaries from the previous step.
Access the original data sheet. Select Insert and Pivot Chart, then select Pivot Analyze and select Clustered Column.
Add “Arrest Time” to the “Axis” box for your pivot chart.
Choose the two months with the highest number of cases from the filter drop-downs in your pivot chart.
Select Crime Type and filter for Burglaries in the “Value” box.
Insert Text Box along the horizontal field at the bottom of the chart. Include the following information:
Burglary Frequency for Most Active Months By Time: May and November
Your name and the date
Capture a screenshot of your chart after resizing and adjusting the Design settings so the chart clearly displays outputs.
Add axis titles to your visualizations, and title the bottom axis with your name and the date.
Concisely summarize statistical relationships for temporal “hot spots” (when [months of the year, times of the day and night] crimes are taking place).
Temporal analysis – hours: Create an additional pivot table with a “Clustered Bar” pivot chart to identify temporal trends for burglary cases. Title this additional sheet Temporal Analysis – Hours.
Identify the distribution and measures of central tendency for burglary cases by hours of the day.
Add “Crime Types” to the “Filters” box, “Arrest Time” to the “Rows” box, and “Crime Types” to the “Values” box.
Group the arrest times into a range of 24 separate hours if this hasn’t already been done in previous steps.
Capture a screenshot of your chart after resizing and adjusting the Design settings so the chart clearly displays the outputs.
Add axis titles to your visualizations, and title the bottom axis with your name and the date.
Concisely summarize statistical relationships for temporal data (when [hours of the day] crimes are taking place).
What to Submit
Organize your screenshots and summaries in a Word document so it’s easy to follow and clearly conveys your findings. Any additional sources should be cited according to APA style. Consult the Shapiro Library APA Style Guide for more information on citations.