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
In [1]:
import pandas as pd

df_08 = pd.read_csv('all_alpha_08.csv')
df_18 = pd.read_csv('all_alpha_18.csv')
In [82]:
df_08.rename(columns={'Sales Area':'Cert Region'}, inplace=True)
In [14]:
df_08.shape
Out[14]:
(2404, 18)
In [17]:
sum(df_08.duplicated())
Out[17]:
25
In [77]:
df_08.drop(['Stnd', 'Underhood ID', 'FE Calc Appr', 'Unadj Cmb MPG'], axis=1, inplace=True)
In [83]:
df_08.head(1)
Out[83]:
Model Displ Cyl Trans Drive Fuel Cert Region Veh Class Air Pollution Score City MPG Hwy MPG Cmb MPG Greenhouse Gas Score SmartWay
0 ACURA MDX 3.7 (6 cyl) Auto-S5 4WD Gasoline CA SUV 7 15 20 17 4 no
In [84]:
# 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)
Out[84]:
model displ cyl trans drive fuel cert_region veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
0 ACURA MDX 3.7 (6 cyl) Auto-S5 4WD Gasoline CA SUV 7 15 20 17 4 no
In [85]:
df_08.describe()
Out[85]:
displ
count 2404.000000
mean 3.748918
std 1.335785
min 1.300000
25% 2.500000
50% 3.500000
75% 4.800000
max 8.400000
In [27]:
type(df_08['City MPG'][0])
Out[27]:
str
In [59]:
len(df_08[df_08['Fuel'] == 'Electricity'])
Out[59]:
0
In [72]:
df_18.shape
Out[72]:
(1611, 18)
In [73]:
sum(df_18.duplicated())
Out[73]:
0
In [76]:
df_18.drop(['Stnd', 'Stnd Description', 'Underhood ID', 'Comb CO2'], axis=1, inplace=True)
In [80]:
df_18.head(1)
Out[80]:
Model Displ Cyl Trans Drive Fuel Cert Region Veh Class Air Pollution Score City MPG Hwy MPG Cmb MPG Greenhouse Gas Score SmartWay
0 ACURA RDX 3.5 6.0 SemiAuto-6 2WD Gasoline FA small SUV 3 20 28 23 5 No
In [86]:
# 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)
Out[86]:
model displ cyl trans drive fuel cert_region veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
0 ACURA RDX 3.5 6.0 SemiAuto-6 2WD Gasoline FA small SUV 3 20 28 23 5 No
In [5]:
df_18.describe()
Out[5]:
Displ Cyl Air Pollution Score Greenhouse Gas Score
count 1609.000000 1609.000000 1611.000000 1611.000000
mean 3.055687 5.479180 3.958411 4.711359
std 1.344574 1.749121 1.824303 1.657429
min 1.200000 3.000000 1.000000 1.000000
25% 2.000000 4.000000 3.000000 4.000000
50% 3.000000 6.000000 3.000000 5.000000
75% 3.600000 6.000000 5.000000 6.000000
max 8.000000 16.000000 10.000000 10.000000
In [61]:
len(df_18[df_18['Fuel'] == 'CNG'])
Out[61]:
0
In [87]:
# confirm column labels for 2008 and 2018 datasets are identical
df_08.columns == df_18.columns
Out[87]:
array([ True,  True,  True,  True,  True,  True,  True,  True,  True,
        True,  True,  True,  True,  True])
In [88]:
# make sure they're all identical like this
(df_08.columns == df_18.columns).all()
Out[88]:
True
In [89]:
# 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)
In [91]:
# load datasets

df_08 = pd.read_csv('data_08_v1.csv')
df_18 = pd.read_csv('data_18_v1.csv')

Filter by Certification Region

In [104]:
# filter datasets for rows following California standards
df_08 = df_08.query('cert_region == "CA"')
df_18 = df_18.query('cert_region == "CA"')
In [106]:
# confirm only certification region is California
df_08['cert_region'].unique()
Out[106]:
array(['CA'], dtype=object)
In [107]:
# confirm only certification region is California
df_18['cert_region'].unique()
Out[107]:
array(['CA'], dtype=object)
In [ ]:
# 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)
In [111]:
df_08.shape
Out[111]:
(1084, 13)
In [112]:
df_18.shape
Out[112]:
(798, 13)

Drop Rows with Missing Values

In [116]:
# view missing value count for each feature in 2008
df_08.isnull().sum()
Out[116]:
model                    0
displ                    0
cyl                     75
trans                   75
drive                   37
fuel                     0
veh_class                0
air_pollution_score      0
city_mpg                75
hwy_mpg                 75
cmb_mpg                 75
greenhouse_gas_score    75
smartway                 0
dtype: int64
In [117]:
# view missing value count for each feature in 2018
df_18.isnull().sum()
Out[117]:
model                   0
displ                   1
cyl                     1
trans                   0
drive                   0
fuel                    0
veh_class               0
air_pollution_score     0
city_mpg                0
hwy_mpg                 0
cmb_mpg                 0
greenhouse_gas_score    0
smartway                0
dtype: int64
In [119]:
# drop rows with any null values in both datasets
df_08.dropna(axis = 0, inplace=True)
df_18.dropna(axis = 0, inplace=True)
In [120]:
# checks if any of columns in 2008 have null values - should print False
df_08.isnull().sum().any()
Out[120]:
False
In [121]:
# checks if any of columns in 2018 have null values - should print False
df_18.isnull().sum().any()
Out[121]:
False

Dedupe Data

In [123]:
# print number of duplicates in 2008 and 2018 datasets
sum(df_08.duplicated())
Out[123]:
23
In [124]:
# drop duplicates in both datasets
df_08.drop_duplicates(inplace=True)
df_18.drop_duplicates(inplace=True)
In [125]:
# print number of duplicates again to confirm dedupe - should both be 0

print(df_08.duplicated().sum())
print(df_18.duplicated().sum())
0
0
In [126]:
# 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)
In [127]:
# load datasets

df_08 = pd.read_csv('data_08_v2.csv')
df_18 = pd.read_csv('data_18_v2.csv')
In [132]:
df_08.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 986 entries, 0 to 985
Data columns (total 13 columns):
model                   986 non-null object
displ                   986 non-null float64
cyl                     986 non-null object
trans                   986 non-null object
drive                   986 non-null object
fuel                    986 non-null object
veh_class               986 non-null object
air_pollution_score     986 non-null object
city_mpg                986 non-null object
hwy_mpg                 986 non-null object
cmb_mpg                 986 non-null object
greenhouse_gas_score    986 non-null object
smartway                986 non-null object
dtypes: float64(1), object(12)
memory usage: 100.2+ KB
In [133]:
df_18.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 794 entries, 0 to 793
Data columns (total 13 columns):
model                   794 non-null object
displ                   794 non-null float64
cyl                     794 non-null float64
trans                   794 non-null object
drive                   794 non-null object
fuel                    794 non-null object
veh_class               794 non-null object
air_pollution_score     794 non-null int64
city_mpg                794 non-null object
hwy_mpg                 794 non-null object
cmb_mpg                 794 non-null object
greenhouse_gas_score    794 non-null int64
smartway                794 non-null object
dtypes: float64(2), int64(2), object(9)
memory usage: 80.7+ KB
In [128]:
type(df_08['cyl'][0])
Out[128]:
str
In [129]:
type(df_18['cyl'][0])
Out[129]:
numpy.float64
In [130]:
type(df_08['air_pollution_score'][0])
Out[130]:
str
In [131]:
type(df_18['air_pollution_score'][0])
Out[131]:
numpy.int64
In [134]:
type(df_08['greenhouse_gas_score'][0])
Out[134]:
str
In [135]:
type(df_18['greenhouse_gas_score'][0])
Out[135]:
numpy.int64

Fixing cyl Data Type

  • 2008: extract int from string
  • 2018: convert float to int
In [136]:
# check value counts for the 2008 cyl column
df_08['cyl'].value_counts()
Out[136]:
(6 cyl)     409
(4 cyl)     283
(8 cyl)     199
(5 cyl)      48
(12 cyl)     30
(10 cyl)     14
(2 cyl)       2
(16 cyl)      1
Name: cyl, dtype: int64
In [137]:
# Extract int from strings in the 2008 cyl column
df_08['cyl'] = df_08['cyl'].str.extract('(\d+)').astype(int)
In [138]:
# Check value counts for 2008 cyl column again to confirm the change
df_08['cyl'].value_counts()
Out[138]:
6     409
4     283
8     199
5      48
12     30
10     14
2       2
16      1
Name: cyl, dtype: int64
In [139]:
# convert 2018 cyl column to int
df_18['cyl'] = df_18['cyl'].astype(int)
In [141]:
# check value counts for the 2018 cyl column
df_18['cyl'].value_counts()
Out[141]:
4     365
6     246
8     153
3      18
12      9
5       2
16      1
Name: cyl, dtype: int64
In [142]:
df_08.to_csv('data_08_v3.csv', index=False)
df_18.to_csv('data_18_v3.csv', index=False)

Fixing air_pollution_score Data Type

  • 2008: convert string to float
  • 2018: convert int to float
In [175]:
# load datasets

df_08 = pd.read_csv('data_08_v3.csv')
df_18 = pd.read_csv('data_18_v3.csv')
In [176]:
df_08['air_pollution_score'].value_counts()
Out[176]:
6      500
7      398
9.5     80
9        7
6/4      1
Name: air_pollution_score, dtype: int64

It's not just the air pollution score!

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.

In [177]:
# First, let's get all the hybrids in 2008
hb_08 = df_08[df_08['fuel'].str.contains('/')]
hb_08
Out[177]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
582 MERCEDES-BENZ C300 3.0 6 Auto-L7 2WD ethanol/gas small car 6/4 13/18 19/25 15/21 7/6 no
In [178]:
# hybrids in 2018
hb_18 = df_18[df_18['fuel'].str.contains('/')]
hb_18.head()
Out[178]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
52 BMW 330e 2.0 4 SemiAuto-8 2WD Gasoline/Electricity small car 3 28/66 34/78 30/71 10 Yes
78 BMW 530e 2.0 4 SemiAuto-8 2WD Gasoline/Electricity small car 7 27/70 31/75 29/72 10 Elite
79 BMW 530e 2.0 4 SemiAuto-8 4WD Gasoline/Electricity small car 7 27/66 31/68 28/67 10 Elite
92 BMW 740e 2.0 4 SemiAuto-8 4WD Gasoline/Electricity large car 3 25/62 29/68 27/64 9 Yes
189 CHEVROLET Impala 3.6 6 SemiAuto-6 2WD Ethanol/Gas large car 5 14/18 20/28 16/22 4 No

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!

In [179]:
# 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
Out[179]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
582 MERCEDES-BENZ C300 3.0 6 Auto-L7 2WD ethanol/gas small car 6/4 13/18 19/25 15/21 7/6 no

For this next part, we're going use pandas' apply function. See the docs here.

In [180]:
# 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])
In [181]:
# this dataframe holds info for the FIRST fuel type of the hybrid
# aka the values before the "/"s
df1
Out[181]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
582 MERCEDES-BENZ C300 3.0 6 Auto-L7 2WD ethanol small car 6 13 19 15 7 no
In [182]:
# this dataframe holds info for the SECOND fuel type of the hybrid
# aka the values before the "/"s
df2
Out[182]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
582 MERCEDES-BENZ C300 3.0 6 Auto-L7 2WD gas small car 4 18 25 21 6 no
In [183]:
# 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
Out[183]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
582 MERCEDES-BENZ C300 3.0 6 Auto-L7 2WD ethanol small car 6 13 19 15 7 no
582 MERCEDES-BENZ C300 3.0 6 Auto-L7 2WD gas small car 4 18 25 21 6 no
In [184]:
hb_08.index
Out[184]:
Int64Index([582], dtype='int64')
In [185]:
# 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)
In [186]:
# check that all the original hybrid rows with "/"s are gone
df_08[df_08['fuel'].str.contains('/')]
Out[186]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
In [187]:
df_08.shape
Out[187]:
(987, 13)

Repeat this process for the 2018 dataset

In [188]:
# create two copies of the 2018 hybrids dataframe, hb_18
df1 = hb_18.copy()
df2 = hb_18.copy()

Split values for 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.

In [189]:
# 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])
In [190]:
# 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)
In [191]:
# check that they're gone
df_18[df_18['fuel'].str.contains('/')]
Out[191]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
In [192]:
df_18.shape
Out[192]:
(832, 13)

Now we can comfortably continue the changes needed for air_pollution_score! Here they are again:

  • 2008: convert string to float
  • 2018: convert int to float
In [193]:
# convert string to float for 2008 air pollution column
df_08['air_pollution_score'] = df_08['air_pollution_score'].str.extract('(\d+)').astype(float)
In [194]:
df_08['air_pollution_score'].value_counts()
Out[194]:
6.0    501
7.0    398
9.0     87
4.0      1
Name: air_pollution_score, dtype: int64
In [195]:
# convert int to float for 2018 air pollution column
df_18['air_pollution_score'] = df_18['air_pollution_score'].astype(float)
In [196]:
df_18['air_pollution_score'].value_counts()
Out[196]:
3.0    393
5.0    196
7.0    146
1.0     90
6.0      7
Name: air_pollution_score, dtype: int64
In [197]:
df_08.to_csv('data_08_v4.csv', index=False)
df_18.to_csv('data_18_v4.csv', index=False)
In [1]:
# load datasets
import pandas as pd

df_08 = pd.read_csv('data_08_v4.csv')
df_18 = pd.read_csv('data_18_v4.csv')
In [2]:
df_08.head()
Out[2]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
0 ACURA MDX 3.7 6 Auto-S5 4WD Gasoline SUV 7.0 15 20 17 4 no
1 ACURA RDX 2.3 4 Auto-S5 4WD Gasoline SUV 7.0 17 22 19 5 no
2 ACURA RL 3.5 6 Auto-S5 4WD Gasoline midsize car 7.0 16 24 19 5 no
3 ACURA TL 3.2 6 Auto-S5 2WD Gasoline midsize car 7.0 18 26 21 6 yes
4 ACURA TL 3.5 6 Auto-S5 2WD Gasoline midsize car 7.0 17 26 20 6 yes
In [3]:
df_18.head()
Out[3]:
model displ cyl trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
0 ACURA RDX 3.5 6 SemiAuto-6 2WD Gasoline small SUV 3.0 20 28 23 5 No
1 ACURA RDX 3.5 6 SemiAuto-6 4WD Gasoline small SUV 3.0 19 27 22 4 No
2 ACURA TLX 2.4 4 AMS-8 2WD Gasoline small car 3.0 23 33 27 6 No
3 ACURA TLX 3.5 6 SemiAuto-9 2WD Gasoline small car 3.0 20 32 24 5 No
4 ACURA TLX 3.5 6 SemiAuto-9 4WD Gasoline small car 3.0 21 30 24 5 No

Fix city_mpg, hwy_mpg, cmb_mpg datatypes

2008 and 2018: convert string to float
In [4]:
# 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)

Fix greenhouse_gas_score datatype

2008: convert from float to int
In [5]:
# convert from float to int
df_08['greenhouse_gas_score'] = df_08['greenhouse_gas_score'].astype(int)
In [6]:
df_08.dtypes
Out[6]:
model                    object
displ                   float64
cyl                       int64
trans                    object
drive                    object
fuel                     object
veh_class                object
air_pollution_score     float64
city_mpg                float64
hwy_mpg                 float64
cmb_mpg                 float64
greenhouse_gas_score      int32
smartway                 object
dtype: object
In [7]:
df_18.dtypes
Out[7]:
model                    object
displ                   float64
cyl                       int64
trans                    object
drive                    object
fuel                     object
veh_class                object
air_pollution_score     float64
city_mpg                float64
hwy_mpg                 float64
cmb_mpg                 float64
greenhouse_gas_score      int64
smartway                 object
dtype: object
In [8]:
df_08.dtypes == df_18.dtypes
Out[8]:
model                    True
displ                    True
cyl                      True
trans                    True
drive                    True
fuel                     True
veh_class                True
air_pollution_score      True
city_mpg                 True
hwy_mpg                  True
cmb_mpg                  True
greenhouse_gas_score    False
smartway                 True
dtype: bool
In [9]:
# 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)
In [ ]: