Data Science — Assignment 02

Question 1 — Exploratory Data Analysis on Automobile Dataset

Dataset Overview
The automobile dataset contains attributes including price, engine-size, horsepower, fuel-type, body-style,
city-mpg, highway-mpg, make, and num-of-cylinders. EDA was performed end-to-end covering missing value
detection, imputation, encoding, transformation, and correlation analysis.

Step 1 — Identifying and Handling Missing Values

Missing values were detected across three numeric columns: engine-size, horsepower, and price. Three
strategies were applied and compared:

Figure 1.1 — Missing value heatmap (yellow = missing)

Imputation Strategies
• Mean Imputation — Replaces missing values with the column mean. Best for small datasets with normally
distributed data where outliers are minimal. Fast and simple but sensitive to skew.
• Median Imputation — Uses the median instead of mean. Preferred for medium-sized datasets where the
distribution is skewed (e.g. price is often right-skewed). Robust to outliers.
• KNN Imputation — Finds the k nearest neighbours and uses their values to estimate missing ones. Best
for large datasets where relationships between features carry predictive power. More computationally
expensive but produces more realistic imputations.
import pandas as pd
import numpy as np
from [Link] import KNNImputer

# Mean imputation
df_mean = [Link]()
df_mean['price'].fillna(df_mean['price'].mean(), inplace=True)

# Median imputation
 df_median = [Link]()
df_median['price'].fillna(df_median['price'].median(), inplace=True)

# KNN imputation (uses relationships across multiple columns)

knn = KNNImputer(n_neighbors=5)
num_cols = ['engine-size', 'horsepower', 'price', 'city-mpg', 'highway-mpg']
df_knn[num_cols] = knn.fit_transform(df_knn[num_cols])

Figure 1.2 — Price distribution after Mean, Median, and KNN imputation

Step 2 — One-Hot Encoding of body-style

The body-style column is a nominal categorical variable with no natural order (sedan, hatchback, wagon,
convertible, hardtop). One-hot encoding converts it into binary columns so machine learning models can process
it without assuming any ordinal relationship between categories.
df_encoded = pd.get_dummies(df_mean, columns=['body-style'], prefix='body')
# Creates: body_convertible, body_hardtop, body_hatchback, body_sedan, body_wagon
print([c for c in df_encoded.columns if [Link]('body')])
# Output: ['body_convertible', 'body_hardtop', 'body_hatchback', 'body_sedan', 'body_wagon']

Step 3 — Log Transformation on Price

Automobile prices are right-skewed — a few very expensive cars stretch the distribution. Many regression
models assume the target variable is approximately normally distributed. Applying log1p (log(x+1)) compresses
the right tail, reduces the effect of outliers, and often leads to better model performance and more stable
coefficient estimates.
df_encoded['log_price'] = np.log1p(df_encoded['price'])
# Before: skewed distribution with long right tail
# After: approximately bell-shaped — better for linear regression
 Figure 1.3 — Original vs log-transformed price distribution

Step 4 — Correlation Analysis

Figure 1.4 — Correlation heatmap of numeric features

Engine-size and horsepower show strong positive correlation with price (~0.85), confirming they are key
predictors. City-mpg and highway-mpg are negatively correlated with price, as larger/more powerful cars tend to
be less fuel efficient. Log_price shows slightly stronger linear correlations than raw price — confirming the value
of the transformation.

Question 2 — End-to-End Retail Sales Data Pipeline

Part (a) — SQL Queries for Sales Extraction

The following SQL extracts the last financial year's sales (April 2023 – March 2024), grouped by category and
region. Window functions add a running total for trend analysis.
 -- Extract last financial year sales, grouped by category and region
SELECT
category,
store_region,
SUM(sales_amount) AS total_sales,
SUM(quantity_sold) AS total_qty,
COUNT(product_id) AS num_transactions,
AVG(sales_amount) AS avg_transaction_value,
-- Running total within each region ordered by category
SUM(SUM(sales_amount)) OVER (
PARTITION BY store_region
ORDER BY category
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_in_region
FROM sales
WHERE sale_date >= '2023-04-01'
AND sale_date < '2024-04-01'
AND sales_amount IS NOT NULL
GROUP BY category, store_region
ORDER BY store_region, total_sales DESC;

Part (b) — Python Pandas Cleaning Pipeline

import pandas as pd

# Load SQL result (from DB connection or CSV export)

df = pd.read_sql(query, connection) # or pd.read_csv('sales_export.csv')

print("Raw shape:", [Link])

print("Nulls:\n", [Link]().sum())
print("Duplicates:", [Link]().sum())

# 1. Remove exact duplicate rows

df.drop_duplicates(inplace=True)

# 2. Handle nulls — fill sales_amount with median (skewed distribution)

df['sales_amount'].fillna(df['sales_amount'].median(), inplace=True)

# 3. Fix data types

df['sale_date'] = pd.to_datetime(df['sale_date'])
df['quantity_sold'] = df['quantity_sold'].astype(int)
df['sales_amount'] = df['sales_amount'].astype(float)

# 4. Group by category and region (mirrors the SQL GROUP BY)

grouped = [Link](['category', 'store_region']).agg(
total_sales = ('sales_amount', 'sum'),
total_qty = ('quantity_sold', 'sum'),
num_transactions = ('product_id', 'count')
).reset_index().round(2)

print([Link](10).to_string(index=False))
 Figure 2.1 — Sales by category (bar) and category-region heatmap

The bar chart shows Electronics and Food as the highest-grossing categories. The heatmap reveals regional
variation — West leads in Clothing sales while Electronics performs strongest in the East. These insights would
drive inventory and regional marketing decisions.

Question 3 — NumPy Broadcasting and Pandas Copy Semantics

Part (a) — Broadcasting: Normalising a 100×5 Exam Score Array

Broadcasting is NumPy's mechanism for performing operations on arrays of different shapes without explicit
loops. When shapes are compatible — meaning each dimension is either equal or one of them is 1 — NumPy
stretches the smaller array across the larger one automatically.
import numpy as np

# Simulate 100 students, 5 subjects

scores = [Link](30, 100, size=(100, 5)).astype(float)

# Min and max computed column-wise — result shape: (5,)

col_min = [Link](axis=0) # shape: (5,)
col_max = [Link](axis=0) # shape: (5,)

# Broadcasting: (100,5) - (5,) → NumPy stretches (5,) across 100 rows

normalised = (scores - col_min) / (col_max - col_min)

# Verify
print("Shape of scores: ", [Link]) # (100, 5)
print("Shape of col_min: ", col_min.shape) # (5,)
print("Min after norm: ", [Link](axis=0)) # all 0.0
print("Max after norm: ", [Link](axis=0)) # all 1.0

# How broadcasting works here:

# scores shape: (100, 5)
# col_min shape: (5,) ← treated as (1, 5), then stretched to (100, 5)
# Result: (100, 5) ← element-wise subtraction, no loop needed
The key rule: dimensions are compared from the right. A (5,) array aligns with the last dimension of (100,5).
Since they match (both are 5), NumPy virtually replicates the (5,) array across all 100 rows. The entire
normalisation happens in a single vectorised operation — no Python for loop, and significantly faster on large
arrays.

Figure 3.1 — Score distributions before and after min-max normalisation via broadcasting

Part (b) — Deep Copy vs Shallow Copy in Pandas

This distinction matters because Pandas DataFrames use an internal memory model where operations may or
may not return a new block of memory. Accidentally modifying a 'copy' that is actually a view of the original can
corrupt the original data — especially dangerous when processing large datasets where you might assume your
transformations are isolated.
import pandas as pd

original = [Link]({'A': [1, 2, 3, 4, 5], 'B': [10, 20, 30, 40, 50]})

# Shallow copy — same underlying data, changes propagate

shallow = original # NOT a copy — same object entirely
view = original[:] # view / may share memory

# Deep copy — fully independent memory block

deep = [Link](deep=True)

# Mutate the original

[Link][0, 'A'] = 999

print("Original A[0]:", [Link][0, 'A']) # 999

print("Shallow A[0]:", [Link][0, 'A']) # 999 ← changed (same object)
print("View A[0]:", [Link][0, 'A']) # may be 999 or 1 depending on copy-on-write
print("Deep A[0]:", [Link][0, 'A']) # 1 ← unaffected, fully independent

# For large datasets: always use deep copy when you need an independent copy
# Shallow copies save memory but risk unintended side effects
safe_df = [Link]() # deep=True is the default — safest habit

For large datasets, creating unnecessary deep copies wastes memory — a 1GB DataFrame copied three times
uses 4GB. The best practice is to use deep copies only when you genuinely need to preserve the original after
transformation, and to chain Pandas operations (e.g. method chaining with assign()) to avoid intermediate copies
altogether.

Figure 3.2 — Effect of mutation on original, shallow copy, and deep copy

Question 4 — End-to-End Data Science Project: Telecom Customer Churn

Part (a) — CRISP-DM Framework: Business Problem & Success Criteria

The telecom company faces a 28% annual churn rate — meaning roughly 1 in 4 customers leaves every year.
Acquiring a new customer typically costs 5–7× more than retaining an existing one, so even a moderate
reduction in churn has significant revenue impact.

• Business Understanding: Goal: reduce annual churn from 28% to under 18% within 12 months by
identifying at-risk customers early and enabling targeted retention interventions. Measurable success: (1)
Model recall ≥ 80% on churned customers; (2) Precision ≥ 65% to avoid over-contacting low-risk customers;
(3) ROI positive — cost of retention offers < revenue saved from churners prevented.
• Data Understanding: Sources: CRM system (demographics, contract type, tenure), billing system (monthly
charges, payment history), network logs (data usage, call volume), and customer service records (number of
complaints). Key initial finding: Month-to-Month contract customers churn at 46% vs 14% for annual
contracts.
• Data Preparation: Handle missing values (median imputation for charges, KNN for usage data), encode
categorical variables (contract type, internet service, region), engineer features (charge-per-gb, complaint
rate, tenure bucket), and balance classes using SMOTE since churned customers are the minority.
• Modelling: Baseline: Logistic Regression. Primary: XGBoost / Random Forest (handle non-linearity well).
Evaluation: F1-Score and Recall prioritised over accuracy due to class imbalance. Use SHAP values for
model explainability — essential for business stakeholders.
• Evaluation: Compare models on held-out test set. Threshold tuning: lower threshold to maximise recall
(catch more churners), accepting slightly lower precision. Present a confusion matrix and cost-benefit
analysis.
• Deployment: Deploy as a monthly scoring pipeline — score all active customers, flag top 15% highest-risk,
trigger automated retention campaigns (loyalty discounts, contract upgrade offers). Monitor model
 performance monthly; retrain quarterly.

Part (b) — SQL: Extract and Join Customer Data

-- Extract customer usage data joined with demographic data
SELECT
c.customer_id,
[Link],
[Link],
[Link],
c.contract_type,
c.internet_service,
b.monthly_charges,
b.tenure_months,
-- Derived features
ROUND(b.monthly_charges * b.tenure_months, 2) AS total_revenue,
u.data_usage_gb,
u.num_calls,
cs.num_complaints,
-- Target variable
[Link]
FROM customers c
LEFT JOIN billing b ON c.customer_id = b.customer_id
LEFT JOIN usage_data u ON c.customer_id = u.customer_id
AND [Link] = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
LEFT JOIN customer_service cs ON c.customer_id = cs.customer_id
LEFT JOIN churn_labels ch ON c.customer_id = ch.customer_id
WHERE c.account_status = 'Active'
AND b.billing_period = DATE_TRUNC('year', CURRENT_DATE)
ORDER BY c.customer_id;

-- Churn rate by contract type (quick business summary)

SELECT
contract_type,
COUNT(*) AS total_customers,
SUM(churned) AS churned_count,
ROUND(AVG(churned) * 100, 1) AS churn_rate_pct,
ROUND(AVG(monthly_charges), 2) AS avg_monthly_charge
FROM customers c
JOIN billing b ON c.customer_id = b.customer_id
JOIN churn_labels ch ON c.customer_id = ch.customer_id
GROUP BY contract_type
ORDER BY churn_rate_pct DESC;

Part (c) — EDA with Python: Key Churn Indicators

import pandas as pd
import [Link] as plt
import seaborn as sns

# Load joined data from SQL

df = pd.read_sql(query, connection)

# ■■ Key churn indicators ■■

print("Overall churn rate:", df['churned'].mean().round(3))
print("\nChurn by contract type:")
print([Link]('contract_type')['churned'].mean().sort_values(ascending=False))

# Visualisation: 4 key indicators

fig, axes = [Link](2, 2, figsize=(13, 9))

# 1. Churn rate by contract type

 churn_by_contract = [Link]('contract_type')['churned'].mean() * 100
axes[0,0].bar(churn_by_contract.index, churn_by_contract.values)
axes[0,0].set_title('Churn Rate by Contract Type')

# 2. Monthly charges distribution

axes[0,1].hist(df[df['churned']==0]['monthly_charges'], alpha=0.6, label='Retained')
axes[0,1].hist(df[df['churned']==1]['monthly_charges'], alpha=0.6, label='Churned')
axes[0,1].legend(); axes[0,1].set_title('Monthly Charges: Churned vs Retained')

# 3. Tenure distribution
axes[1,0].hist(df[df['churned']==0]['tenure_months'], alpha=0.6, label='Retained')
axes[1,0].hist(df[df['churned']==1]['tenure_months'], alpha=0.6, label='Churned')
axes[1,0].legend(); axes[1,0].set_title('Tenure: Churned vs Retained')

# 4. Complaints vs churn rate

comp_churn = [Link]('num_complaints')['churned'].mean() * 100
axes[1,1].bar(comp_churn.[Link](str), comp_churn.values)
axes[1,1].set_title('Churn Rate by Number of Complaints')
plt.tight_layout()
[Link]()

Figure 4.1 — Churn EDA dashboard: contract type, charges, tenure, and complaints

Key findings from EDA: Month-to-Month contract customers churn at 46% vs only 14–15% for annual contracts
— contract type is the strongest churn predictor. Churned customers tend to have higher monthly charges
(suggesting price sensitivity). Low-tenure customers (<12 months) churn at a disproportionately high rate — early
engagement programs are critical. Customers with 2+ complaints have dramatically higher churn rates, making
complaint resolution a high-priority retention lever.
 Figure 4.2 — Correlation heatmap showing relationships between numeric features and churn

The correlation heatmap confirms: num_complaints has the highest positive correlation with churn,
tenure_months has a moderate negative correlation (longer-tenured customers are less likely to churn), and
monthly_charges shows a slight positive correlation with churn. These findings directly inform feature selection
for the predictive model.

Data Science Assignment 02 — Complete Solutions with Code Output