# Praktik Mandiri 1 — Data Readiness Check & Cleaning Validation
## Participant Starter Notebook — Guided Technical Version

Notebook ini membantu kamu menjalankan workflow PM1 dengan **technical guardrails** agar tidak salah folder, salah source, atau salah export.

**Alur PM1:**
**Setup → Technical Check → Load → Intake → Grain & Key → Profiling → Findings → Verification → Rulebook → Cleaning → Validation → Raw Integrity → Export → Final Gate**

### Sebelum RUN
1. Buka **folder `Bootcamp_Data_Analyst` sebagai workspace** di Antigravity.
2. Simpan notebook ini di folder `notebooks/`.
3. Extract package `SB-Pxx`, lalu letakkan **5 raw files langsung** di `data/raw/pm1_swiftbite/`.
4. Buka `PM1_Data_Readiness_Working_Paper.xlsx` sebagai tempat mencatat decision & evidence.
5. Jalankan notebook **dari atas ke bawah**.

> Jika `TECHNICAL GATE: PASS`, **jangan mengubah path** di notebook.

### Guardrails
- Raw/source **tidak boleh diubah atau ditimpa**.
- Jangan menghapus row hanya karena terlihat aneh.
- Repeated `order_id` belum tentu duplicate.
- Finding belum otomatis menjadi cleaning action.
- Material change harus dapat ditelusuri: **Finding ID → Rule ID → Cleaning Log → Validation Evidence**.
- Notebook membantu sisi teknis. **Judgment, cleaning rule, dan interpretation tetap pekerjaan peserta.**

0. Identitas Praktik — EDIT BAGIAN INI

Ubah hanya **Nama** dan **Variant ID** sesuai package yang kamu terima.

Contoh Variant ID: `SB-P01`, `SB-P12`, `SB-P28`.

# ============================================================
# EDIT AREA 1 — IDENTITAS PESERTA
# ============================================================
PARTICIPANT_NAME = "Sintia"
VARIANT_ID = "SB-P04"   # contoh: SB-P01

name_ok = PARTICIPANT_NAME.strip() not in {"", "Isi Nama Kamu"}
variant_ok = (
    VARIANT_ID.startswith("SB-P")
    and len(VARIANT_ID) == 6
    and VARIANT_ID[4:].isdigit()
)

IDENTITY_GATE_PASS = bool(name_ok and variant_ok)

if IDENTITY_GATE_PASS:
    print("IDENTITY GATE: PASS")
    print("Participant :", PARTICIPANT_NAME)
    print("Variant ID  :", VARIANT_ID)
else:
    print("IDENTITY GATE: FAIL")
    print("→ Isi PARTICIPANT_NAME dan VARIANT_ID pada cell ini.")
    print("→ Format Variant ID harus seperti SB-P01 atau SB-P28.")
IDENTITY GATE: PASS
Participant : Sintia
Variant ID  : SB-P04

1. Technical Check — Workspace & Folder

Notebook akan mencari **Workspace Starter yang sama dari Sesi 1** berdasarkan keberadaan folder `data/` dan `notebooks/`.

**Jangan membuat workspace baru. Jangan copy-paste absolute path.**

Jika output menunjukkan `TECHNICAL GATE: PASS`, lanjut ke cell berikutnya tanpa mengubah path.

from pathlib import Path
import json
import hashlib
import pandas as pd
import numpy as np

try:
    import duckdb
    DUCKDB_AVAILABLE = True
except ImportError:
    duckdb = None
    DUCKDB_AVAILABLE = False

pd.set_option("display.max_columns", 100)
pd.set_option("display.max_rows", 100)
pd.set_option("display.width", 180)

def find_workspace_root(start=None):
    start = Path(start or Path.cwd()).resolve()
    for p in [start, *start.parents]:
        if (p / "data").is_dir() and (p / "notebooks").is_dir():
            return p
    return None

WORKSPACE_ROOT = find_workspace_root()

if WORKSPACE_ROOT is None:
    TECHNICAL_GATE_PASS = False
    RAW_DIR = Path("__WORKSPACE_NOT_FOUND__")
    PROCESSED_DIR = Path("__WORKSPACE_NOT_FOUND__")
    OUTPUT_DIR = Path("__WORKSPACE_NOT_FOUND__")
    DOCS_DIR = Path("__WORKSPACE_NOT_FOUND__")

    print("TECHNICAL GATE: FAIL")
    print("→ Workspace Starter tidak ditemukan.")
    print("→ Di Antigravity, buka folder Bootcamp_Data_Analyst sebagai workspace.")
    print("→ Pastikan folder data/ dan notebooks/ terlihat di Explorer.")
    print("→ Setelah itu RUN ulang cell ini. Jangan edit path secara manual.")
else:
    TECHNICAL_GATE_PASS = True
    RAW_DIR = WORKSPACE_ROOT / "data" / "raw" / "pm1_swiftbite"
    PROCESSED_DIR = WORKSPACE_ROOT / "data" / "processed" / "pm1_swiftbite"
    OUTPUT_DIR = WORKSPACE_ROOT / "outputs" / "pm1_swiftbite"
    DOCS_DIR = WORKSPACE_ROOT / "docs" / "pm1_swiftbite"

    for folder in [PROCESSED_DIR, OUTPUT_DIR, DOCS_DIR]:
        folder.mkdir(parents=True, exist_ok=True)

    print("TECHNICAL GATE: PASS")
    print("WORKSPACE_ROOT :", WORKSPACE_ROOT)
    print("RAW_DIR        :", RAW_DIR)
    print("PROCESSED_DIR  :", PROCESSED_DIR)
    print("OUTPUT_DIR     :", OUTPUT_DIR)
    print("DOCS_DIR       :", DOCS_DIR)
TECHNICAL GATE: PASS
WORKSPACE_ROOT : C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst
RAW_DIR        : C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite
PROCESSED_DIR  : C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
OUTPUT_DIR     : C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\outputs\pm1_swiftbite
DOCS_DIR       : C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\docs\pm1_swiftbite

Environment Smoke Test — RUN

Tujuan cell ini hanya memastikan library utama tersedia. Jika DuckDB belum aktif, workflow utama starter tetap dapat berjalan dengan pandas.

print("pandas :", pd.__version__)

try:
    import openpyxl
    EXCEL_ENGINE_AVAILABLE = True
    print("openpyxl:", openpyxl.__version__)
except ImportError:
    EXCEL_ENGINE_AVAILABLE = False
    print("openpyxl: NOT AVAILABLE")

if DUCKDB_AVAILABLE:
    print("DuckDB :", duckdb.__version__)
    try:
        con = duckdb.connect()
        print("DuckDB connection: OK")
    except Exception as e:
        print("DuckDB connection: WARNING —", type(e).__name__, str(e))
else:
    print("DuckDB : NOT AVAILABLE")
    print("INFO: workflow utama notebook tetap dapat dijalankan dengan pandas.")

if not EXCEL_ENGINE_AVAILABLE:
    print("ENVIRONMENT CHECK: NEEDS FIX")
    print("→ File XLSX membutuhkan openpyxl. Aktifkan environment sesuai setup Sesi 1.")
else:
    print("ENVIRONMENT CHECK: PASS")
pandas : 3.0.6
openpyxl: 3.1.5
DuckDB : 1.5.6
DuckDB connection: OK
ENVIRONMENT CHECK: PASS

2. Verify Source Package — RUN

Letakkan **5 file berikut langsung** di:

`data/raw/pm1_swiftbite/`

- `swiftbite_customers.json`
- `swiftbite_orders.xlsx`
- `swiftbite_order_items.csv`
- `swiftbite_payments.txt`
- `swiftbite_restaurants.csv`

**Jangan** meletakkan ZIP di folder tersebut dan **jangan** membuat folder `SB-Pxx` lagi di dalam `pm1_swiftbite/`.

EXPECTED_FILES = {
    "customers": "swiftbite_customers.json",
    "orders": "swiftbite_orders.xlsx",
    "order_items": "swiftbite_order_items.csv",
    "payments": "swiftbite_payments.txt",
    "restaurants": "swiftbite_restaurants.csv",
}

source_status = []

if not TECHNICAL_GATE_PASS:
    missing_files = list(EXPECTED_FILES.values())
    SOURCE_ACCESS_PASS = False
    print("SOURCE ACCESS CHECK: SKIPPED")
    print("→ Perbaiki TECHNICAL GATE terlebih dahulu.")
else:
    for logical_name, filename in EXPECTED_FILES.items():
        path = RAW_DIR / filename
        source_status.append({
            "logical_name": logical_name,
            "filename": filename,
            "exists": path.is_file(),
            "path": str(path),
        })

    source_status_df = pd.DataFrame(source_status)
    display(source_status_df)

    missing_files = source_status_df.loc[
        ~source_status_df["exists"], "filename"
    ].tolist()

    SOURCE_ACCESS_PASS = len(missing_files) == 0

    if SOURCE_ACCESS_PASS:
        print("SOURCE ACCESS CHECK: PASS")
        print("→ Semua 5 raw files ditemukan. Jangan ubah path.")
    else:
        print("SOURCE ACCESS CHECK: FAIL")
        print("File belum ditemukan:", missing_files)
        print("→ Extract package peserta.")
        print("→ Copy hanya 5 raw files langsung ke:", RAW_DIR)
        print("→ Jangan menaruh file di subfolder tambahan.")
  logical_name                   filename  exists                                                                                                                                           path
0    customers   swiftbite_customers.json    True   C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite\swiftbite_customers.json
1       orders      swiftbite_orders.xlsx    True      C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite\swiftbite_orders.xlsx
2  order_items  swiftbite_order_items.csv    True  C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite\swiftbite_order_items.csv
3     payments     swiftbite_payments.txt    True     C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite\swiftbite_payments.txt
4  restaurants  swiftbite_restaurants.csv    True  C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite\swiftbite_restaurants.csv
SOURCE ACCESS CHECK: PASS
→ Semua 5 raw files ditemukan. Jangan ubah path.

3. Load Raw Data & Schema Gate — RUN

Cell ini:
1. membaca kelima source secara aman,
2. menampilkan error per file jika ada,
3. memeriksa kolom minimum yang diperlukan Starter,
4. menyimpan **raw hash baseline** untuk pemeriksaan integritas di akhir.

Jika `RAW LOAD GATE: PASS` dan `SCHEMA GATE: PASS`, lanjutkan.

REQUIRED_COLUMNS = {
    "Customers": {
        "customer_id", "full_name", "email", "phone_number",
        "signup_date", "customer_zone", "customer_segment"
    },
    "Orders": {
        "order_id", "customer_id", "restaurant_id", "order_timestamp",
        "order_status", "subtotal_amount", "delivery_fee",
        "discount_amount", "total_amount"
    },
    "Order Items": {
        "order_item_id", "order_id", "line_number", "item_name",
        "item_category", "quantity", "unit_price",
        "line_discount", "line_amount"
    },
    "Payments": {
        "payment_id", "order_id", "payment_timestamp",
        "payment_method", "payment_status", "payment_amount"
    },
    "Restaurants": {
        "restaurant_id", "restaurant_name", "cuisine_type",
        "restaurant_zone", "price_tier", "active_status"
    },
}

def sha256_file(path: Path):
    h = hashlib.sha256()
    with open(path, "rb") as f:
        for chunk in iter(lambda: f.read(1024 * 1024), b""):
            h.update(chunk)
    return h.hexdigest()

def safe_read_source(label, path, reader):
    try:
        return reader(path), None
    except Exception as e:
        return None, f"{type(e).__name__}: {e}"

raw_tables = {}
load_errors = {}
RAW_HASH_BASELINE = {}

if not SOURCE_ACCESS_PASS:
    RAW_LOAD_GATE_PASS = False
    SCHEMA_GATE_PASS = False
    DATA_READY = False
    print("RAW LOAD GATE: SKIPPED")
    print("→ Perbaiki SOURCE ACCESS CHECK terlebih dahulu.")
else:
    readers = {
        "Customers": lambda p: pd.read_json(p),
        "Orders": lambda p: pd.read_excel(p),
        "Order Items": lambda p: pd.read_csv(p),
        "Payments": lambda p: pd.read_csv(p, sep="|"),
        "Restaurants": lambda p: pd.read_csv(p),
    }
    file_map = {
        "Customers": RAW_DIR / EXPECTED_FILES["customers"],
        "Orders": RAW_DIR / EXPECTED_FILES["orders"],
        "Order Items": RAW_DIR / EXPECTED_FILES["order_items"],
        "Payments": RAW_DIR / EXPECTED_FILES["payments"],
        "Restaurants": RAW_DIR / EXPECTED_FILES["restaurants"],
    }

    for label, path in file_map.items():
        df, err = safe_read_source(label, path, readers[label])
        if err:
            load_errors[label] = err
        else:
            raw_tables[label] = df
            RAW_HASH_BASELINE[label] = sha256_file(path)

    RAW_LOAD_GATE_PASS = len(load_errors) == 0 and len(raw_tables) == 5

    if RAW_LOAD_GATE_PASS:
        customers_raw = raw_tables["Customers"]
        orders_raw = raw_tables["Orders"]
        order_items_raw = raw_tables["Order Items"]
        payments_raw = raw_tables["Payments"]
        restaurants_raw = raw_tables["Restaurants"]
        print("RAW LOAD GATE: PASS")
    else:
        print("RAW LOAD GATE: FAIL")
        for label, err in load_errors.items():
            print(f"→ {label}: {err}")

    schema_rows = []
    if RAW_LOAD_GATE_PASS:
        for label, required in REQUIRED_COLUMNS.items():
            actual = set(raw_tables[label].columns)
            missing_cols = sorted(required - actual)
            schema_rows.append({
                "dataset": label,
                "required_columns": len(required),
                "actual_columns": len(actual),
                "missing_required_columns": ", ".join(missing_cols) if missing_cols else "",
                "status": "PASS" if not missing_cols else "FAIL",
            })

        schema_check_df = pd.DataFrame(schema_rows)
        display(schema_check_df)

        SCHEMA_GATE_PASS = (schema_check_df["status"] == "PASS").all()

        if SCHEMA_GATE_PASS:
            print("SCHEMA GATE: PASS")
        else:
            print("SCHEMA GATE: FAIL")
            print("→ Jangan lanjut cleaning. Periksa apakah package/variant yang digunakan sudah benar.")
    else:
        SCHEMA_GATE_PASS = False

    DATA_READY = bool(
        TECHNICAL_GATE_PASS
        and SOURCE_ACCESS_PASS
        and RAW_LOAD_GATE_PASS
        and SCHEMA_GATE_PASS
    )

if DATA_READY:
    print("DATA READY GATE: PASS")
else:
    print("DATA READY GATE: FAIL")
RAW LOAD GATE: PASS
       dataset  required_columns  actual_columns missing_required_columns status
0    Customers                 7               7                            PASS
1       Orders                 9              10                            PASS
2  Order Items                 9               9                            PASS
3     Payments                 6               7                            PASS
4  Restaurants                 6               7                            PASS
SCHEMA GATE: PASS
DATA READY GATE: PASS

Quick Source Preview — RUN

Pastikan nama kolom dan contoh record masuk akal. Ini **belum** berarti data sudah bersih.

if DATA_READY:
    for name, df in raw_tables.items():
        print(f"\n=== {name} | shape={df.shape} ===")
        display(df.head(3))
else:
    print("PREVIEW SKIPPED — DATA READY GATE belum PASS.")
=== Customers | shape=(176, 7) ===
  customer_id      full_name                      email  phone_number signup_date customer_zone customer_segment
0       C0001   Raka Maulana   rakamaulana1@example.com  082922107186  2025-08-17          East          Regular
1       C0002  Galih Maulana  galihmaulana2@example.com  081215263620  2025-09-08         South          Regular
2       C0003  Wahyu Maulana  wahyumaulana3@example.com  081744088774  2025-08-19       Central          Regular

=== Orders | shape=(354, 10) ===
  order_id customer_id restaurant_id      order_timestamp order_status promo_code  subtotal_amount  delivery_fee  discount_amount  total_amount
0   O00001       C0037         R0014  2026-03-19 22:04:00    completed    HEMAT15           164000         18000            15000        167000
1   O00002       C0044         R0019  2026-03-30 18:58:00    completed     SAVE10           118000         10000            15000        113000
2   O00003       C0051         R0024  2026-03-06 18:19:00    completed        NaN            79000         12000             5000         86000

=== Order Items | shape=(912, 9) ===
  order_item_id order_id  line_number  item_name item_category quantity  unit_price  line_discount  line_amount
0       OI00001   O00001            1  Rice Bowl          Main        2       32000           2000        62000
1       OI00002   O00001            2      Toast         Snack        3       16000              0        48000
2       OI00003   O00001            3  Nasi Ayam          Main        2       28000           2000        54000

=== Payments | shape=(398, 7) ===
  payment_id order_id payment_method payment_status  payment_amount    payment_timestamp  attempt_number
0     P00001   O00001       e_wallet     successful          167000  2026-03-19 22:07:00               1
1     P00002   O00002  bank_transfer     successful          113000  2026-03-30 19:01:00               1
2     P00003   O00003    credit_card     successful           86000  2026-03-06 18:22:00               1

=== Restaurants | shape=(26, 7) ===
  restaurant_id       restaurant_name cuisine_type restaurant_zone price_tier onboarding_date active_status
0         R0001  SwiftBite Partner 01      Dessert           North        Mid      2024-09-13        active
1         R0002  SwiftBite Partner 02   Indonesian           South        Mid      2024-06-09        active
2         R0003  SwiftBite Partner 03        Asian            West     Budget      2024-01-22        active

4. Intake Summary — Working Paper Sheet `01_Intake_Profiling`

Catat business meaning, expected grain, candidate key, row/record count, access status, dan key test result.

def intake_summary(df, dataset_name):
    return {
        "dataset": dataset_name,
        "rows": len(df),
        "columns": len(df.columns),
        "column_names": list(df.columns),
    }

if DATA_READY:
    intake_df = pd.DataFrame([
        intake_summary(customers_raw, "Customers"),
        intake_summary(orders_raw, "Orders"),
        intake_summary(order_items_raw, "Order Items"),
        intake_summary(payments_raw, "Payments"),
        intake_summary(restaurants_raw, "Restaurants"),
    ])
    display(intake_df)
       dataset  rows  columns                                                                                                                                     column_names
0    Customers   176        7                                                      [customer_id, full_name, email, phone_number, signup_date, customer_zone, customer_segment]
1       Orders   354       10  [order_id, customer_id, restaurant_id, order_timestamp, order_status, promo_code, subtotal_amount, delivery_fee, discount_amount, total_amount]
2  Order Items   912        9                               [order_item_id, order_id, line_number, item_name, item_category, quantity, unit_price, line_discount, line_amount]
3     Payments   398        7                                        [payment_id, order_id, payment_method, payment_status, payment_amount, payment_timestamp, attempt_number]
4  Restaurants    26        7                                      [restaurant_id, restaurant_name, cuisine_type, restaurant_zone, price_tier, onboarding_date, active_status]

5. Grain & Candidate Key Check — RUN

Repeated `order_id` pada **Order Items** atau **Payments** belum tentu duplicate karena grain-nya berbeda dari Orders.

Gunakan hasil check sebagai **evidence awal**, bukan cleaning action otomatis.

def key_check(df, key_cols, dataset_name):
    if isinstance(key_cols, str):
        key_cols = [key_cols]

    missing_key_rows = int(df[key_cols].isna().any(axis=1).sum())
    unique_key_count = int(df[key_cols].drop_duplicates().shape[0])
    duplicate_key_rows = int(df.duplicated(subset=key_cols, keep=False).sum())

    return pd.DataFrame([{
        "dataset": dataset_name,
        "key_cols": ", ".join(key_cols),
        "rows": len(df),
        "unique_key_count": unique_key_count,
        "missing_key_rows": missing_key_rows,
        "duplicate_key_rows": duplicate_key_rows,
        "candidate_key_unique": unique_key_count == len(df) and missing_key_rows == 0,
    }])

if DATA_READY:
    key_results = pd.concat([
        key_check(customers_raw, "customer_id", "Customers"),
        key_check(orders_raw, "order_id", "Orders"),
        key_check(order_items_raw, "order_item_id", "Order Items"),
        key_check(order_items_raw, ["order_id", "line_number"], "Order Items — candidate business key"),
        key_check(payments_raw, "payment_id", "Payments"),
        key_check(restaurants_raw, "restaurant_id", "Restaurants"),
    ], ignore_index=True)

    display(key_results)
                                dataset               key_cols  rows  unique_key_count  missing_key_rows  duplicate_key_rows  candidate_key_unique
0                             Customers            customer_id   176               175                 0                   2                 False
1                                Orders               order_id   354               354                 0                   0                  True
2                           Order Items          order_item_id   912               912                 0                   0                  True
3  Order Items — candidate business key  order_id, line_number   912               912                 0                   0                  True
4                              Payments             payment_id   398               398                 0                   0                  True
5                           Restaurants          restaurant_id    26                26                 0                   0                  True

Repeated Reference-Key Review

if DATA_READY:
    order_item_cardinality = (
        order_items_raw.groupby("order_id")
        .size()
        .rename("item_rows")
        .reset_index()
        .sort_values("item_rows", ascending=False)
    )

    payment_cardinality = (
        payments_raw.groupby("order_id")
        .size()
        .rename("payment_rows")
        .reset_index()
        .sort_values("payment_rows", ascending=False)
    )

    print("Order Items cardinality")
    display(order_item_cardinality.head(10))

    print("Payments cardinality")
    display(payment_cardinality.head(10))
Order Items cardinality
    order_id  item_rows
353   O00354          4
325   O00326          4
18    O00019          4
23    O00024          4
323   O00324          4
324   O00325          4
35    O00036          4
63    O00064          4
53    O00054          4
308   O00309          4
Payments cardinality
    order_id  payment_rows
13    O00014             2
23    O00024             2
352   O00353             2
304   O00305             2
301   O00302             2
291   O00292             2
274   O00275             2
269   O00270             2
265   O00266             2
245   O00246             2

6. Profiling

Profiling minimal:
- missing / placeholder,
- data type / format,
- category consistency,
- duplicate / key,
- suspicious numeric / formula,
- potential outlier,
- cross-table concern.

Helper berikut menghasilkan evidence awal, bukan automatic judgment.

def basic_profile(df, dataset_name):
    return pd.DataFrame([{
        "dataset": dataset_name,
        "rows": len(df),
        "columns": len(df.columns),
        "rows_in_exact_duplicate_groups": int(df.duplicated(keep=False).sum()),
        "exact_duplicate_extras": int(df.duplicated(keep="first").sum()),
        "null_cells": int(df.isna().sum().sum()),
    }])

def column_profile(df):
    rows = []
    for col in df.columns:
        s = df[col]
        rows.append({
            "column": col,
            "dtype": str(s.dtype),
            "null_count": int(s.isna().sum()),
            "null_pct": round(float(s.isna().mean()), 4),
            "unique_count": int(s.nunique(dropna=True)),
            "sample_values": list(s.dropna().astype(str).unique()[:5]),
        })
    return pd.DataFrame(rows)

if DATA_READY:
    overall_profile = pd.concat(
        [basic_profile(df, name) for name, df in raw_tables.items()],
        ignore_index=True
    )
    display(overall_profile)
       dataset  rows  columns  rows_in_exact_duplicate_groups  exact_duplicate_extras  null_cells
0    Customers   176        7                               2                       1           0
1       Orders   354       10                               0                       0         167
2  Order Items   912        9                               0                       0           0
3     Payments   398        7                               0                       0           0
4  Restaurants    26        7                               0                       0           0

Column-Level Profiling

# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
    display(column_profile(customers_raw))
             column dtype  null_count  null_pct  unique_count                                                                                                                           sample_values
0       customer_id   str           0       0.0           175                                                                                                     [C0001, C0002, C0003, C0004, C0005]
1         full_name   str           0       0.0           133                                                              [Raka Maulana, Galih Maulana, Wahyu Maulana, Laras Saputra, Jihan Nugroho]
2             email   str           0       0.0           175  [rakamaulana1@example.com, galihmaulana2@example.com, wahyumaulana3@example.com, larassaputra4@example.com, jihannugroho5@example.com]
3      phone_number   str           0       0.0           175                                                                  [082922107186, 081215263620, 081744088774, 081546741417, 086576946498]
4       signup_date   str           0       0.0           117                                                                            [2025-08-17, 2025-09-08, 2025-08-19, 2025-06-09, 2025-08-01]
5     customer_zone   str           0       0.0             5                                                                                                     [East, South, Central, West, North]
6  customer_segment   str           0       0.0             5                                                                                                   [Regular, Loyal, New, NEW , REGULAR ]

Category Review Helper

def category_review(df, column, top_n=30):
    out = (
        df[column]
        .value_counts(dropna=False)
        .rename_axis(column)
        .reset_index(name="count")
    )
    out["pct"] = (out["count"] / len(df)).round(4)
    return out.head(top_n)

# Contoh:
# display(category_review(customers_raw, "customer_segment"))
# display(category_review(orders_raw, "order_status"))
# display(category_review(payments_raw, "payment_method"))
# display(category_review(restaurants_raw, "price_tier"))

Numeric Review Helper

def numeric_profile(df):
    numeric_cols = df.select_dtypes(include="number").columns.tolist()
    if not numeric_cols:
        return pd.DataFrame()
    return df[numeric_cols].describe().T

# Contoh:
# display(numeric_profile(orders_raw))
# display(numeric_profile(order_items_raw))

7. Candidate Findings Register

Gunakan judgment:
- `FIX`
- `STANDARDIZE`
- `KEEP`
- `INVESTIGATE`

Jangan isi judgment sebelum verification evidence cukup.

FINDING_COLUMNS = [
    "finding_id",
    "dataset",
    "field_key",
    "finding_evidence",
    "why_it_matters",
    "verification_method",
    "verification_evidence_result",
    "judgment",
    "reason_for_judgment",
    "cleaning_rule_id",
]

finding_rows = [
    {
        "finding_id": "F-01",
        "dataset": "Customers",
        "field_key": "customer_id / full row",
        "finding_evidence": "1 exact full-row duplicate pada customer_id C0031 (Adi Hidayat, index 30 & 175)",
        "why_it_matters": "Melanggar keunikan candidate key pada grain customer (1 baris = 1 customer)",
        "verification_method": "Perbandingan seluruh kolom dengan cust.duplicated(keep=False)",
        "verification_evidence_result": "Record index 30 & 175 identik 100% pada semua kolom",
        "judgment": "FIX",
        "reason_for_judgment": "Full-row duplicate merupakan artefak redundansi teknis yang harus dibersihkan",
        "cleaning_rule_id": "CR-01",
    },
    {
        "finding_id": "F-02",
        "dataset": "Customers",
        "field_key": "phone_number, email",
        "finding_evidence": "String placeholder 'NULL' (C0054) dan 'N/A' (C0017)",
        "why_it_matters": "Terbaca sebagai data non-null valid; mengaburkan audit kelengkapan kontak",
        "verification_method": "Regex matching terhadap pola nomor telepon dan email standar",
        "verification_evidence_result": "Dikonfirmasi nilai literal 'NULL' dan 'N/A' bukan format kontak valid",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Standardisasi ke representasi missing standar (None / pd.NA) tanpa tebakan data",
        "cleaning_rule_id": "CR-02",
    },
    {
        "finding_id": "F-03",
        "dataset": "Customers",
        "field_key": "customer_segment",
        "finding_evidence": "Inkonsistensi format teks: 'NEW ' (1) dan 'REGULAR ' (1) memiliki trailing space & kapital",
        "why_it_matters": "Menyebabkan fragmentasi kategori segmentasi pelanggan saat agregasi analitik",
        "verification_method": "Value counts dan pemeriksaan whitespace/casing: cust['customer_segment'].unique()",
        "verification_evidence_result": "Ditemukan variasi non-standar berdampingan dengan 'New', 'Regular', 'Loyal'",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Standardisasi trim whitespace dan Title Case mengembalikan nilai ke kelas kanonikal",
        "cleaning_rule_id": "CR-03",
    },
    {
        "finding_id": "F-04",
        "dataset": "Orders",
        "field_key": "order_status",
        "finding_evidence": "Inkonsistensi format teks: 'COMPLETED ' (2) memiliki trailing space & huruf kapital",
        "why_it_matters": "Menyebabkan kegagalan filter order status ('completed' vs 'COMPLETED ')",
        "verification_method": "Value counts: orders['order_status'].value_counts()",
        "verification_evidence_result": "Ditemukan 2 record bernilai 'COMPLETED ' di samping 'completed' dan 'cancelled'",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Standardisasi trim whitespace dan lower case mengembalikan nilai ke kelas kanonikal",
        "cleaning_rule_id": "CR-04",
    },
    {
        "finding_id": "F-05",
        "dataset": "Payments",
        "field_key": "payment_method",
        "finding_evidence": "Inkonsistensi format teks: 'CREDIT CARD ' (1) dan 'CASH ' (1) huruf kapital & trailing space",
        "why_it_matters": "Menyebabkan fragmentasi metode pembayaran saat analisis channel pembayaran",
        "verification_method": "Value counts: payments['payment_method'].value_counts()",
        "verification_evidence_result": "Ditemukan variasi kapital berspasi berdampingan dengan format lower_snake_case",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Standardisasi trim whitespace, lower case, dan replace spasi menjadi underscore",
        "cleaning_rule_id": "CR-05",
    },
    {
        "finding_id": "F-06",
        "dataset": "Restaurants",
        "field_key": "price_tier",
        "finding_evidence": "Inkonsistensi format teks: 'MID ' (2) memiliki trailing space & huruf kapital",
        "why_it_matters": "Menyebabkan kategori tier harga terfragmentasi saat profiling merchant",
        "verification_method": "Value counts: rest['price_tier'].value_counts()",
        "verification_evidence_result": "Ditemukan 2 record 'MID ' berdampingan dengan 'Mid', 'Budget', 'Premium'",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Standardisasi trim whitespace dan Title Case mengembalikan nilai ke kelas kanonikal",
        "cleaning_rule_id": "CR-06",
    },
    {
        "finding_id": "F-07",
        "dataset": "Order Items",
        "field_key": "quantity",
        "finding_evidence": "Kolom quantity bertipe string/object dengan unit 'pcs' (OI00071: '3 pcs', OI00215: '2 pcs')",
        "why_it_matters": "Mencegah operasi aritmatika numerik dan validasi formula pada order items",
        "verification_method": "Deteksi karakter non-digit: items[items['quantity'].str.contains(r'[^\d]')]",
        "verification_evidence_result": "Unit teks terverifikasi; angka numerik cocok persis dengan line_amount / unit_price",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Ekstraksi digit numerik dan konversi kolom quantity menjadi integer",
        "cleaning_rule_id": "CR-07",
    },
    {
        "finding_id": "F-08",
        "dataset": "Order Items",
        "field_key": "line_amount",
        "finding_evidence": "Formula error pada 2 item: OI00131 bernilai 39,000 (harus 32,000); OI00511 bernilai 11,000 (harus 16,000)",
        "why_it_matters": "Menyebabkan salah hitung nilai transaksi item dan selisih rekonsiliasi order subtotal",
        "verification_method": "Kalkulasi expected_line_amount = qty * unit_price - discount dicocokkan ke header subtotal",
        "verification_evidence_result": "Pada O00053 dan O00204, subtotal order cocok persis dengan hasil formula yang benar",
        "judgment": "FIX",
        "reason_for_judgment": "Koreksi line_amount sesuai formula bisnis item didukung evidence order subtotal",
        "cleaning_rule_id": "CR-08",
    },
    {
        "finding_id": "F-09",
        "dataset": "Orders",
        "field_key": "subtotal_amount, total_amount",
        "finding_evidence": "Rekonsiliasi mismatch pada O00201: subtotal 224,000 vs sum line items 214,000 (selisih 10,000)",
        "why_it_matters": "Inkonsistensi header vs item detail dan overstatement total pemesanan (222,000 vs 212,000)",
        "verification_method": "Rekonsiliasi cross-table antara item sum (214,000) dan Payment P00218 (212,000)",
        "verification_evidence_result": "Item detail terbukti valid (sum=214k); pelanggan riil membayar 212k (214k + 8k - 10k)",
        "judgment": "FIX",
        "reason_for_judgment": "Koreksi subtotal_amount menjadi 214,000 dan total_amount menjadi 212,000 sesuai evidence riil",
        "cleaning_rule_id": "CR-09",
    },
    {
        "finding_id": "F-10",
        "dataset": "Payments",
        "field_key": "order_id (repeated keys)",
        "finding_evidence": "Terdapat 44 order dengan 2 payment attempts (total 88 baris pada Payments)",
        "why_it_matters": "Potensi dicurigai sebagai duplicate payment jika tidak memahami grain data pembayaran",
        "verification_method": "Pemeriksaan attempt_number, payment_status, timestamp, dan status order terkait",
        "verification_evidence_result": "Record memiliki payment_id unik, attempt 1 lalu 2 (failed->success atau success->refunded)",
        "judgment": "KEEP",
        "reason_for_judgment": "Legitimate business behavior untuk multi-attempt & refund logging; grain adalah payment attempt",
        "cleaning_rule_id": "",
    },
    {
        "finding_id": "F-11",
        "dataset": "Orders",
        "field_key": "total_amount / outlier",
        "finding_evidence": "Transaksi total ekstrem tinggi pada O00084 (total 1,068,000; subtotal 1,071,000)",
        "why_it_matters": "Dapat disalahartikan sebagai outlier input error numerik jika hanya melihat sebaran statistik",
        "verification_method": "Verifikasi rincian menu pada Order Items dan log pembayaran P00088",
        "verification_evidence_result": "O00084 memesan paket catering premium (unit 455k x 2); pembayaran credit_card sukses",
        "judgment": "KEEP",
        "reason_for_judgment": "Transaksi sah bernilai besar (valid bulk/catering transaction); tidak boleh diubah/dihapus",
        "cleaning_rule_id": "",
    },
    {
        "finding_id": "F-12",
        "dataset": "Orders",
        "field_key": "promo_code (missing values)",
        "finding_evidence": "167 records (47.18%) bernilai missing/NaN pada kolom promo_code",
        "why_it_matters": "Potensi dicurigai sebagai data quality issue missing value",
        "verification_method": "Analisis relasi promo_code terhadap discount_amount transaksi",
        "verification_evidence_result": "Missing bersifat legitimate structural missing (order reguler tanpa voucher promo)",
        "judgment": "KEEP",
        "reason_for_judgment": "Missing value dipertahankan as-is; tidak boleh diimputasi dengan nilai rekaan",
        "cleaning_rule_id": "",
    },
    {
        "finding_id": "F-13",
        "dataset": "Restaurants",
        "field_key": "active_status",
        "finding_evidence": "2 mitra restoran berstatus 'inactive' (R0019, R0024) memiliki histori 28 order",
        "why_it_matters": "Potensi dicurigai sebagai merchant invalid atau referensi usang",
        "verification_method": "Pemeriksaan tanggal onboarding_date dan order_timestamp transaksi terkait",
        "verification_evidence_result": "Transaksi dibuat saat restoran aktif sebelum dinonaktifkan; foreign key valid 100%",
        "judgment": "KEEP",
        "reason_for_judgment": "Status operasional sah; mempertahankan merchant inactive menjaga keutuhan data histori",
        "cleaning_rule_id": "",
    },
]

findings = pd.DataFrame(finding_rows, columns=FINDING_COLUMNS)
display(findings)
   finding_id      dataset                      field_key                                                                                           finding_evidence                                                                                 why_it_matters                                                                         verification_method                                                                verification_evidence_result     judgment                                                                              reason_for_judgment cleaning_rule_id
0        F-01    Customers         customer_id / full row                            1 exact full-row duplicate pada customer_id C0031 (Adi Hidayat, index 30 & 175)                    Melanggar keunikan candidate key pada grain customer (1 baris = 1 customer)                               Perbandingan seluruh kolom dengan cust.duplicated(keep=False)                                         Record index 30 & 175 identik 100% pada semua kolom          FIX                    Full-row duplicate merupakan artefak redundansi teknis yang harus dibersihkan            CR-01
1        F-02    Customers            phone_number, email                                                        String placeholder 'NULL' (C0054) dan 'N/A' (C0017)                      Terbaca sebagai data non-null valid; mengaburkan audit kelengkapan kontak                                Regex matching terhadap pola nomor telepon dan email standar                       Dikonfirmasi nilai literal 'NULL' dan 'N/A' bukan format kontak valid  STANDARDIZE                  Standardisasi ke representasi missing standar (None / pd.NA) tanpa tebakan data            CR-02
2        F-03    Customers               customer_segment                 Inkonsistensi format teks: 'NEW ' (1) dan 'REGULAR ' (1) memiliki trailing space & kapital                   Menyebabkan fragmentasi kategori segmentasi pelanggan saat agregasi analitik           Value counts dan pemeriksaan whitespace/casing: cust['customer_segment'].unique()                 Ditemukan variasi non-standar berdampingan dengan 'New', 'Regular', 'Loyal'  STANDARDIZE              Standardisasi trim whitespace dan Title Case mengembalikan nilai ke kelas kanonikal            CR-03
3        F-04       Orders                   order_status                        Inkonsistensi format teks: 'COMPLETED ' (2) memiliki trailing space & huruf kapital                        Menyebabkan kegagalan filter order status ('completed' vs 'COMPLETED ')                                         Value counts: orders['order_status'].value_counts()             Ditemukan 2 record bernilai 'COMPLETED ' di samping 'completed' dan 'cancelled'  STANDARDIZE              Standardisasi trim whitespace dan lower case mengembalikan nilai ke kelas kanonikal            CR-04
4        F-05     Payments                 payment_method               Inkonsistensi format teks: 'CREDIT CARD ' (1) dan 'CASH ' (1) huruf kapital & trailing space                     Menyebabkan fragmentasi metode pembayaran saat analisis channel pembayaran                                     Value counts: payments['payment_method'].value_counts()              Ditemukan variasi kapital berspasi berdampingan dengan format lower_snake_case  STANDARDIZE                  Standardisasi trim whitespace, lower case, dan replace spasi menjadi underscore            CR-05
5        F-06  Restaurants                     price_tier                              Inkonsistensi format teks: 'MID ' (2) memiliki trailing space & huruf kapital                         Menyebabkan kategori tier harga terfragmentasi saat profiling merchant                                             Value counts: rest['price_tier'].value_counts()                    Ditemukan 2 record 'MID ' berdampingan dengan 'Mid', 'Budget', 'Premium'  STANDARDIZE              Standardisasi trim whitespace dan Title Case mengembalikan nilai ke kelas kanonikal            CR-06
6        F-07  Order Items                       quantity                Kolom quantity bertipe string/object dengan unit 'pcs' (OI00071: '3 pcs', OI00215: '2 pcs')                      Mencegah operasi aritmatika numerik dan validasi formula pada order items                 Deteksi karakter non-digit: items[items['quantity'].str.contains(r'[^\d]')]         Unit teks terverifikasi; angka numerik cocok persis dengan line_amount / unit_price  STANDARDIZE                              Ekstraksi digit numerik dan konversi kolom quantity menjadi integer            CR-07
7        F-08  Order Items                    line_amount  Formula error pada 2 item: OI00131 bernilai 39,000 (harus 32,000); OI00511 bernilai 11,000 (harus 16,000)          Menyebabkan salah hitung nilai transaksi item dan selisih rekonsiliasi order subtotal  Kalkulasi expected_line_amount = qty * unit_price - discount dicocokkan ke header subtotal         Pada O00053 dan O00204, subtotal order cocok persis dengan hasil formula yang benar          FIX                  Koreksi line_amount sesuai formula bisnis item didukung evidence order subtotal            CR-08
8        F-09       Orders  subtotal_amount, total_amount             Rekonsiliasi mismatch pada O00201: subtotal 224,000 vs sum line items 214,000 (selisih 10,000)     Inkonsistensi header vs item detail dan overstatement total pemesanan (222,000 vs 212,000)             Rekonsiliasi cross-table antara item sum (214,000) dan Payment P00218 (212,000)       Item detail terbukti valid (sum=214k); pelanggan riil membayar 212k (214k + 8k - 10k)          FIX    Koreksi subtotal_amount menjadi 214,000 dan total_amount menjadi 212,000 sesuai evidence riil            CR-09
9        F-10     Payments       order_id (repeated keys)                                 Terdapat 44 order dengan 2 payment attempts (total 88 baris pada Payments)          Potensi dicurigai sebagai duplicate payment jika tidak memahami grain data pembayaran             Pemeriksaan attempt_number, payment_status, timestamp, dan status order terkait  Record memiliki payment_id unik, attempt 1 lalu 2 (failed->success atau success->refunded)         KEEP  Legitimate business behavior untuk multi-attempt & refund logging; grain adalah payment attempt                 
10       F-11       Orders         total_amount / outlier                           Transaksi total ekstrem tinggi pada O00084 (total 1,068,000; subtotal 1,071,000)  Dapat disalahartikan sebagai outlier input error numerik jika hanya melihat sebaran statistik                          Verifikasi rincian menu pada Order Items dan log pembayaran P00088        O00084 memesan paket catering premium (unit 455k x 2); pembayaran credit_card sukses         KEEP       Transaksi sah bernilai besar (valid bulk/catering transaction); tidak boleh diubah/dihapus                 
11       F-12       Orders    promo_code (missing values)                                            167 records (47.18%) bernilai missing/NaN pada kolom promo_code                                     Potensi dicurigai sebagai data quality issue missing value                               Analisis relasi promo_code terhadap discount_amount transaksi          Missing bersifat legitimate structural missing (order reguler tanpa voucher promo)         KEEP                    Missing value dipertahankan as-is; tidak boleh diimputasi dengan nilai rekaan                 
12       F-13  Restaurants                  active_status                             2 mitra restoran berstatus 'inactive' (R0019, R0024) memiliki histori 28 order                                Potensi dicurigai sebagai merchant invalid atau referensi usang                   Pemeriksaan tanggal onboarding_date dan order_timestamp transaksi terkait          Transaksi dibuat saat restoran aktif sebelum dinonaktifkan; foreign key valid 100%         KEEP           Status operasional sah; mempertahankan merchant inactive menjaga keutuhan data histori                 
<string>:93: SyntaxWarning: invalid escape sequence '\d'

Template menambahkan finding

```python
new_finding = {
"finding_id": "F-01",
"dataset": "...",
"field_key": "...",
"finding_evidence": "...",
"why_it_matters": "...",
"verification_method": "...",
"verification_evidence_result": "...",
"judgment": "FIX / STANDARDIZE / KEEP / INVESTIGATE",
"reason_for_judgment": "...",
"cleaning_rule_id": "CR-01 atau kosong",
}
findings.loc[len(findings)] = new_finding
```

8. Cross-Table Verification

def fk_check(child_df, parent_df, child_key, parent_key, relationship_name):
    child_keys = child_df[[child_key]].drop_duplicates()
    parent_keys = parent_df[[parent_key]].drop_duplicates()

    merged = child_keys.merge(
        parent_keys,
        left_on=child_key,
        right_on=parent_key,
        how="left",
        indicator=True,
    )

    unmatched = merged.loc[merged["_merge"] == "left_only", child_key]

    return {
        "relationship": relationship_name,
        "child_distinct_keys": len(child_keys),
        "unmatched_count": len(unmatched),
        "unmatched_sample": unmatched.head(10).tolist(),
    }

if DATA_READY:
    relationship_checks = pd.DataFrame([
        fk_check(orders_raw, customers_raw, "customer_id", "customer_id", "Orders → Customers"),
        fk_check(orders_raw, restaurants_raw, "restaurant_id", "restaurant_id", "Orders → Restaurants"),
        fk_check(order_items_raw, orders_raw, "order_id", "order_id", "Order Items → Orders"),
        fk_check(payments_raw, orders_raw, "order_id", "order_id", "Payments → Orders"),
    ])
    display(relationship_checks)
           relationship  child_distinct_keys  unmatched_count unmatched_sample
0    Orders → Customers                   25                0               []
1  Orders → Restaurants                   26                0               []
2  Order Items → Orders                  354                0               []
3     Payments → Orders                  354                0               []

9. Business-Rule Verification

### Order Items
`line_amount = quantity × unit_price − line_discount`

### Orders
`total_amount = subtotal_amount + delivery_fee − discount_amount`

Cell ini hanya membuat verification view. Tidak melakukan cleaning.

if DATA_READY:
    item_verify = order_items_raw.copy()
    item_verify["expected_line_amount"] = (
        pd.to_numeric(item_verify["quantity"], errors="coerce")
        * pd.to_numeric(item_verify["unit_price"], errors="coerce")
        - pd.to_numeric(item_verify["line_discount"], errors="coerce")
    )
    item_verify["line_formula_match"] = (
        pd.to_numeric(item_verify["line_amount"], errors="coerce")
        == item_verify["expected_line_amount"]
    )

    display(
        item_verify[
            ["order_item_id", "order_id", "quantity", "unit_price",
             "line_discount", "line_amount", "expected_line_amount", "line_formula_match"]
        ].head()
    )

    order_verify = orders_raw.copy()
    order_verify["expected_total_amount"] = (
        pd.to_numeric(order_verify["subtotal_amount"], errors="coerce")
        + pd.to_numeric(order_verify["delivery_fee"], errors="coerce")
        - pd.to_numeric(order_verify["discount_amount"], errors="coerce")
    )
    order_verify["order_formula_match"] = (
        pd.to_numeric(order_verify["total_amount"], errors="coerce")
        == order_verify["expected_total_amount"]
    )

    display(
        order_verify[
            ["order_id", "subtotal_amount", "delivery_fee", "discount_amount",
             "total_amount", "expected_total_amount", "order_formula_match"]
        ].head()
    )
  order_item_id order_id quantity  unit_price  line_discount  line_amount  expected_line_amount  line_formula_match
0       OI00001   O00001        2       32000           2000        62000               62000.0                True
1       OI00002   O00001        3       16000              0        48000               48000.0                True
2       OI00003   O00001        2       28000           2000        54000               54000.0                True
3       OI00004   O00002        3       28000              0        84000               84000.0                True
4       OI00005   O00002        2       18000           2000        34000               34000.0                True
  order_id  subtotal_amount  delivery_fee  discount_amount  total_amount  expected_total_amount  order_formula_match
0   O00001           164000         18000            15000        167000                 167000                 True
1   O00002           118000         10000            15000        113000                 113000                 True
2   O00003            79000         12000             5000         86000                  86000                 True
3   O00004           106000         15000            15000        106000                 106000                 True
4   O00005            63000         15000            15000         63000                  63000                 True

Orders ↔ Order Items Reconciliation

Lakukan reconciliation setelah memahami item-level validity. Jangan langsung mengoreksi Orders jika item detail sendiri belum diverifikasi.

if DATA_READY:
    item_amount_numeric = pd.to_numeric(order_items_raw["line_amount"], errors="coerce")

    item_agg_raw = (
        order_items_raw.assign(_line_amount_numeric=item_amount_numeric)
        .groupby("order_id", as_index=False)["_line_amount_numeric"]
        .sum(min_count=1)
        .rename(columns={"_line_amount_numeric": "sum_line_amount"})
    )

    order_item_recon_raw = orders_raw[
        ["order_id", "subtotal_amount"]
    ].merge(item_agg_raw, on="order_id", how="left")

    order_item_recon_raw["subtotal_numeric"] = pd.to_numeric(
        order_item_recon_raw["subtotal_amount"], errors="coerce"
    )
    order_item_recon_raw["reconcile_match"] = (
        order_item_recon_raw["subtotal_numeric"]
        == order_item_recon_raw["sum_line_amount"]
    )

    display(order_item_recon_raw.head())
    print(
        "Raw reconciliation mismatch count:",
        int((~order_item_recon_raw["reconcile_match"]).sum())
    )
  order_id  subtotal_amount  sum_line_amount  subtotal_numeric  reconcile_match
0   O00001           164000           164000            164000             True
1   O00002           118000           118000            118000             True
2   O00003            79000            79000             79000             True
3   O00004           106000           106000            106000             True
4   O00005            63000            63000             63000             True
Raw reconciliation mismatch count: 3

10. Cleaning Rulebook

Rulebook dibuat setelah verification dan sebelum coding.

Gunakan hanya `FIX` atau `STANDARDIZE` sebagai cleaning decision. Finding `KEEP` tidak boleh diubah hanya agar data terlihat lebih rapi.

RULE_COLUMNS = [
    "rule_id",
    "source_finding_id",
    "dataset",
    "trigger_condition",
    "decision",
    "cleaning_action",
    "reason_evidence",
    "implementation_note",
    "validation_criteria",
    "rule_status",
]

rule_rows = [
    {
        "rule_id": "CR-01",
        "source_finding_id": "F-01",
        "dataset": "Customers",
        "trigger_condition": "Terdapat baris duplikat identik 100% pada seluruh atribut (C0031)",
        "decision": "FIX",
        "cleaning_action": "Hapus exact duplicate row dengan mempertahankan baris pertama (drop_duplicates keep='first')",
        "reason_evidence": "Duplikasi baris penuh terbukti merupakan artefak redundansi teknis tanpa nilai unik baru",
        "implementation_note": "Terapkan hanya pada working copy customers_clean; jangan modifikasi source raw",
        "validation_criteria": "Baris berkurang 1 (176 -> 175), customer_id unik 100% tanpa duplicate key",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-02",
        "source_finding_id": "F-02",
        "dataset": "Customers",
        "trigger_condition": "Nilai phone_number == 'NULL' atau email == 'N/A'",
        "decision": "STANDARDIZE",
        "cleaning_action": "Ganti string placeholder literal menjadi None / pd.NA (representasi missing standar)",
        "reason_evidence": "Nilai placeholder bukan data kontak valid dan mengaburkan pengukuran missingness",
        "implementation_note": "Gunakan .replace({'NULL': None, 'N/A': None}) pada kolom phone_number dan email",
        "validation_criteria": "0 string placeholder tersisa; field kontak yang kosong terdeteksi sebagai null standar",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-03",
        "source_finding_id": "F-03",
        "dataset": "Customers",
        "trigger_condition": "Nilai customer_segment memiliki trailing whitespace atau huruf kapital tidak seragam",
        "decision": "STANDARDIZE",
        "cleaning_action": "Lakukan trim whitespace dan format Title Case (.astype(str).str.strip().str.title())",
        "reason_evidence": "Standardisasi mengembalikan konsistensi kategori kanonikal ('New', 'Regular', 'Loyal')",
        "implementation_note": "Terapkan pada seluruh kolom customer_segment working copy",
        "validation_criteria": "Nilai unik hanya terdiri dari 3 kelas kanonikal: ['Loyal', 'New', 'Regular']",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-04",
        "source_finding_id": "F-04",
        "dataset": "Orders",
        "trigger_condition": "Nilai order_status memiliki trailing whitespace atau huruf kapital ('COMPLETED ')",
        "decision": "STANDARDIZE",
        "cleaning_action": "Lakukan trim whitespace dan format lower case (.astype(str).str.strip().str.lower())",
        "reason_evidence": "Standardisasi menyatukan variasi teks ke kelas kanonikal resmi ('completed', 'cancelled')",
        "implementation_note": "Terapkan pada kolom order_status working copy orders_clean",
        "validation_criteria": "Nilai unik hanya terdiri dari 2 kelas kanonikal: ['cancelled', 'completed']",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-05",
        "source_finding_id": "F-05",
        "dataset": "Payments",
        "trigger_condition": "Nilai payment_method memiliki trailing whitespace atau huruf kapital berspasi ('CREDIT CARD ', 'CASH ')",
        "decision": "STANDARDIZE",
        "cleaning_action": "Lakukan trim, lower case, dan replace spasi menjadi underscore ('credit_card', 'cash')",
        "reason_evidence": "Standardisasi menyelaraskan format penamaan kategori ke lower_snake_case",
        "implementation_note": "Terapkan pada kolom payment_method working copy payments_clean",
        "validation_criteria": "Nilai unik payment_method hanya ['bank_transfer', 'cash', 'credit_card', 'e_wallet']",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-06",
        "source_finding_id": "F-06",
        "dataset": "Restaurants",
        "trigger_condition": "Nilai price_tier memiliki trailing whitespace atau huruf kapital ('MID ')",
        "decision": "STANDARDIZE",
        "cleaning_action": "Lakukan trim whitespace dan format Title Case (.astype(str).str.strip().str.title())",
        "reason_evidence": "Standardisasi menyelaraskan tingkatan harga ke kelas kanonikal ('Budget', 'Mid', 'Premium')",
        "implementation_note": "Terapkan pada kolom price_tier working copy restaurants_clean",
        "validation_criteria": "Nilai unik price_tier hanya terdiri dari: ['Budget', 'Mid', 'Premium']",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-07",
        "source_finding_id": "F-07",
        "dataset": "Order Items",
        "trigger_condition": "Nilai kolom quantity berupa string yang mengandung satuan teks ('3 pcs', '2 pcs')",
        "decision": "STANDARDIZE",
        "cleaning_action": "Ekstraksi digit numerik menggunakan regex r'(\d+)' dan ubah tipe data menjadi integer",
        "reason_evidence": "Kuantitas harus bertipe integer agar dapat digunakan dalam kalkulasi formula aritmatika",
        "implementation_note": "Gunakan .str.extract(r'(\d+)')[0].astype(int) pada order_items_clean['quantity']",
        "validation_criteria": "Dtype kolom quantity menjadi integer; seluruh nilai non-digit tereliminasi",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-08",
        "source_finding_id": "F-08",
        "dataset": "Order Items",
        "trigger_condition": "Nilai line_amount != quantity * unit_price - line_discount pada OI00131 dan OI00511",
        "decision": "FIX",
        "cleaning_action": "Kalkulasi ulang line_amount = quantity * unit_price - line_discount",
        "reason_evidence": "Menyelaraskan nilai transaksi item dengan formula bisnis yang sah dan cocok dengan header",
        "implementation_note": "Terapkan kalkulasi formula pada seluruh baris order_items_clean",
        "validation_criteria": "Formula match 100% pada 912 item; 0 formula mismatch",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-09",
        "source_finding_id": "F-09",
        "dataset": "Orders",
        "trigger_condition": "Nilai subtotal_amount pada order O00201 tidak cocok dengan jumlah line item (224k vs 214k)",
        "decision": "FIX",
        "cleaning_action": "Update subtotal_amount O00201 menjadi 214,000 dan hitung ulang total_amount menjadi 212,000",
        "reason_evidence": "Item detail valid (sum=214k) dan log pembayaran membuktikan pelanggan membayar 212,000",
        "implementation_note": "Update spesifik baris order O00201 berdasarkan aggregate sum detail order items",
        "validation_criteria": "Reconciliation Orders.subtotal_amount == SUM(line_amount) match 100% (354/354 orders)",
        "rule_status": "Approved",
    },
]

cleaning_rules = pd.DataFrame(rule_rows, columns=RULE_COLUMNS)
display(cleaning_rules)
  rule_id source_finding_id      dataset                                                                                        trigger_condition     decision                                                                               cleaning_action                                                                              reason_evidence                                                               implementation_note                                                                     validation_criteria rule_status
0   CR-01              F-01    Customers                                        Terdapat baris duplikat identik 100% pada seluruh atribut (C0031)          FIX  Hapus exact duplicate row dengan mempertahankan baris pertama (drop_duplicates keep='first')     Duplikasi baris penuh terbukti merupakan artefak redundansi teknis tanpa nilai unik baru    Terapkan hanya pada working copy customers_clean; jangan modifikasi source raw               Baris berkurang 1 (176 -> 175), customer_id unik 100% tanpa duplicate key    Approved
1   CR-02              F-02    Customers                                                         Nilai phone_number == 'NULL' atau email == 'N/A'  STANDARDIZE          Ganti string placeholder literal menjadi None / pd.NA (representasi missing standar)             Nilai placeholder bukan data kontak valid dan mengaburkan pengukuran missingness   Gunakan .replace({'NULL': None, 'N/A': None}) pada kolom phone_number dan email  0 string placeholder tersisa; field kontak yang kosong terdeteksi sebagai null standar    Approved
2   CR-03              F-03    Customers                     Nilai customer_segment memiliki trailing whitespace atau huruf kapital tidak seragam  STANDARDIZE          Lakukan trim whitespace dan format Title Case (.astype(str).str.strip().str.title())       Standardisasi mengembalikan konsistensi kategori kanonikal ('New', 'Regular', 'Loyal')                         Terapkan pada seluruh kolom customer_segment working copy            Nilai unik hanya terdiri dari 3 kelas kanonikal: ['Loyal', 'New', 'Regular']    Approved
3   CR-04              F-04       Orders                        Nilai order_status memiliki trailing whitespace atau huruf kapital ('COMPLETED ')  STANDARDIZE          Lakukan trim whitespace dan format lower case (.astype(str).str.strip().str.lower())    Standardisasi menyatukan variasi teks ke kelas kanonikal resmi ('completed', 'cancelled')                        Terapkan pada kolom order_status working copy orders_clean             Nilai unik hanya terdiri dari 2 kelas kanonikal: ['cancelled', 'completed']    Approved
4   CR-05              F-05     Payments  Nilai payment_method memiliki trailing whitespace atau huruf kapital berspasi ('CREDIT CARD ', 'CASH ')  STANDARDIZE        Lakukan trim, lower case, dan replace spasi menjadi underscore ('credit_card', 'cash')                     Standardisasi menyelaraskan format penamaan kategori ke lower_snake_case                    Terapkan pada kolom payment_method working copy payments_clean    Nilai unik payment_method hanya ['bank_transfer', 'cash', 'credit_card', 'e_wallet']    Approved
5   CR-06              F-06  Restaurants                                Nilai price_tier memiliki trailing whitespace atau huruf kapital ('MID ')  STANDARDIZE          Lakukan trim whitespace dan format Title Case (.astype(str).str.strip().str.title())  Standardisasi menyelaraskan tingkatan harga ke kelas kanonikal ('Budget', 'Mid', 'Premium')                     Terapkan pada kolom price_tier working copy restaurants_clean                  Nilai unik price_tier hanya terdiri dari: ['Budget', 'Mid', 'Premium']    Approved
6   CR-07              F-07  Order Items                        Nilai kolom quantity berupa string yang mengandung satuan teks ('3 pcs', '2 pcs')  STANDARDIZE         Ekstraksi digit numerik menggunakan regex r'(\d+)' dan ubah tipe data menjadi integer      Kuantitas harus bertipe integer agar dapat digunakan dalam kalkulasi formula aritmatika  Gunakan .str.extract(r'(\d+)')[0].astype(int) pada order_items_clean['quantity']              Dtype kolom quantity menjadi integer; seluruh nilai non-digit tereliminasi    Approved
7   CR-08              F-08  Order Items                      Nilai line_amount != quantity * unit_price - line_discount pada OI00131 dan OI00511          FIX                           Kalkulasi ulang line_amount = quantity * unit_price - line_discount    Menyelaraskan nilai transaksi item dengan formula bisnis yang sah dan cocok dengan header                   Terapkan kalkulasi formula pada seluruh baris order_items_clean                                    Formula match 100% pada 912 item; 0 formula mismatch    Approved
8   CR-09              F-09       Orders               Nilai subtotal_amount pada order O00201 tidak cocok dengan jumlah line item (224k vs 214k)          FIX   Update subtotal_amount O00201 menjadi 214,000 dan hitung ulang total_amount menjadi 212,000       Item detail valid (sum=214k) dan log pembayaran membuktikan pelanggan membayar 212,000   Update spesifik baris order O00201 berdasarkan aggregate sum detail order items   Reconciliation Orders.subtotal_amount == SUM(line_amount) match 100% (354/354 orders)    Approved
<string>:93: SyntaxWarning: invalid escape sequence '\d'
<string>:95: SyntaxWarning: invalid escape sequence '\d'

Rulebook Template

```python
new_rule = {
"rule_id": "CR-01",
"source_finding_id": "F-01",
"dataset": "...",
"trigger_condition": "...",
"decision": "FIX / STANDARDIZE",
"cleaning_action": "...",
"reason_evidence": "...",
"implementation_note": "...",
"validation_criteria": "...",
"rule_status": "Approved",
}
cleaning_rules.loc[len(cleaning_rules)] = new_rule
```

11. Create Working Copies

if DATA_READY:
    customers_clean = customers_raw.copy(deep=True)
    orders_clean = orders_raw.copy(deep=True)
    order_items_clean = order_items_raw.copy(deep=True)
    payments_clean = payments_raw.copy(deep=True)
    restaurants_clean = restaurants_raw.copy(deep=True)

    print("Working copies created.")
Working copies created.

12. Implement Cleaning Rules — EDIT + RUN

**Edit hanya cell Cleaning Code di bawah ini.**

Aturan:
- implementasikan hanya rule yang sudah kamu setujui di Working Paper;
- gunakan `customers_clean`, `orders_clean`, `order_items_clean`, `payments_clean`, atau `restaurants_clean`;
- jangan pernah overwrite object dengan suffix `_raw`;
- beri komentar `CR-xx` untuk setiap rule;
- setelah implementasi, catat before/after pada Cleaning Log.

Starter sengaja **tidak memberikan affected record ID atau jawaban** agar evidence tetap berasal dari variant kamu.

# ============================================================
# EDIT AREA 2 — APPROVED CLEANING CODE
# ============================================================

# CR-01 — Deduplikasi Exact Full-Row Duplicate pada Customers
# Source Finding: F-01
# Evidence basis: Record C0031 identik 100% pada index 30 dan 175
# Validation criteria: Baris berkurang 1 (176 -> 175), candidate key unik
customers_clean = customers_clean.drop_duplicates(keep="first").reset_index(drop=True)

# CR-02 — Standardisasi String Placeholder pada Customers
# Source Finding: F-02
# Evidence basis: phone_number bernilai 'NULL' (C0054) dan email bernilai 'N/A' (C0017)
# Validation criteria: 0 string placeholder literal; field missing menjadi None/NA
customers_clean["phone_number"] = customers_clean["phone_number"].replace({"NULL": None, "null": None, "None": None})
customers_clean["email"] = customers_clean["email"].replace({"N/A": None, "n/a": None, "NA": None})

# CR-03 — Standardisasi customer_segment pada Customers
# Source Finding: F-03
# Evidence basis: Nilai 'NEW ' (1) dan 'REGULAR ' (1) memiliki trailing space & kapital
# Validation criteria: Kategori kanonikal ['Loyal', 'New', 'Regular']
customers_clean["customer_segment"] = customers_clean["customer_segment"].astype(str).str.strip().str.title()

# CR-04 — Standardisasi order_status pada Orders
# Source Finding: F-04
# Evidence basis: Nilai 'COMPLETED ' (2) memiliki trailing space & kapital
# Validation criteria: Kategori kanonikal ['cancelled', 'completed']
orders_clean["order_status"] = orders_clean["order_status"].astype(str).str.strip().str.lower()

# CR-05 — Standardisasi payment_method pada Payments
# Source Finding: F-05
# Evidence basis: Nilai 'CREDIT CARD ' (1) dan 'CASH ' (1) kapital berspasi
# Validation criteria: Format lower_snake_case kanonikal ['bank_transfer', 'cash', 'credit_card', 'e_wallet']
payments_clean["payment_method"] = (
    payments_clean["payment_method"]
    .astype(str)
    .str.strip()
    .str.lower()
    .str.replace(" ", "_")
)

# CR-06 — Standardisasi price_tier pada Restaurants
# Source Finding: F-06
# Evidence basis: Nilai 'MID ' (2) memiliki trailing space & kapital
# Validation criteria: Kategori kanonikal ['Budget', 'Mid', 'Premium']
restaurants_clean["price_tier"] = restaurants_clean["price_tier"].astype(str).str.strip().str.title()

# CR-07 — Standardisasi Tipe Data quantity pada Order Items
# Source Finding: F-07
# Evidence basis: OI00071 bernilai '3 pcs', OI00215 bernilai '2 pcs'
# Validation criteria: Kolom quantity bertipe int64 murni tanpa teks satuan
order_items_clean["quantity"] = (
    order_items_clean["quantity"]
    .astype(str)
    .str.extract(r"(\d+)")[0]
    .astype(int)
)

# CR-08 — Koreksi line_amount Formula pada Order Items
# Source Finding: F-08
# Evidence basis: OI00131 bernilai 39,000 (calc 32,000); OI00511 bernilai 11,000 (calc 16,000)
# Validation criteria: quantity * unit_price - line_discount == line_amount cocok 100%
order_items_clean["line_amount"] = (
    order_items_clean["quantity"] * order_items_clean["unit_price"]
    - order_items_clean["line_discount"]
)

# CR-09 — Koreksi Header Rekonsiliasi pada Orders (O00201)
# Source Finding: F-09
# Evidence basis: subtotal_amount O00201 tercatat 224,000 padahal detail item sum 214,000
# dan pembayaran riil P00218 adalah 212,000
# Validation criteria: Orders.subtotal_amount == SUM(line_amount) cocok 100%
o201_item_sum = int(order_items_clean.loc[order_items_clean["order_id"] == "O00201", "line_amount"].sum())
orders_clean.loc[orders_clean["order_id"] == "O00201", "subtotal_amount"] = o201_item_sum
orders_clean.loc[orders_clean["order_id"] == "O00201", "total_amount"] = (
    o201_item_sum
    + orders_clean.loc[orders_clean["order_id"] == "O00201", "delivery_fee"]
    - orders_clean.loc[orders_clean["order_id"] == "O00201", "discount_amount"]
)

print("Approved cleaning rules successfully applied to working copies.")
Approved cleaning rules successfully applied to working copies.

Cleaning Log Register

CLEANING_LOG_COLUMNS = [
    "log_id",
    "rule_id",
    "dataset",
    "affected_records_scope",
    "action_performed",
    "before_evidence",
    "after_evidence",
    "rows_records_affected",
    "result",
    "notes",
]

log_rows = [
    {
        "log_id": "CL-01",
        "rule_id": "CR-01",
        "dataset": "Customers",
        "affected_records_scope": "Row index 175 (customer_id C0031)",
        "action_performed": "Drop exact full-row duplicate",
        "before_evidence": "Record C0031 muncul 2x (index 30 & 175), shape=(176, 7)",
        "after_evidence": "Record C0031 muncul 1x, shape=(175, 7), 0 duplicate row",
        "rows_records_affected": 1,
        "result": "Success",
        "notes": "Baris terduplikasi dihapus dengan preserve baris pertama.",
    },
    {
        "log_id": "CL-02",
        "rule_id": "CR-02",
        "dataset": "Customers",
        "affected_records_scope": "Record C0054 (phone_number) & C0017 (email)",
        "action_performed": "Replace placeholder string 'NULL' & 'N/A' dengan None",
        "before_evidence": "phone_number='NULL' (1 record), email='N/A' (1 record)",
        "after_evidence": "phone_number=None (1 null), email=None (1 null), 0 string placeholder",
        "rows_records_affected": 2,
        "result": "Success",
        "notes": "Representasi missing distandardisasi ke null standar.",
    },
    {
        "log_id": "CL-03",
        "rule_id": "CR-03",
        "dataset": "Customers",
        "affected_records_scope": "Kolom customer_segment (2 records non-standar)",
        "action_performed": "Strip whitespace & format Title Case",
        "before_evidence": "customer_segment contains 'NEW ' (1), 'REGULAR ' (1)",
        "after_evidence": "customer_segment hanya ['Loyal', 'New', 'Regular']",
        "rows_records_affected": 2,
        "result": "Success",
        "notes": "Kategori segmentasi terstandarisasi kanonikal.",
    },
    {
        "log_id": "CL-04",
        "rule_id": "CR-04",
        "dataset": "Orders",
        "affected_records_scope": "Kolom order_status (2 records non-standar)",
        "action_performed": "Strip whitespace & format lower case",
        "before_evidence": "order_status contains 'COMPLETED ' (2)",
        "after_evidence": "order_status hanya ['cancelled', 'completed']",
        "rows_records_affected": 2,
        "result": "Success",
        "notes": "Status order terstandarisasi kanonikal.",
    },
    {
        "log_id": "CL-05",
        "rule_id": "CR-05",
        "dataset": "Payments",
        "affected_records_scope": "Kolom payment_method (2 records non-standar)",
        "action_performed": "Strip, lower case, dan replace space ke underscore",
        "before_evidence": "payment_method contains 'CREDIT CARD ' (1), 'CASH ' (1)",
        "after_evidence": "payment_method hanya ['bank_transfer', 'cash', 'credit_card', 'e_wallet']",
        "rows_records_affected": 2,
        "result": "Success",
        "notes": "Metode bayar terstandarisasi snake_case kanonikal.",
    },
    {
        "log_id": "CL-06",
        "rule_id": "CR-06",
        "dataset": "Restaurants",
        "affected_records_scope": "Kolom price_tier (2 records non-standar)",
        "action_performed": "Strip whitespace & format Title Case",
        "before_evidence": "price_tier contains 'MID ' (2)",
        "after_evidence": "price_tier hanya ['Budget', 'Mid', 'Premium']",
        "rows_records_affected": 2,
        "result": "Success",
        "notes": "Tier harga restoran terstandarisasi kanonikal.",
    },
    {
        "log_id": "CL-07",
        "rule_id": "CR-07",
        "dataset": "Order Items",
        "affected_records_scope": "Kolom quantity (OI00071 & OI00215 string dengan unit)",
        "action_performed": "Regex digit extraction & cast to integer",
        "before_evidence": "quantity dtype object dengan nilai '3 pcs', '2 pcs'",
        "after_evidence": "quantity dtype int64 dengan nilai 3, 2; 0 string unit",
        "rows_records_affected": 2,
        "result": "Success",
        "notes": "Tipe data kuantitas dikonversi ke integer murni.",
    },
    {
        "log_id": "CL-08",
        "rule_id": "CR-08",
        "dataset": "Order Items",
        "affected_records_scope": "Record OI00131 (order O00053) & OI00511 (order O00204)",
        "action_performed": "Kalkulasi ulang line_amount = qty * unit_price - discount",
        "before_evidence": "OI00131 line_amount=39k (calc 32k), OI00511 line_amount=11k (calc 16k)",
        "after_evidence": "OI00131 line_amount=32k, OI00511 line_amount=16k, formula match 100%",
        "rows_records_affected": 2,
        "result": "Success",
        "notes": "Nilai line_amount diselaraskan dengan formula aritmatika.",
    },
    {
        "log_id": "CL-09",
        "rule_id": "CR-09",
        "dataset": "Orders",
        "affected_records_scope": "Record order O00201",
        "action_performed": "Update subtotal_amount=214k dan total_amount=212k",
        "before_evidence": "subtotal_amount=224k vs item sum 214k; total_amount=222k vs payment 212k",
        "after_evidence": "subtotal_amount=214k (match items); total_amount=212k (match payment)",
        "rows_records_affected": 1,
        "result": "Success",
        "notes": "Rekonsiliasi header dan rincian item cocok 100%.",
    },
]

cleaning_log = pd.DataFrame(log_rows, columns=CLEANING_LOG_COLUMNS)
display(cleaning_log)
  log_id rule_id      dataset                                  affected_records_scope                                           action_performed                                                           before_evidence                                                             after_evidence  rows_records_affected   result                                                      notes
0  CL-01   CR-01    Customers                       Row index 175 (customer_id C0031)                              Drop exact full-row duplicate                   Record C0031 muncul 2x (index 30 & 175), shape=(176, 7)                    Record C0031 muncul 1x, shape=(175, 7), 0 duplicate row                      1  Success  Baris terduplikasi dihapus dengan preserve baris pertama.
1  CL-02   CR-02    Customers             Record C0054 (phone_number) & C0017 (email)      Replace placeholder string 'NULL' & 'N/A' dengan None                    phone_number='NULL' (1 record), email='N/A' (1 record)      phone_number=None (1 null), email=None (1 null), 0 string placeholder                      2  Success      Representasi missing distandardisasi ke null standar.
2  CL-03   CR-03    Customers          Kolom customer_segment (2 records non-standar)                       Strip whitespace & format Title Case                      customer_segment contains 'NEW ' (1), 'REGULAR ' (1)                         customer_segment hanya ['Loyal', 'New', 'Regular']                      2  Success             Kategori segmentasi terstandarisasi kanonikal.
3  CL-04   CR-04       Orders              Kolom order_status (2 records non-standar)                       Strip whitespace & format lower case                                    order_status contains 'COMPLETED ' (2)                              order_status hanya ['cancelled', 'completed']                      2  Success                    Status order terstandarisasi kanonikal.
4  CL-05   CR-05     Payments            Kolom payment_method (2 records non-standar)         Strip, lower case, dan replace space ke underscore                   payment_method contains 'CREDIT CARD ' (1), 'CASH ' (1)  payment_method hanya ['bank_transfer', 'cash', 'credit_card', 'e_wallet']                      2  Success         Metode bayar terstandarisasi snake_case kanonikal.
5  CL-06   CR-06  Restaurants                Kolom price_tier (2 records non-standar)                       Strip whitespace & format Title Case                                            price_tier contains 'MID ' (2)                              price_tier hanya ['Budget', 'Mid', 'Premium']                      2  Success             Tier harga restoran terstandarisasi kanonikal.
6  CL-07   CR-07  Order Items   Kolom quantity (OI00071 & OI00215 string dengan unit)                   Regex digit extraction & cast to integer                       quantity dtype object dengan nilai '3 pcs', '2 pcs'                      quantity dtype int64 dengan nilai 3, 2; 0 string unit                      2  Success           Tipe data kuantitas dikonversi ke integer murni.
7  CL-08   CR-08  Order Items  Record OI00131 (order O00053) & OI00511 (order O00204)  Kalkulasi ulang line_amount = qty * unit_price - discount    OI00131 line_amount=39k (calc 32k), OI00511 line_amount=11k (calc 16k)       OI00131 line_amount=32k, OI00511 line_amount=16k, formula match 100%                      2  Success  Nilai line_amount diselaraskan dengan formula aritmatika.
8  CL-09   CR-09       Orders                                     Record order O00201          Update subtotal_amount=214k dan total_amount=212k  subtotal_amount=224k vs item sum 214k; total_amount=222k vs payment 212k      subtotal_amount=214k (match items); total_amount=212k (match payment)                      1  Success           Rekonsiliasi header dan rincian item cocok 100%.

13. Validation Harness — EDIT + RUN

Validation harus membuktikan hasil cleaning memenuhi **15 check pada Working Paper**:

`V-01` sampai `V-15`

Starter menyediakan fungsi `record_check()`. Kamu tetap harus menentukan evidence dan hasil check berdasarkan variant kamu.

> `FINAL_GATE` tidak akan PASS jika check wajib belum lengkap.

validation_results = []

REQUIRED_VALIDATION_IDS = [f"V-{i:02d}" for i in range(1, 16)]

def record_check(check_id, level, dataset_relationship, question,
                 expected_criteria, actual_result, passed,
                 related_rule_id="", notes=""):
    if check_id not in REQUIRED_VALIDATION_IDS:
        print(f"WARNING — {check_id} bukan ID validation V-01 s.d. V-15.")

    # Jika cell di-run ulang, replace check dengan ID yang sama agar tidak double.
    validation_results[:] = [
        row for row in validation_results if row["check_id"] != check_id
    ]

    validation_results.append({
        "check_id": check_id,
        "validation_level": level,
        "dataset_relationship": dataset_relationship,
        "validation_question": question,
        "expected_criteria": expected_criteria,
        "actual_result_metric": actual_result,
        "status": "PASS" if bool(passed) else "FAIL",
        "related_rule_id": related_rule_id,
        "notes": notes,
    })

print("VALIDATION HARNESS READY")
print("Required IDs:", ", ".join(REQUIRED_VALIDATION_IDS))
VALIDATION HARNESS READY
Required IDs: V-01, V-02, V-03, V-04, V-05, V-06, V-07, V-08, V-09, V-10, V-11, V-12, V-13, V-14, V-15

13A. Schema & Key Validation — EDIT + RUN

Gunakan area ini untuk mencatat check seperti `V-01` sampai `V-05` sesuai Working Paper.

Kamu boleh memakai kembali helper `key_check()` dari bagian sebelumnya. Jangan mengganti nilai menjadi PASS tanpa evidence.

if DATA_READY:
    clean_tables = {
        "Customers": customers_clean,
        "Orders": orders_clean,
        "Order Items": order_items_clean,
        "Payments": payments_clean,
        "Restaurants": restaurants_clean,
    }

    print("Clean dataset shapes:")
    for name, df in clean_tables.items():
        print(name, df.shape)

    # ========================================================
    # EDIT AREA 3A — V-01 s.d. V-05
    # ========================================================

    # V-01 — Schema: Seluruh required column tersedia
    v1_req = {
        "Customers": {"customer_id", "full_name", "email", "phone_number", "signup_date", "customer_zone", "customer_segment"},
        "Orders": {"order_id", "customer_id", "restaurant_id", "order_timestamp", "order_status", "subtotal_amount", "delivery_fee", "discount_amount", "total_amount"},
        "Order Items": {"order_item_id", "order_id", "line_number", "item_name", "item_category", "quantity", "unit_price", "line_discount", "line_amount"},
        "Payments": {"payment_id", "order_id", "payment_timestamp", "payment_method", "payment_status", "payment_amount"},
        "Restaurants": {"restaurant_id", "restaurant_name", "cuisine_type", "restaurant_zone", "price_tier", "active_status"},
    }
    v1_missing = {}
    for name, req_cols in v1_req.items():
        diff = req_cols - set(clean_tables[name].columns)
        if diff:
            v1_missing[name] = list(diff)
    record_check(
        "V-01", "Schema", "All clean datasets",
        "Apakah seluruh required column masih tersedia?",
        "Required columns available",
        f"5 datasets checked, missing columns: {v1_missing if v1_missing else '0'}",
        len(v1_missing) == 0,
        related_rule_id="All",
        notes="Seluruh kolom esensial tersedia lengkap pada kelima clean dataset.",
    )

    # V-02 — Key Customers: customer_id valid sesuai grain customer
    c_nulls = int(customers_clean["customer_id"].isna().sum())
    c_uniq = int(customers_clean["customer_id"].nunique())
    record_check(
        "V-02", "Key", "Customers",
        "Apakah customer_id valid sesuai grain customer?",
        "No null key; uniqueness sesuai grain",
        f"rows={len(customers_clean)}, unique={c_uniq}, nulls={c_nulls}",
        c_nulls == 0 and c_uniq == len(customers_clean),
        related_rule_id="CR-01",
        notes="customer_id 100% unik dan tidak ada null setelah deduplikasi 1 exact duplicate.",
    )

    # V-03 — Key Orders: order_id valid sesuai grain order
    o_nulls = int(orders_clean["order_id"].isna().sum())
    o_uniq = int(orders_clean["order_id"].nunique())
    record_check(
        "V-03", "Key", "Orders",
        "Apakah order_id valid sesuai grain order?",
        "No null key; one row per order",
        f"rows={len(orders_clean)}, unique={o_uniq}, nulls={o_nulls}",
        o_nulls == 0 and o_uniq == len(orders_clean),
        related_rule_id="N/A",
        notes="order_id unik 100% (354/354 distinct), 1 baris per order.",
    )

    # V-04 — Key Order Items: order_item_id / candidate business key valid
    oi_nulls = int(order_items_clean["order_item_id"].isna().sum())
    oi_uniq = int(order_items_clean["order_item_id"].nunique())
    oi_bus_dup = int(order_items_clean.duplicated(subset=["order_id", "line_number"]).sum())
    record_check(
        "V-04", "Key", "Order Items",
        "Apakah order_item_id / candidate business key valid?",
        "No invalid duplicate key",
        f"rows={len(order_items_clean)}, unique_id={oi_uniq}, duplicate_bus_keys={oi_bus_dup}",
        oi_nulls == 0 and oi_uniq == len(order_items_clean) and oi_bus_dup == 0,
        related_rule_id="N/A",
        notes="order_item_id dan komposit (order_id, line_number) 100% unik tanpa duplikasi.",
    )

    # V-05 — Key Payments: payment_id unique tanpa menghapus repeated order_id valid
    p_nulls = int(payments_clean["payment_id"].isna().sum())
    p_uniq = int(payments_clean["payment_id"].nunique())
    p_multi_orders = int((payments_clean.groupby("order_id").size() > 1).sum())
    record_check(
        "V-05", "Key", "Payments",
        "Apakah payment_id unique tanpa menghapus repeated order_id yang valid?",
        "payment_id unique; repeated order_id allowed by grain",
        f"rows={len(payments_clean)}, unique_payment_id={p_uniq}, multi_attempt_orders={p_multi_orders}",
        p_nulls == 0 and p_uniq == len(payments_clean) and p_multi_orders == 44,
        related_rule_id="CR-05",
        notes="payment_id unik 100%; 44 repeated order_id valid dipertahankan sesuai grain payment attempts.",
    )
else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes:
Customers (175, 7)
Orders (354, 10)
Order Items (912, 9)
Payments (398, 7)
Restaurants (26, 7)

13B. Value, Formula & Cross-Table Validation — EDIT + RUN

Gunakan area ini untuk `V-06` sampai `V-14` sesuai Working Paper.

Manfaatkan helper yang sudah tersedia seperti `category_review()`, `fk_check()`, dan perhitungan formula. **Hitung ulang dari clean data**, bukan hanya melihat output raw.

if DATA_READY:
    # ========================================================
    # EDIT AREA 3B — V-06 s.d. V-14
    # ========================================================

    # V-06 — Value Customers: category & missing representation
    cust_seg_unique = set(customers_clean["customer_segment"].unique())
    cust_phone_placeholders = int(customers_clean["phone_number"].isin(["NULL", "null", "None"]).sum())
    cust_email_placeholders = int(customers_clean["email"].isin(["N/A", "n/a", "NA"]).sum())
    v6_pass = (cust_seg_unique == {"New", "Regular", "Loyal"}) and (cust_phone_placeholders == 0) and (cust_email_placeholders == 0)
    record_check(
        "V-06", "Value", "Customers / categories",
        "Apakah category dan missing representation sudah konsisten?",
        "No unexpected canonical-category issue",
        f"categories={sorted(cust_seg_unique)}, placeholder_phone={cust_phone_placeholders}, placeholder_email={cust_email_placeholders}",
        v6_pass,
        related_rule_id="CR-02, CR-03",
        notes="customer_segment canonical (New, Regular, Loyal); placeholder string dinetralkan menjadi None/NA.",
    )

    # V-07 — Value Orders: order status & representation
    order_status_unique = set(orders_clean["order_status"].unique())
    v7_pass = (order_status_unique == {"completed", "cancelled"})
    record_check(
        "V-07", "Value", "Orders / categories",
        "Apakah order status dan representation sudah konsisten?",
        "Only approved canonical values",
        f"order_statuses={sorted(order_status_unique)}",
        v7_pass,
        related_rule_id="CR-04",
        notes="order_status terstandarisasi lower case menjadi canonical ('completed', 'cancelled').",
    )

    # V-08 — Value Payments / Restaurants: category representation
    pay_method_unique = set(payments_clean["payment_method"].unique())
    rest_price_unique = set(restaurants_clean["price_tier"].unique())
    v8_pass = (
        pay_method_unique == {"bank_transfer", "cash", "credit_card", "e_wallet"}
        and rest_price_unique == {"Budget", "Mid", "Premium"}
    )
    record_check(
        "V-08", "Value", "Payments / Restaurants",
        "Apakah category representation sudah konsisten?",
        "Only approved canonical values",
        f"payment_methods={sorted(pay_method_unique)}, price_tiers={sorted(rest_price_unique)}",
        v8_pass,
        related_rule_id="CR-05, CR-06",
        notes="payment_method dan price_tier telah terstandarisasi ke kategori kanonikal.",
    )

    # V-09 — Business Rule Order Items: line_amount formula
    expected_line_amount = (
        order_items_clean["quantity"] * order_items_clean["unit_price"]
        - order_items_clean["line_discount"]
    )
    item_formula_mismatches = int((order_items_clean["line_amount"] != expected_line_amount).sum())
    record_check(
        "V-09", "Business Rule", "Order Items",
        "Apakah line_amount memenuhi formula item?",
        "quantity × unit_price - line_discount = line_amount",
        f"formula_mismatch_count={item_formula_mismatches}",
        item_formula_mismatches == 0,
        related_rule_id="CR-07, CR-08",
        notes="Seluruh 912 item memenuhi formula aritmatika line_amount secara presisi.",
    )

    # V-10 — Business Rule Orders: total_amount formula
    expected_total_amount = (
        orders_clean["subtotal_amount"]
        + orders_clean["delivery_fee"]
        - orders_clean["discount_amount"]
    )
    order_formula_mismatches = int((orders_clean["total_amount"] != expected_total_amount).sum())
    record_check(
        "V-10", "Business Rule", "Orders",
        "Apakah total_amount memenuhi formula order?",
        "subtotal + delivery_fee - discount_amount = total_amount",
        f"order_formula_mismatch_count={order_formula_mismatches}",
        order_formula_mismatches == 0,
        related_rule_id="CR-09",
        notes="Seluruh 354 order memenuhi formula total_amount = subtotal + delivery - discount.",
    )

    # V-11 — Cross-table Orders ↔ Order Items: subtotal reconciliation
    sum_items_by_order = (
        order_items_clean.groupby("order_id")["line_amount"]
        .sum()
        .rename("sum_items")
    )
    orders_recon_clean = orders_clean.merge(sum_items_by_order, on="order_id", how="left")
    recon_mismatches = int((orders_clean["subtotal_amount"] != orders_recon_clean["sum_items"]).sum())
    record_check(
        "V-11", "Cross-table", "Orders ↔ Order Items",
        "Apakah subtotal order konsisten dengan detail item?",
        "SUM(line_amount) = subtotal_amount",
        f"reconciliation_mismatch_count={recon_mismatches}",
        recon_mismatches == 0,
        related_rule_id="CR-08, CR-09",
        notes="Rekonsiliasi header subtotal dan sum of line items cocok 100% (354/354 match).",
    )

    # V-12 — Cross-table Orders → Customers: foreign key check
    unmatched_customers = int(len(set(orders_clean["customer_id"]) - set(customers_clean["customer_id"])))
    record_check(
        "V-12", "Cross-table", "Orders → Customers",
        "Apakah seluruh customer reference valid?",
        "Unmatched customer = 0",
        f"unmatched_customers_count={unmatched_customers}",
        unmatched_customers == 0,
        related_rule_id="CR-01",
        notes="Seluruh 354 order terhubung ke customer_id yang valid pada master Customers.",
    )

    # V-13 — Cross-table Orders → Restaurants: foreign key check
    unmatched_restaurants = int(len(set(orders_clean["restaurant_id"]) - set(restaurants_clean["restaurant_id"])))
    record_check(
        "V-13", "Cross-table", "Orders → Restaurants",
        "Apakah seluruh restaurant reference valid?",
        "Unmatched restaurant = 0",
        f"unmatched_restaurants_count={unmatched_restaurants}",
        unmatched_restaurants == 0,
        related_rule_id="N/A",
        notes="Seluruh 354 order terhubung ke restaurant_id yang valid pada master Restaurants.",
    )

    # V-14 — Cross-table Order Items / Payments → Orders: foreign key check
    orphan_items = int(len(set(order_items_clean["order_id"]) - set(orders_clean["order_id"])))
    orphan_payments = int(len(set(payments_clean["order_id"]) - set(orders_clean["order_id"])))
    record_check(
        "V-14", "Cross-table", "Order Items / Payments → Orders",
        "Apakah seluruh order reference valid?",
        "Orphan order reference = 0",
        f"orphan_items={orphan_items}, orphan_payments={orphan_payments}",
        orphan_items == 0 and orphan_payments == 0,
        related_rule_id="N/A",
        notes="0 orphan records; seluruh relasi foreign key order_id dari items dan payments valid.",
    )
else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")

13C. KEEP Preservation Check — EDIT + RUN

Gunakan `V-15` untuk membuktikan bahwa finding yang kamu putuskan **KEEP** tidak ikut berubah akibat over-cleaning.

Jika kamu memiliki lebih dari satu KEEP finding, gunakan evidence yang cukup di Working Paper lalu rangkum hasilnya ke `V-15`.

# ============================================================
# EDIT AREA 3C — V-15 KEEP PRESERVATION
# ============================================================
p_multi = int((payments_clean.groupby("order_id").size() > 1).sum())
high_val_order = bool(orders_clean.loc[orders_clean["order_id"] == "O00084", "total_amount"].values[0] == 1068000)
missing_promo = int(orders_clean["promo_code"].isna().sum()) == 167
inactive_rest_count = int((restaurants_clean["active_status"] == "inactive").sum()) == 2
v15_pass = (p_multi == 44) and high_val_order and missing_promo and inactive_rest_count

record_check(
    "V-15", "Judgment", "KEEP findings",
    "Apakah legitimate anomaly yang diputuskan KEEP masih dipertahankan?",
    "KEEP findings remain unless new evidence justifies change",
    f"multi_pay_orders={p_multi}, high_val_preserved={high_val_order}, missing_promo_preserved={missing_promo}, inactive_rest_preserved={inactive_rest_count}",
    v15_pass,
    related_rule_id="F-10, F-11, F-12, F-13",
    notes="Seluruh 4 legitimate anomalies (multi payments, high value order, null promo, inactive restaurants) dipertahankan utuh.",
)

Validation Results Table

validation_df = pd.DataFrame(validation_results)

if validation_df.empty:
    print("VALIDATION GATE: PENDING")
    print("→ Belum ada validation evidence yang dicatat.")
else:
    validation_df = validation_df.sort_values("check_id").reset_index(drop=True)
    display(validation_df)

    present_ids = set(validation_df["check_id"])
    missing_validation_ids = [
        x for x in REQUIRED_VALIDATION_IDS if x not in present_ids
    ]

    print(validation_df["status"].value_counts(dropna=False))

    if missing_validation_ids:
        print("VALIDATION GATE: PENDING")
        print("Missing required checks:", ", ".join(missing_validation_ids))
    elif (validation_df["status"] == "FAIL").any():
        print("VALIDATION GATE: FAIL")
        print("→ Review FAIL sebelum finalisasi.")
    else:
        print("VALIDATION GATE: PASS")
   check_id validation_level             dataset_relationship                                                     validation_question                                          expected_criteria                                                                                            actual_result_metric status         related_rule_id                                                                                                                    notes
0      V-01           Schema               All clean datasets                          Apakah seluruh required column masih tersedia?                                 Required columns available                                                                          5 datasets checked, missing columns: 0   PASS                     All                                                       Seluruh kolom esensial tersedia lengkap pada kelima clean dataset.
1      V-02              Key                        Customers                         Apakah customer_id valid sesuai grain customer?                       No null key; uniqueness sesuai grain                                                                                   rows=175, unique=175, nulls=0   PASS                   CR-01                                          customer_id 100% unik dan tidak ada null setelah deduplikasi 1 exact duplicate.
2      V-03              Key                           Orders                               Apakah order_id valid sesuai grain order?                             No null key; one row per order                                                                                   rows=354, unique=354, nulls=0   PASS                     N/A                                                                order_id unik 100% (354/354 distinct), 1 baris per order.
3      V-04              Key                      Order Items                    Apakah order_item_id / candidate business key valid?                                   No invalid duplicate key                                                                   rows=912, unique_id=912, duplicate_bus_keys=0   PASS                     N/A                                            order_item_id dan komposit (order_id, line_number) 100% unik tanpa duplikasi.
4      V-05              Key                         Payments  Apakah payment_id unique tanpa menghapus repeated order_id yang valid?      payment_id unique; repeated order_id allowed by grain                                                        rows=398, unique_payment_id=398, multi_attempt_orders=44   PASS                   CR-05                            payment_id unik 100%; 44 repeated order_id valid dipertahankan sesuai grain payment attempts.
5      V-06            Value           Customers / categories             Apakah category dan missing representation sudah konsisten?                     No unexpected canonical-category issue                                categories=['Loyal', 'New', 'Regular'], placeholder_phone=0, placeholder_email=0   PASS            CR-02, CR-03                        customer_segment canonical (New, Regular, Loyal); placeholder string dinetralkan menjadi None/NA.
6      V-07            Value              Orders / categories                 Apakah order status dan representation sudah konsisten?                             Only approved canonical values                                                                       order_statuses=['cancelled', 'completed']   PASS                   CR-04                                    order_status terstandarisasi lower case menjadi canonical ('completed', 'cancelled').
7      V-08            Value           Payments / Restaurants                         Apakah category representation sudah konsisten?                             Only approved canonical values  payment_methods=['bank_transfer', 'cash', 'credit_card', 'e_wallet'], price_tiers=['Budget', 'Mid', 'Premium']   PASS            CR-05, CR-06                                               payment_method dan price_tier telah terstandarisasi ke kategori kanonikal.
8      V-09    Business Rule                      Order Items                               Apakah line_amount memenuhi formula item?        quantity × unit_price - line_discount = line_amount                                                                                        formula_mismatch_count=0   PASS            CR-07, CR-08                                                 Seluruh 912 item memenuhi formula aritmatika line_amount secara presisi.
9      V-10    Business Rule                           Orders                             Apakah total_amount memenuhi formula order?   subtotal + delivery_fee - discount_amount = total_amount                                                                                  order_formula_mismatch_count=0   PASS                   CR-09                                        Seluruh 354 order memenuhi formula total_amount = subtotal + delivery - discount.
10     V-11      Cross-table             Orders ↔ Order Items                     Apakah subtotal order konsisten dengan detail item?                         SUM(line_amount) = subtotal_amount                                                                                 reconciliation_mismatch_count=0   PASS            CR-08, CR-09                                           Rekonsiliasi header subtotal dan sum of line items cocok 100% (354/354 match).
11     V-12      Cross-table               Orders → Customers                                Apakah seluruh customer reference valid?                                     Unmatched customer = 0                                                                                     unmatched_customers_count=0   PASS                   CR-01                                             Seluruh 354 order terhubung ke customer_id yang valid pada master Customers.
12     V-13      Cross-table             Orders → Restaurants                              Apakah seluruh restaurant reference valid?                                   Unmatched restaurant = 0                                                                                   unmatched_restaurants_count=0   PASS                     N/A                                         Seluruh 354 order terhubung ke restaurant_id yang valid pada master Restaurants.
13     V-14      Cross-table  Order Items / Payments → Orders                                   Apakah seluruh order reference valid?                                 Orphan order reference = 0                                                                               orphan_items=0, orphan_payments=0   PASS                     N/A                                     0 orphan records; seluruh relasi foreign key order_id dari items dan payments valid.
14     V-15         Judgment                    KEEP findings     Apakah legitimate anomaly yang diputuskan KEEP masih dipertahankan?  KEEP findings remain unless new evidence justifies change        multi_pay_orders=44, high_val_preserved=True, missing_promo_preserved=True, inactive_rest_preserved=True   PASS  F-10, F-11, F-12, F-13  Seluruh 4 legitimate anomalies (multi payments, high value order, null promo, inactive restaurants) dipertahankan utuh.
status
PASS    15
Name: count, dtype: int64
VALIDATION GATE: PASS

13D. Raw Integrity Check — RUN

Cell ini membandingkan SHA-256 raw source saat pertama kali di-load dengan file raw saat ini.

Target: **tidak ada raw/source yang berubah**.

RAW_INTEGRITY_PASS = False
raw_integrity_rows = []

if not DATA_READY:
    print("RAW INTEGRITY CHECK: SKIPPED")
    print("→ DATA READY GATE belum PASS.")
else:
    file_map_integrity = {
        "Customers": RAW_DIR / EXPECTED_FILES["customers"],
        "Orders": RAW_DIR / EXPECTED_FILES["orders"],
        "Order Items": RAW_DIR / EXPECTED_FILES["order_items"],
        "Payments": RAW_DIR / EXPECTED_FILES["payments"],
        "Restaurants": RAW_DIR / EXPECTED_FILES["restaurants"],
    }

    for label, path in file_map_integrity.items():
        current_hash = sha256_file(path)
        baseline_hash = RAW_HASH_BASELINE.get(label)
        raw_integrity_rows.append({
            "dataset": label,
            "unchanged": current_hash == baseline_hash,
        })

    raw_integrity_df = pd.DataFrame(raw_integrity_rows)
    display(raw_integrity_df)

    RAW_INTEGRITY_PASS = bool(raw_integrity_df["unchanged"].all())

    if RAW_INTEGRITY_PASS:
        print("RAW INTEGRITY CHECK: PASS")
    else:
        print("RAW INTEGRITY CHECK: FAIL")
        print("→ Raw/source berubah. Kembalikan source asli sebelum finalisasi.")
       dataset  unchanged
0    Customers       True
1       Orders       True
2  Order Items       True
3     Payments       True
4  Restaurants       True
RAW INTEGRITY CHECK: PASS

14. Final Data Readiness Gate — RUN

Final Gate hanya boleh:
- `PASS` jika **IDENTITY PASS + semua V-01 s.d. V-15 PASS + RAW INTEGRITY PASS**;
- `NOT READY` jika ada critical FAIL atau raw source berubah;
- `PENDING` jika validation belum lengkap.

Jangan mengubah nilai `FINAL_GATE` secara manual.

def derive_final_gate(validation_df, raw_integrity_pass, identity_gate_pass):
    if not identity_gate_pass:
        return "PENDING", ["Identity belum lengkap."]

    if validation_df.empty:
        return "PENDING", ["Validation evidence masih kosong."]

    present_ids = set(validation_df["check_id"])
    missing_ids = [x for x in REQUIRED_VALIDATION_IDS if x not in present_ids]

    required_rows = validation_df[
        validation_df["check_id"].isin(REQUIRED_VALIDATION_IDS)
    ].copy()

    if (required_rows["status"] == "FAIL").any():
        failed_ids = required_rows.loc[
            required_rows["status"] == "FAIL", "check_id"
        ].tolist()
        return "NOT READY", [f"Validation FAIL: {', '.join(failed_ids)}"]

    if not raw_integrity_pass:
        return "NOT READY", ["Raw Integrity Check FAIL."]

    if missing_ids:
        return "PENDING", [f"Validation belum lengkap: {', '.join(missing_ids)}"]

    all_pass = (
        len(required_rows) == len(REQUIRED_VALIDATION_IDS)
        and (required_rows["status"] == "PASS").all()
    )

    if all_pass:
        return "PASS", []

    return "PENDING", ["Review validation evidence."]

FINAL_GATE, FINAL_GATE_REASONS = derive_final_gate(
    validation_df,
    RAW_INTEGRITY_PASS,
    IDENTITY_GATE_PASS,
)

print("PM1 DATA READINESS GATE:", FINAL_GATE)
if FINAL_GATE_REASONS:
    for reason in FINAL_GATE_REASONS:
        print("→", reason)

FINAL_JUDGMENT_RATIONALE = (
    "Seluruh 15 checkpoint validasi (V-01 s.d. V-15) berstatus PASS, Raw Integrity Check PASS (hash file raw 100% "
    "tidak berubah), dan 5 clean dataset berhasil diekspor. Isu duplikasi pelanggan (C0031), placeholder string ('NULL', 'N/A'), "
    "inkonsistensi kategori teks, kuantitas berformat string, kesalahan formula item (OI00131, OI00511), dan rekonsiliasi order O00201 "
    "telah diperbaiki secara presisi berbasis rulebook tanpa mengorbankan legitimate business records (multi-attempts, promo nulls, outlier sah). "
    "Data dinyatakan PASS dan siap dipercaya untuk analisis lanjutan."
)
print(FINAL_JUDGMENT_RATIONALE)
PM1 DATA READINESS GATE: PASS
Seluruh 15 checkpoint validasi (V-01 s.d. V-15) berstatus PASS, Raw Integrity Check PASS (hash file raw 100% tidak berubah), dan 5 clean dataset berhasil diekspor. Isu duplikasi pelanggan (C0031), placeholder string ('NULL', 'N/A'), inkonsistensi kategori teks, kuantitas berformat string, kesalahan formula item (OI00131, OI00511), dan rekonsiliasi order O00201 telah diperbaiki secara presisi berbasis rulebook tanpa mengorbankan legitimate business records (multi-attempts, promo nulls, outlier sah). Data dinyatakan PASS dan siap dipercaya untuk analisis lanjutan.

15. Export Clean Data — RUN SETELAH FINAL GATE PASS

Output:
- `customers_clean.csv`
- `orders_clean.csv`
- `order_items_clean.csv`
- `payments_clean.csv`
- `restaurants_clean.csv`

Jika Final Gate belum PASS, export akan **diblok otomatis**.

def export_clean_data():
    if FINAL_GATE != "PASS":
        print("CLEAN EXPORT BLOCKED")
        print("→ FINAL_GATE belum PASS. Selesaikan validation terlebih dahulu.")
        return False

    customers_clean.to_csv(PROCESSED_DIR / "customers_clean.csv", index=False)
    orders_clean.to_csv(PROCESSED_DIR / "orders_clean.csv", index=False)
    order_items_clean.to_csv(PROCESSED_DIR / "order_items_clean.csv", index=False)
    payments_clean.to_csv(PROCESSED_DIR / "payments_clean.csv", index=False)
    restaurants_clean.to_csv(PROCESSED_DIR / "restaurants_clean.csv", index=False)

    print("CLEAN PACKAGE SAVE: PASS")
    print("Saved to:", PROCESSED_DIR)
    return True

export_clean_data()
CLEAN PACKAGE SAVE: PASS
Saved to: C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite

16. Export Evidence — RUN SETELAH EVIDENCE LENGKAP

Evidence boleh diekspor untuk review, tetapi **clean package final tetap hanya boleh disimpan saat FINAL_GATE: PASS**.

def export_evidence():
    findings.to_csv(OUTPUT_DIR / "pm1_findings.csv", index=False)
    cleaning_rules.to_csv(OUTPUT_DIR / "pm1_cleaning_rulebook.csv", index=False)
    cleaning_log.to_csv(OUTPUT_DIR / "pm1_cleaning_log.csv", index=False)
    validation_df.to_csv(OUTPUT_DIR / "pm1_validation_results.csv", index=False)

    summary = {
        "participant_name": PARTICIPANT_NAME,
        "variant_id": VARIANT_ID,
        "final_gate": FINAL_GATE,
        "final_gate_reasons": FINAL_GATE_REASONS,
        "raw_integrity_pass": RAW_INTEGRITY_PASS,
        "final_judgment_rationale": FINAL_JUDGMENT_RATIONALE,
    }

    with open(OUTPUT_DIR / "pm1_readiness_summary.json", "w", encoding="utf-8") as f:
        json.dump(summary, f, ensure_ascii=False, indent=2)

    print("EVIDENCE EXPORT: PASS")
    print("Saved to:", OUTPUT_DIR)
    return True

export_evidence()
EVIDENCE EXPORT: PASS
Saved to: C:\Users\LENOVO X270\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\outputs\pm1_swiftbite

17. Final Submission Checklist

### Technical
- [ ] `IDENTITY GATE: PASS`
- [ ] `TECHNICAL GATE: PASS`
- [ ] `SOURCE ACCESS CHECK: PASS`
- [ ] `RAW LOAD GATE: PASS`
- [ ] `SCHEMA GATE: PASS`
- [ ] `RAW INTEGRITY CHECK: PASS`

### Intake, Profiling & Judgment
- [ ] 5 source berhasil dibaca.
- [ ] Grain dan candidate key sudah diperiksa.
- [ ] Relationship utama sudah diperiksa.
- [ ] Material findings sudah diverifikasi.
- [ ] FIX / STANDARDIZE / KEEP / INVESTIGATE memiliki evidence dan alasan.
- [ ] Finding yang menghasilkan cleaning memiliki Rule ID.

### Cleaning & Validation
- [ ] Rulebook dibuat sebelum implementation.
- [ ] Cleaning hanya dilakukan pada working copy.
- [ ] Cleaning Log lengkap.
- [ ] `V-01` sampai `V-15` sudah dicatat.
- [ ] `PM1 DATA READINESS GATE: PASS`
- [ ] 5 clean CSV berhasil diekspor.

Definition of Done

```text
Raw Data
↓
Technical & Source Gate
↓
Understand Grain & Key
↓
Profile → Find → Verify → Judge
↓
Define Cleaning Rule
↓
Clean Working Copy
↓
Validate V-01 ... V-15
↓
Raw Integrity Check
↓
FINAL GATE
↓
Export Clean Package
```