Manufacturing Analytics with Python and Power BI

Introduction

30 Second Summary

Every manufacturing shift generates thousands of production records. Machines stamp, attach, and inspect components around the clock. But when a machine quietly starts degrading or a batch of material is contaminated, engineers only find out after hundreds of defective parts have already shipped.

In this project, you will step into the role of a Data Analytics Intern at NovaStor Technologies, a fictional manufacturer of precision hard disk drive components. You'll take 100,000 rows of messy production data, clean it, uncover hidden quality patterns, answer business questions with SQL, and deliver an interactive dashboard to manufacturing engineers.

What You'll Build

You open your final dashboard, filter to a specific machine, and watch the defect trend line climb month over month. You show the engineering team exactly which machine needs maintenance, which shift combination spikes defects, and which component batch was contaminated.

By the end of this project, you'll have:

• Cleaned data and Matplotlib visualizations that reveal hidden quality patterns: a machine degrading from 3% to 14% defect rate, a Monday night-shift spike, and a contamination event invisible in raw spreadsheets.
• SQL queries answering 5 manufacturing business questions, loaded into a SQLite database you built from your cleaned data, plus exported summaries ready for dashboarding.
• A Power BI dashboard with KPI cards, trend lines, and interactive slicers where engineers can drill into any machine, shift, or time period to find the root cause of quality issues.
• Secret Mission: Translate your data findings into a formal engineering recommendations report with quantified impact estimates that manufacturing engineers can act on.

Are there any prerequisites?

This project assumes basic Python and pandas experience (loading CSVs, filtering DataFrames), basic SQL knowledge (SELECT, GROUP BY, JOIN), and Power BI Desktop installed on your machine.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

You're stepping into the role of a Data Analytics Intern at NovaStor Technologies, a manufacturer of precision slider and HGA components for enterprise hard disk drives. Your job is to take messy production data, clean it, find hidden quality patterns, and deliver a dashboard that manufacturing engineers can act on.

Set Up the Project and Generate the Dataset

Before you can analyze manufacturing data, you need manufacturing data. Real companies would not hand an intern raw production databases on day one.

In this step, you will generate a realistic 100,000-row synthetic dataset that simulates what a Manufacturing Execution System would produce at NovaStor Technologies. You will embed deliberate data quality issues and hidden performance patterns, giving you a controlled environment where you know exactly what insights to look for later.

In this step, get ready to:

• Create the project folder structure with subfolders for raw data, cleaned data, scripts, and reports.
• Verify that pandas, numpy, and matplotlib are installed correctly.
• Build and run a Python script that generates 100,000 production records with embedded quality issues and hidden patterns.

Create the project folder and verify tools

Your analytics project needs a clear folder structure to keep raw data, cleaned data, scripts, and output reports separated.

• Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
• Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.

• Move to your Desktop by running this command:

cd ~/Desktop

• Create the project folder with all subfolders by running these commands:

mkdir -p novastor-analytics/data/raw novastor-analytics/data/cleaned novastor-analytics/scripts novastor-analytics/reports
cd novastor-analytics

• Confirm the folders were created by running:

ls

You should see four items listed: data, reports, and scripts.

• Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
• Type VS Code and press Enter to open it.
• In VS Code, click File in the top menu bar.
• Click Open Folder.
• Select the novastor-analytics folder on your Desktop, then click Open.

Now verify your Python packages are available.

• Install the required packages by running this in your terminal:

pip install pandas numpy matplotlib

What does this command do?

This installs three packages: pandas for tabular data manipulation, numpy for numerical operations, and matplotlib for creating charts. If they are already installed, pip will confirm they are satisfied.

• Verify the packages import correctly by running:

python -c "import pandas; import numpy; import matplotlib; print('Ready')"

You should see Ready printed to your terminal.

Seeing ModuleNotFoundError?

Make sure you ran the pip install command first. If you have multiple Python versions, try pip3 install pandas numpy matplotlib instead.

help me fix a ModuleNotFoundError when importing pandas, numpy, or matplotlib

Build the dataset generation script

The generation script defines everything about NovaStor's manufacturing operation: which production lines exist, which machines run on each line, what components are produced, and what defect types can occur. You will build this script piece by piece.

• In the VS Code Explorer sidebar, right-click the scripts folder and select New File.
• Name the file generate_dataset.py and press Enter.
• Start building the script by pasting this configuration code into the empty file:

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import os

np.random.seed(42)

# === Configuration ===
NUM_RECORDS = 100000
START_DATE = datetime(2024, 1, 1)
END_DATE = datetime(2024, 6, 30)
OUTPUT_DIR = "data/raw"

os.makedirs(OUTPUT_DIR, exist_ok=True)

# === Define NovaStor Manufacturing Context ===

# Production Lines
production_lines = ["Slider Processing", "HGA Assembly", "Final Inspection"]

What does this code do?

This sets the foundation: 100,000 records spanning January to June 2024, saved to data/raw/. The random seed (42) ensures you get the same dataset every time you run the script.

NovaStor has 3 production lines: Slider Processing (precision grinding), HGA Assembly (attaching sliders to suspensions), and Final Inspection.

• Add the machine definitions directly below the previous code:

# Machines (10 total across 3 lines)
machines_data = {
    "machine_id": ["LAP-01", "LAP-02", "LAP-03", "ACAM-01", "ACAM-02", "ACAM-03",
                   "DISP-01", "BOND-01", "INSP-01", "INSP-02"],
    "machine_type": ["Lapping Machine", "Lapping Machine", "Lapping Machine",
                     "ACAM (Slider Attach)", "ACAM (Slider Attach)", "ACAM (Slider Attach)",
                     "Adhesive Dispenser", "Wire Bonder", "Inspection System", "Inspection System"],
    "production_line": ["Slider Processing", "Slider Processing", "Slider Processing",
                        "HGA Assembly", "HGA Assembly", "HGA Assembly",
                        "HGA Assembly", "HGA Assembly", "Final Inspection", "Final Inspection"],
    "install_year": [2019, 2020, 2021, 2020, 2021, 2018, 2022, 2020, 2021, 2023]
}
machines_df = pd.DataFrame(machines_data)

What does this code do?

This defines 10 machines across the 3 production lines. Notice ACAM-03 was installed in 2018, making it the oldest machine in the factory. That detail will matter later when you discover its performance degradation.

• Confirm your code has no typos by running this syntax check in your terminal:

python -c "import ast; ast.parse(open('scripts/generate_dataset.py').read()); print('Syntax OK')"

You should see Syntax OK printed.

• Add the component and defect type definitions below the machines code:

# Components
components = ["NV-Slider-A", "NV-Slider-B", "NV-HGA-100", "NV-HGA-200"]
component_target_ct = {"NV-Slider-A": 38, "NV-Slider-B": 42, "NV-HGA-100": 55, "NV-HGA-200": 62}

# Defect Types
defect_types_data = {
    "defect_code": ["SLD", "ADH", "CON", "ELO", "SJC", "SFD", "ALD", "VAC"],
    "defect_type": ["Slider Loss", "Adhesive Misalignment", "Contamination",
                    "Electrical Open", "Solder Joint Crack", "Surface Damage",
                    "Alignment Drift", "Vacuum Leak"],
    "category": ["Mechanical", "Process", "Material", "Electrical",
                 "Mechanical", "Surface", "Process", "Equipment"],
    "severity": ["Critical", "High", "High", "Critical", "High", "Medium", "Medium", "High"]
}
defect_types_df = pd.DataFrame(defect_types_data)

# Shifts
shifts_clean = ["Morning", "Afternoon", "Night"]

What does this code do?

NovaStor produces 4 component types, each with a target cycle time in seconds. The 8 defect types are specific to slider and HGA manufacturing. Slider Loss (SLD) occurs when the vacuum mount head fails to hold the slider during attachment.

• Add the record generation logic that creates dates, assigns shifts, machines, and base production values:

print("Generating 100,000 NovaStor production records...")

# === Generate Production Records ===
total_days = (END_DATE - START_DATE).days + 1
dates = [START_DATE + timedelta(days=np.random.randint(0, total_days)) for _ in range(NUM_RECORDS)]
dates.sort()

# Assign shifts with intentional inconsistency (data quality issue)
shift_options = ["Morning", "Afternoon", "Night", "night", "NIGHT", "Night Shift",
                 "morning", "Morning ", "Afternoon", "Night"]
shift_weights = [0.28, 0.25, 0.20, 0.06, 0.03, 0.02, 0.03, 0.02, 0.06, 0.05]
shifts = np.random.choice(shift_options, size=NUM_RECORDS, p=shift_weights)

# Assign machines and components
machine_ids = np.random.choice(machines_df["machine_id"].values, size=NUM_RECORDS)
component_ids = np.random.choice(components, size=NUM_RECORDS)

# Derive production line from machine
line_lookup = dict(zip(machines_df["machine_id"], machines_df["production_line"]))
prod_lines = [line_lookup[m] for m in machine_ids]

# Generate base production values
units_produced = np.random.randint(80, 200, size=NUM_RECORDS)
base_defect_rate = np.full(NUM_RECORDS, 0.04)

What does this code do?

This generates dates spread across January to June 2024, then randomly assigns shifts, machines, and components to each record. Notice the shift_options list includes intentional inconsistencies ("night", "NIGHT", "Night Shift") that mimic sloppy manual operator entries.

The base defect rate starts at 4% for all records. The hidden patterns in the next section will selectively increase this rate for specific machines, shifts, and components.

• Save generate_dataset.py and confirm there are no syntax errors by running:

python -c "import ast; ast.parse(open('scripts/generate_dataset.py').read()); print('Syntax OK')"

You should see Syntax OK again.

Seeing a SyntaxError?

Check for missing commas between list items or unmatched quotes in the string arrays. The error message will include a line number pointing to where Python got confused.

help me fix a SyntaxError in my generate_dataset.py script

Embed hidden patterns and data quality issues

Real manufacturing data contains patterns that are not obvious from summary statistics alone. You will embed four hidden patterns that simulate real equipment and process problems, then inject data quality issues that mimic sensor dropouts and manual entry errors.

• Add the hidden pattern logic below the base production values:

# === Hidden Pattern 1: ACAM-03 Degradation ===
for i in range(NUM_RECORDS):
    if machine_ids[i] == "ACAM-03":
        month = dates[i].month
        base_defect_rate[i] = 0.03 + (month - 1) * 0.022  # 3% to ~14%

# === Hidden Pattern 2: Night Shift Monday Effect ===
for i in range(NUM_RECORDS):
    shift_norm = shifts[i].strip().lower()
    if shift_norm in ["night", "night shift"] and dates[i].weekday() == 0:
        base_defect_rate[i] *= 1.45

# === Hidden Pattern 3: NV-Slider-A March Contamination ===
for i in range(NUM_RECORDS):
    if component_ids[i] == "NV-Slider-A" and dates[i].month == 3:
        if np.random.random() < 0.12:
            base_defect_rate[i] = 0.22

# === Hidden Pattern 4: Cycle Time Creep on Old Lapping Machines ===
# LAP-01 (2019) gets slower over time
for i in range(NUM_RECORDS):
    if machine_ids[i] == "LAP-01":
        month = dates[i].month

What do these patterns simulate?

Pattern 1: ACAM-03's defect rate climbs from 3% in January to 14% by June, simulating a mount head wearing down over time.

Pattern 2: Night shift on Mondays gets a 45% defect rate increase, simulating operator fatigue after weekend breaks. Pattern 3: NV-Slider-A in March gets a 22% defect spike, simulating a contaminated material batch. Pattern 4: LAP-01 captures the month for cycle time creep applied below.

• Add the calculation logic for defective units, cycle times, and downtime:

# Calculate defectives
units_defective = np.array([
    max(0, int(units_produced[i] * min(base_defect_rate[i], 0.4) + np.random.normal(0, 1)))
    for i in range(NUM_RECORDS)
])
units_passed = units_produced - units_defective
units_passed = np.maximum(units_passed, 0)

# Generate cycle times
cycle_times = np.array([
    component_target_ct[component_ids[i]] + np.random.normal(0, 4)
    for i in range(NUM_RECORDS)
])
# Apply LAP-01 aging pattern
for i in range(NUM_RECORDS):
    if machine_ids[i] == "LAP-01":
        cycle_times[i] += dates[i].month * 1.5  # +1.5 sec/month

# Generate downtime
downtime = np.random.exponential(4, size=NUM_RECORDS)
downtime = np.where(np.random.random(NUM_RECORDS) > 0.25, 0, downtime)
# ACAM-03 has more downtime events
for i in range(NUM_RECORDS):
    if machine_ids[i] == "ACAM-03" and dates[i].month >= 4:
        if np.random.random() < 0.08:
            downtime[i] = np.random.uniform(15, 45)

What does this code do?

This converts the defect rates into actual unit counts, generates cycle times with noise around each component's target, and creates random downtime events. LAP-01 gets 1.5 seconds slower each month (aging equipment). ACAM-03 starts getting longer downtime events from April onward.

• Save the file and run a syntax check:

python -c "import ast; ast.parse(open('scripts/generate_dataset.py').read()); print('Syntax OK')"

You should see Syntax OK.

• Add the defect code assignment logic that distributes defect types differently per machine type:

# Assign defect types (only when defects exist)
defect_codes = []
for i in range(NUM_RECORDS):
    if units_defective[i] > 0:
        if machine_ids[i].startswith("ACAM"):
            weights = [0.35, 0.15, 0.10, 0.10, 0.05, 0.05, 0.10, 0.10]
        elif machine_ids[i].startswith("LAP"):
            weights = [0.05, 0.05, 0.30, 0.05, 0.05, 0.35, 0.10, 0.05]
        else:
            weights = [0.10, 0.15, 0.15, 0.20, 0.15, 0.10, 0.10, 0.05]
        defect_codes.append(np.random.choice(defect_types_df["defect_code"].values, p=weights))
    else:
        defect_codes.append(None)

What does this code do?

ACAM machines have a 35% chance of producing Slider Loss (SLD) defects, reflecting the vacuum mount head failure mode. Lapping machines favor Contamination and Surface Damage. This makes the defect-machine correlation discoverable through analysis.

• Add the data quality issues that simulate real-world messiness:

# === Inject Data Quality Issues ===

# Issue 1: ~50 duplicate production IDs
production_ids = [f"PRD-{str(i+1).zfill(6)}" for i in range(NUM_RECORDS)]
dup_indices = np.random.choice(range(500, NUM_RECORDS), size=50, replace=False)
for idx in dup_indices:
    production_ids[idx] = production_ids[np.random.randint(0, 500)]

# Issue 2: ~5% missing cycle times
mask_ct = np.random.random(NUM_RECORDS) < 0.05
cycle_times[mask_ct] = np.nan

# Issue 3: 25 negative downtime records
neg_idx = np.random.choice(NUM_RECORDS, size=25, replace=False)
downtime[neg_idx] = -np.random.uniform(1, 10, size=25)

# Issue 4: 12 future dates
future_idx = np.random.choice(NUM_RECORDS, size=12, replace=False)
for idx in future_idx:
    dates[idx] = datetime(2024, 7, np.random.randint(1, 20))

# Issue 5: 6 extreme cycle time outliers
outlier_idx = np.random.choice(range(NUM_RECORDS), size=6, replace=False)
for idx in outlier_idx:
    if not np.isnan(cycle_times[idx]):
        cycle_times[idx] = cycle_times[idx] * 10

What does this code do?

This injects 5 categories of data quality issues that mimic real Manufacturing Execution System problems: duplicate IDs from system glitches, missing sensor readings, impossible negative values, out-of-range dates, and extreme outliers from miscalibrated sensors.

You will discover and fix each of these in Step 2 during data cleaning.

• Save the file and verify syntax is still clean:

python -c "import ast; ast.parse(open('scripts/generate_dataset.py').read()); print('Syntax OK')"

You should see Syntax OK.

• Add the final section that builds the DataFrame, saves all CSV files, and prints a summary:

# === Build and Save DataFrames ===
production = pd.DataFrame({
    "production_id": production_ids,
    "date": [d.strftime("%Y-%m-%d") for d in dates],
    "production_line": prod_lines,
    "machine_id": machine_ids,
    "component": component_ids,
    "shift": shifts,
    "units_produced": units_produced,
    "units_passed": units_passed,
    "units_defective": units_defective,
    "defect_type": defect_codes,
    "cycle_time_seconds": np.round(cycle_times, 2),
    "downtime_minutes": np.round(downtime, 2)
})

production.to_csv(f"{OUTPUT_DIR}/production.csv", index=False)
machines_df.to_csv(f"{OUTPUT_DIR}/machines.csv", index=False)
defect_types_df.to_csv(f"{OUTPUT_DIR}/defect_types.csv", index=False)

What does this code do?

This assembles all the generated arrays into a single pandas DataFrame with 12 columns, then saves three CSV files: the main production data (100,000 rows), the machine reference table (10 rows), and the defect type reference table (8 rows).

• Add the summary output that confirms what was generated:

# === Summary ===
print("\n=== NovaStor Technologies Dataset Generated ===")
print(f"Production records: {len(production):,}")
print(f"Machines: {len(machines_df)}")
print(f"Components: {len(components)}")
print(f"Defect types: {len(defect_types_df)}")
print(f"Date range: {production['date'].min()} to {production['date'].max()}")
print(f"\nFiles saved to {OUTPUT_DIR}/:")
print(f"  - production.csv ({len(production):,} rows)")
print(f"  - machines.csv ({len(machines_df)} rows)")
print(f"  - defect_types.csv ({len(defect_types_df)} rows)")
print(f"\nData quality issues embedded:")
print(f"  - Duplicate production IDs: ~50")
print(f"  - Inconsistent shift naming: mixed case/format")
print(f"  - Missing cycle times: ~{mask_ct.sum()}")
print(f"  - Negative downtime values: 25")
print(f"  - Future dates (July 2024): 12")
print(f"  - Extreme cycle time outliers: 6")
print(f"\nHidden patterns embedded:")
print(f"  - ACAM-03 degradation: defect rate 3% -> 14% over 6 months")
print(f"  - Night shift + Monday: +45% defect rate")
print(f"  - NV-Slider-A March contamination: 22% defect rate spike")
print(f"  - LAP-01 cycle time creep: +1.5 sec/month aging")

Before you run the full script, take a moment to predict what you expect to see. How many records should the summary report? What date range should appear?

• Save generate_dataset.py and run the complete script:

python scripts/generate_dataset.py

You should see output starting with "Generating 100,000 NovaStor production records..." followed by a full summary showing 100,000 production records, 10 machines, 4 components, 8 defect types, and a date range from 2024-01-01 to 2024-07-something (the July dates are intentional quality issues).

• Confirm the CSV files were created by running:

ls data/raw/

You should see three files: defect_types.csv, machines.csv, and production.csv.

Seeing a NameError or FileNotFoundError?

If you see a NameError, a variable from an earlier chunk is missing. Make sure all code chunks are pasted in order without gaps between them.

If you see a FileNotFoundError, make sure you are running the command from the novastor-analytics folder (run pwd to check).

help me fix an error when running my generate_dataset.py script

✔️ Awesome, I've got everything!

Great. Double check you have saved scripts/generate_dataset.py and that all three CSV files exist in data/raw/.

ⓧ I'd like to double check the full code

Your scripts/generate_dataset.py file should contain these sections in order, with no gaps between them:

• Imports and configuration (imports, seed, constants, makedirs, production_lines).
• Machine definitions (machines_data dictionary, machines_df).
• Components and defect types (components list, component_target_ct, defect_types_data, defect_types_df, shifts_clean).
• Record generation (print statement, dates, shift_options/weights, machine/component assignment, line_lookup, units_produced, base_defect_rate).
• Hidden patterns (4 for-loops for ACAM-03, Night Monday, Slider-A March, LAP-01).
• Calculations (units_defective, units_passed, cycle_times with LAP-01 aging, downtime with ACAM-03 events).
• Defect codes (defect_codes list with machine-specific weight distributions).
• Data quality issues (duplicate IDs, missing cycle times, negative downtime, future dates, outliers).
• Build and save (DataFrame creation, 3 CSV saves, full summary print).

Scroll up and confirm each section is present. If anything is missing, paste the relevant chunk from the instructions above.

Your synthetic manufacturing dataset is ready. Next up, you will load this messy data into pandas and clean it, discovering and fixing each embedded quality issue like a real analyst inheriting production data.

Clean the Production Data

Your generated dataset has the same quality problems you would find pulling data from a real MES (Manufacturing Execution System). Sensor dropouts create missing values. Manual operator entries produce inconsistencies. System glitches generate invalid records.

Analysis on dirty data produces wrong conclusions. In this step, you will write a cleaning script that audits the raw data, then fixes each issue category so your downstream analysis starts from a trustworthy foundation.

In this step, get ready to:

• Load and audit the raw production data for quality issues.
• Apply cleaning transformations for duplicates, naming, invalid dates, negatives, missing values, and outliers.
• Save the cleaned dataset and review the before/after comparison.

Load and audit the raw data

Before fixing anything, you need to see exactly what is wrong. A data quality audit counts each problem type so you know the scope of the mess.

• Create a new file called clean_data.py inside your scripts folder.
• Add the imports, loading, and audit logic by copying this code into the file:

import pandas as pd
import numpy as np
import os

RAW_DIR = "data/raw"
CLEAN_DIR = "data/cleaned"
os.makedirs(CLEAN_DIR, exist_ok=True)

# === 1. Load Raw Data ===
print("=== NovaStor Data Cleaning Report ===\n")
production = pd.read_csv(f"{RAW_DIR}/production.csv")
print(f"Loaded {len(production):,} records from production.csv\n")

# === 2. Data Quality Audit ===
print("--- Data Quality Audit ---")
print(f"Total rows: {len(production):,}")
print(f"\nMissing values:")
missing = production.isnull().sum()
for col in missing[missing > 0].index:
    print(f"  {col}: {missing[col]} ({missing[col]/len(production)*100:.1f}%)")

dup_count = production["production_id"].duplicated().sum()
print(f"\nDuplicate production_ids: {dup_count}")
print(f"Unique shift values: {sorted(production['shift'].unique())}")

production["date"] = pd.to_datetime(production["date"])
print(f"Date range: {production['date'].min().date()} to {production['date'].max().date()}")
future = (production["date"] > "2024-06-30").sum()
print(f"Records with future dates: {future}")

neg_dt = (production["downtime_minutes"] < 0).sum()
print(f"Negative downtime values: {neg_dt}")

What does this code do?

The audit section checks five specific problem categories in the data before any cleaning happens.

• isnull().sum() counts missing values in every column.
• duplicated() flags rows with repeated production IDs.
• The shift unique-values check reveals inconsistent naming like "night", "NIGHT", and "Night Shift" that should all map to one value.
• The date range check catches records outside January to June 2024.

• Save scripts/clean_data.py.
• Run the script to see the audit output by running this command in your terminal:

python scripts/clean_data.py

You should see a report showing 100,000 total rows, missing values in cycle_time_seconds and defect_type, duplicate IDs, a messy list of shift values, a date range extending into July 2024, and negative downtime counts.

Seeing a FileNotFoundError?

• Make sure you are running the command from your novastor-analytics folder, not from inside scripts/.
• Confirm that data/raw/production.csv exists by running ls data/raw/.

Help me debug why my clean_data.py can't find the CSV file.

Apply cleaning transformations

Now that you can see the problems, it is time to fix them. Each cleaning step targets one issue category and prints what it did so the report is self-documenting.

• In scripts/clean_data.py, add the first two cleaning steps below your audit code:

# === 3. Clean the Data ===
print("\n--- Cleaning Steps ---")
log = {}

# 3a. Remove duplicates
before = len(production)
production = production.drop_duplicates(subset=["production_id"], keep="first")
log["Duplicates removed"] = before - len(production)
print(f"[1] Duplicates removed: {log['Duplicates removed']}")

# 3b. Standardize shift names
def clean_shift(s):
    s = s.strip().lower()
    if s in ["morning"]:
        return "Morning"
    elif s in ["afternoon"]:
        return "Afternoon"
    elif s in ["night", "night shift"]:
        return "Night"
    return s.title()

production["shift"] = production["shift"].apply(clean_shift)
log["Shifts standardized"] = True
print(f"[2] Shifts standardized to: {sorted(production['shift'].unique())}")

What does this code do?

• drop_duplicates(subset=["production_id"], keep="first") removes rows where the same production ID appears more than once, keeping only the first occurrence.
• The clean_shift function normalizes all the messy shift entries ("night", "NIGHT", "Night Shift", "morning", etc.) into three consistent values: Morning, Afternoon, Night.
• The log dictionary tracks what was fixed so the report is auditable.

• Save the file and run it again to confirm the first two cleaning steps print correctly:

python scripts/clean_data.py

Below the audit section, you should now see the cleaning steps header, the duplicate count removed, and the standardized shift list showing only ['Afternoon', 'Morning', 'Night'].

• Now add cleaning steps 3 through 6 below the shift standardization code:

# 3c. Remove future dates
before = len(production)
production = production[production["date"] <= "2024-06-30"]
log["Future dates removed"] = before - len(production)
print(f"[3] Future date records removed: {log['Future dates removed']}")

# 3d. Fix negative downtime
neg_count = (production["downtime_minutes"] < 0).sum()
production.loc[production["downtime_minutes"] < 0, "downtime_minutes"] = 0
log["Negative downtime fixed"] = neg_count
print(f"[4] Negative downtime set to 0: {neg_count}")

# 3e. Fill missing cycle times with median by machine
missing_ct = production["cycle_time_seconds"].isnull().sum()
production["cycle_time_seconds"] = production.groupby("machine_id")["cycle_time_seconds"].transform(
    lambda x: x.fillna(x.median())
)
log["Missing cycle times filled"] = missing_ct
print(f"[5] Missing cycle_time filled (median by machine): {missing_ct}")

# 3f. Remove extreme cycle time outliers (>3 std per component)
before = len(production)
stats = production.groupby("component")["cycle_time_seconds"].agg(["mean", "std"]).reset_index()
stats.columns = ["component", "ct_mean", "ct_std"]
production = production.merge(stats, on="component", how="left")
production = production[
    abs(production["cycle_time_seconds"] - production["ct_mean"]) <= 3 * production["ct_std"]
]
production = production.drop(columns=["ct_mean", "ct_std"])
log["Cycle time outliers removed"] = before - len(production)
print(f"[6] Cycle time outliers removed: {log['Cycle time outliers removed']}")

What does this code do?

• Step 3 filters out records with dates beyond June 30, 2024 (the 12 July records from system glitches).
• Step 4 replaces negative downtime values with 0. Downtime cannot be negative in reality.
• Step 5 fills missing cycle times using the median for that specific machine. This is better than a global median because each machine type has a different normal speed.
• Step 6 removes extreme outliers (values beyond 3 standard deviations per component). These represent sensor glitches, not real production times.

Why fill by machine median instead of dropping rows?

Dropping 5,000 rows (5% of data) would bias your analysis by removing records that might cluster in specific machines or time periods. Filling with the per-machine median preserves the row while giving a reasonable estimate of what the cycle time likely was.

Save and verify the cleaned data

• Add the save and summary section at the bottom of scripts/clean_data.py:

# === 4. Save Cleaned Data ===
production.to_csv(f"{CLEAN_DIR}/production_cleaned.csv", index=False)
print(f"\n--- Summary ---")
print(f"Original records: {100000:,}")
print(f"Cleaned records: {len(production):,}")
print(f"Records removed: {100000 - len(production):,}")
print(f"\nSaved: {CLEAN_DIR}/production_cleaned.csv")

What does this code do?

This final section writes the cleaned DataFrame to data/cleaned/production_cleaned.csv and prints a before/after comparison so you can see exactly how many records were affected by the cleaning.

• Save scripts/clean_data.py.

Before you run the full script, what do you expect to see in the summary? Think about how many total records the cleaning steps might have removed.

• Run the complete cleaning script by running this command:

python scripts/clean_data.py

You should see the full report: the audit section listing all data quality issues, then six numbered cleaning steps with counts, and finally a summary showing approximately 100,000 original records reduced to roughly 99,900+ cleaned records saved to data/cleaned/production_cleaned.csv.

• Confirm the cleaned file exists by running:

ls data/cleaned/

You should see production_cleaned.csv listed in the output.

Seeing a KeyError or unexpected row count?

• If you see a KeyError for a column name, check that your column names in the cleaning code exactly match what the CSV contains (e.g., cycle_time_seconds not cycle_time).
• If the script says 0 duplicates removed, make sure you ran generate_dataset.py first to produce the raw data with embedded issues.
• If you get a merge error on step 6, confirm you kept the drop(columns=["ct_mean", "ct_std"]) line at the end of the outlier section.

Help me debug my data cleaning script.

✔️ Awesome, I've got everything!

Great. Double check you have saved scripts/clean_data.py and that data/cleaned/production_cleaned.csv exists.

ⓧ I'd like to double check the full code

Scroll up and confirm your file contains all these sections in order:

• Imports and config (RAW_DIR, CLEAN_DIR, os.makedirs).
• Section 1: Load raw data and print record count.
• Section 2: Data quality audit (missing values, duplicates, shift values, date range, negatives).
• Section 3: Six cleaning steps (3a through 3f) with printed progress for each.
• Section 4: Save to CSV and print summary with before/after counts.

Your data is clean and trustworthy. Next up, you will visualize the 100,000 records with matplotlib to uncover the hidden patterns that numbers alone cannot reveal.

Explore the Data with Matplotlib

Your cleaning script proved the data is now trustworthy. But staring at 100,000 rows in a spreadsheet will never reveal patterns.

Visualizing the data across time, machines, and shifts is how you spot anomalies that summary statistics alone would miss. In this step, you will build an exploratory data analysis script using Matplotlib to surface hidden manufacturing problems.

In this step, get ready to:

• Calculate and print summary KPIs for the entire production dataset.
• Create 4 matplotlib charts revealing a degrading machine, a shift-day defect hotspot, and defect type distribution.
• Document key anomalies discovered in the data.

Load the cleaned data and compute derived columns

Before you can chart anything, you need time-based columns (month, week, day of week) and a per-record defect rate. These derived columns power every visualization and comparison below.

• Create a new file called eda.py inside the scripts folder.
• Add the imports and data loading section by pasting this code:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import os

CLEAN_DIR = "data/cleaned"
REPORTS_DIR = "reports"
os.makedirs(REPORTS_DIR, exist_ok=True)

# Load cleaned production data
production = pd.read_csv(f"{CLEAN_DIR}/production_cleaned.csv")
production["date"] = pd.to_datetime(production["date"])

# Add derived time columns for grouping
production["month"] = production["date"].dt.to_period("M").astype(str)
production["week"] = production["date"].dt.isocalendar().week.astype(int)
production["day_of_week"] = production["date"].dt.day_name()

# Calculate per-record defect rate
production["defect_rate"] = production["units_defective"] / production["units_produced"]

What does this code do?

The script loads your cleaned CSV and creates four new columns. month and week let you group records by time period for trend charts.

day_of_week and defect_rate enable the shift-day heatmap and per-record quality comparisons.

• Below the loading section, add the KPI summary block:

# === Summary KPIs ===
print("=== NovaStor Technologies - EDA Summary ===\n")
print(f"Total Production: {production['units_produced'].sum():,} units")
print(f"Total Defective:  {production['units_defective'].sum():,} units")
print(f"Overall Yield:    {production['units_passed'].sum() / production['units_produced'].sum() * 100:.1f}%")
print(f"Overall Defect Rate: {production['units_defective'].sum() / production['units_produced'].sum() * 100:.2f}%")
print(f"Total Downtime:   {production['downtime_minutes'].sum() / 60:.1f} hours")
print(f"Avg Cycle Time:   {production['cycle_time_seconds'].mean():.1f} seconds")
print(f"\nTop 3 Defect Types:")
top_defects = production[production["defect_type"].notna()]["defect_type"].value_counts().head(3)
for defect, count in top_defects.items():
    print(f"  {defect}: {count:,} occurrences")

What does this code do?

This block calculates plant-wide manufacturing KPIs: total output, defect rate, yield rate, downtime hours, and average cycle time. These are the numbers a manufacturing engineer would check first thing every morning.

The top 3 defect types tell you where to focus quality improvement efforts.

• Save scripts/eda.py.
• Run the script to see your KPIs by running this command:

python scripts/eda.py

You should see a printed summary with total production in the millions of units, an overall yield rate above 90%, total downtime in hours, and the top 3 defect codes ranked by occurrence.

Seeing a FileNotFoundError?

Make sure you are running the command from the novastor-analytics folder and that data/cleaned/production_cleaned.csv exists from your cleaning step.

help me debug a FileNotFoundError in my EDA script

Create time-series and distribution charts

KPIs give you a snapshot, but charts reveal trends over time. You will create 4 charts that make hidden patterns jump off the screen.

• Below the KPI section in scripts/eda.py, add Chart 1 (weekly production volume trend):

# === Chart 1: Weekly Production Volume ===
fig, ax = plt.subplots(figsize=(12, 5))
weekly = production.groupby("week")["units_produced"].sum().reset_index()
ax.plot(weekly["week"], weekly["units_produced"], color="#2196F3", linewidth=2)
ax.fill_between(weekly["week"], weekly["units_produced"], alpha=0.1, color="#2196F3")
ax.set_xlabel("Week Number (2024)")
ax.set_ylabel("Total Units Produced")
ax.set_title("NovaStor Technologies - Weekly Production Volume")
ax.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig(f"{REPORTS_DIR}/01_weekly_production.png", dpi=150)
plt.close()
print("\nSaved: reports/01_weekly_production.png")

What does this chart show?

This line chart shows whether total production volume is stable, trending up, or trending down across the 6-month window. A sudden dip would indicate a line-down event. The fill_between adds a subtle area fill to make the trend easier to read.

• Below Chart 1, add Chart 2 (monthly defect rate by machine). This is the chart that exposes the degrading ACAM-03 machine:

# === Chart 2: Monthly Defect Rate by Machine ===
fig, ax = plt.subplots(figsize=(12, 6))
monthly_machine = production.groupby(["month", "machine_id"]).agg(
    total_defective=("units_defective", "sum"),
    total_produced=("units_produced", "sum")
).reset_index()
monthly_machine["defect_rate"] = monthly_machine["total_defective"] / monthly_machine["total_produced"]

for machine_id in production["machine_id"].unique():
    data = monthly_machine[monthly_machine["machine_id"] == machine_id]
    if machine_id == "ACAM-03":
        ax.plot(data["month"], data["defect_rate"] * 100, linewidth=3,
                color="red", marker="o", markersize=8, label="ACAM-03 (DEGRADING)")
    else:
        ax.plot(data["month"], data["defect_rate"] * 100, linewidth=1, alpha=0.4, color="gray")

ax.set_xlabel("Month")
ax.set_ylabel("Defect Rate (%)")
ax.set_title("NovaStor - Monthly Defect Rate by Machine\n(ACAM-03 shows clear degradation pattern)")
ax.legend(loc="upper left")
ax.grid(True, alpha=0.3)
plt.xticks(rotation=45)
plt.tight_layout()
plt.savefig(f"{REPORTS_DIR}/02_machine_defect_trends.png", dpi=150)
plt.close()
print("Saved: reports/02_machine_defect_trends.png")

What does this chart show?

Every machine gets a gray line showing its monthly defect rate. ACAM-03 is highlighted in red with markers. While all other machines hover steadily, ACAM-03's line climbs from about 3% in January to approximately 14% in June. That steady climb is classic equipment degradation.

• Save scripts/eda.py and run it again:

python scripts/eda.py

You should see the KPIs printed, followed by two new lines: Saved: reports/01_weekly_production.png and Saved: reports/02_machine_defect_trends.png.

• Open the reports folder in your file browser to confirm the two PNG files exist.
• Open 02_machine_defect_trends.png. You should see a single red line rising steeply from left to right while all gray lines stay flat.

Charts not saving?

Check that the reports folder exists. The os.makedirs(REPORTS_DIR, exist_ok=True) line at the top should create it automatically. If you see a permission error, check you are in the correct project folder.

help me debug matplotlib chart saving issues

• Now add Chart 3 below Chart 2. This heatmap reveals the shift-day defect hotspot:

# === Chart 3: Shift x Day Heatmap ===
fig, ax = plt.subplots(figsize=(10, 4))
day_order = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
shift_day = production.groupby(["shift", "day_of_week"])["defect_rate"].mean().reset_index()
pivot = shift_day.pivot(index="shift", columns="day_of_week", values="defect_rate")
pivot = pivot.reindex(columns=day_order)

im = ax.imshow(pivot.values * 100, cmap="YlOrRd", aspect="auto", vmin=3, vmax=12)
ax.set_xticks(range(7))
ax.set_xticklabels(day_order, rotation=45, ha="right")
ax.set_yticks(range(len(pivot.index)))
ax.set_yticklabels(pivot.index)
ax.set_title("NovaStor - Defect Rate (%) by Shift and Day of Week")
plt.colorbar(im, ax=ax, label="Defect Rate (%)")

for i in range(len(pivot.index)):
    for j in range(7):
        val = pivot.values[i, j]
        if not np.isnan(val):
            ax.text(j, i, f"{val*100:.1f}%", ha="center", va="center", fontsize=8,
                    color="white" if val > 0.08 else "black")

plt.tight_layout()
plt.savefig(f"{REPORTS_DIR}/03_shift_day_heatmap.png", dpi=150)
plt.close()
print("Saved: reports/03_shift_day_heatmap.png")

What does this chart show?

A heatmap lets you spot combinations that stand out. Each cell shows the average defect rate for one shift-day pair. The Night+Monday cell should glow much darker (higher defect rate) than the rest of the grid. That means something specific about Monday nights causes quality to drop.

• Below Chart 3, add Chart 4 (defect type distribution):

# === Chart 4: Defect Type Breakdown ===
fig, ax = plt.subplots(figsize=(10, 5))
defect_counts = production[production["defect_type"].notna()]["defect_type"].value_counts()
colors = plt.cm.Set2(np.linspace(0, 1, len(defect_counts)))
bars = ax.barh(defect_counts.index, defect_counts.values, color=colors)
ax.set_xlabel("Number of Occurrences")
ax.set_title("NovaStor - Defect Type Distribution")
for bar, val in zip(bars, defect_counts.values):
    ax.text(val + 50, bar.get_y() + bar.get_height()/2, f"{val:,}", va="center", fontsize=9)
plt.tight_layout()
plt.savefig(f"{REPORTS_DIR}/04_defect_types.png", dpi=150)
plt.close()
print("Saved: reports/04_defect_types.png")

What does this chart show?

This horizontal bar chart ranks defect types by how often they occur. Slider Loss (SLD) dominates because it is both a Critical-severity defect and the most common failure mode on ACAM machines. A manufacturing engineer seeing this chart immediately knows where to focus process improvement.

• Save scripts/eda.py and run the script again:

python scripts/eda.py

You should now see 4 saved-chart lines in the output. Confirm by listing the reports folder:

ls reports/

You should see 01_weekly_production.png, 02_machine_defect_trends.png, 03_shift_day_heatmap.png, and 04_defect_types.png.

Document initial findings

Charts show patterns visually, but you need to quantify what you found. This final section of the script computes the exact numbers behind the three anomalies so you can cite them in later analysis.

• Below Chart 4 in scripts/eda.py, add the key findings section:

# === Key Findings ===
print("\n=== Key Findings ===")
print(f"1. ACAM-03 defect rate: Jan={monthly_machine[(monthly_machine['machine_id']=='ACAM-03') & (monthly_machine['month']=='2024-01')]['defect_rate'].values[0]*100:.1f}% -> Jun={monthly_machine[(monthly_machine['machine_id']=='ACAM-03') & (monthly_machine['month']=='2024-06')]['defect_rate'].values[0]*100:.1f}%")
night_mon = production[(production["shift"]=="Night") & (production["day_of_week"]=="Monday")]["defect_rate"].mean()
overall = production["defect_rate"].mean()
print(f"2. Night+Monday defect rate: {night_mon*100:.1f}% vs overall {overall*100:.1f}% (+{(night_mon/overall - 1)*100:.0f}%)")
mar_slider = production[(production["component"]=="NV-Slider-A") & (production["month"]=="2024-03")]["defect_rate"].mean()
other_slider = production[(production["component"]=="NV-Slider-A") & (production["month"]!="2024-03")]["defect_rate"].mean()
print(f"3. NV-Slider-A March: {mar_slider*100:.1f}% defect rate vs {other_slider*100:.1f}% other months")

What does this code do?

This section quantifies three anomalies. Finding 1 uses the monthly_machine DataFrame (already computed for Chart 2) to print ACAM-03's January vs June defect rate.

Finding 2 compares Night+Monday defect rate against the plant-wide average. Finding 3 isolates NV-Slider-A in March vs all other months. Each finding gives you a quotable number for later recommendations.

• Save scripts/eda.py.

Before you run the final version, what do you think the Night+Monday defect rate will be relative to the overall average? Take a guess, then run:

python scripts/eda.py

At the bottom of the output, you should see the Key Findings section showing ACAM-03 rising from approximately 3% to approximately 14%, a Night+Monday rate significantly above the overall average, and NV-Slider-A March spiking above its normal rate in other months.

Seeing a KeyError or empty values?

The findings section references the monthly_machine DataFrame created in Chart 2. If you see a NameError for monthly_machine, make sure Chart 2's code block appears above the findings section in your file.

help me debug my EDA findings section

✔️ Awesome, I've got everything!

Great. Double check you have saved scripts/eda.py and that all 4 chart files exist in reports/.

ⓧ I'd like to double check the full code

Your scripts/eda.py should contain these sections in order. Scroll up and confirm each is present:

• Imports (pandas, numpy, matplotlib.pyplot, os) and directory setup.
• Data loading with derived columns (month, week, day_of_week, defect_rate).
• Summary KPIs block (Total Production, Yield, Defect Rate, Downtime, Cycle Time, Top 3 Defects).
• Chart 1: Weekly production volume line chart saved to 01_weekly_production.png.
• Chart 2: Monthly defect rate by machine saved to 02_machine_defect_trends.png.
• Chart 3: Shift x Day heatmap saved to 03_shift_day_heatmap.png.
• Chart 4: Defect type horizontal bar chart saved to 04_defect_types.png.
• Key Findings section printing ACAM-03 degradation, Night+Monday rate, and NV-Slider-A March spike.

You now have hard evidence of three manufacturing problems hiding in the data. Next, you will use SQL to quantify these patterns precisely and answer specific business questions that manufacturing engineers need answered.

Analyze with SQL Business Questions

Your EDA charts revealed three clear anomalies: ACAM-03 degrading over time, a Monday night shift defect spike, and a March contamination event on NV-Slider-A.

Manufacturing engineers will not look at your matplotlib charts and accept "it looks worse" as evidence. They need precise numbers. SQL lets you quantify each pattern exactly and prove it is statistically meaningful, not just visual noise.

In this step, get ready to:

• Load the cleaned production data into a SQLite database.
• Answer 5 manufacturing business questions with SQL aggregations, JOINs, and CTEs.
• Export summary CSVs ready for Power BI import.

Load cleaned data into SQLite

Python's built-in sqlite3 module gives you a local SQL database with zero installation. You will load your cleaned CSV into it so you can run proper SQL queries.

• Create a new file called sql_analysis.py inside your scripts folder.
• Add the following code to load your cleaned data into a SQLite database:

import pandas as pd
import sqlite3
import os

CLEAN_DIR = "data/cleaned"
DB_PATH = "novastor.db"

# Connect to SQLite (creates the file if it doesn't exist)
print("=== NovaStor SQL Analysis ===\n")
conn = sqlite3.connect(DB_PATH)

# Load cleaned data and reference tables
production = pd.read_csv(f"{CLEAN_DIR}/production_cleaned.csv")
machines = pd.read_csv("data/raw/machines.csv")
defect_types = pd.read_csv("data/raw/defect_types.csv")

# Write DataFrames into SQLite tables
production.to_sql("production", conn, if_exists="replace", index=False)
machines.to_sql("machines", conn, if_exists="replace", index=False)
defect_types.to_sql("defect_types", conn, if_exists="replace", index=False)

# Verify tables exist
tables = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", conn)
print(f"Tables created: {tables['name'].tolist()}")
print(f"Production records: {len(production):,}\n")

What does this code do?

• sqlite3.connect(DB_PATH) creates a database file called novastor.db in your project root. If the file already exists, it opens it.
• DataFrame.to_sql() writes each pandas DataFrame as a table in the database. The if_exists="replace" flag means re-running the script overwrites existing tables cleanly.
• The sqlite_master query confirms the tables were created successfully.

• Save scripts/sql_analysis.py.
• Run the script to confirm the database is created:

python scripts/sql_analysis.py

You should see output listing 3 tables (production, machines, defect_types) and the record count.

Seeing a file not found error?

• Make sure you are running the command from the novastor-analytics folder, not from inside scripts/.
• Confirm data/cleaned/production_cleaned.csv exists. If not, re-run python scripts/clean_data.py first.

Why can't my script find the CSV files?

Answer 5 business questions with SQL

Each query answers a question a manufacturing engineer would actually ask. You will add them one group at a time to your script.

• Add the following code below the table verification section in sql_analysis.py. These two queries answer: which machines cause the most downtime, and is the night shift actually worse?

# === Q1: Machine Downtime Rankings ===
print("=" * 60)
print("Q1: Which machines have the highest total downtime?")
print("=" * 60)
q1 = """
SELECT p.machine_id, m.machine_type, m.production_line,
    ROUND(SUM(p.downtime_minutes), 1) AS total_downtime_min,
    ROUND(SUM(p.downtime_minutes) / 60, 1) AS total_downtime_hrs
FROM production p
JOIN machines m ON p.machine_id = m.machine_id
GROUP BY p.machine_id, m.machine_type, m.production_line
ORDER BY total_downtime_min DESC LIMIT 5;
"""
print(pd.read_sql(q1, conn).to_string(index=False))

# === Q2: Defect Rate by Shift ===
print("\n" + "=" * 60)
print("Q2: What is the defect rate by shift?")
print("=" * 60)
q2 = """
SELECT shift, COUNT(*) AS records,
    SUM(units_produced) AS total_produced,
    SUM(units_defective) AS total_defective,
    ROUND(CAST(SUM(units_defective) AS FLOAT) / SUM(units_produced) * 100, 2) AS defect_rate_pct
FROM production GROUP BY shift ORDER BY defect_rate_pct DESC;
"""
print(pd.read_sql(q2, conn).to_string(index=False))

What do these queries do?

Q1 uses a JOIN between the production and machines tables to enrich machine IDs with their type and production line. The GROUP BY + ORDER BY pattern ranks machines by total accumulated downtime.

Q2 calculates a percentage using CAST to avoid integer division. Grouping by shift lets you compare Morning, Afternoon, and Night directly.

• Save the file and run it again:

python scripts/sql_analysis.py

You should see a ranked table of machines by downtime (ACAM-03 likely near the top) and defect rate percentages per shift (Night shift showing the highest rate).

• Now add queries Q3 and Q4 below Q2. These reveal which component-month combinations have the worst yield, and which machines produce which defect types:

# === Q3: Component Yield by Month ===
print("\n" + "=" * 60)
print("Q3: Which component has worst yield, and when?")
print("=" * 60)
q3 = """
SELECT component, SUBSTR(date, 1, 7) AS month,
    SUM(units_produced) AS produced, SUM(units_passed) AS passed,
    ROUND(CAST(SUM(units_passed) AS FLOAT) / SUM(units_produced) * 100, 1) AS yield_pct
FROM production GROUP BY component, month ORDER BY yield_pct ASC LIMIT 8;
"""
print(pd.read_sql(q3, conn).to_string(index=False))

# === Q4: Defect Types by Machine ===
print("\n" + "=" * 60)
print("Q4: Top defect types and which machines produce them?")
print("=" * 60)
q4 = """
SELECT p.defect_type, dt.category, dt.severity, p.machine_id,
    COUNT(*) AS occurrences, SUM(p.units_defective) AS total_defective
FROM production p
JOIN defect_types dt ON p.defect_type = dt.defect_code
WHERE p.defect_type IS NOT NULL
GROUP BY p.defect_type, dt.category, dt.severity, p.machine_id
ORDER BY total_defective DESC LIMIT 10;
"""
print(pd.read_sql(q4, conn).to_string(index=False))

What do Q3 and Q4 reveal?

Q3 uses SUBSTR to extract the year-month from the date column, then groups by component and month. NV-Slider-A in March 2024 should appear at the top with the lowest yield, confirming the contamination event your heatmap spotted.

Q4 joins the production table with defect_types to bring in severity and category labels. This shows which machine-defect combinations produce the most waste.

• Now add the most advanced query, Q5. This uses a CTE (Common Table Expression) to compare each machine's defect rate in the first half vs second half of the period:

# === Q5: Machines with Increasing Defect Rate (CTE) ===
print("\n" + "=" * 60)
print("Q5: Which machines show increasing defect rates?")
print("=" * 60)
q5 = """
WITH h1 AS (
    SELECT machine_id,
        ROUND(CAST(SUM(units_defective) AS FLOAT) / SUM(units_produced) * 100, 2) AS rate_h1
    FROM production WHERE date BETWEEN '2024-01-01' AND '2024-03-31'
    GROUP BY machine_id
),
h2 AS (
    SELECT machine_id,
        ROUND(CAST(SUM(units_defective) AS FLOAT) / SUM(units_produced) * 100, 2) AS rate_h2
    FROM production WHERE date BETWEEN '2024-04-01' AND '2024-06-30'
    GROUP BY machine_id
)
SELECT h1.machine_id, m.machine_type,
    h1.rate_h1 AS defect_pct_Q1Q2, h2.rate_h2 AS defect_pct_Q3Q4,
    ROUND(h2.rate_h2 - h1.rate_h1, 2) AS change,
    CASE WHEN h2.rate_h2 - h1.rate_h1 > 3 THEN 'CRITICAL INCREASE'
         WHEN h2.rate_h2 - h1.rate_h1 > 1 THEN 'Moderate increase'
         ELSE 'Stable' END AS assessment
FROM h1 JOIN h2 ON h1.machine_id = h2.machine_id
JOIN machines m ON h1.machine_id = m.machine_id
ORDER BY change DESC;
"""
result_q5 = pd.read_sql(q5, conn)
print(result_q5.to_string(index=False))

What is a CTE?

A CTE (WITH ... AS) creates a temporary named result set you can reference in the main query. Here, h1 calculates each machine's defect rate for Jan-Mar, and h2 does the same for Apr-Jun. The main SELECT joins them to calculate the change.

The CASE statement labels each machine as Stable, Moderate increase, or CRITICAL INCREASE based on how much worse it got. ACAM-03 should stand out clearly.

• Save the file and run it to see all 5 query results:

python scripts/sql_analysis.py

You should see formatted results for all 5 questions. Q5 should label ACAM-03 as CRITICAL INCREASE with a change of roughly +7 to +10 percentage points.

Seeing a SQL syntax error?

• Check that each SQL string starts and ends with triple quotes and a newline. A stray quote inside the SQL will break the string.
• Make sure you pasted each query in order, below the previous one. The script runs top-to-bottom and uses the same conn connection throughout.

Help me debug my SQL query error in sql_analysis.py.

Export results for Power BI

Power BI Desktop cannot connect to SQLite directly. You will export your key summaries as CSVs that Power BI can import easily.

• Add the following code at the bottom of sql_analysis.py, below the Q5 result:

# === Export summaries for Power BI ===
print("\n\n=== Exporting for Power BI ===")
result_q5.to_csv(f"{CLEAN_DIR}/machine_summary.csv", index=False)
print(f"Saved: {CLEAN_DIR}/machine_summary.csv")

kpi = pd.read_sql("""
SELECT SUM(units_produced) AS total_production,
    SUM(units_defective) AS total_defects,
    ROUND(CAST(SUM(units_passed) AS FLOAT) / SUM(units_produced) * 100, 2) AS yield_rate_pct,
    ROUND(CAST(SUM(units_defective) AS FLOAT) / SUM(units_produced) * 100, 2) AS defect_rate_pct,
    ROUND(SUM(downtime_minutes) / 60, 1) AS total_downtime_hrs,
    ROUND(AVG(cycle_time_seconds), 1) AS avg_cycle_time
FROM production;
""", conn)
kpi.to_csv(f"{CLEAN_DIR}/monthly_kpis.csv", index=False)
print(f"Saved: {CLEAN_DIR}/monthly_kpis.csv")
print(f"\nKPIs:\n{kpi.to_string(index=False)}")

conn.close()
print(f"\nDatabase: {DB_PATH}")

What gets exported?

• machine_summary.csv contains the Q5 results: each machine's defect rate in H1 vs H2, the change, and the assessment label. Power BI will use this for machine comparison visuals.
• monthly_kpis.csv holds the overall factory KPIs in a single row. Power BI will use these for KPI card visuals at the top of your dashboard.
• conn.close() releases the database file so other tools can access it.

• Save scripts/sql_analysis.py.

Before you run the final version, predict: how many CSV files will be saved, and where will the SQLite database appear?

• Run the complete script:

python scripts/sql_analysis.py

You should see all 5 business question results printed, then "Exporting for Power BI" followed by the two saved CSV paths and the overall KPI values.

• Confirm the database and export files exist by running:

ls novastor.db data/cleaned/machine_summary.csv data/cleaned/monthly_kpis.csv

You should see all three files listed. The novastor.db file lives in your project root, and the two CSVs sit in data/cleaned/.

Missing one of the export files?

• If machine_summary.csv is missing, check that the Q5 query completed without errors. The export relies on result_q5 being defined by that query.
• If novastor.db is missing, make sure you are running the script from the novastor-analytics folder, since the path "novastor.db" is relative.

Help me figure out why my export files are not being created.

✔️ Awesome, I've got everything!

Great. Double check you have saved scripts/sql_analysis.py and that all 5 query results printed cleanly.

ⓧ I'd like to double check the full code

Scroll up through your scripts/sql_analysis.py and confirm these sections appear in order:

• Imports and constants (pandas, sqlite3, os, paths).
• Load CSVs and write them to SQLite with to_sql().
• Q1: Machine downtime rankings (JOIN + GROUP BY + ORDER BY DESC LIMIT 5).
• Q2: Defect rate by shift (GROUP BY shift, CAST for percentage).
• Q3: Component yield by month (SUBSTR + GROUP BY component, month).
• Q4: Defect types by machine (JOIN defect_types, GROUP BY multiple columns).
• Q5: Increasing defect rates CTE (WITH h1, h2, CASE statement).
• Export section: machine_summary.csv and monthly_kpis.csv saved, conn.close() at the end.

You now have precise, quantified answers to 5 manufacturing questions. Your SQL proved what the charts suggested: ACAM-03 is degrading critically, night shifts are worse, and NV-Slider-A had a March contamination event. Next up, you will turn these numbers into a Power BI dashboard that manufacturing engineers can interact with directly.

Build the Power BI Dashboard

Your SQL queries quantified the patterns you spotted during EDA. ACAM-03 is degrading, night-shift Mondays are a defect hotspot, and NV-Slider-A had a March contamination event.

Manufacturing engineers will never open a Python script. A Power BI dashboard puts your KPIs and trends in front of decision-makers in a format they can filter, drill into, and act on without writing code.

In this step, get ready to:

• Import production and machine data into Power BI Desktop and create a table relationship.
• Create DAX measures and KPI cards showing manufacturing performance at a glance.
• Build analytical visuals and slicers for interactive exploration.

Import data into Power BI Desktop

Power BI Desktop cannot connect directly to SQLite databases. You will import the cleaned CSV files you exported in the previous step.

• Press the Windows key to open your search bar.
• Type Power BI Desktop and press Enter to open it.
• On the Home tab in the ribbon, click Get Data.
• Select Text/CSV from the list, then click Connect.
• Navigate to your novastor-analytics/data/cleaned/ folder and select production_cleaned.csv.
• Click Open, then click Load in the preview window.

You should see the production_cleaned table appear in the Fields pane on the right.

• Repeat the import process: click Get Data > Text/CSV again.
• This time, navigate to novastor-analytics/data/raw/ and select machines.csv.
• Click Open, then click Load.

Both tables now appear in the Fields pane. Next, connect them with a relationship.

• Click the Model icon on the left side of the Power BI Desktop window to switch to Model view.
• You should see both tables displayed as boxes on the canvas.
• Drag the machine_id column from the machines table and drop it onto the machine_id column in the production_cleaned table.

A relationship line appears between the two tables. The machines table is the "one" side (10 unique machines) and production_cleaned is the "many" side (many production records per machine).

What does a relationship do here?

Relationships let Power BI propagate filters across tables. When you filter by production_line in the machines table, Power BI automatically filters the matching production records. This is how slicers will work on your dashboard.

Don't see both tables in Model view?

Make sure you clicked Load (not Transform Data) during the import step. If a table is missing, go back to Home > Get Data > Text/CSV and re-import the missing file.

help me fix my Power BI data import

Create DAX measures and KPI cards

Power BI uses DAX (Data Analysis Expressions) to define calculated measures. These are formulas that respond to filters and slicers in real time.

• Click the Report icon on the left side to switch back to Report view.
• In the Fields pane, right-click the production_cleaned table.
• Select New measure from the context menu.
• In the formula bar that appears at the top, type the first measure and press Enter:

Total Production = SUM(production_cleaned[units_produced])

• Right-click production_cleaned again and select New measure. Create each of the remaining measures one at a time, pressing Enter after each:

Total Defects = SUM(production_cleaned[units_defective])

Yield Rate = SUM(production_cleaned[units_passed]) / SUM(production_cleaned[units_produced])

Defect Rate = SUM(production_cleaned[units_defective]) / SUM(production_cleaned[units_produced])

Total Downtime = SUM(production_cleaned[downtime_minutes]) / 60

Avg Cycle Time = AVERAGE(production_cleaned[cycle_time_seconds])

What do these measures calculate?

• Total Production sums all units produced across whatever filter context is active.
• Yield Rate and Defect Rate calculate quality percentages that respond to slicers.
• Total Downtime converts minutes to hours for readability.
• Avg Cycle Time shows the average seconds per unit for the filtered selection.

Now add KPI cards so the measures are visible on the report page.

• In the Visualizations pane, click the Card visual icon (a single number in a box).
• With the new Card selected on the canvas, go to the Fields pane, expand production_cleaned, and drag Total Production into the Card's Fields well.
• Resize and position the Card at the top of the report page.
• Repeat this process four more times to create Cards for Yield Rate, Defect Rate, Total Downtime, and Avg Cycle Time.
• Arrange all 5 Cards in a row across the top of the page.

You should see numeric values in each card. Total Production shows a number in the millions. Defect Rate shows a decimal around 0.05 (about 5%).

Format percentages

To display Yield Rate and Defect Rate as percentages, select the Card, click the measure in the Fields pane, and in the Measure tools tab change the Format dropdown to Percentage.

Card showing a blank or error?

Check that the formula bar shows the correct table name production_cleaned (matching your imported CSV filename) and the column name matches exactly. Column names are case-sensitive in DAX.

help me fix my DAX measure in Power BI

Build analytical visuals and slicers

KPI cards give the summary. Analytical visuals let engineers explore what drives those numbers.

• Click an empty area on the canvas below your KPI cards.
• In the Visualizations pane, select Line chart.
• Drag date to the X-axis well. Power BI may auto-create a date hierarchy. If so, drill down to the Month level.
• Drag the Defect Rate measure to the Y-axis well.
• Drag production_line to the Legend well so each production line gets its own coloured trend line.

You should see a line chart with separate trend lines for Slider Processing, HGA Assembly, and Final Inspection across the months of 2024.

• Add a second visual: click an empty area, then select Clustered bar chart from the Visualizations pane.
• Drag machine_id to the Y-axis well.
• Drag the Total Downtime measure to the X-axis well.
• Click the three-dot menu on the visual, select Sort axis, and choose Total Downtime descending so the worst machine appears at the top.

ACAM-03 should appear near the top of the downtime rankings, consistent with what your SQL analysis found.

• Add a third visual: select Treemap from the Visualizations pane.
• Drag defect_type to the Category well.
• Drag the Total Defects measure to the Values well.
• Add a fourth visual: select Matrix from the Visualizations pane.
• Drag shift to Rows.
• Drag day_of_week to Columns.
• Drag the Defect Rate measure to Values.

Why a matrix for shift and day?

The matrix visual works like a pivot table. It lets you scan across days and down shifts to spot the Night + Monday hotspot at a glance. Manufacturing engineers use this layout to plan staffing and maintenance schedules.

Finally, add slicers so viewers can filter the entire dashboard interactively.

• Click an empty area, then select the Slicer visual from the Visualizations pane.
• Drag date into the slicer's Field well. This creates a date range slider.
• Add a second slicer and drag production_line into its Field well. This creates a dropdown filter for production lines.
• Add a third slicer and drag component into its Field well.
• Position all three slicers along the top or side of the report, near the KPI cards.

Before you test, predict: if you filter the Production Line slicer to just "HGA Assembly", do you expect the Defect Rate card to go up or down compared to the overall rate?

• Click HGA Assembly in the Production Line slicer to test it.

All visuals on the page update to show only HGA Assembly data. The KPI cards recalculate, the line chart shows only HGA lines, and the bar chart filters to HGA machines. Click the selection again to clear the filter.

• Save your report by pressing Ctrl+S.
• Name the file NovaStor_Dashboard.pbix and save it inside your novastor-analytics folder.

Slicers not filtering other visuals?

This usually means the relationship between tables is missing or inactive. Switch to Model view and confirm you see a solid line between production_cleaned and machines on machine_id. If the line is dashed, double-click it and check Make this relationship active.

help me fix Power BI slicers not filtering

Open your saved NovaStor_Dashboard.pbix file and confirm you see 5 KPI cards at the top, 4 analytical visuals (line chart, bar chart, treemap, matrix), and 3 working slicers that filter the entire page.

Secret mission

Write an Insights and Recommendations Report

You have charts, SQL results, and a dashboard. But manufacturing engineers do not read Python scripts. Can you translate your data findings into a formal report with quantified impact and specific engineering actions?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

• The novastor-analytics folder containing all scripts, CSV data files, matplotlib charts, and the SQLite database (approximately 40-60 MB).
• Power BI .pbix dashboard file.
• Python packages installed via pip: pandas, numpy, matplotlib.

Keep everything running

No action needed. Choose this if you plan to extend the project, revisit the analysis, or use it as a reference for future work.

• All files remain on your local machine at no cost.
• Your Power BI dashboard stays accessible whenever you open the .pbix file.
• The installed Python packages (pandas, numpy, matplotlib) remain available for other projects.

Pause - I'll come back to this later

Since this project is entirely local with no running processes, there is nothing to pause. Your files stay on disk until you delete them.

• Close the Power BI Desktop file if it is open.
• Close any open terminal sessions running in the novastor-analytics folder.
• Your project files will be waiting for you when you return.

Delete - I don't want to use this again

Remove all project files from your machine. This is permanent.

• Close Power BI Desktop if it is open.
• Delete the project folder by running this command on macOS/Linux:

cd ~/Desktop
rm -rf novastor-analytics

• On Windows, open Command Prompt and run:

cd %USERPROFILE%\Desktop
rmdir /s /q novastor-analytics

• If you saved your .pbix file outside the project folder, delete it from wherever you saved it.
• Optionally, uninstall the Python packages if you do not need them for other projects:

pip uninstall pandas numpy matplotlib -y

Keep the packages if you do other data work

If you use Python for other analytics or data science work, keep pandas, numpy, and matplotlib installed. They are standard tools you will use in most Python data projects.

Nice Work!

Nice Work!

You just completed a full manufacturing data analytics pipeline for NovaStor Technologies. From generating 100,000 rows of messy production data to delivering an interactive dashboard and actionable recommendations, you worked through the same workflow a real Data Analytics Intern would follow.

You've learned how to:

• Generate, audit, and clean a realistic 100,000-row manufacturing dataset using pandas, handling duplicates, inconsistent naming, missing values, negative values, and outliers.
• Uncover hidden quality patterns through matplotlib visualizations and SQL queries: a degrading ACAM-03 machine, a Night Monday defect spike, and a March contamination event.
• Build a Power BI dashboard with 5 KPI cards and interactive analytical visuals that manufacturing engineers can filter by date, production line, and component.
• Secret Mission: Translated data findings into a formal engineering recommendations report with quantified impact and prioritized actions for manufacturing leadership.

Ready to quiz yourself?