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

name_ok = PARTICIPANT_NAME.strip() not in {"", "Isi Nama Anda"}
variant_ok = (
    VARIANT_ID.startswith("SB-P2")
    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 : Nusaibah
Variant ID  : SB-P26

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 : /Users/mac/Downloads/Bootcamp_Data_Analyst
RAW_DIR        : /Users/mac/Downloads/Bootcamp_Data_Analyst/data/raw/pm1_swiftbite
PROCESSED_DIR  : /Users/mac/Downloads/Bootcamp_Data_Analyst/data/processed/pm1_swiftbite
OUTPUT_DIR     : /Users/mac/Downloads/Bootcamp_Data_Analyst/outputs/pm1_swiftbite
DOCS_DIR       : /Users/mac/Downloads/Bootcamp_Data_Analyst/docs/pm1_swiftbite

Environment Smoke Test — RUN

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

print("pandas :", pd.__version__)

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

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

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

2. Verify Source Package — RUN

Letakkan **5 file berikut langsung** di:

`data/raw/pm1_swiftbite/`

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

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

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

source_status = []

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

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

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

    SOURCE_ACCESS_PASS = len(missing_files) == 0

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

3. Load Raw Data & Schema Gate — RUN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Quick Source Preview — RUN

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

if DATA_READY:
    for name, df in raw_tables.items():
        print(f"\n=== {name} | shape={df.shape} ===")
        display(df.head(3))
else:
    print("PREVIEW SKIPPED — DATA READY GATE belum PASS.")
=== Customers | shape=(164, 7) ===
customer_id full_name email phone_number signup_date customer_zone customer_segment
0 C0001 Wahyu Sari wahyusari1@example.com 086481175070 2025-12-11 East Loyal
1 C0002 Dimas Permata dimaspermata2@example.com 085483504601 2025-08-11 South Regular
2 C0003 Zaki Wijaya zakiwijaya3@example.com 088025705563 2025-06-08 South Regular
=== Orders | shape=(320, 10) ===
order_id customer_id restaurant_id order_timestamp order_status promo_code subtotal_amount delivery_fee discount_amount total_amount
0 O00001 C0081 R0002 2026-03-24 09:05:00 completed NaN 229000 15000 0 244000
1 O00002 C0088 R0007 2026-03-15 22:25:00 completed NaN 62000 15000 15000 62000
2 O00003 C0095 R0012 2026-02-19 16:58:00 completed NaN 136000 18000 10000 144000
=== Order Items | shape=(875, 9) ===
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
0 OI00001 O00001 1 Rice Bowl Main 3 32000 0 96000
1 OI00002 O00001 2 Iced Tea Drink 3 9000 5000 22000
2 OI00003 O00001 3 Toast Snack 2 16000 0 32000
=== Payments | shape=(357, 7) ===
payment_id order_id payment_method payment_status payment_amount payment_timestamp attempt_number
0 P00001 O00001 e_wallet successful 244000 2026-03-24 09:08:00 1
1 P00002 O00002 bank_transfer successful 62000 2026-03-15 22:28:00 1
2 P00003 O00003 credit_card successful 144000 2026-02-19 17:01:00 1
=== Restaurants | shape=(30, 7) ===
restaurant_id restaurant_name cuisine_type restaurant_zone price_tier onboarding_date active_status
0 R0001 SwiftBite Partner 01 Fast Food East Mid 2025-03-28 active
1 R0002 SwiftBite Partner 02 Cafe & Beverage North Mid 2025-01-11 active
2 R0003 SwiftBite Partner 03 Dessert South Premium 2024-02-06 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.

import pandas as pd

# Pengaturan agar kolom teks ditampilkan penuh tanpa terpotong
pd.set_option('display.max_colwidth', None)

# Jika ada kolom lain yang terpotong horizontal, tambahkan ini:
pd.set_option('display.max_columns', None)

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 164 7 [customer_id, full_name, email, phone_number, signup_date, customer_zone, customer_segment]
1 Orders 320 10 [order_id, customer_id, restaurant_id, order_timestamp, order_status, promo_code, subtotal_amount, delivery_fee, discount_amount, total_amount]
2 Order Items 875 9 [order_item_id, order_id, line_number, item_name, item_category, quantity, unit_price, line_discount, line_amount]
3 Payments 357 7 [payment_id, order_id, payment_method, payment_status, payment_amount, payment_timestamp, attempt_number]
4 Restaurants 30 7 [restaurant_id, restaurant_name, cuisine_type, restaurant_zone, price_tier, onboarding_date, active_status]

5. Grain & Candidate Key Check — RUN

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

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

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

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

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

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

    display(key_results)
dataset key_cols rows unique_key_count missing_key_rows duplicate_key_rows candidate_key_unique
0 Customers customer_id 164 163 0 2 False
1 Orders order_id 320 320 0 0 True
2 Order Items order_item_id 875 875 0 0 True
3 Order Items — candidate business key order_id, line_number 875 875 0 0 True
4 Payments payment_id 357 357 0 0 True
5 Restaurants restaurant_id 30 30 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
292 O00293 5
34 O00035 5
133 O00134 5
135 O00136 5
105 O00106 5
112 O00113 5
215 O00216 5
10 O00011 5
270 O00271 4
268 O00269 4
Payments cardinality
order_id payment_rows
319 O00320 2
318 O00319 2
317 O00318 2
316 O00317 2
38 O00039 2
315 O00316 2
314 O00315 2
313 O00314 2
32 O00033 2
312 O00313 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 164 7 2 1 0
1 Orders 320 10 0 0 175
2 Order Items 875 9 0 0 0
3 Payments 357 7 0 0 0
4 Restaurants 30 7 0 0 0

Column-Level Profiling

# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
    display(column_profile(order_items_raw))
column dtype null_count null_pct unique_count sample_values
0 order_item_id str 0 0.0 875 [OI00001, OI00002, OI00003, OI00004, OI00005]
1 order_id str 0 0.0 320 [O00001, O00002, O00003, O00004, O00005]
2 line_number int64 0 0.0 5 [1, 2, 3, 4, 5]
3 item_name str 0 0.0 14 [Rice Bowl, Iced Tea, Toast, Nasi Ayam, Pudding]
4 item_category str 0 0.0 4 [Main, Drink, Snack, Dessert]
5 quantity str 0 0.0 5 [3, 2, 1, 4, 3 pcs]
6 unit_price int64 0 0.0 11 [32000, 9000, 16000, 28000, 18000]
7 line_discount int64 0 0.0 3 [0, 5000, 2000]
8 line_amount int64 0 0.0 74 [96000, 22000, 32000, 79000, 28000]

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)
if DATA_READY:
    display(category_review(order_items_raw, "line_amount"))

# Contoh:
# display(category_review(customers_raw, "customer_segment"))
# display(category_review(orders_raw, "order_status"))
# display(category_review(payments_raw, "payment_method"))
# display(category_review(restaurants_raw, "price_tier"))
line_amount count pct
0 18000 50 0.0571
1 22000 45 0.0514
2 32000 44 0.0503
3 20000 32 0.0366
4 16000 31 0.0354
5 40000 31 0.0354
6 27000 29 0.0331
7 28000 26 0.0297
8 52000 25 0.0286
9 26000 25 0.0286
10 84000 25 0.0286
11 54000 24 0.0274
12 42000 23 0.0263
13 48000 23 0.0263
14 44000 22 0.0251
15 36000 20 0.0229
16 64000 19 0.0217
17 14000 18 0.0206
18 56000 18 0.0206
19 9000 16 0.0183
20 60000 16 0.0183
21 79000 15 0.0171
22 96000 13 0.0149
23 126000 13 0.0149
24 78000 12 0.0137
25 13000 12 0.0137
26 30000 12 0.0137
27 24000 12 0.0137
28 66000 11 0.0126
29 23000 11 0.0126

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
if DATA_READY:
    display(numeric_profile(order_items_raw))

# Contoh:
# display(numeric_profile(orders_raw))
# display(numeric_profile(order_items_raw))
count mean std min 25% 50% 75% max
line_number 875.0 2.008000 0.965624 1.0 1.0 2.0 3.0 5.0
unit_price 875.0 24157.714286 19085.446653 9000.0 18000.0 22000.0 28000.0 533000.0
line_discount 875.0 1330.285714 1925.246998 0.0 0.0 0.0 2000.0 5000.0
line_amount 875.0 45080.000000 59020.595250 4000.0 22000.0 37000.0 57000.0 1594000.0

7. Candidate Findings Register

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

Jangan isi judgment sebelum verification evidence cukup.

# 1. Definisi daftar nama kolom
FINDING_COLUMNS = [
    "finding_id",
    "dataset",
    "field_key",
    "finding_evidence",
    "why_it_matters",
    "verification_method",
    "verification_evidence_result",
    "judgment",
    "reason_for_judgment",
    "cleaning_rule_id",
]

# 2. Definisi isi/baris temuan kamu
findings_data = [
    {
        "finding_id": "F-01",
        "dataset": "Customers",
        "field_key": "customer_id",
        "finding_evidence": "exact_duplicate_extras = 1",
        "why_it_matters": "order_item_id seharusnya unik per baris item. Duplikasi ID pelanggan dapat menyebabkan penghitungan ganda pada metrik transaksi.",
        "verification_method": "Pengecekan baris terduplikat penuh menggunakan `.duplicated()`",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "FIX",
        "reason_for_judgment": "Duplikat redundan harus dihapus agar Primary Key tetap unik.",
        "cleaning_rule_id": "CR-01",
    },
    {
        "finding_id": "F-02",
        "dataset": "Customers",
        "field_key": "email",
        "finding_evidence": "Ada 1 email berisi N/A",
        "why_it_matters": "Nilai placeholder 'N/A' menyulitkan komunikasi dan validasi format email pelanggan.",
        "verification_method": "Filter baris dengan email == 'N/A'",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "FIX",
        "reason_for_judgment": "Ubah placeholder 'N/A' menjadi NaN/null agar konsisten sebagai missing value.",
        "cleaning_rule_id": "CR-02",
    },
    {
        "finding_id": "F-03",
        "dataset": "Customers",
        "field_key": "customer_segment",
        "finding_evidence": "Inkonsistensi kategori: LOYAL, REGULAR",
        "why_it_matters": "Penulisan segmen huruf kapital atau spasi berlebih merusak pengelompokan segmen pelanggan.",
        "verification_method": "Pengecekan nilai unik dengan .value_counts()",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Standarkan penulisan teks menjadi format Title Case atau Lowercase yang seragam.",
        "cleaning_rule_id": "CR-03",
    },
    {
        "finding_id": "F-04",
        "dataset": "Orders",
        "field_key": "promo_code",
        "finding_evidence": "Ada 175 promo_code yang kosong (NaN)",
        "why_it_matters": "Nilai kosong pada promo_code adalah hal yang wajar jika transaksi tidak menggunakan diskon.",
        "verification_method": "Pengecekan jumlah null dengan .isna().sum()",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "SKIP",
        "reason_for_judgment": "Merupakan perilaku transaksi yang valid (pelanggan tidak memakai voucher/promo).",
        "cleaning_rule_id": "",  # Kosong karena SKIP
    },
    {
        "finding_id": "F-05",
        "dataset": "Orders",
        "field_key": "order_status",
        "finding_evidence": "Inkonsistensi kategori: COMPLETED",
        "why_it_matters": "Perbedaan kapitalisasi status pesanan dapat mengacaukan perhitungan jumlah transaksi yang sukses.",
        "verification_method": "Pengecekan nilai unik pada order_status",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Seragamkan semua nilai status menjadi huruf kapital (UPPERCASE).",
        "cleaning_rule_id": "CR-04",
    },
    {
        "finding_id": "F-06",
        "dataset": "Order Items",
        "field_key": "quantity",
        "finding_evidence": "Inkonsistensi format: 3 pcs",
        "why_it_matters": "Kehadiran karakter string ('pcs') pada kolom kuantitas mencegah perhitungan numerik/perkalian.",
        "verification_method": "Pengecekan tipe data dan ekstraksi string non-numerik",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "FIX",
        "reason_for_judgment": "Hapus teks 'pcs', ambil angka numeriknya saja, lalu ubah tipe data menjadi integer.",
        "cleaning_rule_id": "CR-05",
    },
    {
        "finding_id": "F-07",
        "dataset": "Order Items",
        "field_key": "unit_price",
        "finding_evidence": "outlier unit_price di atas rata-rata: 533.000",
        "why_it_matters": "Harga item yang sangat tinggi bisa merupakan kesalahan input atau barang premium.",
        "verification_method": "Pengecekan distribusi nilai / IQR / Boxplot",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "INVESTIGATE",
        "reason_for_judgment": "Perlu dipastikan apakah menu tersebut memang bernilai mahal (paket catering/porsi besar) atau murni salah input.",
        "cleaning_rule_id": "",  # Kosong karena INVESTIGATE
    },
    {
        "finding_id": "F-08",
        "dataset": "Payments",
        "field_key": "payment_method",
        "finding_evidence": "Inkonsistensi kategori: E WALLET, BANK TRANSFER",
        "why_it_matters": "Format variatif seperti spasi atau strip ('E WALLET' vs 'e-wallet') menyulitkan analisis metode pembayaran.",
        "verification_method": "Pengecekan nilai unik pada payment_method",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Standarkan penulisan nama metode pembayaran ke format yang disepakati.",
        "cleaning_rule_id": "CR-06",
    },
    {
        "finding_id": "F-09",
        "dataset": "Restaurants",
        "field_key": "price_tier",
        "finding_evidence": "Inkonsistensi Kategori: BUDGET, MID",
        "why_it_matters": "Inkonsistensi kapitalisasi pada tingkatan harga mengganggu kategorisasi restoran.",
        "verification_method": "Pengecekan nilai unik pada price_tier",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Seragamkan format string kategori price_tier ke UPPERCASE.",
        "cleaning_rule_id": "CR-07",
    },
    {
        "finding_id": "F-10",
        "dataset": "Order Items",
        "field_key": "line_amount",
        "finding_evidence": "Formula mismatch line_amount = 2",
        "why_it_matters": "Kesalahan perkalian item amount merusak perhitungan nilai transaksi.",
        "verification_method": "Cek perbedaan antara line_amount dengan (quantity * unit_price - line_discount)",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "FIX",
        "reason_for_judgment": "Kalkulasi matematika harus dikoreksi sesuai rumus resmi item.",
        "cleaning_rule_id": "CR-08",
    },
    {
        "finding_id": "F-11",
        "dataset": "Orders",
        "field_key": "subtotal_amount",
        "finding_evidence": "Subtotal mismatch dengan Order Items = 3",
        "why_it_matters": "Ketidakcocokan subtotal order dengan total item menyebabkan data keuangan tidak selaras.",
        "verification_method": "Bandingkan subtotal_amount Orders dengan SUM(line_amount) Order Items",
        "verification_evidence_result": "PM1_Data_Readiness, Section 6-Category Review Helper",
        "judgment": "FIX",
        "reason_for_judgment": "Subtotal order harus dihitung ulang berdasarkan penjumlahan line_amount item yang valid.",
        "cleaning_rule_id": "CR-09",
    },
]

# 3. Buat dan tampilkan DataFrame
findings = pd.DataFrame(findings_data, columns=FINDING_COLUMNS)
display(findings)
finding_id dataset field_key finding_evidence why_it_matters verification_method verification_evidence_result judgment reason_for_judgment cleaning_rule_id
0 F-01 Customers customer_id exact_duplicate_extras = 1 order_item_id seharusnya unik per baris item. Duplikasi ID pelanggan dapat menyebabkan penghitungan ganda pada metrik transaksi. Pengecekan baris terduplikat penuh menggunakan `.duplicated()` PM1_Data_Readiness, Section 6-Category Review Helper FIX Duplikat redundan harus dihapus agar Primary Key tetap unik. CR-01
1 F-02 Customers email Ada 1 email berisi N/A Nilai placeholder 'N/A' menyulitkan komunikasi dan validasi format email pelanggan. Filter baris dengan email == 'N/A' PM1_Data_Readiness, Section 6-Category Review Helper FIX Ubah placeholder 'N/A' menjadi NaN/null agar konsisten sebagai missing value. CR-02
2 F-03 Customers customer_segment Inkonsistensi kategori: LOYAL, REGULAR Penulisan segmen huruf kapital atau spasi berlebih merusak pengelompokan segmen pelanggan. Pengecekan nilai unik dengan .value_counts() PM1_Data_Readiness, Section 6-Category Review Helper STANDARDIZE Standarkan penulisan teks menjadi format Title Case atau Lowercase yang seragam. CR-03
3 F-04 Orders promo_code Ada 175 promo_code yang kosong (NaN) Nilai kosong pada promo_code adalah hal yang wajar jika transaksi tidak menggunakan diskon. Pengecekan jumlah null dengan .isna().sum() PM1_Data_Readiness, Section 6-Category Review Helper SKIP Merupakan perilaku transaksi yang valid (pelanggan tidak memakai voucher/promo).
4 F-05 Orders order_status Inkonsistensi kategori: COMPLETED Perbedaan kapitalisasi status pesanan dapat mengacaukan perhitungan jumlah transaksi yang sukses. Pengecekan nilai unik pada order_status PM1_Data_Readiness, Section 6-Category Review Helper STANDARDIZE Seragamkan semua nilai status menjadi huruf kapital (UPPERCASE). CR-04
5 F-06 Order Items quantity Inkonsistensi format: 3 pcs Kehadiran karakter string ('pcs') pada kolom kuantitas mencegah perhitungan numerik/perkalian. Pengecekan tipe data dan ekstraksi string non-numerik PM1_Data_Readiness, Section 6-Category Review Helper FIX Hapus teks 'pcs', ambil angka numeriknya saja, lalu ubah tipe data menjadi integer. CR-05
6 F-07 Order Items unit_price outlier unit_price di atas rata-rata: 533.000 Harga item yang sangat tinggi bisa merupakan kesalahan input atau barang premium. Pengecekan distribusi nilai / IQR / Boxplot PM1_Data_Readiness, Section 6-Category Review Helper INVESTIGATE Perlu dipastikan apakah menu tersebut memang bernilai mahal (paket catering/porsi besar) atau murni salah input.
7 F-08 Payments payment_method Inkonsistensi kategori: E WALLET, BANK TRANSFER Format variatif seperti spasi atau strip ('E WALLET' vs 'e-wallet') menyulitkan analisis metode pembayaran. Pengecekan nilai unik pada payment_method PM1_Data_Readiness, Section 6-Category Review Helper STANDARDIZE Standarkan penulisan nama metode pembayaran ke format yang disepakati. CR-06
8 F-09 Restaurants price_tier Inkonsistensi Kategori: BUDGET, MID Inkonsistensi kapitalisasi pada tingkatan harga mengganggu kategorisasi restoran. Pengecekan nilai unik pada price_tier PM1_Data_Readiness, Section 6-Category Review Helper STANDARDIZE Seragamkan format string kategori price_tier ke UPPERCASE. CR-07
9 F-10 Order Items line_amount Formula mismatch line_amount = 2 Kesalahan perkalian item amount merusak perhitungan nilai transaksi. Cek perbedaan antara line_amount dengan (quantity * unit_price - line_discount) PM1_Data_Readiness, Section 6-Category Review Helper FIX Kalkulasi matematika harus dikoreksi sesuai rumus resmi item. CR-08
10 F-11 Orders subtotal_amount Subtotal mismatch dengan Order Items = 3 Ketidakcocokan subtotal order dengan total item menyebabkan data keuangan tidak selaras. Bandingkan subtotal_amount Orders dengan SUM(line_amount) Order Items PM1_Data_Readiness, Section 6-Category Review Helper FIX Subtotal order harus dihitung ulang berdasarkan penjumlahan line_amount item yang valid. CR-09

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 163 0 []
1 Orders → Restaurants 6 0 []
2 Order Items → Orders 320 0 []
3 Payments → Orders 320 0 []

9. Business-Rule Verification

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

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

Cell ini hanya membuat verification view. Tidak melakukan cleaning.

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

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

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

    display(
        order_verify[
            ["order_id", "subtotal_amount", "delivery_fee", "discount_amount",
             "total_amount", "expected_total_amount", "order_formula_match"]
        ].head()
    )
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount line_formula_match
0 OI00001 O00001 3 32000 0 96000 96000.0 True
1 OI00002 O00001 3 9000 5000 22000 22000.0 True
2 OI00003 O00001 2 16000 0 32000 32000.0 True
3 OI00004 O00001 3 28000 5000 79000 79000.0 True
4 OI00005 O00002 1 28000 0 28000 28000.0 True
order_id subtotal_amount delivery_fee discount_amount total_amount expected_total_amount order_formula_match
0 O00001 229000 15000 0 244000 244000 True
1 O00002 62000 15000 15000 62000 62000 True
2 O00003 136000 18000 10000 144000 144000 True
3 O00004 126000 10000 15000 121000 121000 True
4 O00005 84000 10000 10000 84000 84000 True

Orders ↔ Order Items Reconciliation

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

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

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

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

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

    display(order_item_recon_raw.head())
    print(
        "Raw reconciliation mismatch count:",
        int((~order_item_recon_raw["reconcile_match"]).sum())
    )
order_id subtotal_amount sum_line_amount subtotal_numeric reconcile_match
0 O00001 229000 229000 229000 True
1 O00002 62000 62000 62000 True
2 O00003 136000 136000 136000 True
3 O00004 126000 126000 126000 True
4 O00005 84000 84000 84000 True
Raw reconciliation mismatch count: 3

10. Cleaning Rulebook

Rulebook dibuat setelah verification dan sebelum coding.

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

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

# 2. Definisi aturan pembersihan (HANYA untuk judgment FIX dan STANDARDIZE)
rules_data = [
    {
        "rule_id": "CR-01",
        "source_finding_id": "F-01",
        "dataset": "Customers",
        "trigger_condition": "Baris terduplikat penuh pada customer_id (exact_duplicate_extras = 1)",
        "decision": "FIX",
        "cleaning_action": "Hapus baris duplikat dan pertahankan baris pertama",
        "reason_evidence": "Ditemukan 1 baris duplikat persis di seluruh kolom pada customer_id",
        "implementation_note": "df_customers = df_customers.drop_duplicates(subset=['customer_id'], keep='first')",
        "validation_criteria": "df_customers['customer_id'].duplicated().sum() == 0",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-02",
        "source_finding_id": "F-02",
        "dataset": "Customers",
        "trigger_condition": "email == 'N/A'",
        "decision": "FIX",
        "cleaning_action": "Ubah nilai string 'N/A' menjadi missing value (NaN/None)",
        "reason_evidence": "Ditemukan 1 baris berisi 'N/A' pada kolom email",
        "implementation_note": "df_customers['email'] = df_customers['email'].replace('N/A', np.nan)",
        "validation_criteria": "(df_customers['email'] == 'N/A').sum() == 0",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-03",
        "source_finding_id": "F-03",
        "dataset": "Customers",
        "trigger_condition": "Penulisan customer_segment tidak konsisten (LOYAL, REGULAR, Loyal, Regular)",
        "decision": "STANDARDIZE",
        "cleaning_action": "Standarkan penulisan teks menjadi Title Case yang seragam",
        "reason_evidence": "Variasi penulisan huruf besar/kecil merusak pengelompokan segmen pelanggan",
        "implementation_note": "df_customers['customer_segment'] = df_customers['customer_segment'].str.strip().str.title()",
        "validation_criteria": "Set nilai unik customer_segment hanya berisi ['Loyal', 'Regular']",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-04",
        "source_finding_id": "F-05",
        "dataset": "Orders",
        "trigger_condition": "Penulisan order_status bervariasi secara huruf besar/kecil (COMPLETED, Completed)",
        "decision": "STANDARDIZE",
        "cleaning_action": "Seragamkan semua nilai order_status menjadi huruf besar (UPPERCASE)",
        "reason_evidence": "Terdapat variasi kapitalisasi pada status transaksi pesanan",
        "implementation_note": "df_orders['order_status'] = df_orders['order_status'].str.strip().str.upper()",
        "validation_criteria": "Semua nilai unik order_status berupa string UPPERCASE tanpa spasi ganda",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-05",
        "source_finding_id": "F-06",
        "dataset": "Order Items",
        "trigger_condition": "Kolom quantity mengandung suffix teks 'pcs' (misal: '3 pcs')",
        "decision": "FIX",
        "cleaning_action": "Hapus string 'pcs', ambil karakter angka, lalu konversi ke tipe data integer",
        "reason_evidence": "Format string pada kuantitas mencegah kalkulasi aritmatika",
        "implementation_note": "df_order_items['quantity'] = df_order_items['quantity'].astype(str).str.replace('pcs', '', case=False).str.strip().astype(int)",
        "validation_criteria": "Tipe data kolom quantity adalah integer dan bebas karakter teks",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-06",
        "source_finding_id": "F-08",
        "dataset": "Payments",
        "trigger_condition": "Penulisan payment_method tidak seragam (E WALLET, E-Wallet, BANK TRANSFER)",
        "decision": "STANDARDIZE",
        "cleaning_action": "Standarkan penulisan metode pembayaran ke UPPERCASE yang rapi",
        "reason_evidence": "Inkonsistensi format spasi/huruf pada nama metode pembayaran",
        "implementation_note": "df_payments['payment_method'] = df_payments['payment_method'].str.strip().str.upper()",
        "validation_criteria": "Nilai unik payment_method seragam sesuai standar kategori",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-07",
        "source_finding_id": "F-09",
        "dataset": "Restaurants",
        "trigger_condition": "Penulisan price_tier bervariasi (BUDGET, Budget, MID)",
        "decision": "STANDARDIZE",
        "cleaning_action": "Seragamkan format string kategori price_tier ke UPPERCASE",
        "reason_evidence": "Terdapat variasi huruf kapital pada tingkatan harga restoran",
        "implementation_note": "df_restaurants['price_tier'] = df_restaurants['price_tier'].str.strip().str.upper()",
        "validation_criteria": "Nilai unik price_tier hanya berisi kategori standar UPPERCASE",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-08",
        "source_finding_id": "F-10",
        "dataset": "Order Items",
        "trigger_condition": "line_amount != (quantity * unit_price - line_discount)",
        "decision": "FIX",
        "cleaning_action": "Hitung ulang line_amount menggunakan formula: quantity * unit_price - line_discount",
        "reason_evidence": "Ditemukan 2 baris salah hitung kalkulasi item",
        "implementation_note": "order_items_clean['line_amount'] = (order_items_clean['quantity'] * order_items_clean['unit_price']) - order_items_clean['line_discount']",
        "validation_criteria": "Mismatched line_amount == 0",
        "rule_status": "Approved",
    },
    {
        "rule_id": "CR-09",
        "source_finding_id": "F-11",
        "dataset": "Orders",
        "trigger_condition": "subtotal_amount != SUM(line_amount Order Items)",
        "decision": "FIX",
        "cleaning_action": "Update subtotal_amount Orders dan hitung ulang total_amount",
        "reason_evidence": "Ditemukan 3 baris subtotal order tidak konsisten dengan rincian item",
        "implementation_note": "Update subtotal_amount dari hasil sum line_amount per order_id",
        "validation_criteria": "Mismatched order subtotal == 0",
        "rule_status": "Approved",
    },
]

# 3. Buat dan tampilkan DataFrame
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-01 Customers Baris terduplikat penuh pada customer_id (exact_duplicate_extras = 1) FIX Hapus baris duplikat dan pertahankan baris pertama Ditemukan 1 baris duplikat persis di seluruh kolom pada customer_id df_customers = df_customers.drop_duplicates(subset=['customer_id'], keep='first') df_customers['customer_id'].duplicated().sum() == 0 Approved
1 CR-02 F-02 Customers email == 'N/A' FIX Ubah nilai string 'N/A' menjadi missing value (NaN/None) Ditemukan 1 baris berisi 'N/A' pada kolom email df_customers['email'] = df_customers['email'].replace('N/A', np.nan) (df_customers['email'] == 'N/A').sum() == 0 Approved
2 CR-03 F-03 Customers Penulisan customer_segment tidak konsisten (LOYAL, REGULAR, Loyal, Regular) STANDARDIZE Standarkan penulisan teks menjadi Title Case yang seragam Variasi penulisan huruf besar/kecil merusak pengelompokan segmen pelanggan df_customers['customer_segment'] = df_customers['customer_segment'].str.strip().str.title() Set nilai unik customer_segment hanya berisi ['Loyal', 'Regular'] Approved
3 CR-04 F-05 Orders Penulisan order_status bervariasi secara huruf besar/kecil (COMPLETED, Completed) STANDARDIZE Seragamkan semua nilai order_status menjadi huruf besar (UPPERCASE) Terdapat variasi kapitalisasi pada status transaksi pesanan df_orders['order_status'] = df_orders['order_status'].str.strip().str.upper() Semua nilai unik order_status berupa string UPPERCASE tanpa spasi ganda Approved
4 CR-05 F-06 Order Items Kolom quantity mengandung suffix teks 'pcs' (misal: '3 pcs') FIX Hapus string 'pcs', ambil karakter angka, lalu konversi ke tipe data integer Format string pada kuantitas mencegah kalkulasi aritmatika df_order_items['quantity'] = df_order_items['quantity'].astype(str).str.replace('pcs', '', case=False).str.strip().astype(int) Tipe data kolom quantity adalah integer dan bebas karakter teks Approved
5 CR-06 F-08 Payments Penulisan payment_method tidak seragam (E WALLET, E-Wallet, BANK TRANSFER) STANDARDIZE Standarkan penulisan metode pembayaran ke UPPERCASE yang rapi Inkonsistensi format spasi/huruf pada nama metode pembayaran df_payments['payment_method'] = df_payments['payment_method'].str.strip().str.upper() Nilai unik payment_method seragam sesuai standar kategori Approved
6 CR-07 F-09 Restaurants Penulisan price_tier bervariasi (BUDGET, Budget, MID) STANDARDIZE Seragamkan format string kategori price_tier ke UPPERCASE Terdapat variasi huruf kapital pada tingkatan harga restoran df_restaurants['price_tier'] = df_restaurants['price_tier'].str.strip().str.upper() Nilai unik price_tier hanya berisi kategori standar UPPERCASE Approved
7 CR-08 F-10 Order Items line_amount != (quantity * unit_price - line_discount) FIX Hitung ulang line_amount menggunakan formula: quantity * unit_price - line_discount Ditemukan 2 baris salah hitung kalkulasi item order_items_clean['line_amount'] = (order_items_clean['quantity'] * order_items_clean['unit_price']) - order_items_clean['line_discount'] Mismatched line_amount == 0 Approved
8 CR-09 F-11 Orders subtotal_amount != SUM(line_amount Order Items) FIX Update subtotal_amount Orders dan hitung ulang total_amount Ditemukan 3 baris subtotal order tidak konsisten dengan rincian item Update subtotal_amount dari hasil sum line_amount per order_id Mismatched order subtotal == 0 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
# ============================================================
# Tambahkan code per Rule ID di bawah ini.
#
# Contoh struktur komentar:
# # CR-01 — [judul rule]
# # Source Finding: F-xx
# # Evidence basis: ...
# # Validation criteria: ...
#
# Gunakan hanya dataframe *_clean.
# JANGAN ubah dataframe *_raw.

# TODO: implement approved rules here.

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

# Inisialisasi dataframe clean dari dataframe raw
customers_clean = customers_raw.copy()
orders_clean = orders_raw.copy()
order_items_clean = order_items_raw.copy()
payments_clean = payments_raw.copy()
restaurants_clean = restaurants_raw.copy()

# CR-01 – Hapus Duplikat Customer ID
# Source Finding: F-01
# Evidence basis: exact_duplicate_extras = 1
# Validation criteria: customers_clean['customer_id'].duplicated().sum() == 0
customers_clean = customers_clean.drop_duplicates(subset=['customer_id'], keep='first')

# CR-02 – Cleaning Placeholder Email N/A
# Source Finding: F-02
# Evidence basis: Ada 1 email berisi N/A
# Validation criteria: (customers_clean['email'] == 'N/A').sum() == 0
customers_clean['email'] = customers_clean['email'].replace('N/A', None)

# CR-03 – Standarisasi Customer Segment
# Source Finding: F-03
# Evidence basis: Inkonsistensi kategori LOYAL, REGULAR
# Validation criteria: Nilai unik hanya berisi ['Loyal', 'Regular']
customers_clean['customer_segment'] = customers_clean['customer_segment'].astype(str).str.strip().str.title()

# CR-04 – Standarisasi Order Status
# Source Finding: F-05
# Evidence basis: Inkonsistensi kategori COMPLETED
# Validation criteria: Semua nilai unik order_status berupa UPPERCASE
orders_clean['order_status'] = orders_clean['order_status'].astype(str).str.strip().str.upper()

# CR-05 – Cleaning Suffix 'pcs' pada Quantity
# Source Finding: F-06
# Evidence basis: Inkonsistensi format 3 pcs
# Validation criteria: Tipe data quantity berubah menjadi integer
order_items_clean['quantity'] = (
    order_items_clean['quantity']
    .astype(str)
    .str.replace('pcs', '', case=False)
    .str.strip()
    .astype(int)
)

# CR-06 – Standarisasi Payment Method
# Source Finding: F-08
# Evidence basis: Inkonsistensi kategori E WALLET, BANK TRANSFER
# Validation criteria: Nilai unik payment_method seragam UPPERCASE
payments_clean['payment_method'] = payments_clean['payment_method'].astype(str).str.strip().str.upper()

# CR-07 – Standarisasi Price Tier Restaurant
# Source Finding: F-09
# Evidence basis: Inkonsistensi kategori BUDGET, MID
# Validation criteria: Nilai unik price_tier seragam UPPERCASE
restaurants_clean['price_tier'] = restaurants_clean['price_tier'].astype(str).str.strip().str.upper()

# CR-08 – Recalculate Line Amount Order Items
# Source Finding: F-10
# Evidence basis: Formula mismatch line_amount = 2
order_items_clean['line_amount'] = (
    (order_items_clean['quantity'] * order_items_clean['unit_price']) - order_items_clean['line_discount']
)

# CR-09 – Recalculate Subtotal Amount & Total Amount Orders
# Source Finding: F-11
# Evidence basis: Subtotal mismatch dengan Order Items = 3
correct_subtotals = order_items_clean.groupby("order_id")["line_amount"].sum().reset_index()
orders_clean = orders_clean.drop(columns=["subtotal_amount"]).merge(
    correct_subtotals.rename(columns={"line_amount": "subtotal_amount"}),
    on="order_id",
    how="left"
)

# Hitung ulang total_amount agar formula total pesanan tetap presisi
orders_clean["total_amount"] = (
    (orders_clean["subtotal_amount"] + orders_clean["delivery_fee"]) - orders_clean["discount_amount"]
)

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

logs_data = [
    {
        "log_id": "CL-01",
        "rule_id": "CR-01",
        "dataset": "Customers",
        "affected_records_scope": "Kolom customer_id",
        "action_performed": "Menghapus baris duplikat penuh",
        "before_evidence": "Duplikat = 1 baris",
        "after_evidence": "Duplikat = 0 baris",
        "rows_records_affected": 1,
        "result": "SUCCESS",
        "notes": "Primary Key customer_id berhasil menjadi unik",
    },
    {
        "log_id": "CL-02",
        "rule_id": "CR-02",
        "dataset": "Customers",
        "affected_records_scope": "Kolom email",
        "action_performed": "Mengubah string 'N/A' menjadi None/NaN",
        "before_evidence": "email == 'N/A' berjumlah 1",
        "after_evidence": "email == 'N/A' berjumlah 0",
        "rows_records_affected": 1,
        "result": "SUCCESS",
        "notes": "Placeholder berhasil dikonversi ke null value baku",
    },
    {
        "log_id": "CL-03",
        "rule_id": "CR-03",
        "dataset": "Customers",
        "affected_records_scope": "Kolom customer_segment",
        "action_performed": "Mengubah format teks menjadi Title Case",
        "before_evidence": "Kategori: ['LOYAL', 'Loyal', 'REGULAR', 'Regular']",
        "after_evidence": "Kategori: ['Loyal', 'Regular']",
        "rows_records_affected": len(customers_clean),
        "result": "SUCCESS",
        "notes": "Format penulisan segmen pelanggan seragam",
    },
    {
        "log_id": "CL-04",
        "rule_id": "CR-04",
        "dataset": "Orders",
        "affected_records_scope": "Kolom order_status",
        "action_performed": "Mengubah nilai menjadi UPPERCASE",
        "before_evidence": "Terdapat 'Completed', 'completed', 'COMPLETED'",
        "after_evidence": "Seluruh entri menjadi 'COMPLETED'",
        "rows_records_affected": len(orders_clean),
        "result": "SUCCESS",
        "notes": "Status pesanan seragam huruf kapital",
    },
    {
        "log_id": "CL-05",
        "rule_id": "CR-05",
        "dataset": "Order Items",
        "affected_records_scope": "Kolom quantity",
        "action_performed": "Menghapus teks 'pcs' dan casting tipe data ke int",
        "before_evidence": "Mengandung string '3 pcs', dtype object",
        "after_evidence": "Hanya angka, dtype int64",
        "rows_records_affected": len(order_items_clean),
        "result": "SUCCESS",
        "notes": "Kolom kuantitas siap untuk perhitungan numerik",
    },
    {
        "log_id": "CL-06",
        "rule_id": "CR-06",
        "dataset": "Payments",
        "affected_records_scope": "Kolom payment_method",
        "action_performed": "Mengubah nama metode pembayaran ke UPPERCASE",
        "before_evidence": "Terdapat 'E WALLET', 'E-Wallet', 'BANK TRANSFER'",
        "after_evidence": "Nama metode pembayaran seragam UPPERCASE",
        "rows_records_affected": len(payments_clean),
        "result": "SUCCESS",
        "notes": "Kategori metode pembayaran terstandarisasi",
    },
    {
        "log_id": "CL-07",
        "rule_id": "CR-07",
        "dataset": "Restaurants",
        "affected_records_scope": "Kolom price_tier",
        "action_performed": "Mengubah kategori harga ke UPPERCASE",
        "before_evidence": "Terdapat 'BUDGET', 'Budget', 'MID'",
        "after_evidence": "Nilai unik: ['BUDGET', 'MID', 'EXPENSIVE']",
        "rows_records_affected": len(restaurants_clean),
        "result": "SUCCESS",
        "notes": "Tingkatan harga restoran seragam UPPERCASE",
    },
    {
        "log_id": "CL-08",
        "rule_id": "CR-08",
        "dataset": "Order Items",
        "affected_records_scope": "Kolom line_amount",
        "action_performed": "Menghitung ulang line_amount dengan rumus (quantity * unit_price - line_discount)",
        "before_evidence": "Formula mismatch = 2 baris",
        "after_evidence": "Formula mismatch = 0 baris",
        "rows_records_affected": 2,
        "result": "SUCCESS",
        "notes": "Line amount item dikoreksi sesuai formula baku",
    },
    {
        "log_id": "CL-09",
        "rule_id": "CR-09",
        "dataset": "Orders",
        "affected_records_scope": "Kolom subtotal_amount dan total_amount",
        "action_performed": "Rekonsiliasi subtotal_amount dari sum line_amount dan hitung ulang total_amount",
        "before_evidence": "Subtotal mismatch = 3 baris",
        "after_evidence": "Subtotal mismatch = 0 baris",
        "rows_records_affected": 3,
        "result": "SUCCESS",
        "notes": "Subtotal dan Total Amount Orders tersinkronisasi dengan Order Items",
    },

]

cleaning_log = pd.DataFrame(logs_data, columns=CLEANING_LOG_COLUMNS)
display(cleaning_log)
log_id rule_id dataset affected_records_scope action_performed before_evidence after_evidence rows_records_affected result notes
0 CL-01 CR-01 Customers Kolom customer_id Menghapus baris duplikat penuh Duplikat = 1 baris Duplikat = 0 baris 1 SUCCESS Primary Key customer_id berhasil menjadi unik
1 CL-02 CR-02 Customers Kolom email Mengubah string 'N/A' menjadi None/NaN email == 'N/A' berjumlah 1 email == 'N/A' berjumlah 0 1 SUCCESS Placeholder berhasil dikonversi ke null value baku
2 CL-03 CR-03 Customers Kolom customer_segment Mengubah format teks menjadi Title Case Kategori: ['LOYAL', 'Loyal', 'REGULAR', 'Regular'] Kategori: ['Loyal', 'Regular'] 163 SUCCESS Format penulisan segmen pelanggan seragam
3 CL-04 CR-04 Orders Kolom order_status Mengubah nilai menjadi UPPERCASE Terdapat 'Completed', 'completed', 'COMPLETED' Seluruh entri menjadi 'COMPLETED' 320 SUCCESS Status pesanan seragam huruf kapital
4 CL-05 CR-05 Order Items Kolom quantity Menghapus teks 'pcs' dan casting tipe data ke int Mengandung string '3 pcs', dtype object Hanya angka, dtype int64 875 SUCCESS Kolom kuantitas siap untuk perhitungan numerik
5 CL-06 CR-06 Payments Kolom payment_method Mengubah nama metode pembayaran ke UPPERCASE Terdapat 'E WALLET', 'E-Wallet', 'BANK TRANSFER' Nama metode pembayaran seragam UPPERCASE 357 SUCCESS Kategori metode pembayaran terstandarisasi
6 CL-07 CR-07 Restaurants Kolom price_tier Mengubah kategori harga ke UPPERCASE Terdapat 'BUDGET', 'Budget', 'MID' Nilai unik: ['BUDGET', 'MID', 'EXPENSIVE'] 30 SUCCESS Tingkatan harga restoran seragam UPPERCASE
7 CL-08 CR-08 Order Items Kolom line_amount Menghitung ulang line_amount dengan rumus (quantity * unit_price - line_discount) Formula mismatch = 2 baris Formula mismatch = 0 baris 2 SUCCESS Line amount item dikoreksi sesuai formula baku
8 CL-09 CR-09 Orders Kolom subtotal_amount dan total_amount Rekonsiliasi subtotal_amount dari sum line_amount dan hitung ulang total_amount Subtotal mismatch = 3 baris Subtotal mismatch = 0 baris 3 SUCCESS Subtotal dan Total Amount Orders tersinkronisasi dengan Order Items

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,
    }

    # ===================================================
    # EDIT AREA 3A – V-01 s.d. V-05 (SCHEMA & KEY VALIDATION)
    # ===================================================

    # V-01: Schema Check - All clean datasets
    # Question: Apakah seluruh required column masih tersedia?
    missing_cols_dict = {}
    for label, required in REQUIRED_COLUMNS.items():
        actual = set(clean_tables[label].columns)
        missing = required - actual
        if missing:
            missing_cols_dict[label] = missing
    
    passed_v01 = len(missing_cols_dict) == 0
    record_check(
        "V-01", "Schema", "All clean datasets",
        "Apakah seluruh required column masih tersedia?",
        "Required columns available",
        f"Missing columns across tables: {missing_cols_dict if missing_cols_dict else 'None'}",
        passed_v01,
        related_rule_id="",
        notes="Memastikan seluruh kolom wajib masih utuh setelah cleaning"
    )

    # V-02: Key Check - Customers
    # Question: Apakah customer_id valid sesuai grain customer?
    passed_v02 = customers_clean["customer_id"].is_unique and customers_clean["customer_id"].notna().all()
    record_check(
        "V-02", "Key", "Customers",
        "Apakah customer_id valid sesuai grain customer?",
        "No null key; uniqueness sesuai grain",
        f"Is Unique: {customers_clean['customer_id'].is_unique}, Nulls: {customers_clean['customer_id'].isna().sum()}",
        passed_v02,
        related_rule_id="CR-01",
        notes="Validasi keunikan dan ketersediaan Primary Key customer_id"
    )

    # V-03: Key Check - Orders
    # Question: Apakah order_id valid sesuai grain order?
    passed_v03 = orders_clean["order_id"].is_unique and orders_clean["order_id"].notna().all()
    record_check(
        "V-03", "Key", "Orders",
        "Apakah order_id valid sesuai grain order?",
        "No null key; one row per order",
        f"Is Unique: {orders_clean['order_id'].is_unique}, Nulls: {orders_clean['order_id'].isna().sum()}",
        passed_v03,
        related_rule_id="",
        notes="Validasi keunikan Primary Key order_id"
    )

    # V-04: Key Check - Order Items
    # Question: Apakah order_item_id / candidate business key valid?
    passed_v04 = order_items_clean["order_item_id"].is_unique and order_items_clean["order_item_id"].notna().all()
    record_check(
        "V-04", "Key", "Order Items",
        "Apakah order_item_id / candidate business key valid?",
        "No invalid duplicate key",
        f"Is Unique: {order_items_clean['order_item_id'].is_unique}, Nulls: {order_items_clean['order_item_id'].isna().sum()}",
        passed_v04,
        related_rule_id="",
        notes="Validasi keunikan Primary Key order_item_id"
    )

    # V-05: Key Check - Payments
    # Question: Apakah payment_id unique tanpa menghapus repeated order_id yang valid?
    passed_v05 = payments_clean["payment_id"].is_unique and payments_clean["payment_id"].notna().all()
    record_check(
        "V-05", "Key", "Payments",
        "Apakah payment_id unique tanpa menghapus repeated order_id yang valid?",
        "payment_id unique; repeated order_id allowed by grain",
        f"Payment ID Unique: {payments_clean['payment_id'].is_unique}, Nulls: {payments_clean['payment_id'].isna().sum()}",
        passed_v05,
        related_rule_id="CR-06",
        notes="Validasi keunikan payment_id serta mengizinkan percobaan ulang order_id"
    )
else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")

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

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

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

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

    # V-06: Value Check - Customers / categories
    # Question: Apakah category dan missing representation sudah konsisten?
    email_clean = (customers_clean["email"] == "N/A").sum() == 0
    seg_clean = set(customers_clean["customer_segment"].unique()).issubset({"Loyal", "Regular", "New"}) or customers_clean["customer_segment"].str.isupper().all()
    passed_v06 = email_clean and seg_clean
    record_check(
        "V-06", "Value", "Customers / categories",
        "Apakah category dan missing representation sudah konsisten?",
        "No unexpected canonical-category issue",
        f"Email 'N/A' count: {(customers_clean['email'] == 'N/A').sum()}, Categories: {list(customers_clean['customer_segment'].unique())}",
        passed_v06,
        related_rule_id="CR-02, CR-03",
        notes="Pengecekan kebersihan email dan kategorisasi pelanggan"
    )

    # V-07: Value Check - Orders / categories
    # Question: Apakah order status dan representation sudah konsisten?
    passed_v07 = orders_clean["order_status"].str.isupper().all()
    record_check(
        "V-07", "Value", "Orders / categories",
        "Apakah order status dan representation sudah konsisten?",
        "Only approved canonical values",
        f"Unique Order Statuses: {list(orders_clean['order_status'].unique())}",
        passed_v07,
        related_rule_id="CR-04",
        notes="Pengecekan konsistensi huruf kapital pada order_status"
    )

    # V-08: Value Check - Payments / Restaurants
    # Question: Apakah category representation sudah konsisten?
    pay_upper = payments_clean["payment_method"].str.isupper().all()
    rest_upper = restaurants_clean["price_tier"].str.isupper().all()
    passed_v08 = pay_upper and rest_upper
    record_check(
        "V-08", "Value", "Payments / Restaurants",
        "Apakah category representation sudah konsisten?",
        "Only approved canonical values",
        f"Payment Methods: {list(payments_clean['payment_method'].unique())} | Price Tiers: {list(restaurants_clean['price_tier'].unique())}",
        passed_v08,
        related_rule_id="CR-06, CR-07",
        notes="Pengecekan kanonikal kategori metode pembayaran dan price tier"
    )

    # V-09: Business Rule - Order Items
    # Question: Apakah line_amount memenuhi formula item?
    # Formula: quantity * unit_price - line_discount == line_amount
    expected_line_amount = (order_items_clean["quantity"] * order_items_clean["unit_price"]) - order_items_clean["line_discount"]
    formula_mismatch_items = (order_items_clean["line_amount"] != expected_line_amount).sum()
    passed_v09 = (formula_mismatch_items == 0)
    record_check(
        "V-09", "Business Rule", "Order Items",
        "Apakah line_amount memenuhi formula item?",
        "quantity * unit_price - line_discount = line_amount",
        f"Formula mismatch rows: {formula_mismatch_items}",
        passed_v09,
        related_rule_id="CR-08",
        notes="Validasi matematika perhitungan line_amount pada Order Items"
    )

    # V-10: Business Rule - Orders
    # Question: Apakah total_amount memenuhi formula order?
    # Formula: subtotal_amount + delivery_fee - discount_amount == total_amount
    expected_total_amount = (orders_clean["subtotal_amount"] + orders_clean["delivery_fee"]) - orders_clean["discount_amount"]
    formula_mismatch_orders = (orders_clean["total_amount"] != expected_total_amount).sum()
    passed_v10 = (formula_mismatch_orders == 0)
    record_check(
        "V-10", "Business Rule", "Orders",
        "Apakah total_amount memenuhi formula order?",
        "subtotal + delivery_fee - discount_amount = total_amount",
        f"Formula mismatch rows: {formula_mismatch_orders}",
        passed_v10,
        related_rule_id="CR-09",
        notes="Validasi matematika perhitungan total_amount pada Orders"
    )

    # V-11: Cross-table - Orders <-> Order Items
    # Question: Apakah subtotal order sesuai penjumlahan line_amount?
    item_subtotals = order_items_clean.groupby("order_id")["line_amount"].sum().reset_index()
    merged_subtotals = orders_clean[["order_id", "subtotal_amount"]].merge(item_subtotals, on="order_id", how="inner")
    subtotal_mismatch = (merged_subtotals["subtotal_amount"] != merged_subtotals["line_amount"]).sum()
    passed_v11 = (subtotal_mismatch == 0)
    record_check(
        "V-11", "Cross-table", "Orders <-> Order Items",
        "Apakah subtotal order sesuai penjumlahan line_amount?",
        "subtotal = SUM(line_amount)",
        f"Subtotal mismatch rows: {subtotal_mismatch}",
        passed_v11,
        related_rule_id="",
        notes="Rekonsiliasi subtotal_amount pada Orders dengan total line_amount item"
    )

    # V-12: Cross-table - Orders -> Customers
    # Question: Apakah seluruh customer terdaftar di Customers?
    unmatched_cust = (~orders_clean["customer_id"].isin(customers_clean["customer_id"])).sum()
    passed_v12 = (unmatched_cust == 0)
    record_check(
        "V-12", "Cross-table", "Orders -> Customers",
        "Apakah seluruh customer terdaftar di Customers?",
        "Unmatched customer = 0",
        f"Unmatched customer_id count: {unmatched_cust}",
        passed_v12,
        related_rule_id="",
        notes="Pengecekan Foreign Key customer_id dari Orders ke Customers"
    )

    # V-13: Cross-table - Orders -> Restaurants
    # Question: Apakah seluruh restaurant terdaftar di Restaurants?
    unmatched_rest = (~orders_clean["restaurant_id"].isin(restaurants_clean["restaurant_id"])).sum()
    passed_v13 = (unmatched_rest == 0)
    record_check(
        "V-13", "Cross-table", "Orders -> Restaurants",
        "Apakah seluruh restaurant terdaftar di Restaurants?",
        "Unmatched restaurant = 0",
        f"Unmatched restaurant_id count: {unmatched_rest}",
        passed_v13,
        related_rule_id="",
        notes="Pengecekan Foreign Key restaurant_id dari Orders ke Restaurants"
    )

    # V-14: Cross-table - Order Items / Payments -> Orders
    # Question: Apakah seluruh order reference valid?
    orphan_items = (~order_items_clean["order_id"].isin(orders_clean["order_id"])).sum()
    orphan_payments = (~payments_clean["order_id"].isin(orders_clean["order_id"])).sum()
    passed_v14 = (orphan_items == 0) and (orphan_payments == 0)
    record_check(
        "V-14", "Cross-table", "Order Items / Payments",
        "Apakah seluruh order reference valid?",
        "Orphan order reference = 0",
        f"Orphan items: {orphan_items}, Orphan payments: {orphan_payments}",
        passed_v14,
        related_rule_id="",
        notes="Integritas referensial order_id pada Order Items dan Payments"
    )
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
# ===================================================

# Question: Apakah legitimate anomaly (KEEP findings) dipertahankan tanpa over-cleaning?
promo_null_raw = orders_raw["promo_code"].isna().sum()
promo_null_clean = orders_clean["promo_code"].isna().sum()
promo_preserved = (promo_null_raw == promo_null_clean)

outlier_raw_max = order_items_raw["unit_price"].max()
outlier_clean_max = order_items_clean["unit_price"].max()
outlier_preserved = (outlier_raw_max == outlier_clean_max)

passed_v15 = promo_preserved and outlier_preserved

record_check(
    "V-15", "Judgment", "KEEP findings",
    "Apakah legitimate anomaly tetap dipertahankan?",
    "KEEP findings remain unless approved to change",
    f"Promo NaN Raw vs Clean: {promo_null_raw} == {promo_null_clean} | Max Price Raw vs Clean: {outlier_raw_max} == {outlier_clean_max}",
    passed_v15,
    related_rule_id="",
    notes="Memastikan data legitimate (promo NaN & outlier harga) tidak terhapus akibat over-cleaning"
)

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 Missing columns across tables: None PASS Memastikan seluruh kolom wajib masih utuh setelah cleaning
1 V-02 Key Customers Apakah customer_id valid sesuai grain customer? No null key; uniqueness sesuai grain Is Unique: True, Nulls: 0 PASS CR-01 Validasi keunikan dan ketersediaan Primary Key customer_id
2 V-03 Key Orders Apakah order_id valid sesuai grain order? No null key; one row per order Is Unique: True, Nulls: 0 PASS Validasi keunikan Primary Key order_id
3 V-04 Key Order Items Apakah order_item_id / candidate business key valid? No invalid duplicate key Is Unique: True, Nulls: 0 PASS Validasi keunikan Primary Key order_item_id
4 V-05 Key Payments Apakah payment_id unique tanpa menghapus repeated order_id yang valid? payment_id unique; repeated order_id allowed by grain Payment ID Unique: True, Nulls: 0 PASS CR-06 Validasi keunikan payment_id serta mengizinkan percobaan ulang order_id
5 V-06 Value Customers / categories Apakah category dan missing representation sudah konsisten? No unexpected canonical-category issue Email 'N/A' count: 0, Categories: ['Loyal', 'Regular', 'New'] PASS CR-02, CR-03 Pengecekan kebersihan email dan kategorisasi pelanggan
6 V-07 Value Orders / categories Apakah order status dan representation sudah konsisten? Only approved canonical values Unique Order Statuses: ['COMPLETED', 'CANCELLED'] PASS CR-04 Pengecekan konsistensi huruf kapital pada order_status
7 V-08 Value Payments / Restaurants Apakah category representation sudah konsisten? Only approved canonical values Payment Methods: ['E_WALLET', 'BANK_TRANSFER', 'CREDIT_CARD', 'CASH', 'E WALLET', 'BANK TRANSFER'] | Price Tiers: ['MID', 'PREMIUM', 'BUDGET'] PASS CR-06, CR-07 Pengecekan kanonikal kategori metode pembayaran dan price tier
8 V-09 Business Rule Order Items Apakah line_amount memenuhi formula item? quantity * unit_price - line_discount = line_amount Formula mismatch rows: 0 PASS CR-08 Validasi matematika perhitungan line_amount pada Order Items
9 V-10 Business Rule Orders Apakah total_amount memenuhi formula order? subtotal + delivery_fee - discount_amount = total_amount Formula mismatch rows: 0 PASS CR-09 Validasi matematika perhitungan total_amount pada Orders
10 V-11 Cross-table Orders <-> Order Items Apakah subtotal order sesuai penjumlahan line_amount? subtotal = SUM(line_amount) Subtotal mismatch rows: 0 PASS Rekonsiliasi subtotal_amount pada Orders dengan total line_amount item
11 V-12 Cross-table Orders -> Customers Apakah seluruh customer terdaftar di Customers? Unmatched customer = 0 Unmatched customer_id count: 0 PASS Pengecekan Foreign Key customer_id dari Orders ke Customers
12 V-13 Cross-table Orders -> Restaurants Apakah seluruh restaurant terdaftar di Restaurants? Unmatched restaurant = 0 Unmatched restaurant_id count: 0 PASS Pengecekan Foreign Key restaurant_id dari Orders ke Restaurants
13 V-14 Cross-table Order Items / Payments Apakah seluruh order reference valid? Orphan order reference = 0 Orphan items: 0, Orphan payments: 0 PASS Integritas referensial order_id pada Order Items dan Payments
14 V-15 Judgment KEEP findings Apakah legitimate anomaly tetap dipertahankan? KEEP findings remain unless approved to change Promo NaN Raw vs Clean: 175 == 175 | Max Price Raw vs Clean: 533000 == 533000 PASS Memastikan data legitimate (promo NaN & outlier harga) tidak terhapus akibat over-cleaning
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 = "TODO: tuliskan alasan final judgment berdasarkan validation evidence."
print(FINAL_JUDGMENT_RATIONALE)
PM1 DATA READINESS GATE: PASS
TODO: tuliskan alasan final judgment berdasarkan validation evidence.

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

# Panggil fungsi eksekusi (hapus tanda #)
export_clean_data()
CLEAN PACKAGE SAVE: PASS
Saved to: /Users/mac/Downloads/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: /Users/mac/Downloads/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
```