PS06#
DS256, Gettysburg College
Prof Eatai Roth
Due: Nov 3, 2025 5p
Your Name: …
Collaborators: …
By submitting this work, you attest that:
it reflects your own effort and understanding of the material
you did not ask for help from students outside the course, other than PLAs
you did not use a generative AI to produce any code used below.
1.
Looking again at the dataframe of election results. It’s not formatted in the most convenient way. Let’s improve the organization of this dataframe.
Goal:
One row for each county-and-year combo.
Within a row, there should be columns for each party candidate and the number of votes they received.
No duplicated or unnecessary data.
Columns should be:
year
state_po
county
county_fips
candidate_D
votes_D
candidate_R
votes_R
totalvotes
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
elec_df = pd.read_csv('https://raw.githubusercontent.com/GettysburgDataScience/datasets/refs/heads/main/us_political/countypres_2000-2024.csv')
elec_df.head()
| year | state | state_po | county_name | county_fips | office | candidate | party | candidatevotes | totalvotes | version | mode | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2000 | ALABAMA | AL | AUTAUGA | 1001.0 | US PRESIDENT | AL GORE | DEMOCRAT | 4942 | 17208 | 20250821 | TOTAL |
| 1 | 2000 | ALABAMA | AL | AUTAUGA | 1001.0 | US PRESIDENT | GEORGE W. BUSH | REPUBLICAN | 11993 | 17208 | 20250821 | TOTAL |
| 2 | 2000 | ALABAMA | AL | AUTAUGA | 1001.0 | US PRESIDENT | OTHER | OTHER | 113 | 17208 | 20250821 | TOTAL |
| 3 | 2000 | ALABAMA | AL | AUTAUGA | 1001.0 | US PRESIDENT | RALPH NADER | GREEN | 160 | 17208 | 20250821 | TOTAL |
| 4 | 2000 | ALABAMA | AL | BALDWIN | 1003.0 | US PRESIDENT | AL GORE | DEMOCRAT | 13997 | 56480 | 20250821 | TOTAL |
2. The remainder of the assignment uses the dataframe you re-organized above in conjunction with two other dataframes:
ev_df- the Washington state electric vehicle dataframe from last classagesex_df- a dataframe of age and sex demographics for all US counties.
ev_url = 'https://raw.githubusercontent.com/GettysburgDataScience/datasets/refs/heads/main/Electric_Vehicle_WA.csv'
ev_df = pd.read_csv(ev_url)
agesex_df = pd.read_csv('https://raw.githubusercontent.com/GettysburgDataScience/datasets/refs/heads/main/us_political/stats_america/Population-by-Age-and-Sex/Population%20by%20Age%20and%20Sex%20-%20US%2C%20States%2C%20Counties.csv')
display(ev_df.head())
display(agesex_df.head())
| VIN (1-10) | County | City | State | Postal Code | Model Year | Make | Model | Electric Vehicle Type | Clean Alternative Fuel Vehicle (CAFV) Eligibility | Electric Range | Base MSRP | Legislative District | DOL Vehicle ID | Vehicle Location | Electric Utility | 2020 Census Tract | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | WA1E2AFY8R | Thurston | Olympia | WA | 98512.0 | 2024 | AUDI | Q5 E | Plug-in Hybrid Electric Vehicle (PHEV) | Not eligible due to low battery range | 23.0 | 0 | 22.0 | 263239938 | POINT (-122.90787 46.9461) | PUGET SOUND ENERGY INC | 5.306701e+10 |
| 1 | WAUUPBFF4J | Yakima | Wapato | WA | 98951.0 | 2018 | AUDI | A3 | Plug-in Hybrid Electric Vehicle (PHEV) | Not eligible due to low battery range | 16.0 | 0 | 15.0 | 318160860 | POINT (-120.42083 46.44779) | PACIFICORP | 5.307794e+10 |
| 2 | 1N4AZ0CP0F | King | Seattle | WA | 98125.0 | 2015 | NISSAN | LEAF | Battery Electric Vehicle (BEV) | Clean Alternative Fuel Vehicle Eligible | 84.0 | 0 | 46.0 | 184963586 | POINT (-122.30253 47.72656) | CITY OF SEATTLE - (WA)|CITY OF TACOMA - (WA) | 5.303300e+10 |
| 3 | WA1VAAGE5K | King | Kent | WA | 98031.0 | 2019 | AUDI | E-TRON | Battery Electric Vehicle (BEV) | Clean Alternative Fuel Vehicle Eligible | 204.0 | 0 | 11.0 | 259426821 | POINT (-122.17743 47.41185) | PUGET SOUND ENERGY INC||CITY OF TACOMA - (WA) | 5.303303e+10 |
| 4 | 7SAXCAE57N | Snohomish | Bothell | WA | 98021.0 | 2022 | TESLA | MODEL X | Battery Electric Vehicle (BEV) | Eligibility unknown as battery range has not b... | 0.0 | 0 | 1.0 | 208182236 | POINT (-122.18384 47.8031) | PUGET SOUND ENERGY INC | 5.306105e+10 |
| IBRC_Geo_ID | Statefips | Countyfips | Description | Year | Total Population | Population 0-4 | Population 5-17 | Population 18-24 | Population 25-44 | Population 45-64 | Population 65+ | Population Under 18 | Population 18-54 | Population 55+ | Male Population | Female Population | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | 0 | 0 | U.S. | 2000 | 282162411 | 19178293 | 53197896 | 27315274 | 84973340 | 62428040 | 35069568 | 72376189 | 150287588 | 59498634.0 | 138443407 | 143719004 |
| 1 | 0 | 0 | 0 | U.S. | 2001 | 284968955 | 19298217 | 53372958 | 27992652 | 84523274 | 64491563 | 35290291 | 72671175 | 151902194 | 60395586.0 | 139891492 | 145077463 |
| 2 | 0 | 0 | 0 | U.S. | 2002 | 287625193 | 19429192 | 53507265 | 28480708 | 83990295 | 66695526 | 35522207 | 72936457 | 152463197 | 62225539.0 | 141230559 | 146394634 |
| 3 | 0 | 0 | 0 | U.S. | 2003 | 290107933 | 19592446 | 53508312 | 28916746 | 83398001 | 68828899 | 35863529 | 73100758 | 153134701 | 63872474.0 | 142428897 | 147679036 |
| 4 | 0 | 0 | 0 | U.S. | 2004 | 292805298 | 19785885 | 53511850 | 29302179 | 83066831 | 70935234 | 36203319 | 73297735 | 153998940 | 65508623.0 | 143828012 | 148977286 |
2
a. What is the fips of the PA county with the greatest republican lean in 2024?
b. In the US in 2010, what is the fips (same as IBRC_Geo_ID) of the county that had the highest percentage of individuals aged 65+?
c. In Washington state 2024, how many electric vehicles were in the county with the highest percentage of people in the age range 18-44?
d. Rounded to the nearest percentage, what was the lean of that county (as a positive number)?
e. Find the most popular make of electric car in the Washington state county that has the greatest democratic lean. How many vehicles of that make are registered in that state?
f. Create three scatter plots, for each of 2016, 2020, 2024 in which each data point represents a county, the x-axis is percentage of people age 18-24 and the y-axis is political leaning of the county.