Using Mapping Software to Analyze Shooting Data

 

I undertook an analysis project to examine shooting data from Paterson, NJ from January 1^st through June 30^th, 2021. The crime mapping software and techniques that I teach in the classroom allow students to visualize data. Students are also taught to look for solutions to problems based on temporal and spatial trends that the data shows. Most importantly, they can observe the human toll that crime takes on a community. This important concept should never be lost to push-button analyses or media blurbs.

Fast facts on Paterson Shootings: January 1st 2021 through June 30th, 2021

• 89 shooting incidents
• On average, a shooting incident took place every 2.8 days
• 20 multiple victim incidents (2 or more victims)
• 10 people killed
• 92 people injured
• 28 gunshot victims walked into the hospital

 

Using Excel to Tame Large Datasets

I teach various Microsoft software; Word, Publisher, PowerPoint, Access, and Excel.  Each has value in the workplace depending on the tasks that need to be completed.  Of the five, however, I find that intermediate to advanced knowledge of Access and Excel is like having money in the bank.   The ability to manage large data sets in this “age of data” is a crucial skill to develop.   In this posting, I will demonstrate how to use Excel to manage a dataset from the Federal Bureau of Investigation’s Uniform Crime Report.   The Uniform Crime Report is a list of offenses known to law enforcement that is reported by municipalities each year.  Part of the UCR is broken down by state, allowing users to select and download individual states for analysis.  In this example, I am using New Jersey’s 2010 UCR data. 

The dataset contains 493 cities and towns.  A complete record for each town is represented in the rows.  There are 12 categories of information including the city name and categories of crime.  These categories are contained in the columns.

The first thing you may notice when attempting to browse the sheet is that it is quite easy to lose your place.  In the screenshot, what does the value 146 in cell H38 represent?  Of course I can scroll back and over to the beginning of the spreadsheet and find that the H column reflects property crime, and Row 38 reflects Bernards Township. 

An easy way to keep column headings and row labels in view all the time while scrolling the data is to use the freeze pane option.  In this example, the cells I want to freeze are located above and to the left of cell B6.  I highlight the cell, select View-> Freeze Panes-> Freeze Panes.  Now when I browse the sheet, the column headings and row labels stay in view. 

 

Even with the spreadsheet easier to scroll through, finding specific data can still be a cumbersome task.  For example, suppose I want to review crimes for New Jersey’s 4 most populated cities.  I could employ Excel’s Sort and Filter option by selecting the Population column and then selecting Sort & Filter.  I would have to repeat the steps to sort other categories.  A downside of this method is that it changes the layout of the spreadsheet.

A better option for data management in this case format the data as a table.  I click on cell A5, select Format as Table, and select a color scheme.  The Format as Table dialog box appears.  I will have to change the data range from $A$1 to $A$5 since the actual data starts in cell A5.  I also have to check “my table has headers” since each column heading represents a category of data.  My data is formatted into a table with filter control boxes for each column.

Using the filter control boxes, I can perform specific searches based on data criteria.  For example, perhaps I want to see a list of cities where the violent crimes were 100 or lower.  I select the arrow in the violent crime column and select number filters.  Notice the number filtering option available.  I will use the less than or equal to filter.  I set the criteria as 100 and I am presented with a list of towns

and cities where the reported violent crimes were 100 or less. 

These results can then be sorted largest to smallest or smallest to largest. Another useful number filter is the Top 10 filter, although the title of this filter can be misleading. Not only can you filter for a top 10, you can also filter for the bottom 10, as well as change the min/max value and whether to search by items or percentages.  

When I demonstrate these techniques in class, many students assume that data is deleted.  However, filtering data hides those data that do not meet the criteria.  No data is deleted.   Removing the filter makes all data visible again.  A filter can be removed by clicking on the arrow in the column and selecting Select All.   

One of Excel’s most recognized user features is the software’s power of calculation, from basic mathematical functions to advanced statistical functions.  I like to convert data to tables whenever feasible because it allows me to append a total row to my table.  The total row gives me access to all of Excels functions with the click of the mouse.  By highlighting any cell in my table, selecting Table Tools and Design and clicking Total Row, a total row is added to the end of the table.  For example, summing the population column, I can see that the total population in this data set is 8,439,459.  While the SUM function is a commonly used tool, each column has a total cell with all of Excels functions available for use.      

A Lesson in Technology and Critical Thinking: The Red Light Camera Example

Red light cameras are being embraced by many municipalities. They are meant to deter drivers from traveling through an intersection against a red light. They provide a means to ticket drivers that run red lights without the need for a police officer to witness the alleged crime. This technology has been widely touted as a way to increase the safety of intersections, a primary reason given by elected officials for their use. But what do the statistics say about red light cameras? Statistics show that accidents have increased at intersections where red light cameras are in use. Statistics also show that injuries have decreased at intersections where red light cameras are in use. When I cite these examples to students, their response is “How can both statements be true?”

Both statements need further clarification through an analysis of the data. Intersections where red light cameras are in use have seen a decrease in the number of side impact or “t-bone” collisions, but an increase the number of rear-end collisions. Side impact collisions have a higher injury rate than rear- end collisions. So what is the process for instituting a technology like red light cameras given this data? Was an increase in rear-end collisions anticipated? Did designers and municipalities perhaps agree that rear-end collisions are preferable to side impact collisions? This type of critical thinking about the unforeseen consequences of a technology, as well as the successes of a technology,  is a must during design and implementation.

When a technology has the potential to impact upon the rights of the citizenry, it is equally crucial to examine its potential for circumventing protections we take for granted. To be sure, red light cameras are a convenient source of revenue for municipalities. In addition to the detection and reporting of a motor vehicle offense, red light cameras operate 24 hours a day, 7 days a week without collecting overtime pay, holiday pay, or disability pay if “injured”. They do not get paid for court time. These are all wins for a town or city: Easy fines with low overhead. An additional argument can be made that red light cameras free police officers from court obligations, allowing them to concentrate on other law enforcement activities. But what about the rights of those being fined? Under our system of law, we have the right to face and cross examine our accuser in a court of law. How does one cross examine a camera? Perhaps this is the reason that most red light camera tickets are “no point” tickets.

A brief look at one piece of technology in criminal justice exposes a plethora of issues upon closer examination.  Red light cameras are but one example of how the use of technology is a practiced art, not an absolute science. Given the use of drones, electronic eavesdropping, and surveillance technologies to name a few, the ability to think critically about the use of any technology in the criminal justice system is paramount in a free society.

Want to Know More about Red Light Cameras? 

Red Light camera safety statistics:

• Executive Summary of the report Safety Evaluation of Red-Light Cameras, published by the Federal Highway Administration here.

Some interesting articles on the Constitutionality of the red light camera, both pro and con, can be found here:

• Pasco judge challenges constitutionality of red light cameras
• NYS Supreme Court rules Rochester's red light cameras constitutional
• Connecticut State Police Union president opposes red light cameras bill

A Tale of Two Analysis Programs: Excel and SPSS

Excel and SPSS (statistical packages for the social sciences) each have their strong points and weaknesses when it comes to data analysis and presentation.  In this example, I am going to use Excel and SPSS to help me demonstrate how crime increases and decreases through various months in Washington, D.C.  I am going to use the 2013 Crime Incidents csv file from the Washington, D.C. Office of the City Administrator available at: http://data.dc.gov/ .   After downloading the file, I open it with MS Excel and perform a “save-as” in order to save the csv file as a spreadsheet.  This will allow me to use formulas and better manage the data.  The first thing to realize about this dataset is that it contains 35152 records!  That is a lot of data to manage!

 

You can see that the report and time variable in column B is in the format mm/dd/yyyy  hh:mm.  Ideally, I would like a way to convert these dates to a three letter month abbreviation for ease of analysis.    Fortunately, Excel’s rich function library will allow me to perform the task.  I insert a column  to the right of column B, the REPORTDATETIME column.  I call the new column MONTHS.  In cell C2,  I use the following formula to convert the date/time format into the three letter date abbreviation:  =TEXT(B2, "mmm") where TEXT is the name of the function, B2 is the location of the data to be converted, and “mmm” represents the three letter abbreviation.  In a smaller dataset, I would simply copy the formula down using my mouse.  However with 35000 plus rows, the better solution is to highlight the cell containing the formula, move the mouse to the lower right hand side, and double click.  Doing so populates the entire column with the formula and results. 

My goal is to demonstrate how crime increases and decreases through the months.  While the spreadsheet contains a list of the months and a list of the reported offenses, it does not give me a count of reported offenses nor does it categorize the counts of crimes according to each month.  Excel does have PivotCharts for this purpose, but the process with this much data is cumbersome at best.  The easier solution, as we will see, is to import the data we need to analyze into SPSS.

SPSS allows users to import entire spreadsheets (both Excel and csv formats), text files, and MS Access files to name a few.  But for the purpose of this example, we only need to import two columns of data, the months column and the offense column.  The first thing I want to do is make sure I already have SPSS open and ready to use.  In Excel, I highlight each column, copy them, and paste the columns into SPSS.

I need to perform a little maintenance on the data I just pasted.  Row 1, containing MONTHS and OFFENSE must be cleared since these are labels, not variables.  In the SPSS Variable View window, I will use months and offense as labels to identify the variables.   The data is now ready for analysis.
 

SPSS allows a user to simply count variables, called frequency analysis in SPSS.  SPSS can be used to provide the counts or frequencies of the individual crimes and individual months as shown below.

      

However, one of SPSS strongest tools is crosstab analysis.  Crosstabing creates a table of summaries that shows how data interacts.  When we crosstab Months with Offenses, we can count the number of crimes committed in each month.  We can count individual categories of crimes for each month.  We can also count each category of crime for the year, all while viewing one table of data.  For example, in the month of February, there were 2234 total crimes reported.  Of these, 184 were burglaries.  And there were 3346 burglaries for the year.  

 

 As useful as SPSS is for performing numeric analysis and visualizing this analysis in organized tables, I find SPSS graphing functions to be a bit tedious when compared to Excel. Excel also produces better quality graphs that are more easily edited and enhanced for presentation.   Importing data from SPSS into Excel is a relatively easy process. I simply right click over the MONTH/OFFENSE crosstab and select copy.  I copy this data into a blank spreadsheet in Excel, making sure I use the paste special option so that the table maintains data integrity.

Creating a graph that demonstrates rate of crime through the months  is a matter of selecting the correct data and proper chart, in this case, months and total crimes for our data and a line chart as our display graph.   A couple of formatting edits of the chart and I am ready to discuss the data, which seems to show that crime increased from February on, and peaked in September.   

I can easily create an additional graph comparing numbers of the types of crime committed by selecting the crime labels, the totals for each label, and an appropriate pie chart. 

The charts can easily be used in a PowerPoint presentation or on a web site.  The ability to work with different software and move data from one tool to another is an easy and profitable skill to develop, and all it takes is a little practice.