Car Evaluation Sample Project - Part 2
Car Evaluation Dataset: https://archive.ics.uci.edu/ml/datasets/Car+Evaluation
Second Step: Data Wrangling¶
Identifying and handling missing values¶
1. Identify missing data¶
In our dataset, the missing data comes with the question mark "?", so let's replace ? with NaN:
In [12]:
df.replace("?", np.nan, inplace = True)
df.head(5)
Out[12]:
In [13]:
missing_data = df.isnull()
missing_data.head(10)
Out[13]:
In [14]:
missing_data.tail(10)
Out[14]:
Let's count the missing values in each column:¶
In [15]:
for column in missing_data.columns.values.tolist():
print(column)
print (missing_data[column].value_counts())
print("")
Based on the summary above, you can find the below:
- "normalized-losses": 40 missing data
- "num-of-doors": 2 missing data
- "bore": 4 missing data
- "stroke" : 4 missing data
- "horsepower": 2 missing data
- "peak-rpm": 2 missing data
- "price": 4 missing data
2. Dealing with missing data¶
To deal with the missing data, we can choose one way of the below:
- Drop data
a. Drop the whole row
b. Drop the whole column - Replace data
a. Replace it by mean
b. Replace it by frequency
c. Replace it based on other functions
Here is what we are going to do:¶
Replace by mean:
- "normalized-losses": 40 missing data
- "stroke": 4 missing data
- "bore": 4 missing data
- "horsepower": 2 missing data
- "peak-rpm": 2 missing data
Replace by frequency:
- "num-of-doors": 2 missing data, replace them with "4".
Drop the whole row:
- "price": 4 missing data "cannot be used in prediction
[A] Calculate he mean value for the "normalized-losses" column
In [16]:
avg_norm_loss = df["normalized-losses"].astype("float").mean(axis=0)
print("Average of normalized-losses:", avg_norm_loss)
Replace "NaN" with mean value in "normalized-losses" column
In [17]:
df["normalized-losses"].replace(np.nan, avg_norm_loss, inplace=True)
[B] Calculate the mean value for the "bore" column
In [18]:
avg_bore=df['bore'].astype('float').mean(axis=0)
print("Average of bore:", avg_bore)
Replace "NaN" with the mean value in the "bore" column
In [19]:
df["bore"].replace(np.nan, avg_bore, inplace=True)
[C] Calculate the mean value for the "stroke" column
In [20]:
avg_stroke = df["stroke"].astype("float").mean(axis = 0)
print("Average of stroke:", avg_stroke)
Replace "NaN" with the mean value in the "stroke" column
In [21]:
df["stroke"].replace(np.nan, avg_stroke, inplace = True)
[D] Calculate the mean value for the "horsepower" column
In [22]:
avg_horsepower = df['horsepower'].astype('float').mean(axis=0)
print("Average horsepower:", avg_horsepower)
Replace "NaN" with the mean value in the "horsepower" column
In [23]:
df['horsepower'].replace(np.nan, avg_horsepower, inplace=True)
[E] Calculate the mean value for "peak-rpm" column
In [24]:
avg_peakrpm=df['peak-rpm'].astype('float').mean(axis=0)
print("Average peak rpm:", avg_peakrpm)
Replace "NaN" with the mean value in the "peak-rpm" column
In [25]:
df['peak-rpm'].replace(np.nan, avg_peakrpm, inplace=True)
Now, let's check the number of doors column, since we are going to replace the number of doors by frequency, let's check the count of the variables inside the column:¶
In [26]:
df['num-of-doors'].value_counts()
Out[26]:
Replace "NaN" with the most frequent value in the "num-of-doors" column
In [27]:
df["num-of-doors"].replace(np.nan, "four", inplace=True)
Now, let's drop the rows that has no price:¶
In [28]:
df.dropna(subset=["price"], axis=0, inplace=True)
# reset index, because we droped two rows
df.reset_index(drop=True, inplace=True)
In [29]:
df.head()
Out[29]:
3. Correct data format¶
In [30]:
df.dtypes
Out[30]:
Let's correct some columns format:¶
In [31]:
df[["bore", "stroke"]] = df[["bore", "stroke"]].astype("float")
df[["normalized-losses"]] = df[["normalized-losses"]].astype("int")
df[["price"]] = df[["price"]].astype("float")
df[["peak-rpm"]] = df[["peak-rpm"]].astype("float")
Let's recheck again:¶
In [32]:
df.dtypes
Out[32]:
Data Standardization:¶
Transform mpg to L/100km:
In our dataset, the fuel consumption columns "city-mpg" and "highway-mpg" are represented by mpg (miles per gallon) unit
We will need to apply data transformation to transform mpg into L/100km.
Fromula is L/100km = 235 / mpg
In [33]:
df['city-L/100km'] = 235/df["city-mpg"]
# check your transformed data
df.head()
Out[33]:
In [34]:
# replace (original value) by (original value)/(maximum value)
df['length'] = df['length']/df['length'].max()
df['width'] = df['width']/df['width'].max()
df['height'] = df['height']/df['height'].max()
# show the scaled columns
df[["length","width","height"]].head()
Out[34]:
In [35]:
df["horsepower"]=df["horsepower"].astype(int, copy=True)
In [36]:
%matplotlib inline
import matplotlib as plt
from matplotlib import pyplot
plt.pyplot.hist(df["horsepower"])
# set x/y labels and plot title
plt.pyplot.xlabel("horsepower")
plt.pyplot.ylabel("count")
plt.pyplot.title("horsepower bins")
Out[36]:
In [37]:
#We build a bin array with a minimum value to a maximum value by using the bandwidth calculated above.
#The values will determine when one bin ends and another begins.
bins = np.linspace(min(df["horsepower"]), max(df["horsepower"]), 4)
bins
Out[37]:
In [38]:
#set group names
group_names = ['Low', 'Medium', 'High']
In [39]:
#We apply the function "cut" to determine what each value of `df['horsepower']` belongs to.
df['horsepower-binned'] = pd.cut(df['horsepower'], bins, labels=group_names, include_lowest=True )
df[['horsepower','horsepower-binned']].head(20)
Out[39]:
In [40]:
#check number of cars in each bin
df["horsepower-binned"].value_counts()
Out[40]:
In [41]:
#plotting the distribution of each bin
%matplotlib inline
import matplotlib as plt
from matplotlib import pyplot
pyplot.bar(group_names, df["horsepower-binned"].value_counts())
# set x/y labels and plot title
plt.pyplot.xlabel("horsepower")
plt.pyplot.ylabel("count")
plt.pyplot.title("horsepower bins")
Out[41]:
Indicator Variables¶
We use indicator variables so we can use categorical variables for regression analysis in the later modules¶
Let's check our columns again:¶
In [42]:
df.columns
Out[42]:
We'll create another 2 columns representing the categories of fuel (gas or deisel)¶
In [43]:
dummy_variable_1 = pd.get_dummies(df["fuel-type"])
dummy_variable_1.head()
Out[43]:
We'll change the column names for clarity¶
In [44]:
dummy_variable_1.rename(columns={'gas':'fuel-type-gas', 'diesel':'fuel-type-diesel'}, inplace=True)
dummy_variable_1.head()
Out[44]:
Now, we have to merge the new columns in our dataframe, and drop the "fuel-type" column¶
In [45]:
# merge data frame "df" and "dummy_variable_1"
df = pd.concat([df, dummy_variable_1], axis=1)
In [46]:
# drop original column "fuel-type" from "df"
df.drop("fuel-type", axis = 1, inplace=True)
We will do the same to the aspiration column:¶
In [47]:
# get indicator variables of aspiration and assign it to data frame "dummy_variable_2"
dummy_variable_2 = pd.get_dummies(df['aspiration'])
# change column names for clarity
dummy_variable_2.rename(columns={'std':'aspiration-std', 'turbo': 'aspiration-turbo'}, inplace=True)
# show first 5 instances of data frame "dummy_variable_1"
dummy_variable_2.head()
Out[47]:
In [48]:
# merge the new dataframe to the original datafram
df = pd.concat([df, dummy_variable_2], axis=1)
# drop original column "aspiration" from "df"
df.drop('aspiration', axis = 1, inplace=True)
Let's check finally our clean dataframe:¶
In [49]:
df.head()
Out[49]:
We can now save this clean dataframe to a new csv:¶
In [50]:
df.to_csv('clean_df.csv')