Week 4 Operational Data Sourcing Lab

This lab package supports Data Sourcing, APIs, Web Scraping, SQL Querying, and Week 4 ETL/data-quality practice.

Assets

• operational_events_5000.csv: 5,000 operational records.
• mock_weather_api_response.json: local API-style JSON response.
• mock_market_prices.html: local web page with a scrapeable market-prices table.
• ops_lab.db: SQLite database with operational_events and depots tables.
• data_dictionary.csv: compact field descriptions.

Data-quality targets

• Total rows: 5,000
• Missing-record rows: 800, marked by missing_record_flag = 1 and quality_issue_type = 'MISSING_RECORD'
• Null-record rows: 200, marked by null_record_flag = 1 and quality_issue_type = 'NULL_RECORD'

API connection concept

import json
import pandas as pd
from pathlib import Path

# Local stand-in for an API response body.
payload = json.loads(Path("mock_weather_api_response.json").read_text())
weather_df = pd.DataFrame(payload["records"])
print(weather_df.head())

# Live API pattern:
# import requests
# response = requests.get(API_URL, headers={"Authorization": f"Bearer {API_KEY}"}, timeout=30)
# response.raise_for_status()
# api_df = pd.DataFrame(response.json()["records"])

Web scraping concept

import pandas as pd

tables = pd.read_html("mock_market_prices.html")
market_df = tables[0]
print(market_df.head())

SQL querying concept

import sqlite3
import pandas as pd

with sqlite3.connect("ops_lab.db") as conn:
    query = '''
    SELECT
        depot_name,
        product_code,
        COUNT(*) AS total_records,
        SUM(missing_record_flag) AS missing_records,
        SUM(null_record_flag) AS null_records,
        ROUND(AVG(pressure_psi), 2) AS avg_pressure_psi
    FROM operational_events
    GROUP BY depot_name, product_code
    ORDER BY missing_records DESC
    LIMIT 10;
    '''
    summary = pd.read_sql_query(query, conn)
print(summary)

Week 4 ETL challenge

1. Extract from the CSV, JSON API mock, HTML table, and SQLite database.
2. Transform timestamps, numeric fields, and source labels.
3. Validate row count, non-null critical fields, pressure bounds, unique record_id, and accepted status values.
4. Load a clean subset into a new SQLite table using an idempotent strategy.
5. Log every phase and write a manager-friendly quality summary.