<jupyter_start><jupyter_text>Case Study 2 - Analyzing Fuel Economy Data 1. Assessing DataUsing pandas to explore ```all_alpha_08.csv``` and ```all_alpha_18.csv``` to answer the following questions about the characteristics of the datasets:- number of samples in each dataset- number of columns in each dataset- duplicate rows in each dataset- datatypes of columns- features with missing values- number of non-null unique values for features in each dataset- what those unique values are and counts for each- Number of rows with missing values in each dataset- Types of fuels present in each dataset<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
% matplotlib inline

df_08 = pd.read_csv('all_alpha_08.csv')
df_18 = pd.read_csv('all_alpha_18.csv')<jupyter_output><empty_output><jupyter_text>Number of samples & columns in the fuel economy 2008 dataset<jupyter_code>print(df_08.shape)
df_08.head()<jupyter_output>(2404, 18)<jupyter_text>Number of samples & columns in the fuel economy 2018 dataset<jupyter_code>print(df_18.shape)
df_18.head()<jupyter_output>(1611, 18)<jupyter_text>Duplicate rows in the fuel economy 2008 dataset<jupyter_code>df_08.duplicated().sum()<jupyter_output><empty_output><jupyter_text>Duplicate rows in the fuel economy 2018 dataset<jupyter_code>df_18.duplicated().sum()<jupyter_output><empty_output><jupyter_text>Datatype of column in the fuel economy 2008 dataset<jupyter_code>df_08.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2404 entries, 0 to 2403
Data columns (total 18 columns):
Model                   2404 non-null object
Displ                   2404 non-null float64
Cyl                     2205 non-null object
Trans                   2205 non-null object
Drive                   2311 non-null object
Fuel                    2404 non-null object
Sales Area              2404 non-null object
Stnd                    2404 non-null object
Underhood ID            2404 non-null object
Veh Class               2404 non-null object
Air Pollution Score     2404 non-null object
FE Calc Appr            2205 non-null object
City MPG                2205 non-null object
Hwy MPG                 2205 non-null object
Cmb MPG                 2205 non-null object
Unadj Cmb MPG           2205 non-null float64
Greenhouse Gas Score    2205 non-null object
SmartWay                2404 non-null object
dtypes: float64(2), object(16)
memory usage: 338.1+ KB<jupyter_text>Datatype of column in the fuel economy 2018 dataset<jupyter_code>df_18.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 1611 entries, 0 to 1610
Data columns (total 18 columns):
Model                   1611 non-null object
Displ                   1609 non-null float64
Cyl                     1609 non-null float64
Trans                   1611 non-null object
Drive                   1611 non-null object
Fuel                    1611 non-null object
Cert Region             1611 non-null object
Stnd                    1611 non-null object
Stnd Description        1611 non-null object
Underhood ID            1611 non-null object
Veh Class               1611 non-null object
Air Pollution Score     1611 non-null int64
City MPG                1611 non-null object
Hwy MPG                 1611 non-null object
Cmb MPG                 1611 non-null object
Greenhouse Gas Score    1611 non-null int64
SmartWay                1611 non-null object
Comb CO2                1611 non-null object
dtypes: float64(2), int64(2), object(14)
memory usage: 226.6+ KB<jupyter_text>> Noticed that the following features have different datatype in 2008 and 2018:> - Cyl (2018) - float> - Cyl (2008) - string> - Greenhouse Gas Score (2008) - string> - Greenhouse Gas Score (2018) - int  Features with missing values in the fuel economy 2008 dataset<jupyter_code>df_08.isnull().sum()<jupyter_output><empty_output><jupyter_text>Features with missing values in the fuel economy 2018 dataset<jupyter_code>df_18.isnull().sum()<jupyter_output><empty_output><jupyter_text>Number of unique values for quality in fuel economy 2008 dataset<jupyter_code>df_08.nunique()<jupyter_output><empty_output><jupyter_text>Number of unique values for quality in fuel economy 2018 dataset<jupyter_code>df_18.nunique()<jupyter_output><empty_output><jupyter_text>Number of rows with missing values in fuel economy 2008 dataset<jupyter_code>df_08.isnull().any(axis=1).sum()<jupyter_output><empty_output><jupyter_text>Number of rows with missing values in fuel economy 2018 dataset<jupyter_code>df_18.isnull().any(axis=1).sum()<jupyter_output><empty_output><jupyter_text>Types of fuels present in 2008 dataset<jupyter_code>df_08.Fuel.unique()<jupyter_output><empty_output><jupyter_text>Types of fuels present in 2018 dataset<jupyter_code>df_18.Fuel.unique()<jupyter_output><empty_output><jupyter_text>2. Cleaning Column Labels<jupyter_code># view 2008 dataset
df_08.head(1)
# view 2018 dataset
df_18.head(1)<jupyter_output><empty_output><jupyter_text>Drop Extraneous ColumnsDrop features that aren't consistent (not present in both datasets) or aren't relevant to our questions.Columns to Drop:- From 2008 dataset: ```'Stnd', 'Underhood ID', 'FE Calc Appr', 'Unadj Cmb MPG'```- From 2018 dataset: ```'Stnd', 'Stnd Description', 'Underhood ID', 'Comb CO2'```<jupyter_code># drop columns from 2008 dataset
df_08.drop(['Stnd', 'Underhood ID', 'FE Calc Appr', 'Unadj Cmb MPG'], axis=1, inplace=True)

# confirm changes
df_08.head(1)
# drop columns from 2018 dataset
df_18.drop(['Stnd', 'Stnd Description', 'Underhood ID', 'Comb CO2'], axis=1, inplace=True)

# confirm changes
df_18.head(1)<jupyter_output><empty_output><jupyter_text>Rename ColumnsChange the "Sales Area" column label in the 2008 dataset to "Cert Region" for consistency.Rename all column labels to replace spaces with underscores and convert everything to lowercase.<jupyter_code># rename Sales Area to Cert Region
df_08.rename(columns={'Sales Area': 'Cert Region'}, inplace=True)

# confirm changes
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)
# 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)
# 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.csv', index=False)
df_18.to_csv('data_18.csv', index=False)<jupyter_output><empty_output><jupyter_text>3. Filter, Drop Nulls, Dedupe<jupyter_code># view dimensions of dataset
df_08.shape
# view dimensions of dataset
df_18.shape<jupyter_output><empty_output><jupyter_text>Filter by Certification Region<jupyter_code># 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', axis=1, inplace=True)
df_18.drop('cert_region', axis=1, inplace=True)
df_08.shape
df_18.shape<jupyter_output><empty_output><jupyter_text>Drop Rows with Missing Values<jupyter_code># 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(inplace=True)
df_18.dropna(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()<jupyter_output><empty_output><jupyter_text>Dedupe Data<jupyter_code># print number of duplicates in 2008 datasets
df_08.duplicated().sum()
# print number of duplicates in 2018 datasets
df_18.duplicated().sum()
# drop duplicates in 2008 datasets
df_08.drop_duplicates(inplace=True)
# drop duplicates in 2018 datasets
df_18.drop_duplicates(inplace=True)
# print number of duplicates again to confirm dedupe - should be 0
df_08.duplicated().sum()
# print number of duplicates again to confirm dedupe - should be 0
df_18.duplicated().sum()
# save progress for the next section
df_08.to_csv('data_08.csv', index=False)
df_18.to_csv('data_18.csv', index=False)<jupyter_output><empty_output><jupyter_text>4. Inspecting Data TypesUse the following Jupyter Notebook to inspect the datatypes of features in each dataset and think about what changes should be made to make them practical and consistent (in both datasets).<jupyter_code>df_08 = pd.read_csv('data_08.csv')
df_18 = pd.read_csv('data_18.csv')
# Datatype of column in the fuel economy 2008 dataset
df_08.info()
# Datatype of column in the fuel economy 2018 dataset
df_18.info()
# View first row of data in the fuel economy 2008 dataset
df_08.head(1)
# View first row of data in the fuel economy 2018 dataset
df_18.head(1)<jupyter_output><empty_output><jupyter_text>> 1) Need to extract int values from the strings in the 2008 ```cyl``` column and convert floats to ints in the 2018 >```cyl``` column to make the ```cyl``` column in both dataset clear and consistent.>2) For ```air_pollution_score``` column: Need to convert strings to floats in the 2008 column and convert ints to floats in >the 2018 columns.>3) The following features need to be convereted to floats from strings for both dataset:> - city_mpg> - hwy_mpg> - cmb_mpg>4) Need to convert string to ints in the 2008 column to make the ```greenhouse_gas_score``` columns in both datasets >consistent.   5. Fixing Data TypesMake the following changes to make the datatypes consistent and practical to work with: 1) Fix ```cyl``` datatype- 2008: extract int from string.- 2018: convert float to int.<jupyter_code># check value counts for the 2008 cyl column
df_08['cyl'].value_counts()
# Extract int from strings in the 2008 cyl column

# str.extract() takes the string data and extracts the argument passed within
# \d refers to characters which are digits
# + matches one or more characters
# astype(int) then converts it to an integer

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 2018 cyl column again to confirm the change
df_18['cyl'].value_counts()
df_08.to_csv('data_08.csv', index=False)
df_18.to_csv('data_18.csv', index=False)<jupyter_output><empty_output><jupyter_text>2) Fix ```air_pollution_score``` datatype- 2008: convert string to float.- 2018: convert int to float.<jupyter_code># try using Pandas to_numeric or astype function to convert the
# 2008 air_pollution_score column to float -- this won't work
df_08.air_pollution_score = df_08.air_pollution_score.astype(float)<jupyter_output><empty_output><jupyter_text>> The above did not work. According to the error above, there is error with the value "6/4" - let's check it out.<jupyter_code># To find out the position for the value - "6/4"

error_position = df_08.query('air_pollution_score == "6/4"')
print(error_position)<jupyter_output><empty_output><jupyter_text>> According to the error above and position, the value at row 582 is "6/4".<jupyter_code># Figuring out the issue
df_08.iloc[582]<jupyter_output><empty_output><jupyter_text>> According to [this link](http://www.fueleconomy.gov/feg/findacarhelp.shtmlairPollutionScore), 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."    >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.<jupyter_code># 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<jupyter_output><empty_output><jupyter_text>> 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 "/").<jupyter_code># 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
# 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
# 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<jupyter_output><empty_output><jupyter_text>Repeat process for the 2018 dataset<jupyter_code># create two copies of the 2018 hybrids dataframe, hb_18
df1 = hb_18.copy()
df2 = hb_18.copy()<jupyter_output><empty_output><jupyter_text>Split values for `fuel`, `city_mpg`, `hwy_mpg`, `cmb_mpg`not required for `air_pollution_score` or `greenhouse_gas_score` here because these columns are already ints in the 2018 dataset.<jupyter_code># 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<jupyter_output><empty_output><jupyter_text>Continue the changes needed for `air_pollution_score`:- 2008: convert string to float- 2018: convert int to float<jupyter_code># convert string to float for 2008 air pollution column
df_08.air_pollution_score = df_08.air_pollution_score.astype(float)
# convert int to float for 2018 air pollution column
df_18.air_pollution_score = df_18.air_pollution_score.astype(float)
df_08.to_csv('data_08.csv', index=False)
df_18.to_csv('data_18.csv', index=False)<jupyter_output><empty_output><jupyter_text>3) Fix ```city_mpg```, ```hwy_mpg```, ```cmb_mpg``` datatypes- 2008 and 2018: convert string to float.<jupyter_code># 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)<jupyter_output><empty_output><jupyter_text>4) Fix ```greenhouse_gas_score``` datatype- 2008: convert from float to int.<jupyter_code># convert from float to int
df_08['greenhouse_gas_score'] = df_08['greenhouse_gas_score'].astype(int)<jupyter_output><empty_output><jupyter_text>Check to make sure all datatype are fixed<jupyter_code>df_08.dtypes
df_18.dtypes
df_08.dtypes == df_18.dtypes
# Save your new CLEAN datasets as new files!
df_08.to_csv('clean_08.csv', index=False)
df_18.to_csv('clean_18.csv', index=False)<jupyter_output><empty_output><jupyter_text>6. Exploring with VisualsUse histograms and scatterplots to explore clean_08.csv and clean_18.csv in the Jupyter notebook.<jupyter_code>df_08 = pd.read_csv('clean_08.csv')
df_18 = pd.read_csv('clean_18.csv')<jupyter_output><empty_output><jupyter_text>Compare the distributions of greenhouse gas score in 2008 and 2018.<jupyter_code>df_08['greenhouse_gas_score'].hist();
df_18['greenhouse_gas_score'].hist();<jupyter_output><empty_output><jupyter_text>> Distribution for 2008 is more skewed to the left.  How has the distribution of combined mpg changed from 2008 to 2018?<jupyter_code>df_08['cmb_mpg'].hist();
df_18['cmb_mpg'].hist();<jupyter_output><empty_output><jupyter_text>> Became much more skewed to the right  Describe the correlation between displacement and combined mpg.<jupyter_code>df_08.plot(x='displ', y='cmb_mpg', kind='scatter');
df_18.plot(x='displ', y='cmb_mpg', kind='scatter');<jupyter_output><empty_output><jupyter_text>> Negative correlation  Describe the correlation between greenhouse gas score and combined mpg.<jupyter_code>df_08.plot(x='greenhouse_gas_score', y='cmb_mpg', kind='scatter');
df_18.plot(x='greenhouse_gas_score', y='cmb_mpg', kind='scatter');<jupyter_output><empty_output><jupyter_text>> Positive correlation  7. Conclusions & Visuals<jupyter_code># load datasets
df_08 = pd.read_csv('clean_08.csv')
df_18 = pd.read_csv('clean_18.csv')
df_08.head(1)<jupyter_output><empty_output><jupyter_text>Q1: Are more unique models using alternative sources of fuel? By how much? Let's first look at what the sources of fuel are and which ones are alternative sources.<jupyter_code>df_08.fuel.value_counts()
df_18.fuel.value_counts()<jupyter_output><empty_output><jupyter_text>Looks like the alternative sources of fuel available in 2008 are CNG and ethanol, and those in 2018 ethanol and electricity. (You can use Google if you weren't sure which ones are alternative sources of fuel!)<jupyter_code># how many unique models used alternative sources of fuel in 2008
alt_08 = df_08.query('fuel in ["CNG", "ethanol"]').model.nunique()
alt_08
# how many unique models used alternative sources of fuel in 2018
alt_18 = df_18.query('fuel in ["Ethanol", "Electricity"]').model.nunique()
alt_18
plt.bar(["2008", "2018"], [alt_08, alt_18])
plt.title("Number of Unique Models Using Alternative Fuels")
plt.xlabel("Year")
plt.ylabel("Number of Unique Models");<jupyter_output><empty_output><jupyter_text>Since 2008, the number of unique models using alternative sources of fuel increased by 24. We can also look at proportions.<jupyter_code># total unique models each year
total_08 = df_08.model.nunique()
total_18 = df_18.model.nunique()
total_08, total_18
prop_08 = alt_08/total_08
prop_18 = alt_18/total_18
prop_08, prop_18
plt.bar(["2008", "2018"], [prop_08, prop_18])
plt.title("Proportion of Unique Models Using Alternative Fuels")
plt.xlabel("Year")
plt.ylabel("Proportion of Unique Models");<jupyter_output><empty_output><jupyter_text>Q2: How much have vehicle classes improved in fuel economy?   Let's look at the average fuel economy for each vehicle class for both years.<jupyter_code>veh_08 = df_08.groupby('veh_class').cmb_mpg.mean()
veh_08
veh_18 = df_18.groupby('veh_class').cmb_mpg.mean()
veh_18
# how much they've increased by for each vehicle class
inc = veh_18 - veh_08
inc
# only plot the classes that exist in both years
inc.dropna(inplace=True)
plt.subplots(figsize=(8, 5))
plt.bar(inc.index, inc)
plt.title('Improvements in Fuel Economy from 2008 to 2018 by Vehicle Class')
plt.xlabel('Vehicle Class')
plt.ylabel('Increase in Average Combined MPG');<jupyter_output><empty_output><jupyter_text>Q3: What are the characteristics of SmartWay vehicles? Have they changed over time? We can analyze this by filtering each dataframe by SmartWay classification and exploring these datasets.<jupyter_code># smartway labels for 2008
df_08.smartway.unique()
# get all smartway vehicles in 2008
smart_08 = df_08.query('smartway == "yes"')
# explore smartway vehicles in 2008
smart_08.describe()<jupyter_output><empty_output><jupyter_text>Use what you've learned so for to further explore this dataset on 2008 smartway vehicles.<jupyter_code># smartway labels for 2018
df_18.smartway.unique()
# get all smartway vehicles in 2018
smart_18 = df_18.query('smartway in ["Yes", "Elite"]')
smart_18.describe()<jupyter_output><empty_output><jupyter_text>Use what you've learned so for to further explore this dataset on 2018 smartway vehicles.  Q4: What features are associated with better fuel economy? You can explore trends between cmb_mpg and the other features in this dataset, or filter this dataset like in the previous question and explore the properties of that dataset. For example, you can select all vehicles that have the top 50% fuel economy ratings like this.<jupyter_code>top_08 = df_08.query('cmb_mpg > cmb_mpg.mean()')
top_08.describe()
top_18 = df_18.query('cmb_mpg > cmb_mpg.mean()')
top_18.describe()<jupyter_output><empty_output><jupyter_text>Q5: For all of the models that were produced in 2008 that are still being produced in 2018, how much has the mpg improved and which vehicle improved the most? This is a question regarding models that were updated since 2008 and still being produced in 2018. In order to do this, we need a way to compare models that exist in both datasets.  Create combined dataset1. Rename 2008 columns to distinguish from 2018 columns after the mergeTo do this, use Pandas' rename() with a lambda function. In the lambda function, take the first 10 characters of the column label and and concatenate it with _2008. (Only take the first 10 characters to prevent really long column names.)<jupyter_code># rename 2008 columns
df_08.rename(columns=lambda x: x[:10] + "_2008", inplace=True)
# view to check names
df_08.head()
# merge datasets
df_combined = df_08.merge(df_18, left_on='model_2008', right_on='model', how='inner')
# view to check merge
df_combined.head()<jupyter_output><empty_output><jupyter_text>Save the combined dataset<jupyter_code>df_combined.to_csv('combined_dataset.csv', index=False)<jupyter_output><empty_output><jupyter_text>1) Create a new dataframe, ```model_mpg```, that contain the mean combined mpg values in 2008 and 2018 for each unique modelTo do this, group by model and find the mean ```cmb_mpg_2008``` and ```mean cmb_mpg``` for each.<jupyter_code>combined_df = pd.read_csv('combined_dataset.csv')
# get mean values for each model
model_mpg = combined_df.groupby(['model'])['cmb_mpg_2008', 'cmb_mpg','model'].mean()
model_mpg.head()<jupyter_output><empty_output><jupyter_text>2) Create a new column, ```mpg_change```, with the change in mpg Subtract the mean mpg in 2008 from that in 2018 to get the change in mpg<jupyter_code># add column for mpg_change
model_mpg['mpg_change'] = model_mpg['cmb_mpg'] - model_mpg['cmb_mpg_2008']<jupyter_output><empty_output><jupyter_text>3) Find the vehicle that improved the mostFind the max mpg change, and then use query or indexing to see what model it is!<jupyter_code>model_mpg.sort_values(by='mpg_change', ascending=False).head(10)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09862119324771272, 'fraction_numerical': 0.05094282891628366, 'mean_word_length': 5.426166160640354, 'pattern_counts': {'":': 0, '<': 211, '<?xml version=': 0, '>': 238, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '53', 'dir': 'jupyter-structured-clean-dedup', 'id': '137458', 'license': 'MIT', 'n_tokens_mistral': 8395, 'n_tokens_neox': 7412, 'n_words': 2459, 'path': 'Fuel Data Analysis/analyze_fuel_economy_data.ipynb', 'repo_name': 'Siddharth1698/Data-Analyst-Nanodegree', 'size': '23281'}