Creating a national homicide profile using data from the Statistical Abstract.

You will work on this assignment individually in class this week.  Through this assignment you will not only have the chance to practice data analysis and graphical representation, but you will get a thorough introduction to the United States Statistical Abstract which is a huge data base of statistics describing different aspects of our society (population, economy, crime, standard of living, etc.).  You will be working from the Statistical Abstract for your project later in the quarter. 

In this assignment,  you will recreate most of the graphs from the article in the Chicago Tribune homicide series.  Instead of focusing on Chicago, however, you will use data from the Statistical Abstract to create a national profile of homicides, offenders, and victims.  By working on this project you will gain a behind-the-scenes look at how these graphs were produced; you will be able to draw an interesting comparison between Chicago and the entire U.S.; and finally you will learn all of the necessary graph making skills for your group project.  The link to the Statistical Abstract is found at the bottom of Excel Files page. 

Record all of your answers in a Word document. You may find the following guide to be a helpful resource for this assignment:  Guide to the Statistical Abstract.

ALL GRAPHS FROM ARTICLE

1.)  "Chicago murder rate is tops among biggest cities"

The subtitle explains that this grpah is: Homicides per 100,000 people: For 2001 in cities with populations of more than 1 million people.

You will be making the same graph as appears in the article but for the year 2002.  For this you need data on city populations and murder rates.  Go to the Statistical Abstract link on the bottom of the Excel files page.  Go to section 5: Law Enforcement, Courts, and Prison, then open file 292, Crime Rates, by Type-Selected Large Cities.  You will notice that the file contains a lot of data, most of which we do not need for this task.  Sometimes copying and pasting the necessary data into a blank spreadsheet (MS Excel or OpenOffice Calc) file is helpful to minimize distractions. 

a. Begin by copying columns A, B and E (cities, resident populations, murder rate in 2002) into a blank Excel (or Calc) file page. You can copy just the data and add your own column headings. (In Excel, you can use the control key to select all three columns then copy and paste them. Calc can not copy non-contiguous cells ("multiple selections") though, so in Calc you will have to first copy and paste columns A and B, then do column E in a separate step.)   To insert an entire row, right click on the row number then click insert. 

b. Sort the data by resident population (if it is not already sorted).  The cities with the largest populations should be at the top.  We are only interested in cities with populations over 1 million.  Copy and paste those rows to another part of the spreadsheet page.  

c. Sort this set of columns by murder rate (note: the data is already per 100,000 people).  Create a bar or column graph of this data and paste it into your Word/Writer document.  Change axes as needed to make a visually pleasing graph.

d. Describe, in a brief paragraph, how this graph compares to the one in the article.  If there are any differences explain why these differences may exist.

2.)  Victims - From the data in file number 294 (also in section 5 of Statistical Abstract), make the following graphs: 

1. Pie graph of Murder Victims by Gender for 2002
2. Pie graph of Murder Victims by Race for 2002
3. Bar graph of Murder Victims by Age for 2002.  Play close attention to the age categories used in the article's graph. You will have to combine categories from the Statistical Abstract in order to reproduce the categories from the article. 

Copy and paste these three graphs into your Word document.  Compare and contrast each of your graphs with the  corresponding graph in the article.  Make sure to note any differences.

3.) Offenders - From the data in file number 309, make the following graphs:

1. Pie graph of Murder Offenders by Gender for 2002
2. Pie graph of Murder Offenders by Race for 2002

Copy and paste these three graphs into your Word document.  Compare and contrast each of your graphs with the  corresponding graph in the article.  Make sure to note any differences.

4.) Geographic Distribution of Murder Rate - Use the data in file number 291 to make a map of the murder rate by state in 2003. 

    The murder rate per 100,000 for 2003 is found in column AC. Sort the data by this column.  You may consider deleting the data for D.C. and Louisiana because they both have inordinately high murder rates. If you choose to delete these two, your map will shade these two states yellow, but it will make it easier to see distinctions between the rest of the states. This type of omission is okay as long as your make it clear in a caption, footnote, or other accompanying explanation. Also, you will have to clean up the states names so that they don't have footnote symbols after them. For example, Delaware \2 should become Delaware.

Using Map Tool, make a map of the murder rate by state.  Copy and paste the map into your Word document. 

This map will not directly compare to the map in the article. But you should still describe the map you have made and make comparisons, if possible, to the map in the Tribune article.

5.) National Homicide Rate and Population of Males 18-24 -Using the data in file numbers 289, 11, and 2 (11 and 2 are found in section 1: Population), you will make a graph of homicide rate and male 18-24 population. 

   File 289 contains number of murders, percent change in murders and murder rate for the nation from 1960 to 2003.  You are interested in murder rate which is toward the bottom of the page and only interested in years 1970, 1980-2003.  Copy and paste this data into a blank Excel file. 

    Table 11 contains data on the US population by age groups (it only has 1970 and then 1980 to 2003).  You are interested in males age 18-24 (a separate age group toward the bottom of the list)  This will be a little tricky because you need to click on the bottom tabs to find the data for the separate years. Copy and paste this data in the same Excel file as above matching it up with the murder rate data for the same years.  Next, you need to calculate the percent of the population made up of males, age 18-24.  To do this, you will need the data in table 2 which contains the US population from 1900-2003.  Again, you are only interested in 1970 and 1980-2003.  Copy and paste the total population of the US for those years into the Excel file.  Then add a column containing the percent of the population made up of males, age 18-24.  When you calculate the percentage DO NOT hit the % key as you usually do. Instead multiply by 100. This will make it easier for Excel to graph the two groups of data together.

Now make an x-y scatter plot of the data the homicide rate and % of population.  To do this highlight the data in the three important columns: the year, the homicide rate, and the percentage. Copy and paste your graph into your word document.  Write a brief paragraph describing how your graph compares to the graph in the article.  Make sure to note any differences in the data.