<jupyter_start><jupyter_text>OverviewThe purpose of this notebook is to review the dataset at a high level and prepare the data for modeling   Libraries Import<jupyter_code>import pandas as pd # pandas for DataFrame data structure 
import numpy as np # numpy for calculation, especially vectorization if necessary
import os # importing OS might be necessary to read files depending on your setup<jupyter_output><empty_output><jupyter_text>Data OverviewPandas is an excellent library for processing any tabulated or similar data. It offers phenomenal out of the box functionality for data structure, analysis, visualization, and many more. It is based on numpy making the data processing extremely fast, at least in terms of python, and has become an industry standard in data science. Many libraries that will be used in this notebook and other tutorials in this series also support pandas streamlining many tasks to come.<jupyter_code>df = pd.read_csv('..\data\Emissions_Canada.csv') # import the csv file as DataFrame
df.head() # print the head (top 5 rows) to preview the data<jupyter_output><empty_output><jupyter_text>*** Data CleaningThe first step to any EDA should be checking the completeness of the dataset. If any missing data or obvious outliers are found we can strategize on how to replace or even remove parts of the dataset.<jupyter_code>df.isnull().sum() # chaining isnull with sum methods will provide the total count of missing or null data points on each column<jupyter_output><empty_output><jupyter_text>Looks like the dataset we are dealing with is very clean thanks to Mr. <NAME>!  Organizing the ColumnsKeeping a consistent, clean, and concise naming scheme of the DataFrame columns is a very important aspect that is often overlooked. First, the columns should be clear in delivering the summary of the data that it contains. Second, the columns should be named in a single line of string, connected with underscores if necessary, to take advantage of using the column name as the attribute of the DataFrame. See the example below for my 2nd point.<jupyter_code>df.columns # prints all column names
df.Make # this should print Makes of each index (rows)
df.Make.unique() # this should print an array of unique brand names<jupyter_output><empty_output><jupyter_text>As you might have guessed, the two operations above is not possible with some of the columns, but there are workarounds.<jupyter_code>df.Fuel Type # this will throw a syntax error due to the blank space
df['Fuel Type'] # a work around is possible by slicing the DataFrame with the column name as a string<jupyter_output><empty_output><jupyter_text>Taking some time now to rename these columns will save a lot more time down the line. Also, in this data set there are duplicate columns for combined fuel consumptions where one is provided in L/100km and the other in mpg. I'm going to drop the mpg column to reduce the dataset size and keep the trend of metric units within the dataset.<jupyter_code># dropping the mpg column

df.drop(labels='Fuel Consumption Comb (mpg)', axis=1, inplace=True)
df.head() # no more mpg columns
# rename the columns
# use lowercase letters only
# drop any references to units
# connect words with underscores or simplify to a single word
# making new variable for column_names will be cleaner

column_names = ['make', 'model', 'class', 'engine_size', 'cylinders',
       'transmission', 'fuel_type', 'fuel_consumption_city',
       'fuel_consumption_hwy', 'fuel_consumption_comb', 'emissions']

df.columns = column_names
df.head() #columns should be changed
df.fuel_type # now this should print with no error!<jupyter_output><empty_output><jupyter_text>*** Details on Model, Transmission, and Fuel Type ColumnsAs provided on the [kaggle](https://www.kaggle.com/debajyotipodder/co2-emission-by-vehicles) values within these columns belows can be broken down to the following: Model- 4WD/4X4 = Four-wheel drive- AWD = All-wheel drive- FFV = Flexible-fuel vehicle- SWB = Short wheelbase- LWB = Long wheelbase- EWB = Extended wheelbase Transmission***PREFIX***- A = Automatic- AM = Automated manual- AS = Automatic with select shift- AV = Continuously variable- M = Manual***NUMERICAL SUFFIX***- 3 - 10 = Number of gears Fuel Type- X = Regular gasoline- Z = Premium gasoline- D = Diesel- E = Ethanol (E85)- N = Natural gasLet's dive deeper into each of these columns and see if we can formulate methods to clean or recategorize some of them. We will work backwards from the list above due to the high variety we will face in transmission and model.<jupyter_code># Fuel Type data integrity check

df.fuel_type.unique() # looks like there are no incorrect entry on this column<jupyter_output><empty_output><jupyter_text>TransmissionContinous variable Transmission (CVT) are marked as AV**n** (**n** denoting the  gears) in this data set. However, as far as my understanding of CVT design goes, there are no "gears" in the transmission ([wikipedia](https://en.wikipedia.org/wiki/Continuously_variable_transmission) for reference). CVT is designed to continuously change the gear ratio depending on the engine output to provide a linear power output, hence the name. Although, there are certain settings to make CVT feel more like a traditional geared transmission or usage of clutch to quickly change the ratio it's hard to say what the number of gears should be in a given CVT. Perhaps, the numbers were used to denote the final gearing ratio of the CVT. Meaning that higher the number, more efficient the vehicle is at higher speed, but I cannot be certain of this information. Therefore, I will be changing all entries of AV**n** as CVT.<jupyter_code># Transmission

df.transmission.unique() #again the entry seems correct, but I see some issues
# this function will convert any entry containing 'AV' as 'CVT'
def transmission_clean(transmission):
    if 'AV' in transmission:
        return 'CVT'
    else:
        return transmission
df.transmission = df.transmission.apply(transmission_clean) # you can apply a function to the column with this syntax
df.transmission.unique() #now all AV entries should be changed to CVT<jupyter_output><empty_output><jupyter_text>ModelsSome finesse is going to be required to parse the vehicle model column. Many companies offer many trim levels in a single vehicle model. Also given the repeated model entry it is likely that this dataset contains many vehicles from different year. I will demonstrate this with a simple **groupby** method.<jupyter_code>df.groupby(['make', 'model', 'engine_size', 'transmission'])['class'].count() 
#you can see the multiple entries!<jupyter_output><empty_output><jupyter_text>Let's see what we are dealing with here. Running the code below shows that the model names are quite messy. - There are trim level and modification that is listed with model names. - Not all AWD or 4x4 systems are named AWD, but uses brand name technology like xDrive.<jupyter_code>for name in df.model:
    if len(name.split()) >= 3:
        print(name)<jupyter_output>V8 VANTAGE S
V8 VANTAGE S
A5 CABRIOLET QUATTRO
A6 QUATTRO TDI (modified)
A7 QUATTRO TDI (modified)
A8 TDI (modified)
A8L TDI (modified)
Q5 TDI (modified)
Q7 TDI (modified)
RS 5 CABRIOLET
TT COUPE QUATTRO
TT ROADSTER QUATTRO
TTS COUPE QUATTRO
TTS ROADSTER QUATTRO
CONTINENTAL GT SPEED CONVERTIBLE
328d xDRIVE TOURING
328i xDRIVE GRAN TURISMO
328i xDRIVE TOURING
335i xDRIVE GRAN TURISMO
428i xDRIVE COUPE
435i xDRIVE COUPE
435i xDRIVE COUPE
535i xDRIVE GRAN TURISMO
550i xDRIVE GRAN TURISMO
640i xDRIVE GRAN COUPE
650i xDRIVE CABRIOLET
650i xDRIVE COUPE
650i xDRIVE GRAN COUPE
ALPINA B7 xDRIVE LWB
ALPINA B7 xDRIVE SWB
M6 GRAN COUPE
M6 GRAN COUPE
CTS COUPE AWD
CTS SEDAN AWD
CTS SEDAN AWD
CTS SEDAN Vsport
CTS SPORT WAGON
CTS SPORT WAGON
CTS SPORT WAGON AWD
CTS SPORT WAGON AWD
CTS-V SPORT WAGON
CTS-V SPORT WAGON
ESCALADE ESV AWD
ESCALADE ESV AWD
XTS Vsport AWD
EXPRESS 1500 CARGO
EXPRESS 1500 CARGO
EXPRESS 1500 CARGO
EXPRESS 1500 CARGO AWD
EXPRESS 1500 CARGO AWD
EXPRESS 1500 CARGO CONV
EXPRESS 150[...]<jupyter_text>All in all I think that the model names should be tackled at feature engineering level. For now we will take the classic method of taking the **average** fuel consumption ane emissions. This is done to keep a single row of data per model.   Averaging the consumption and emissionsRunning the code below will create one row of data per model with a unique set of engine and tranmission option. ***reset_index*** function returns the groupby into a dataframe.<jupyter_code>clean_df = df.groupby(['make', 'model', 'engine_size', 'transmission']).mean().reset_index()
clean_df.head()<jupyter_output><empty_output><jupyter_text>Luxury Brand TagsThis section is for creating a `Luxury Brand` column to make data separation easier in the future. In this section following brands will be considered `Luxury`: 'ACURA', 'ALFA ROMEO', 'ASTON MARTIN', 'AUDI', 'BENTLEY', 'BMW', 'BUGATTI', 'BUICK', 'CADILLAC', 'GENESIS', 'JAGUAR', 'LAMBORGHINI', 'LAND ROVER', 'LEXUS', 'LINCOLN', 'MASERATI', 'MERCEDES-BENZ', 'PORSCHE', 'ROLLS-ROYCE'.<jupyter_code>clean_df.make.unique()
def is_luxury(make=str):
    luxury = ['ACURA', 'ALFA ROMEO', 'ASTON MARTIN', 'AUDI', 'BENTLEY', 'BMW', 'BUGATTI', 
              'BUICK', 'CADILLAC', 'GENESIS', 'JAGUAR', 'LAMBORGHINI', 'LAND ROVER', 
              'LEXUS', 'LINCOLN', 'MASERATI', 'MERCEDES-BENZ', 'PORSCHE', 'ROLLS-ROYCE']
    if make in luxury:
        return 1
    else:
        return 0
clean_df['luxury'] = clean_df.make.apply(is_luxury)
clean_df.head()<jupyter_output><empty_output><jupyter_text>Saving the DataFrame<jupyter_code>clean_df.to_csv('..\data\Emissions_Clean.csv', )<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07096441464672512, 'fraction_numerical': 0.010314595152140279, 'mean_word_length': 5.056214865708932, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 54, 'https://': 2, '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': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '82964', 'license': 'MIT', 'n_tokens_mistral': 3027, 'n_tokens_neox': 2822, 'n_words': 1297, 'path': 'eda/emission_clean.ipynb', 'repo_name': 'yunghanjeong/revisit_data_science', 'size': '9705'}