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}")

Lab environment ready.
Database will be created at: f:\#SQL-PYTHON-Training\mfg-python-sql-training\Phase-02-Training\data\factory.db

# ── Exercise 1 -- SOLUTION ────────────────────────────────────────────────────

# Step 1: Connect to factory.db
conn = sqlite3.connect(DB_PATH)

# Step 2: Create the production_log table
conn.execute("DROP TABLE IF EXISTS production_log;")
conn.execute("""
    CREATE TABLE IF NOT EXISTS production_log (
        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
    );
""")
conn.commit()

# Step 3: Insert 10 records using executemany and a list of tuples
records = [
    ("M-101", "2026-07-01", 480, 2),
    ("M-101", "2026-07-02", 510, 1),
    ("M-101", "2026-07-03", 495, 4),
    ("M-102", "2026-07-01", 520, 3),
    ("M-102", "2026-07-02", 505, 0),
    ("M-102", "2026-07-03", 488, 6),
    ("M-103", "2026-07-01", 475, 8),
    ("M-103", "2026-07-02", 460, 10),
    ("M-103", "2026-07-03", 490, 5),
    ("M-101", "2026-07-04", 502, 2),
]
conn.execute("DELETE FROM production_log;")
conn.executemany(
    "INSERT INTO production_log (machine_id, log_date, units_produced, defects) VALUES (?,?,?,?)",
    records
)
conn.commit()
print(f"Inserted {len(records)} records.")

# Step 4: Query total units per machine
q4 = pd.read_sql_query("""
    SELECT machine_id,
           COUNT(*)           AS days_recorded,
           SUM(units_produced) AS total_units,
           SUM(defects)        AS total_defects
    FROM   production_log
    GROUP  BY machine_id
    ORDER  BY total_units DESC;
""", conn)
print("\nQuery 4 -- Total units per machine:")
display(q4)

# Step 5: Query records where defects > 3
q5 = pd.read_sql_query("""
    SELECT * FROM production_log WHERE defects > 3 ORDER BY defects DESC;
""", conn)
print("\nQuery 5 -- High-defect rows (defects > 3):")
display(q5)

conn.close()

Inserted 10 records.

Query 4 -- Total units per machine:

Query 5 -- High-defect rows (defects > 3):

# ── Exercise 2 -- SOLUTION ────────────────────────────────────────────────────

# Step 1: Load production_log into a DataFrame
conn = sqlite3.connect(DB_PATH)
df2 = pd.read_sql_query("SELECT * FROM production_log ORDER BY log_date;", conn)
conn.close()
print(f"Loaded {len(df2)} rows. Columns: {list(df2.columns)}")

# Step 2: Convert log_date to datetime
df2["log_date"] = pd.to_datetime(df2["log_date"])
print(f"log_date dtype: {df2['log_date'].dtype}")

# Step 3: Compute yield_pct
df2["yield_pct"] = (
    (df2["units_produced"] - df2["defects"]) / df2["units_produced"] * 100
).round(2)
display(df2)

# Step 4: Matplotlib line chart
fig, ax = plt.subplots()
ax.plot(df2["log_date"], df2["units_produced"],
        color="steelblue", linewidth=1.8, marker="o", label="Units Produced")
ax.axhline(500, color="red", linestyle="--", linewidth=1.2, label="Target 500")
ax.xaxis.set_major_formatter(mdates.DateFormatter("%b %d"))
ax.xaxis.set_major_locator(mdates.DayLocator(interval=1))
plt.setp(ax.get_xticklabels(), rotation=30, ha="right")
ax.set_title("Daily Units Produced", fontsize=12, fontweight="bold")
ax.set_xlabel("Date")
ax.set_ylabel("Units Produced")
ax.legend(fontsize=9)
plt.tight_layout()
plt.show()

# Step 5: Seaborn histogram of yield_pct
fig, ax = plt.subplots()
sns.histplot(data=df2, x="yield_pct", bins=10, kde=True, color="teal", ax=ax)
ax.set_title("Yield Rate Distribution", fontsize=12, fontweight="bold")
ax.set_xlabel("Yield %")
ax.set_ylabel("Frequency")
plt.tight_layout()
plt.show()

Loaded 10 rows. Columns: ['log_id', 'machine_id', 'log_date', 'units_produced', 'defects']
log_date dtype: datetime64[ns]

# ── Exercise 3 -- SOLUTION ────────────────────────────────────────────────────

# Step 1: units_array
units_array = np.random.normal(500, 18, size=30).round(0).astype(int)
print(f"units_array shape: {units_array.shape}, dtype: {units_array.dtype}")

# Step 2: defects_array
defects_array = np.random.poisson(lam=4, size=30)

# Step 3: yield_array
yield_array = ((units_array - defects_array) / units_array * 100).round(2)

# Step 4: NumPy-only statistics
print(f"\nMean yield      : {np.mean(yield_array):.2f}%")
print(f"Min yield       : {np.min(yield_array):.2f}%")
print(f"Max yield       : {np.max(yield_array):.2f}%")
print(f"StdDev yield    : {np.std(yield_array):.2f}%")
print(f"Days below 99%  : {(yield_array < 99).sum()}")
print(f"10th percentile : {np.percentile(yield_array, 10):.2f}%")

# Step 5: Reshape and row means
matrix = units_array.reshape(5, 6)
print(f"\nReshaped matrix shape: {matrix.shape}")
print("Row means:", np.mean(matrix, axis=1))

units_array shape: (30,), dtype: int64

Mean yield      : 99.24%
Min yield       : 98.58%
Max yield       : 99.80%
StdDev yield    : 0.35%
Days below 99%  : 9
10th percentile : 98.78%

Reshaped matrix shape: (5, 6)
Row means: [506.33333333 504.66666667 486.16666667 492.66666667 493.66666667]

# ── 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 dataset ready: (80, 6)

# ── Exercise 4 -- SOLUTION ────────────────────────────────────────────────────

fig, axes = plt.subplots(1, 3, figsize=(18, 5))

# Task 1: Box plot of yield_pct by machine_id
sns.boxplot(data=ex4_df, x="machine_id", y="yield_pct",
            hue="machine_id", palette="tab10", legend=False, linewidth=1.2, ax=axes[0])
axes[0].axhline(99.0, color="red", linestyle="--", linewidth=1.2, label="Target 99%")
axes[0].set_title("Yield Rate by Machine", fontsize=12, fontweight="bold")
axes[0].set_xlabel("Machine")
axes[0].set_ylabel("Yield Rate (%)")
axes[0].legend(fontsize=9)

# Task 2: Violin plot of defects by shift
sns.violinplot(data=ex4_df, x="shift", y="defects",
               hue="shift", palette="Set2", legend=False, inner="quartile", ax=axes[1])
axes[1].set_title("Defects by Shift", fontsize=12, fontweight="bold")
axes[1].set_xlabel("Shift")
axes[1].set_ylabel("Defects")

# Task 3: Correlation heatmap
numeric_cols = ["units_produced", "defects", "temperature_c", "yield_pct"]
corr = ex4_df[numeric_cols].corr()
sns.heatmap(corr, annot=True, fmt=".2f", cmap="coolwarm",
            vmin=-1, vmax=1, linewidths=0.5, ax=axes[2])
axes[2].set_title("Correlation Matrix", fontsize=12, fontweight="bold")

plt.suptitle("Exercise 4 -- Statistical Visualization", fontsize=13, y=1.02)
plt.tight_layout()
plt.show()

# Task 4: Machine with lowest median yield
lowest_machine = ex4_df.groupby("machine_id")["yield_pct"].median().idxmin()
lowest_val = ex4_df.groupby("machine_id")["yield_pct"].median().min()
print(f"Machine with lowest median yield: {lowest_machine} ({lowest_val:.2f}%)")

Machine with lowest median yield: M-103 (97.49%)

# ── 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)}")

batch_quality.csv saved: f:\#SQL-PYTHON-Training\mfg-python-sql-training\Phase-02-Training\data\batch_quality.csv
Rows: 120, Columns: ['batch_id', 'product_code', 'batch_date', 'machine_id', 'units_produced', 'defects', 'temperature_c']

# ── Exercise 5 -- SOLUTION ────────────────────────────────────────────────────

# Step 2: Load and inspect
df5 = pd.read_csv(LAB_DIR / "batch_quality.csv")
df5["batch_date"] = pd.to_datetime(df5["batch_date"])
print(f"Shape: {df5.shape}")
print(f"Missing values:\n{df5.isnull().sum()}")
display(df5.head(4))

# Step 3: Clean
df5["temperature_c"] = df5["temperature_c"].fillna(df5["temperature_c"].median())
df5 = df5[df5["units_produced"] > 0].copy()
df5["yield_pct"] = (
    (df5["units_produced"] - df5["defects"]) / df5["units_produced"] * 100
).round(2)
print(f"\nAfter cleaning: {df5.shape}")
print(f"Null temperature_c: {df5['temperature_c'].isnull().sum()}")

# Step 4: Store in SQLite with df.to_sql()
conn5 = sqlite3.connect(DB_PATH)
df5.to_sql("batch_quality", conn5, if_exists="replace", index=False)
print("batch_quality table written to factory.db.")

# Step 5: Three SQL queries
q_a = pd.read_sql_query("""
    SELECT product_code,
           COUNT(*)                   AS total_batches,
           ROUND(AVG(yield_pct), 2)   AS avg_yield_pct,
           SUM(defects)               AS total_defects
    FROM   batch_quality
    GROUP  BY product_code
    ORDER  BY avg_yield_pct DESC;
""", conn5)
print("\nQuery A -- Batches and avg yield per product:")
display(q_a)

q_b = pd.read_sql_query("""
    SELECT batch_id, product_code, batch_date, machine_id, defects
    FROM   batch_quality
    ORDER  BY defects DESC
    LIMIT  5;
""", conn5)
print("\nQuery B -- Top 5 batches by defect count:")
display(q_b)

q_c = pd.read_sql_query("""
    SELECT strftime('%Y-%m', batch_date)  AS month,
           ROUND(AVG(defects), 2)         AS avg_defects,
           COUNT(*)                       AS batches
    FROM   batch_quality
    GROUP  BY strftime('%Y-%m', batch_date)
    ORDER  BY month;
""", conn5)
print("\nQuery C -- Monthly average defect rate:")
display(q_c)

conn5.close()

# Step 6: 2x2 Dashboard
fig, axes = plt.subplots(2, 2, figsize=(14, 9))
fig.suptitle("Batch Quality Analysis Dashboard", fontsize=14, fontweight="bold")

df5_sorted = df5.sort_values("batch_date")

# [0,0] Line chart: daily yield with 7-day rolling average
rolling7 = df5_sorted.set_index("batch_date")["yield_pct"].rolling(7).mean()
axes[0,0].plot(df5_sorted["batch_date"], df5_sorted["yield_pct"],
               color="steelblue", linewidth=0.8, alpha=0.5, label="Daily yield")
axes[0,0].plot(rolling7.index, rolling7.values,
               color="navy", linewidth=2.0, label="7-day rolling avg")
axes[0,0].xaxis.set_major_formatter(mdates.DateFormatter("%b"))
axes[0,0].xaxis.set_major_locator(mdates.MonthLocator())
plt.setp(axes[0,0].get_xticklabels(), rotation=20, ha="right")
axes[0,0].set_title("Daily Yield with 7-Day Rolling Avg", fontsize=11)
axes[0,0].set_ylabel("Yield %")
axes[0,0].legend(fontsize=8)

# [0,1] Box plot: yield by product_code
sns.boxplot(data=df5, x="product_code", y="yield_pct",
            hue="product_code", palette="tab10", legend=False, ax=axes[0,1])
axes[0,1].set_title("Yield by Product Code", fontsize=11)
axes[0,1].set_xlabel("Product Code")
axes[0,1].set_ylabel("Yield %")

# [1,0] Histogram: defect count distribution
axes[1,0].hist(df5["defects"], bins=15, color="teal", edgecolor="white", linewidth=0.6)
axes[1,0].set_title("Defect Count Distribution", fontsize=11)
axes[1,0].set_xlabel("Defects")
axes[1,0].set_ylabel("Frequency")

# [1,1] Heatmap: correlation matrix
num_cols = ["units_produced", "defects", "temperature_c", "yield_pct"]
corr5 = df5[num_cols].corr()
sns.heatmap(corr5, annot=True, fmt=".2f", cmap="coolwarm",
            vmin=-1, vmax=1, linewidths=0.5, ax=axes[1,1])
axes[1,1].set_title("Correlation Matrix", fontsize=11)

plt.tight_layout()
plt.savefig("ex5_dashboard.png", dpi=150, bbox_inches="tight")
plt.show()
print("Dashboard saved.")

Shape: (120, 7)
Missing values:
batch_id          0
product_code      0
batch_date        0
machine_id        0
units_produced    0
defects           0
temperature_c     8
dtype: int64

After cleaning: (118, 8)
Null temperature_c: 0
batch_quality table written to factory.db.

Query A -- Batches and avg yield per product:

Query B -- Top 5 batches by defect count:

Query C -- Monthly average defect rate:

Dashboard saved.

	month	avg_defects	batches
0	2026-04	3.05	22
1	2026-05	3.05	20
2	2026-06	2.36	22
3	2026-07	2.70	23
4	2026-08	3.15	20
5	2026-09	3.55	11

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	[ ]


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

Python for Data Analytics: Phase 2 Training¶

Notebook 2.6: Lab Assignment -- Solution¶

Phase 2 -- Lab Assignment -- Solution¶

Setup -- Run This Cell First¶

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

Exercise 1 -- Verification Checklist¶

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

Exercise 2 -- Verification Checklist¶

Exercise 3 -- NumPy: Yield Statistics without Pandas¶

Exercise 3 -- Verification Checklist¶

Exercise 4 -- Seaborn: Statistical Visualization and Anomaly Detection¶

Exercise 4 -- Verification Checklist¶

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

Exercise 5 -- Verification Checklist¶

Lab Completion Summary¶

About This Material¶

	log_id	machine_id	log_date	units_produced	defects
0	8	M-103	2026-07-02	460	10
1	7	M-103	2026-07-01	475	8
2	6	M-102	2026-07-03	488	6
3	9	M-103	2026-07-03	490	5
4	3	M-101	2026-07-03	495	4

	log_id	machine_id	log_date	units_produced	defects	yield_pct
0	1	M-101	2026-07-01	480	2	99.58
1	4	M-102	2026-07-01	520	3	99.42
2	7	M-103	2026-07-01	475	8	98.32
3	2	M-101	2026-07-02	510	1	99.80
4	5	M-102	2026-07-02	505	0	100.00
5	8	M-103	2026-07-02	460	10	97.83
6	3	M-101	2026-07-03	495	4	99.19
7	6	M-102	2026-07-03	488	6	98.77
8	9	M-103	2026-07-03	490	5	98.98
9	10	M-101	2026-07-04	502	2	99.60

	machine_id	shift	units_produced	defects	temperature_c	yield_pct
0	M-102	Night	488	2	69.9	99.59
1	M-104	Afternoon	491	5	72.6	98.98
2	M-102	Morning	475	5	70.4	98.95
3	M-101	Morning	482	4	69.3	99.17

	batch_id	product_code	batch_date	machine_id	units_produced	defects	temperature_c
0	B1000	PROD-A	2026-04-01	M-101	222	1	70.2
1	B1001	PROD-B	2026-04-02	M-102	221	1	68.7
2	B1002	PROD-C	2026-04-03	M-102	244	1	NaN
3	B1003	PROD-A	2026-04-06	M-103	232	2	71.2

	batch_id	product_code	batch_date	machine_id	defects
0	B1047	PROD-B	2026-06-05 00:00:00	M-101	8
1	B1094	PROD-C	2026-08-11 00:00:00	M-101	7
2	B1105	PROD-B	2026-08-26 00:00:00	M-101	7
3	B1015	PROD-A	2026-04-22 00:00:00	M-102	6
4	B1024	PROD-B	2026-05-05 00:00:00	M-102	6

	product_code	total_batches	avg_yield_pct	total_defects
0	PROD-B	36	98.85	104
1	PROD-C	37	98.83	110
2	PROD-A	45	98.76	130