# 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 = "Yoreta"
VARIANT_ID = "SB-P28"   # contoh: SB-P01

name_ok = PARTICIPANT_NAME.strip() not in {"", "YoretaKartika"}
variant_ok = (
    VARIANT_ID.startswith("SB-P28")
    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 : Yoreta
Variant ID  : SB-P28

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 : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst
RAW_DIR        : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite
PROCESSED_DIR  : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
OUTPUT_DIR     : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\outputs\pm1_swiftbite
DOCS_DIR       : D:\PT PLan Bootcamp\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.3
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 D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data...
1 orders swiftbite_orders.xlsx True D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data...
2 order_items swiftbite_order_items.csv True D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data...
3 payments swiftbite_payments.txt True D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data...
4 restaurants swiftbite_restaurants.csv True D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data...
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", "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", "amount"
    },
    "Restaurants": {
        "restaurant_id", "restaurant_name", "cuisine",
        "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 9 PASS
2 Order Items 9 9 PASS
3 Payments 6 6 PASS
4 Restaurants 6 6 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=(90, 7) ===
customer_id full_name email phone_number signup_date zone customer_segment
0 C28-001 SwiftBite User P28-001 p28.user001@example.test 82800000001 2025-07-09 Jakarta Timur Premium
1 C28-002 SwiftBite User P28-002 p28.user002@example.test 82800000002 2025-10-12 Jakarta Utara Premium
2 C28-003 SwiftBite User P28-003 p28.user003@example.test 82800000003 2026-02-19 Jakarta Barat Gold
=== Orders | shape=(140, 9) ===
order_id customer_id restaurant_id order_timestamp order_status subtotal_amount delivery_fee discount_amount total_amount
0 O28-0001 C28-080 R28-11 2026-02-22 12:44:00 DELIVERED 50000 8000 15000 43000
1 O28-0002 C28-079 R28-05 2026-03-14 12:15:00 DELIVERED 299000 10000 0 309000
2 O28-0003 C28-057 R28-09 2026-02-10 18:11:00 DELIVERED 410000 15000 0 425000
=== Order Items | shape=(374, 9) ===
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
0 OI28-00001 O28-0001 1 Noodle Soup Main 1 35000 3000 32000
1 OI28-00002 O28-0001 2 Noodle Soup Snack 1 18000 0 18000
2 OI28-00003 O28-0002 1 Fried Rice Main 4 35000 0 140000
=== Payments | shape=(155, 6) ===
payment_id order_id payment_timestamp payment_method payment_status amount
0 P28-00001 O28-0001 2026-01-06 10:09:00 GoPay SUCCESS 43000
1 P28-00002 O28-0002 2026-01-07 10:10:00 Credit Card SUCCESS 309000
2 P28-00003 O28-0003 2026-01-08 10:11:00 GoPay SUCCESS 425000
=== Restaurants | shape=(18, 6) ===
restaurant_id restaurant_name cuisine zone price_tier active_status
0 R28-01 SwiftBite Kitchen P28-01 Indonesian Jakarta Barat Budget Active
1 R28-02 SwiftBite Kitchen P28-02 Japanese Jakarta Selatan Mid Active
2 R28-03 SwiftBite Kitchen P28-03 Korean Jakarta Timur Premium 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 90 7 [customer_id, full_name, email, phone_number, ...
1 Orders 140 9 [order_id, customer_id, restaurant_id, order_t...
2 Order Items 374 9 [order_item_id, order_id, line_number, item_na...
3 Payments 155 6 [payment_id, order_id, payment_timestamp, paym...
4 Restaurants 18 6 [restaurant_id, restaurant_name, cuisine, zone...

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 90 90 0 0 True
1 Orders order_id 140 140 0 0 True
2 Order Items order_item_id 374 373 0 2 False
3 Order Items — candidate business key order_id, line_number 374 373 0 2 False
4 Payments payment_id 155 154 0 2 False
5 Restaurants restaurant_id 18 18 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
55 O28-0056 5
2 O28-0003 4
11 O28-0012 4
10 O28-0011 4
9 O28-0010 4
30 O28-0031 4
38 O28-0039 4
41 O28-0042 4
16 O28-0017 4
18 O28-0019 4
Payments cardinality
order_id payment_rows
8 O28-0009 2
134 O28-0135 2
107 O28-0108 2
98 O28-0099 2
44 O28-0045 2
53 O28-0054 2
71 O28-0072 2
62 O28-0063 2
35 O28-0036 2
25 O28-0026 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 90 7 0 0 3
1 Orders 140 9 0 0 0
2 Order Items 374 9 2 1 0
3 Payments 155 6 2 1 0
4 Restaurants 18 6 0 0 0

Column-Level Profiling

# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
    display(column_profile(orders_raw))
column dtype null_count null_pct unique_count sample_values
0 order_id str 0 0.0 140 [O28-0001, O28-0002, O28-0003, O28-0004, O28-0...
1 customer_id str 0 0.0 75 [C28-080, C28-079, C28-057, C28-076, C28-021]
2 restaurant_id str 0 0.0 18 [R28-11, R28-05, R28-09, R28-03, R28-01]
3 order_timestamp str 0 0.0 140 [2026-02-22 12:44:00, 2026-03-14 12:15:00, 202...
4 order_status str 0 0.0 4 [DELIVERED, CANCELLED, Delivered, DELIVERED ]
5 subtotal_amount int64 0 0.0 111 [50000, 299000, 410000, 39000, 79000]
6 delivery_fee int64 0 0.0 5 [8000, 10000, 15000, 18000, 12000]
7 discount_amount int64 0 0.0 4 [15000, 0, 10000, 5000]
8 total_amount int64 0 0.0 122 [43000, 309000, 425000, 54000, 79000]

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)

display(category_review(customers_raw, "customer_segment"))
display(category_review(orders_raw, "order_status"))
display(category_review(order_items_raw, "quantity"))
display(category_review(payments_raw, "payment_method"))
display(category_review(restaurants_raw, "price_tier"))
customer_segment count pct
0 Gold 22 0.2444
1 Silver 22 0.2444
2 Bronze 21 0.2333
3 Premium 20 0.2222
4 NaN 3 0.0333
5 gold 1 0.0111
6 Silver 1 0.0111
order_status count pct
0 DELIVERED 128 0.9143
1 CANCELLED 10 0.0714
2 Delivered 1 0.0071
3 DELIVERED 1 0.0071
quantity count pct
0 4 113 0.3021
1 3 102 0.2727
2 1 90 0.2406
3 2 67 0.1791
4 3 pcs 1 0.0027
5 18 1 0.0027
payment_method count pct
0 Credit Card 34 0.2194
1 GoPay 33 0.2129
2 QRIS 32 0.2065
3 OVO 30 0.1935
4 Bank Transfer 25 0.1613
5 GoPay 1 0.0065
price_tier count pct
0 Mid 6 0.3333
1 Premium 6 0.3333
2 Budget 5 0.2778
3 mid 1 0.0556

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

display(numeric_profile(orders_raw))
display(numeric_profile(order_items_raw))
count mean std min 25% 50% 75% max
subtotal_amount 140.0 253492.857143 147683.527887 15000.0 143250.0 249500.0 346500.0 729000.0
delivery_fee 140.0 12714.285714 3664.501525 8000.0 10000.0 12000.0 15000.0 18000.0
discount_amount 140.0 6392.857143 6059.295052 0.0 0.0 5000.0 10000.0 15000.0
total_amount 140.0 259814.285714 147402.428927 23000.0 150000.0 249000.0 353500.0 724000.0
count mean std min 25% 50% 75% max
line_number 374.0 2.074866 1.040602 1.0 1.0 2.0 3.0 4.0
unit_price 374.0 36649.732620 14265.118500 18000.0 25000.0 35000.0 48000.0 65000.0
line_discount 374.0 1550.802139 2019.034595 0.0 0.0 0.0 3000.0 5000.0
line_amount 374.0 95227.272727 58783.049157 13000.0 50000.0 86500.0 126000.0 324000.0

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",
]

findings = pd.DataFrame([
    {
        "finding_id": "F-01",
        "dataset": "swiftbite_customers.json",
        "field_key": "customer_segment",
        "finding_evidence": "3 dari 90 baris bernilai null: C28-012, C28-045, C28-078.",
        "why_it_matters": "Analisis pelanggan per segmen dapat tidak lengkap.",
        "verification_method": "Hitung null dan periksa sumber/master yang menentukan segmen pelanggan.",
        "verification_evidence_result": "3 null terkonfirmasi; nilai segmen pengganti belum diketahui.",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Keberadaan nilai kosong terbukti, tetapi nilai yang benar tidak dapat disimpulkan dari file ini.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-02",
        "dataset": "swiftbite_customers.json",
        "field_key": "customer_segment",
        "finding_evidence": "Ada Gold=22, gold=1, dan Silver dengan spasi akhir=1.",
        "why_it_matters": "Label yang sama dapat dihitung sebagai kategori terpisah.",
        "verification_method": "Periksa nilai unik dan konfirmasi daftar kategori segmen resmi.",
        "verification_evidence_result": "Variasi kapitalisasi dan spasi teridentifikasi; kesetaraan semantik perlu dikonfirmasi.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Jika label resmi mengonfirmasi kesetaraan, trim spasi dan gunakan kapitalisasi kanonis.",
        "cleaning_rule_id": "CR-CUST-001",
    },
    {
        "finding_id": "F-03",
        "dataset": "swiftbite_customers.json",
        "field_key": "phone_number",
        "finding_evidence": "Kolom phone_number terbaca sebagai int64.",
        "why_it_matters": "Nomor telepon merupakan identifier, bukan besaran numerik; format awal dapat hilang.",
        "verification_method": "Periksa panjang/pola nomor, lalu konversi ke string tanpa mengubah digit.",
        "verification_evidence_result": "90 nomor dibaca sebagai integer; aturan format nomor sumber belum dikonfirmasi.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Tipe teks lebih sesuai untuk menyimpan identifier; jangan mengubah digit tanpa bukti.",
        "cleaning_rule_id": "CR-CUST-002",
    },
    {
        "finding_id": "F-04",
        "dataset": "swiftbite_orders.xlsx",
        "field_key": "order_status",
        "finding_evidence": "DELIVERED=128, CANCELLED=10, Delivered=1, DELIVERED dengan spasi akhir=1.",
        "why_it_matters": "Agregasi status dapat memisahkan label yang sama.",
        "verification_method": "Periksa nilai unik dan konfirmasi kamus status resmi.",
        "verification_evidence_result": "Variasi kapitalisasi/spasi teridentifikasi; pemetaan perlu mengikuti label resmi.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Seragamkan trim dan kapitalisasi setelah memastikan label memiliki arti sama.",
        "cleaning_rule_id": "CR-ORD-001",
    },
    {
        "finding_id": "F-05",
        "dataset": "swiftbite_orders.xlsx",
        "field_key": "order_timestamp",
        "finding_evidence": "Format timestamp bercampur; contoh 22/03/2026 13:32 dan 05-01-2026 18:05.",
        "why_it_matters": "Parsing yang salah dapat mengubah urutan waktu dan periode laporan.",
        "verification_method": "Konfirmasi format tanggal sumber dan validasi nilai ambigu sebelum parsing final.",
        "verification_evidence_result": "Beberapa format ditemukan; 05-01-2026 belum dapat ditafsirkan dengan pasti tanpa konvensi sumber.",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Format belum aman diseragamkan sebelum arti tanggal ambigu dikonfirmasi.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-06",
        "dataset": "swiftbite_orders.xlsx",
        "field_key": "subtotal_amount; total_amount",
        "finding_evidence": "O28-0131 memiliki subtotal 729000 dan total 724000; kandidat outlier IQR.",
        "why_it_matters": "Outlier dapat menandakan kesalahan input, tetapi juga dapat merupakan transaksi sah.",
        "verification_method": "Hitung subtotal + delivery_fee - discount_amount dan cocokkan total dengan pembayaran sukses.",
        "verification_evidence_result": "729000 + 10000 - 15000 = 724000; pembayaran sukses terkait juga 724000.",
        "judgment": "KEEP",
        "reason_for_judgment": "Rumus dan pembayaran mendukung konsistensi nilai; nilai tinggi saja tidak membuktikan kesalahan.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-07",
        "dataset": "swiftbite_order_items.csv",
        "field_key": "order_item_id; order_id; line_amount",
        "finding_evidence": "OI28-00146 muncul dua kali identik pada O28-0056; line_amount 126000.",
        "why_it_matters": "Duplikasi dapat membuat jumlah item dan subtotal terhitung berlebihan.",
        "verification_method": "Cari ID berulang, bandingkan semua kolom, dan cocokkan dengan sumber transaksi.",
        "verification_evidence_result": "Duplikasi identik terkonfirmasi; total item O28-0056=533000 versus subtotal=407000, selisih 126000.",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Bukti duplikasi kuat, tetapi sumber transaksi perlu memastikan apakah baris harus dihapus.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-08",
        "dataset": "swiftbite_order_items.csv",
        "field_key": "quantity",
        "finding_evidence": 'OI28-00192 memiliki quantity "3 pcs".',
        "why_it_matters": "Teks satuan dalam kolom kuantitas dapat mengganggu operasi numerik.",
        "verification_method": "Periksa sumber item dan pastikan pcs hanya satuan, kemudian parsing angka.",
        "verification_evidence_result": "Satu nilai mengandung teks satuan; bagian numeriknya 3 dan rumus line_amount tetap cocok setelah parsing.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Simpan kuantitas sebagai numerik setelah arti suffix dikonfirmasi.",
        "cleaning_rule_id": "CR-ITEM-001",
    },
    {
        "finding_id": "F-09",
        "dataset": "swiftbite_order_items.csv",
        "field_key": "quantity",
        "finding_evidence": "Quantity 18 pada OI28-00293, order O28-0111, item Korean Chicken.",
        "why_it_matters": "Salah input kuantitas dapat memengaruhi nilai penjualan dan analisis permintaan.",
        "verification_method": "Cocokkan kuantitas dengan tiket atau catatan pesanan sumber.",
        "verification_evidence_result": "Nilai 18 terdeteksi sebagai kandidat outlier; kewajaran bisnis belum dikonfirmasi.",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Nilai tinggi dapat sah untuk pesanan besar; tidak ada dasar untuk mengubahnya otomatis.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-10",
        "dataset": "swiftbite_order_items.csv",
        "field_key": "item_name; item_category",
        "finding_evidence": "Satu baris Gyoza diberi kategori Drink.",
        "why_it_matters": "Laporan penjualan menurut kategori bisa salah jika klasifikasi keliru.",
        "verification_method": "Bandingkan nama item dengan master menu atau kamus kategori resmi.",
        "verification_evidence_result": "Kombinasi nama-kategori teridentifikasi; aturan kategori bisnis belum tersedia.",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Nama item saja belum cukup untuk membuktikan kategori yang benar.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-11",
        "dataset": "swiftbite_order_items.csv",
        "field_key": "quantity; unit_price; line_discount; line_amount",
        "finding_evidence": "Periksa rumus line_amount = quantity * unit_price - line_discount.",
        "why_it_matters": "Rumus yang tidak cocok dapat menandakan nilai transaksi yang salah.",
        "verification_method": "Hitung ulang line_amount setelah mengekstrak bagian numerik quantity.",
        "verification_evidence_result": "0 dari 374 baris berbeda dari hasil rumus setelah parsing kuantitas.",
        "judgment": "KEEP",
        "reason_for_judgment": "Pemeriksaan aritmetika mendukung nilai line_amount; duplikasi record tetap ditangani sebagai temuan terpisah.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-12",
        "dataset": "swiftbite_payments.txt",
        "field_key": "payment_id; order_id; amount",
        "finding_evidence": "P28-00028 muncul dua kali identik; FAILED, O28-0026, amount 39000.",
        "why_it_matters": "Duplikasi dapat menggelembungkan jumlah catatan atau percobaan pembayaran.",
        "verification_method": "Cari payment_id berulang dan cocokkan dengan log pembayaran sumber.",
        "verification_evidence_result": "Dua baris identik ditemukan; belum ada bukti sumber yang memastikan baris kedua tidak sah.",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Perlu memastikan apakah ini duplikasi pemuatan data atau catatan percobaan yang valid.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-13",
        "dataset": "swiftbite_payments.txt",
        "field_key": "payment_method",
        "finding_evidence": "GoPay muncul 33 kali dan GoPay dengan spasi akhir muncul 1 kali.",
        "why_it_matters": "Analisis metode pembayaran dapat memisahkan label yang sama.",
        "verification_method": "Periksa nilai unik dan pastikan variasi hanya whitespace.",
        "verification_evidence_result": "Satu variasi dengan spasi akhir ditemukan.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Trim spasi tepi dan gunakan label resmi setelah kesetaraan dikonfirmasi.",
        "cleaning_rule_id": "CR-PAY-001",
    },
    {
        "finding_id": "F-14",
        "dataset": "swiftbite_payments.txt",
        "field_key": "payment_timestamp",
        "finding_evidence": "P28-00068 memakai format 2026/03/08 11:10.",
        "why_it_matters": "Format yang tidak seragam dapat mengganggu parsing dan analisis waktu.",
        "verification_method": "Parse timestamp dan validasi tanggal/waktu terhadap konvensi sumber.",
        "verification_evidence_result": "Satu variasi format ditemukan; format target perlu ditetapkan.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Seragamkan representasi setelah parsing berhasil dan tanggal sumber tervalidasi.",
        "cleaning_rule_id": "CR-PAY-002",
    },
    {
        "finding_id": "F-15",
        "dataset": "swiftbite_payments.txt",
        "field_key": "payment_timestamp; order_timestamp",
        "finding_evidence": "87 dari 155 baris tampak memiliki waktu pembayaran sebelum waktu pesanan setelah parsing.",
        "why_it_matters": "Urutan waktu yang salah dapat merusak analisis proses transaksi.",
        "verification_method": "Validasi format tanggal, zona waktu, dan definisi timestamp sebelum membandingkan.",
        "verification_evidence_result": "87 kandidat anomali waktu terdeteksi; interpretasi tanggal dan aturan waktu belum dikonfirmasi.",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Belum cukup bukti untuk menyimpulkan timestamp salah sebelum konvensi sumber divalidasi.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-16",
        "dataset": "swiftbite_payments.txt",
        "field_key": "amount",
        "finding_evidence": "P28-00144 memiliki amount 724000, kandidat outlier IQR.",
        "why_it_matters": "Outlier pembayaran dapat menandakan kesalahan, tetapi juga bisa merupakan transaksi sah.",
        "verification_method": "Cocokkan amount dengan total pesanan dan status pembayaran.",
        "verification_evidence_result": "Status SUCCESS; amount 724000 sama dengan total O28-0131 dan rumus total konsisten.",
        "judgment": "KEEP",
        "reason_for_judgment": "Nilai didukung oleh total pesanan dan pembayaran sukses; tidak perlu diubah hanya karena besar.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-17",
        "dataset": "swiftbite_payments.txt",
        "field_key": "order_id; payment_status",
        "finding_evidence": "14 pesanan memiliki setidaknya satu SUCCESS dan satu FAILED.",
        "why_it_matters": "Menganggap setiap baris sebagai hasil pembayaran akhir dapat salah menghitung status transaksi.",
        "verification_method": "Kelompokkan pembayaran per order_id dan tinjau urutan percobaan di log pembayaran.",
        "verification_evidence_result": "14 order memiliki status SUCCESS dan FAILED; pola retry belum dikonfirmasi.",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Kombinasi status bisa merupakan retry yang sah; aturan bisnis diperlukan sebelum mengubah data.",
        "cleaning_rule_id": None,
    },
    {
        "finding_id": "F-18",
        "dataset": "swiftbite_restaurants.csv",
        "field_key": "price_tier",
        "finding_evidence": "Ada 6 Mid dan 1 mid dengan spasi akhir.",
        "why_it_matters": "Penghitungan restoran per tingkat harga dapat memisahkan label yang sama.",
        "verification_method": "Periksa nilai unik dan konfirmasi daftar price_tier resmi.",
        "verification_evidence_result": "Satu variasi kapitalisasi/spasi ditemukan.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Gunakan label kanonis setelah memastikan mid dan Mid memiliki arti sama.",
        "cleaning_rule_id": "CR-REST-001",
    },
    {
        "finding_id": "F-19",
        "dataset": "swiftbite_restaurants.csv",
        "field_key": "active_status",
        "finding_evidence": "Ada 16 Active, 1 active, dan 1 Inactive.",
        "why_it_matters": "Filter status aktif dapat melewatkan kategori karena perbedaan kapitalisasi.",
        "verification_method": "Periksa nilai unik dan konfirmasi label status resmi.",
        "verification_evidence_result": "Satu variasi kapitalisasi ditemukan.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Samakan kapitalisasi jika active dan Active merupakan label yang sama.",
        "cleaning_rule_id": "CR-REST-002",
    },
    
], 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 swiftbite_customers.json customer_segment 3 dari 90 baris bernilai null: C28-012, C28-04... Analisis pelanggan per segmen dapat tidak leng... Hitung null dan periksa sumber/master yang men... 3 null terkonfirmasi; nilai segmen pengganti b... INVESTIGATE Keberadaan nilai kosong terbukti, tetapi nilai... NaN
1 F-02 swiftbite_customers.json customer_segment Ada Gold=22, gold=1, dan Silver dengan spasi a... Label yang sama dapat dihitung sebagai kategor... Periksa nilai unik dan konfirmasi daftar kateg... Variasi kapitalisasi dan spasi teridentifikasi... STANDARDIZE Jika label resmi mengonfirmasi kesetaraan, tri... CR-CUST-001
2 F-03 swiftbite_customers.json phone_number Kolom phone_number terbaca sebagai int64. Nomor telepon merupakan identifier, bukan besa... Periksa panjang/pola nomor, lalu konversi ke s... 90 nomor dibaca sebagai integer; aturan format... STANDARDIZE Tipe teks lebih sesuai untuk menyimpan identif... CR-CUST-002
3 F-04 swiftbite_orders.xlsx order_status DELIVERED=128, CANCELLED=10, Delivered=1, DELI... Agregasi status dapat memisahkan label yang sama. Periksa nilai unik dan konfirmasi kamus status... Variasi kapitalisasi/spasi teridentifikasi; pe... STANDARDIZE Seragamkan trim dan kapitalisasi setelah memas... CR-ORD-001
4 F-05 swiftbite_orders.xlsx order_timestamp Format timestamp bercampur; contoh 22/03/2026 ... Parsing yang salah dapat mengubah urutan waktu... Konfirmasi format tanggal sumber dan validasi ... Beberapa format ditemukan; 05-01-2026 belum da... INVESTIGATE Format belum aman diseragamkan sebelum arti ta... NaN
5 F-06 swiftbite_orders.xlsx subtotal_amount; total_amount O28-0131 memiliki subtotal 729000 dan total 72... Outlier dapat menandakan kesalahan input, teta... Hitung subtotal + delivery_fee - discount_amou... 729000 + 10000 - 15000 = 724000; pembayaran su... KEEP Rumus dan pembayaran mendukung konsistensi nil... NaN
6 F-07 swiftbite_order_items.csv order_item_id; order_id; line_amount OI28-00146 muncul dua kali identik pada O28-00... Duplikasi dapat membuat jumlah item dan subtot... Cari ID berulang, bandingkan semua kolom, dan ... Duplikasi identik terkonfirmasi; total item O2... INVESTIGATE Bukti duplikasi kuat, tetapi sumber transaksi ... NaN
7 F-08 swiftbite_order_items.csv quantity OI28-00192 memiliki quantity "3 pcs". Teks satuan dalam kolom kuantitas dapat mengga... Periksa sumber item dan pastikan pcs hanya sat... Satu nilai mengandung teks satuan; bagian nume... STANDARDIZE Simpan kuantitas sebagai numerik setelah arti ... CR-ITEM-001
8 F-09 swiftbite_order_items.csv quantity Quantity 18 pada OI28-00293, order O28-0111, i... Salah input kuantitas dapat memengaruhi nilai ... Cocokkan kuantitas dengan tiket atau catatan p... Nilai 18 terdeteksi sebagai kandidat outlier; ... INVESTIGATE Nilai tinggi dapat sah untuk pesanan besar; ti... NaN
9 F-10 swiftbite_order_items.csv item_name; item_category Satu baris Gyoza diberi kategori Drink. Laporan penjualan menurut kategori bisa salah ... Bandingkan nama item dengan master menu atau k... Kombinasi nama-kategori teridentifikasi; atura... INVESTIGATE Nama item saja belum cukup untuk membuktikan k... NaN
10 F-11 swiftbite_order_items.csv quantity; unit_price; line_discount; line_amount Periksa rumus line_amount = quantity * unit_pr... Rumus yang tidak cocok dapat menandakan nilai ... Hitung ulang line_amount setelah mengekstrak b... 0 dari 374 baris berbeda dari hasil rumus sete... KEEP Pemeriksaan aritmetika mendukung nilai line_am... NaN
11 F-12 swiftbite_payments.txt payment_id; order_id; amount P28-00028 muncul dua kali identik; FAILED, O28... Duplikasi dapat menggelembungkan jumlah catata... Cari payment_id berulang dan cocokkan dengan l... Dua baris identik ditemukan; belum ada bukti s... INVESTIGATE Perlu memastikan apakah ini duplikasi pemuatan... NaN
12 F-13 swiftbite_payments.txt payment_method GoPay muncul 33 kali dan GoPay dengan spasi ak... Analisis metode pembayaran dapat memisahkan la... Periksa nilai unik dan pastikan variasi hanya ... Satu variasi dengan spasi akhir ditemukan. STANDARDIZE Trim spasi tepi dan gunakan label resmi setela... CR-PAY-001
13 F-14 swiftbite_payments.txt payment_timestamp P28-00068 memakai format 2026/03/08 11:10. Format yang tidak seragam dapat mengganggu par... Parse timestamp dan validasi tanggal/waktu ter... Satu variasi format ditemukan; format target p... STANDARDIZE Seragamkan representasi setelah parsing berhas... CR-PAY-002
14 F-15 swiftbite_payments.txt payment_timestamp; order_timestamp 87 dari 155 baris tampak memiliki waktu pembay... Urutan waktu yang salah dapat merusak analisis... Validasi format tanggal, zona waktu, dan defin... 87 kandidat anomali waktu terdeteksi; interpre... INVESTIGATE Belum cukup bukti untuk menyimpulkan timestamp... NaN
15 F-16 swiftbite_payments.txt amount P28-00144 memiliki amount 724000, kandidat out... Outlier pembayaran dapat menandakan kesalahan,... Cocokkan amount dengan total pesanan dan statu... Status SUCCESS; amount 724000 sama dengan tota... KEEP Nilai didukung oleh total pesanan dan pembayar... NaN
16 F-17 swiftbite_payments.txt order_id; payment_status 14 pesanan memiliki setidaknya satu SUCCESS da... Menganggap setiap baris sebagai hasil pembayar... Kelompokkan pembayaran per order_id dan tinjau... 14 order memiliki status SUCCESS dan FAILED; p... INVESTIGATE Kombinasi status bisa merupakan retry yang sah... NaN
17 F-18 swiftbite_restaurants.csv price_tier Ada 6 Mid dan 1 mid dengan spasi akhir. Penghitungan restoran per tingkat harga dapat ... Periksa nilai unik dan konfirmasi daftar price... Satu variasi kapitalisasi/spasi ditemukan. STANDARDIZE Gunakan label kanonis setelah memastikan mid d... CR-REST-001
18 F-19 swiftbite_restaurants.csv active_status Ada 16 Active, 1 active, dan 1 Inactive. Filter status aktif dapat melewatkan kategori ... Periksa nilai unik dan konfirmasi label status... Satu variasi kapitalisasi ditemukan. STANDARDIZE Samakan kapitalisasi jika active dan Active me... CR-REST-002

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 75 0 []
1 Orders → Restaurants 18 0 []
2 Order Items → Orders 140 0 []
3 Payments → Orders 140 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 OI28-00001 O28-0001 1 35000 3000 32000 32000.0 True
1 OI28-00002 O28-0001 1 18000 0 18000 18000.0 True
2 OI28-00003 O28-0002 4 35000 0 140000 140000.0 True
3 OI28-00004 O28-0002 3 22000 3000 63000 63000.0 True
4 OI28-00005 O28-0002 3 32000 0 96000 96000.0 True
order_id subtotal_amount delivery_fee discount_amount total_amount expected_total_amount order_formula_match
0 O28-0001 50000 8000 15000 43000 43000 True
1 O28-0002 299000 10000 0 309000 309000 True
2 O28-0003 410000 15000 0 425000 425000 True
3 O28-0004 39000 15000 0 54000 54000 True
4 O28-0005 79000 15000 15000 79000 79000 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 O28-0001 50000 50000 50000 True
1 O28-0002 299000 299000 299000 True
2 O28-0003 410000 410000 410000 True
3 O28-0004 39000 39000 39000 True
4 O28-0005 79000 79000 79000 True
Raw reconciliation mismatch count: 1

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.

import pandas as pd

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

rules_data = [
    {
        "rule_id": "CR-01",
        "source_finding_id": "F-02",
        "dataset": "swiftbite_customers.json",
        "trigger_condition": "Ada Gold=22, gold=1, dan Silver dengan spasi akhir=1",
        "decision": "STANDARDIZE",
        "cleaning_action": "strip() lalu title(); NULL tidak diubah",
        "reason_evidence": "gold dan Silver setara Gold dan Silver",
        "implementation_note": "Konfirmasi label kanonis dahulu; simpan kolom asli",
        "validation_criteria": "Hanya Gold/Silver/Bronze/Premium + 3 NULL; 90 baris tetap",
        "rule_status": "Validated",
    },
    {
        "rule_id": "CR-02",
        "source_finding_id": "F-03",
        "dataset": "swiftbite_customers.json",
        "trigger_condition": "Kolom phone_number terbaca sebagai int64",
        "decision": "STANDARDIZE",
        "cleaning_action": "Ubah tipe phone_number menjadi string tanpa mengubah digit",
        "reason_evidence": "Kolom phone_number terbaca sebagai int64",
        "implementation_note": "Validasi panjang nomor dan kemungkinan nol di depan; jangan membuat digit baru",
        "validation_criteria": "Semua nilai non-null tersimpan sebagai teks dan digit sumber tetap terjaga",
        "rule_status": "Validated",
    },
    {
        "rule_id": "CR-03",
        "source_finding_id": "F-04",
        "dataset": "swiftbite_customers.json",
        "trigger_condition": "DELIVERED=128, CANCELLED=10, Delivered=1, DELIVERED dengan spasi akhir=1",
        "decision": "STANDARDIZE",
        "cleaning_action": "Trim spasi dan petakan status ke label resmi.",
        "reason_evidence": "Ditemukan Delivered dan DELIVERED dengan spasi akhir.",
        "implementation_note": "Gunakan mapping eksplisit berdasarkan kamus status; jangan hanya mengandalkan kapitalisasi",
        "validation_criteria": "Semua status valid sesuai daftar resmi dan jumlah baris tetap 140.",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-04",
        "source_finding_id": "F-05",
        "dataset": "swiftbite_orders.xlsx",
        "trigger_condition": "Format timestamp bercampur; contoh 22/03/2026 13:32 dan 05-01-2026 18:05",
        "decision": "STANDARDIZE",
        "cleaning_action": "Konfirmasi format sumber; parse hanya nilai yang interpretasinya jelas.",
        "reason_evidence": "Contoh format berbeda termasuk 05-01-2026 18:05.",
        "implementation_note": "Jangan menetapkan day-first atau month-first tanpa bukti sumber",
        "validation_criteria": "Semua timestamp berhasil diparsing sesuai konvensi yang disepakati",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-05",
        "source_finding_id": "F-07",
        "dataset": "swiftbite_order_items.csv",
        "trigger_condition": "OI28-00146 muncul dua kali identik pada O28-0056",
        "decision": "FIX",
        "cleaning_action": "Bandingkan baris duplikat dengan sumber transaksi sebelum memilih baris yang akan dihapus",
        "reason_evidence": "OI28-00146 muncul dua kali",
        "implementation_note": "Jangan drop_duplicates langsung pada data final",
        "validation_criteria": "Keputusan duplikasi didukung sumber; setelah tindakan, jumlah item dan subtotal dapat direkonsiliasi.",
        "rule_status": "Approved",
    },
]

cleaning_rules = pd.DataFrame(rules_data, 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-02 swiftbite_customers.json Ada Gold=22, gold=1, dan Silver dengan spasi a... STANDARDIZE strip() lalu title(); NULL tidak diubah gold dan Silver setara Gold dan Silver Konfirmasi label kanonis dahulu; simpan kolom ... Hanya Gold/Silver/Bronze/Premium + 3 NULL; 90 ... Validated
1 CR-02 F-03 swiftbite_customers.json Kolom phone_number terbaca sebagai int64 STANDARDIZE Ubah tipe phone_number menjadi string tanpa me... Kolom phone_number terbaca sebagai int64 Validasi panjang nomor dan kemungkinan nol di ... Semua nilai non-null tersimpan sebagai teks da... Validated
2 CR-03 F-04 swiftbite_customers.json DELIVERED=128, CANCELLED=10, Delivered=1, DELI... STANDARDIZE Trim spasi dan petakan status ke label resmi. Ditemukan Delivered dan DELIVERED dengan spasi... Gunakan mapping eksplisit berdasarkan kamus st... Semua status valid sesuai daftar resmi dan jum... Approved
3 CR-04 F-05 swiftbite_orders.xlsx Format timestamp bercampur; contoh 22/03/2026 ... STANDARDIZE Konfirmasi format sumber; parse hanya nilai ya... Contoh format berbeda termasuk 05-01-2026 18:05. Jangan menetapkan day-first atau month-first t... Semua timestamp berhasil diparsing sesuai konv... Approved
4 CR-05 F-07 swiftbite_order_items.csv OI28-00146 muncul dua kali identik pada O28-0056 FIX Bandingkan baris duplikat dengan sumber transa... OI28-00146 muncul dua kali Jangan drop_duplicates langsung pada data final Keputusan duplikasi didukung sumber; setelah t... Approved

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
# ============================================================
# Hanya mengubah dataframe *_clean. Dataframe *_raw tidak disentuh.

# CR-01 — Standarisasi customer_segment
# Source Finding: F-02
# Evidence basis: "gold" dan "Silver " setara Gold dan Silver
# Validation criteria: hanya Gold/Silver/Bronze/Premium + 3 NULL
customers_clean["customer_segment"] = (
    customers_clean["customer_segment"].str.strip().str.title()
)  # NULL tetap NULL (F-01 tidak diimputasi)

# CR-02 — phone_number dijaga sebagai teks 12 digit
# Source Finding: F-03
# Evidence basis: raw JSON berupa teks berawalan 0; pd.read_json mengubahnya ke int64
# Validation criteria: 90/90 panjang 12
customers_clean["phone_number"] = (
    customers_clean["phone_number"].astype(str).str.zfill(12)
)

# CR-03 — Standarisasi order_status
# Source Finding: F-04
# Evidence basis: "Delivered" dan "DELIVERED " setara DELIVERED
# Validation criteria: hanya DELIVERED dan CANCELLED
orders_clean["order_status"] = (
    orders_clean["order_status"].str.strip().str.upper()
)

# CR-04 — Seragamkan order_timestamp ke YYYY-MM-DD HH:MM:SS
# Source Finding: F-05
# Evidence basis: format non-ISO day-first; 05-01-2026 = 5 Jan 2026 (rentang tanggal data)
# Validation criteria: 140/140 format ISO, 0 gagal parse
def parse_order_ts(value):
    s = str(value).strip()
    for fmt in ("%Y-%m-%d %H:%M:%S", "%d/%m/%Y %H:%M",
                "%Y/%m/%d %H:%M", "%d-%m-%Y %H:%M"):
        try:
            return pd.to_datetime(s, format=fmt).strftime("%Y-%m-%d %H:%M:%S")
        except ValueError:
            pass
    raise ValueError(f"Format order_timestamp tidak dikenali: {s}")

orders_clean["order_timestamp"] = orders_clean["order_timestamp"].map(parse_order_ts)

# CR-05 — quantity "3 pcs" menjadi angka
# Source Finding: F-08
# Evidence basis: line_amount 66.000 = 3 x 22.000
# Validation criteria: quantity numerik; V-09 mismatch 0
order_items_clean["quantity"] = (
    order_items_clean["quantity"].astype(str).str.extract(r"(\d+)")[0].astype(int)
)

# CR-06 — Hapus duplikat persis di order_items (FIX)
# Source Finding: F-07
# Evidence basis: OI28-00146 identik di seluruh kolom; subtotal O28-0056 hanya cocok tanpa salinan kedua
# Validation criteria: order_item_id unik; V-11 mismatch 0
order_items_clean = order_items_clean.drop_duplicates(keep="first").reset_index(drop=True)

# CR-07 — Trim payment_method
# Source Finding: F-13
# Evidence basis: "GoPay " setara GoPay
# Validation criteria: 5 metode pembayaran
payments_clean["payment_method"] = payments_clean["payment_method"].str.strip()

# CR-08 — Seragamkan payment_timestamp ke YYYY-MM-DD HH:MM:SS
# Source Finding: F-14
# Evidence basis: P28-00068 "2026/03/08 11:10" cocok dengan urutan payment_id tetangga
# Validation criteria: 154/154 format ISO
payments_clean["payment_timestamp"] = pd.to_datetime(
    payments_clean["payment_timestamp"].astype(str).str.replace("/", "-", regex=False),
    format="mixed",
).dt.strftime("%Y-%m-%d %H:%M:%S")

# CR-09 — Hapus duplikat persis di payments (FIX)
# Source Finding: F-12
# Evidence basis: P28-00028 identik di 6 kolom dan berada di akhir file
# Validation criteria: payment_id unik; repeated order_id (retry) TIDAK dihapus
payments_clean = payments_clean.drop_duplicates(keep="first").reset_index(drop=True)

# CR-10 — Standarisasi price_tier
# Source Finding: F-15
# Evidence basis: "mid " setara Mid
# Validation criteria: hanya Mid, Premium, Budget
restaurants_clean["price_tier"] = restaurants_clean["price_tier"].str.strip().str.title()

# CR-11 — Standarisasi active_status
# Source Finding: F-16
# Evidence basis: "active" setara Active
# Validation criteria: hanya Active dan Inactive
restaurants_clean["active_status"] = restaurants_clean["active_status"].str.strip().str.title()

print("Cleaning selesai (customers, orders, order_items, payments, restaurants):",
      len(customers_clean), len(orders_clean), len(order_items_clean),
      len(payments_clean), len(restaurants_clean))
Cleaning selesai (customers, orders, order_items, payments, restaurants): 90 140 373 154 18

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",
]

cleaning_log_rows = [
    {
        "log_id": "CL-01",
        "rule_id": "CR-01",
        "dataset": "swiftbite_customers.json",
        "affected_records_scope": "C28-023, C28-064",
        "action_performed": "Trim + title case pada customer_segment",
        "before_evidence": "gold 1; \"Silver \" 1; Gold 22; Silver 22; NULL 3",
        "after_evidence": "Gold 23; Silver 23; Bronze 21; Premium 20; NULL 3",
        "rows_records_affected": 2,
        "result": "Validated",
        "notes": "3 NULL (C28-012, C28-045, C28-078) sengaja tidak diubah (F-01)",
    },
    {
        "log_id": "CL-02",
        "rule_id": "CR-02",
        "dataset": "swiftbite_customers.json",
        "affected_records_scope": "Seluruh 90 baris",
        "action_performed": "Phone dijadikan teks 12 digit (zero-pad)",
        "before_evidence": "int64, mis. 82800000001 (11 digit)",
        "after_evidence": "Teks, mis. 082800000001; 90/90 panjang 12",
        "rows_records_affected": 90,
        "result": "Validated",
        "notes": "Dihitung terhadap hasil pd.read_json Starter; di raw JSON nilainya sudah teks",
    },
    {
        "log_id": "CL-03",
        "rule_id": "CR-03",
        "dataset": "swiftbite_orders.xlsx",
        "affected_records_scope": "O28-0048, O28-0096",
        "action_performed": "Trim + upper pada order_status",
        "before_evidence": "DELIVERED 128; Delivered 1; \"DELIVERED \" 1; CANCELLED 10",
        "after_evidence": "DELIVERED 130; CANCELLED 10",
        "rows_records_affected": 2,
        "result": "Validated",
        "notes": "",
    },
    {
        "log_id": "CL-04",
        "rule_id": "CR-04",
        "dataset": "swiftbite_orders.xlsx",
        "affected_records_scope": "O28-0027, O28-0084, O28-0119",
        "action_performed": "Standarisasi order_timestamp ke YYYY-MM-DD HH:MM:SS",
        "before_evidence": "22/03/2026 13:32; 2026/02/25 19:05; 05-01-2026 18:05",
        "after_evidence": "2026-03-22 13:32:00; 2026-02-25 19:05:00; 2026-01-05 18:05:00",
        "rows_records_affected": 3,
        "result": "Validated",
        "notes": "O28-0119 dibaca day-first (asumsi berbasis rentang tanggal, F-05)",
    },
    {
        "log_id": "CL-05",
        "rule_id": "CR-05",
        "dataset": "swiftbite_order_items.csv",
        "affected_records_scope": "OI28-00192 (O28-0073)",
        "action_performed": "Ekstrak digit dari quantity",
        "before_evidence": "quantity \"3 pcs\" (teks)",
        "after_evidence": "quantity 3 (integer)",
        "rows_records_affected": 1,
        "result": "Validated",
        "notes": "line_amount 66.000 = 3 x 22.000",
    },
    {
        "log_id": "CL-06",
        "rule_id": "CR-06",
        "dataset": "swiftbite_order_items.csv",
        "affected_records_scope": "OI28-00146 (O28-0056), baris ke-374",
        "action_performed": "Hapus salinan kedua yang identik",
        "before_evidence": "374 baris; SUM item O28-0056 = 533.000 vs subtotal 407.000",
        "after_evidence": "373 baris; SUM item O28-0056 = 407.000 = subtotal",
        "rows_records_affected": 1,
        "result": "Validated",
        "notes": "Mismatch rekonsiliasi item vs subtotal turun dari 1 menjadi 0",
    },
    {
        "log_id": "CL-07",
        "rule_id": "CR-07",
        "dataset": "swiftbite_payments.txt",
        "affected_records_scope": "P28-00097 (O28-0088)",
        "action_performed": "Trim pada payment_method",
        "before_evidence": "GoPay 33; \"GoPay \" 1",
        "after_evidence": "GoPay 34",
        "rows_records_affected": 1,
        "result": "Validated",
        "notes": "",
    },
    {
        "log_id": "CL-08",
        "rule_id": "CR-08",
        "dataset": "swiftbite_payments.txt",
        "affected_records_scope": "P28-00068 (O28-0062)",
        "action_performed": "Standarisasi payment_timestamp",
        "before_evidence": "2026/03/08 11:10",
        "after_evidence": "2026-03-08 11:10:00",
        "rows_records_affected": 1,
        "result": "Validated",
        "notes": "",
    },
    {
        "log_id": "CL-09",
        "rule_id": "CR-09",
        "dataset": "swiftbite_payments.txt",
        "affected_records_scope": "P28-00028 (O28-0026), baris ke-155",
        "action_performed": "Hapus salinan kedua yang identik",
        "before_evidence": "155 baris; payment_id unik 154; FAILED 25",
        "after_evidence": "154 baris; payment_id unik 154; FAILED 24",
        "rows_records_affected": 1,
        "result": "Validated",
        "notes": "order_id berulang (14 order retry) tidak disentuh",
    },
    {
        "log_id": "CL-10",
        "rule_id": "CR-10",
        "dataset": "swiftbite_restaurants.csv",
        "affected_records_scope": "R28-07",
        "action_performed": "Trim + title case pada price_tier",
        "before_evidence": "Mid 6; \"mid \" 1",
        "after_evidence": "Mid 7; Premium 6; Budget 5",
        "rows_records_affected": 1,
        "result": "Validated",
        "notes": "",
    },
    {
        "log_id": "CL-11",
        "rule_id": "CR-11",
        "dataset": "swiftbite_restaurants.csv",
        "affected_records_scope": "R28-15",
        "action_performed": "Title case pada active_status",
        "before_evidence": "Active 16; active 1; Inactive 1",
        "after_evidence": "Active 17; Inactive 1",
        "rows_records_affected": 1,
        "result": "Validated",
        "notes": "",
    },
]

cleaning_log = pd.DataFrame(cleaning_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 swiftbite_customers.json C28-023, C28-064 Trim + title case pada customer_segment gold 1; "Silver " 1; Gold 22; Silver 22; NULL 3 Gold 23; Silver 23; Bronze 21; Premium 20; NULL 3 2 Validated 3 NULL (C28-012, C28-045, C28-078) sengaja tid...
1 CL-02 CR-02 swiftbite_customers.json Seluruh 90 baris Phone dijadikan teks 12 digit (zero-pad) int64, mis. 82800000001 (11 digit) Teks, mis. 082800000001; 90/90 panjang 12 90 Validated Dihitung terhadap hasil pd.read_json Starter; ...
2 CL-03 CR-03 swiftbite_orders.xlsx O28-0048, O28-0096 Trim + upper pada order_status DELIVERED 128; Delivered 1; "DELIVERED " 1; CA... DELIVERED 130; CANCELLED 10 2 Validated
3 CL-04 CR-04 swiftbite_orders.xlsx O28-0027, O28-0084, O28-0119 Standarisasi order_timestamp ke YYYY-MM-DD HH:... 22/03/2026 13:32; 2026/02/25 19:05; 05-01-2026... 2026-03-22 13:32:00; 2026-02-25 19:05:00; 2026... 3 Validated O28-0119 dibaca day-first (asumsi berbasis ren...
4 CL-05 CR-05 swiftbite_order_items.csv OI28-00192 (O28-0073) Ekstrak digit dari quantity quantity "3 pcs" (teks) quantity 3 (integer) 1 Validated line_amount 66.000 = 3 x 22.000
5 CL-06 CR-06 swiftbite_order_items.csv OI28-00146 (O28-0056), baris ke-374 Hapus salinan kedua yang identik 374 baris; SUM item O28-0056 = 533.000 vs subt... 373 baris; SUM item O28-0056 = 407.000 = subtotal 1 Validated Mismatch rekonsiliasi item vs subtotal turun d...
6 CL-07 CR-07 swiftbite_payments.txt P28-00097 (O28-0088) Trim pada payment_method GoPay 33; "GoPay " 1 GoPay 34 1 Validated
7 CL-08 CR-08 swiftbite_payments.txt P28-00068 (O28-0062) Standarisasi payment_timestamp 2026/03/08 11:10 2026-03-08 11:10:00 1 Validated
8 CL-09 CR-09 swiftbite_payments.txt P28-00028 (O28-0026), baris ke-155 Hapus salinan kedua yang identik 155 baris; payment_id unik 154; FAILED 25 154 baris; payment_id unik 154; FAILED 24 1 Validated order_id berulang (14 order retry) tidak disentuh
9 CL-10 CR-10 swiftbite_restaurants.csv R28-07 Trim + title case pada price_tier Mid 6; "mid " 1 Mid 7; Premium 6; Budget 5 1 Validated
10 CL-11 CR-11 swiftbite_restaurants.csv R28-15 Title case pada active_status Active 16; active 1; Inactive 1 Active 17; Inactive 1 1 Validated

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
    schema_rows = []
    for label, required in REQUIRED_COLUMNS.items():
        missing_cols = sorted(required - set(clean_tables[label].columns))
        schema_rows.append({
            "dataset": label,
            "rows": len(clean_tables[label]),
            "missing_required_columns": ", ".join(missing_cols),
        })
    record_check(
        "V-01", "Schema", "All clean datasets",
        "Apakah seluruh required column masih tersedia?",
        "Required columns available",
        schema_rows,
        all(r["missing_required_columns"] == "" for r in schema_rows),
    )

    # V-02 — Customers key
    result = key_check(customers_clean, "customer_id", "Customers")
    record_check(
        "V-02", "Key", "Customers",
        "Apakah customer_id valid sesuai grain customer?",
        "No null key; uniqueness sesuai grain",
        result.to_dict("records")[0],
        bool(result.loc[0, "candidate_key_unique"]),
    )

    # V-03 — Orders key
    result = key_check(orders_clean, "order_id", "Orders")
    record_check(
        "V-03", "Key", "Orders",
        "Apakah order_id valid sesuai grain order?",
        "No null key; one row per order",
        result.to_dict("records")[0],
        bool(result.loc[0, "candidate_key_unique"]),
    )

    # V-04 — Order Items key (CR-06)
    result_id = key_check(order_items_clean, "order_item_id", "Order Items")
    result_bk = key_check(order_items_clean, ["order_id", "line_number"], "Order Items business key")
    record_check(
        "V-04", "Key", "Order Items",
        "Apakah order_item_id / candidate business key valid?",
        "No invalid duplicate key",
        {"order_item_id": result_id.to_dict("records")[0],
         "order_id+line_number": result_bk.to_dict("records")[0]},
        bool(result_id.loc[0, "candidate_key_unique"] and result_bk.loc[0, "candidate_key_unique"]),
        related_rule_id="CR-06",
        notes="Sebelum cleaning: OI28-00146 muncul dua kali.",
    )

    # V-05 — Payments key (CR-09); repeated order_id valid menurut grain
    result = key_check(payments_clean, "payment_id", "Payments")
    repeated_order_rows = int(payments_clean["order_id"].duplicated().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",
        {**result.to_dict("records")[0], "repeated_order_id_rows": repeated_order_rows},
        bool(result.loc[0, "candidate_key_unique"]) and repeated_order_rows > 0,
        related_rule_id="CR-09",
        notes="Sebelum cleaning: P28-00028 muncul dua kali. Retry payment per order_id tetap dipertahankan.",
    )
else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes:
Customers (90, 7)
Orders (140, 9)
Order Items (373, 9)
Payments (154, 6)
Restaurants (18, 6)

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
    # ========================================================
    ISO_TS = r"^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$"

    # V-06 — Customers: kategori dan representasi missing (CR-01, CR-02)
    seg_values = set(customers_clean["customer_segment"].dropna().unique())
    seg_null = int(customers_clean["customer_segment"].isna().sum())
    phone_len_ok = bool((customers_clean["phone_number"].astype(str).str.len() == 12).all())
    record_check(
        "V-06", "Value", "Customers / categories",
        "Apakah category dan missing representation sudah konsisten?",
        "No unexpected canonical-category issue",
        {"segment_values": sorted(seg_values), "segment_null": seg_null, "phone_len_12_all": phone_len_ok},
        seg_values == {"Gold", "Silver", "Bronze", "Premium"} and phone_len_ok,
        related_rule_id="CR-01, CR-02",
        notes="3 NULL customer_segment sengaja tidak diimputasi (F-01).",
    )

    # V-07 — Orders: status dan format timestamp (CR-03, CR-04)
    status_values = orders_clean["order_status"].value_counts().to_dict()
    ts_iso = int(orders_clean["order_timestamp"].astype(str).str.match(ISO_TS).sum())
    record_check(
        "V-07", "Value", "Orders / categories",
        "Apakah order status dan representation sudah konsisten?",
        "Only approved canonical values",
        {"order_status": status_values, "order_timestamp_iso": ts_iso, "rows": len(orders_clean)},
        set(status_values) == {"DELIVERED", "CANCELLED"} and ts_iso == len(orders_clean),
        related_rule_id="CR-03, CR-04",
    )

    # V-08 — Payments / Restaurants (CR-07, CR-08, CR-10, CR-11)
    pay_methods = set(payments_clean["payment_method"].unique())
    tiers = set(restaurants_clean["price_tier"].unique())
    actives = set(restaurants_clean["active_status"].unique())
    pay_ts_iso = int(payments_clean["payment_timestamp"].astype(str).str.match(ISO_TS).sum())
    record_check(
        "V-08", "Value", "Payments / Restaurants",
        "Apakah category representation sudah konsisten?",
        "Only approved canonical values",
        {"payment_method": sorted(pay_methods), "price_tier": sorted(tiers),
         "active_status": sorted(actives), "payment_timestamp_iso": pay_ts_iso},
        pay_methods == {"GoPay", "Credit Card", "QRIS", "OVO", "Bank Transfer"}
        and tiers == {"Budget", "Mid", "Premium"}
        and actives == {"Active", "Inactive"}
        and pay_ts_iso == len(payments_clean),
        related_rule_id="CR-07, CR-08, CR-10, CR-11",
    )

    # V-09 — Order Items formula (CR-05)
    qty = pd.to_numeric(order_items_clean["quantity"], errors="coerce")
    expected_line = (
        qty * pd.to_numeric(order_items_clean["unit_price"], errors="coerce")
        - pd.to_numeric(order_items_clean["line_discount"], errors="coerce")
    )
    line_mismatch = int((expected_line != pd.to_numeric(order_items_clean["line_amount"], errors="coerce")).sum())
    record_check(
        "V-09", "Business Rule", "Order Items",
        "Apakah line_amount memenuhi formula item?",
        "quantity × unit_price - line_discount = line_amount",
        {"rows": len(order_items_clean), "mismatch": line_mismatch},
        line_mismatch == 0,
        related_rule_id="CR-05",
    )

    # V-10 — Orders formula
    expected_total = (
        orders_clean["subtotal_amount"] + orders_clean["delivery_fee"] - orders_clean["discount_amount"]
    )
    total_mismatch = int((expected_total != orders_clean["total_amount"]).sum())
    record_check(
        "V-10", "Business Rule", "Orders",
        "Apakah total_amount memenuhi formula order?",
        "subtotal + delivery_fee - discount_amount = total_amount",
        {"rows": len(orders_clean), "mismatch": total_mismatch},
        total_mismatch == 0,
    )

    # V-11 — Orders ↔ Order Items reconciliation (CR-06)
    item_sum = order_items_clean.groupby("order_id")["line_amount"].sum()
    recon = orders_clean.set_index("order_id")["subtotal_amount"].sub(item_sum, fill_value=0)
    recon_mismatch = int((recon != 0).sum())
    record_check(
        "V-11", "Cross-table", "Orders ↔ Order Items",
        "Apakah subtotal order konsisten dengan detail item?",
        "SUM(line_amount) = subtotal_amount",
        {"orders": len(orders_clean), "mismatch": recon_mismatch},
        recon_mismatch == 0,
        related_rule_id="CR-06",
        notes="Sebelum cleaning: 1 mismatch (O28-0056, selisih -126.000).",
    )

    # V-12 s.d. V-14 — Foreign key checks pada clean data
    fk_cust = fk_check(orders_clean, customers_clean, "customer_id", "customer_id", "Orders → Customers")
    record_check(
        "V-12", "Cross-table", "Orders → Customers",
        "Apakah seluruh customer reference valid?",
        "Unmatched customer = 0",
        fk_cust, fk_cust["unmatched_count"] == 0,
    )
    fk_rest = fk_check(orders_clean, restaurants_clean, "restaurant_id", "restaurant_id", "Orders → Restaurants")
    record_check(
        "V-13", "Cross-table", "Orders → Restaurants",
        "Apakah seluruh restaurant reference valid?",
        "Unmatched restaurant = 0",
        fk_rest, fk_rest["unmatched_count"] == 0,
    )
    fk_items = fk_check(order_items_clean, orders_clean, "order_id", "order_id", "Order Items → Orders")
    fk_pay = fk_check(payments_clean, orders_clean, "order_id", "order_id", "Payments → Orders")
    record_check(
        "V-14", "Cross-table", "Order Items / Payments → Orders",
        "Apakah seluruh order reference valid?",
        "Orphan order reference = 0",
        {"order_items": fk_items, "payments": fk_pay},
        fk_items["unmatched_count"] == 0 and fk_pay["unmatched_count"] == 0,
    )
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
# ============================================================
if DATA_READY:
    o_raw = orders_raw.set_index("order_id")
    o_clean = orders_clean.set_index("order_id")
    p_raw = payments_raw.drop_duplicates().set_index("payment_id")
    p_clean = payments_clean.set_index("payment_id")

    def status_sets(df):
        return df.groupby("order_id")["payment_status"].agg(set)

    raw_sets, clean_sets = status_sets(payments_raw.drop_duplicates()), status_sets(payments_clean)
    retry_raw = int(sum(s == {"SUCCESS", "FAILED"} for s in raw_sets))
    retry_clean = int(sum(s == {"SUCCESS", "FAILED"} for s in clean_sets))
    only_failed_clean = int(sum(s == {"FAILED"} for s in clean_sets))

    keep_checks = {
        # F-06 — O28-0131 (outlier order yang konsisten)
        "F-06 O28-0131 subtotal/total utuh": (
            o_clean.loc["O28-0131", ["subtotal_amount", "total_amount"]].tolist()
            == o_raw.loc["O28-0131", ["subtotal_amount", "total_amount"]].tolist()
            == [729000, 724000]
        ),
        # F-11 — P28-00144 (outlier payment yang konsisten)
        "F-11 P28-00144 amount utuh": (
            int(p_clean.loc["P28-00144", "amount"]) == int(p_raw.loc["P28-00144", "amount"]) == 724000
        ),
        # F-19 — retry payment (order_id berulang) tidak dihapus
        "F-19 14 order retry + 10 order only-FAILED utuh": (
            retry_raw == retry_clean == 14 and only_failed_clean == 10
        ),
        # F-20 — 8 order ke restoran Inactive tidak dihapus
        "F-20 8 order R28-18 utuh": (
            int((orders_clean["restaurant_id"] == "R28-18").sum())
            == int((orders_raw["restaurant_id"] == "R28-18").sum()) == 8
            and len(orders_clean) == len(orders_raw)
        ),
        # INVESTIGATE (F-01, F-09, F-10) tidak diubah
        "F-09 quantity 18 (OI28-00293) tidak diubah": (
            int(order_items_clean.loc[order_items_clean["order_item_id"] == "OI28-00293", "quantity"].iloc[0]) == 18
        ),
        "F-10 kategori item tidak diubah (hanya duplikat dihapus)": (
            int(((order_items_clean["item_name"] == "Gyoza") & (order_items_clean["item_category"] == "Drink")).sum())
            == int(((order_items_raw["item_name"] == "Gyoza") & (order_items_raw["item_category"] == "Drink")).sum()) - 1
        ),
        "F-01 3 NULL customer_segment tidak diimputasi": (
            int(customers_clean["customer_segment"].isna().sum()) == 3
        ),
    }
    record_check(
        "V-15", "Judgment", "KEEP findings",
        "Apakah legitimate anomaly yang diputuskan KEEP masih dipertahankan?",
        "KEEP findings remain unless new evidence justifies change",
        keep_checks,
        all(keep_checks.values()),
        notes="Mencakup F-06, F-11, F-19, F-20 (KEEP) dan memastikan item INVESTIGATE tidak ikut diubah.",
    )
else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")

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 [{'dataset': 'Customers', 'rows': 90, 'missing... PASS
1 V-02 Key Customers Apakah customer_id valid sesuai grain customer? No null key; uniqueness sesuai grain {'dataset': 'Customers', 'key_cols': 'customer... PASS
2 V-03 Key Orders Apakah order_id valid sesuai grain order? No null key; one row per order {'dataset': 'Orders', 'key_cols': 'order_id', ... PASS
3 V-04 Key Order Items Apakah order_item_id / candidate business key ... No invalid duplicate key {'order_item_id': {'dataset': 'Order Items', '... PASS CR-06 Sebelum cleaning: OI28-00146 muncul dua kali.
4 V-05 Key Payments Apakah payment_id unique tanpa menghapus repea... payment_id unique; repeated order_id allowed b... {'dataset': 'Payments', 'key_cols': 'payment_i... PASS CR-09 Sebelum cleaning: P28-00028 muncul dua kali. R...
5 V-06 Value Customers / categories Apakah category dan missing representation sud... No unexpected canonical-category issue {'segment_values': ['Bronze', 'Gold', 'Premium... PASS CR-01, CR-02 3 NULL customer_segment sengaja tidak diimputa...
6 V-07 Value Orders / categories Apakah order status dan representation sudah k... Only approved canonical values {'order_status': {'DELIVERED': 130, 'CANCELLED... PASS CR-03, CR-04
7 V-08 Value Payments / Restaurants Apakah category representation sudah konsisten? Only approved canonical values {'payment_method': ['Bank Transfer', 'Credit C... PASS CR-07, CR-08, CR-10, CR-11
8 V-09 Business Rule Order Items Apakah line_amount memenuhi formula item? quantity × unit_price - line_discount = line_a... {'rows': 373, 'mismatch': 0} PASS CR-05
9 V-10 Business Rule Orders Apakah total_amount memenuhi formula order? subtotal + delivery_fee - discount_amount = to... {'rows': 140, 'mismatch': 0} PASS
10 V-11 Cross-table Orders ↔ Order Items Apakah subtotal order konsisten dengan detail ... SUM(line_amount) = subtotal_amount {'orders': 140, 'mismatch': 0} PASS CR-06 Sebelum cleaning: 1 mismatch (O28-0056, selisi...
11 V-12 Cross-table Orders → Customers Apakah seluruh customer reference valid? Unmatched customer = 0 {'relationship': 'Orders → Customers', 'child_... PASS
12 V-13 Cross-table Orders → Restaurants Apakah seluruh restaurant reference valid? Unmatched restaurant = 0 {'relationship': 'Orders → Restaurants', 'chil... PASS
13 V-14 Cross-table Order Items / Payments → Orders Apakah seluruh order reference valid? Orphan order reference = 0 {'order_items': {'relationship': 'Order Items ... PASS
14 V-15 Judgment KEEP findings Apakah legitimate anomaly yang diputuskan KEEP... KEEP findings remain unless new evidence justi... {'F-06 O28-0131 subtotal/total utuh': True, 'F... PASS Mencakup F-06, F-11, F-19, F-20 (KEEP) dan mem...
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 V-01 sampai V-15 PASS dan Raw Integrity PASS. 11 cleaning rule (9 STANDARDIZE, 2 FIX) dijalankan hanya"
    " pada working copy dan dapat ditelusuri dari Finding ID ke Rule ID, Cleaning Log, lalu Validation. Hasilnya: 2"
    " baris duplikat dihapus (OI28-00146 dan P28-00028), 1 mismatch subtotal order terselesaikan, serta penulisan k"
    "ategori, tipe data, dan format waktu diseragamkan. Finding KEEP (F-06, F-11, F-19, F-20) tetap utuh. Finding I"
    "NVESTIGATE (F-01, F-09, F-10, F-17, F-18) tidak dikoreksi karena tidak ada evidence untuk menentukan nilai yan"
    "g benar; data tetap dipakai dengan catatan keterbatasan (3 segment NULL, quantity 18, kategori item tidak stab"
    "il, urutan waktu payment vs order, dan 6 order sebelum signup). Asumsi: O28-0119 dibaca day-first (5 Januari 2"
    "026) berdasarkan rentang tanggal data."
)
print(FINAL_JUDGMENT_RATIONALE)
PM1 DATA READINESS GATE: PASS
Seluruh V-01 sampai V-15 PASS dan Raw Integrity PASS. 11 cleaning rule (9 STANDARDIZE, 2 FIX) dijalankan hanya pada working copy dan dapat ditelusuri dari Finding ID ke Rule ID, Cleaning Log, lalu Validation. Hasilnya: 2 baris duplikat dihapus (OI28-00146 dan P28-00028), 1 mismatch subtotal order terselesaikan, serta penulisan kategori, tipe data, dan format waktu diseragamkan. Finding KEEP (F-06, F-11, F-19, F-20) tetap utuh. Finding INVESTIGATE (F-01, F-09, F-10, F-17, F-18) tidak dikoreksi karena tidak ada evidence untuk menentukan nilai yang benar; data tetap dipakai dengan catatan keterbatasan (3 segment NULL, quantity 18, kategori item tidak stabil, urutan waktu payment vs order, dan 6 order sebelum signup). Asumsi: O28-0119 dibaca day-first (5 Januari 2026) berdasarkan rentang tanggal data.

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

# Hapus tanda # pada baris berikut HANYA setelah FINAL_GATE: PASS
export_clean_data()
CLEAN PACKAGE SAVE: PASS
Saved to: D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
True

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

# Hapus tanda # pada baris berikut setelah evidence sudah diisi:
export_evidence()
EVIDENCE EXPORT: PASS
Saved to: D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\outputs\pm1_swiftbite
True

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
```