Python for Data Analytics: Phase 2 Training

Notebook 2.5: Lab Assignment

---

Author: Prakash Ukhalkar
Role: Assistant Professor (MCA) | Researcher in Data Science and Machine Learning

Training Date: Monday, 06th July 2026
Notebook Scope: Practical lab exercises covering all Phase 2 topics -- SQLite3, Pandas, NumPy, Matplotlib, and Seaborn.

---

Phase 2 -- Lab Assignment

Instructions

• Work through each exercise in sequence. Each builds on the previous one.
• Read the task description carefully before writing any code.
• Run each cell to verify your output matches the expected result described below it.
• Solutions are intentionally not provided -- attempt each exercise independently first.

Topics Covered

• Exercise 1: SQLite3 -- Create database, schema, insert records, run SQL queries
• Exercise 2: Pandas + Matplotlib -- Load SQL data and create production charts
• Exercise 3: NumPy -- Compute yield statistics without Pandas
• Exercise 4: Seaborn -- Statistical visualization and anomaly identification
• Exercise 5: End-to-end mini-project -- Batch quality analysis pipeline

Training Date: Monday, 06th July 2026
Estimated Duration: 90 minutes
Prerequisites: Notebooks 2.1-2.4 completed

---

Setup -- Run This Cell First

import sqlite3
from pathlib import Path

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import seaborn as sns

%matplotlib inline
sns.set_theme(style="whitegrid", palette="tab10")
plt.rcParams["figure.figsize"] = (10, 5)
plt.rcParams["figure.dpi"]     = 100
np.random.seed(42)

LAB_DIR = Path.cwd() / "data"
LAB_DIR.mkdir(parents=True, exist_ok=True)
DB_PATH = LAB_DIR / "factory.db"

print("Lab environment ready.")
print(f"Database will be created at: {DB_PATH}")

---

Exercise 1 -- SQLite3: Create, Insert, and Query

Task

1. Create a SQLite3 database called factory.db in the data/ folder.

2. Create a table called production_log with the following columns:

   | Column | Type | Constraints | |--------|------|-------------| | log_id | INTEGER | PRIMARY KEY AUTOINCREMENT | | machine_id | TEXT | NOT NULL | | log_date | DATE | NOT NULL | | units_produced | INTEGER | NOT NULL | | defects | INTEGER | DEFAULT 0 |

3. Insert exactly 10 records using parameterised INSERT statements covering machines M-101, M-102, and M-103.

4. Run a SQL query returning the total units produced per machine, ordered highest to lowest.

5. Run a second query returning only rows where defects > 3.

Expected Output: A totals-by-machine table and a filtered high-defect table.

# ── Exercise 1 -- YOUR CODE HERE ─────────────────────────────────────────────

# Step 1: Connect to factory.db


# Step 2: Create the production_log table


# Step 3: Insert 10 records using executemany and a list of tuples


# Step 4: Query total units per machine


# Step 5: Query records where defects > 3

Exercise 1 -- Verification Checklist

• factory.db appears in the data/ folder after running the cell.
• production_log table is listed when you query sqlite_master.
• Query 4 shows exactly 3 rows (one per machine) with a total_units column.
• Query 5 returns only rows where the defects value exceeds 3.

Common Mistakes to Avoid

• Forgetting conn.commit() after INSERT -- data is not saved to disk without it.
• Using string concatenation in SQL instead of ? placeholders -- always use parameterised queries.
• Calling conn.close() before running queries.

---

Exercise 2 -- Pandas and Matplotlib: Load and Visualize SQL Data

Task

1. Load the production_log table from factory.db into a Pandas DataFrame using pd.read_sql_query().
2. Convert the log_date column to datetime using pd.to_datetime().
3. Add a computed column yield_pct = (units_produced - defects) / units_produced * 100, rounded to 2 decimal places.
4. Plot a Matplotlib line chart of units_produced over log_date with a red dashed target line at 500, a formatted date axis, title, x-label, and y-label.
5. Plot a Seaborn histogram of yield_pct with a KDE overlay.

Hint: Use mdates.DateFormatter("%b %d") for the date axis and kde=True in sns.histplot().

# ── Exercise 2 -- YOUR CODE HERE ─────────────────────────────────────────────

# Step 1: Load production_log into a DataFrame


# Step 2: Convert log_date to datetime


# Step 3: Compute yield_pct


# Step 4: Matplotlib line chart


# Step 5: Seaborn histogram of yield_pct

Exercise 2 -- Verification Checklist

• DataFrame has 10 rows with log_date, units_produced, defects, yield_pct columns.
• log_date dtype is datetime64[ns], not object.
• Line chart has a red dashed target line at 500 and formatted date axis.
• Histogram shows a KDE curve overlaid on the bars.

---

Exercise 3 -- NumPy: Yield Statistics without Pandas

Task

1. Create units_array (30 values) from np.random.normal(mean=500, std=18, size=30). Round to integers.
2. Create defects_array (30 values) from np.random.poisson(lam=4, size=30).
3. Compute yield_array = (units_array - defects_array) / units_array * 100. Round to 2 decimal places.
4. Using only NumPy functions, compute and print:
   • Mean, minimum, and maximum yield
   • Standard deviation of yield
   • Number of days where yield fell below 99%
   • The 10th percentile yield
5. Reshape units_array into a (5, 6) matrix and print its shape and the mean of each row.

Hint: axis=1 means "average across columns within each row".

# ── Exercise 3 -- YOUR CODE HERE ─────────────────────────────────────────────

# Step 1: units_array


# Step 2: defects_array


# Step 3: yield_array


# Step 4: NumPy-only statistics


# Step 5: Reshape and row means

Exercise 3 -- Verification Checklist

• units_array has shape (30,) and dtype int.
• yield_array values are all between 95% and 100%.
• Statistics are computed using np.mean(), np.std(), etc. -- not Pandas methods.
• (yield_array < 99).sum() counts sub-99% days correctly.
• Reshaped matrix has shape (5, 6) and row means return 5 values.

---

Exercise 4 -- Seaborn: Statistical Visualization and Anomaly Detection

Task

Use the production dataset provided in the setup cell below (run it without modification).

1. Create a Seaborn box plot of yield_pct by machine_id. Add a horizontal reference line at 99%.
2. Create a Seaborn violin plot of defects by shift. Which shift has the most variable defect count?
3. Create a heatmap of the correlation matrix for numeric columns (units_produced, defects, temperature_c, yield_pct).
4. Using Pandas, identify and print the machine with the lowest median yield.

Hint for Task 4: groupby("machine_id")["yield_pct"].median().idxmin()

# ── Exercise 4 Setup -- Run without modification ──────────────────────────────
np.random.seed(99)
n_ex4 = 80
ex4_df = pd.DataFrame({
    "machine_id"    : np.random.choice(["M-101","M-102","M-103","M-104"], size=n_ex4),
    "shift"         : np.random.choice(["Morning","Afternoon","Night"],    size=n_ex4),
    "units_produced": np.random.normal(500, 22, size=n_ex4).round(0).astype(int),
    "defects"       : np.random.poisson(4, size=n_ex4),
    "temperature_c" : np.random.normal(72, 3, size=n_ex4).round(1),
})
m103_rows = ex4_df[ex4_df["machine_id"] == "M-103"].index[:10]
ex4_df.loc[m103_rows, "defects"] += np.random.randint(6, 12, size=len(m103_rows))
ex4_df["yield_pct"] = (
    (ex4_df["units_produced"] - ex4_df["defects"]) / ex4_df["units_produced"] * 100
).round(2)
print("Exercise 4 dataset ready:", ex4_df.shape)
display(ex4_df.head(4))

# ── Exercise 4 -- YOUR CODE HERE ─────────────────────────────────────────────

# Task 1: Box plot of yield_pct by machine_id


# Task 2: Violin plot of defects by shift


# Task 3: Correlation heatmap


# Task 4: Machine with lowest median yield

Exercise 4 -- Verification Checklist

• Box plot has one box per machine; M-103's box should sit visibly lower.
• Violin plot has one shape per shift; width shows concentration or spread.
• Heatmap shows values between -1 and 1 with annot=True; defects and yield_pct show strong negative correlation.
• The lowest-median machine printed matches what the box plot shows.

---

Exercise 5 -- End-to-End Mini-Project: Batch Quality Analysis Pipeline

Scenario: You receive a CSV of batch quality records from a production database export. Your job is to clean it, store it in SQLite, analyse it with SQL, and produce a one-page visual summary.

Steps:

1. Generate the dataset -- run the setup cell to create batch_quality.csv in data/.
2. Load and inspect -- read the CSV, check dtypes, missing values, and shape.
3. Clean -- fill missing temperature_c with the column median; drop rows where units_produced <= 0.
4. Store in SQLite -- write the cleaned DataFrame to table batch_quality in factory.db using df.to_sql().
5. SQL analysis -- run three queries:
   • Total batches and average yield per product_code
   • Top 5 batches by defect count
   • Monthly average defect rate using strftime
6. Visual summary -- create a 2x2 dashboard:
   • [0,0] Line chart: daily yield with 7-day rolling average
   • [0,1] Box plot: yield by product_code
   • [1,0] Histogram: defect count distribution
   • [1,1] Heatmap: correlation matrix

Key Hints

• df["temperature_c"].fillna(df["temperature_c"].median(), inplace=True) fills NaNs.
• df.to_sql("batch_quality", conn, if_exists="replace", index=False) writes the table.
• For rolling average: df.set_index("batch_date")["yield_pct"].rolling(7).mean().

# ── Exercise 5 Setup -- Generate batch_quality.csv ───────────────────────────
np.random.seed(7)
n_batch = 120
batch_dates = pd.date_range("2026-04-01", periods=n_batch, freq="B")

batch_df = pd.DataFrame({
    "batch_id"      : [f"B{1000 + i}" for i in range(n_batch)],
    "product_code"  : np.random.choice(["PROD-A","PROD-B","PROD-C"], size=n_batch),
    "batch_date"    : batch_dates,
    "machine_id"    : np.random.choice(["M-101","M-102","M-103"], size=n_batch),
    "units_produced": np.random.normal(250, 30, size=n_batch).round(0).astype(int),
    "defects"       : np.random.poisson(3, size=n_batch),
    "temperature_c" : np.random.normal(68, 3, size=n_batch).round(1),
})

# Inject 8 missing temperature values and 2 zero-production rows
batch_df.loc[np.random.choice(batch_df.index, 8, replace=False), "temperature_c"] = None
batch_df.loc[np.random.choice(batch_df.index, 2, replace=False), "units_produced"] = 0

csv_path = LAB_DIR / "batch_quality.csv"
batch_df.to_csv(csv_path, index=False)
print(f"batch_quality.csv saved: {csv_path}")
print(f"Rows: {len(batch_df)}, Columns: {list(batch_df.columns)}")

# ── Exercise 5 -- YOUR CODE HERE ─────────────────────────────────────────────

# Step 2: Load and inspect


# Step 3: Clean


# Step 4: Store in SQLite with df.to_sql()


# Step 5: Three SQL queries


# Step 6: 2x2 Dashboard

Exercise 5 -- Verification Checklist

• CSV loads correctly and batch_date is converted to datetime.
• After cleaning: no null values in temperature_c; no rows with units_produced <= 0.
• df.to_sql(...) creates the batch_quality table without errors.
• SQL query 1 shows 3 rows (one per product code) with average yield.
• SQL query 2 returns exactly 5 rows sorted by defects DESC.
• Dashboard has 4 panels with titles, axis labels, and a figure-level suptitle.

---

Lab Completion Summary

When all five exercises are complete, verify:

Exercise	Key Deliverable	Status
Exercise 1	factory.db, production_log table, 10 rows, 2 SQL queries	[ ]
Exercise 2	DataFrame with yield_pct, line chart, Seaborn histogram	[ ]
Exercise 3	NumPy statistics on 30-day arrays, reshaped (5, 6) matrix	[ ]
Exercise 4	Box plot, violin plot, heatmap, lowest-yield machine	[ ]
Exercise 5	batch_quality table, 3 SQL queries, 2x2 dashboard	[ ]

Submission: Share your completed .ipynb file with the trainer.
Ensure all cells have been executed and outputs are visible before submission.

---

---

About This Material

Author	Prakash Ukhalkar
Role	Assistant Professor (MCA) · Researcher in Data Science and Machine Learning
GitHub	@prakash-ukhalkar
Repository	mfg-python-sql-training
License	MIT

Provided for educational use in Python Data Analytics Phase 2 training programmes.
For questions, corrections, or contributions, open an issue or pull request on GitHub.

---

_{© 2026 Prakash Ukhalkar · Python for Data Analytics · MIT License}