eco table

The overall aim of our lab section this term is to increase your proficiency in Microsoft Excel and data analysis. By the end of the term, you should feel comfortable taking on any workplace assignments that involves data analysis through Excel. The first official lab aims to teach you a few lessons:

· Learn how to collect publicly accessible data.

· Find the descriptive measures of the data.

· Show the distribution of the data with a histogram.

· Present the data in a format that is easy-to-read for a wide audience.

This week we will obtain data on the Violent and Property Crime rates in Riverside, as well as a city of your choice. Data on crime rates can be found at FBI’s Uniform Crime Reporting website at: https://www.ucrdatatool.gov/ .

Here are the steps that your lab TA will go through with you.

1. Go to https://www.ucrdatatool.gov/ , click on “Go to the table-building tool” at the bottom of the page:

2. On the next page, you will see the possible geographical coverage areas that the FBI reports the data on the left-hand side (All States and US Total, Agencies by State, Larger Agencies). Click on “Larger Agencies”:

3. On the next screen, you will see the different ways that you can download data (Single agency reported crime, One Variable of Data or One year of data). Click on “Single agency reported crime”:

4. The next screen allows you to restrict which types of police departments you can look at. You can click “All” and then next.

5. This final screen allows you to choose the police department you want to analyze, the type of variables you want to obtain and the years you are interested in:

6. Scroll through the police departments/agencies (a.) and click on “Riverside Police Dept”. In (b.), hold down Ctrl and click on “Violent crime rates” and “Property crime rates”. Both terms should be highlighted. The years to include (c.) should go from 1985 to 2014, the maximum range. Click “Get Table”.

7. The following screen will show the rates for Violent Crime and Property crime in Riverside between 1985 and 2014. This is the “raw data”. Quickly get this into an Excel spreadsheet by clicking “Spreadsheet of this table (.csv file)” at the top of the page:

A download should start and when you open up the file, Microsoft Excel will open up and the same data on the webpage will be available in the Excel file.

8. Now that you have your data in Excel, you will be able to obtain a number of statistics. Create a table that shows the average/mean, median, variance and standard deviation for both violent crime and property crime. Plot the histogram for both violent crime and property crime setting the number of bins is 10. PS: you will use the Excel functions: average, median, var.s and stdev.s.

9. Make sure your table in a Microsoft Word document is easy-to-read and informative.

10. Return to the screen where you can choose which agency you want to examine. Choose an agency that you are interested in. The agency can be where you live, where you were born, where you vacation or a place you are interested in learning about. Make sure that the agency is a Police Department instead of a Sheriff’s Department.

11. Create a table with the mean, median, variance and standard deviation. Plot the histogram for both violent crime and property crime setting the number of bins is 10.

12. Combine the new table with the table that you created using the Riverside data and transfer it to a Microsoft Word document.

13. In one paragraph, describe your tables and compare the statistics between the cities in your table.

Turn in your printed document (table, histogram and paragraph）