Attribute | Description |
---|---|
Model | Vehicle make and model |
Displ | Engine displacement - the size of an engine in liters |
Cyl | The number of cylinders in a particular engine |
Trans | Transmission Type and Number of Gears |
Drive | Drive axle type (2WD = 2-wheel drive, 4WD = 4-wheel/all-wheel drive) |
Fuel | Fuel Type |
Cert Region* | Certification Region Code |
Sales Area** | Certification Region Code |
Stnd | Vehicle emissions standard code (View Vehicle Emissions Standards here) |
Stnd Description* | Vehicle emissions standard description |
Underhood ID | This is a 12-digit ID number that can be found on the underhood emission label of every vehicle. It's required by the EPA to designate its "test group" or "engine family." This is explained more here |
Veh Class | EPA Vehicle Class |
Air Pollution Score | Air pollution score (smog rating) |
City MPG | Estimated city mpg (miles/gallon) |
Hwy MPG | Estimated highway mpg (miles/gallon) |
Cmb MPG | Estimated combined mpg (miles/gallon) |
Greenhouse Gas Score | Greenhouse gas rating |
SmartWay | Yes, No, or Elite |
Comb CO2* | Combined city/highway CO2 tailpipe emissions in grams per mile |
import pandas as pd
df_08 = pd.read_csv('all_alpha_08.csv')
df_18 = pd.read_csv('all_alpha_18.csv')
df_08.rename(columns={'Sales Area':'Cert Region'}, inplace=True)
df_08.shape
sum(df_08.duplicated())
df_08.drop(['Stnd', 'Underhood ID', 'FE Calc Appr', 'Unadj Cmb MPG'], axis=1, inplace=True)
df_08.head(1)
# replace spaces with underscores and lowercase labels for 2008 dataset
df_08.rename(columns=lambda x: x.strip().lower().replace(" ", "_"), inplace=True)
# confirm changes
df_08.head(1)
df_08.describe()
type(df_08['City MPG'][0])
len(df_08[df_08['Fuel'] == 'Electricity'])
df_18.shape
sum(df_18.duplicated())
df_18.drop(['Stnd', 'Stnd Description', 'Underhood ID', 'Comb CO2'], axis=1, inplace=True)
df_18.head(1)
# replace spaces with underscores and lowercase labels for 2018 dataset
df_18.rename(columns=lambda x: x.strip().lower().replace(" ", "_"), inplace=True)
# confirm changes
df_18.head(1)
df_18.describe()
len(df_18[df_18['Fuel'] == 'CNG'])
# confirm column labels for 2008 and 2018 datasets are identical
df_08.columns == df_18.columns
# make sure they're all identical like this
(df_08.columns == df_18.columns).all()
# save new datasets for next section
df_08.to_csv('data_08_v1.csv', index=False)
df_18.to_csv('data_18_v1.csv', index=False)
# load datasets
df_08 = pd.read_csv('data_08_v1.csv')
df_18 = pd.read_csv('data_18_v1.csv')
# filter datasets for rows following California standards
df_08 = df_08.query('cert_region == "CA"')
df_18 = df_18.query('cert_region == "CA"')
# confirm only certification region is California
df_08['cert_region'].unique()
# confirm only certification region is California
df_18['cert_region'].unique()
# drop certification region columns form both datasets
df_08.drop('cert_region', inplace = True, axis=1)
df_18.drop('cert_region', inplace = True, axis=1)
df_08.shape
df_18.shape
# view missing value count for each feature in 2008
df_08.isnull().sum()
# view missing value count for each feature in 2018
df_18.isnull().sum()
# drop rows with any null values in both datasets
df_08.dropna(axis = 0, inplace=True)
df_18.dropna(axis = 0, inplace=True)
# checks if any of columns in 2008 have null values - should print False
df_08.isnull().sum().any()
# checks if any of columns in 2018 have null values - should print False
df_18.isnull().sum().any()
# print number of duplicates in 2008 and 2018 datasets
sum(df_08.duplicated())
# drop duplicates in both datasets
df_08.drop_duplicates(inplace=True)
df_18.drop_duplicates(inplace=True)
# print number of duplicates again to confirm dedupe - should both be 0
print(df_08.duplicated().sum())
print(df_18.duplicated().sum())
# save progress for the next section
df_08.to_csv('data_08_v2.csv', index=False)
df_18.to_csv('data_18_v2.csv', index=False)
# load datasets
df_08 = pd.read_csv('data_08_v2.csv')
df_18 = pd.read_csv('data_18_v2.csv')
df_08.info()
df_18.info()
type(df_08['cyl'][0])
type(df_18['cyl'][0])
type(df_08['air_pollution_score'][0])
type(df_18['air_pollution_score'][0])
type(df_08['greenhouse_gas_score'][0])
type(df_18['greenhouse_gas_score'][0])
# check value counts for the 2008 cyl column
df_08['cyl'].value_counts()
# Extract int from strings in the 2008 cyl column
df_08['cyl'] = df_08['cyl'].str.extract('(\d+)').astype(int)
# Check value counts for 2008 cyl column again to confirm the change
df_08['cyl'].value_counts()
# convert 2018 cyl column to int
df_18['cyl'] = df_18['cyl'].astype(int)
# check value counts for the 2018 cyl column
df_18['cyl'].value_counts()
df_08.to_csv('data_08_v3.csv', index=False)
df_18.to_csv('data_18_v3.csv', index=False)
air_pollution_score
Data Type¶# load datasets
df_08 = pd.read_csv('data_08_v3.csv')
df_18 = pd.read_csv('data_18_v3.csv')
df_08['air_pollution_score'].value_counts()
The mpg columns and greenhouse gas scores also seem to have the same problem - maybe that's why these were all saved as strings! According to this link, which I found from the PDF documentation:
"If a vehicle can operate on more than one type of fuel, an estimate is provided for each fuel type."
Ohh... so all vehicles with more than one fuel type, or hybrids, like the one above (it uses ethanol AND gas) will have a string that holds two values - one for each. This is a little tricky, so I'm going to show you how to do it with the 2008 dataset, and then you'll try it with the 2018 dataset.
# First, let's get all the hybrids in 2008
hb_08 = df_08[df_08['fuel'].str.contains('/')]
hb_08
# hybrids in 2018
hb_18 = df_18[df_18['fuel'].str.contains('/')]
hb_18.head()
We're going to take each hybrid row and split them into two new rows - one with values for the first fuel type (values before the "/"), and the other with values for the second fuel type (values after the "/"). Let's separate them with two dataframes!
# create two copies of the 2008 hybrids dataframe
df1 = hb_08.copy() # data on first fuel type of each hybrid vehicle
df2 = hb_08.copy() # data on second fuel type of each hybrid vehicle
# Each one should look like this
df1
For this next part, we're going use pandas' apply function. See the docs here.
# columns to split by "/"
split_columns = ['fuel', 'air_pollution_score', 'city_mpg', 'hwy_mpg', 'cmb_mpg', 'greenhouse_gas_score']
# apply split function to each column of each dataframe copy
for c in split_columns:
df1[c] = df1[c].apply(lambda x: x.split("/")[0])
df2[c] = df2[c].apply(lambda x: x.split("/")[1])
# this dataframe holds info for the FIRST fuel type of the hybrid
# aka the values before the "/"s
df1
# this dataframe holds info for the SECOND fuel type of the hybrid
# aka the values before the "/"s
df2
# combine dataframes to add to the original dataframe
new_rows = df1.append(df2)
# now we have separate rows for each fuel type of each vehicle!
new_rows
hb_08.index
# drop the original hybrid rows
df_08.drop(hb_08.index, inplace=True)
# add in our newly separated rows
df_08 = df_08.append(new_rows, ignore_index=True)
# check that all the original hybrid rows with "/"s are gone
df_08[df_08['fuel'].str.contains('/')]
df_08.shape
# create two copies of the 2018 hybrids dataframe, hb_18
df1 = hb_18.copy()
df2 = hb_18.copy()
fuel
, city_mpg
, hwy_mpg
, cmb_mpg
¶You don't need to split for air_pollution_score
or greenhouse_gas_score
here because these columns are already ints in the 2018 dataset.
# list of columns to split
split_columns = ['fuel', 'city_mpg', 'hwy_mpg', 'cmb_mpg']
# apply split function to each column of each dataframe copy
for c in split_columns:
df1[c] = df1[c].apply(lambda x: x.split("/")[0])
df2[c] = df2[c].apply(lambda x: x.split("/")[1])
# append the two dataframes
new_rows = df1.append(df2)
# drop each hybrid row from the original 2018 dataframe
# do this by using pandas' drop function with hb_18's index
df_18.drop(hb_18.index, inplace=True)
# append new_rows to df_18
df_18 = df_18.append(new_rows, ignore_index=True)
# check that they're gone
df_18[df_18['fuel'].str.contains('/')]
df_18.shape
air_pollution_score
! Here they are again:¶# convert string to float for 2008 air pollution column
df_08['air_pollution_score'] = df_08['air_pollution_score'].str.extract('(\d+)').astype(float)
df_08['air_pollution_score'].value_counts()
# convert int to float for 2018 air pollution column
df_18['air_pollution_score'] = df_18['air_pollution_score'].astype(float)
df_18['air_pollution_score'].value_counts()
df_08.to_csv('data_08_v4.csv', index=False)
df_18.to_csv('data_18_v4.csv', index=False)
# load datasets
import pandas as pd
df_08 = pd.read_csv('data_08_v4.csv')
df_18 = pd.read_csv('data_18_v4.csv')
df_08.head()
df_18.head()
city_mpg
, hwy_mpg
, cmb_mpg
datatypes¶2008 and 2018: convert string to float
# convert mpg columns to floats
mpg_columns = ['city_mpg', 'hwy_mpg', 'cmb_mpg']
for c in mpg_columns:
df_18[c] = df_18[c].astype(float)
df_08[c] = df_08[c].astype(float)
greenhouse_gas_score
datatype¶2008: convert from float to int
# convert from float to int
df_08['greenhouse_gas_score'] = df_08['greenhouse_gas_score'].astype(int)
df_08.dtypes
df_18.dtypes
df_08.dtypes == df_18.dtypes
# Save your final CLEAN datasets as new files!
df_08.to_csv('clean_08.csv', index=False)
df_18.to_csv('clean_18.csv', index=False)