# Introduction I usually do most of my random pondering about life either while driving or in the shower. On a particular day on the I-95, I was whining to myself about the amount I spend on gas on my beautiful old gas guzzler, a 2010 BMW 328xi. She gives me about 21 MPG on the highway and elegantly requests only the finest of fuels from the premium aisle. I love my car but like Tiger Woods off the golf course, I started wondering about life on the other side. Electric cars are easier on the wallet in the long run but by how much? # Methodology ## 1. Get the Miles Per Gallon data The [EPA website](https://fueleconomy.gov/feg/download.shtml) has a downloadable CSV file on almost all cars made since 1984. The dataset includes almost all the relevant information about MPG for ICE (Internal combustion engine) vehicles, kWh for electric vehicles, and a combination of the two for hybrid vehicles [explained here](#hybrid). We also get information on the type of fuel a gas-powered or hybrid vehicle uses, i.e. whether the vehicle requires regular, mid-grade, or premium gasoline.

**Click to view python code**

```python import pandas as pd import numpy as np df = pd.read_csv('data/car-data/vehicles.csv', usecols=['year', 'make', 'model', 'barrels08', 'barrelsA08', 'city08', 'city08U', 'cityA08', 'cityA08U', 'cityCD', 'cityE', 'cityUF', 'co2TailpipeGpm', 'co2TailpipeAGpm', 'comb08', 'comb08U', 'combA08', 'combA08U', 'combE', 'combinedCD', 'combinedUF', 'cylinders', 'displ', 'drive', 'fuelCost08', 'fuelCostA08', 'fuelType', 'fuelType1', 'fuelType2', 'highway08', 'highway08U', 'highwayA08', 'highwayA08U', 'highwayCD', 'highwayE', 'highwayUF', 'phevBlended', 'rangeA', 'rangeCityA', 'rangeHwyA', 'trany', 'VClass', 'phevCity', 'phevHwy', 'phevComb', 'atvType']) ```` ### Data Legend: 1. year - model year 2. make - manufacturer (division) 3. model - model name (carline) 19. VClass - EPA vehicle size class 7. atvType - type of alternative fuel or advanced technology vehicle 3. cityCD - city gasoline consumption (gallons/100 miles) in charge depleting mode 4. cityE - city electricity consumption in kw-hrs/100 miles 7. comb08U - unrounded combined MPG for fuelType1 8. combA08U - unrounded combined MPG for fuelType2 9. combE - combined electricity consumption in kw-hrs/100 miles 10. combinedCD - combined gasoline consumption (gallons/100 miles) in charge depleting mode 11. highway08U - unrounded highway MPG for fuelType1 12. highwayA08U - unrounded highway MPG for fuelType2 13. highwayCD - highway gasoline consumption (gallons/100miles) in charge depleting mode 14. highwayE - highway electricity consumption in kw-hrs/100 miles 15. phevBlended - if true, this vehicle operates on a blend of gasoline and electricity in charge depleting mode 16. rangeA - EPA range for fuelType2 17. rangeCityA - EPA city range for fuelType2 18. rangeHwyA - EPA highway range for fuelType2 1. barrels08 - annual petroleum consumption in barrels for fuelType1 2. barrelsA08 - annual petroleum consumption in barrels for fuelType2 5. co2TailpipeAGpm - tailpipe CO2 in grams/mile for fuelType2 6. co2TailpipeGpm- tailpipe CO2 in grams/mile for fuelType1 7. fuelType - fuel type with fuelType1 and fuelType2 (if applicable) 8. fuelType1 - fuel type 1. For single fuel vehicles, this will be the only fuel. For dual fuel vehicles, this will be the conventional fuel. 9. fuelType2 - fuel type 2. For dual fuel vehicles, this will be the alternative fuel (e.g. E85, Electricity, CNG, LPG). For single fuel vehicles, this field is not used [Source](https://fueleconomy.gov/feg/ws/index.shtml) ### Let's remove vehicles that run on CNG, Natural gas, or propane. Why? Firstly because getting these rates is a little harder, and involves a few extra steps. We would probably have to scrape a few different websites to get this data. But the most important reason- most consumers are not going to be looking for these types of vehicles. Vehicles that run on CNG, Natural gas, or propane are mainly used in commercial applications or older vehicles ```python to_drop = df[(df['fuelType2'] == 'Natural Gas') | (df['fuelType2'] == 'Propane') | (df['fuelType1'] == 'Natural Gas')].index df.drop(index = to_drop, axis=1, inplace=True) df.reset_index(drop=True, inplace=True) ### Some vehicles do not have unrounded MPG values. For those vehicles, the unrounded MPG values are set to 0. This section will set those empty values to the rounded figures. df['city08U'] = np.where(df['city08U'] == 0, df['city08'], df['city08U']) df['cityA08U'] = np.where(df['cityA08U'] == 0, df['cityA08'], df['cityA08U']) df['comb08U'] = np.where(df['comb08U'] == 0, df['comb08'], df['comb08U']) df['combA08U'] = np.where(df['combA08U'] == 0, df['combA08'], df['combA08U']) df['highway08U'] = np.where(df['highway08U'] == 0, df['highway08'], df['highway08U']) df['highwayA08U'] = np.where(df['highwayA08U'] == 0, df['highwayA08'], df['highwayA08U']) ```` ### Saving results to csv ```python df.to_csv('data/car-data/vehicles-cleaned.csv') ```

## 2. Get the MSRP data Unfortunately, the above dataset does not have information about the price of each vehicle. To get this data, we have to scrape the [fueleconomy.gov](https://fueleconomy.gov/feg/findacar.shtml) website. I used the *selenium* python package for this, as I find that the commonly used *requests* python package has a lot of issues when it comes to dynamic websites. The *selenium* package also launches a browser window as it scrapes through webpages, and that helps a lot with debugging.

**Click to view python code**

```python import os import csv import pandas as pd import numpy as np from datetime import date from time import sleep from bs4 import BeautifulSoup from selenium import webdriver from selenium.common.exceptions import NoSuchElementException, ElementClickInterceptedException from selenium.webdriver.common.by import By driver = webdriver.Firefox() driver.implicitly_wait(10) category = [] car_name = [] config = [] per100mls = [] driver_mpg = [] annual_fuel_cost = [] msrp = [] energy_impact_score = [] greenhouse_emissions = [] combined_mpgf1 = [] mpg_unitsf1 = [] city_mpgf1 = [] # class is same, first city then highway hwy_mpgf1 = [] elegasmiles = [] # gas + electric miles. this contains all ele miles as well totalrange = [] # total car range for hybrids phevgascomb = [] # combined mpg (city + hwy) for hybrids phevgasper100 = [] # combined gal/100 miles (city + hwy) for hybrids fuel_type = [] ``` ### Code for scraping the website ```python def get_mpg_data(cat): category = [] car_name = [] config = [] fuel_type = [] per100mls = [] driver_mpg = [] annual_fuel_cost = [] msrp = [] energy_impact_score = [] greenhouse_emissions = [] combined_mpgf1 = [] mpg_unitsf1 = [] city_mpgf1 = [] hwy_mpgf1 = [] elegasmiles = [] # gas + electric miles. this contains all ele miles as well totalrange = [] # total car range for hybrids phevgascomb = [] # combined mpg (city + hwy) for hybrids phevgasper100 = [] # combined gal/100 miles (city + hwy) for hybrids page = driver.find_element(By.CSS_SELECTOR, 'table.cars.display.responsive.stickyHeader') soup = BeautifulSoup(page.get_attribute('innerHTML'), 'lxml') all_cards = soup.find_all('tr', class_='ymm-row') count = 0 for card in all_cards: count += 1 if count == 199: count = 0 category.append(cat) # append car category car_name.append(card.find('a').text) # append car name conf = card.find('span', class_='config').text # get car configuration conf = conf.split(',') ft = conf.pop(-1) # last part of conf has fuel type fuel_type.append(ft) # Todo: add this to output s = '-'.join(conf) # creating string from list config.append(s) card = card.next_sibling.next_sibling mpgepa = card.find('td', class_='mpg-epa') if 'Not Available' in mpgepa.text or 'Currently unavailable' in mpgepa.text: print(f'skipping {car_name[-1]} because it does not have MPG information') category = category[:-1] car_name = car_name[:-1] config = config[:-1] fuel_type = fuel_type[:-1] continue if 'and Electricity' in ft: r1 = card.findNext('td', class_='range phev') elegasmiles.append(r1.find('div', class_='cdRange').text) totalrange.append(r1.find('div', class_='phevRange').text) fuel2 = mpgepa.find('div', class_='fuel2') phevgascomb.append(fuel2.find('td', class_='mpg-comb').text) phevgasper100.append(fuel2.find('td', class_='cons').text) elif 'or Electricity' in ft: r1 = card.findNext('td', class_='range phev') elegasmiles.append(r1.find('div', class_='cdRange').text) totalrange.append(r1.find('div', class_='phevRange').text) fuel2 = mpgepa.find('div', class_='fuel2') phevgascomb.append(fuel2.find('td', class_='mpg-comb').text) phevgasper100.append(fuel2.find('td', class_='cons').text) else: elegasmiles.append(np.nan) totalrange.append(np.nan) phevgascomb.append(np.nan) phevgasper100.append(np.nan) combined_mpgf1.append(mpgepa.find('td', class_='mpg-comb').text) mpg_unitsf1.append(mpgepa.find('td', class_='unitsLabel').text) ctyhwyf1 = mpgepa.find_all('td', class_='ctyhwy') city_mpgf1.append(ctyhwyf1[0].text) # class is same, first city then highway hwy_mpgf1.append(ctyhwyf1[1].text) per100mls.append(mpgepa.find('td', class_='cons').text) driver_mpg.append(card.find('td', class_='mpg-user').text.replace('\t', '').replace('\n', '')) annual_fuel_cost.append(card.find('td', class_='ann-fuel-cost').text.replace('\t', '').replace('\n', '')) msrp.append(card.find('td', class_='msrp').text.replace('\t', '').replace('\n', '')) energy_impact_score.append(card.find('td', class_='energy').text.replace('\n', '')) greenhouse_emissions.append(card.find('td', class_='ghg').text.replace('\n', '')) rules = [len(category) == len(car_name), # checks if all lengths are equal len(car_name) == len(config), len(config) == len(per100mls), len(per100mls) == len(driver_mpg), len(driver_mpg) == len(annual_fuel_cost), len(annual_fuel_cost) == len(msrp), len(msrp) == len(energy_impact_score), len(energy_impact_score) == len(greenhouse_emissions), len(greenhouse_emissions) == len(combined_mpgf1), len(combined_mpgf1) == len(mpg_unitsf1), len(mpg_unitsf1) == len(city_mpgf1), len(city_mpgf1) == len(hwy_mpgf1), len(hwy_mpgf1) == len(elegasmiles), len(elegasmiles) == len(totalrange), len(totalrange) == len(phevgascomb), len(phevgascomb) == len(phevgasper100), len(phevgasper100) == len(fuel_type) ] if not all(rules): print('Error, data lengths not equal') return category, car_name, config, per100mls, driver_mpg, annual_fuel_cost, msrp, energy_impact_score, \ greenhouse_emissions, combined_mpgf1, mpg_unitsf1, city_mpgf1, hwy_mpgf1, elegasmiles, totalrange, \ phevgascomb, phevgasper100, fuel_type ``` ### Running the scraper for different categories of vehicles ```python site_dict = { # 'test': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcfamilySedans=Family+Sedans&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Family+Sedans&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200' 'Small Cars': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcsmallCars=Small+Cars&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Small+Cars&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Family Sedans': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcfamilySedans=Family+Sedans&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Family+Sedans&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Upscale Sedans': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcupscaleSedans=Upscale+Sedans&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Upscale+Sedans&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Luxury Sedans': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcluxurySedans=Luxury+Sedans&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Luxury+Sedans&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Large Sedans': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmclargeSedans=Large+Sedans&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Large+Sedans&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Hatchbacks': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmchatchbacks=Hatchbacks&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Hatchbacks&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Coupes': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcCoupes=Coupes&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Coupes&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Convertibles': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcconvertibles=Convertibles&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Convertibles&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Sports/Sporty Cars': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcSports_Sporty_Cars=Sports%2FSporty+Cars&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Sports%2FSporty+Cars&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Station Wagons': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcstationWagons=Station+Wagons&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Station+Wagons&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Pickup Trucks': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcpickupTrucks=Pickup+Trucks&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Pickup+Trucks&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'SUVs': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcsportUtilityVehicles=Sport+Utility+Vehicles&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Sport+Utility+Vehicles&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200', 'Minivans': 'https://www.fueleconomy.gov/feg/PowerSearch.do?action=PowerSearch&year1=2010&year2=2023&cbmcminivans=Minivans&minmsrpsel=0&maxmsrpsel=0&city=0&highway=0&combined=0&cbftreggasoline=Regular+Gasoline&cbftmidgasoline=Midgrade+Gasoline&cbftprmgasoline=Premium+Gasoline&cbftdiesel=Diesel&cbftelectricity=Electricity&YearSel=2010-2023&MakeSel=&MarClassSel=Minivans&FuelTypeSel=Regular+Gasoline%2C+Midgrade+Gasoline%2C+Premium+Gasoline%2C+Diesel%2C+Electricity&VehTypeSel=&TranySel=&DriveTypeSel=&CylindersSel=&MpgSel=000&sortBy=Comb&Units=&url=SearchServlet&opt=new&minmsrp=0&maxmsrp=0&minmpg=0&maxmpg=0&sCharge=&tCharge=&startstop=&cylDeact=&rowLimit=200' } for cat, link in site_dict.items(): # Loops through the different category pages print(cat) driver.get(link) sleep(5) # Have to add this otherwise selenium breaks once in a while i = 0 while True: i += 1 print(f'Scraping page {i} from {cat}') mpg_data = get_mpg_data(cat) # This function scrapes the data category += mpg_data[0] car_name += mpg_data[1] config += mpg_data[2] per100mls += mpg_data[3] driver_mpg += mpg_data[4] annual_fuel_cost += mpg_data[5] msrp += mpg_data[6] energy_impact_score += mpg_data[7] greenhouse_emissions += mpg_data[8] combined_mpgf1 += mpg_data[9] mpg_unitsf1 += mpg_data[10] city_mpgf1 += mpg_data[11] hwy_mpgf1 += mpg_data[12] elegasmiles += mpg_data[13] # allelemiles += mpg_data[14] totalrange += mpg_data[14] phevgascomb += mpg_data[15] phevgasper100 += mpg_data[16] fuel_type += mpg_data[17] try: driver.find_element(By.CSS_SELECTOR, 'a[aria-label="Next"]').click() except ElementClickInterceptedException: # If selenium does not find the next button, signaling that there is no next page, the loop breaks and break ``` ### Saving the data ```python headers = ['category', 'car_name', 'config', 'fuel_type', 'per100mls', 'driver_mpg', 'annual_fuel_cost', 'msrp', 'energy_impact_score', 'greenhouse_emissions', 'combined_mpgf1', 'mpg_unitsf1', 'city_mpgf1', 'hwy_mpgf1', 'elegasmiles', 'totalrange', 'phevgascomb', 'phevgasper100'] with open(f'data/car-data/{str(date.today())}.csv', 'w') as file: writer = csv.writer(file) writer.writerow(headers) for i in range(len(car_name)): writer.writerow([category[i], car_name[i], config[i], fuel_type[i], per100mls[i], driver_mpg[i], annual_fuel_cost[i], msrp[i], energy_impact_score[i], greenhouse_emissions[i], combined_mpgf1[i], mpg_unitsf1[i], city_mpgf1[i], hwy_mpgf1[i], elegasmiles[i], totalrange[i], phevgascomb[i], phevgasper100[i]]) ```

## 3. Combine the two datasets Now that we have information on the MPG of each vehicle and the price of the respective vehicle, we can combine this into one dataset. We then need to standardize the data from both datasets to make the text from both sources comparable. Next, we can match different attributes of each vehicle like make, model, year, config, etc., and save the resulting data in a csv After matching, data on about 12,340 vehicles manufactured from 2010 on-wards were gathered. Out of this: * 10,843 vehicles are gas powered * 98 vehicles are powered by Diesel * 347 are EVs * 787 are Hybrids, and 265 are Plug-in Hybrids

**Click to view python code**

```python import os import re import csv import pandas as pd import numpy as np pd.set_option('display.max_rows', 50) pd.set_option('display.max_columns', 50) ``` ## Prepare the MSRP dataset ```python msrpdf = pd.read_csv('data/car-data/2022-11-09.csv') # Removing formating, '$' signs and ',' msrpdf['msrp'] = msrpdf['msrp'].apply(lambda x: x.replace('\xa0', ' ').replace('$', '').replace(',', '').strip()) msrpdf = msrpdf[msrpdf['msrp'] != ''].reset_index(drop=True) msrpdf['fuel_type'] = msrpdf['fuel_type'].apply(lambda x: x.strip()) ```` ```python def parse_msrp(df): ''' Function to convert msrp from strings to int/float, also computes average. If a range of values are given, average is calculated else, single msrp is set into the average column ''' text = df['msrp'] regex = re.compile(r'\d+') matches = regex.findall(text) if len(matches) == 2: low_end = int(matches[0]) high_end = int(matches[1]) average = (high_end + low_end)/2 elif len(matches) == 1: low_end = np.nan high_end = np.nan average = int(matches[0]) else: raise Exception('Error in parsing MSRP') return low_end, high_end, average ``` ```python def parse_model(df): text = df['car_name'] split_text = text.split() txt_modifier = 2 year = split_text[0] make = split_text[1] if make in ['Land', 'Aston', 'Alfa', 'Roush', 'McLaren', 'CODA']: make += ' ' + split_text[2] txt_modifier = 3 model = '' for i in range(len(split_text) - txt_modifier): model += split_text[i+txt_modifier] + ' ' model = model.strip() return year, make, model ``` ```python def parse_trany(df): text = df['config'] regex = re.compile(r'(Automatic[a-zA-Z0-9$\- ]*$|Automatic[a-zA-Z0-9\(\- ]*-spd|Manual[a-zA-Z0-9\(\- ]*-spd)') matches = regex.findall(text) if len(matches) == 1: trany = matches[0] elif len(matches) == 0: print(f'Error - could not find trany: {text}') trany = np.nan elif len(mathces) > 1: raise Exception('Error in parsing displacement - too many options') regex = re.compile(r'\d?\d.\d L') matches = regex.findall(text) if len(matches) == 1: displacement = matches[0].replace(' L', '') elif len(matches) == 0: displacement = np.nan elif len(mathces) > 1: raise Exception('Error in parsing displacement - too many options') regex = re.compile(r'\d?\d cyl') matches = regex.findall(text) if len(matches) == 1: cylinders = matches[0].replace(' cyl', '') elif len(matches) == 0: cylinders = np.nan elif len(mathces) > 1: raise Exception('Error in parsing displacement - too many options') return trany, displacement, cylinders ``` ```python msrpdf[['msrp_low', 'msrp_high', 'msrp_average']] = msrpdf.apply(parse_msrp, axis=1, result_type ='expand') msrpdf[['year', 'make', 'model']] = msrpdf.apply(parse_model, axis=1, result_type ='expand') msrpdf[['trany', 'displacement', 'cylinders']] = msrpdf.apply(parse_trany, axis=1, result_type ='expand') # Probably could have done the below line at the import, but I wasn't sure which columns I would require until later msrpdf = msrpdf[['category', 'year', 'make', 'model', 'config', 'trany', 'displacement', 'cylinders', 'msrp_low', 'msrp_high', 'msrp_average', 'fuel_type']].copy() msrpdf['cylinders'] = msrpdf['cylinders'].astype('string') msrpdf['year'] = msrpdf['year'].astype('int') msrpdf['model'] = msrpdf['model'].apply(lambda x: x.replace(' ', ' ').replace(' inch', 'in').replace('inch', 'in').replace('-inch', 'in').replace('21-in', '21in').replace('22-in', '22in').replace('wheels', 'Wheels')) ``` ### Preparing the MPG data ```python mpgdf = pd.read_csv('data/car-data/vehicles-cleaned.csv', index_col=0) mpgdf = mpgdf[['year', 'make', 'model', 'highway08U', 'highwayCD', 'highwayE', 'city08U', 'cityCD', 'cityE', 'comb08U', 'combinedCD', 'combE', 'trany', 'displ', 'cylinders', 'atvType', 'fuelType1', 'fuelType2', 'rangeA']].copy() mpgdf['displ'] = mpgdf['displ'].astype('string') # converting to string since we will need to compare it later, comparing floats may create errors mpgdf['cylinders'] = mpgdf['cylinders'].astype('string') mpgdf['model'] = mpgdf['model'].apply(lambda x: x.replace(' ', ' ').replace('-inch', 'in').replace('wheels', 'Wheels').replace('MUSTANG MACH', 'Mustang Mach')) ``` ### Matching the common attributes between the two dataframes ```python def compare_datasets(df1, df2): # Could have made this code more readable, but I spent waaaaay too much time on it ''' first compare datasets for the same year, make, and model if multiple results are found, compare datasets for the same transmission if multiple results are found, compare datasets for the same engine displacement and number of cylinders if multiple reults are found, compare datasets for the same fuel type ''' year = df1.year make = df1.make model = df1.model trany = df1.trany displacement = df1.displacement cylinders = df1.cylinders fuel_type = df1.fuel_type matches = df2[(df2['year'] == year) & (df2['make'] == make) & (df2['model'] == model)] if len(matches) == 1: category = df1.category config = df1.config msrp_low = df1.msrp_low msrp_high = df1.msrp_high msrp_average = df1.msrp_average highway_mpg = matches.highway08U.values[0] highwayCD = matches.highwayCD.values[0] highwayE = matches.highwayE.values[0] city_mpg = matches.city08U.values[0] cityCD = matches.cityCD.values[0] cityE = matches.cityE.values[0] comb_mpg = matches.comb08U.values[0] combCD = matches.combinedCD.values[0] combE = matches.combE.values[0] atv_type = matches.atvType.values[0] fuel_type1 = matches.fuelType1.values[0] fuel_type2 = matches.fuelType2.values[0] rangeA = matches.rangeA.values[0] return category, year, make, model, config, msrp_low, msrp_high, msrp_average, highway_mpg, highwayCD, \ highwayE, city_mpg, cityCD, cityE, comb_mpg, combCD, combE, \ trany, displacement, cylinders, atv_type, fuel_type1, fuel_type2, rangeA elif len(matches) > 1: matches2 = matches[matches['trany'] == trany] if len(matches2) == 1: category = df1.category config = df1.config msrp_low = df1.msrp_low msrp_high = df1.msrp_high msrp_average = df1.msrp_average highway_mpg = matches2.highway08U.values[0] highwayCD = matches2.highwayCD.values[0] highwayE = matches2.highwayE.values[0] city_mpg = matches2.city08U.values[0] cityCD = matches2.cityCD.values[0] cityE = matches2.cityE.values[0] comb_mpg = matches2.comb08U.values[0] combCD = matches2.combinedCD.values[0] combE = matches2.combE.values[0] atv_type = matches2.atvType.values[0] fuel_type1 = matches2.fuelType1.values[0] fuel_type2 = matches2.fuelType2.values[0] rangeA = matches2.rangeA.values[0] return category, year, make, model, config, msrp_low, msrp_high, msrp_average, highway_mpg, highwayCD, \ highwayE, city_mpg, cityCD, cityE, comb_mpg, combCD, combE, \ trany, displacement, cylinders, atv_type, fuel_type1, fuel_type2, rangeA elif len(matches2) > 1: matches3 = matches2[(matches2['displ'] == displacement) & (matches2['cylinders'] == cylinders)] if len(matches3) == 1: category = df1.category config = df1.config msrp_low = df1.msrp_low msrp_high = df1.msrp_high msrp_average = df1.msrp_average highway_mpg = matches3.highway08U.values[0] highwayCD = matches3.highwayCD.values[0] highwayE = matches3.highwayE.values[0] city_mpg = matches3.city08U.values[0] cityCD = matches3.cityCD.values[0] cityE = matches3.cityE.values[0] comb_mpg = matches3.comb08U.values[0] combCD = matches3.combinedCD.values[0] combE = matches3.combE.values[0] atv_type = matches3.atvType.values[0] fuel_type1 = matches3.fuelType1.values[0] fuel_type2 = matches3.fuelType2.values[0] rangeA = matches3.rangeA.values[0] return category, year, make, model, config, msrp_low, msrp_high, msrp_average, highway_mpg, highwayCD, \ highwayE, city_mpg, cityCD, cityE, comb_mpg, combCD, combE, \ trany, displacement, cylinders, atv_type, fuel_type1, fuel_type2, rangeA elif len(matches3) > 1: matches4 = matches3[matches3['fuelType1'] == fuel_type] if len(matches4) == 1: category = df1.category config = df1.config msrp_low = df1.msrp_low msrp_high = df1.msrp_high msrp_average = df1.msrp_average highway_mpg = matches4.highway08U.values[0] highwayCD = matches4.highwayCD.values[0] highwayE = matches4.highwayE.values[0] city_mpg = matches4.city08U.values[0] cityCD = matches4.cityCD.values[0] cityE = matches4.cityE.values[0] comb_mpg = matches4.comb08U.values[0] combCD = matches4.combinedCD.values[0] combE = matches4.combE.values[0] atv_type = matches4.atvType.values[0] fuel_type1 = matches4.fuelType1.values[0] fuel_type2 = matches4.fuelType2.values[0] rangeA = matches4.rangeA.values[0] return category, year, make, model, config, msrp_low, msrp_high, msrp_average, highway_mpg, highwayCD, \ highwayE, city_mpg, cityCD, cityE, comb_mpg, combCD, combE, \ trany, displacement, cylinders, atv_type, fuel_type1, fuel_type2, rangeA elif len(matches4) > 1: with open('data/car-data/msc/multiple-matches.csv', 'a') as f1: f1writer = csv.writer(f1) f1writer.writerow([df1.name, df1.category, year, make, model]) elif len(matches4) == 0: with open('data/car-data/msc/could-not-match.csv', 'a') as f3: f3writer = csv.writer(f3) f3writer.writerow([df1.name, df1.category, year, make, model]) elif len(matches3) == 0: with open('data/car-data/msc/could-not-match.csv', 'a') as f3: f3writer = csv.writer(f3) f3writer.writerow([df1.name, df1.category, year, make, model]) elif len(matches2) == 0: with open('data/car-data/msc/could-not-match.csv', 'a') as f3: f3writer = csv.writer(f3) f3writer.writerow([df1.name, df1.category, year, make, model]) elif len(matches) == 0: with open('data/car-data/msc/no-matches.csv', 'a') as f2: f2writer = csv.writer(f2) f2writer.writerow([df1.name, df1.category, year, make, model]) ``` ### Running the matching algorithm and saving the vehicles that were not able to be matched ```python path1 = 'data/car-data/msc/multiple-matches.csv' if os.path.isfile(path1): os.remove(path1) path2 = 'data/car-data/msc/no-matches.csv' if os.path.isfile(path2): os.remove(path2) with open('data/car-data/msc/multiple-matches.csv', 'w') as f1: f1writer = csv.writer(f1) f1writer.writerow(['index_in_msepdf', 'category', 'year', 'make', 'model']) with open('data/car-data/msc/no-matches.csv', 'w') as f2: f2writer = csv.writer(f2) f2writer.writerow(['index_in_msepdf', 'category', 'year', 'make', 'model']) combdf = pd.DataFrame() combdf = msrpdf.apply(compare_datasets, axis=1, args=(mpgdf,), result_type='expand') combdf.columns = ['category', 'year', 'make', 'model', 'config', 'msrp_low', 'msrp_high', 'msrp_average', 'highway_mpg', 'highwayCD', 'highwayE', 'city_mpg', 'cityCD', 'cityE', 'comb_mpg', 'combCD', 'combE', 'trany', 'displacement', 'cylinders', 'atv_type', 'fuel_type1', 'fuel_type2', 'rangeA'] combdf.dropna(how='all', inplace=True) combdf = combdf.reset_index(drop=True) combdf['atv_type'] = combdf.atv_type.fillna('Gas') ``` ### Saving the final dataset ```python combdf.to_csv('data/car-data/combined-mpg-msrp.csv') ```

## 4. Getting the current gas prices The [AAA website](https://gasprices.aaa.com/) has gas prices for each state and every fuel grade. The prices are updated daily as well.

**Click to view python code**

```python from selenium import webdriver from selenium.webdriver.common.by import By from datetime import date import pandas as pd ``` ```python driver = webdriver.Firefox() driver.implicitly_wait(10) state_list = [] regular_current = [] midgrade_current = [] premium_current = [] diesel_current = [] states_dict = { 'AK': 'Alaska', 'AL': 'Alabama', 'AR': 'Arkansas', 'AZ': 'Arizona', 'CA': 'California', 'CO': 'Colorado', 'CT': 'Connecticut', 'DC': 'District of Columbia', 'DE': 'Delaware', 'FL': 'Florida', 'GA': 'Georgia', 'HI': 'Hawaii', 'IA': 'Iowa', 'ID': 'Idaho', 'IL': 'Illinois', 'IN': 'Indiana', 'KS': 'Kansas', 'KY': 'Kentucky', 'LA': 'Louisiana', 'MA': 'Massachusetts', 'MD': 'Maryland', 'ME': 'Maine', 'MI': 'Michigan', 'MN': 'Minnesota', 'MO': 'Missouri', 'MS': 'Mississippi', 'MT': 'Montana', 'NC': 'North Carolina', 'ND': 'North Dakota', 'NE': 'Nebraska', 'NH': 'New Hampshire', 'NJ': 'New Jersey', 'NM': 'New Mexico', 'NV': 'Nevada', 'NY': 'New York', 'OH': 'Ohio', 'OK': 'Oklahoma', 'OR': 'Oregon', 'PA': 'Pennsylvania', 'PR': 'Puerto Rico', 'RI': 'Rhode Island', 'SC': 'South Carolina', 'SD': 'South Dakota', 'TN': 'Tennessee', 'TX': 'Texas', 'UT': 'Utah', 'VA': 'Virginia', 'VT': 'Vermont', 'WA': 'Washington', 'WI': 'Wisconsin', 'WV': 'West Virginia', 'WY': 'Wyoming' } ``` ### Scraping the AAA website ```python link = 'https://gasprices.aaa.com/' driver.get(link) table = driver.find_element(By.CSS_SELECTOR, 'table.table-mob') state_list.append('National') prices = table.find_element(By.CSS_SELECTOR, 'tbody').find_elements(By.CSS_SELECTOR, 'td') regular_current.append(prices[1].text.replace('$', '')) midgrade_current.append(prices[2].text.replace('$', '')) premium_current.append(prices[3].text.replace('$', '')) diesel_current.append(prices[4].text.replace('$', '')) for abr, state in states_dict.items(): site = link + f'?state={abr}' driver.get(site) state_list.append(state) get_table = driver.find_element(By.CSS_SELECTOR, 'table.table-mob') tbtable = get_table.find_element(By.CSS_SELECTOR, 'tbody') tables = tbtable.find_elements(By.CSS_SELECTOR, 'tr') prices = tables[0].find_elements(By.CSS_SELECTOR, 'td') regular_current.append(prices[1].text.replace('$', '')) midgrade_current.append(prices[2].text.replace('$', '')) premium_current.append(prices[3].text.replace('$', '')) diesel_current.append(prices[4].text.replace('$', '')) ``` ### Create a dataframe and save csv ```python df = pd.DataFrame({ 'state': state_list, 'regular': regular_current, 'midgrade': midgrade_current, 'premium': premium_current, 'diesel': diesel_current, }) df.to_csv(f'data/car-data/gas-prices/gas-prices-{date.today()}.csv', index=False) driver.close() ```

## 5. Getting the current electricity rates [This website](https://www.chooseenergy.com/electricity-rates-by-state/) has electricity prices for every state, so it shall be scraped!

**Click to view python code**

```python from selenium import webdriver from selenium.webdriver.common.by import By from datetime import date import pandas as pd ``` ```python driver = webdriver.Firefox() driver.implicitly_wait(10) state = [] current_rate = [] last_year_rate = [] ``` ### Scraping the website ```python link = 'https://www.chooseenergy.com/electricity-rates-by-state/' driver.get(link) table = driver.find_element(By.CSS_SELECTOR, 'table') # gets the first table, the one we need rows = table.find_elements(By.CSS_SELECTOR, 'tr') rows = rows[1:] for row in rows: column = row.find_elements(By.CSS_SELECTOR, 'td') state.append(column[0].text.replace(' ¢/kWh', '').replace('¢/kWh', '')) current_rate.append(column[1].text.replace(' ¢/kWh', '').replace('¢/kWh', '')) last_year_rate.append(column[2].text.replace(' ¢/kWh', '').replace('¢/kWh', '')) for i, item in enumerate(state): if item == 'U.S.': state[i] = 'National' ``` ### Creating a dataframe and saving it to a csv file ```python df = pd.DataFrame({ 'state': state, 'current_rate': current_rate, 'last_year_rate': last_year_rate }) df.to_csv(f'data/car-data/electricity-prices/ele-prices-{date.today()}.csv', index=False) driver.close() ```

## 6. Process all the data collected Now that we have all the data we need for our analysis, we can process it and combine everything into one dataset. The price per mile for each vehicle in the dataset is calculated from the prices of the fuel source in each state. For calculating the annual fuel costs, the following calculations were used: ### For Gas and Diesel Vehicles: From the gas prices dataset, we have the price of the grade of gasoline/diesel (regular, mid-grade, or premium) in dollars per gallon. From the combined vehicle dataset from [step 3](#step-3), we have the MPG of the vehicle and the grade of gasoline/diesel it uses. We can calculate the price per mile for each car in each state in the US using: ![](/images/gas-d7dc8497.png) We can then multiply this by the number of miles driven in a year to get the annual fuel cost for gas and diesel vehicles. ### For Electric Vehicles: We have the price of electricity in each state in cents per kWh(s), and from the combined vehicle dataset, we know the energy used (kWh) for electric cars per 100 miles (160.93 km). We can then use this to calculate the price per mile for EVs in each state: ![](/images/electic.png) We can then multiply the price per mile by miles driven per year to get the annual fuel cost for the vehicle. ### For Hybrid vehicles: A hybrid vehicle can be charged by electricity (plug-in Hybrid) or be charged by the ICE engine (called Hybrid in this project). The calculations change depending on whether the vehicle can be charged by the grid or not: #### Plug-in Hybrids: The first step is to divide the number of miles driven in a year by the number of days in a year (365). This gives the number of miles driven per day. The dataset contains a range column, which gives us the number of miles a plug-in hybrid vehicle can be driven off electricity alone. If the range available is less than the number of miles driven per day, the remaining miles use gasoline. To calculate the annual fuel costs: *Electricity costs:* ![](/images/hbd-1.png) *Gas costs:* ![](/images/hbd-2.png) #### For Hybrids: Hybrid vehicle annual fuel costs are calculated like a gas/diesel vehicle ![](/images/hbd-3.png) The price per mile is then multiplied by the number of miles driven in a year to get the annual fuel cost. The final saved dataset is created in a form suitable for analysis in tableau.

**Click to view python code**

```python import os import csv import pandas as pd import matplotlib.pyplot as plt import plotly.express as px from datetime import datetime, date pd.set_option('display.max_columns', 100) ``` ### Get the latest gas prices from datasets ```python df = pd.read_csv('data/car-data/combined-mpg-msrp.csv', index_col=0) gas_dir, _, gas_price_list = next(os.walk('data/car-data/gas-prices/')) gas_dates = [] for file in gas_price_list: gas_dates.append(file.strip('gas-prices-').strip('.csv')) gas_date_latest = sorted(gas_dates, key = lambda d: datetime.strptime(d, '%Y-%m-%d'), reverse=True)[0] ele_dir, _, ele_price_list = next(os.walk('data/car-data/electricity-prices/')) ele_dates = [] for file in ele_price_list: ele_dates.append(file.strip('ele-prices-').strip('.csv')) ele_date_latest = sorted(ele_dates, key = lambda d: datetime.strptime(d, '%Y-%m-%d'), reverse=True)[0] ``` ### Set the number of miles driven each year the EPA estimates that the average miles driven per person per year are 15000 ```python miles_per_year = 15000 # EPA estimate years_of_ownership = 11 # Number or years of ownership we want to calculate for ``` ### The electricity prices dataset has fewer states than the gas prices dataset, we need to find those extra states and remove them ```python gp = pd.read_csv(gas_dir+'gas-prices-'+gas_date_latest+'.csv', index_col='state') ep = pd.read_csv(ele_dir+'ele-prices-'+ele_date_latest+'.csv', index_col='state') b = list(gp.index) a = list(ep.index) for item in b: if item not in a: print(item) ``` ```python District of Columbia Puerto Rico ``` ### District of Columbia and Puerto Rico are not in the electricity dataset so removing from the gas_prices dataset ```python gp.drop(index=['District of Columbia', 'Puerto Rico'], inplace=True) states = list(gp.index) ``` ### **Looking for outliers** ```python fig = px.scatter(df, x='make', y='msrp_average') fig.update_layout(title_text='All cars Make vs MSRP', title_x=0.5) fig.show() ``` ![](/images/output\_12\_1-47f01fa8.png) ```python gas_df = df[(df['atv_type'] =='Gas') | (df['atv_type'] =='FFV')].copy() fig = px.scatter(gas_df, x='make', y='msrp_average') fig.update_layout(title_text='ICE Cars Make vs MSRP', title_x=0.5) fig.show() ``` ![](/images/output\_13\_0-8409a4fc.png) ```python ele_df = df[(df['atv_type'] =='EV')].copy() fig = px.scatter(ele_df, x='make', y='msrp_average') fig.update_layout(title_text='Electric Cars Make vs MSRP', title_x=0.5) fig.show() ``` ![](/images/output\_14\_0-5d153609.png) ```python hyb_df = df[(df['atv_type'] =='Hybrid') | (df['atv_type'] =='Plug-in Hybrid')].copy() fig = px.scatter(hyb_df, x='make', y='msrp_average') fig.update_layout(title_text='Hybrid Cars Make vs MSRP', title_x=0.5) fig.show() ``` ![](/images/output\_15\_0-c3a214b2.png) Looking at the scatter plots of gas, electric, and hybrid vehicles, we can see that most vehicles are under $200K with some outliers above that. Additionally, a lot of the high-end automotive manufacturers only produce gas vehicles which can skew the results. So let's remove very high-end manufacturers like Ferrari, Lamborghini, Bentley, Rolls-Royce, Maybach, Aston Martin, and McLaren. *Additionally, I would think that if somebody is about to buy a Ferrari, he is not gonna ask what mileage it gets at the dealership* ```python clean_df = df[(df['make'] != 'Ferrari') & (df['make'] != 'Bentley') & (df['make'] != 'Lamborghini') & (df['make'] != 'Rolls-Royce') & (df['make'] != 'Maybach') & (df['make'] != 'Aston Martin') & (df['make'] != 'McLaren Automotive') & (df['make'] != 'Maserati') & (df['msrp_average'] < 200000)] ``` ### Final scatterplot for all vehicle types ```python fig = px.scatter(clean_df, x='make', y='msrp_average') fig.update_layout(title_text='All cars Make vs MSRP', title_x=0.5) fig.show() ``` ![](/images/output\_19\_0-49558d25.png) ### Creating a new csv file ```python path1 = f'data/car-data/tableau/cars-{date.today()}.csv' if os.path.isfile(path1): os.remove(path1) with open(f'data/car-data/tableau/cars-{date.today()}.csv', 'w') as f1: f1writer = csv.writer(f1) f1writer.writerow(['year_ownership', 'year', 'make', 'model', 'config', 'atv_type', 'msrp', 'state', 'mpg/e', 'annual_fuel_cost', 'cumm_cost']) ``` ## Gas vehicle calculations Assume we only buy the car on December 31st of the current year and don't spend anything on fuel the first year ```python def gas_diesel(df, gp, state, miles_per_year, years_of_ownership, f1writer): grade = df.fuel_type1.split()[0].lower() price = gp[grade][state] mpg = df.comb_mpg cost = df.msrp_average year = int(df.year) make = df.make model = df.model config = df.config if df.atv_type == 'FFV': # Considering FFV vehicles as gas vehicles since we are ignoring the alternative fuel atv_type = 'Gas' else: atv_type = df.atv_type msrp = df.msrp_average annual_fuel_costs = price/mpg*miles_per_year mpg_ret = str(mpg)+ ' mpg' for i in range(years_of_ownership): f1writer.writerow([i, year, make, model, config, atv_type, msrp, state, mpg_ret, annual_fuel_costs, msrp+annual_fuel_costs*i]) ``` ### Adding the ICE Vehicle calculations to the csv file ```python gas_diesel_df = df[(df['atv_type'] == 'Gas') | (df['atv_type'] == 'Diesel') | (df['atv_type'] == 'FFV')] with open(f'data/car-data/tableau/cars-{date.today()}.csv', 'a') as f1: f1writer = csv.writer(f1) for state in states: gas_diesel_df.apply(gas_diesel, axis=1, args=(gp, state, miles_per_year, years_of_ownership, f1writer)) ``` ## Electric vehicle calculations ```python def electric(df, ep, req_rate, state, miles_per_year, years_of_ownership, f1writer): price = ep[req_rate][state] # cents per KWh price = price/100 # dollars per KWh mpe = df.combE # combined electricity consumption in KWh per 100 miles mpe = mpe/100 # KWh per mile cost = df.msrp_average year = int(df.year) make = df.make model = df.model config = df.config atv_type = df.atv_type msrp = df.msrp_average annual_fuel_costs = price*mpe*miles_per_year mpg_ret = str(mpe*100) + ' KWh/100miles' for i in range(years_of_ownership): f1writer.writerow([i, year, make, model, config, atv_type, msrp, state, mpg_ret, annual_fuel_costs, msrp+annual_fuel_costs*i]) ``` ### Adding the Electric Vehicle calculations to the csv file ```python req_rate = 'current_rate' # Take the current rate ele_df = df[(df['atv_type'] == 'EV')] with open(f'data/car-data/tableau/cars-{date.today()}.csv', 'a') as f1: f1writer = csv.writer(f1) for state in states: ele_df.apply(electric, axis=1, args=(ep, req_rate, state, miles_per_year, years_of_ownership, f1writer)) ``` ## Hybrid vehicle calculations For plug-in hybrids, we can use the following methodology: 1. Using the range of the vehicle, and the kWh per mile, we calculate how much it costs to fully charge the vehicle with electricity through the grid 2. Next, we know how many miles are traveled per year (we have taken it to be 15000 as specified by the EPA), so we can calculate the number of miles driven per day by dividing by 365 (eg. 15000/365=41.096) 3. We can subtract the hybrid vehicle range from this number, the balance will be the amount driven by the gasoline engine. The fuel costs for this can then be calculated. ```python def hybrid(df, gp, ep, req_rate, state, miles_per_year, years_of_ownership, f1writer): price_ele = ep[req_rate][state] # cents per KWh price_ele = price_ele/100 # dollars per KWh grade = df.fuel_type1.split()[0].lower() price_gas = gp[grade][state] cost = df.msrp_average year = int(df.year) make = df.make model = df.model config = df.config atv_type = df.atv_type msrp = df.msrp_average if df.atv_type == 'Plug-in Hybrid': miles_per_day = miles_per_year/365 rangeA = float(df.rangeA) gas_engine_miles = miles_per_day - rangeA if gas_engine_miles < 0: gas_engine_miles = 0 rangeA = miles_per_day # take miles traveled in a day instead of car range when calculating daily costs # cost to charge = range * combined electricity consumption / 100 = KWh for full charge mpe = df.combE # combined electricity consumption in KWh per 100 miles mpe = mpe/100 # KWh per mile full_charge_cost = rangeA*mpe*price_ele # cost of running the rest using gas mpg = df.comb_mpg gpm = 1/mpg gas_costs = gpm*gas_engine_miles*price_gas annual_fuel_costs = (full_charge_cost + gas_costs)*365 # price per day * 365 days in a year mpg_return = str(mpe*100) + ' KWh/100 miles + ' + str(mpg) + ' mpg' elif df.atv_type == 'Hybrid': mpg = df.comb_mpg annual_fuel_costs = price_gas/mpg*miles_per_year mpg_return = mpg else: print('error') for i in range(years_of_ownership): f1writer.writerow([i, year, make, model, config, atv_type, msrp, state, mpg_return, annual_fuel_costs, msrp+annual_fuel_costs*i]) ``` ### Adding the Hybrid Vehicle calculations to the csv file ```python hbd_df = df[(df['atv_type'] == 'Plug-in Hybrid') | (df['atv_type'] == 'Hybrid')] with open(f'data/car-data/tableau/cars-{date.today()}.csv', 'a') as f1: f1writer = csv.writer(f1) for state in states: hbd_df.apply(hybrid, axis=1, args=(gp, ep, req_rate, state, miles_per_year, years_of_ownership, f1writer)) ``` ### Some interesting information: ```python df11 = pd.read_csv(f'data/car-data/tableau/cars-{date.today()}.csv') df11.groupby('atv_type').median() ```

atv_type	year_ownership	year	msrp	annual_fuel_cost	cumm_cost
Diesel	5.0	2015.0	40806.25	2353.593750	49292.317335
EV	5.0	2019.0	44450.00	726.015000	48278.370223
Gas	5.0	2016.0	40000.00	2403.246772	52668.665374
Hybrid	5.0	2018.0	41820.00	1662.857581	49046.851910
Plug-in Hybrid	5.0	2019.0	51500.00	1493.719735	57608.160000

# Results *Visualizations are done using tableau* ## 1. Years of Ownership vs Fuel type Plug-in Hybrids have the highest median MSRP followed by EVs, Hybrids, Diesel, and Gas vehicles. After 10 years of ownership, plug-in Hybrids still have the highest cumulative ownership costs followed very closely by Gas, then Diesel, Hybrids, and finally EVs. These results mainly stay the same for each state but you can use the drop-down filter on the bottom left of the visualization to check for different states. ## 2. Annual Fuel Costs by State Use the filter on the bottom right to select the fuel type and view the median annual fuel costs for the fuel type by state. The states with the 5 highest and 5 lowest annual fuel costs are shown at the bottom. ## 3. The Difference in Annual Fuel Costs (Gas - Electricity) This graph shows the difference between the median annual fuel costs for gas-powered vehicles (considering the fuel grade the vehicle uses), and the median annual fuel costs for electric vehicles. The higher the difference, the better the state to own the electric vehicle. A higher difference indicates that there is a higher cost associated with filling gas at the pump compared to charging at home. The current best state to own an electric car is Washington, due to the high gas prices and very low electricity rates. The current worst state to own an electric car is New Hampshire because of low gas prices and extremely high electricity rates. # Conclusion This project shows the advantages of owning an electric car or other alternative power trains over the long run. While buying a new electric vehicle is generally more expensive, over the long run the amount we would save on fuel costs would eventually make it cheaper to own. With the current electricity rates and gas prices, electric cars would be cheaper to own in less than 3 years [(see graph 1)](#results-1). These figures depend on the state in which you would operate the vehicle. [Graph 3](#results-3) shows that the best state to own an electric vehicle is Washington, where it would be cheaper compared to gas-powered options in under 2 years (with current rates), while it would take about 4 years in New Hampshire. [Graph 1](#results-1) also shows that there isn't much benefit to owning a plug-in hybrid vehicle, and a normal Hybrid which uses a gas engine to charge the battery might be more beneficial over the long run. [Graph 2](#results-2) is helpful as it considers the efficiency of an electric vehicle, and uses MPG and Fuel grade for gasoline vehicles to determine the annual fuel cost. This makes it more representative of the fuel costs you would be expected to pay every year rather than just having a map of fuel prices and electricity prices in each state. ### Limitations 1. Does not consider maintenance costs – Electric cars are cheaper to maintain as they have fewer moving parts, but they would require a battery replacement at around 70,000–150,000 miles at an average price of $5000 [(source)](https://carsofelectric.com/how-long-does-an-electric-car-battery-last-before-replacement/). 2. For plug-in hybrids, this project assumes that the car runs completely on electricity and later on gas when the battery is drained. In actuality, most plug-in hybrids run on 'charge depletion mode' where the battery and engine work in tandem to provide power.

Home

WHAT THE CRUNCH

Featured Projects

To EV or Not to EV

"The two most important days in your life are the day you are born and the day you find out why."

— Mark Twain

Follow me on LinkedIn