Question

Solved Case Study based on Open Datasets.

UCI dataset is a collection of open datasets, available to the public for experimentation and research purposes. ‘auto-mpg’ is one such open dataset. It contains data related to fuel consumption by automobiles in a city. Consumption is measured in miles per gallon (mpg), hence the name of the dataset is auto-mpg. The data has 398 rows (also known as items or instances or objects) and nine columns (also known as attributes). The attributes are: mpg, cylinders, displacement, horsepower, weight, acceleration, model year, origin, car name. Three attributes, cylinders, model year and origin have categorical values, car name is a string with a unique value for every row, while the remaining five attributes have numeric value. The data has been downloaded from the UCI data repository available at http://archive.ics.uci.edu/ ml/machine-learning-databases/auto-mpg/.

Following are the exercises to analyse the data.

1. Load auto-mpg.data into a DataFrame autodf.
2. Give description of the generated DataFrame autodf.
3. Display the first 10 rows of the DataFrame autodf.
4. Find the attributes which have missing values. Handle the missing values using following two ways:
   1. Replace the missing values by a value before that.
   2. Remove the rows having missing values from the original dataset.
5. Print the details of the car which gave the maximum mileage.
6. Find the average displacement of the car given the number of cylinders.
7. What is the average number of cylinders in a car?
8. Determine the no. of cars with weight greater than the average weight.

Answer 1

1)

import pandas as pd
import numpy as np
url = "http://uci.edu"
column_names = ['mpg', 'cylinders', 'displacement', 'horsepower', 'weight', 'acceleration', 'model year', 'origin', 'car name']
autodf = pd.read_csv(url, sep=r'\s+', names=column_names)
print("Data successfully loaded!")

2) 

print(autodf.info())
 print(autodf.describe(include='all'))

3) # View the top 10 rows print(autodf.head(10))

4) 

autodf['horsepower'] = autodf['horsepower'].replace('?', np.nan)
print("Columns with missing values:\n", autodf.isnull().sum())

i. 

autodf_filled = autodf.copy()
autodf_filled['horsepower'] = autodf_filled['horsepower'].ffill()

ii.

autodf.dropna(inplace=True)
autodf['horsepower'] = autodf['horsepower'].astype(float)
print("Shape after dropping missing values:", autodf.shape)

5) 

max_mpg_idx = autodf['mpg'].idxmax()
max_mileage_car = autodf.loc[max_mpg_idx]
print("Car with Maximum Mileage Details:\n", max_mileage_car)

6) 

avg_displacement = autodf.groupby('cylinders')['displacement'].mean() 
print("Average displacement based on cylinders:\n", avg_displacement)

7)

 avg_cylinders = autodf['cylinders'].mean() 
print(f"The average number of cylinders in a car is: {avg_cylinders:.2f}")

8) 

mean_weight = autodf['weight'].mean()
heavy_cars_count = len(autodf[autodf['weight'] > mean_weight])
print(f"The number of cars heavier than the average weight ({mean_weight:.2f} lbs) is: {heavy_cars_count}")