Exploration questions
Data Sources
Methodology
Summary
# Load necessary packages for data reading and visualization
import numpy as np
import pandas as pd
import matplotlib.pylab as plt
plt.style.use('ggplot')
import seaborn as sns
# Load the dataset
licensed_df = pd.read_csv('/work/Licensed_drivers_By_State.csv')
licensed_df.head()
# Display the data shape summary information
licensed_df.info()
# Dataset statistics
licensed_df.describe()
# Check data for null values
licensed_df.isnull().sum()
# Look for the null values row in Drivers column
licensed_df[licensed_df.isnull().any(axis=1)]
# Check if we have duplicates for null values row to understand if its missing or undefined value
licensed_df[(licensed_df['Year'] == 2011) & (licensed_df['Gender'] == 'Male') & (licensed_df['Cohort'] == 'Under 16') & (licensed_df['State'] == 'New Mexico')]
# Replace Nan value with 0 and recheck count of nulls
licensed_df['Drivers'] = licensed_df['Drivers'].fillna(0)
licensed_df.isnull().sum()
# Check data for duplicate values
duplicates = licensed_df.duplicated()
print(f"Dataset contains {duplicates.sum()} duplicated values")
licensed_df[duplicates]
# Check if we have duplicates for null values row to understand if its missing or undefined value
# licensed_df[(licensed_df['Year'] == 1994) & (licensed_df['Gender'] == 'Female') & (licensed_df['Cohort'] == 'Under 16') & (licensed_df['State'] == 'Hawaii')]
# Drop duplicates and check that changes took place in the dataset
print(f"Before: dataset contains {licensed_df.shape[0]} rows and {licensed_df.shape[1]} columns ")
print(f"Dataset contains {duplicates.sum()} duplicated values")
licensed_drivers_df = licensed_df.drop_duplicates()
print(f"After: dataset contains {licensed_drivers_df.shape[0]} rows and {licensed_drivers_df.shape[1]} columns ")
# Check for duplicated rows by skipping Drivers column
duplicates = licensed_drivers_df.duplicated(subset=['Year', 'Gender', 'Cohort', 'State'])
print(f"Dataset contains {duplicates.sum()} duplicated values")
# Display all duplicated rows
key_names = ['Year', 'Gender', 'Cohort', 'State']
duplicates = licensed_drivers_df.duplicated(subset=key_names, keep=False)
licensed_drivers_df[duplicates].sort_values(by=key_names)
# Drop duplicated rows for year '1994', Gender 'Female' by keeping first row
key_names = ['Year', 'Gender', 'Cohort', 'State']
duplicates = licensed_drivers_df.duplicated(subset=key_names, keep='first')
licensed_drivers_df[duplicates].drop_duplicates()
# Store key_names = ['Year', 'Gender', 'Cohort', 'State']
duplicates = licensed_drivers_df.duplicated(subset=key_names, keep="first")
drivers_df = licensed_drivers_df.drop_duplicates(subset=key_names)
drivers_df
Driver Gender
SELECT *
FROM drivers_df
The distribution of drivers by gender spanning the years from 1994 to 2018.
SELECT Year,
Gender,
SUM(Drivers) as Total_drivers
FROM licensed_drivers
GROUP BY Year, Gender
with unstack as (
SELECT Year,
max(case when Gender = 'Male' then Total_drivers end) as Male,
max(case when Gender = 'Female' then Total_drivers end) as Female,
sum(Total_drivers) all_drivers
FROM drivers_per_gender_df
GROUP BY Year )
SELECT
Year, Male, Female,
(Male / all_drivers) * 100 perc_male,
(Female / all_drivers) * 100 perc_female,
((Male / all_drivers) * 100) - ((Female / all_drivers) * 100) diff,
((Male / all_drivers) * 100) - 50 diff50male,
((Female / all_drivers) * 100) - 50 diff50female
from unstack
# Palette
color_male = '#2f4b7c'
color_female = '#a05195'
color_total = '#665191'
# color_repub = '#ff7c43'
# color_democ = '#003f5c'
custom_palette = {'Male': color_male, 'Female': color_female}
plt.figure(figsize=(12, 6))
sns.barplot(x='Year', y='Total_drivers', hue='Gender', data=drivers_per_gender_df, palette=custom_palette)
plt.title('Total Drivers Per Gender Over Time')
plt.xlabel('Year')
plt.ylabel('Total Drivers')
plt.legend(title='Gender')
plt.xticks(rotation=90)
plt.tight_layout()
plt.show()
# I've flipped the plot to horizontal, and limited the axis so the differences are more visible
sns.set_style('whitegrid')
custom_palette = {'Male': '#5EB4FF', 'Female': '#CC4A79'}
plt.figure(figsize=(4, 7))
sns.barplot(y='Year', x='Total_drivers', hue='Gender',
data=drivers_per_gender_df, palette=custom_palette,
orient="h") # i confess i did not know how to flip!
plt.title('Total Drivers Per Gender Over Time')
plt.ylabel('Year')
plt.xlabel('Total Drivers')
plt.xlim(left=82000000)
plt.legend(title='Gender')
# plt.xticks(rotation=90)
plt.tight_layout()
sns.despine(left=True, bottom=True)
plt.show()
# % diff from midpoint (50%).
# colors chosen using this color picker https://davidmathlogic.com/colorblind/#%23CC4A79-%235EB4FF-%23FFC107-%23004D40
sns.set_style('whitegrid')
f,ax = plt.subplots(1, 1) # helpful if you are either overlaying figures, or want to create a multi-panel style display
sns.barplot(y='Year', x='diff50female', color='#CC4A79', label='Female',
data=drivers_per_gender_df_2,
orient="h", ax=ax)
plt.title('Female drivers as percent of total driver population, 1994-2018')
plt.xlabel('Difference from 50%')
plt.tight_layout()
sns.despine(left=True)
f.show()
More exploration and insights
The distribution of drivers categorized by both gender and state.
SELECT State,
SUM(Drivers) as Total_drivers,
SUM(CASE WHEN Gender = 'Female' THEN Drivers ELSE 0 END) as Total_female,
SUM(CASE WHEN Gender = 'Male' THEN Drivers ELSE 0 END) as Total_male,
ROUND(SUM(CASE WHEN Gender = 'Female' THEN Drivers ELSE 0 END)/SUM(Drivers)*100, 2) as Female_perc,
ROUND(SUM(CASE WHEN Gender = 'Male' THEN Drivers ELSE 0 END)/SUM(Drivers)*100, 2) as Male_perc
FROM licensed_drivers
GROUP BY State
SELECT State,
ROUND((SUM(Drivers) / (SELECT SUM(Drivers) as drivers FROM licensed_drivers)) *100,2) as Drivers_perc
FROM licensed_drivers
GROUP BY State
drivers_perc_per_state_sorted_df = drivers_perc_per_state_df.sort_values('Drivers_perc', ascending=False)
plt.figure(figsize=(10, 10))
sns.barplot(x='Drivers_perc', y='State', data=drivers_perc_per_state_sorted_df, color=color_total)
index = 0
padding = 0.1
for index_unused, row in drivers_perc_per_state_sorted_df.iterrows():
plt.text(row['Drivers_perc']+padding, index, f"{row['Drivers_perc']}%", va='center', ha="left", fontsize=8)
index += 1
plt.title('Drivers distribution by State (%)')
plt.xlabel('Pencentage')
plt.ylabel('State')
plt.xticks(rotation=0)
plt.show()
drivers_per_gender_per_state_sorted_df = drivers_per_gender_per_state_df.sort_values('Total_drivers', ascending=False)
plt.figure(figsize=(10, 10))
sns.barplot(x='Female_perc', y='State', data=drivers_per_gender_per_state_df, label='Female', color=color_female)
sns.barplot(x='Male_perc', y='State', data=drivers_per_gender_per_state_df, label='Male',color=color_male, left=drivers_per_gender_per_state_df['Female_perc'])
for index, row in drivers_per_gender_per_state_df.iterrows():
plt.text(row['Female_perc'] / 2, index, f'{row["Female_perc"]}%', va='center', ha='center', color='white', fontsize=10)
plt.text(row['Female_perc'] + row['Male_perc'] / 2, index, f'{row["Male_perc"]}%', va='center', ha='center', color='white', fontsize=10)
plt.title('Female and Male Drivers distribution by State (%)')
plt.xlabel('State')
plt.ylabel('Total Drivers')
plt.xticks(rotation=0)
plt.legend(title='Gender', loc='upper right')
plt.show()
The distribution of drivers is categorized by age group and gender.
SELECT Cohort,
SUM(Drivers) as Total_drivers,
SUM(CASE WHEN Gender = 'Female' THEN Drivers ELSE 0 END) as Female,
SUM(CASE WHEN Gender = 'Male' THEN Drivers ELSE 0 END) as Male
FROM licensed_drivers
GROUP BY Cohort
ORDER BY Total_drivers DESC
plt.figure(figsize=(12, 6))
sns.barplot(x='Cohort', y='Total_drivers',color=color_total, data=drivers_per_cohort_df)
plt.title('Drivers distribution over age group')
plt.xlabel('Age group')
plt.ylabel('Total Drivers')
plt.xticks(rotation=90)
plt.tight_layout()
plt.show()
The total count of years available in the dataset.
SELECT MAX(Year) - MIN(Year) as year_count
FROM licensed_drivers
List the number of years per each state where there were no drivers in the 'Under 16' age cohort
SELECT State,
COUNT(Distinct Year) as Total_years
FROM licensed_drivers
WHERE Drivers = 0 and Cohort = 'Under 16'
GROUP BY State
ORDER BY Total_years DESC, State ASC
Between 1994 and 2018, there were 24 states where no drivers under the age of 16 were documented.
plt.figure(figsize=(12, 6))
sns.barplot(x='State', y='Total_years',color=color_total, data=drivers_under_16_df)
plt.title('Total Years of No Drivers Under 16 by State')
plt.xlabel('State')
plt.ylabel('Total Years')
plt.xticks(rotation=90)
plt.tight_layout()
plt.show()
# Load election dataset
election_df = pd.read_csv('/work/Elections.csv', header=0)
election_df.head()
election_df.info()
election_df.describe()
election_df.isnull().sum()
The columns "candidate," "party_detailed," "writein," and "notes" contain null values, but they will not be utilized in the analysis.
duplicates_election = election_df.duplicated()
print(f"Dataset conteins {duplicates_election.sum()} duplicated values")
election_df[duplicates_election]
Filtering the data by year and assigning a single governing party for each year.
WITH ranked AS (
SELECT *,
DENSE_RANK() OVER (PARTITION BY year, state_po ORDER BY candidatevotes DESC) AS rank
FROM election_df
WHERE year >= 1992 AND year <= 2018)
SELECT year,
LOWER(state) AS state,
LOWER(party_simplified) AS party
FROM ranked
WHERE rank = 1
ORDER BY year, state
Verify if there are only two unique parties assigned to the states.
SELECT DISTINCT party
FROM elections_state_year_party_df
Elections occur once every four years, and for accurate results, it is necessary to assign election year party results to each year between elections.
SELECT year,
year-(year%4) as election_year_assign
FROM licensed_drivers
GROUP BY year
ORDER BY year
Examine both tables for the presence of null values.
SELECT COUNT(*) AS null_states
FROM licensed_drivers AS l
FULL JOIN elections_state_year_party_df AS e
ON LOWER(l.state) = e.state
WHERE l.state IS NULL OR e.state IS NULL
Merge two tables
SELECT l.*,
e.year AS year_election,
e.party
FROM licensed_drivers as l
INNER JOIN elections_state_year_party_df AS e
ON LOWER(l.state) = e.state AND (l.year-(l.year%4)) = e.year
WITH total_drivers AS (
SELECT year,
party,
SUM(drivers) AS total_drivers_num
FROM licenses_elections_medged_df
GROUP BY year, party
)
SELECT l.year,
l.party,
l.gender,
ROUND(SUM(l.drivers)/MIN(td.total_drivers_num)*100,2) AS perc
FROM total_drivers AS td
INNER JOIN licenses_elections_medged_df as l
ON td.year = l.year AND td.party = l.party
WHERE LOWER(gender) = 'female'
GROUP BY l.year, l.party, l.gender
custom_palette = {'republican': color_repub, 'democrat': color_democ}
plt.figure(figsize=(12, 6))
sns.barplot(x='Year', y='perc', hue='party', data=female_party_percentage_df, palette=custom_palette)
plt.title('Female drivers per governing party over time')
plt.xlabel('Year')
plt.ylabel('Perc')
plt.legend(title='Party', loc='lower right')
plt.xticks(rotation=90)
plt.tight_layout()
plt.show()
WITH total_drivers AS (
SELECT year,
state,
gender,
SUM(drivers) AS drivers
FROM licensed_drivers
WHERE state IN ('New York', 'California')
GROUP BY year, state, gender
)
SELECT l.year,
l.state,
l.gender,
SUM(td.drivers) AS Total,
SUM(l.drivers) AS Drivers,
ROUND(SUM(l.drivers)/MIN(td.drivers)*100,2) AS Perc
FROM licensed_drivers AS l
INNER JOIN total_drivers AS td
ON l.year = td.year
AND l.state = td.state
AND l.gender = td.gender
WHERE l.state IN ('New York', 'California')
AND l.cohort IN ('Under 16', '16', '17')
GROUP BY l.year, l.state, l.gender
ORDER BY drivers DESC
custom_palette = {'Male': color_male, 'Female': color_female}
plt.figure(figsize=(10, 5))
sns.barplot(x='State', y='Perc', hue='Gender', data=ny_la_analysis_df, palette=custom_palette)
plt.title('Early age drivers distribution by gender in California and New York states')
plt.xlabel('State')
plt.ylabel('Gender')
plt.legend(title='Percentage')
plt.xticks(rotation=0)
plt.tight_layout()
plt.show()
How changed the parties distribution over states per election year?
SELECT Year,
COUNT(CASE WHEN party='republican' THEN party END) as republican_total,
COUNT(CASE WHEN party='democrat' THEN party END) as democrat_total
FROM elections_state_year_party_df
GROUP BY Year
custom_palette = {'republican': color_repub, 'democrat': color_democ}
plt.figure(figsize=(12, 4))
sns.barplot(x='year', y='republican_total', label='Republican', color=color_repub, data=total_party_per_year_df)
sns.barplot(x='year', y='democrat_total', label='Democrat', color=color_democ, data=total_party_per_year_df)
plt.xlabel('Year')
plt.ylabel('Party Distribution')
plt.title('Party Distribution Over Election Years')
plt.legend()
plt.show()
Display the absolute value
SELECT Year,
SUM(CASE WHEN Gender='Female'AND party = 'republican' THEN Drivers ELSE 0 END) as Female_repub_drivers,
SUM(CASE WHEN Gender='Male' AND party = 'republican' THEN Drivers ELSE 0 END) as Male_repub_drivers,
SUM(CASE WHEN Gender='Female' AND party = 'democrat' THEN Drivers ELSE 0 END) as Female_democ_drivers,
SUM(CASE WHEN Gender='Male' AND party = 'democrat' THEN Drivers ELSE 0 END) as Male_democ_drivers
FROM licenses_elections_medged_df
GROUP BY Year
ORDER BY Year ASC
SELECT l2.Year,
ROUND((l2.Female_repub_drivers-l1.Female_repub_drivers)/l2.Female_repub_drivers*100, 2) AS Fem_rep_run_diff,
ROUND((l2.Male_repub_drivers-l1.Male_repub_drivers)/l2.Male_repub_drivers*100, 2) AS Male_rep_run_diff,
ROUND((l2.Female_democ_drivers-l1.Female_repub_drivers)/l2.Female_repub_drivers*100, 2) AS Fem_dem_run_diff,
ROUND((l2.Male_democ_drivers-l1.Male_democ_drivers)/l2.Male_democ_drivers*100, 2) AS Male_dem_run_diff
FROM gender_drivers_per_party AS l1
INNER JOIN gender_drivers_per_party AS l2 ON l1.Year = l2.Year-1
ORDER BY l2.Year
plt.figure(figsize=(12, 6))
ax = plt.gca()
years = running_diff_per_gerder_year_df['Year']
positions = range(len(years))
bar_width = 0.2
for i, gender_party in enumerate(['Fem_rep_run_diff', 'Male_rep_run_diff', 'Fem_dem_run_diff', 'Male_dem_run_diff']):
plt.bar(
[pos + i * bar_width for pos in positions],
running_diff_per_gerder_year_df[gender_party],
width=bar_width,
label=gender_party
)
plt.xlabel('Year')
plt.ylabel('Running Difference (%)')
plt.title('Running Difference in Percentage of Drivers by Gender and Party')
plt.xticks([pos + bar_width for pos in positions], years)
plt.xticks(rotation=90)
plt.legend()
plt.grid(axis='y')
plt.tight_layout()
plt.show()
WITH election_years AS (
SELECT DISTINCT Year FROM elections_state_year_party_df
)
SELECT l2.Year,
ROUND((l2.Female_repub_drivers-l1.Female_repub_drivers)/l2.Female_repub_drivers*100, 2) AS Fem_rep_run_diff,
ROUND((l2.Male_repub_drivers-l1.Male_repub_drivers)/l2.Male_repub_drivers*100, 2) AS Male_rep_run_diff,
ROUND((l2.Female_democ_drivers-l1.Female_repub_drivers)/l2.Female_repub_drivers*100, 2) AS Fem_dem_run_diff,
ROUND((l2.Male_democ_drivers-l1.Male_democ_drivers)/l2.Male_democ_drivers*100, 2) AS Male_dem_run_diff
FROM gender_drivers_per_party AS l1
INNER JOIN gender_drivers_per_party AS l2 ON l1.Year = l2.Year-1
LEFT JOIN election_years AS ey ON ey.Year = l2.Year
WHERE ey.Year IS NULL
ORDER BY l2.Year
plt.figure(figsize=(12, 6))
ax = plt.gca()
years = running_diff_skip_elect_year_df['Year']
positions = range(len(years))
bar_width = 0.2
for i, gender_party in enumerate(['Fem_rep_run_diff', 'Male_rep_run_diff', 'Fem_dem_run_diff', 'Male_dem_run_diff']):
plt.bar(
[pos + i * bar_width for pos in positions],
running_diff_skip_elect_year_df[gender_party],
width=bar_width,
label=gender_party
)
plt.xlabel('Year')
plt.ylabel('Running Difference (%)')
plt.title('Running Difference in Percentage of Drivers by Gender and Party by skipping election years')
plt.xticks([pos + bar_width for pos in positions], years)
plt.xticks(rotation=90)
plt.legend()
plt.grid(axis='y')
plt.tight_layout()
plt.show()