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

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

IDENTITY_GATE_PASS = bool(name_ok and variant_ok)

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

1. Technical Check — Workspace & Folder

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

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

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

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

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

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

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

WORKSPACE_ROOT = find_workspace_root()

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

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

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

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

Environment Smoke Test — RUN

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

print("pandas :", pd.__version__)

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

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

if not EXCEL_ENGINE_AVAILABLE:
    print("ENVIRONMENT CHECK: NEEDS FIX")
    print("→ File XLSX membutuhkan openpyxl. Aktifkan environment sesuai setup Sesi 1.")
else:
    print("ENVIRONMENT CHECK: PASS")
pandas : 2.2.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 C:\Users\User\Downloads\Bootcamp_Data_Analyst_...
1 orders swiftbite_orders.xlsx True C:\Users\User\Downloads\Bootcamp_Data_Analyst_...
2 order_items swiftbite_order_items.csv True C:\Users\User\Downloads\Bootcamp_Data_Analyst_...
3 payments swiftbite_payments.txt True C:\Users\User\Downloads\Bootcamp_Data_Analyst_...
4 restaurants swiftbite_restaurants.csv True C:\Users\User\Downloads\Bootcamp_Data_Analyst_...
SOURCE ACCESS CHECK: PASS
→ Semua 5 raw files ditemukan. Jangan ubah path.

3. Load Raw Data & Schema Gate — RUN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Quick Source Preview — RUN

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

if DATA_READY:
    for name, df in raw_tables.items():
        print(f"\n=== {name} | shape={df.shape} ===")
        display(df.head(3))
else:
    print("PREVIEW SKIPPED — DATA READY GATE belum PASS.")
=== Customers | shape=(159, 7) ===
customer_id full_name email phone_number signup_date customer_zone customer_segment
0 C0001 Wahyu Permata wahyupermata1@example.com 084510209970 2025-10-30 East Regular
1 C0002 Ayu Nugroho ayunugroho2@example.com 088076065565 2025-11-02 South Regular
2 C0003 Eka Santoso ekasantoso3@example.com 085131488452 2025-08-07 North Regular
=== Orders | shape=(330, 10) ===
order_id customer_id restaurant_id order_timestamp order_status promo_code subtotal_amount delivery_fee discount_amount total_amount
0 O00001 C0058 R0016 2026-01-31 00:19:00 completed NaN 111000 12000 15000 108000
1 O00002 C0065 R0021 2026-03-25 04:52:00 completed NEWUSER 29000 8000 10000 27000
2 O00003 C0072 R0026 2026-01-29 14:16:00 completed HEMAT15 184000 15000 15000 184000
=== Order Items | shape=(872, 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 1 32000 5000 27000
1 OI00002 O00001 2 Nasi Ayam Main 3 28000 0 84000
2 OI00003 O00002 1 Fries Snack 1 18000 0 18000
=== Payments | shape=(374, 7) ===
payment_id order_id payment_method payment_status payment_amount payment_timestamp attempt_number
0 P00001 O00001 e_wallet failed 108000 2026-01-31 00:21:00 1
1 P00002 O00001 e_wallet successful 108000 2026-01-31 00:24:00 2
2 P00003 O00002 bank_transfer successful 27000 2026-03-25 04:55:00 1
=== Restaurants | shape=(28, 7) ===
restaurant_id restaurant_name cuisine_type restaurant_zone price_tier onboarding_date active_status
0 R0001 SwiftBite Partner 01 Asian West Mid 2024-06-15 active
1 R0002 SwiftBite Partner 02 Western East Premium 2025-01-15 active
2 R0003 SwiftBite Partner 03 Fast Food East Budget 2024-10-30 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 159 7 [customer_id, full_name, email, phone_number, ...
1 Orders 330 10 [order_id, customer_id, restaurant_id, order_t...
2 Order Items 872 9 [order_item_id, order_id, line_number, item_na...
3 Payments 374 7 [payment_id, order_id, payment_method, payment...
4 Restaurants 28 7 [restaurant_id, restaurant_name, cuisine_type,...

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 159 158 0 2 False
1 Orders order_id 330 330 0 0 True
2 Order Items order_item_id 872 872 0 0 True
3 Order Items — candidate business key order_id, line_number 872 872 0 0 True
4 Payments payment_id 374 374 0 0 True
5 Restaurants restaurant_id 28 28 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
224 O00225 5
142 O00143 5
117 O00118 5
180 O00181 5
69 O00070 4
61 O00062 4
243 O00244 4
32 O00033 4
90 O00091 4
154 O00155 4
Payments cardinality
order_id payment_rows
0 O00001 2
134 O00135 2
229 O00230 2
228 O00229 2
217 O00218 2
181 O00182 2
169 O00170 2
164 O00165 2
153 O00154 2
130 O00131 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 159 7 2 1 0
1 Orders 330 10 0 0 161
2 Order Items 872 9 0 0 0
3 Payments 374 7 0 0 0
4 Restaurants 28 7 0 0 0

Column-Level Profiling

# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
    display(column_profile(restaurants_raw))
column dtype null_count null_pct unique_count sample_values
0 restaurant_id object 0 0.0 28 [R0001, R0002, R0003, R0004, R0005]
1 restaurant_name object 0 0.0 28 [SwiftBite Partner 01, SwiftBite Partner 02, S...
2 cuisine_type object 0 0.0 6 [Asian, Western, Fast Food, Cafe & Beverage, D...
3 restaurant_zone object 0 0.0 5 [West, East, North, South, Central]
4 price_tier object 0 0.0 5 [Mid, Premium, Budget, PREMIUM , BUDGET ]
5 onboarding_date object 0 0.0 28 [2024-06-15, 2025-01-15, 2024-10-30, 2024-09-2...
6 active_status object 0 0.0 2 [active, inactive]

Category Review Helper

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

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

Numeric Review Helper

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

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

7. Candidate Findings Register

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

Jangan isi judgment sebelum verification evidence cukup.

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

findings = pd.DataFrame(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

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 158 0 []
1 Orders → Restaurants 28 0 []
2 Order Items → Orders 330 0 []
3 Payments → Orders 330 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 1 32000 5000 27000 27000.0 True
1 OI00002 O00001 3 28000 0 84000 84000.0 True
2 OI00003 O00002 1 18000 0 18000 18000.0 True
3 OI00004 O00002 1 16000 5000 11000 11000.0 True
4 OI00005 O00003 1 32000 0 32000 32000.0 True
order_id subtotal_amount delivery_fee discount_amount total_amount expected_total_amount order_formula_match
0 O00001 111000 12000 15000 108000 108000 True
1 O00002 29000 8000 10000 27000 27000 True
2 O00003 184000 15000 15000 184000 184000 True
3 O00004 183000 15000 0 198000 198000 True
4 O00005 134000 18000 10000 142000 142000 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 111000 111000 111000 True
1 O00002 29000 29000 29000 True
2 O00003 184000 184000 184000 True
3 O00004 183000 183000 183000 True
4 O00005 134000 134000 134000 True
Raw reconciliation mismatch count: 3
if DATA_READY:
    mismatch_rows = order_item_recon_raw[
        ~order_item_recon_raw["reconcile_match"]
    ]
    display(mismatch_rows)
order_id subtotal_amount sum_line_amount subtotal_numeric reconcile_match
49 O00050 205000 212000 205000 False
190 O00191 199000 194000 199000 False
200 O00201 161000 151000 161000 False
if DATA_READY:
    duplicate_customers = customers_raw[
        customers_raw.duplicated(
            subset=["customer_id"],
            keep=False
        )
    ].sort_values("customer_id")

    display(duplicate_customers)
customer_id full_name email phone_number signup_date customer_zone customer_segment
30 C0031 Farah Hidayat farahhidayat31@example.com 089605670796 2025-06-11 North Regular
158 C0031 Farah Hidayat farahhidayat31@example.com 089605670796 2025-06-11 North Regular
if DATA_READY:
    item_formula_check = order_items_raw.copy()

    item_formula_check["quantity_numeric"] = pd.to_numeric(
        item_formula_check["quantity"],
        errors="coerce"
    )

    item_formula_check["expected_line_amount"] = (
        item_formula_check["quantity_numeric"]
        * pd.to_numeric(item_formula_check["unit_price"], errors="coerce")
        - pd.to_numeric(item_formula_check["line_discount"], errors="coerce")
    )

    item_formula_check["line_amount_numeric"] = pd.to_numeric(
        item_formula_check["line_amount"],
        errors="coerce"
    )

    item_formula_check["line_formula_match"] = (
        item_formula_check["line_amount_numeric"]
        == item_formula_check["expected_line_amount"]
    )

    mismatch_items_all = item_formula_check[
        item_formula_check["line_formula_match"] == False
    ]

    print("Total baris Order Items:", len(item_formula_check))
    print("Jumlah baris tidak sesuai:", len(mismatch_items_all))
    print(
        "Jumlah baris sesuai:",
        int(item_formula_check["line_formula_match"].sum())
    )

    display(
        mismatch_items_all[
            [
                "order_item_id",
                "order_id",
                "quantity",
                "unit_price",
                "line_discount",
                "line_amount",
                "expected_line_amount",
                "line_formula_match"
            ]
        ]
    )
Total baris Order Items: 872
Jumlah baris tidak sesuai: 4
Jumlah baris sesuai: 868
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount line_formula_match
70 OI00071 O00027 3 pcs 20000 5000 55000 NaN False
130 OI00131 O00050 3 20000 0 67000 60000.0 False
214 OI00215 O00083 1 pcs 22000 0 22000 NaN False
510 OI00511 O00191 3 32000 5000 86000 91000.0 False
if DATA_READY:
    display(
        order_items_raw[
            order_items_raw["order_item_id"].isin(
                ["OI00071", "OI00215"]
            )
        ][
            [
                "order_item_id",
                "order_id",
                "quantity",
                "unit_price",
                "line_discount",
                "line_amount"
            ]
        ]
    )
order_item_id order_id quantity unit_price line_discount line_amount
70 OI00071 O00027 3 pcs 20000 5000 55000
214 OI00215 O00083 1 pcs 22000 0 22000
if DATA_READY:
    print("Jumlah variasi nilai quantity:")
    print(order_items_raw["quantity"].astype(str).value_counts().head(30))

    print("\nJumlah nilai quantity yang mengandung teks:")
    quantity_text_mask = (
        order_items_raw["quantity"]
        .astype(str)
        .str.contains(r"[A-Za-z]", na=False)
    )
    print(int(quantity_text_mask.sum()))

    print("\nBaris dengan quantity yang mengandung teks:")
    display(
        order_items_raw.loc[
            quantity_text_mask,
            ["order_item_id", "order_id", "quantity", "unit_price",
             "line_discount", "line_amount"]
        ]
    )
Jumlah variasi nilai quantity:
quantity
2        287
1        280
3        263
4         40
3 pcs      1
1 pcs      1
Name: count, dtype: int64

Jumlah nilai quantity yang mengandung teks:
2

Baris dengan quantity yang mengandung teks:
order_item_id order_id quantity unit_price line_discount line_amount
70 OI00071 O00027 3 pcs 20000 5000 55000
214 OI00215 O00083 1 pcs 22000 0 22000
if DATA_READY:
    display(
        order_items_raw[
            order_items_raw["order_item_id"].isin(
                ["OI00131", "OI00511"]
            )
        ][
            [
                "order_item_id",
                "order_id",
                "quantity",
                "unit_price",
                "line_discount",
                "line_amount"
            ]
        ]
    )

    print("\nDetail item untuk kedua pesanan:")
    display(
        order_items_raw[
            order_items_raw["order_id"].isin(
                ["O00050", "O00191"]
            )
        ][
            [
                "order_item_id",
                "order_id",
                "quantity",
                "unit_price",
                "line_discount",
                "line_amount"
            ]
        ].sort_values(["order_id", "order_item_id"])
    )
order_item_id order_id quantity unit_price line_discount line_amount
130 OI00131 O00050 3 20000 0 67000
510 OI00511 O00191 3 32000 5000 86000
Detail item untuk kedua pesanan:
order_item_id order_id quantity unit_price line_discount line_amount
130 OI00131 O00050 3 20000 0 67000
131 OI00132 O00050 2 20000 0 40000
132 OI00133 O00050 2 32000 2000 62000
133 OI00134 O00050 3 16000 5000 43000
508 OI00509 O00191 2 42000 2000 82000
509 OI00510 O00191 1 26000 0 26000
510 OI00511 O00191 3 32000 5000 86000
if DATA_READY:
    display(
        orders_raw.loc[
            orders_raw["order_id"].isin(["O00050", "O00191"]),
            [
                "order_id",
                "subtotal_amount",
                "delivery_fee",
                "discount_amount",
                "total_amount"
            ]
        ].to_string(index=False)
    )
'order_id  subtotal_amount  delivery_fee  discount_amount  total_amount\n  O00050           205000         10000            10000        205000\n  O00191           199000          8000            10000        197000'
date_columns = {
    "customers": (customers_raw, "signup_date"),
    "orders": (orders_raw, "order_timestamp"),
    "payments": (payments_raw, "payment_timestamp"),
    "restaurants": (restaurants_raw, "onboarding_date"),
}

for dataset_name, (df, column) in date_columns.items():
    print(f"\n{'=' * 60}")
    print(f"Dataset: {dataset_name} | Kolom: {column}")
    print(f"Total baris: {len(df)}")
    print(f"Nilai null: {df[column].isna().sum()}")
    print(f"Jumlah nilai unik: {df[column].nunique(dropna=True)}")

    print("\nContoh nilai:")
    display(df[column].dropna().drop_duplicates().head(10).to_frame())
============================================================
Dataset: customers | Kolom: signup_date
Total baris: 159
Nilai null: 0
Jumlah nilai unik: 108

Contoh nilai:
signup_date
0 2025-10-30
1 2025-11-02
2 2025-08-07
3 2025-11-12
4 2025-07-30
5 2025-08-11
6 2025-06-03
7 2025-07-25
8 2025-10-31
9 2025-11-22
============================================================
Dataset: orders | Kolom: order_timestamp
Total baris: 330
Nilai null: 0
Jumlah nilai unik: 329

Contoh nilai:
order_timestamp
0 2026-01-31 00:19:00
1 2026-03-25 04:52:00
2 2026-01-29 14:16:00
3 2026-01-02 16:09:00
4 2026-03-10 14:20:00
5 2026-03-14 14:50:00
6 2026-02-18 18:48:00
7 2026-03-29 19:52:00
8 2026-01-14 13:00:00
9 2026-02-12 21:37:00
============================================================
Dataset: payments | Kolom: payment_timestamp
Total baris: 374
Nilai null: 0
Jumlah nilai unik: 373

Contoh nilai:
payment_timestamp
0 2026-01-31 00:21:00
1 2026-01-31 00:24:00
2 2026-03-25 04:55:00
3 2026-01-29 14:19:00
4 2026-01-02 16:12:00
5 2026-03-10 14:23:00
6 2026-03-14 14:53:00
7 2026-02-18 18:50:00
8 2026-02-18 18:53:00
9 2026-03-29 19:55:00
============================================================
Dataset: restaurants | Kolom: onboarding_date
Total baris: 28
Nilai null: 0
Jumlah nilai unik: 28

Contoh nilai:
onboarding_date
0 2024-06-15
1 2025-01-15
2 2024-10-30
3 2024-09-24
4 2024-11-14
5 2025-04-02
6 2025-01-18
7 2024-05-07
8 2025-03-13
9 2024-06-17
date_formats = {
    "customers": ("signup_date", "%Y-%m-%d"),
    "orders": ("order_timestamp", "%Y-%m-%d %H:%M:%S"),
    "payments": ("payment_timestamp", "%Y-%m-%d %H:%M:%S"),
    "restaurants": ("onboarding_date", "%Y-%m-%d"),
}

date_results = []

for dataset_name, (column, date_format) in date_formats.items():
    df = date_columns[dataset_name][0]
    raw = df[column]

    # Parse untuk validasi saja; data asli tidak diubah
    parsed = pd.to_datetime(
        raw,
        format=date_format,
        errors="coerce"
    )

    invalid_mask = raw.notna() & parsed.isna()
    duplicate_count = raw.notna().sum() - raw.nunique(dropna=True)

    date_results.append({
        "dataset": dataset_name,
        "column": column,
        "total_rows": len(raw),
        "null_count": raw.isna().sum(),
        "invalid_format_or_date": invalid_mask.sum(),
        "duplicate_values": duplicate_count,
        "earliest_date": parsed.min(),
        "latest_date": parsed.max(),
    })

    if invalid_mask.any():
        print(f"\nNilai tanggal tidak valid — {dataset_name}:")
        display(df.loc[invalid_mask, [column]].head(20))

date_validation = pd.DataFrame(date_results)
display(date_validation)
Nilai tanggal tidak valid — customers:
signup_date
21 15/08/2025
68 Oct 02 2025
dataset column total_rows null_count invalid_format_or_date duplicate_values earliest_date latest_date
0 customers signup_date 159 0 2 51 2025-06-01 00:00:00 2025-12-27 00:00:00
1 orders order_timestamp 330 0 0 1 2026-01-01 17:30:00 2026-03-31 00:01:00
2 payments payment_timestamp 374 0 0 1 2026-01-01 17:33:00 2026-03-31 00:06:00
3 restaurants onboarding_date 28 0 0 0 2024-01-03 00:00:00 2025-05-04 00:00:00
customers_raw.loc[[21, 68], ["signup_date"]].copy().assign(
    parsed_date=lambda x: [
        pd.to_datetime("15/08/2025", format="%d/%m/%Y"),
        pd.to_datetime("Oct 02 2025", format="%b %d %Y")
    ]
)
signup_date parsed_date
21 15/08/2025 2025-08-15
68 Oct 02 2025 2025-10-02

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

cleaning_rules = pd.DataFrame(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

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

new_rules = [
    {
        "rule_id": "CR-01",
        "source_finding_id": "F-01",
        "dataset": "customers",
        "trigger_condition": "customer_id C0031 muncul dua kali dengan seluruh nilai kolom identik",
        "decision": "FIX",
        "cleaning_action": "Hapus satu baris duplikat C0031 dan pertahankan satu baris yang identik",
        "reason_evidence": "C0031 ditemukan pada indeks 30 dan 158; seluruh nilai pada kedua baris identik",
        "implementation_note": "Terapkan pada customers_clean, pertahankan satu baris C0031",
        "validation_criteria": "Jumlah baris customers_clean menjadi 158 dan customer_id C0031 hanya muncul satu kali",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-02",
        "source_finding_id": "F-02",
        "dataset": "customers",
        "trigger_condition": "customer_segment memiliki variasi penulisan Regular, REGULAR, Loyal, dan LOYAL",
        "decision": "STANDARDIZE",
        "cleaning_action": "Standarkan label menjadi Regular dan Loyal",
        "reason_evidence": "Ditemukan variasi huruf kapital dan spasi pada label customer_segment",
        "implementation_note": "Terapkan pemetaan label pada customers_clean tanpa mengubah kategori",
        "validation_criteria": "Nilai unik customer_segment hanya Regular dan Loyal",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-03",
        "source_finding_id": "F-03",
        "dataset": "orders",
        "trigger_condition": "order_status memiliki label completed dan COMPLETED dengan spasi di akhir",
        "decision": "STANDARDIZE",
        "cleaning_action": "Standarkan label COMPLETED menjadi completed",
        "reason_evidence": "Variasi kapitalisasi dan spasi ditemukan pada label status yang sama",
        "implementation_note": "Terapkan pada orders_clean; pertahankan status cancelled",
        "validation_criteria": "Label completed konsisten dan status cancelled tetap dipertahankan",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-04",
        "source_finding_id": "F-06",
        "dataset": "order_items",
        "trigger_condition": "item_category memiliki variasi Main dan MAIN dengan spasi di akhir",
        "decision": "STANDARDIZE",
        "cleaning_action": "Standarkan label MAIN menjadi Main",
        "reason_evidence": "Ditemukan variasi kapitalisasi dan spasi pada label kategori",
        "implementation_note": "Terapkan pada order_items_clean tanpa mengubah makna kategori",
        "validation_criteria": "Label kategori Main konsisten dan tidak ada variasi MAIN dengan spasi",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-05",
        "source_finding_id": "F-07",
        "dataset": "restaurants",
        "trigger_condition": "price_tier memiliki variasi Premium, PREMIUM, Budget, dan BUDGET dengan spasi di akhir",
        "decision": "STANDARDIZE",
        "cleaning_action": "Standarkan label menjadi Premium dan Budget",
        "reason_evidence": "Ditemukan variasi kapitalisasi dan spasi pada label price_tier",
        "implementation_note": "Terapkan pemetaan label pada restaurants_clean",
        "validation_criteria": "Nilai unik price_tier hanya Premium dan Budget",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-06",
        "source_finding_id": "F-11",
        "dataset": "customers",
        "trigger_condition": "signup_date memiliki format tanggal yang tidak konsisten, termasuk 15/08/2025 dan Oct 02 2025",
        "decision": "STANDARDIZE",
        "cleaning_action": "Seragamkan seluruh nilai signup_date yang valid menjadi format YYYY-MM-DD",
        "reason_evidence": "Nilai pada indeks 21 berhasil dikonversi menjadi 2025-08-15 dan indeks 68 menjadi 2025-10-02",
        "implementation_note": "Terapkan hanya pada customers_clean dan pertahankan tanggal yang direpresentasikan",
        "validation_criteria": "Semua nilai signup_date yang tidak kosong mengikuti format YYYY-MM-DD; jumlah baris tetap dan tidak ada nilai kosong baru",
        "rule_status": "Draft",
    },
        {
        "rule_id": "CR-07",
        "source_finding_id": "F-12",
        "dataset": "payments",
        "trigger_condition": "payment_method memiliki variasi cash dan CASH , serta credit_card dan CREDIT CARD ",
        "decision": "STANDARDIZE",
        "cleaning_action": "Standarkan CASH menjadi cash dan CREDIT CARD menjadi credit_card setelah menghapus spasi di akhir label",
        "reason_evidence": "Pemeriksaan nilai unik menemukan cash sebanyak 89 baris dan CASH dengan spasi di akhir sebanyak 1 baris, serta credit_card sebanyak 93 baris dan CREDIT CARD dengan spasi di akhir sebanyak 1 baris",
        "implementation_note": "Terapkan pemetaan hanya pada payments_clean['payment_method']; jangan menghapus baris atau mengubah kolom lain",
        "validation_criteria": "Nilai CASH dan CREDIT CARD dengan spasi di akhir tidak lagi ditemukan; label standar cash dan credit_card tetap ada; jumlah baris tetap 374 dan kolom lain tidak berubah",
        "rule_status": "Draft",
    },
       {
        "rule_id": "CR-08",
        "source_finding_id": "F-05",
        "dataset": "order_items",
        "trigger_condition": "Kolom quantity bertipe teks dan mengandung akhiran 'pcs' pada baris OI00071 dan OI00215",
        "decision": "STANDARDIZE",
        "cleaning_action": "Menghapus teks 'pcs' dan spasi, lalu mengonversi kolom quantity ke integer",
        "reason_evidence": "Ditemukan 2 baris non-numerik ('3 pcs' dan '1 pcs') dari 872 baris yang menyebabkan konversi numerik gagal",
        "implementation_note": "Terapkan pada order_items_clean['quantity']; pastikan nilai '3 pcs' menjadi 3 dan '1 pcs' menjadi 1",
        "validation_criteria": "Tidak ada lagi string 'pcs', seluruh kolom quantity bertipe integer, tidak ada nilai NaN baru",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-09",
        "source_finding_id": "F-10",
        "dataset": "order_items",
        "trigger_condition": "Dua baris memiliki line_amount yang tidak sesuai rumus (OI00131 selisih 7.000 dan OI00511 selisih 5.000)",
        "decision": "FIX",
        "cleaning_action": "Menghitung ulang line_amounnt = quantity * unit_price - line_discount",
        "reason_evidence": "OI00131 bernilai 67.000 (harus 60.000) dan OI00511 bernilai 86.000 (harus 91.000). Koreksi ini cocok dengan rekonsiliasi subtotal order O00050 dan O00191",
        "implementation_note": "Terapkan formula pada order_items_clean setelah quantity distandarkan menjadi angka",
        "validation_criteria": "Seluruh baris order_items_clean memenuhi formula line_amount tanpa ada selisih perhitungan",
        "rule_status": "Draft",
    },
     {
        "rule_id": "CR-10",
        "source_finding_id": "F-08",
        "dataset": "orders",
        "trigger_condition": "Subtotal amount pada order O00201 tercatat 161.000, selisih 10.000 dengan jumlah item (151.000) dan pembayaran (159.000)",
        "decision": "FIX",
        "cleaning_action": "Menyesuaikan subtotal_amount menjadi 151.000 dan total_amount menjadi 159.000 pada order O00201",
        "reason_evidence": "Tabel payments membuktikan pelanggan membayar 159.000 (151.000 item + 8.000 delivery fee). Subtotal 161.000 adalah salah rekam di sistem order",
        "implementation_note": "Perbarui baris O00201 pada orders_clean",
        "validation_criteria": "Rekonsiliasi subtotal_amount dengan SUM(line_amount) untuk seluruh order cocok tanpa selisih (mismatch = 0)",
        "rule_status": "Draft",
    },

]

cleaning_rules = pd.DataFrame(new_rules, 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 customer_id C0031 muncul dua kali dengan selur... FIX Hapus satu baris duplikat C0031 dan pertahanka... C0031 ditemukan pada indeks 30 dan 158; seluru... Terapkan pada customers_clean, pertahankan sat... Jumlah baris customers_clean menjadi 158 dan c... Draft
1 CR-02 F-02 customers customer_segment memiliki variasi penulisan Re... STANDARDIZE Standarkan label menjadi Regular dan Loyal Ditemukan variasi huruf kapital dan spasi pada... Terapkan pemetaan label pada customers_clean t... Nilai unik customer_segment hanya Regular dan ... Draft
2 CR-03 F-03 orders order_status memiliki label completed dan COMP... STANDARDIZE Standarkan label COMPLETED menjadi completed Variasi kapitalisasi dan spasi ditemukan pada ... Terapkan pada orders_clean; pertahankan status... Label completed konsisten dan status cancelled... Draft
3 CR-04 F-06 order_items item_category memiliki variasi Main dan MAIN d... STANDARDIZE Standarkan label MAIN menjadi Main Ditemukan variasi kapitalisasi dan spasi pada ... Terapkan pada order_items_clean tanpa mengubah... Label kategori Main konsisten dan tidak ada va... Draft
4 CR-05 F-07 restaurants price_tier memiliki variasi Premium, PREMIUM, ... STANDARDIZE Standarkan label menjadi Premium dan Budget Ditemukan variasi kapitalisasi dan spasi pada ... Terapkan pemetaan label pada restaurants_clean Nilai unik price_tier hanya Premium dan Budget Draft
5 CR-06 F-11 customers signup_date memiliki format tanggal yang tidak... STANDARDIZE Seragamkan seluruh nilai signup_date yang vali... Nilai pada indeks 21 berhasil dikonversi menja... Terapkan hanya pada customers_clean dan pertah... Semua nilai signup_date yang tidak kosong meng... Draft
6 CR-07 F-12 payments payment_method memiliki variasi cash dan CASH ... STANDARDIZE Standarkan CASH menjadi cash dan CREDIT CARD m... Pemeriksaan nilai unik menemukan cash sebanyak... Terapkan pemetaan hanya pada payments_clean['p... Nilai CASH dan CREDIT CARD dengan spasi di akh... Draft
7 CR-08 F-05 order_items Kolom quantity bertipe teks dan mengandung akh... STANDARDIZE Menghapus teks 'pcs' dan spasi, lalu mengonver... Ditemukan 2 baris non-numerik ('3 pcs' dan '1 ... Terapkan pada order_items_clean['quantity']; p... Tidak ada lagi string 'pcs', seluruh kolom qua... Draft
8 CR-09 F-10 order_items Dua baris memiliki line_amount yang tidak sesu... FIX Menghitung ulang line_amounnt = quantity * uni... OI00131 bernilai 67.000 (harus 60.000) dan OI0... Terapkan formula pada order_items_clean setela... Seluruh baris order_items_clean memenuhi formu... Draft
9 CR-10 F-08 orders Subtotal amount pada order O00201 tercatat 161... FIX Menyesuaikan subtotal_amount menjadi 151.000 d... Tabel payments membuktikan pelanggan membayar ... Perbarui baris O00201 pada orders_clean Rekonsiliasi subtotal_amount dengan SUM(line_a... Draft
print(cleaning_rules.columns.tolist())
display(cleaning_rules[["rule_id", "source_finding_id", "dataset", "decision", "rule_status"]])
['rule_id', 'source_finding_id', 'dataset', 'trigger_condition', 'decision', 'cleaning_action', 'reason_evidence', 'implementation_note', 'validation_criteria', 'rule_status']
rule_id source_finding_id dataset decision rule_status
0 CR-01 F-01 customers FIX Draft
1 CR-02 F-02 customers STANDARDIZE Draft
2 CR-03 F-03 orders STANDARDIZE Draft
3 CR-04 F-06 order_items STANDARDIZE Draft
4 CR-05 F-07 restaurants STANDARDIZE Draft
5 CR-06 F-11 customers STANDARDIZE Draft
6 CR-07 F-12 payments STANDARDIZE Draft
7 CR-08 F-05 order_items STANDARDIZE Draft
8 CR-09 F-10 order_items FIX Draft
9 CR-10 F-08 orders FIX Draft

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.

pass

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 = pd.DataFrame(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
from IPython import display
# ============================================================
# EDIT AREA 2 — APPROVED CLEANING CODE
# ============================================================

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 = []


def add_cleaning_log(
    rule_id, dataset, scope, action,
    before, after, affected, notes="",
    result="Pending",
    prefix="CL"  # Diselaraskan dengan format Working Paper Excel: CL-01, CL-02, dst.
):
    cleaning_log_rows.append({
        "log_id": f"{prefix}-{len(cleaning_log_rows) + 1:02d}",
        "rule_id": rule_id,
        "dataset": dataset,
        "affected_records_scope": scope,
        "action_performed": action,
        "before_evidence": before,
        "after_evidence": after,
        "rows_records_affected": affected,
        "result": result,
        "notes": notes,
    })


# ------------------------------------------------------------
# CR-01 — Menghapus satu baris customer duplikat
# Source Finding: F-01
# ------------------------------------------------------------
before_rows = len(customers_clean)
before_duplicates = int(
    customers_clean["customer_id"].duplicated(keep="first").sum()
)
before_c0031 = int(
    (customers_clean["customer_id"] == "C0031").sum()
)

customers_clean = customers_clean.drop_duplicates(
    subset=["customer_id"], keep="first"
).copy()

after_rows = len(customers_clean)
after_c0031 = int(
    (customers_clean["customer_id"] == "C0031").sum()
)

cr01_pass = (
    after_rows == 158
    and after_c0031 == 1
)

add_cleaning_log(
    "CR-01", "customers", "customer_id C0031",
    "Mempertahankan satu baris untuk setiap customer_id duplikat",
    f"Baris={before_rows}; C0031={before_c0031}",
    f"Baris={after_rows}; C0031={after_c0031}",
    before_rows - after_rows,
    f"Duplikat customer_id sebelum cleaning={before_duplicates}",
    "Validated" if cr01_pass else "Needs Review"
)


# ------------------------------------------------------------
# CR-02 — Menyeragamkan customer_segment
# Source Finding: F-02
# ------------------------------------------------------------
before_segment = customers_clean["customer_segment"].copy()

customers_clean["customer_segment"] = (
    customers_clean["customer_segment"]
    .astype("string")
    .str.strip()
    .replace({"REGULAR": "Regular", "LOYAL": "Loyal"})
)

changed = int(
    before_segment.fillna("<NA>").ne(
        customers_clean["customer_segment"].fillna("<NA>")
    ).sum()
)

segment_values = set(
    customers_clean["customer_segment"].dropna().unique()
)

# Kategori sah mencakup Regular, Loyal, dan New
cr02_pass = (
    segment_values.issubset({"Regular", "Loyal", "New"})
    and not any(v in {"REGULAR", "LOYAL"} for v in segment_values)
)

add_cleaning_log(
    "CR-02", "customers", "customer_segment",
    "Menghapus spasi berlebih dan menyeragamkan kapitalisasi kategori",
    f"Nilai unik sebelum={before_segment.nunique(dropna=True)}",
    f"Nilai unik sesudah={customers_clean['customer_segment'].nunique(dropna=True)}",
    changed,
    f"Nilai unik sesudah={sorted(segment_values)}",
    "Validated" if cr02_pass else "Needs Review"
)


# ------------------------------------------------------------
# CR-03 — Menyeragamkan order_status
# Source Finding: F-03
# ------------------------------------------------------------
before_status = orders_clean["order_status"].copy()

orders_clean["order_status"] = (
    orders_clean["order_status"]
    .astype("string")
    .str.strip()
    .replace({"COMPLETED": "completed"})
)

changed = int(
    before_status.fillna("<NA>").ne(
        orders_clean["order_status"].fillna("<NA>")
    ).sum()
)

status_values = set(
    orders_clean["order_status"].dropna().unique()
)

# Status kanonikal hanya completed dan cancelled
cr03_pass = (
    status_values.issubset({"completed", "cancelled"})
    and "completed" in status_values
    and "COMPLETED" not in status_values
)

add_cleaning_log(
    "CR-03", "orders", "order_status",
    "Menghapus spasi berlebih dan menyeragamkan completed",
    f"Nilai unik sebelum={before_status.nunique(dropna=True)}",
    f"Nilai unik sesudah={orders_clean['order_status'].nunique(dropna=True)}",
    changed,
    "Status selain completed dipertahankan.",
    "Validated" if cr03_pass else "Needs Review"
)


# ------------------------------------------------------------
# CR-04 — Menyeragamkan item_category
# Source Finding: F-06
# ------------------------------------------------------------
before_category = order_items_clean["item_category"].copy()

order_items_clean["item_category"] = (
    order_items_clean["item_category"]
    .astype("string")
    .str.strip()
    .replace({"MAIN": "Main"})
)

changed = int(
    before_category.fillna("<NA>").ne(
        order_items_clean["item_category"].fillna("<NA>")
    ).sum()
)

category_values = set(
    order_items_clean["item_category"].dropna().unique()
)

cr04_pass = (
    "MAIN" not in category_values
    and "Main" in category_values
    and not any(v != v.strip() for v in category_values)
)

add_cleaning_log(
    "CR-04", "order_items", "item_category",
    "Menghapus spasi berlebih dan menyeragamkan kategori Main",
    f"Nilai unik sebelum={before_category.nunique(dropna=True)}",
    f"Nilai unik sesudah={order_items_clean['item_category'].nunique(dropna=True)}",
    changed,
    f"Nilai unik sesudah={sorted(category_values)}",
    "Validated" if cr04_pass else "Needs Review"
)


# ------------------------------------------------------------
# CR-05 — Menyeragamkan price_tier
# Source Finding: F-07
# ------------------------------------------------------------
before_tier = restaurants_clean["price_tier"].copy()

restaurants_clean["price_tier"] = (
    restaurants_clean["price_tier"]
    .astype("string")
    .str.strip()
    .replace({"PREMIUM": "Premium", "BUDGET": "Budget"})
)

changed = int(
    before_tier.fillna("<NA>").ne(
        restaurants_clean["price_tier"].fillna("<NA>")
    ).sum()
)

tier_values = set(
    restaurants_clean["price_tier"].dropna().unique()
)

# Kategori harga hanya Budget, Mid, dan Premium
cr05_pass = (
    tier_values.issubset({"Budget", "Mid", "Premium"})
    and {"Premium", "Budget"}.issubset(tier_values)
    and not any(v in {"PREMIUM", "BUDGET"} for v in tier_values)
    and not any(v != v.strip() for v in tier_values)
)

add_cleaning_log(
    "CR-05", "restaurants", "price_tier",
    "Menghapus spasi berlebih dan menyeragamkan kategori harga",
    f"Nilai unik sebelum={before_tier.nunique(dropna=True)}",
    f"Nilai unik sesudah={restaurants_clean['price_tier'].nunique(dropna=True)}",
    changed,
    f"Nilai unik sesudah={sorted(tier_values)}; kategori Mid dipertahankan bila ada.",
    "Validated" if cr05_pass else "Needs Review"
)


# ------------------------------------------------------------
# CR-06 — Menyeragamkan format signup_date
# Source Finding: F-11
# ------------------------------------------------------------
before_dates = customers_clean["signup_date"].copy()
before_rows = len(customers_clean)
before_nulls = int(before_dates.isna().sum())


def standardize_signup_date(value):
    if pd.isna(value):
        return pd.NA

    for date_format in ("%Y-%m-%d", "%d/%m/%Y", "%b %d %Y"):
        parsed = pd.to_datetime(
            value, format=date_format, errors="coerce"
        )
        if not pd.isna(parsed):
            return parsed.strftime("%Y-%m-%d")

    return pd.NA


customers_clean["signup_date"] = before_dates.map(
    standardize_signup_date
).astype("string")

after_rows = len(customers_clean)
after_nulls = int(customers_clean["signup_date"].isna().sum())

changed = int(
    before_dates.fillna("<NA>").ne(
        customers_clean["signup_date"].fillna("<NA>")
    ).sum()
)

# Format YYYY-MM-DD diperiksa secara eksplisit
non_null_dates = customers_clean["signup_date"].dropna()
date_format_ok = non_null_dates.str.fullmatch(
    r"\d{4}-\d{2}-\d{2}"
).all()

cr06_pass = (
    after_rows == before_rows
    and after_nulls <= before_nulls
    and date_format_ok
)

add_cleaning_log(
    "CR-06", "customers", "signup_date",
    "Mengonversi tanggal valid ke format YYYY-MM-DD",
    f"Contoh indeks 21={before_dates.loc[21] if 21 in before_dates.index else '-'}; "
    f"indeks 68={before_dates.loc[68] if 68 in before_dates.index else '-'}; "
    f"baris={before_rows}; null={before_nulls}",
    f"Indeks 21={customers_clean.loc[21, 'signup_date'] if 21 in customers_clean.index else '-'}; "
    f"indeks 68={customers_clean.loc[68, 'signup_date'] if 68 in customers_clean.index else '-'}; "
    f"baris={after_rows}; null={after_nulls}",
    changed,
    "Periksa nilai tanggal yang gagal dikonversi sebelum menyatakan PASS.",
    "Validated" if cr06_pass else "Needs Review"
)


# ------------------------------------------------------------
# CR-07 — Menyeragamkan payment_method
# Source Finding: F-12
# ------------------------------------------------------------
before_method = payments_clean["payment_method"].copy()
before_rows = len(payments_clean)
before_unique = before_method.value_counts(
    dropna=False
).to_dict()

payments_clean["payment_method"] = (
    payments_clean["payment_method"]
    .astype("string")
    .str.strip()
    .replace({
        "CASH": "cash",
        "CREDIT CARD": "credit_card",
    })
)

after_method = payments_clean["payment_method"]
after_rows = len(payments_clean)

changed = int(
    before_method.fillna("<NA>").ne(
        after_method.fillna("<NA>")
    ).sum()
)

after_unique = after_method.value_counts(
    dropna=False
).to_dict()

remaining_variants = {
    str(value)
    for value in after_method.dropna().unique()
}.intersection({"CASH", "CREDIT CARD"})

cr07_pass = (
    after_rows == before_rows == 374
    and len(remaining_variants) == 0
    and {"cash", "credit_card"}.issubset(
        set(after_method.dropna().unique())
    )
    and not any(v != v.strip() for v in after_method.dropna().unique())
)

add_cleaning_log(
    "CR-07", "payments", "payment_method",
    "Menghapus spasi berlebih dan menyeragamkan label metode pembayaran",
    f"Nilai unik sebelum={before_unique}; baris={before_rows}",
    f"Nilai unik sesudah={after_unique}; baris={after_rows}",
    changed,
    "Kolom lain tidak diubah oleh blok CR-07. Pastikan definisi CREDIT CARD dan credit_card setara.",
    "Validated" if cr07_pass else "Needs Review"
)

# ------------------------------------------------------------
# CR-08 — Menyeragamkan quantity ke numerik integer
# Source Finding: F-05
# ------------------------------------------------------------
before_qty = order_items_clean["quantity"].copy()
before_non_numeric = int(
    pd.to_numeric(before_qty.astype(str).str.strip(), errors="coerce").isna().sum()
)

# Hilangkan 'pcs' dan spasi, lalu ubah ke integer
order_items_clean["quantity"] = (
    order_items_clean["quantity"]
    .astype(str)
    .str.replace("pcs", "", regex=False)
    .str.strip()
    .astype(int)
)

after_qty = order_items_clean["quantity"]
after_non_numeric = int(
    pd.to_numeric(after_qty, errors="coerce").isna().sum()
)

changed_qty = int(
    before_qty.astype(str).fillna("<NA>").ne(
        after_qty.astype(str).fillna("<NA>")
    ).sum()
)

cr08_pass = (
    after_non_numeric == 0
    and changed_qty == 2
    and order_items_clean.loc[order_items_clean["order_item_id"] == "OI00071", "quantity"].values[0] == 3
    and order_items_clean.loc[order_items_clean["order_item_id"] == "OI00215", "quantity"].values[0] == 1
)

add_cleaning_log(
    "CR-08", "order_items", "quantity",
    "Menghapus teks 'pcs' dan mengonversi kolom quantity ke integer",
    f"Baris non-numerik sebelum={before_non_numeric}",
    f"Baris non-numerik sesudah={after_non_numeric}",
    changed_qty,
    "OI00071 diubah menjadi 3 dan OI00215 diubah menjadi 1.",
    "Validated" if cr08_pass else "Needs Review"
)


# ------------------------------------------------------------
# CR-09 — Koreksi perhitungan line_amount sesuai formula
# Source Finding: F-10
# ------------------------------------------------------------
before_line_amount = order_items_clean["line_amount"].copy()

expected_calc = (
    order_items_clean["quantity"] * order_items_clean["unit_price"]
    - order_items_clean["line_discount"]
)

before_mismatch = int(
    before_line_amount.ne(expected_calc).sum()
)

# Hitung ulang line_amount berdasarkan formula resmi
order_items_clean["line_amount"] = expected_calc

after_line_amount = order_items_clean["line_amount"]
after_mismatch = int(
    after_line_amount.ne(expected_calc).sum()
)

changed_formula = int(
    before_line_amount.ne(after_line_amount).sum()
)

cr09_pass = (
    after_mismatch == 0
    and changed_formula == 2
)

add_cleaning_log(
    "CR-09", "order_items", "line_amount",
    "Mengoreksi line_amount sesuai formula quantity * unit_price - line_discount",
    f"Baris salah hitung sebelum={before_mismatch}",
    f"Baris salah hitung sesudah={after_mismatch}",
    changed_formula,
    "Koreksi dilakukan pada OI00131 (67.000 -> 60.000) dan OI00511 (86.000 -> 91.000).",
    "Validated" if cr09_pass else "Needs Review"
)

# ------------------------------------------------------------
# CR-10 — Koreksi subtotal_amount dan total_amount pada O00201
# Source Finding: F-08
# ------------------------------------------------------------
before_subtotal_o201 = orders_clean.loc[
    orders_clean["order_id"] == "O00201", "subtotal_amount"
].values[0]
before_total_o201 = orders_clean.loc[
    orders_clean["order_id"] == "O00201", "total_amount"
].values[0]

# Hitung subtotal riil dari total item O00201
o201_item_sum = int(
    order_items_clean.loc[
        order_items_clean["order_id"] == "O00201", "line_amount"
    ].sum()
)

# Update subtotal_amount dan total_amount agar sesuai dengan item dan payment
orders_clean.loc[orders_clean["order_id"] == "O00201", "subtotal_amount"] = o201_item_sum
orders_clean.loc[orders_clean["order_id"] == "O00201", "total_amount"] = (
    o201_item_sum
    + orders_clean.loc[orders_clean["order_id"] == "O00201", "delivery_fee"].values[0]
    - orders_clean.loc[orders_clean["order_id"] == "O00201", "discount_amount"].values[0]
)

after_subtotal_o201 = orders_clean.loc[
    orders_clean["order_id"] == "O00201", "subtotal_amount"
].values[0]
after_total_o201 = orders_clean.loc[
    orders_clean["order_id"] == "O00201", "total_amount"
].values[0]

cr10_pass = (
    after_subtotal_o201 == 151000
    and after_total_o201 == 159000
)

add_cleaning_log(
    "CR-10", "orders", "subtotal_amount, total_amount",
    "Menyesuaikan subtotal dan total order O00201 agar cocok dengan rincian item dan pembayaran",
    f"O00201 subtotal={before_subtotal_o201}, total={before_total_o201}",
    f"O00201 subtotal={after_subtotal_o201}, total={after_total_o201}",
    1,
    "Koreksi subtotal 161.000 -> 151.000 dan total 169.000 -> 159.000 diverifikasi dari pembayaran P00222 (159.000).",
    "Validated" if cr10_pass else "Needs Review"
)


# ------------------------------------------------------------
# BUILD CLEANING LOG
# ------------------------------------------------------------
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 customers customer_id C0031 Mempertahankan satu baris untuk setiap custome... Baris=159; C0031=2 Baris=158; C0031=1 1 Validated Duplikat customer_id sebelum cleaning=1
1 CL-02 CR-02 customers customer_segment Menghapus spasi berlebih dan menyeragamkan kap... Nilai unik sebelum=5 Nilai unik sesudah=3 2 Validated Nilai unik sesudah=['Loyal', 'New', 'Regular']
2 CL-03 CR-03 orders order_status Menghapus spasi berlebih dan menyeragamkan com... Nilai unik sebelum=3 Nilai unik sesudah=2 2 Validated Status selain completed dipertahankan.
3 CL-04 CR-04 order_items item_category Menghapus spasi berlebih dan menyeragamkan kat... Nilai unik sebelum=5 Nilai unik sesudah=4 2 Validated Nilai unik sesudah=['Dessert', 'Drink', 'Main'...
4 CL-05 CR-05 restaurants price_tier Menghapus spasi berlebih dan menyeragamkan kat... Nilai unik sebelum=5 Nilai unik sesudah=3 2 Validated Nilai unik sesudah=['Budget', 'Mid', 'Premium'...
5 CL-06 CR-06 customers signup_date Mengonversi tanggal valid ke format YYYY-MM-DD Contoh indeks 21=15/08/2025; indeks 68=Oct 02 ... Indeks 21=2025-08-15; indeks 68=2025-10-02; ba... 2 Validated Periksa nilai tanggal yang gagal dikonversi se...
6 CL-07 CR-07 payments payment_method Menghapus spasi berlebih dan menyeragamkan lab... Nilai unik sebelum={'bank_transfer': 99, 'cred... Nilai unik sesudah={'bank_transfer': 99, 'cred... 2 Validated Kolom lain tidak diubah oleh blok CR-07. Pasti...
7 CL-08 CR-08 order_items quantity Menghapus teks 'pcs' dan mengonversi kolom qua... Baris non-numerik sebelum=2 Baris non-numerik sesudah=0 2 Validated OI00071 diubah menjadi 3 dan OI00215 diubah me...
8 CL-09 CR-09 order_items line_amount Mengoreksi line_amount sesuai formula quantity... Baris salah hitung sebelum=2 Baris salah hitung sesudah=0 2 Validated Koreksi dilakukan pada OI00131 (67.000 -> 60.0...
9 CL-10 CR-10 orders subtotal_amount, total_amount Menyesuaikan subtotal dan total order O00201 a... O00201 subtotal=161000, total=169000 O00201 subtotal=151000, total=159000 1 Validated Koreksi subtotal 161.000 -> 151.000 dan total ...

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)

    # V-01 — Required columns tersedia
    required_columns = {
        "Customers": [
            "customer_id", "customer_segment", "signup_date"
        ],
        "Orders": [
            "order_id", "customer_id", "restaurant_id",
            "order_status", "order_timestamp"
        ],
        "Order Items": [
            "order_item_id", "order_id", "line_number",
            "quantity", "unit_price", "line_discount", "line_amount"
        ],
        "Payments": [
            "payment_id", "order_id", "payment_method",
            "payment_status", "payment_timestamp", "attempt_number"
        ],
        "Restaurants": [
            "restaurant_id", "price_tier", "onboarding_date"
        ],
    }

    # Perbaikan: periksa kolom pada tabel yang sesuai
    missing_columns = {
        name: [
            col for col in cols
            if col not in clean_tables[name].columns
        ]
        for name, cols in required_columns.items()
    }

    missing_columns = {
        name: cols
        for name, cols in missing_columns.items()
        if cols
    }

    passed = len(missing_columns) == 0

    record_check(
        "V-01", "Schema", "All clean tables",
        "Apakah seluruh kolom wajib tersedia?",
        "Tidak ada kolom wajib yang hilang",
        {"missing_columns": missing_columns},
        passed,
    )

    # V-02 — Customer key
    result = key_check(customers_clean, "customer_id", "Customers")
    passed = bool(
        result.loc[0, "candidate_key_unique"]
    ) and customers_clean["customer_id"].notna().all()

    record_check(
        "V-02", "Key", "Customers",
        "Apakah customer_id unik dan tidak null?",
        "Tidak ada null key dan customer_id unik",
        result.to_dict("records")[0],
        passed,
    )

    # V-03 — Order key
    result = key_check(orders_clean, "order_id", "Orders")
    passed = bool(
        result.loc[0, "candidate_key_unique"]
    ) and orders_clean["order_id"].notna().all()

    record_check(
        "V-03", "Key", "Orders",
        "Apakah order_id unik dan tidak null?",
        "Tidak ada null key dan order_id unik",
        result.to_dict("records")[0],
        passed,
    )

    # V-04 — Order Items key
    result = key_check(
        order_items_clean, "order_item_id", "Order Items"
    )
    composite_unique = not order_items_clean.duplicated(
        subset=["order_id", "line_number"]
    ).any()
    composite_not_null = order_items_clean[
        ["order_id", "line_number"]
    ].notna().all().all()

    passed = (
        bool(result.loc[0, "candidate_key_unique"])
        and order_items_clean["order_item_id"].notna().all()
        and composite_unique
        and composite_not_null
    )

    record_check(
        "V-04", "Key", "Order Items",
        "Apakah order_item_id valid dan kombinasi order_id + line_number unik?",
        "Key tidak null; key tunggal dan kombinasi key sesuai grain",
        {
            "key_check": result.to_dict("records")[0],
            "order_line_combination_unique": bool(composite_unique),
            "order_line_combination_not_null": bool(composite_not_null),
        },
        passed,
    )

    # V-05 — Payment key
    result = key_check(payments_clean, "payment_id", "Payments")
    passed = bool(
        result.loc[0, "candidate_key_unique"]
    ) and payments_clean["payment_id"].notna().all()

    record_check(
        "V-05", "Key", "Payments",
        "Apakah payment_id unik dan tidak null?",
        "Tidak ada null key dan payment_id unik; order_id boleh berulang",
        result.to_dict("records")[0],
        passed,
    )

else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes:
Customers (158, 7)
Orders (330, 10)
Order Items (872, 9)
Payments (374, 7)
Restaurants (28, 7)

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

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

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

if DATA_READY:
    # ========================================================
    # EDIT AREA 3B — V-06 s.d. V-14
    # Seluruh pemeriksaan menggunakan clean data
    # ========================================================

    # V-06 — Customers: kategori customer_segment
    allowed_segments = {"New", "Regular", "Loyal"}
    actual_segments = set(
        customers_clean["customer_segment"].dropna().astype(str).unique()
    )
    customer_category_ok = (
        customers_clean["customer_segment"].notna().all()
        and customers_clean["customer_segment"].astype(str).str.strip().eq(
            customers_clean["customer_segment"].astype(str)
        ).all()
        and actual_segments.issubset(allowed_segments)
    )

    record_check(
        "V-06", "Value", "Customers",
        "Apakah kategori customer_segment konsisten?",
        "Tidak ada null dan hanya kategori New, Regular, Loyal",
        {
            "categories": sorted(actual_segments),
            "null_count": int(customers_clean["customer_segment"].isna().sum())
        },
        customer_category_ok,
        related_rule_id="CR-02"
    )

    # V-07 — Orders: kategori order_status
    allowed_statuses = {"completed", "cancelled"}
    actual_statuses = set(
        orders_clean["order_status"].dropna().astype(str).unique()
    )
    order_category_ok = (
        orders_clean["order_status"].notna().all()
        and orders_clean["order_status"].astype(str).str.strip().eq(
            orders_clean["order_status"].astype(str)
        ).all()
        and actual_statuses.issubset(allowed_statuses)
    )

    record_check(
        "V-07", "Value", "Orders",
        "Apakah kategori order_status konsisten?",
        "Tidak ada null dan hanya status completed atau cancelled",
        {
            "categories": sorted(actual_statuses),
            "null_count": int(orders_clean["order_status"].isna().sum())
        },
        order_category_ok,
        related_rule_id="CR-03"
    )

    # V-08 — Payments dan Restaurants: periksa representasi kategori
    category_columns = {
        "payment_method": payments_clean["payment_method"],
        "payment_status": payments_clean["payment_status"],
        "price_tier": restaurants_clean["price_tier"],
    }

    category_evidence = {}
    category_representation_ok = True

    for col, series in category_columns.items():
        values = series.dropna().astype(str)
        category_evidence[col] = {
            "categories": sorted(values.unique().tolist()),
            "null_count": int(series.isna().sum()),
            "blank_count": int(values.str.strip().eq("").sum()),
            "whitespace_issue_count": int(
                values.ne(values.str.strip()).sum()
            ),
        }

        if (
            series.isna().any()
            or values.str.strip().eq("").any()
            or values.ne(values.str.strip()).any()
        ):
            category_representation_ok = False

    record_check(
        "V-08", "Value", "Payments / Restaurants",
        "Apakah representasi kategori konsisten dan tidak kosong?",
        "Kategori tidak null, tidak kosong, dan tidak memiliki spasi tepi",
        category_evidence,
        category_representation_ok,
        related_rule_id="CR-05, CR-07"
    )

    # V-09 — Order Items: quantity × unit_price - line_discount
    item_check = order_items_clean.copy()

    item_check["quantity_numeric"] = pd.to_numeric(
        item_check["quantity"].astype(str).str.strip(),
        errors="coerce"
    )
    item_check["unit_price_numeric"] = pd.to_numeric(
        item_check["unit_price"], errors="coerce"
    )
    item_check["line_discount_numeric"] = pd.to_numeric(
        item_check["line_discount"], errors="coerce"
    )
    item_check["line_amount_numeric"] = pd.to_numeric(
        item_check["line_amount"], errors="coerce"
    )

    item_check["expected_line_amount"] = (
        item_check["quantity_numeric"]
        * item_check["unit_price_numeric"]
        - item_check["line_discount_numeric"]
    )

    item_check["formula_unverifiable"] = item_check[
        [
            "quantity_numeric",
            "unit_price_numeric",
            "line_discount_numeric",
            "line_amount_numeric",
        ]
    ].isna().any(axis=1)

    item_check["formula_match"] = (
        item_check["line_amount_numeric"]
        .eq(item_check["expected_line_amount"])
        .fillna(False)
    )

    item_unverifiable = int(item_check["formula_unverifiable"].sum())
    item_mismatch = int(
        (
            ~item_check["formula_match"]
            & ~item_check["formula_unverifiable"]
        ).sum()
    )

    item_formula_evidence = {
        "total_rows": len(item_check),
        "confirmed_mismatch_count": item_mismatch,
        "unverifiable_count": item_unverifiable,
        "mismatch_ids": item_check.loc[
            ~item_check["formula_match"]
            & ~item_check["formula_unverifiable"],
            "order_item_id"
        ].tolist(),
        "unverifiable_ids": item_check.loc[
            item_check["formula_unverifiable"],
            "order_item_id"
        ].tolist(),
    }

    record_check(
        "V-09", "Business Rule", "Order Items",
        "Apakah line_amount = quantity × unit_price - line_discount?",
        "Semua baris dapat diverifikasi dan seluruh formula sesuai",
        item_formula_evidence,
        item_mismatch == 0 and item_unverifiable == 0,
        related_rule_id="F-10",
        notes="Baris yang belum dapat diverifikasi tidak dianggap valid."
    )

    # V-10 — Orders: subtotal + delivery_fee - discount_amount
    order_check = orders_clean.copy()

    order_check["expected_total"] = (
        pd.to_numeric(order_check["subtotal_amount"], errors="coerce")
        + pd.to_numeric(order_check["delivery_fee"], errors="coerce")
        - pd.to_numeric(order_check["discount_amount"], errors="coerce")
    )
    order_check["total_numeric"] = pd.to_numeric(
        order_check["total_amount"], errors="coerce"
    )

    order_check["total_unverifiable"] = order_check[
        ["expected_total", "total_numeric"]
    ].isna().any(axis=1)

    order_check["total_match"] = (
        order_check["total_numeric"]
        .eq(order_check["expected_total"])
        .fillna(False)
    )

    total_unverifiable = int(order_check["total_unverifiable"].sum())
    total_mismatch = int(
        (
            ~order_check["total_match"]
            & ~order_check["total_unverifiable"]
        ).sum()
    )

    total_evidence = {
        "total_rows": len(order_check),
        "confirmed_mismatch_count": total_mismatch,
        "unverifiable_count": total_unverifiable,
        "mismatch_ids": order_check.loc[
            ~order_check["total_match"]
            & ~order_check["total_unverifiable"],
            "order_id"
        ].tolist(),
    }

    record_check(
        "V-10", "Business Rule", "Orders",
        "Apakah total_amount = subtotal_amount + delivery_fee - discount_amount?",
        "Semua baris dapat diverifikasi dan seluruh formula sesuai",
        total_evidence,
        total_mismatch == 0 and total_unverifiable == 0
    )

    # V-11 — Rekonsiliasi Orders dengan Order Items
    item_amounts = pd.to_numeric(
        order_items_clean["line_amount"], errors="coerce"
    )

    item_sums = (
        order_items_clean.assign(_line_amount=item_amounts)
        .groupby("order_id", dropna=False)["_line_amount"]
        .sum(min_count=1)
        .rename("sum_line_amount")
        .reset_index()
    )

    recon = orders_clean[
        ["order_id", "subtotal_amount"]
    ].merge(item_sums, on="order_id", how="left")

    recon["subtotal_numeric"] = pd.to_numeric(
        recon["subtotal_amount"], errors="coerce"
    )
    recon["reconcile_unverifiable"] = recon[
        ["subtotal_numeric", "sum_line_amount"]
    ].isna().any(axis=1)

    recon["reconcile_match"] = recon["subtotal_numeric"].eq(
        recon["sum_line_amount"]
    ).fillna(False)

    recon_unverifiable = int(recon["reconcile_unverifiable"].sum())
    recon_mismatch = int(
        (
            ~recon["reconcile_match"]
            & ~recon["reconcile_unverifiable"]
        ).sum()
    )

    recon_evidence = {
        "orders_checked": len(recon),
        "confirmed_mismatch_count": recon_mismatch,
        "unverifiable_count": recon_unverifiable,
        "mismatch_order_ids": recon.loc[
            ~recon["reconcile_match"]
            & ~recon["reconcile_unverifiable"],
            "order_id"
        ].tolist(),
    }

    record_check(
        "V-11", "Cross-table", "Orders ↔ Order Items",
        "Apakah SUM(line_amount) = subtotal_amount per order?",
        "Semua order dapat direkonsiliasi tanpa selisih",
        recon_evidence,
        recon_mismatch == 0 and recon_unverifiable == 0,
        related_rule_id="F-08"
    )

    # V-12 — Orders → Customers
    fk_customer = fk_check(
        orders_clean, customers_clean,
        "customer_id", "customer_id",
        "Orders → Customers"
    )

    record_check(
        "V-12", "Cross-table", "Orders → Customers",
        "Apakah seluruh customer_id pada Orders ditemukan di Customers?",
        "Unmatched customer = 0",
        fk_customer,
        fk_customer["unmatched_count"] == 0
    )

    # V-13 — Orders → Restaurants
    fk_restaurant = fk_check(
        orders_clean, restaurants_clean,
        "restaurant_id", "restaurant_id",
        "Orders → Restaurants"
    )

    record_check(
        "V-13", "Cross-table", "Orders → Restaurants",
        "Apakah seluruh restaurant_id pada Orders ditemukan di Restaurants?",
        "Unmatched restaurant = 0",
        fk_restaurant,
        fk_restaurant["unmatched_count"] == 0
    )

    # V-14 — Order Items / Payments → Orders
    fk_items = fk_check(
        order_items_clean, orders_clean,
        "order_id", "order_id",
        "Order Items → Orders"
    )
    fk_payments = fk_check(
        payments_clean, orders_clean,
        "order_id", "order_id",
        "Payments → Orders"
    )

    fk_combined = {
        "order_items_to_orders": fk_items,
        "payments_to_orders": fk_payments,
    }

    record_check(
        "V-14", "Cross-table", "Order Items / Payments → Orders",
        "Apakah tidak ada referensi order yang orphan?",
        "Unmatched order reference = 0 pada kedua relasi",
        fk_combined,
        (
            fk_items["unmatched_count"] == 0
            and fk_payments["unmatched_count"] == 0
        )
    )

    print("VALIDATION V-06 s.d. V-14 SELESAI")
    display(pd.DataFrame(validation_results).sort_values("check_id"))

else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")

VALIDATION V-06 s.d. V-14 SELESAI
check_id validation_level dataset_relationship validation_question expected_criteria actual_result_metric status related_rule_id notes
0 V-01 Schema All clean tables Apakah seluruh kolom wajib tersedia? Tidak ada kolom wajib yang hilang {'missing_columns': {}} PASS
1 V-02 Key Customers Apakah customer_id unik dan tidak null? Tidak ada null key dan customer_id unik {'dataset': 'Customers', 'key_cols': 'customer... PASS
2 V-03 Key Orders Apakah order_id unik dan tidak null? Tidak ada null key dan order_id unik {'dataset': 'Orders', 'key_cols': 'order_id', ... PASS
3 V-04 Key Order Items Apakah order_item_id valid dan kombinasi order... Key tidak null; key tunggal dan kombinasi key ... {'key_check': {'dataset': 'Order Items', 'key_... PASS
4 V-05 Key Payments Apakah payment_id unik dan tidak null? Tidak ada null key dan payment_id unik; order_... {'dataset': 'Payments', 'key_cols': 'payment_i... PASS
5 V-06 Value Customers Apakah kategori customer_segment konsisten? Tidak ada null dan hanya kategori New, Regular... {'categories': ['Loyal', 'New', 'Regular'], 'n... PASS CR-02
6 V-07 Value Orders Apakah kategori order_status konsisten? Tidak ada null dan hanya status completed atau... {'categories': ['cancelled', 'completed'], 'nu... PASS CR-03
7 V-08 Value Payments / Restaurants Apakah representasi kategori konsisten dan tid... Kategori tidak null, tidak kosong, dan tidak m... {'payment_method': {'categories': ['bank_trans... PASS CR-05, CR-07
8 V-09 Business Rule Order Items Apakah line_amount = quantity × unit_price - l... Semua baris dapat diverifikasi dan seluruh for... {'total_rows': 872, 'confirmed_mismatch_count'... PASS F-10 Baris yang belum dapat diverifikasi tidak dian...
9 V-10 Business Rule Orders Apakah total_amount = subtotal_amount + delive... Semua baris dapat diverifikasi dan seluruh for... {'total_rows': 330, 'confirmed_mismatch_count'... PASS
10 V-11 Cross-table Orders ↔ Order Items Apakah SUM(line_amount) = subtotal_amount per ... Semua order dapat direkonsiliasi tanpa selisih {'orders_checked': 330, 'confirmed_mismatch_co... PASS F-08
11 V-12 Cross-table Orders → Customers Apakah seluruh customer_id pada Orders ditemuk... Unmatched customer = 0 {'relationship': 'Orders → Customers', 'child_... PASS
12 V-13 Cross-table Orders → Restaurants Apakah seluruh restaurant_id pada Orders ditem... Unmatched restaurant = 0 {'relationship': 'Orders → Restaurants', 'chil... PASS
13 V-14 Cross-table Order Items / Payments → Orders Apakah tidak ada referensi order yang orphan? Unmatched order reference = 0 pada kedua relasi {'order_items_to_orders': {'relationship': 'Or... 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
# ============================================================
# TODO: tambahkan evidence V-15 menggunakan record_check(...).

if DATA_READY:
    # ========================================================
    # EDIT AREA 3C — V-15 KEEP Preservation Check
    # ========================================================

    keep_evidence = {}

    # KEEP F-09: order_timestamp tetap dapat diparse
    raw_order_ts = pd.to_datetime(
        orders_raw["order_timestamp"],
        format="%Y-%m-%d %H:%M:%S",
        errors="coerce"
    )
    clean_order_ts = pd.to_datetime(
        orders_clean["order_timestamp"],
        format="%Y-%m-%d %H:%M:%S",
        errors="coerce"
    )

    timestamp_preserved = (
        len(orders_raw) == len(orders_clean)
        and raw_order_ts.notna().all()
        and clean_order_ts.notna().all()
        and raw_order_ts.reset_index(drop=True).equals(
            clean_order_ts.reset_index(drop=True)
        )
    )

    keep_evidence["F-09_order_timestamp"] = {
        "raw_rows": len(orders_raw),
        "clean_rows": len(orders_clean),
        "raw_parse_failures": int(raw_order_ts.isna().sum()),
        "clean_parse_failures": int(clean_order_ts.isna().sum()),
        "timestamp_values_preserved": timestamp_preserved
    }

    # KEEP: pengulangan order_id pada Payments dapat sah
    # karena satu order bisa memiliki beberapa payment attempts.
    payment_order_repeats_raw = int(
        payments_raw["order_id"].duplicated().sum()
    )
    payment_order_repeats_clean = int(
        payments_clean["order_id"].duplicated().sum()
    )

    payment_repeat_preserved = (
        payment_order_repeats_raw == payment_order_repeats_clean
        and len(payments_raw) == len(payments_clean)
    )

    keep_evidence["Payments_repeated_order_id"] = {
        "raw_rows": len(payments_raw),
        "clean_rows": len(payments_clean),
        "raw_repeated_order_id_rows": payment_order_repeats_raw,
        "clean_repeated_order_id_rows": payment_order_repeats_clean,
        "repeat_pattern_preserved": payment_repeat_preserved
    }

    v15_pass = (
        timestamp_preserved
        and payment_repeat_preserved
    )

    record_check(
        "V-15",
        "KEEP Preservation",
        "Orders / Payments",
        "Apakah temuan KEEP yang diperiksa tetap terjaga setelah cleaning?",
        "Timestamp yang valid tetap terjaga dan pengulangan order_id pada Payments tidak dihapus secara keliru",
        keep_evidence,
        v15_pass,
        related_rule_id="F-09",
        notes="Pemeriksaan ini mencakup dua pola KEEP yang disebutkan dalam evidence."
    )

    print("KEEP PRESERVATION EVIDENCE")
    display(pd.DataFrame([
        {"finding": key, **value}
        for key, value in keep_evidence.items()
    ]))
    print("V-15 STATUS:", "PASS" if v15_pass else "FAIL")

else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")

KEEP PRESERVATION EVIDENCE
finding raw_rows clean_rows raw_parse_failures clean_parse_failures timestamp_values_preserved raw_repeated_order_id_rows clean_repeated_order_id_rows repeat_pattern_preserved
0 F-09_order_timestamp 330 330 0.0 0.0 True NaN NaN NaN
1 Payments_repeated_order_id 374 374 NaN NaN NaN 44.0 44.0 True
V-15 STATUS: 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 tables Apakah seluruh kolom wajib tersedia? Tidak ada kolom wajib yang hilang {'missing_columns': {}} PASS
1 V-02 Key Customers Apakah customer_id unik dan tidak null? Tidak ada null key dan customer_id unik {'dataset': 'Customers', 'key_cols': 'customer... PASS
2 V-03 Key Orders Apakah order_id unik dan tidak null? Tidak ada null key dan order_id unik {'dataset': 'Orders', 'key_cols': 'order_id', ... PASS
3 V-04 Key Order Items Apakah order_item_id valid dan kombinasi order... Key tidak null; key tunggal dan kombinasi key ... {'key_check': {'dataset': 'Order Items', 'key_... PASS
4 V-05 Key Payments Apakah payment_id unik dan tidak null? Tidak ada null key dan payment_id unik; order_... {'dataset': 'Payments', 'key_cols': 'payment_i... PASS
5 V-06 Value Customers Apakah kategori customer_segment konsisten? Tidak ada null dan hanya kategori New, Regular... {'categories': ['Loyal', 'New', 'Regular'], 'n... PASS CR-02
6 V-07 Value Orders Apakah kategori order_status konsisten? Tidak ada null dan hanya status completed atau... {'categories': ['cancelled', 'completed'], 'nu... PASS CR-03
7 V-08 Value Payments / Restaurants Apakah representasi kategori konsisten dan tid... Kategori tidak null, tidak kosong, dan tidak m... {'payment_method': {'categories': ['bank_trans... PASS CR-05, CR-07
8 V-09 Business Rule Order Items Apakah line_amount = quantity × unit_price - l... Semua baris dapat diverifikasi dan seluruh for... {'total_rows': 872, 'confirmed_mismatch_count'... PASS F-10 Baris yang belum dapat diverifikasi tidak dian...
9 V-10 Business Rule Orders Apakah total_amount = subtotal_amount + delive... Semua baris dapat diverifikasi dan seluruh for... {'total_rows': 330, 'confirmed_mismatch_count'... PASS
10 V-11 Cross-table Orders ↔ Order Items Apakah SUM(line_amount) = subtotal_amount per ... Semua order dapat direkonsiliasi tanpa selisih {'orders_checked': 330, 'confirmed_mismatch_co... PASS F-08
11 V-12 Cross-table Orders → Customers Apakah seluruh customer_id pada Orders ditemuk... Unmatched customer = 0 {'relationship': 'Orders → Customers', 'child_... PASS
12 V-13 Cross-table Orders → Restaurants Apakah seluruh restaurant_id pada Orders ditem... Unmatched restaurant = 0 {'relationship': 'Orders → Restaurants', 'chil... PASS
13 V-14 Cross-table Order Items / Payments → Orders Apakah tidak ada referensi order yang orphan? Unmatched order reference = 0 pada kedua relasi {'order_items_to_orders': {'relationship': 'Or... PASS
14 V-15 KEEP Preservation Orders / Payments Apakah temuan KEEP yang diperiksa tetap terjag... Timestamp yang valid tetap terjaga dan pengula... {'F-09_order_timestamp': {'raw_rows': 330, 'cl... PASS F-09 Pemeriksaan ini mencakup dua pola KEEP yang di...
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 = (
    "Dataset dinyatakan READY (PASS) untuk analisis lanjutan berdasarkan bukti validasi berikut:\n"
    "1. Schema & Key (V-01 s.d. V-05): Seluruh kolom wajib tersedia lengkap. Primary key (customer_id, order_id, "
    "order_item_id, payment_id) dan composite key terbukti unik 100% tanpa nilai null.\n"
    "2. Standardisasi Nilai (V-06 s.d. V-08): Pembersihan pada customer_segment (CR-02), order_status (CR-03), "
    "item_category (CR-04), price_tier (CR-05), dan payment_method (CR-07) berhasil menghilangkan spasi berlebih "
    "dan menyeragamkan nilai kanonikal tanpa anomali representasi.\n"
    "3. Format & Aturan Bisnis (V-09 s.d. V-11): Kolom quantity berhasil dikonversi ke numerik (CR-08), formula line_amount "
    "terkoreksi presisi (CR-09), serta rekonsiliasi subtotal_amount dan total_amount pada order O00201 cocok sempurna "
    "dengan detail item dan bukti pembayaran P00222 (CR-10).\n"
    "4. Integritas Relasional (V-12 s.d. V-14): Seluruh Foreign Key valid tanpa referensi orphan pada relasi Orders ke Customers, "
    "Orders ke Restaurants, maupun Items/Payments ke Orders.\n"
    "5. Preservasi KEEP & Raw Integrity (V-15 & Raw Integrity): Nilai sah pada order_timestamp dan pola multiple payment "
    "tetap terjaga (tanpa over-cleaning), serta seluruh file sumber raw tidak mengalami perubahan (hash SHA-256 identik)."
)
print(FINAL_JUDGMENT_RATIONALE)

PM1 DATA READINESS GATE: PASS
Dataset dinyatakan READY (PASS) untuk analisis lanjutan berdasarkan bukti validasi berikut:
1. Schema & Key (V-01 s.d. V-05): Seluruh kolom wajib tersedia lengkap. Primary key (customer_id, order_id, order_item_id, payment_id) dan composite key terbukti unik 100% tanpa nilai null.
2. Standardisasi Nilai (V-06 s.d. V-08): Pembersihan pada customer_segment (CR-02), order_status (CR-03), item_category (CR-04), price_tier (CR-05), dan payment_method (CR-07) berhasil menghilangkan spasi berlebih dan menyeragamkan nilai kanonikal tanpa anomali representasi.
3. Format & Aturan Bisnis (V-09 s.d. V-11): Kolom quantity berhasil dikonversi ke numerik (CR-08), formula line_amount terkoreksi presisi (CR-09), serta rekonsiliasi subtotal_amount dan total_amount pada order O00201 cocok sempurna dengan detail item dan bukti pembayaran P00222 (CR-10).
4. Integritas Relasional (V-12 s.d. V-14): Seluruh Foreign Key valid tanpa referensi orphan pada relasi Orders ke Customers, Orders ke Restaurants, maupun Items/Payments ke Orders.
5. Preservasi KEEP & Raw Integrity (V-15 & Raw Integrity): Nilai sah pada order_timestamp dan pola multiple payment tetap terjaga (tanpa over-cleaning), serta seluruh file sumber raw tidak mengalami perubahan (hash SHA-256 identik).

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()

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: C:\Users\User\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\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
```