SQL and EDA of Election Data

1 Section - Objectives

The objective of this project is to create a relational database and pull various data sets together into in order to perform exploratory data analysis of Election, Unemployment, Poverty, and Education data. The goal is to learn how to use sql and data management skills to pull needed variables into one dataframe to perform necessary Exploratory Data Analysis.

1.1 Data Integration

1.1.1 Data Preparation - Initial Download and Analysis

In order to create a database all four of the datasets needed must be downloaded. Once all data is incorporated it is necessary to get a basic understanding of each dataset. The four datasets used in this project as education, which contains variables looking at education levels in various states. The second dataset is unemployment, which contains information pertaining to unemployment rates in different states and counties. The third dataset is poverty, which pertains information about poverty rates across the united states. The final dataset is contains election data, such as parties and the winning party for previous elections.

Here is a quick summary of the four datasets. A connection is then made to a sqlite database and data tables are input. Now the process of data integration can begin.

1.1.2 Data Preparation - Presidential Election Data

When preparing the first data set, Presidential Election Data, it must fit the following requirements: 1.only use the 2020 election data 2.only keep the data for the two major parties: Democrats and Republicans 3.aggregate the total votes and keep the winning party in the data 4.county FIPS code, State name, county name, total votes received in the winning party, and the name of the party.

SELECT 
    county_fips AS fips_code,
    state,
    county_name,
    SUM(totalvotes) AS total_votes,
    CASE 
        WHEN SUM(CASE WHEN party = 'DEMOCRAT' THEN candidatevotes ELSE 0 END) > 
             SUM(CASE WHEN party = 'REPUBLICAN' THEN candidatevotes ELSE 0 END) 
        THEN 'DEMOCRAT'
        ELSE 'REPUBLICAN'
    END AS winning_party
FROM 
    election
WHERE 
    year = 2020 
    AND party IN ('DEMOCRAT', 'REPUBLICAN')
GROUP BY 
    county_fips, state, county_name;

1.1.3 Data Preparation - Unemployment Data

When preparing the second data set, Unemployment Data, it must fit the following requirements: 1.Only keep the unemployment date in the year 2020 or the most recent year if the 2020 employment rate is unavailable 2.County FIPS code 3.Unemployment rate

I renamed ‘FIPS_Code’ to ‘fips_code’ for data consistency. Value was also renamed to unemployment_rate for clarity when it comes to merging datasets. The substring function was used on the ‘Attribute’ varible in order to extract the year 2020. This was aptly named ‘year’.

SELECT 
    FIPS_Code AS fips_code,
    Value AS unemployment_rate, 
    SUBSTR(Attribute, -4) AS year
FROM 
    unemployment
WHERE 
    year = "2020"

1.1.4 Data Preparation - Poverty Data

When preparing the third data set, Poverty Data, it must fit the following requirements: 1.Keep only 2019 poverty rate 2.County FIPS code

I renamed ‘FIPStxt’ to ‘fips_code’ for data consistancy. Value was also renamed to poverty_rate for clarity when it comes to merging datasets.

SELECT 
    FIPStxt AS fips_code,
    Value as poverty_rate
FROM 
    poverty
WHERE 
    Attribute = "PCTPOVALL_2019";

1.1.5 Data Preparation - Education Data

When preparing the final data set, Education Data, it must fit the following requirements: 1.only keep the percentage of education levels between 2015 and 2019. a.Education levels b.less than a high school diploma c.high school diploma d.completed some college (1-3 years) e.completed four years of college 2.County FIPS code

I renamed the variables above to ‘fips_code’ and replaced all “.” with “_” in order to run a sql query.

SELECT 
    fips_code,
    Percent_of_adults_with_less_than_a_high_school_diploma__2015_19,
    Percent_of_adults_with_a_high_school_diploma_only__2015_19,
    Percent_of_adults_completing_some_college_or_associate_s_degree__2015_19,
    Percent_of_adults_with_a_bachelor_s_degree_or_higher__2015_19
FROM 
    education
GROUP BY 
    fips_code;

1.1.6 Merge Datasets

Here I left joined all datasets together. Then, using the dplyr library, I removed all duplicates based on the fips_code and any missing observations. The final dataset is called fips_202_elections. This dataset has 3113 observations consisting of 12 variables of interest.

2 Exploratory Data Anaylsis

2.1 Univariate Analysis

The first EDA I want to do is a univariate analysis of the variable “winning_party” Below is pie chart that shows the distribution of winning parties.

From this chart we can see that the republican party has a higher amount of winning counties than the democratic party. However, in the electoral college, it is not how many counties your party wins, but how many delegates belong to that county. In 2020 the Democratic party won the presidency, this was done by winning fewer but larger counties with more delegates.

2.2 Bivariate Anaylsis

In looking at two continous varibles “poverty_rate” and “unemployment_rate” the best way to visualize if there is any association is through a scatterplot. The plot can be seen below.

Here we can see that there does not seem to be an association between poverty rate and unemployment rate in the 2020 presidential election. A statistical test to confirm this would be Pearson’s Correlation test. ## Pairwise comparisons

A pairwise comparisons was conducted for adults and various educational levels. The graphic can be seen below.

# Results and Discussions In this project four datasets were queried in a SQLite database and merged into one dataframe in order to perform Exploratory Data Analysis. When looking at the distribution of winning parties, we see that the Republication Party won a larger amount of counties compared to the Democratic Party. In a Bivariate analysis looking for an association between poverty rate and unemployment rate, none was found. Finally a pairwise analysis was conducted looking at correlations between party and educational levels. Here it can be seen higher education had a negative correlation both parties, except for republicans with a bachelor degree or higher. This can be due to where colleges are located in specific counties or job opportunists for professionals.