Python and SQL for Manufacturing: Hands-On Training¶

Demo Notebook 4B: Statistical Process Control (SPC) with SQL + Python¶

Python SQLite Matplotlib Jupyter License: MIT Author GitHub

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

Notebook Scope: Build a dedicated SPC database, load process measurement data using SQL, and draw control charts using Python.

What is SPC?¶

SPC (Statistical Process Control) is a set of simple tools that help you detect problems in a manufacturing process early - before bad parts reach the customer.

Every process has two types of variation:

Type Meaning What to do
Common cause Normal random variation - always present Leave the process alone
Assignable cause A real problem - tool wear, bad material, operator error Find it and fix it

A control chart plots sample measurements over time. As long as all points stay inside the control limits and look random, the process is in control. A point outside the limits is a signal to investigate.

Charts we build in this notebook:

Chart What it monitors Data type Purpose in manufacturing
X-bar Is the process mean staying on target? Continuous measurements (diameter, weight, temperature) Detect shifts in the process centre — e.g., a worn tool gradually moving a dimension off target
R Is the process spread staying stable? Same measurements as X-bar, used together Detect increases in within-subgroup variability — e.g., a loose fixture causing inconsistent clamping
p Is the fraction of defective units acceptable? Pass / fail inspection (attribute data) Monitor yield on high-volume lines where each unit is classed as good or bad
c Is the number of defects per unit acceptable? Defect count per finished part Detect quality upsets on complex assemblies where a single unit can have multiple defects

Step 1 - Import libraries and create the SPC database¶

We create a fresh SQLite database spc_demo.db dedicated to this notebook.
No shared state with earlier demo notebooks.

In [1]:
import sqlite3                              # For database operations
import math                                 # For mathematical functions
import numpy as np                          # For numerical operations
import matplotlib.pyplot as plt             # For plotting / data visualization

# Create the SPC demo database and connect to it
conn   = sqlite3.connect('spc_demo.db')     # Connect to the database (or create it if it doesn't exist)
cursor = conn.cursor()                      # Create a cursor object to interact with the database

print('Connected to spc_demo.db')           # Print a message confirming the connection to the database

Connected to spc_demo.db

Step 2 - Create SPC tables¶

Three tables, one per chart type:

Table Chart Data recorded Example in this notebook
shaft_measurements X-bar / R 4 raw continuous readings per subgroup Shaft diameter in mm; each row is one subgroup of 4 measurements taken close together in time
defect_inspection p-chart Number of defective units found in a fixed-size batch 100 shafts inspected per shift; each row records how many were rejected
surface_defects c-chart Total defect count on each individual finished part Each completed part is inspected for surface flaws; a single part can have several defects

Variable vs attribute data: shaft_measurements holds measured values (continuous).
defect_inspection and surface_defects hold counted values (discrete) — this distinction drives which chart type and which formula to use.

In [2]:
# Drop old tables so this cell can be re-run cleanly
cursor.execute('DROP TABLE IF EXISTS shaft_measurements')
cursor.execute('DROP TABLE IF EXISTS defect_inspection')
cursor.execute('DROP TABLE IF EXISTS surface_defects')

# Table 1: shaft diameter readings — 4 observations per sample
cursor.execute('''
    CREATE TABLE shaft_measurements (
        sample_no  INTEGER PRIMARY KEY,
        obs1       REAL NOT NULL,
        obs2       REAL NOT NULL,
        obs3       REAL NOT NULL,
        obs4       REAL NOT NULL
    )
''')

# Table 2: how many defective units per inspection batch
cursor.execute('''
    CREATE TABLE defect_inspection (
        sample_no   INTEGER PRIMARY KEY,
        sample_size INTEGER NOT NULL,
        defectives  INTEGER NOT NULL
    )
''')

# Table 3: how many surface defects found on each finished part
cursor.execute('''
    CREATE TABLE surface_defects (
        unit_no      INTEGER PRIMARY KEY,
        defect_count INTEGER NOT NULL
    )
''')

conn.commit()
print('Tables created: shaft_measurements, defect_inspection, surface_defects')

Tables created: shaft_measurements, defect_inspection, surface_defects

Step 3 - Insert shaft diameter data (variable measurements)¶

20 subgroups of 4 measurements each. Target diameter = 50.00 mm.
Subgroups 13 (upward shift) and 18 (downward shift) are deliberate out-of-control events.

In [3]:
# 20 subgroups, n=4, shaft diameter in mm
# Samples 13 and 18 simulate assignable-cause process shifts
shaft_data = [
    (1,  49.85, 50.12, 50.03, 49.95),
    (2,  50.10, 49.90, 50.05, 50.15),
    (3,  49.95, 50.08, 49.88, 50.02),
    (4,  50.00, 50.10, 50.05, 49.90),
    (5,  50.05, 49.85, 50.15, 50.00),
    (6,  49.90, 50.00, 50.10, 50.05),
    (7,  50.08, 50.02, 49.92, 50.05),
    (8,  49.95, 50.00, 50.10, 50.05),
    (9,  50.05, 49.95, 50.00, 50.15),
    (10, 50.00, 49.90, 50.10, 50.00),
    (11, 50.05, 49.85, 49.95, 50.10),
    (12, 50.00, 50.10, 50.05, 49.95),
    (13, 50.35, 50.40, 50.38, 50.42),  # process shift UP
    (14, 50.05, 50.00, 49.90, 50.10),
    (15, 49.95, 50.08, 50.02, 49.88),
    (16, 50.00, 49.95, 50.05, 50.10),
    (17, 50.08, 50.02, 49.95, 50.00),
    (18, 49.55, 49.60, 49.58, 49.62),  # process shift DOWN
    (19, 50.00, 50.05, 49.90, 50.05),
    (20, 49.95, 50.10, 50.00, 50.05),
]

cursor.executemany('INSERT INTO shaft_measurements VALUES (?,?,?,?,?)', shaft_data)
conn.commit()

print(f'Inserted {len(shaft_data)} subgroups into shaft_measurements')

Inserted 20 subgroups into shaft_measurements

Step 4 - Compute subgroup means and ranges with SQL¶

For each subgroup of 4 readings, we need two numbers:

  • Mean (X-bar): average of the 4 readings - tells us where the process is centred
  • Range (R): largest reading minus smallest - tells us how spread out the readings are

MAX(obs1, obs2, obs3, obs4) and MIN(...) in SQLite can take multiple columns from the same row, so the whole calculation stays inside the SQL query.

In [4]:
# SQL computes the subgroup mean and range for us in one query
rows = cursor.execute('''
    SELECT
        sample_no,
        ROUND((obs1 + obs2 + obs3 + obs4) / 4.0, 4) AS xbar,
        ROUND(MAX(obs1,obs2,obs3,obs4) - MIN(obs1,obs2,obs3,obs4), 4) AS range_val
    FROM shaft_measurements
    ORDER BY sample_no
''').fetchall()

# Print results row by row using a simple for loop
print('Sample   X-bar (mm)   Range (mm)')
print('------   ----------   ----------')
for row in rows:
    sample = row[0]
    xbar   = row[1]
    rng    = row[2]
    print(f'  {sample:2d}       {xbar:.4f}        {rng:.4f}')

Sample   X-bar (mm)   Range (mm)
------   ----------   ----------
   1       49.9875        0.2700
   2       50.0500        0.2500
   3       49.9825        0.2000
   4       50.0125        0.2000
   5       50.0125        0.3000
   6       50.0125        0.2000
   7       50.0175        0.1600
   8       50.0250        0.1500
   9       50.0375        0.2000
  10       50.0000        0.2000
  11       49.9875        0.2500
  12       50.0250        0.1500
  13       50.3875        0.0700
  14       50.0125        0.2000
  15       49.9825        0.2000
  16       50.0250        0.1500
  17       50.0125        0.1300
  18       49.5875        0.0700
  19       50.0000        0.1500
  20       50.0250        0.1500

Step 5 - Compute X-bar and R control limits¶

Control limit formulas¶

The limits are placed at ±3 standard deviations from the centre line — covering 99.73 % of normal variation.
Rather than estimating σ directly, Shewhart derived simple multipliers that work from $\bar{R}$ alone:

$$UCL_{\bar{X}} = \bar{\bar{X}} + A_2\bar{R}, \qquad LCL_{\bar{X}} = \bar{\bar{X}} - A_2\bar{R}$$

$$UCL_R = D_4\bar{R}, \qquad LCL_R = D_3\bar{R}$$

Variables:

  • $\bar{\bar{X}}$ — grand mean (average of all subgroup means)
  • $\bar{R}$ — average range across all subgroups
  • $A_2, D_3, D_4, d_2$ — Shewhart constants (depend only on subgroup size $n$)

Shewhart control chart constants¶

These values are published in ASTM E2281 and ISO 8258. They are derived mathematically from the distribution of ranges for samples drawn from a normal population — you look them up, you do not calculate them.

$n$ $A_2$ $D_3$ $D_4$ $d_2$
2 1.880 0 3.267 1.128
3 1.023 0 2.574 1.693
4 0.729 0 2.282 2.059
5 0.577 0 2.114 2.326
6 0.483 0 2.004 2.534
7 0.419 0.076 1.924 2.704
8 0.373 0.136 1.864 2.847

This notebook uses $n = 4$ (highlighted row above).

What each factor does:

Factor Role Formula it appears in
$A_2$ Scales $\bar{R}$ into a ±3σ half-width for the X-bar chart $UCL/LCL_{\bar{X}} = \bar{\bar{X}} \pm A_2\bar{R}$
$D_4$ Upper multiplier for the R chart $UCL_R = D_4\bar{R}$
$D_3$ Lower multiplier for the R chart (0 for $n \le 6$ — range cannot be negative) $LCL_R = D_3\bar{R}$
$d_2$ Unbiasing constant; converts $\bar{R}$ into an estimate of σ $\hat{\sigma} = \bar{R}/d_2$ (used for Cpk in Step 12)

SQL gives us $\bar{\bar{X}}$ and $\bar{R}$; Python just multiplies by the factors above.

In [5]:
# Shewhart factors for subgroup size n = 4  (standard published table)
A2 = 0.729   # multiplied by R-bar to get X-bar chart limits
D3 = 0.000   # lower factor for R chart (0 for n <= 6)
D4 = 2.282   # upper factor for R chart
d2 = 2.059   # divides R-bar to estimate process standard deviation

# Ask SQL for the grand mean and average range across all 20 subgroups
result = cursor.execute('''
    SELECT
        AVG((obs1 + obs2 + obs3 + obs4) / 4.0),
        AVG(MAX(obs1,obs2,obs3,obs4) - MIN(obs1,obs2,obs3,obs4))
    FROM shaft_measurements
''').fetchone()

grand_mean = round(result[0], 4)   # X-double-bar
r_bar      = round(result[1], 4)   # R-bar

# X-bar chart limits:  grand_mean  ±  A2 × R-bar
ucl_x = round(grand_mean + A2 * r_bar, 4)
lcl_x = round(grand_mean - A2 * r_bar, 4)

# R chart limits:  D4 × R-bar  and  D3 × R-bar
ucl_r = round(D4 * r_bar, 4)
lcl_r = round(D3 * r_bar, 4)

print('Grand Mean :', grand_mean, 'mm')
print('Avg Range  :', r_bar, 'mm')
print()
print('X-bar chart   UCL:', ucl_x, '  Centre:', grand_mean, '  LCL:', lcl_x)
print('R chart       UCL:', ucl_r, '  Centre:', r_bar, '     LCL:', lcl_r)

Grand Mean : 50.0091 mm
Avg Range  : 0.1825 mm

X-bar chart   UCL: 50.1421   Centre: 50.0091   LCL: 49.8761
R chart       UCL: 0.4165   Centre: 0.1825      LCL: 0.0

Step 6 - X-bar Control Chart¶

The X-bar chart monitors the process mean over time.

$$UCL_{\bar{X}} = \bar{\bar{X}} + A_2\bar{R}, \quad CL = \bar{\bar{X}}, \quad LCL_{\bar{X}} = \bar{\bar{X}} - A_2\bar{R}$$

Any point outside UCL/LCL indicates an assignable cause has shifted the mean.

In [6]:
# Unpack the SQL results into plain lists for the chart
sample_nos = []
xbars      = []
for row in rows:
    sample_nos.append(row[0])
    xbars.append(row[1])

# Find which samples fall outside the control limits
ooc_samples = []
ooc_values  = []
for row in rows:
    if row[1] > ucl_x or row[1] < lcl_x:
        ooc_samples.append(row[0])
        ooc_values.append(row[1])

# Plot the X-bar chart
fig, ax = plt.subplots(figsize=(12, 4))

ax.plot(sample_nos, xbars, marker='o', color='steelblue', linewidth=1.5, label='Sample X-bar')
ax.axhline(grand_mean, color='green', linewidth=1.5, linestyle='-',  label='Centre Line')
ax.axhline(ucl_x,      color='red',   linewidth=1.5, linestyle='--', label='UCL / LCL')
ax.axhline(lcl_x,      color='red',   linewidth=1.5, linestyle='--')

# Highlight out-of-control points in red
ax.scatter(ooc_samples, ooc_values, color='red', s=120, zorder=5, label='Out of Control')

ax.set_title('X-bar Control Chart - Shaft Diameter (mm)', fontweight='bold')
ax.set_xlabel('Sample Number')
ax.set_ylabel('Mean Diameter (mm)')
ax.legend()
ax.set_xticks(sample_nos)
plt.tight_layout()
plt.show()

print('Out-of-control sample numbers:', ooc_samples)
No description has been provided for this image 
Out-of-control sample numbers: [13, 18]

Step 7 - R (Range) Control Chart¶

The R chart monitors process precision (within-subgroup spread).

$$UCL_R = D_4\bar{R}, \quad CL = \bar{R}, \quad LCL_R = D_3\bar{R}$$

An out-of-control R value means variability itself has changed — even if the mean looks fine.

In [12]:
# Unpack range values into a plain list
ranges = []
for row in rows:
    ranges.append(row[2])

# Find out-of-control range values (above UCL means spread got worse)
ooc_r_samples = []
ooc_r_values  = []
for row in rows:
    if row[2] > ucl_r:
        ooc_r_samples.append(row[0])
        ooc_r_values.append(row[2])

# Plot the R chart
fig, ax = plt.subplots(figsize=(12, 4))

ax.plot(sample_nos, ranges, marker='s', color='darkorange', linewidth=1.5, label='Sample Range')
ax.axhline(r_bar, color='green', linewidth=1.5, linestyle='-',  label='Centre Line (R-bar)')
ax.axhline(ucl_r, color='red',   linewidth=1.5, linestyle='--', label='UCL / LCL')
ax.axhline(lcl_r, color='red',   linewidth=1.5, linestyle='--')

if ooc_r_samples:
    ax.scatter(ooc_r_samples, ooc_r_values, color='red', s=120, zorder=5, label='Out of Control')

ax.set_title('R (Range) Control Chart — Shaft Diameter (mm)', fontweight='bold')
ax.set_xlabel('Sample Number')
ax.set_ylabel('Within-Sample Range (mm)')
ax.legend()
ax.set_xticks(sample_nos)
plt.tight_layout()
plt.show()

if ooc_r_samples:
    print('Out-of-control samples (R chart):', ooc_r_samples)
else:
    print('All ranges are in control — process precision is stable.')
No description has been provided for this image 
All ranges are in control — process precision is stable.

Step 8 - Classify each sample as in-control or out-of-control¶

SQL fetches each sample's X-bar value.
Python's if/elif then assigns a status label - this is the standard SQL + Python pattern: SQL retrieves the data, Python applies the logic.

In [13]:
# SQL fetches each sample's X-bar; Python classifies it with a simple if/elif
sample_status = cursor.execute('''
    SELECT
        sample_no,
        ROUND((obs1 + obs2 + obs3 + obs4) / 4.0, 4)  AS xbar
    FROM shaft_measurements
    ORDER BY sample_no
''').fetchall()

print('Sample   X-bar      Status')
print('------   -------    ------')
for row in sample_status:
    sample = row[0]
    xbar   = row[1]

    if xbar > ucl_x:
        status = 'ABOVE UCL  *** Assignable Cause'
    elif xbar < lcl_x:
        status = 'BELOW LCL  *** Assignable Cause'
    else:
        status = 'In Control'

    print(f'  {sample:2d}     {xbar:.4f}   {status}')

Sample   X-bar      Status
------   -------    ------
   1     49.9875   In Control
   2     50.0500   In Control
   3     49.9825   In Control
   4     50.0125   In Control
   5     50.0125   In Control
   6     50.0125   In Control
   7     50.0175   In Control
   8     50.0250   In Control
   9     50.0375   In Control
  10     50.0000   In Control
  11     49.9875   In Control
  12     50.0250   In Control
  13     50.3875   ABOVE UCL  *** Assignable Cause
  14     50.0125   In Control
  15     49.9825   In Control
  16     50.0250   In Control
  17     50.0125   In Control
  18     49.5875   BELOW LCL  *** Assignable Cause
  19     50.0000   In Control
  20     50.0250   In Control

Step 9 - Attribute data: insert inspection results (p-chart)¶

A p-chart monitors the fraction defective when each unit is classified as pass/fail.
We inspect 100 units per sample. Samples 7 and 13 simulate a quality upset.

In [14]:
# 15 inspection samples, n=100 each
# Defective counts for samples 7 (18) and 13 (17) are intentionally elevated
defect_data = [
    (1,  100, 4),  (2,  100, 6),  (3,  100, 3),  (4,  100, 5),
    (5,  100, 7),  (6,  100, 4),  (7,  100, 18), (8,  100, 6),
    (9,  100, 3),  (10, 100, 5),  (11, 100, 4),  (12, 100, 6),
    (13, 100, 17), (14, 100, 5),  (15, 100, 3),
]

cursor.executemany('INSERT INTO defect_inspection VALUES (?,?,?)', defect_data)
conn.commit()

print(f'Inserted {len(defect_data)} inspection records into defect_inspection')

Inserted 15 inspection records into defect_inspection

Step 10 - p-Chart: compute limits and visualize¶

SQL sums all defectives and all units inspected to give us $\bar{p}$ (the overall fraction defective).
Python then calculates the control limits using $\bar{p}$ and the sample size.

$$\bar{p} = \frac{\sum d_i}{\sum n_i}, \qquad \sigma_p = \sqrt{\frac{\bar{p}(1-\bar{p})}{n}}$$

$$UCL_p = \bar{p} + 3\sigma_p, \qquad LCL_p = \bar{p} - 3\sigma_p \; (\ge 0)$$

Variables: $d_i$ = defectives in sample $i$; $n_i$ = sample size; $n$ = constant inspection batch size.

In [15]:
# Get per-sample defective proportions from SQL
p_rows = cursor.execute('''
    SELECT
        sample_no,
        defectives,
        sample_size,
        ROUND(CAST(defectives AS REAL) / sample_size, 4)  AS p_val
    FROM defect_inspection
    ORDER BY sample_no
''').fetchall()

# Compute overall p-bar: total defectives divided by total units inspected
result           = cursor.execute(
    'SELECT SUM(defectives), SUM(sample_size) FROM defect_inspection'
).fetchone()
total_defectives = result[0]
total_inspected  = result[1]
p_bar = round(total_defectives / total_inspected, 4)

# Control limits  (sample size n = 100 throughout)
n_insp = 100
sig_p  = math.sqrt(p_bar * (1 - p_bar) / n_insp)
ucl_p  = round(p_bar + 3 * sig_p, 4)
lcl_p  = max(0.0, round(p_bar - 3 * sig_p, 4))   # LCL cannot be negative

print('p-bar (overall fraction defective) :', p_bar)
print('p-Chart   UCL:', ucl_p, '  Centre:', p_bar, '  LCL:', lcl_p)

p-bar (overall fraction defective) : 0.064
p-Chart   UCL: 0.1374   Centre: 0.064   LCL: 0.0
In [16]:
# Unpack sample numbers and p-values into plain lists
sn_p  = []
pvals = []
for row in p_rows:
    sn_p.append(row[0])
    pvals.append(row[3])

# Find out-of-control samples
ooc_p_samples = []
ooc_p_values  = []
for row in p_rows:
    if row[3] > ucl_p:
        ooc_p_samples.append(row[0])
        ooc_p_values.append(row[3])

# Plot the p-chart
fig, ax = plt.subplots(figsize=(12, 4))

ax.plot(sn_p, pvals, marker='o', color='purple', linewidth=1.5, label='Fraction Defective (p)')
ax.axhline(p_bar, color='green', linewidth=1.5, linestyle='-',  label='p-bar (centre)')
ax.axhline(ucl_p, color='red',   linewidth=1.5, linestyle='--', label='UCL / LCL')
ax.axhline(lcl_p, color='red',   linewidth=1.5, linestyle='--')

ax.scatter(ooc_p_samples, ooc_p_values, color='red', s=120, zorder=5, label='Out of Control')

ax.set_title('p-Chart — Fraction Defective per Inspection Sample', fontweight='bold')
ax.set_xlabel('Sample Number')
ax.set_ylabel('Fraction Defective (p)')
ax.legend()
ax.set_xticks(sn_p)
plt.tight_layout()
plt.show()

print('Out-of-control samples:', ooc_p_samples)
No description has been provided for this image 
Out-of-control samples: [7, 13]

Step 11 - c-Chart: defects per unit¶

A c-chart counts the number of defects on each unit (not whether the unit passes or fails).
Defect counts follow a Poisson distribution, so the standard deviation is simply $\sqrt{\bar{c}}$:

$$\bar{c} = \frac{\sum c_i}{k}, \qquad UCL_c = \bar{c} + 3\sqrt{\bar{c}}, \qquad LCL_c = \bar{c} - 3\sqrt{\bar{c}} \; (\ge 0)$$

Variables: $c_i$ = defect count on unit $i$; $k$ = total number of units inspected.

Units 8 and 15 simulate a burst of defects — for example from tool wear or contamination.

In [17]:
# 20 finished parts, surface defect count per unit
c_raw = [
    (1,  3), (2,  4), (3,  2), (4,  5), (5,  3),
    (6,  4), (7,  3), (8, 11), (9,  2), (10, 4),
    (11, 3), (12, 5), (13, 2), (14, 4), (15,12),
    (16, 3), (17, 4), (18, 2), (19, 3), (20, 5),
]

cursor.executemany('INSERT INTO surface_defects VALUES (?,?)', c_raw)
conn.commit()

# Average defects per unit from SQL
result = cursor.execute(
    'SELECT AVG(CAST(defect_count AS REAL)) FROM surface_defects'
).fetchone()
c_bar = round(result[0], 4)

# Poisson-based control limits:  c-bar  ±  3 × sqrt(c-bar)
ucl_c = round(c_bar + 3 * math.sqrt(c_bar), 4)
lcl_c = max(0.0, round(c_bar - 3 * math.sqrt(c_bar), 4))

print('c-bar (average defects per unit) :', c_bar)
print('c-Chart   UCL:', ucl_c, '  Centre:', c_bar, '  LCL:', lcl_c)

c-bar (average defects per unit) : 4.2
c-Chart   UCL: 10.3482   Centre: 4.2   LCL: 0.0
In [18]:
# Unpack unit numbers and defect counts into plain lists
unit_nos   = []
def_counts = []
for row in c_raw:
    unit_nos.append(row[0])
    def_counts.append(row[1])

# Find out-of-control units
ooc_c_units  = []
ooc_c_values = []
for row in c_raw:
    if row[1] > ucl_c:
        ooc_c_units.append(row[0])
        ooc_c_values.append(row[1])

# Plot the c-chart
fig, ax = plt.subplots(figsize=(12, 4))

ax.plot(unit_nos, def_counts, marker='^', color='teal', linewidth=1.5, label='Defects per Unit')
ax.axhline(c_bar, color='green', linewidth=1.5, linestyle='-',  label='c-bar (centre)')
ax.axhline(ucl_c, color='red',   linewidth=1.5, linestyle='--', label='UCL / LCL')
ax.axhline(lcl_c, color='red',   linewidth=1.5, linestyle='--')

ax.scatter(ooc_c_units, ooc_c_values, color='red', s=120, zorder=5, label='Out of Control')

ax.set_title('c-Chart — Surface Defects per Manufactured Unit', fontweight='bold')
ax.set_xlabel('Unit Number')
ax.set_ylabel('Number of Defects')
ax.legend()
ax.set_xticks(unit_nos)
plt.tight_layout()
plt.show()

print('Out-of-control units:', ooc_c_units)
No description has been provided for this image 
Out-of-control units: [8, 15]

Step 12 - Process Capability (Cpk)¶

SPC tells you when a process goes out of control.
Cpk tells you how capable the process is — whether it fits comfortably within the engineering tolerances, even when it is in control.

Formulas¶

First, estimate the process standard deviation from the average range:

$$\hat{\sigma} = \frac{\bar{R}}{d_2}$$

Then measure how many 3σ gaps fit between the process mean and each spec limit:

$$C_p = \frac{USL - LSL}{6\hat{\sigma}}, \qquad C_{pu} = \frac{USL - \bar{\bar{X}}}{3\hat{\sigma}}, \qquad C_{pl} = \frac{\bar{\bar{X}} - LSL}{3\hat{\sigma}}$$

$$C_{pk} = \min(C_{pu},\; C_{pl})$$

Variables: $USL$ = upper spec limit; $LSL$ = lower spec limit; $\bar{\bar{X}}$ = grand mean; $\hat{\sigma}$ = estimated process standard deviation.
$C_p$ assumes a perfectly centred process. $C_{pk}$ accounts for any off-centre mean — it is always $\le C_p$.

How to interpret Cpk:

Cpk What it means
≥ 1.33 Capable — comfortable margin inside the spec limits
1.00 – 1.33 Marginally capable — just fitting inside
< 1.00 Not capable — defects are being produced
In [19]:
# Engineering tolerance: shaft must be 50.00 mm ± 0.40 mm
USL = 50.40   # Upper Specification Limit
LSL = 49.60   # Lower Specification Limit

# Estimate the process standard deviation from the average range
sigma_hat = r_bar / d2

# Cp: potential capability — assumes the process is perfectly centred
Cp  = round((USL - LSL) / (6 * sigma_hat), 4)

# Cpu and Cpl: how much room on each side of the mean
Cpu = round((USL - grand_mean) / (3 * sigma_hat), 4)   # upper side
Cpl = round((grand_mean - LSL) / (3 * sigma_hat), 4)   # lower side

# Cpk: actual capability — whichever side is tighter wins
Cpk = round(min(Cpu, Cpl), 4)

print('Spec limits : LSL =', LSL, 'mm   USL =', USL, 'mm')
print('Sigma-hat   :', round(sigma_hat, 4), 'mm')
print()
print('Cp  =', Cp,  ' — potential capability')
print('Cpu =', Cpu, ' — upper-half capability')
print('Cpl =', Cpl, ' — lower-half capability')
print('Cpk =', Cpk, ' — actual capability  (the key number)')
print()
if Cpk >= 1.33:
    print('Verdict: Process IS CAPABLE  (Cpk >= 1.33)')
elif Cpk >= 1.00:
    print('Verdict: Process is MARGINALLY capable  (1.00 <= Cpk < 1.33)')
else:
    print('Verdict: Process is NOT capable  (Cpk < 1.00 — defects expected)')

Spec limits : LSL = 49.6 mm   USL = 50.4 mm
Sigma-hat   : 0.0886 mm

Cp  = 1.5043  — potential capability
Cpu = 1.4701  — upper-half capability
Cpl = 1.5385  — lower-half capability
Cpk = 1.4701  — actual capability  (the key number)

Verdict: Process IS CAPABLE  (Cpk >= 1.33)
In [20]:
# Build a normal curve with numpy, then overlay the spec limits
x_vals = np.linspace(LSL - 0.2, USL + 0.2, 400)
y_vals = (1 / (sigma_hat * (2 * math.pi) ** 0.5)) * np.exp(
    -0.5 * ((x_vals - grand_mean) / sigma_hat) ** 2
)

fig, ax = plt.subplots(figsize=(9, 4))
ax.plot(x_vals, y_vals, color='steelblue', linewidth=2)
ax.fill_between(x_vals, y_vals, alpha=0.25, color='steelblue', label='Process distribution')
ax.axvline(LSL,        color='red',   linestyle='--', linewidth=1.5, label='LSL = ' + str(LSL))
ax.axvline(USL,        color='red',   linestyle='--', linewidth=1.5, label='USL = ' + str(USL))
ax.axvline(grand_mean, color='green', linestyle='-',  linewidth=1.5, label='Process mean')

ax.set_title('Process Capability - Cpk = ' + str(Cpk), fontweight='bold')
ax.set_xlabel('Shaft Diameter (mm)')
ax.set_ylabel('Probability Density')
ax.legend()
plt.tight_layout()
plt.show()
No description has been provided for this image

Step 13 - Close the connection¶

In [21]:
conn.close()
print('Connection to spc_demo.db closed.')

Connection to spc_demo.db closed.

Summary — What we built¶

Step SQL / Python What was done
1 sqlite3.connect Fresh spc_demo.db, 3 tables
2–3 CREATE TABLE, executemany Schema and data load
4 SELECT MAX/MIN row-wise X-bar and Range in SQL
5 SELECT AVG + Python multiply Grand mean, R-bar, control limits
6–7 Matplotlib + ax.scatter X-bar and R charts, OOC highlighted
8 SQL SELECT + Python if/elif Sample-by-sample status report
9–10 SQL SUM + Python math.sqrt p-chart (fraction defective)
11 SQL AVG + Python math.sqrt c-chart (defects per unit)
12 Python arithmetic + numpy curve Cpk and capability plot

Key SPC rules to remember:

  • A process is in control when all points are inside the limits and look random.
  • 8 consecutive points on one side of the centre line signal a shift — even without breaching the limits.
  • Cpk ≥ 1.33 is the common industry benchmark for a capable process.

Previous: day3_visualization.ipynb — bar, pie, line, scatter, histogram, and horizontal bar charts
Also see: day4_capstone.ipynb — full SQL + Python + CSV export capstone

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
Reference ASTM E2281 / ISO 8258 — Control Chart Constants
License MIT

Provided for educational use in manufacturing analytics training programmes.
For questions, corrections, or contributions, open an issue or pull request on GitHub.

© 2026 Prakash Ukhalkar · Python and SQL for Manufacturing · MIT License