This is a small research project.

As a first step, for 11 selected New York State counties, calculate 2020 household income GINI coefficients and the corresponding mean/median income ratios. In a second step, create two scatterplots in Excel.

The first scatterplot shows the mean/median income ratios on the vertical y-axis and the Gini coefficients (as calculated in the first step) on the horizontal x-axis.

The second scatterplot shows violent crime rates (per 100,000 population - data provided on a separate Excel sheet) on the vertical y-axis and the Gini coefficients (as calculated in the first step) on the horizontal x-axis. Part 1 Compute 2020 ACS* 5-year estimates household income GINI coefficients for 11 New York State counties: (1) Suffolk, (2) Nassau, (3) Queens, (4) Kings (= Brooklyn), (5) Richmond (= Staten Island), (6) New York (= Manhattan), (7) Bronx, (8) Westchester, (9) Rockland, (10) Putnam, and (11) Orange; all these counties are in or around New York City (see the map below).

* ACS stands for American Community Survey

Go to data.census.gov and search for "income Queens County New York". Make sure you have HOUSEHOLDS, 2020 ACS 5-YEAR ESTIMATES data for Queens County (highlighted below).

Make sure you use American Community Survey S1901 data (2020: ACS 5-Year Estimates Subject Tables - do not use ACS 1-year averages or others) and use household incomes.

Also, for some counties, the names may be case sensitive (New York vs. new york). Be extra careful and always check on the left that you have the data for the correct county (if you mistype, it will automatically use National data). There will no credit it you use wrong data!

Calculate the income GINI coefficient for households in Queens County. Do the same for the other remaining 10 counties. You need to calculate a total of 11 GINI coefficients.

Show your work on 11 different Excel worksheets. Also calculate the respective mean/median income ratios. List your results for all counties on a separate summary worksheet.

Please use the solution template that is posted under "Content" on Brightspace. Part 2 Now produce two scatterplots in Excel, on the Summary worksheet.

For both scatterplots plot the GINI coefficients from Part 1 on the horizontal x-axis! (Excel scatterplots are x-y scatterplots. That is, if you have two columns, your y-data are on the right) a) GINI and Mean/Median Income ratios b) GINI and Violent Crime Rates NY State provides crime statistics at https://www.criminaljustice.ny.gov/crimnet/ojsa/countycrimestats.htm Select violent crime rates (which are per 100,000 population) for 2020. For each scatterplot, add a linear trendline that also shows the equation and the R-squared value. Do not set the intercept.