Selection of Data Set
I got the data of education, unemployment, poverty and population from data.org. I downloaded the data in an excel format. The data covers all the States including Puerto Rico. The data for education, unemployment and population have time series.
Cleaning the Data
I wanted to focus on Virginia so I deleted the data for the States that are not needed. I also deleted other columns such that have values and not rates. This is because due to difference in for example size, economic activities, etc., I wanted to focus on rates which may be comparable. I used the Microsoft Excel software to clean the data format the table and to do the pivot table.
Formatting the Data as Table
In order to prepare the data to be used for the pivot table, I have to summarize the time series by finding and using the averages. I used data on average percent of adults with less than high school diploma, average unemployment rate, average household median income as percentage of State median household income and poverty rate for 132 counties and cities in Virginia. I have to take out Clifton Forge City and Bedford City because they do not have values for the variables above with the exception of education.
Pivot Table
I did the pivot table based on the counties and cities data on average percent of adults with less than high school diploma, average unemployment rate, average household median income as percentage of State median household income and poverty rate. I used the pivot table to find the average , minimum value and maximum value for the variable. For education Buchanan County has about 52% of adults with less than high school diploma from the estimated average with data from 1970 to 2022. Falls Church City has a minimum percent (9.5%) of adults with less than high school diploma. Attached is the excel document with the data for the pivot table, and the data for the pivot chart.
Preliminary Findings
Education may have some effect on unemployment, household income and poverty. The pivot table shows that Buchanan County with the percent of adults without high school diploma has an unemployment rate higher than than Virginia unemployment rate, a median household income that is about half of the Virginia average and a poverty rate which is about 10% higher than Virginia average.
Image
This is the image chart trend of the percentage of adults with less than high school diploma in the US, Virginia, Buchanan County and Falls Church City from 1970 to 2022.
