Project 1
ANS:Objective: In this project we are going to clean the dataset of '1985 Automobile Dataset'.
About the Dataset: This is a raw dataset about cars in 1985.
This data set consists of three types of entities:
(a) the specification of an auto in terms of various characteristics,
(b) its assigned insurance risk rating,
(c) its normalized losses in use as compared to other cars. The second rating corresponds to the degree to which the auto is more risky than its price indicates. Cars are initially assigned a risk factor symbol associated with its price. Then, if it is more risky (or less), this symbol is adjusted by moving it up (or down) the scale. Actuarians call this process "symboling". A value of +3 indicates that the auto is risky, -3 that it is probably pretty safe. The third factor is the relative average loss payment per insured vehicle year. This value is normalized for all autos within a particular size classification (two-door small, station wagons, sports/specialty, etc...), and represents the average loss per car per year. Note: Several of the attributes in the database could be used as a "class" attribute. Number of Instances: 205 Number of Attributes: 26 total
-- 15 continuous -- 1 integer -- 10 nominal.
Attribute Information: 1. symboling: -3, -2, -1, 0, 1, 2, 3
2. normalized-losses: continuous from 65 to 256
3. make: alfa-romero, audi, bmw, chevrolet, dodge, honda, isuzu, jaguar, mazda, mercedes-benz, mercury, mitsubishi, nissan, peugot, plymouth, porsche, renault, saab, subaru, toyota, volkswagen, volvo
4. fuel-type: diesel, gas
5. aspiration: std, turbo
6. num-of-doors: four, two
7. body-style: hardtop, wagon, sedan, hatchback, convertible
8. drive-wheels: 4wd, fwd, rwd
9. engine-location: front, rear
10. wheel-base: continuous from 86.6 120.9
11. length: continuous from 141.1 to 208.1
12. width: continuous from 60.3 to 72.3
13. height: continuous from 47.8 to 59.8
14. curb-weight: continuous from 1488 to 4066
15. engine-type: dohc, dohcv, I, ohc, ohcf, ohcv, rotor
16. num-of-cylinders: eight, five, four, six, three, twelve, two
17. engine-size: continuous from 61 to 326
18. fuel-system: lbbl, 2bbl, 4bbl, idi, mfi,
19. bore: continuous from 2.54 to 3.94
20. stroke: continuous from 2.07 to 4.17
21. compression-ratio: continuous from 7 to 23
22. horsepower: continuous from 48 to 288
23. peak-rpm: continuous from 4150 to 6600
24. city-mpg: continuous from 13 to 49
25. highway-mpg: continuous from 16 to 54
26. price: continuous from 5118 to 45400.
Overview: • Importing the necessary libraries and the raw data. • Renaming the columns. • Finding the missing values. • Filling the missing values. • Exporting the data into a new csv file. • Summary and Conclusion.
1. Importing the necessary libraries and the raw data. • In the first step, we are going to import the libraries like pandas, numpy, and matplotlib. After that we are going to import the csv file called 'auto.csv'. import numpy as np import pandas as pd import matplotlib.pyplot as plt df = pd.read_csv('auto.csv'). df
• As we can see that there are 204 data points and 26 attributes in the dataset but the column names are absurdly written. Lets see all the columns at once so that we can get the idea on the number of absurdly written columns
. df.info() <class 'pandas.core.="" frame.="" dataframe'=""> Range Index: 204 entries, 0 to 203 Data columns (total 26 columns):
# Column Non-Null Count Dtype -- ------
03 204 non- null
1 ? 204 non-null
2 alfa-romero 204 non- null
3.gas 204 non- null
4.std 204 non- null
5.two 204 non-null
6convertible 204 non-null
7.rwd 204 non- null
8 front 204 non- null
9 88.6 204 non- null
10 168.8 204 non- null
11 64.1 204 non- null
12 48.8 204 non- null
13 2548 204 non- null int64
14 dohc 204 non- null object
15 four 204 non-null object
16 130 204 non-null int64
17 mpfi 204 non-null object
18 3.47 204 non-null object
19 2.68 204 non-null object
20 9.0 204 non- null floate
21 111 204 non-null object
22 5000 204 non-null object
23 21 204 non-null int64
24 27 204 non-null int64
25 13495 204 non- null object
dtypes: float64(5), int64(5), object(16) memory usage: 41.6+ KB
• By observing the above data we can find that all the column names are absurdly written. So in the next step we will be renaming the columns.
2. Renaming the Columns: • In this step we will first pass a variable named 'column' which contains the array of the new column names and then we will pass this variable to the pandas dataframe to replace it with the new column names
. column = ['Symboling', 'Normalized Losse: 'Engine Location', 'Wheel Base', 'Fuel System', 'Bore','Stroke', df.columns = column df
• So in this way we have replaced the new column names with old one.
3. Finding the missing values:
• If we closely look at the data we will find that there are several '?' sign on the data points which indicates that there are some missing values in the dataset.
As it is difficult to deal with '?' in tha dataset we will replacing it with 'Nan' value so that we can easily deal with the null values. df[df == '?']
The replancement of '?' sign to null values is successfully changed. • Now we can easily determine the columns containing the null values.
df.isna().sum() # Counting the NaN value Symboling
Normalized Losses
Make
FuelType
Aspiration
No. of Doors
Body Style
Drive Wheels
Engine Location
Wheel Base
Length
Width
Height
Curb Weight
Engine Type
No. of Cylinders
Engine Size
Fuel System
Bore Stroke
Compression Ratio
Horsepower
Peak RPM
City MPG
Highway MPG
Price
dtype: int64
By observing the above results we have successfully counted the number missing values in different columns
. According to the results
, 1. Normalized Losses has 40 missing values.
2. No. of Doors has 2 missing values
. 3. Bore has 4 missing values.
4. Stroke has 4 missing values.
5. Horsepower and Peak RPM have 2 missing values each.
6. Price has 4 missing values. • We are now going to check for the number of rows having at least one missing values.
For this process, we will first find the indexes of the datasets which contains at least one null values and then pass it on to the dataframe. d = df.isnull().any(axis = 1) index = d.index[d.values] index Int64Index( [ 0, 1, 4, 6, 8, 45, 47, 48, 54, 55, 1: 56 01 02 0 100 100 1.1)
Int64Index( [ 0, 1, 4, 6, 8, 1: 45, 47, 48, 54, 55, 56 81, 82, 83, 108, 109, 112 130, 180, 188, 190, 191, 19; dtype='int64') These are the indexes of the dataset having at least one missing value throughout the datapoint
Now we are going to locate all these datapoints at once and counting it
. missing = df.iloc[index, :] # No of rows missing 1220 W turne 156 156 ud ud fud 70 10 turbo turbo to hatchback we hatchback to hatchback wagon four wagon 701 turbo wd will front turbo turbo 120 152 156 154 the hatchback hardtop wd hardtop 1220 bis 1220 mitsubishga 1220 peugotas 1220 Peugt diesel 1220 Peugotas 122.0 pengor diesel 122.0 plymouth 122.0 porsche gas 122.0 porsche 1220 porsche 1220 porsche 1220 renautas 1220 1220 toyota gas 1220 volkswagen Gas 1220 volkswagen 1 220 volkswagen diesel 1220 volwagen Spd 3.50000 30000 si 2.59000 3.600 spd 350000 30000 mp3.48000 3.1900 di 3.70000 35200 mp 3.45000 2.1900 3.70000 3.5200 Spdi 3.50000 30000 mpi 3.74000 29000 mp 374000 29000 mph 374000 29000 mp3.94000 1100 mai 46000 39000 3.45000 39000 moll 327000 3.3500 moll 319000 34000 m 3 19000 34000 3 01000 34000 mp 2.19000 34000 so we 194 convertible hatchback w 203 two hatchback 132 to ou 151 wagon two convertible sedan four sedan four wagon wd tud tud wid tud vid od od 0 0 turbo ad front 1004 front 1004 front 1004 136 97 100 90 45 rows x 26 columns
So its been foound out that there are 45 datapoints exist which has at least one missing value
. • Now we are going to check if there is any row or column having all null values. df.isnull().all(axis=0).sum() # If anys
Fuel System Compression Ratio City MPG Symboling dtype: float64 0.00 0.00 0.00 0.00
According to the results it has been found that:
1. Normalized Losses is the column having the most null values of about 19.61 %
. 2. Price,Stroke and Bore are the columns containing 1.96% null values each.
3. Peak RPM, Horsepower and No. of Doors are the columns containing 0.98 % null values each.
4. Filling the missing values: Now that we know every detail about the missing values in the dataset we will now fill the null values. We will replace the null values with the mean of the values of that particular column. In order to do that, the data type of the particular columns should be in floating types, otherwise we will not be able to find the mean of that particular column. • Changing the data types of the target columns. Here our target columns are 'Normalized Losses', 'Price','Stroke','Bore','Peak RPM','Horsepower' and 'No. of Doors. As 'No. of Doors' column is a categorical data it can't be change into float data type. We will deal
with this column later.
First we will change the data type of the target columns excluding 'No. of Doors'. df[['Normalized Losses', 'Price', 'Stroke Now checking whether the mean can be calculated or not. df[['Normalized Losses', 'Price', 'Stroke count mean Std min Normalized Losses Price Stroke Bore Peak RPM Horsepower 204.000000 204.0000 204.00000 204.000000 204.000000 204.000000 122.000000 13205.6900 3.25830 3.329050 5125.990099104222772 31.758944 7888.0996 0.31175 0.271331 478.071104 39.613588 65.000000 5118.0000 2.070002.540000 4150.000000 48.000000 101.000000 7784.7500 3.11000 3.150000 4800.000000 70.000000 122.000000 10470.0000 3.29000 3.310000 5200.000000 95.000000 137.000000 16500.0000 3.41000 3.582500 5500.000000 116.000000 256.000000 45400.0000 4.17000 3.940000 6600.000000 288.000000 25% 50% 75% max So it is clear that we can find out the mean of the each target columns
. • Replacing the null values in 'Normalized Losses' column with the mean of the column i.e. 122.
Let us deal with the 'Normalized Losses' column first as it contains the most number of null values. We will replace the null values with its mean which is 122. df.loc[:, 'Normalized Losses'].fillna(df| Normalized Losses 122.0
122.0 164.0 164.0 122.0 199 95.0 200 95.0 201 95.0 202 95.0 203 95.0 204 rows x 1 columns
It looks like all the null values are replaced with the mean value of the column.
Let's check whether actually all the null values are gone or not. round(df.isnull().sum().sort_values(asce Price Stroke Bore Peak RPM 1.96 1.96 1.96 0.98
'four' indicator to fill the null values. df.loc[:,'No. of Doors'].fillna('four',: df['No. of Doors'].astype('category').Vi 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Price Highway MPG Normalized Losses Make Fuel Type Aspiration No. of Doors Body Style Drive Wheels Engine Location Wheel Base Length Width Height Curb Weight Engine Type No. of Cylinders Engine Size Fuel System Bore Stroke Compression Ratio Horsepower Peak RPM City MPG Symboling 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 We have successfully removed all the null values and hence we have cleaned our whole dataset in this way. 5. Exporting the cleaned dataframe to a csv
5. Exporting the cleaned dataframe to a csv file: df.to_csv('clean_auto.csv') Cars_mileage clean_auto 11-06-2021 19:14 16-06-2021 22:34 Microsoft Excel Co... Microsoft Excel Co... 4 KB 28 KB
6. Summary and Conclusion:
• The raw data was imported. • All the columns was renamed. • The null values was counted. • All the null values was replaced by the columns's mean value. • In the 'No. of Doors' column the null values are replaced by the most numbered categorical indicator. • The new dataframe is exported to a csv file. • Hence the '1985 Automobile Dataset' is cleaned.