# 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 = "Naurah"
VARIANT_ID = "SB-P02"

name_ok = PARTICIPANT_NAME.strip() != ""

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 : Naurah
Variant ID  : SB-P02

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:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst
RAW_DIR        : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite
PROCESSED_DIR  : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
OUTPUT_DIR     : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\outputs\pm1_swiftbite
DOCS_DIR       : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\docs\pm1_swiftbite

Environment Smoke Test — RUN

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

print("pandas :", pd.__version__)

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

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

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

2. Verify Source Package — RUN

Letakkan **5 file berikut langsung** di:

`data/raw/pm1_swiftbite/`

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

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

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

source_status = []

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

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

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

    SOURCE_ACCESS_PASS = len(missing_files) == 0

    if SOURCE_ACCESS_PASS:
        print("SOURCE ACCESS CHECK: PASS")
        print("→ Semua 5 raw files ditemukan. Jangan ubah path.")
    else:
        print("SOURCE ACCESS CHECK: FAIL")
        print("File belum ditemukan:", missing_files)
        print("→ Extract package peserta.")
        print("→ Copy hanya 5 raw files langsung ke:", RAW_DIR)
        print("→ Jangan menaruh file di subfolder tambahan.")
logical_name filename exists path
0 customers swiftbite_customers.json True C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy...
1 orders swiftbite_orders.xlsx True C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy...
2 order_items swiftbite_order_items.csv True C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy...
3 payments swiftbite_payments.txt True C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy...
4 restaurants swiftbite_restaurants.csv True C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy...
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
#Check Error "SCHEMA GATE: FAIL
#→ Jangan lanjut cleaning. Periksa apakah package/variant yang digunakan sudah benar.
#DATA READY GATE: FAIL"

print("Customers:", customers_raw.columns.tolist())
print("Payments:", payments_raw.columns.tolist())
print("Restaurants:", restaurants_raw.columns.tolist())

#Terdapat ketidaksesuaian nama antara kolom aktual dengan required kolom, sudah dibenarkan. statu sudah PASS
Customers: ['customer_id', 'full_name', 'email', 'phone_number', 'signup_date', 'customer_zone', 'customer_segment']
Payments: ['payment_id', 'order_id', 'payment_method', 'payment_status', 'payment_amount', 'payment_timestamp', 'attempt_number']
Restaurants: ['restaurant_id', 'restaurant_name', 'cuisine_type', 'restaurant_zone', 'price_tier', 'onboarding_date', 'active_status']

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=(170, 7) ===
customer_id full_name email phone_number signup_date customer_zone customer_segment
0 C0001 Eka Wijaya ekawijaya1@example.com 082114273585 2025-08-14 Central New
1 C0002 Hana Kusuma hanakusuma2@example.com 085043192435 2025-11-18 Central New
2 C0003 Adi Saputra adisaputra3@example.com 084202997840 2025-09-17 Central Loyal
=== Orders | shape=(332, 10) ===
order_id customer_id restaurant_id order_timestamp order_status promo_code subtotal_amount delivery_fee discount_amount total_amount
0 O00001 C0040 R0020 2026-02-12 19:26:00 completed NEWUSER 367000 12000 0 379000
1 O00002 C0047 R0001 2026-01-14 14:38:00 completed HEMAT15 163000 15000 0 178000
2 O00003 C0054 R0006 2026-01-31 23:31:00 completed SAVE10 240000 10000 10000 240000
=== Order Items | shape=(944, 9) ===
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
0 OI00001 O00001 1 Cake Slice Dessert 4 24000 0 96000
1 OI00002 O00001 2 Dimsum Snack 2 22000 0 44000
2 OI00003 O00001 3 Chicken Set Main 4 42000 5000 163000
=== Payments | shape=(357, 7) ===
payment_id order_id payment_method payment_status payment_amount payment_timestamp attempt_number
0 P00001 O00001 e_wallet successful 379000 2026-02-12 19:29:00 1
1 P00002 O00002 bank_transfer successful 178000 2026-01-14 14:41:00 1
2 P00003 O00003 credit_card successful 240000 2026-01-31 23:34:00 1
=== Restaurants | shape=(24, 7) ===
restaurant_id restaurant_name cuisine_type restaurant_zone price_tier onboarding_date active_status
0 R0001 SwiftBite Partner 01 Fast Food Central Premium 2025-01-30 active
1 R0002 SwiftBite Partner 02 Cafe & Beverage Central Premium 2024-10-27 active
2 R0003 SwiftBite Partner 03 Dessert South Budget 2025-01-02 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 170 7 [customer_id, full_name, email, phone_number, ...
1 Orders 332 10 [order_id, customer_id, restaurant_id, order_t...
2 Order Items 944 9 [order_item_id, order_id, line_number, item_na...
3 Payments 357 7 [payment_id, order_id, payment_method, payment...
4 Restaurants 24 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 170 169 0 2 False
1 Orders order_id 332 332 0 0 True
2 Order Items order_item_id 944 944 0 0 True
3 Order Items — candidate business key order_id, line_number 944 944 0 0 True
4 Payments payment_id 357 357 0 0 True
5 Restaurants restaurant_id 24 24 0 0 True
#Melihat data duplikat pada dataset customer, customer_id
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 Vina Hidayat vinahidayat31@example.com 082060687029 2025-11-12 North Loyal
169 C0031 Vina Hidayat vinahidayat31@example.com 082060687029 2025-11-12 North Loyal

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
14 O00015 5
0 O00001 5
291 O00292 5
42 O00043 5
295 O00296 5
114 O00115 5
107 O00108 5
181 O00182 5
167 O00168 5
168 O00169 5
Payments cardinality
order_id payment_rows
298 O00299 2
282 O00283 2
266 O00267 2
254 O00255 2
249 O00250 2
236 O00237 2
206 O00207 2
155 O00156 2
130 O00131 2
110 O00111 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 170 7 2 1 0
1 Orders 332 10 0 0 176
2 Order Items 944 9 0 0 0
3 Payments 357 7 0 0 0
4 Restaurants 24 7 0 0 0
#Melihat data duplikat pada dataset customer, customer_id
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 Vina Hidayat vinahidayat31@example.com 082060687029 2025-11-12 North Loyal
169 C0031 Vina Hidayat vinahidayat31@example.com 082060687029 2025-11-12 North Loyal
null_cols = orders_raw.columns[orders_raw.isna().any()]

display(orders_raw.loc[orders_raw.isna().any(axis=1), null_cols])
promo_code
3 NaN
4 NaN
6 NaN
8 NaN
10 NaN
... ...
323 NaN
326 NaN
328 NaN
329 NaN
330 NaN

176 rows × 1 columns

Column-Level Profiling

# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
    display(column_profile(customers_raw))
column dtype null_count null_pct unique_count sample_values
0 customer_id str 0 0.0 169 [C0001, C0002, C0003, C0004, C0005]
1 full_name str 0 0.0 136 [Eka Wijaya, Hana Kusuma, Adi Saputra, Jihan K...
2 email str 0 0.0 169 [ekawijaya1@example.com, hanakusuma2@example.c...
3 phone_number str 0 0.0 169 [082114273585, 085043192435, 084202997840, 084...
4 signup_date str 0 0.0 118 [2025-08-14, 2025-11-18, 2025-09-17, 2025-09-1...
5 customer_zone str 0 0.0 5 [Central, West, East, South, North]
6 customer_segment str 0 0.0 4 [New, Loyal, Regular, REGULAR ]
if DATA_READY:
    display(column_profile(orders_raw))
column dtype null_count null_pct unique_count sample_values
0 order_id str 0 0.0000 332 [O00001, O00002, O00003, O00004, O00005]
1 customer_id str 0 0.0000 169 [C0040, C0047, C0054, C0061, C0068]
2 restaurant_id str 0 0.0000 24 [R0020, R0001, R0006, R0011, R0016]
3 order_timestamp str 0 0.0000 331 [2026-02-12 19:26:00, 2026-01-14 14:38:00, 202...
4 order_status str 0 0.0000 3 [completed, COMPLETED , cancelled]
5 promo_code str 176 0.5301 3 [NEWUSER, HEMAT15, SAVE10]
6 subtotal_amount int64 0 0.0000 193 [367000, 163000, 240000, 68000, 24000]
7 delivery_fee int64 0 0.0000 5 [12000, 15000, 10000, 8000, 18000]
8 discount_amount int64 0 0.0000 4 [0, 10000, 5000, 15000]
9 total_amount int64 0 0.0000 195 [379000, 178000, 240000, 73000, 34000]
if DATA_READY:
    display(column_profile(order_items_raw))
column dtype null_count null_pct unique_count sample_values
0 order_item_id str 0 0.0 944 [OI00001, OI00002, OI00003, OI00004, OI00005]
1 order_id str 0 0.0 332 [O00001, O00002, O00003, O00004, O00005]
2 line_number int64 0 0.0 5 [1, 2, 3, 4, 5]
3 item_name str 0 0.0 14 [Cake Slice, Dimsum, Chicken Set, Toast, Iced ...
4 item_category str 0 0.0 5 [Dessert, Snack, Main, Drink, SNACK ]
5 quantity str 0 0.0 6 [4, 2, 1, 3, 2 pcs]
6 unit_price int64 0 0.0 11 [24000, 22000, 42000, 16000, 9000]
7 line_discount int64 0 0.0 3 [0, 5000, 2000]
8 line_amount int64 0 0.0 82 [96000, 44000, 163000, 22000, 42000]
if DATA_READY:
    display(column_profile(payments_raw))
column dtype null_count null_pct unique_count sample_values
0 payment_id str 0 0.0 357 [P00001, P00002, P00003, P00004, P00005]
1 order_id str 0 0.0 332 [O00001, O00002, O00003, O00004, O00005]
2 payment_method str 0 0.0 5 [e_wallet, bank_transfer, credit_card, cash, E...
3 payment_status str 0 0.0 3 [successful, failed, refunded]
4 payment_amount int64 0 0.0 195 [379000, 178000, 240000, 73000, 34000]
5 payment_timestamp str 0 0.0 356 [2026-02-12 19:29:00, 2026-01-14 14:41:00, 202...
6 attempt_number int64 0 0.0 2 [1, 2]
if DATA_READY:
    display(column_profile(restaurants_raw))
column dtype null_count null_pct unique_count sample_values
0 restaurant_id str 0 0.0 24 [R0001, R0002, R0003, R0004, R0005]
1 restaurant_name str 0 0.0 24 [SwiftBite Partner 01, SwiftBite Partner 02, S...
2 cuisine_type str 0 0.0 6 [Fast Food, Cafe & Beverage, Dessert, Indonesi...
3 restaurant_zone str 0 0.0 5 [Central, South, East, North, West]
4 price_tier str 0 0.0 5 [Premium, Budget, MID , Mid, BUDGET ]
5 onboarding_date str 0 0.0 23 [2025-01-30, 2024-10-27, 2025-01-02, 2024-06-2...
6 active_status str 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"))
display(category_review(customers_raw, "customer_zone"))
display(category_review(orders_raw, "promo_code"))
display(category_review(payments_raw, "payment_status"))
display(category_review(order_items_raw, "item_name"))
display(category_review(order_items_raw, "item_category"))
display(category_review(restaurants_raw, "restaurant_name"))
display(category_review(restaurants_raw, "cuisine_type"))
display(category_review(restaurants_raw, "restaurant_zone"))
display(category_review(restaurants_raw, "active_status"))
customer_segment count pct
0 Regular 73 0.4294
1 New 55 0.3235
2 Loyal 40 0.2353
3 REGULAR 2 0.0118
order_status count pct
0 completed 300 0.9036
1 cancelled 30 0.0904
2 COMPLETED 2 0.0060
payment_method count pct
0 credit_card 93 0.2605
1 bank_transfer 88 0.2465
2 cash 88 0.2465
3 e_wallet 86 0.2409
4 E WALLET 2 0.0056
price_tier count pct
0 Premium 10 0.4167
1 Budget 10 0.4167
2 Mid 2 0.0833
3 MID 1 0.0417
4 BUDGET 1 0.0417
customer_zone count pct
0 Central 44 0.2588
1 North 38 0.2235
2 West 31 0.1824
3 South 29 0.1706
4 East 28 0.1647
promo_code count pct
0 NaN 176 0.5301
1 SAVE10 56 0.1687
2 HEMAT15 51 0.1536
3 NEWUSER 49 0.1476
payment_status count pct
0 successful 312 0.8739
1 failed 35 0.0980
2 refunded 10 0.0280
item_name count pct
0 Chicken Set 111 0.1176
1 Rice Bowl 100 0.1059
2 Nasi Ayam 97 0.1028
3 Mie Special 88 0.0932
4 Milk Tea 79 0.0837
5 Coffee 75 0.0794
6 Juice 64 0.0678
7 Dimsum 62 0.0657
8 Iced Tea 59 0.0625
9 Toast 48 0.0508
10 Cake Slice 47 0.0498
11 Fries 46 0.0487
12 Pudding 34 0.0360
13 Ice Cream 34 0.0360
item_category count pct
0 Main 396 0.4195
1 Drink 277 0.2934
2 Snack 154 0.1631
3 Dessert 115 0.1218
4 SNACK 2 0.0021
restaurant_name count pct
0 SwiftBite Partner 01 1 0.0417
1 SwiftBite Partner 02 1 0.0417
2 SwiftBite Partner 03 1 0.0417
3 SwiftBite Partner 04 1 0.0417
4 SwiftBite Partner 05 1 0.0417
5 SwiftBite Partner 06 1 0.0417
6 SwiftBite Partner 07 1 0.0417
7 SwiftBite Partner 08 1 0.0417
8 SwiftBite Partner 09 1 0.0417
9 SwiftBite Partner 10 1 0.0417
10 SwiftBite Partner 11 1 0.0417
11 SwiftBite Partner 12 1 0.0417
12 SwiftBite Partner 13 1 0.0417
13 SwiftBite Partner 14 1 0.0417
14 SwiftBite Partner 15 1 0.0417
15 SwiftBite Partner 16 1 0.0417
16 SwiftBite Partner 17 1 0.0417
17 SwiftBite Partner 18 1 0.0417
18 SwiftBite Partner 19 1 0.0417
19 SwiftBite Partner 20 1 0.0417
20 SwiftBite Partner 21 1 0.0417
21 SwiftBite Partner 22 1 0.0417
22 SwiftBite Partner 23 1 0.0417
23 SwiftBite Partner 24 1 0.0417
cuisine_type count pct
0 Fast Food 4 0.1667
1 Cafe & Beverage 4 0.1667
2 Dessert 4 0.1667
3 Indonesian 4 0.1667
4 Asian 4 0.1667
5 Western 4 0.1667
restaurant_zone count pct
0 East 8 0.3333
1 South 6 0.2500
2 Central 5 0.2083
3 North 3 0.1250
4 West 2 0.0833
active_status count pct
0 active 22 0.9167
1 inactive 2 0.0833
customers_raw[
    customers_raw["customer_segment"].isin(["Regular", "REGULAR "])
][["customer_id", "full_name", "customer_segment"]]
customer_id full_name customer_segment
3 C0004 Jihan Kusuma Regular
4 C0005 Sinta Saputra Regular
8 C0009 Raka Permata REGULAR
10 C0011 Tio Permata Regular
11 C0012 Eka Kusuma Regular
12 C0013 Hana Maulana Regular
13 C0014 Kevin Nugroho Regular
16 C0017 Jihan Santoso Regular
17 C0018 Laras Hidayat Regular
20 C0021 Eka Pratama Regular
21 C0022 Bima Utami Regular
22 C0023 Ayu Sari Regular
23 C0024 Wahyu Hidayat Regular
24 C0025 Yani Hidayat Regular
25 C0026 Citra Kusuma Regular
28 C0029 Dimas Wijaya Regular
29 C0030 Ayu Hidayat Regular
31 C0032 Tio Wijaya Regular
35 C0036 Nadia Sari Regular
38 C0039 Farah Lestari Regular
39 C0040 Raka Santoso Regular
41 C0042 Dimas Lestari Regular
43 C0044 Oka Sari Regular
45 C0046 Laras Kusuma REGULAR
46 C0047 Ayu Wijaya Regular
47 C0048 Wahyu Sari Regular
48 C0049 Raka Permata Regular
52 C0053 Zaki Nugroho Regular
58 C0059 Vina Utami Regular
59 C0060 Farah Kusuma Regular
60 C0061 Bima Wijaya Regular
63 C0064 Hana Pratama Regular
64 C0065 Ayu Santoso Regular
65 C0066 Adi Permata Regular
68 C0069 Putri Maulana Regular
69 C0070 Maya Kusuma Regular
71 C0072 Laras Sari Regular
75 C0076 Kevin Sari Regular
76 C0077 Indra Pratama Regular
77 C0078 Hana Nugroho Regular
78 C0079 Dimas Permata Regular
79 C0080 Laras Nugroho Regular
80 C0081 Adi Utami Regular
81 C0082 Indra Santoso Regular
83 C0084 Oka Maulana Regular
86 C0087 Eka Santoso Regular
87 C0088 Adi Nugroho Regular
93 C0094 Raka Saputra Regular
96 C0097 Oka Maulana Regular
98 C0099 Adi Nugroho Regular
99 C0100 Wahyu Sari Regular
104 C0105 Vina Saputra Regular
105 C0106 Maya Pratama Regular
106 C0107 Kevin Maulana Regular
107 C0108 Indra Lestari Regular
109 C0110 Bima Utami Regular
114 C0115 Yani Kusuma Regular
116 C0117 Maya Utami Regular
118 C0119 Adi Hidayat Regular
120 C0121 Kevin Santoso Regular
124 C0125 Laras Lestari Regular
125 C0126 Galih Hidayat Regular
126 C0127 Nadia Santoso Regular
127 C0128 Putri Sari Regular
132 C0133 Raka Lestari Regular
137 C0138 Zaki Sari Regular
139 C0140 Raka Kusuma Regular
145 C0146 Kevin Hidayat Regular
151 C0152 Zaki Permata Regular
154 C0155 Kevin Saputra Regular
161 C0162 Bima Wijaya Regular
163 C0164 Citra Wijaya Regular
165 C0166 Galih Santoso Regular
167 C0168 Nadia Hidayat Regular
168 C0169 Adi Nugroho Regular
print(orders_raw.columns.tolist())
['order_id', 'customer_id', 'restaurant_id', 'order_timestamp', 'order_status', 'promo_code', 'subtotal_amount', 'delivery_fee', 'discount_amount', 'total_amount']
orders_raw[
    orders_raw["order_status"] == "COMPLETED "
][["order_id", "customer_id", "order_status"]]
order_id customer_id order_status
17 O00018 C0159 COMPLETED
56 O00057 C0094 COMPLETED
print(payments_raw.columns.tolist())
['payment_id', 'order_id', 'payment_method', 'payment_status', 'payment_amount', 'payment_timestamp', 'attempt_number']
payments_raw[
    payments_raw["payment_method"] == "E WALLET "
][["payment_id", "payment_method"]]
payment_id payment_method
35 P00041 E WALLET
79 P00089 E WALLET
print(restaurants_raw.columns.tolist())
['restaurant_id', 'restaurant_name', 'cuisine_type', 'restaurant_zone', 'price_tier', 'onboarding_date', 'active_status']
restaurants_raw[
    restaurants_raw["price_tier"] == "BUDGET "
][["restaurant_id", "price_tier"]]
restaurant_id price_tier
12 R0013 BUDGET

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))
count mean std min 25% 50% 75% max
subtotal_amount 332.0 154572.289157 130672.525643 16000.0 91000.0 135500.0 194000.0 2094000.0
delivery_fee 332.0 12765.060241 3541.584937 8000.0 10000.0 12000.0 15000.0 18000.0
discount_amount 332.0 5391.566265 5738.273577 0.0 0.0 5000.0 10000.0 15000.0
total_amount 332.0 161945.783133 130797.357440 23000.0 96750.0 146000.0 202250.0 2097000.0
count mean std min 25% 50% 75% max
line_number 944.0 2.083686 1.027918 1.0 1.0 2.0 3.0 5.0
unit_price 944.0 24965.042373 17914.011838 9000.0 18000.0 22000.0 28000.0 508000.0
line_discount 944.0 1506.355932 2028.942718 0.0 0.0 0.0 2000.0 5000.0
line_amount 944.0 54353.813559 71114.442040 4000.0 27750.0 48000.0 70000.0 2032000.0
# Order yang ingin diperiksa
target_order = "O00084"

# 1. Seluruh kolom order
print("=== ORDERS_RAW ===")
display(
    orders_raw[
        orders_raw["order_id"] == target_order
    ].T
)

# 2. Rincian seluruh item
print("=== ORDER_ITEMS_RAW ===")
display(
    order_items_raw[
        order_items_raw["order_id"] == target_order
    ]
)

# 3. Total rincian item
items = order_items_raw[
    order_items_raw["order_id"] == target_order
]

print("Total line_amount:", items["line_amount"].sum())

# 4. Data pembayaran
print("=== PAYMENTS_RAW ===")
display(
    payments_raw[
        payments_raw["order_id"] == target_order
    ]
)
=== ORDERS_RAW ===
83
order_id O00084
customer_id C0114
restaurant_id R0003
order_timestamp 2026-01-19 01:47:00
order_status completed
promo_code HEMAT15
subtotal_amount 2094000
delivery_fee 18000
discount_amount 15000
total_amount 2097000
=== ORDER_ITEMS_RAW ===
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
230 OI00231 O00084 1 Rice Bowl Main 2 32000 2000 62000
231 OI00232 O00084 2 Coffee Drink 4 508000 0 2032000
Total line_amount: 2094000
=== PAYMENTS_RAW ===
payment_id order_id payment_method payment_status payment_amount payment_timestamp attempt_number
86 P00096 O00084 cash successful 2097000 2026-01-19 01:50:00 1
coffee_check = order_items_raw[
    order_items_raw["item_name"].str.strip().str.lower() == "coffee"
]

display(
    coffee_check[
        ["order_id", "item_name", "quantity", "unit_price", "line_amount"]
    ].sort_values("unit_price", ascending=False).head(20)
)

print("Statistik harga satuan Coffee:")
display(coffee_check["unit_price"].describe())
order_id item_name quantity unit_price line_amount
231 O00084 Coffee 4 508000 2032000
15 O00006 Coffee 2 18000 31000
11 O00003 Coffee 2 18000 34000
83 O00032 Coffee 1 18000 16000
110 O00043 Coffee 3 18000 49000
124 O00047 Coffee 2 18000 36000
128 O00049 Coffee 2 18000 34000
134 O00051 Coffee 3 18000 54000
147 O00056 Coffee 2 18000 36000
187 O00068 Coffee 3 18000 52000
19 O00007 Coffee 2 18000 36000
198 O00072 Coffee 2 18000 31000
200 O00072 Coffee 2 18000 36000
208 O00076 Coffee 3 18000 54000
201 O00073 Coffee 3 18000 54000
229 O00083 Coffee 3 18000 49000
232 O00085 Coffee 3 18000 49000
240 O00088 Coffee 2 18000 36000
247 O00090 Coffee 1 18000 16000
312 O00114 Coffee 1 18000 18000
Statistik harga satuan Coffee:
count        75.000000
mean      24533.333333
std       56580.326381
min       18000.000000
25%       18000.000000
50%       18000.000000
75%       18000.000000
max      508000.000000
Name: unit_price, dtype: float64
# 1. Cek status 10 pesanan
target_orders = [f"O{i:05d}" for i in range(323, 333)]

print("=== ORDERS_RAW ===")
display(
    orders_raw[
        orders_raw["order_id"].isin(target_orders)
    ].sort_values("order_id")
)

# 2. Cek seluruh baris pembayaran untuk 10 pesanan
print("=== PAYMENTS_RAW ===")
payment_check = payments_raw[
    payments_raw["order_id"].isin(target_orders)
].sort_values(["order_id", "attempt_number"])

display(
    payment_check[
        [
            "payment_id",
            "order_id",
            "payment_method",
            "payment_status",
            "payment_amount",
            "payment_timestamp",
            "attempt_number"
        ]
    ]
)

# 3. Ringkasan jumlah baris pembayaran per pesanan dan statusnya
print("=== RINGKASAN PEMBAYARAN ===")
display(
    payment_check.groupby(
        ["order_id", "payment_status"]
    ).agg(
        jumlah_baris=("payment_id", "count"),
        total_nominal=("payment_amount", "sum")
    ).reset_index()
)
=== ORDERS_RAW ===
order_id customer_id restaurant_id order_timestamp order_status promo_code subtotal_amount delivery_fee discount_amount total_amount
322 O00323 C0097 R0022 2026-03-17 05:50:00 cancelled SAVE10 323000 10000 10000 323000
323 O00324 C0104 R0003 2026-03-03 12:46:00 cancelled NaN 288000 10000 5000 293000
324 O00325 C0111 R0008 2026-02-22 22:26:00 cancelled NEWUSER 49000 18000 15000 52000
325 O00326 C0118 R0013 2026-01-02 02:16:00 cancelled HEMAT15 212000 18000 5000 225000
326 O00327 C0125 R0018 2026-03-17 10:27:00 cancelled NaN 73000 12000 0 85000
327 O00328 C0132 R0023 2026-02-07 06:48:00 cancelled SAVE10 124000 18000 5000 137000
328 O00329 C0139 R0004 2026-03-27 19:28:00 cancelled NaN 110000 18000 0 128000
329 O00330 C0146 R0009 2026-03-10 03:21:00 cancelled NaN 109000 8000 0 117000
330 O00331 C0153 R0014 2026-02-23 15:34:00 cancelled NaN 107000 10000 0 117000
331 O00332 C0160 R0019 2026-02-11 21:51:00 cancelled HEMAT15 364000 18000 0 382000
=== PAYMENTS_RAW ===
payment_id order_id payment_method payment_status payment_amount payment_timestamp attempt_number
337 P00356 O00323 credit_card successful 323000 2026-03-17 05:52:00 1
338 P00357 O00323 credit_card refunded 323000 2026-03-17 06:15:00 2
339 P00358 O00324 cash successful 293000 2026-03-03 12:48:00 1
340 P00359 O00324 cash refunded 293000 2026-03-03 13:11:00 2
341 P00360 O00325 e_wallet successful 52000 2026-02-22 22:28:00 1
342 P00361 O00325 e_wallet refunded 52000 2026-02-22 22:51:00 2
343 P00362 O00326 bank_transfer successful 225000 2026-01-02 02:18:00 1
344 P00363 O00326 bank_transfer refunded 225000 2026-01-02 02:41:00 2
345 P00364 O00327 credit_card successful 85000 2026-03-17 10:29:00 1
346 P00365 O00327 credit_card refunded 85000 2026-03-17 10:52:00 2
347 P00366 O00328 cash successful 137000 2026-02-07 06:50:00 1
348 P00367 O00328 cash refunded 137000 2026-02-07 07:13:00 2
349 P00368 O00329 e_wallet successful 128000 2026-03-27 19:30:00 1
350 P00369 O00329 e_wallet refunded 128000 2026-03-27 19:53:00 2
351 P00370 O00330 bank_transfer successful 117000 2026-03-10 03:23:00 1
352 P00371 O00330 bank_transfer refunded 117000 2026-03-10 03:46:00 2
353 P00372 O00331 credit_card successful 117000 2026-02-23 15:36:00 1
354 P00373 O00331 credit_card refunded 117000 2026-02-23 15:59:00 2
355 P00374 O00332 cash successful 382000 2026-02-11 21:53:00 1
356 P00375 O00332 cash refunded 382000 2026-02-11 22:16:00 2
=== RINGKASAN PEMBAYARAN ===
order_id payment_status jumlah_baris total_nominal
0 O00323 refunded 1 323000
1 O00323 successful 1 323000
2 O00324 refunded 1 293000
3 O00324 successful 1 293000
4 O00325 refunded 1 52000
5 O00325 successful 1 52000
6 O00326 refunded 1 225000
7 O00326 successful 1 225000
8 O00327 refunded 1 85000
9 O00327 successful 1 85000
10 O00328 refunded 1 137000
11 O00328 successful 1 137000
12 O00329 refunded 1 128000
13 O00329 successful 1 128000
14 O00330 refunded 1 117000
15 O00330 successful 1 117000
16 O00331 refunded 1 117000
17 O00331 successful 1 117000
18 O00332 refunded 1 382000
19 O00332 successful 1 382000
# 1. Cek semua pembayaran yang statusnya refunded
refunded_check = payments_raw[
    payments_raw["payment_status"].str.lower().str.strip() == "refunded"
].sort_values(["order_id", "attempt_number"])

display(
    refunded_check[
        [
            "payment_id",
            "order_id",
            "payment_status",
            "payment_amount",
            "attempt_number"
        ]
    ]
)

# 2. Ringkasan: status pembayaran berdasarkan attempt
display(
    payments_raw.groupby(
        ["attempt_number", "payment_status"]
    ).size().reset_index(name="jumlah_baris")
)
payment_id order_id payment_status payment_amount attempt_number
338 P00357 O00323 refunded 323000 2
340 P00359 O00324 refunded 293000 2
342 P00361 O00325 refunded 52000 2
344 P00363 O00326 refunded 225000 2
346 P00365 O00327 refunded 85000 2
348 P00367 O00328 refunded 137000 2
350 P00369 O00329 refunded 128000 2
352 P00371 O00330 refunded 117000 2
354 P00373 O00331 refunded 117000 2
356 P00375 O00332 refunded 382000 2
attempt_number payment_status jumlah_baris
0 1 failed 35
1 1 successful 297
2 2 refunded 10
3 2 successful 15
attempt2_check = payments_raw[
    payments_raw["attempt_number"] == 2
].merge(
    orders_raw[["order_id", "order_status"]],
    on="order_id",
    how="left"
)

display(
    attempt2_check[
        [
            "order_id",
            "order_status",
            "payment_status",
            "payment_amount",
            "attempt_number"
        ]
    ].sort_values("order_id")
)
order_id order_status payment_status payment_amount attempt_number
0 O00005 completed successful 34000 2
1 O00014 completed successful 76000 2
2 O00033 completed successful 149000 2
3 O00100 completed successful 75000 2
4 O00110 completed successful 68000 2
5 O00111 completed successful 117000 2
6 O00131 completed successful 158000 2
7 O00156 completed successful 86000 2
8 O00207 completed successful 67000 2
9 O00237 completed successful 113000 2
10 O00250 completed successful 54000 2
11 O00255 completed successful 31000 2
12 O00267 completed successful 205000 2
13 O00283 completed successful 98000 2
14 O00299 completed successful 68000 2
15 O00323 cancelled refunded 323000 2
16 O00324 cancelled refunded 293000 2
17 O00325 cancelled refunded 52000 2
18 O00326 cancelled refunded 225000 2
19 O00327 cancelled refunded 85000 2
20 O00328 cancelled refunded 137000 2
21 O00329 cancelled refunded 128000 2
22 O00330 cancelled refunded 117000 2
23 O00331 cancelled refunded 117000 2
24 O00332 cancelled refunded 382000 2
# Cek total master restoran dan restoran inactive
print("Total master restoran:", len(restaurants_raw))

display(
    restaurants_raw[
        restaurants_raw["restaurant_id"].isin(["R0019", "R0022"])
    ]
)
Total master restoran: 24
restaurant_id restaurant_name cuisine_type restaurant_zone price_tier onboarding_date active_status
18 R0019 SwiftBite Partner 19 Fast Food South Premium 2025-01-18 inactive
21 R0022 SwiftBite Partner 22 Indonesian East Budget 2024-08-01 inactive
# Cek pesanan dari kedua restoran
display(
    orders_raw[
        orders_raw["restaurant_id"].isin(["R0019", "R0022"])
    ].groupby(
        ["restaurant_id", "order_status"]
    ).size().reset_index(name="jumlah_pesanan")
)
restaurant_id order_status jumlah_pesanan
0 R0019 cancelled 2
1 R0019 completed 12
2 R0022 cancelled 1
3 R0022 completed 13

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
import pandas as pd

findings_data = []
new_finding = {
    "finding_id": "F-01",
    "dataset": "customers_raw",
    "field_key": "customer_id",
    "finding_evidence": "Customer ID C0031 teridentifikasi sebagai exact duplicate.",
    "why_it_matters": "Duplikasi dapat menyebabkan pelanggan dihitung lebih dari satu kali.",
    "verification_method": "Memeriksa duplikasi customer_id dan membandingkan seluruh kolom pada baris duplikat.",
    "verification_evidence_result": "C0031 teridentifikasi sebagai exact duplicate.",
    "judgment": "FIX",
    "reason_for_judgment": "Duplikasi exact tidak menambah informasi baru dan berpotensi menggandakan perhitungan.",
    "cleaning_rule_id": "CR-01",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-02",
    "dataset": "customers_raw",
    "field_key": "customer_segment",
    "finding_evidence": "Terdapat variasi penulisan Regular dan REGULAR.",
    "why_it_matters": "Kategori yang sama dapat terhitung sebagai kategori berbeda.",
    "verification_method": "Memeriksa nilai unik customer_segment dan membandingkan kapitalisasi serta spasi.",
    "verification_evidence_result": "Regular dan REGULAR menunjukkan inkonsistensi format.",
    "judgment": "STANDARDIZE",
    "reason_for_judgment": "Format kategori perlu diseragamkan tanpa mengubah maknanya.",
    "cleaning_rule_id": "CR-02",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-03",
    "dataset": "orders_raw",
    "field_key": "order_status",
    "finding_evidence": "Terdapat variasi penulisan completed dan COMPLETED.",
    "why_it_matters": "Perbedaan kapitalisasi dapat menyebabkan agregasi status pesanan tidak akurat.",
    "verification_method": "Memeriksa nilai unik order_status.",
    "verification_evidence_result": "completed dan COMPLETED merupakan variasi format status yang sama.",
    "judgment": "STANDARDIZE",
    "reason_for_judgment": "Format status perlu diseragamkan untuk menjaga konsistensi analisis.",
    "cleaning_rule_id": "CR-03",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-04",
    "dataset": "payments_raw",
    "field_key": "payment_method",
    "finding_evidence": "Terdapat variasi penulisan e_wallet dan E WALLET.",
    "why_it_matters": "Kategori metode pembayaran dapat terpecah dalam laporan.",
    "verification_method": "Memeriksa nilai unik payment_method.",
    "verification_evidence_result": "e_wallet dan E WALLET menunjukkan inkonsistensi format.",
    "judgment": "STANDARDIZE",
    "reason_for_judgment": "Format kategori metode pembayaran perlu diseragamkan.",
    "cleaning_rule_id": "CR-04",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-05",
    "dataset": "restaurants_raw",
    "field_key": "price_tier",
    "finding_evidence": "Terdapat variasi penulisan Mid dan MID.",
    "why_it_matters": "Kategori tingkat harga yang sama dapat dihitung sebagai dua kategori berbeda.",
    "verification_method": "Memeriksa nilai unik price_tier.",
    "verification_evidence_result": "Mid dan MID berbeda dalam kapitalisasi.",
    "judgment": "STANDARDIZE",
    "reason_for_judgment": "Penulisan kategori perlu diseragamkan.",
    "cleaning_rule_id": "CR-05",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-06",
    "dataset": "restaurants_raw",
    "field_key": "price_tier",
    "finding_evidence": "Terdapat variasi penulisan Budget dan BUDGET.",
    "why_it_matters": "Kategori tingkat harga yang sama dapat terhitung sebagai kategori berbeda.",
    "verification_method": "Memeriksa nilai unik price_tier.",
    "verification_evidence_result": "Budget dan BUDGET berbeda dalam kapitalisasi.",
    "judgment": "STANDARDIZE",
    "reason_for_judgment": "Penulisan kategori perlu diseragamkan.",
    "cleaning_rule_id": "CR-06",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-07",
    "dataset": "order_items_raw",
    "field_key": "item_category",
    "finding_evidence": "Terdapat variasi penulisan Snack dan SNACK.",
    "why_it_matters": "Kategori item dapat terhitung sebagai dua kategori berbeda.",
    "verification_method": "Memeriksa nilai unik item_category.",
    "verification_evidence_result": "Snack dan SNACK berbeda dalam kapitalisasi.",
    "judgment": "STANDARDIZE",
    "reason_for_judgment": "Penulisan kategori item perlu diseragamkan.",
    "cleaning_rule_id": "CR-07",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-08",
    "dataset": "orders_raw",
    "field_key": "promo_code",
    "finding_evidence": "Sebanyak 176 baris memiliki promo_code kosong.",
    "why_it_matters": "Nilai kosong perlu dibedakan antara data hilang dan pesanan yang memang tidak menggunakan promo.",
    "verification_method": "Menghitung nilai kosong promo_code dan memeriksa apakah penggunaan promo bersifat opsional.",
    "verification_evidence_result": "Ketiadaan kode promo dapat merepresentasikan pesanan tanpa promo.",
    "judgment": "KEEP",
    "reason_for_judgment": "Nilai kosong dapat valid jika pesanan tidak menggunakan promo.",
    "cleaning_rule_id": "CR-08",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-09",
    "dataset": "orders_raw",
    "field_key": "subtotal_amount, total_amount",
    "finding_evidence": "Rekonsiliasi nominal menemukan ketidaksesuaian pada order O00049, O00182, dan O00201.",
    "why_it_matters": "Ketidaksesuaian nominal dapat memengaruhi akurasi laporan transaksi.",
    "verification_method": "Membandingkan subtotal dengan jumlah line_amount dan memeriksa perhitungan total.",
    "verification_evidence_result": "Ditemukan mismatch pada O00049, O00182, dan O00201. Subtotal O00201 yang diharapkan Rp125.000 dan total Rp138.000.",
    "judgment": "FIX",
    "reason_for_judgment": "Nilai yang tidak sesuai perlu dikoreksi berdasarkan hasil rekonsiliasi terverifikasi.",
    "cleaning_rule_id": "CR-09",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-10",
    "dataset": "order_items_raw",
    "field_key": "line_amount",
    "finding_evidence": "OI00131 pada order O00049 untuk Iced Tea tercatat Rp34.000, sedangkan nilai yang diharapkan Rp27.000.",
    "why_it_matters": "Kesalahan line_amount dapat menyebabkan subtotal pesanan tidak akurat.",
    "verification_method": "Menghitung ulang line_amount berdasarkan quantity, unit_price, dan diskon yang berlaku.",
    "verification_evidence_result": "OI00131 memiliki line_amount tercatat Rp34.000, sedangkan hasil perhitungan yang diharapkan Rp27.000.",
    "judgment": "FIX",
    "reason_for_judgment": "Nilai perlu dikoreksi setelah formula dan bukti transaksi diverifikasi.",
    "cleaning_rule_id": "CR-10",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-11",
    "dataset": "order_items_raw",
    "field_key": "line_amount",
    "finding_evidence": "OI00511 pada order O00182 untuk Coffee tercatat Rp31.000, sedangkan nilai yang diharapkan Rp36.000.",
    "why_it_matters": "Kesalahan line_amount dapat menyebabkan subtotal pesanan tidak akurat.",
    "verification_method": "Menghitung ulang line_amount berdasarkan quantity, unit_price, dan diskon yang berlaku.",
    "verification_evidence_result": "OI00511 memiliki line_amount tercatat Rp31.000, sedangkan hasil perhitungan yang diharapkan Rp36.000.",
    "judgment": "FIX",
    "reason_for_judgment": "Nilai perlu dikoreksi setelah formula dan bukti transaksi diverifikasi.",
    "cleaning_rule_id": "CR-11",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-12",
    "dataset": "order_items_raw",
    "field_key": "quantity",
    "finding_evidence": "OI00071 dan OI00215 memiliki quantity berbentuk teks, yaitu '2 pcs' dan '1 pcs'.",
    "why_it_matters": "Nilai quantity berbentuk teks dapat menghambat perhitungan numerik.",
    "verification_method": "Memeriksa tipe data quantity dan mengidentifikasi nilai yang mengandung satuan teks.",
    "verification_evidence_result": "OI00071 dan OI00215 mengandung satuan pcs yang perlu dipisahkan dari nilai numerik.",
    "judgment": "STANDARDIZE",
    "reason_for_judgment": "Quantity perlu dikonversi ke format numerik.",
    "cleaning_rule_id": "CR-12",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-13",
    "dataset": "order_items_raw, orders_raw, payments_raw",
    "field_key": "unit_price, quantity, line_amount, subtotal_amount, total_amount",
    "finding_evidence": "Order O00084 memiliki item Coffee dengan unit_price Rp508.000 dan quantity 4, sehingga line_amount Rp2.032.000. Harga Coffee yang umum ditemukan sekitar Rp18.000.",
    "why_it_matters": "Nilai ekstrem dapat berasal dari kesalahan input atau transaksi khusus dan memengaruhi analisis harga.",
    "verification_method": "Membandingkan unit_price dengan harga Coffee lainnya serta memeriksa rekonsiliasi nominal dan pembayaran.",
    "verification_evidence_result": "Subtotal Rp2.094.000, biaya pengiriman Rp18.000, diskon Rp15.000, dan total Rp2.097.000 konsisten secara aritmetika; pembayaran juga cocok.",
    "judgment": "INVESTIGATE",
    "reason_for_judgment": "Nilai ekstrem belum tentu salah karena nominal transaksi konsisten. Perlu verifikasi sumber transaksi sebelum koreksi.",
    "cleaning_rule_id": "CR-13",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-14",
    "dataset": "payments_raw, orders_raw",
    "field_key": "order_id, payment_attempt, payment_status, order_status",
    "finding_evidence": "Sepuluh order cancelled O00323–O00332 memiliki payment attempt pertama successful dan attempt kedua refunded dengan nominal sama. Dari 25 baris attempt kedua, 15 successful terkait order completed dan 10 refunded terkait order cancelled.",
    "why_it_matters": "Riwayat pembayaran berulang dapat disalahartikan sebagai duplikasi jika konteks payment attempt diabaikan.",
    "verification_method": "Menghubungkan payments_raw dengan orders_raw dan membandingkan payment attempt, status pembayaran, status pesanan, serta nominal.",
    "verification_evidence_result": "Status attempt kedua selaras dengan status order: successful pada order completed dan refunded pada order cancelled.",
    "judgment": "KEEP",
    "reason_for_judgment": "Baris tersebut dapat merepresentasikan riwayat pembayaran dan refund yang valid, bukan duplikasi yang perlu dihapus.",
    "cleaning_rule_id": "CR-14",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
    "finding_id": "F-15",
    "dataset": "restaurants_raw, orders_raw",
    "field_key": "restaurant_id, active_status, order_timestamp, order_status",
    "finding_evidence": "Dua dari 24 restoran berstatus inactive, yaitu R0019 dan R0022. R0019 memiliki 12 order completed dan 2 cancelled, sedangkan R0022 memiliki 13 order completed dan 1 cancelled.",
    "why_it_matters": "Restoran inactive yang memiliki riwayat pesanan perlu diperiksa agar status operasional tidak disalahartikan.",
    "verification_method": "Menghubungkan restaurants_raw dengan orders_raw berdasarkan restaurant_id dan memeriksa status pesanan serta ketersediaan tanggal perubahan status.",
    "verification_evidence_result": "R0019 dan R0022 memiliki riwayat pesanan, tetapi tidak tersedia timestamp perubahan status untuk memastikan kapan restoran dinonaktifkan.",
    "judgment": "INVESTIGATE",
    "reason_for_judgment": "Pesanan mungkin terjadi sebelum restoran dinonaktifkan sehingga perlu konfirmasi sebelum menyimpulkan adanya kesalahan.",
    "cleaning_rule_id": "CR-15",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
display(findings)
finding_id dataset field_key finding_evidence why_it_matters verification_method verification_evidence_result judgment reason_for_judgment cleaning_rule_id
0 F-01 customers_raw customer_id Customer ID C0031 teridentifikasi sebagai exac... Duplikasi dapat menyebabkan pelanggan dihitung... Memeriksa duplikasi customer_id dan membanding... C0031 teridentifikasi sebagai exact duplicate. FIX Duplikasi exact tidak menambah informasi baru ... CR-01
1 F-02 customers_raw customer_segment Terdapat variasi penulisan Regular dan REGULAR. Kategori yang sama dapat terhitung sebagai kat... Memeriksa nilai unik customer_segment dan memb... Regular dan REGULAR menunjukkan inkonsistensi ... STANDARDIZE Format kategori perlu diseragamkan tanpa mengu... CR-02
2 F-03 orders_raw order_status Terdapat variasi penulisan completed dan COMPL... Perbedaan kapitalisasi dapat menyebabkan agreg... Memeriksa nilai unik order_status. completed dan COMPLETED merupakan variasi form... STANDARDIZE Format status perlu diseragamkan untuk menjaga... CR-03
3 F-04 payments_raw payment_method Terdapat variasi penulisan e_wallet dan E WALLET. Kategori metode pembayaran dapat terpecah dala... Memeriksa nilai unik payment_method. e_wallet dan E WALLET menunjukkan inkonsistens... STANDARDIZE Format kategori metode pembayaran perlu disera... CR-04
4 F-05 restaurants_raw price_tier Terdapat variasi penulisan Mid dan MID. Kategori tingkat harga yang sama dapat dihitun... Memeriksa nilai unik price_tier. Mid dan MID berbeda dalam kapitalisasi. STANDARDIZE Penulisan kategori perlu diseragamkan. CR-05
5 F-06 restaurants_raw price_tier Terdapat variasi penulisan Budget dan BUDGET. Kategori tingkat harga yang sama dapat terhitu... Memeriksa nilai unik price_tier. Budget dan BUDGET berbeda dalam kapitalisasi. STANDARDIZE Penulisan kategori perlu diseragamkan. CR-06
6 F-07 order_items_raw item_category Terdapat variasi penulisan Snack dan SNACK. Kategori item dapat terhitung sebagai dua kate... Memeriksa nilai unik item_category. Snack dan SNACK berbeda dalam kapitalisasi. STANDARDIZE Penulisan kategori item perlu diseragamkan. CR-07
7 F-08 orders_raw promo_code Sebanyak 176 baris memiliki promo_code kosong. Nilai kosong perlu dibedakan antara data hilan... Menghitung nilai kosong promo_code dan memerik... Ketiadaan kode promo dapat merepresentasikan p... KEEP Nilai kosong dapat valid jika pesanan tidak me... CR-08
8 F-09 orders_raw subtotal_amount, total_amount Rekonsiliasi nominal menemukan ketidaksesuaian... Ketidaksesuaian nominal dapat memengaruhi akur... Membandingkan subtotal dengan jumlah line_amou... Ditemukan mismatch pada O00049, O00182, dan O0... FIX Nilai yang tidak sesuai perlu dikoreksi berdas... CR-09
9 F-10 order_items_raw line_amount OI00131 pada order O00049 untuk Iced Tea terca... Kesalahan line_amount dapat menyebabkan subtot... Menghitung ulang line_amount berdasarkan quant... OI00131 memiliki line_amount tercatat Rp34.000... FIX Nilai perlu dikoreksi setelah formula dan bukt... CR-10
10 F-11 order_items_raw line_amount OI00511 pada order O00182 untuk Coffee tercata... Kesalahan line_amount dapat menyebabkan subtot... Menghitung ulang line_amount berdasarkan quant... OI00511 memiliki line_amount tercatat Rp31.000... FIX Nilai perlu dikoreksi setelah formula dan bukt... CR-11
11 F-12 order_items_raw quantity OI00071 dan OI00215 memiliki quantity berbentu... Nilai quantity berbentuk teks dapat menghambat... Memeriksa tipe data quantity dan mengidentifik... OI00071 dan OI00215 mengandung satuan pcs yang... STANDARDIZE Quantity perlu dikonversi ke format numerik. CR-12
12 F-13 order_items_raw, orders_raw, payments_raw unit_price, quantity, line_amount, subtotal_am... Order O00084 memiliki item Coffee dengan unit_... Nilai ekstrem dapat berasal dari kesalahan inp... Membandingkan unit_price dengan harga Coffee l... Subtotal Rp2.094.000, biaya pengiriman Rp18.00... INVESTIGATE Nilai ekstrem belum tentu salah karena nominal... CR-13
13 F-14 payments_raw, orders_raw order_id, payment_attempt, payment_status, ord... Sepuluh order cancelled O00323–O00332 memiliki... Riwayat pembayaran berulang dapat disalahartik... Menghubungkan payments_raw dengan orders_raw d... Status attempt kedua selaras dengan status ord... KEEP Baris tersebut dapat merepresentasikan riwayat... CR-14
14 F-15 restaurants_raw, orders_raw restaurant_id, active_status, order_timestamp,... Dua dari 24 restoran berstatus inactive, yaitu... Restoran inactive yang memiliki riwayat pesana... Menghubungkan restaurants_raw dengan orders_ra... R0019 dan R0022 memiliki riwayat pesanan, teta... INVESTIGATE Pesanan mungkin terjadi sebelum restoran dinon... CR-15

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 169 0 []
1 Orders → Restaurants 24 0 []
2 Order Items → Orders 332 0 []
3 Payments → Orders 332 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 4 24000 0 96000 96000.0 True
1 OI00002 O00001 2 22000 0 44000 44000.0 True
2 OI00003 O00001 4 42000 5000 163000 163000.0 True
3 OI00004 O00001 1 22000 0 22000 22000.0 True
4 OI00005 O00001 2 22000 2000 42000 42000.0 True
order_id subtotal_amount delivery_fee discount_amount total_amount expected_total_amount order_formula_match
0 O00001 367000 12000 0 379000 379000 True
1 O00002 163000 15000 0 178000 178000 True
2 O00003 240000 10000 10000 240000 240000 True
3 O00004 68000 10000 5000 73000 73000 True
4 O00005 24000 10000 0 34000 34000 True
# Tampilkan Order Items yang formula-nya mismatch (False)

item_mismatch = item_verify[
    item_verify["line_formula_match"] == False
]

print("Total baris yang diperiksa:", len(item_verify))
print("Jumlah formula mismatch:", len(item_mismatch))

display(
    item_mismatch[
        [
            "order_item_id",
            "order_id",
            "quantity",
            "unit_price",
            "line_discount",
            "line_amount",
            "expected_line_amount",
            "line_formula_match"
        ]
    ]
)
Total baris yang diperiksa: 944
Jumlah formula mismatch: 4
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount line_formula_match
70 OI00071 O00027 2 pcs 9000 2000 16000 NaN False
130 OI00131 O00049 3 9000 0 34000 27000.0 False
214 OI00215 O00078 1 pcs 32000 5000 27000 NaN False
510 OI00511 O00182 2 18000 0 31000 36000.0 False
# 1. Lihat semua variasi nilai quantity dan frekuensinya
print("Ragam nilai quantity:")
display(
    order_items_raw["quantity"]
    .value_counts(dropna=False)
    .rename_axis("quantity")
    .reset_index(name="jumlah")
)
Ragam nilai quantity:
quantity jumlah
0 3 331
1 2 309
2 1 248
3 4 54
4 2 pcs 1
5 1 pcs 1
# 1. Periksa seluruh item dari order O00201
order_id_target = "O00201"

display(
    item_verify[
        item_verify["order_id"].astype("string").str.strip()
        == order_id_target
    ][[
        "order_item_id",
        "order_id",
        "quantity",
        "unit_price",
        "line_discount",
        "line_amount",
        "expected_line_amount",
        "line_formula_match"
    ]]
)

# 2. Periksa data order O00201
display(
    order_verify[
        order_verify["order_id"].astype("string").str.strip()
        == order_id_target
    ][[
        "order_id",
        "subtotal_amount",
        "delivery_fee",
        "discount_amount",
        "total_amount",
        "expected_total_amount",
        "order_formula_match"
    ]]
)
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount line_formula_match
564 OI00565 O00201 3 24000 0 72000 72000.0 True
565 OI00566 O00201 1 26000 5000 21000 21000.0 True
566 OI00567 O00201 1 32000 0 32000 32000.0 True
order_id subtotal_amount delivery_fee discount_amount total_amount expected_total_amount order_formula_match
200 O00201 135000 18000 5000 148000 148000 True
# Hitung subtotal berdasarkan seluruh item
item_subtotal = (
    item_verify.groupby("order_id")["line_amount"]
    .sum()
    .rename("calculated_subtotal")
)

# Gabungkan dengan data order
subtotal_check = order_verify.merge(
    item_subtotal,
    on="order_id",
    how="left"
)

# Bandingkan subtotal aktual dengan subtotal hasil agregasi
subtotal_check["subtotal_match"] = (
    pd.to_numeric(
        subtotal_check["subtotal_amount"], errors="coerce"
    ).round(2)
    ==
    pd.to_numeric(
        subtotal_check["calculated_subtotal"], errors="coerce"
    ).round(2)
)

# Periksa order O00201
display(
    subtotal_check[
        subtotal_check["order_id"] == "O00201"
    ][[
        "order_id",
        "subtotal_amount",
        "calculated_subtotal",
        "subtotal_match"
    ]]
)
order_id subtotal_amount calculated_subtotal subtotal_match
200 O00201 135000 125000 False
target_order = "O00201"

# Tampilkan semua item milik order tersebut
target_items = item_verify[
    item_verify["order_id"].astype("string").str.strip()
    == target_order
]

display(target_items[[
    "order_item_id",
    "quantity",
    "unit_price",
    "line_discount",
    "line_amount",
    "expected_line_amount",
    "line_formula_match"
]])

print("Jumlah baris item:", len(target_items))
print(
    "Total line_amount:",
    pd.to_numeric(target_items["line_amount"], errors="coerce").sum()
)
order_item_id quantity unit_price line_discount line_amount expected_line_amount line_formula_match
564 OI00565 3 24000 0 72000 72000.0 True
565 OI00566 1 26000 5000 21000 21000.0 True
566 OI00567 1 32000 0 32000 32000.0 True
Jumlah baris item: 3
Total line_amount: 125000
# Cek baris mentah order O00201
display(
    orders_raw[
        orders_raw["order_id"].astype("string").str.strip()
        == "O00201"
    ][[
        "order_id",
        "subtotal_amount",
        "delivery_fee",
        "discount_amount",
        "total_amount"
    ]]
)

# Cek jumlah item dan subtotal dari data mentah
target_items_raw = order_items_raw[
    order_items_raw["order_id"].astype("string").str.strip()
    == "O00201"
]

print("Jumlah item:", len(target_items_raw))
print(
    "Total line_amount mentah:",
    pd.to_numeric(
        target_items_raw["line_amount"], errors="coerce"
    ).sum()
)

display(target_items_raw)
order_id subtotal_amount delivery_fee discount_amount total_amount
200 O00201 135000 18000 5000 148000
Jumlah item: 3
Total line_amount mentah: 125000
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
564 OI00565 O00201 1 Cake Slice Dessert 3 24000 0 72000
565 OI00566 O00201 2 Mie Special Main 1 26000 5000 21000
566 OI00567 O00201 3 Rice Bowl Main 1 32000 0 32000
if DATA_READY:

    # Hitung subtotal dari seluruh item per order
    item_subtotal = (
        item_verify.groupby("order_id")["line_amount"]
        .sum(min_count=1)
        .rename("calculated_subtotal")
    )

    # Gabungkan hasil agregasi ke data order
    order_verify = order_verify.merge(
        item_subtotal,
        on="order_id",
        how="left",
        validate="one_to_one"
    )

    # Validasi subtotal dengan total item
    order_verify["subtotal_match"] = (
        pd.to_numeric(
            order_verify["subtotal_amount"], errors="coerce"
        ).round(2)
        ==
        pd.to_numeric(
            order_verify["calculated_subtotal"], errors="coerce"
        ).round(2)
    )

    # Tampilkan order dengan subtotal tidak cocok
    subtotal_mismatch = order_verify[
        order_verify["subtotal_match"] == False
    ]

    display(
        subtotal_mismatch[[
            "order_id",
            "subtotal_amount",
            "calculated_subtotal",
            "subtotal_match"
        ]]
    )
order_id subtotal_amount calculated_subtotal subtotal_match
48 O00049 187000 194000 False
181 O00182 222000 217000 False
200 O00201 135000 125000 False
display(
    payments_raw[
        payments_raw["order_id"] == "O00201"
    ].T
)
208
payment_id P00226
order_id O00201
payment_method e_wallet
payment_status successful
payment_amount 138000
payment_timestamp 2026-03-11 02:19:00
attempt_number 1
display(
    orders_raw[
        orders_raw["order_id"] == "O00201"
    ].T
)
200
order_id O00201
customer_id C0088
restaurant_id R0012
order_timestamp 2026-03-11 02:16:00
order_status completed
promo_code NaN
subtotal_amount 135000
delivery_fee 18000
discount_amount 5000
total_amount 148000
display(
    order_items_raw[
        order_items_raw["order_id"] == "O00201"
    ]
)
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
564 OI00565 O00201 1 Cake Slice Dessert 3 24000 0 72000
565 OI00566 O00201 2 Mie Special Main 1 26000 5000 21000
566 OI00567 O00201 3 Rice Bowl Main 1 32000 0 32000
target_orders = ["O00049", "O00182", "O00201"]

display(
    item_verify[
        item_verify["order_id"].astype("string").str.strip()
        .isin(target_orders)
    ][[
        "order_item_id",
        "order_id",
        "line_number",
        "item_name",
        "quantity",
        "unit_price",
        "line_discount",
        "line_amount",
        "expected_line_amount",
        "line_formula_match"
    ]].sort_values(["order_id", "line_number"])
)
order_item_id order_id line_number item_name quantity unit_price line_discount line_amount expected_line_amount line_formula_match
128 OI00129 O00049 1 Coffee 2 18000 2000 34000 34000.0 True
129 OI00130 O00049 2 Chicken Set 3 42000 0 126000 126000.0 True
130 OI00131 O00049 3 Iced Tea 3 9000 0 34000 27000.0 False
507 OI00508 O00182 1 Chicken Set 2 42000 5000 79000 79000.0 True
508 OI00509 O00182 2 Cake Slice 1 24000 5000 19000 19000.0 True
509 OI00510 O00182 3 Coffee 2 18000 2000 34000 34000.0 True
510 OI00511 O00182 4 Coffee 2 18000 0 31000 36000.0 False
511 OI00512 O00182 5 Nasi Ayam 2 28000 2000 54000 54000.0 True
564 OI00565 O00201 1 Cake Slice 3 24000 0 72000 72000.0 True
565 OI00566 O00201 2 Mie Special 1 26000 5000 21000 21000.0 True
566 OI00567 O00201 3 Rice Bowl 1 32000 0 32000 32000.0 True
# 1. Formula item yang mismatch
item_mismatch = item_verify[
    item_verify["line_formula_match"] == False
]

# 2. Formula total order yang mismatch
order_formula_mismatch = order_verify[
    order_verify["order_formula_match"] == False
]

# 3. Subtotal yang tidak cocok dengan jumlah line_amount
subtotal_mismatch = order_verify[
    order_verify["subtotal_match"] == False
]

print("=== RINGKASAN VALIDASI ===")
print("Item formula mismatch:", len(item_mismatch))
print("Order formula mismatch:", len(order_formula_mismatch))
print("Subtotal mismatch:", len(subtotal_mismatch))

print("\n=== ITEM BERMASALAH ===")
display(
    item_mismatch[
        [
            "order_item_id",
            "order_id",
            "line_amount",
            "expected_line_amount"
        ]
    ]
)

print("\n=== SUBTOTAL BERMASALAH ===")
display(
    subtotal_mismatch[
        [
            "order_id",
            "subtotal_amount",
            "calculated_subtotal",
            "subtotal_match"
        ]
    ]
)
=== RINGKASAN VALIDASI ===
Item formula mismatch: 4
Order formula mismatch: 0
Subtotal mismatch: 3

=== ITEM BERMASALAH ===
order_item_id order_id line_amount expected_line_amount
70 OI00071 O00027 16000 NaN
130 OI00131 O00049 34000 27000.0
214 OI00215 O00078 27000 NaN
510 OI00511 O00182 31000 36000.0
=== SUBTOTAL BERMASALAH ===
order_id subtotal_amount calculated_subtotal subtotal_match
48 O00049 187000 194000 False
181 O00182 222000 217000 False
200 O00201 135000 125000 False

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 367000 367000 367000 True
1 O00002 163000 163000 163000 True
2 O00003 240000 240000 240000 True
3 O00004 68000 68000 68000 True
4 O00005 24000 24000 24000 True
Raw reconciliation mismatch count: 3
# Tampilkan order dengan subtotal yang tidak sesuai
raw_mismatch = order_item_recon_raw[
    order_item_recon_raw["reconcile_match"] == False
].copy()

raw_mismatch["difference"] = (
    raw_mismatch["subtotal_numeric"]
    - raw_mismatch["sum_line_amount"]
)

display(raw_mismatch)
order_id subtotal_amount sum_line_amount subtotal_numeric reconcile_match difference
48 O00049 187000 194000 187000 False -7000
181 O00182 222000 217000 222000 False 5000
200 O00201 135000 125000 135000 False 10000
# Order yang akan diinvestigasi
target_orders = ["O00049", "O00182"]

# 1. Ringkasan order
print("=== ORDERS_RAW ===")
display(
    orders_raw[
        orders_raw["order_id"].isin(target_orders)
    ].T
)

# 2. Rincian item
print("=== ORDER_ITEMS_RAW ===")
display(
    order_items_raw[
        order_items_raw["order_id"].isin(target_orders)
    ]
)

# 3. Pembayaran
print("=== PAYMENTS_RAW ===")
display(
    payments_raw[
        payments_raw["order_id"].isin(target_orders)
    ]
)
=== ORDERS_RAW ===
48 181
order_id O00049 O00182
customer_id C0038 C0124
restaurant_id R0020 R0013
order_timestamp 2026-03-14 09:50:00 2026-01-17 13:54:00
order_status completed completed
promo_code NaN NEWUSER
subtotal_amount 187000 222000
delivery_fee 8000 12000
discount_amount 5000 15000
total_amount 190000 219000
=== ORDER_ITEMS_RAW ===
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
128 OI00129 O00049 1 Coffee Drink 2 18000 2000 34000
129 OI00130 O00049 2 Chicken Set Main 3 42000 0 126000
130 OI00131 O00049 3 Iced Tea Drink 3 9000 0 34000
507 OI00508 O00182 1 Chicken Set Main 2 42000 5000 79000
508 OI00509 O00182 2 Cake Slice Dessert 1 24000 5000 19000
509 OI00510 O00182 3 Coffee Drink 2 18000 2000 34000
510 OI00511 O00182 4 Coffee Drink 2 18000 0 31000
511 OI00512 O00182 5 Nasi Ayam Main 2 28000 2000 54000
=== PAYMENTS_RAW ===
payment_id order_id payment_method payment_status payment_amount payment_timestamp attempt_number
51 P00058 O00049 e_wallet successful 190000 2026-03-14 09:53:00 1
189 P00204 O00182 bank_transfer successful 219000 2026-01-17 13:57:00 1

10. Cleaning Rulebook

Rulebook dibuat setelah verification dan sebelum coding.

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

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 duplikat dan seluruh atribut pada baris identik; C0031 muncul pada row 30 dan 169.",
        "decision": "FIX",
        "cleaning_action": "Hapus satu baris dari pasangan exact duplicate.",
        "reason_evidence": "Dua baris memiliki customer_id dan seluruh atribut identik; jumlah baris 170 sedangkan jumlah unique key 169.",
        "implementation_note": "Pertahankan satu record C0031 dan dokumentasikan baris yang dihapus.",
        "validation_criteria": "Jumlah baris menjadi 169; customer_id unik; tidak ada exact duplicate yang tersisa.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-02",
        "source_finding_id": "F-02",
        "dataset": "Customers",
        "trigger_condition": "customer_segment memiliki variasi Regular, REGULAR, atau REGULAR karena kapitalisasi dan spasi.",
        "decision": "STANDARDIZE",
        "cleaning_action": "Hapus spasi di awal/akhir dan ubah label menjadi Title Case.",
        "reason_evidence": "Label menunjukkan kategori yang sama, tetapi penulisannya tidak konsisten.",
        "implementation_note": "Terapkan str.strip().str.title() pada kolom customer_segment.",
        "validation_criteria": "Hanya satu label standar Regular untuk kategori tersebut; jumlah record tetap.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-03",
        "source_finding_id": "F-03",
        "dataset": "Orders",
        "trigger_condition": "order_status memiliki variasi completed dan COMPLETED.",
        "decision": "STANDARDIZE",
        "cleaning_action": "Hapus spasi ekstra dan seragamkan kapitalisasi menjadi Completed.",
        "reason_evidence": "Perbedaan kapitalisasi menyebabkan kategori yang sama terhitung sebagai label berbeda.",
        "implementation_note": "Terapkan trimming dan Title Case pada order_status; pastikan label lain tetap valid.",
        "validation_criteria": "Tidak ada variasi kapitalisasi untuk status yang sama; jumlah record tetap.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-04",
        "source_finding_id": "F-04",
        "dataset": "Payments",
        "trigger_condition": "payment_method memiliki variasi e_wallet, E WALLET, dan E WALLET .",
        "decision": "STANDARDIZE",
        "cleaning_action": "Hapus spasi ekstra, samakan kapitalisasi, dan petakan E WALLET menjadi E_WALLET.",
        "reason_evidence": "Perbedaan spasi, kapitalisasi, dan pemisah menyebabkan inkonsistensi kategori.",
        "implementation_note": "Gunakan pemetaan label eksplisit ke e_wallet atau label kanonis yang disepakati.",
        "validation_criteria": "Seluruh variasi yang telah dikonfirmasi ekuivalen menggunakan satu label kanonis; jumlah record tetap.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-05",
        "source_finding_id": "F-05",
        "dataset": "Restaurants",
        "trigger_condition": "price_tier memiliki variasi Mid dan MID.",
        "decision": "STANDARDIZE",
        "cleaning_action": "Hapus spasi ekstra dan ubah menjadi Mid.",
        "reason_evidence": "Variasi kapitalisasi mengakibatkan kategori harga terpecah saat agregasi.",
        "implementation_note": "Terapkan trimming dan Title Case pada price_tier.",
        "validation_criteria": "Label Mid konsisten dan jumlah record tetap.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-06",
        "source_finding_id": "F-06",
        "dataset": "Restaurants",
        "trigger_condition": "price_tier memiliki variasi Budget dan BUDGET.",
        "decision": "STANDARDIZE",
        "cleaning_action": "Hapus spasi ekstra dan ubah menjadi Budget.",
        "reason_evidence": "Variasi kapitalisasi mengakibatkan kategori harga terpecah saat agregasi.",
        "implementation_note": "Terapkan trimming dan Title Case pada price_tier.",
        "validation_criteria": "Label Budget konsisten dan jumlah record tetap.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-07",
        "source_finding_id": "F-07",
        "dataset": "Order Items",
        "trigger_condition": "item_category memiliki variasi Snack dan SNACK .",
        "decision": "STANDARDIZE",
        "cleaning_action": "Hapus spasi ekstra dan ubah menjadi Snack.",
        "reason_evidence": "Record OI00091 dan OI00331 memiliki label kategori yang berbeda secara format, tetapi bermakna sama.",
        "implementation_note": "Terapkan trimming dan Title Case pada item_category.",
        "validation_criteria": "Tidak ada variasi kapitalisasi/spasi untuk kategori Snack; jumlah record tetap.",
        "rule_status": "Draft"
    },

    {
        "rule_id": "CR-08",
        "source_finding_id": "F-09",
        "dataset": "Orders",
        "trigger_condition": "Rekonsiliasi transaksi menunjukkan mismatch pada O00049, O00182, dan O00201; O00201 terbukti mengalami kesalahan pencatatan.",
        "decision": "FIX",
        "cleaning_action": "Koreksi subtotal_amount menjadi Rp125.000 dan total_amount menjadi Rp138.000 untuk O00201.",
        "reason_evidence": "Total item Rp125.000 ditambah ongkir Rp18.000 dikurangi diskon Rp5.000 menghasilkan Rp138.000, sesuai pembayaran berhasil P00226.",
        "implementation_note": "Terapkan koreksi hanya pada record yang didukung bukti rekonsiliasi; periksa dua order lainnya secara terpisah.",
        "validation_criteria": "Untuk O00201, subtotal sama dengan jumlah item dan total sama dengan perhitungan subtotal + ongkir - diskon serta pembayaran berhasil.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-09",
        "source_finding_id": "F-10",
        "dataset": "Order Items",
        "trigger_condition": "line_amount tidak sesuai formula quantity × unit_price - line_discount, khususnya OI00131 pada O00049.",
        "decision": "FIX",
        "cleaning_action": "Koreksi line_amount OI00131 dari Rp34.000 menjadi Rp27.000.",
        "reason_evidence": "Perhitungan ulang menghasilkan Rp27.000; koreksi membuat total item sesuai subtotal Rp187.000.",
        "implementation_note": "Hitung ulang formula dan bandingkan dengan subtotal serta pembayaran; pertahankan nilai asli dalam audit log.",
        "validation_criteria": "Formula cocok untuk record yang diperbaiki; total item O00049 menjadi Rp187.000 dan total pesanan Rp190.000 sesuai pembayaran berhasil.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-10",
        "source_finding_id": "F-11",
        "dataset": "Order Items",
        "trigger_condition": "line_amount pada OI00511, order O00182, tidak sesuai hasil perhitungan.",
        "decision": "FIX",
        "cleaning_action": "Koreksi line_amount dari Rp31.000 menjadi Rp36.000.",
        "reason_evidence": "Hasil formula menunjukkan selisih Rp5.000; total item berubah dari Rp217.000 menjadi Rp222.000, sesuai subtotal.",
        "implementation_note": "Hitung ulang formula berdasarkan kuantitas, harga satuan, dan diskon; simpan nilai sebelum koreksi.",
        "validation_criteria": "line_amount sesuai formula; jumlah item O00182 menjadi Rp222.000; total pesanan Rp219.000 sesuai pembayaran berhasil.",
        "rule_status": "Draft"
    },
    {
        "rule_id": "CR-11",
        "source_finding_id": "F-12",
        "dataset": "Order Items",
        "trigger_condition": "quantity mengandung teks satuan seperti 2 pcs pada OI00071 dan 1 pcs pada OI00215.",
        "decision": "STANDARDIZE",
        "cleaning_action": "Hilangkan teks pcs, bersihkan spasi, lalu konversi quantity menjadi integer.",
        "reason_evidence": "Nilai kuantitas dasar valid, tetapi format teks menghambat operasi numerik.",
        "implementation_note": "Ekstrak angka setelah validasi format; jangan mengubah nilai yang tidak dapat dikonversi tanpa investigasi.",
        "validation_criteria": "OI00071 menjadi integer 2, OI00215 menjadi integer 1; tidak ada nilai nonnumerik yang belum ditangani.",
        "rule_status": "Draft"
    }
]

# Tambahkan seluruh rule ke cleaning_rules
cleaning_rules = pd.concat(
    [cleaning_rules, pd.DataFrame(new_rules)],
    ignore_index=True
)

# Periksa hasil
print(cleaning_rules.tail(11))
print("Total rules:", len(cleaning_rules))
   rule_id source_finding_id      dataset                                  trigger_condition     decision                                    cleaning_action  \
0    CR-01              F-01    Customers  customer_id duplikat dan seluruh atribut pada ...          FIX    Hapus satu baris dari pasangan exact duplicate.   
1    CR-02              F-02    Customers  customer_segment memiliki variasi Regular, REG...  STANDARDIZE  Hapus spasi di awal/akhir dan ubah label menja...   
2    CR-03              F-03       Orders  order_status memiliki variasi completed dan CO...  STANDARDIZE  Hapus spasi ekstra dan seragamkan kapitalisasi...   
3    CR-04              F-04     Payments  payment_method memiliki variasi e_wallet, E WA...  STANDARDIZE  Hapus spasi ekstra, samakan kapitalisasi, dan ...   
4    CR-05              F-05  Restaurants           price_tier memiliki variasi Mid dan MID.  STANDARDIZE           Hapus spasi ekstra dan ubah menjadi Mid.   
5    CR-06              F-06  Restaurants     price_tier memiliki variasi Budget dan BUDGET.  STANDARDIZE        Hapus spasi ekstra dan ubah menjadi Budget.   
6    CR-07              F-07  Order Items   item_category memiliki variasi Snack dan SNACK .  STANDARDIZE         Hapus spasi ekstra dan ubah menjadi Snack.   
7    CR-08              F-09       Orders  Rekonsiliasi transaksi menunjukkan mismatch pa...          FIX  Koreksi subtotal_amount menjadi Rp125.000 dan ...   
8    CR-09              F-10  Order Items  line_amount tidak sesuai formula quantity × un...          FIX  Koreksi line_amount OI00131 dari Rp34.000 menj...   
9    CR-10              F-11  Order Items  line_amount pada OI00511, order O00182, tidak ...          FIX  Koreksi line_amount dari Rp31.000 menjadi Rp36...   
10   CR-11              F-12  Order Items  quantity mengandung teks satuan seperti 2 pcs ...  STANDARDIZE  Hilangkan teks pcs, bersihkan spasi, lalu konv...   

                                      reason_evidence                                implementation_note                                validation_criteria rule_status  
0   Dua baris memiliki customer_id dan seluruh atr...  Pertahankan satu record C0031 dan dokumentasik...  Jumlah baris menjadi 169; customer_id unik; ti...       Draft  
1   Label menunjukkan kategori yang sama, tetapi p...  Terapkan str.strip().str.title() pada kolom cu...  Hanya satu label standar Regular untuk kategor...       Draft  
2   Perbedaan kapitalisasi menyebabkan kategori ya...  Terapkan trimming dan Title Case pada order_st...  Tidak ada variasi kapitalisasi untuk status ya...       Draft  
3   Perbedaan spasi, kapitalisasi, dan pemisah men...  Gunakan pemetaan label eksplisit ke e_wallet a...  Seluruh variasi yang telah dikonfirmasi ekuiva...       Draft  
4   Variasi kapitalisasi mengakibatkan kategori ha...  Terapkan trimming dan Title Case pada price_tier.       Label Mid konsisten dan jumlah record tetap.       Draft  
5   Variasi kapitalisasi mengakibatkan kategori ha...  Terapkan trimming dan Title Case pada price_tier.    Label Budget konsisten dan jumlah record tetap.       Draft  
6   Record OI00091 dan OI00331 memiliki label kate...  Terapkan trimming dan Title Case pada item_cat...  Tidak ada variasi kapitalisasi/spasi untuk kat...       Draft  
7   Total item Rp125.000 ditambah ongkir Rp18.000 ...  Terapkan koreksi hanya pada record yang diduku...  Untuk O00201, subtotal sama dengan jumlah item...       Draft  
8   Perhitungan ulang menghasilkan Rp27.000; korek...  Hitung ulang formula dan bandingkan dengan sub...  Formula cocok untuk record yang diperbaiki; to...       Draft  
9   Hasil formula menunjukkan selisih Rp5.000; tot...  Hitung ulang formula berdasarkan kuantitas, ha...  line_amount sesuai formula; jumlah item O00182...       Draft  
10  Nilai kuantitas dasar valid, tetapi format tek...  Ekstrak angka setelah validasi format; jangan ...  OI00071 menjadi integer 2, OI00215 menjadi int...       Draft  
Total rules: 11

11. Create Working Copies

# ============================================================
# RESET — 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 reset successfully.")
else:
    print("ERROR: Raw datasets are not ready.")
Working copies reset successfully.

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
# ============================================================
# EDIT AREA 2 — APPROVED CLEANING CODE
# ============================================================
# Gunakan hanya dataframe *_clean.
# JANGAN ubah dataframe *_raw.

import pandas as pd

# ------------------------------------------------------------
# CR-01 — Remove Exact Duplicate Customer
# Source Finding: F-01
# Evidence basis: C0031 muncul identik pada row 30 dan 169.
# Validation criteria: 169 rows, unique customer_id.
# ------------------------------------------------------------
customers_clean = customers_clean.drop_duplicates(
    subset=customers_clean.columns.tolist(),
    keep="first"
).copy()

assert customers_clean["customer_id"].is_unique
# ============================================================
# VALIDATION — CR-01
# ============================================================

# 1. Cek jumlah baris setelah cleaning
print("Jumlah baris customers_clean:", len(customers_clean))

# 2. Cek jumlah unique customer_id
print("Jumlah unique customer_id:", customers_clean["customer_id"].nunique())

# 3. Cek apakah masih ada customer_id duplikat
print(
    "Jumlah baris dengan customer_id duplikat:",
    customers_clean["customer_id"].duplicated().sum()
)

# 4. Cek apakah masih ada exact duplicate
print(
    "Jumlah exact duplicate:",
    customers_clean.duplicated().sum()
)

# 5. Tampilkan semua customer_id yang masih duplikat, jika ada
duplicate_customers = customers_clean[
    customers_clean["customer_id"].duplicated(keep=False)
].sort_values("customer_id")

if duplicate_customers.empty:
    print("PASS: Tidak ada customer_id duplikat.")
else:
    print("FAIL: Masih ditemukan customer_id duplikat.")
    display(duplicate_customers)

# 6. Validasi hasil berdasarkan kriteria CR-01
if (
    len(customers_clean) == 169
    and customers_clean["customer_id"].is_unique
    and not customers_clean.duplicated().any()
):
    print("CR-01 VALIDATION: PASS")
else:
    print("CR-01 VALIDATION: FAIL — Periksa kembali hasil cleaning.")
Jumlah baris customers_clean: 169
Jumlah unique customer_id: 169
Jumlah baris dengan customer_id duplikat: 0
Jumlah exact duplicate: 0
PASS: Tidak ada customer_id duplikat.
CR-01 VALIDATION: PASS
# ------------------------------------------------------------
# CR-02 — Standardize Customer Segment
# Source Finding: F-02
# Evidence basis: Variasi Regular, REGULAR, dan label dengan spasi.
# Validation criteria: Semua variasi tersebut menjadi Regular.
# ------------------------------------------------------------

customers_clean["customer_segment"] = (
    customers_clean["customer_segment"]
    .apply(
        lambda x: "Regular"
        if isinstance(x, str) and x.strip().casefold() == "regular"
        else x
    )
)
# ============================================================
# VALIDATION — CR-02
# ============================================================
# Validasi hasil
print("Kategori customer_segment:")
print(sorted(customers_clean["customer_segment"].dropna().unique()))

print("\nJumlah setiap kategori:")
print(customers_clean["customer_segment"].value_counts(dropna=False))

# Pastikan variasi Regular sudah tidak tersisa
remaining_variants = customers_clean["customer_segment"].dropna().apply(
    lambda x: x.strip().casefold() == "regular"
    and x != "Regular"
    if isinstance(x, str)
    else False
)

assert not remaining_variants.any(), "Masih ada variasi label Regular!"

print("\nCR-02 VALIDATION: PASS")
Kategori customer_segment:
['Loyal', 'New', 'Regular']

Jumlah setiap kategori:
customer_segment
Regular    75
New        55
Loyal      39
Name: count, dtype: int64

CR-02 VALIDATION: PASS
# ------------------------------------------------------------
# CR-03 — Standardize Order Status
# Source Finding: F-03
# Evidence basis: completed and COMPLETED represent the same status.
# Validation criteria: All equivalent labels become completed.
# ------------------------------------------------------------

orders_clean["order_status"] = orders_clean["order_status"].apply(
    lambda x: "completed"
    if isinstance(x, str) and x.strip().casefold() == "completed"
    else x
)
# ============================================================
# VALIDATION — CR-03
# ============================================================
# Validasi hasil
print("Kategori order_status:")
print(sorted(orders_clean["order_status"].dropna().unique()))

print("\nJumlah setiap status:")
print(orders_clean["order_status"].value_counts(dropna=False))

# Pastikan variasi completed sudah menjadi lowercase
remaining_variants = orders_clean["order_status"].dropna().apply(
    lambda x: x.strip().casefold() == "completed" and x != "completed"
    if isinstance(x, str)
    else False
)

assert not remaining_variants.any(), "Masih ada variasi completed!"

print("\nCR-03 VALIDATION: PASS")
Kategori order_status:
['cancelled', 'completed']

Jumlah setiap status:
order_status
completed    302
cancelled     30
Name: count, dtype: int64

CR-03 VALIDATION: PASS
# ------------------------------------------------------------
# CR-04 — Standardize Payment Method
# Source Finding: F-04
# Evidence basis: e_wallet, E WALLET, and E WALLET .
# Validation criteria: Equivalent labels use canonical e_wallet.
# ------------------------------------------------------------

payment_method_map = {
    "e_wallet": "e_wallet",
    "e wallet": "e_wallet",
}

payments_clean["payment_method"] = (
    payments_clean["payment_method"]
    .str.strip()
    .str.lower()
    .replace(payment_method_map)
)
# ============================================================
# VALIDATION — CR-04
# ============================================================


# Validasi hasil
print("Kategori payment_method:")
print(sorted(payments_clean["payment_method"].dropna().unique()))

print("\nJumlah setiap metode pembayaran:")
print(payments_clean["payment_method"].value_counts(dropna=False))

# Pastikan variasi e_wallet sudah seragam
e_wallet_values = payments_clean["payment_method"].dropna()
remaining_variants = e_wallet_values[
    e_wallet_values.str.strip().str.lower().isin(
        ["e_wallet", "e wallet"]
    )
]

assert (remaining_variants == "e_wallet").all(), \
    "Masih ada variasi payment_method yang belum distandardisasi!"

print("\nCR-04 VALIDATION: PASS")
Kategori payment_method:
['bank_transfer', 'cash', 'credit_card', 'e_wallet']

Jumlah setiap metode pembayaran:
payment_method
credit_card      93
e_wallet         88
bank_transfer    88
cash             88
Name: count, dtype: int64

CR-04 VALIDATION: PASS
# ------------------------------------------------------------
# CR-05 — Standardize Restaurant Price Tier: Mid
# Source Finding: F-05
# Evidence basis: Mid (2 data) and MID (1 data) are equivalent.
# Validation criteria: All equivalent labels become Mid.
# ------------------------------------------------------------

restaurants_clean["price_tier"] = restaurants_clean["price_tier"].apply(
    lambda x: "Mid"
    if isinstance(x, str) and x.strip().casefold() == "mid"
    else x
)
# ============================================================
# VALIDATION — CR-05
# ============================================================


# Validasi hasil
print("Kategori price_tier:")
print(sorted(restaurants_clean["price_tier"].dropna().unique()))

print("\nJumlah setiap kategori:")
print(restaurants_clean["price_tier"].value_counts(dropna=False))

# Pastikan tidak ada variasi Mid yang tersisa
mid_values = restaurants_clean["price_tier"].dropna()
remaining_variants = mid_values[
    mid_values.str.strip().str.casefold() == "mid"
]

assert (remaining_variants == "Mid").all(), \
    "Masih ada variasi label Mid!"

print("\nCR-05 VALIDATION: PASS")
Kategori price_tier:
['BUDGET ', 'Budget', 'Mid', 'Premium']

Jumlah setiap kategori:
price_tier
Premium    10
Budget     10
Mid         3
BUDGET      1
Name: count, dtype: int64

CR-05 VALIDATION: PASS

# ------------------------------------------------------------
# CR-06 — Standardize Restaurant Price Tier: Budget
# Source Finding: F-06
# Evidence basis: Budget and BUDGET are equivalent labels.
# Validation criteria: Standard label is Budget.
# ------------------------------------------------------------
# Covered by CR-05 using the same column transformation.

restaurants_clean["price_tier"] = restaurants_clean["price_tier"].apply(
    lambda x: "Budget"
    if isinstance(x, str) and x.strip().casefold() == "budget"
    else x
)
# ============================================================
# VALIDATION — CR-06
# ============================================================

# Validasi hasil
print("Kategori price_tier:")
print(sorted(restaurants_clean["price_tier"].dropna().unique()))

print("\nJumlah setiap kategori:")
print(restaurants_clean["price_tier"].value_counts(dropna=False))

# Pastikan tidak ada variasi Budget yang tersisa
budget_values = restaurants_clean["price_tier"].dropna()
remaining_variants = budget_values[
    budget_values.str.strip().str.casefold() == "budget"
]

assert (remaining_variants == "Budget").all(), \
    "Masih ada variasi label Budget!"

print("\nCR-06 VALIDATION: PASS")

Kategori price_tier:
['Budget', 'Mid', 'Premium']

Jumlah setiap kategori:
price_tier
Budget     11
Premium    10
Mid         3
Name: count, dtype: int64

CR-06 VALIDATION: PASS
# ------------------------------------------------------------
# CR-07 — Standardize Item Category
# Source Finding: F-07
# Evidence basis: Snack and SNACK have inconsistent formatting.
# Validation criteria: Standard label is Snack.
# ------------------------------------------------------------

# ============================================================
# CR-07 — Standardize Item Category
# ============================================================

order_items_clean["item_category"] = (
    order_items_clean["item_category"].apply(
        lambda x: "Snack"
        if isinstance(x, str) and x.strip().casefold() == "snack"
        else x
    )
)

# ============================================================
# VALIDATION — CR-07
# ============================================================

print("Kategori item_category:")
print(sorted(order_items_clean["item_category"].dropna().unique()))

print("\nJumlah setiap kategori:")
print(order_items_clean["item_category"].value_counts(dropna=False))

snack_values = order_items_clean["item_category"].dropna()
remaining_variants = snack_values[
    snack_values.str.strip().str.casefold() == "snack"
]

assert (remaining_variants == "Snack").all(), \
    "Masih ada variasi label Snack!"

print("\nCR-07 VALIDATION: PASS")
Kategori item_category:
['Dessert', 'Drink', 'Main', 'Snack']

Jumlah setiap kategori:
item_category
Main       396
Drink      277
Snack      156
Dessert    115
Name: count, dtype: int64

CR-07 VALIDATION: PASS
# ============================================================
# VALIDATION — CR-07
# ============================================================
# Validasi hasil
print("Kategori item_category:")
print(sorted(order_items_clean["item_category"].dropna().unique()))

print("\nJumlah setiap kategori:")
print(order_items_clean["item_category"].value_counts(dropna=False))

# Pastikan tidak ada variasi Snack yang tersisa
snack_values = order_items_clean["item_category"].dropna()
remaining_variants = snack_values[
    snack_values.str.strip().str.casefold() == "snack"
]

assert (remaining_variants == "Snack").all(), \
    "Masih ada variasi label Snack!"

print("\nCR-07 VALIDATION: PASS")
Kategori item_category:
['Dessert', 'Drink', 'Main', 'Snack']

Jumlah setiap kategori:
item_category
Main       396
Drink      277
Snack      156
Dessert    115
Name: count, dtype: int64

CR-07 VALIDATION: PASS
# ------------------------------------------------------------
# CR-08 — Correct Order O00201
# Source Finding: F-09
# Evidence basis: Reconciliation with successful payment P00226.
# Validation criteria: Subtotal 125000 and total 138000.
# ------------------------------------------------------------
orders_clean.loc[
    orders_clean["order_id"].eq("O00201"),
    "subtotal_amount"
] = 125000

orders_clean.loc[
    orders_clean["order_id"].eq("O00201"),
    "total_amount"
] = 138000
# ============================================================
# VALIDATION — CR-08
# ============================================================

# 1. Ambil data order O00201
order_check = orders_clean[
    orders_clean["order_id"] == "O00201"
]

# 2. Tampilkan hasil koreksi
print("Hasil koreksi O00201:")
display(order_check)

# 3. Periksa apakah order ditemukan tepat satu kali
assert len(order_check) == 1, \
    "FAIL: O00201 tidak ditemukan atau memiliki lebih dari satu record."

# 4. Validasi subtotal dan total
assert order_check["subtotal_amount"].iloc[0] == 125000, \
    "FAIL: subtotal_amount belum sesuai."

assert order_check["total_amount"].iloc[0] == 138000, \
    "FAIL: total_amount belum sesuai."

print("Subtotal O00201:", order_check["subtotal_amount"].iloc[0])
print("Total O00201:", order_check["total_amount"].iloc[0])

print("\nCR-09 VALIDATION: PASS")
Hasil koreksi O00201:
order_id customer_id restaurant_id order_timestamp order_status promo_code subtotal_amount delivery_fee discount_amount total_amount
200 O00201 C0088 R0012 2026-03-11 02:16:00 completed NaN 125000 18000 5000 138000
Subtotal O00201: 125000
Total O00201: 138000

CR-09 VALIDATION: PASS
# ------------------------------------------------------------
# CR-09 — Correct Line Amount for OI00131
# Source Finding: F-10
# Evidence basis: Recalculation gives 27000.
# Validation criteria: Corrected line amount is 27000.
# ------------------------------------------------------------
order_items_clean.loc[
    order_items_clean["order_item_id"].eq("OI00131"),
    "line_amount"
] = 27000
# ============================================================
# VALIDATION — CR-09
# ============================================================

import pandas as pd

# 1. Periksa tipe data kolom numerik
numeric_cols = [
    "quantity",
    "unit_price",
    "line_discount",
    "line_amount"
]

print("Tipe data sebelum konversi:")
print(order_items_clean[numeric_cols].dtypes)

# 2. Konversi kolom menjadi numerik
for col in numeric_cols:
    order_items_clean[col] = pd.to_numeric(
        order_items_clean[col],
        errors="coerce"
    )

# 3. Periksa apakah ada nilai yang gagal dikonversi
invalid_values = order_items_clean[numeric_cols].isna().sum()

print("\nJumlah nilai NaN setelah konversi:")
print(invalid_values)

# 4. Ambil record OI00131
item_check = order_items_clean[
    order_items_clean["order_item_id"] == "OI00131"
]

# 5. Pastikan record ditemukan tepat satu kali
assert len(item_check) == 1, (
    "FAIL: OI00131 tidak ditemukan atau memiliki lebih "
    "dari satu record."
)

# 6. Tampilkan hasil koreksi
display(item_check)

# 7. Validasi line_amount
item = item_check.iloc[0]

assert pd.notna(item["line_amount"]), (
    "FAIL: line_amount OI00131 bukan nilai numerik yang valid."
)

assert item["line_amount"] == 27000, (
    "FAIL: line_amount OI00131 belum menjadi Rp27.000."
)

# 8. Hitung ulang berdasarkan formula
calculated_amount = (
    item["quantity"] * item["unit_price"]
    - item["line_discount"]
)

print("\nLine amount pada dataset:", item["line_amount"])
print("Hasil perhitungan formula:", calculated_amount)

assert pd.notna(calculated_amount), (
    "FAIL: Hasil perhitungan mengandung NaN."
)

assert calculated_amount == item["line_amount"], (
    "FAIL: line_amount tidak sesuai formula."
)

# 9. Validasi subtotal item order O00049
order_items_o00049 = order_items_clean[
    order_items_clean["order_id"] == "O00049"
]

assert not order_items_o00049.empty, (
    "FAIL: Tidak ditemukan item untuk order O00049."
)

assert order_items_o00049["line_amount"].notna().all(), (
    "FAIL: Terdapat line_amount yang tidak valid pada O00049."
)

total_items_o00049 = order_items_o00049["line_amount"].sum()

print("\nTotal item O00049:", total_items_o00049)

assert total_items_o00049 == 187000, (
    "FAIL: Total item O00049 belum menjadi Rp187.000."
)

print("\nCR-09 VALIDATION: PASS")
Tipe data sebelum konversi:
quantity           str
unit_price       int64
line_discount    int64
line_amount      int64
dtype: object

Jumlah nilai NaN setelah konversi:
quantity         2
unit_price       0
line_discount    0
line_amount      0
dtype: int64
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
130 OI00131 O00049 3 Iced Tea Drink 3.0 9000 0 27000
Line amount pada dataset: 27000
Hasil perhitungan formula: 27000.0

Total item O00049: 187000

CR-09 VALIDATION: PASS

# ------------------------------------------------------------
# CR-10 — Correct Line Amount for OI00511
# Source Finding: F-11
# Evidence basis: Recalculation gives 36000.
# Validation criteria: Corrected line amount is 36000.
# ------------------------------------------------------------
order_items_clean.loc[
    order_items_clean["order_item_id"].eq("OI00511"),
    "line_amount"
] = 36000
# ============================================================
# VALIDATION — CR-10
# ============================================================

# 1. Ambil record OI00511
item_check = order_items_clean[
    order_items_clean["order_item_id"] == "OI00511"
]

# 2. Pastikan record ditemukan tepat satu kali
assert len(item_check) == 1, \
    "FAIL: OI00511 tidak ditemukan atau memiliki lebih dari satu record."

# 3. Tampilkan hasil koreksi
display(item_check)

# 4. Validasi line_amount = Rp36.000
item = item_check.iloc[0]

assert item["line_amount"] == 36000, \
    "FAIL: line_amount belum menjadi Rp36.000."

# 5. Hitung ulang formula
calculated_amount = (
    item["quantity"] * item["unit_price"]
    - item["line_discount"]
)

print("Line amount pada dataset:", item["line_amount"])
print("Hasil perhitungan formula:", calculated_amount)

assert calculated_amount == item["line_amount"], \
    "FAIL: line_amount tidak sesuai formula."

# 6. Validasi total item order O00182
order_items_o00182 = order_items_clean[
    order_items_clean["order_id"] == "O00182"
]

total_items_o00182 = order_items_o00182["line_amount"].sum()

print("\nTotal item O00182:", total_items_o00182)

assert total_items_o00182 == 222000, \
    "FAIL: Total item O00182 belum menjadi Rp222.000."

print("\nCR-11 VALIDATION: PASS")
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
510 OI00511 O00182 4 Coffee Drink 2.0 18000 0 36000
Line amount pada dataset: 36000
Hasil perhitungan formula: 36000.0

Total item O00182: 222000

CR-11 VALIDATION: PASS
# ------------------------------------------------------------
# CR-11 — Convert Quantity to Integer
# Source Finding: F-12
# Evidence basis: Quantity contains text such as "2 pcs".
# Validation criteria: OI00071 = 2 and OI00215 = 1.
# ------------------------------------------------------------

import pandas as pd

# Ambil ulang working copy dari raw
order_items_clean = order_items_raw.copy(deep=True)

# Bersihkan format quantity dari raw
quantity_text = (
    order_items_raw["quantity"]
    .astype("string")
    .str.strip()
)

# Ekstrak angka dari format seperti "2 pcs" atau "1"
quantity_extracted = quantity_text.str.extract(
    r"^(\d+)\s*(?:pcs)?$",
    flags=0,
    expand=False
)

# Periksa nilai yang tidak sesuai format sebelum konversi
invalid_mask = quantity_text.notna() & quantity_extracted.isna()

if invalid_mask.any():
    print("Ditemukan format quantity yang belum tertangani:")
    display(
        order_items_raw.loc[
            invalid_mask,
            ["order_item_id", "quantity"]
        ]
    )
    raise ValueError("Cleaning dihentikan: periksa format quantity.")

# Konversi menjadi nullable integer
order_items_clean["quantity"] = pd.to_numeric(
    quantity_extracted,
    errors="raise"
).astype("Int64")

# Validasi hasil CR-11
check = order_items_clean.loc[
    order_items_clean["order_item_id"].isin(
        ["OI00071", "OI00215"]
    ),
    ["order_item_id", "quantity"]
]

display(check)

results = check.set_index("order_item_id")["quantity"]

assert len(check) == 2, "FAIL: Record tidak ditemukan tepat dua."

assert results.loc["OI00071"] == 2, (
    "FAIL: OI00071 seharusnya memiliki quantity 2."
)

assert results.loc["OI00215"] == 1, (
    "FAIL: OI00215 seharusnya memiliki quantity 1."
)

assert str(order_items_clean["quantity"].dtype) == "Int64"

print("CR-11 VALIDATION: PASS")
order_item_id quantity
70 OI00071 2
214 OI00215 1
CR-11 VALIDATION: PASS
# ============================================================
# INVESTIGATION — RAW VS CLEAN (CR-11 / CR-12)
# ============================================================

import pandas as pd

target_ids = ["OI00071", "OI00215"]

# Ambil data dari raw dan clean
raw_check = order_items_raw.loc[
    order_items_raw["order_item_id"].isin(target_ids),
    ["order_item_id", "quantity"]
].copy()

clean_check = order_items_clean.loc[
    order_items_clean["order_item_id"].isin(target_ids),
    ["order_item_id", "quantity"]
].copy()

print("=== RAW DATA ===")
display(raw_check)
print("Raw quantity dtype:", order_items_raw["quantity"].dtype)

print("\n=== CLEAN DATA ===")
display(clean_check)
print("Clean quantity dtype:", order_items_clean["quantity"].dtype)

# Bandingkan nilai mentah secara persis
print("\n=== DETAIL NILAI RAW ===")
for _, row in raw_check.iterrows():
    print(
        row["order_item_id"],
        "| value:", repr(row["quantity"]),
        "| type:", type(row["quantity"]).__name__
    )

print("\n=== DETAIL NILAI CLEAN ===")
for _, row in clean_check.iterrows():
    print(
        row["order_item_id"],
        "| value:", repr(row["quantity"]),
        "| type:", type(row["quantity"]).__name__
    )
=== RAW DATA ===
order_item_id quantity
70 OI00071 2 pcs
214 OI00215 1 pcs
Raw quantity dtype: str

=== CLEAN DATA ===
order_item_id quantity
70 OI00071 2
214 OI00215 1
Clean quantity dtype: Int64

=== DETAIL NILAI RAW ===
OI00071 | value: '2 pcs' | type: str
OI00215 | value: '1 pcs' | type: str

=== DETAIL NILAI CLEAN ===
OI00071 | value: 2 | type: int
OI00215 | value: 1 | type: int
# ============================================================
# VALIDATION — CR-11
# ============================================================

import pandas as pd

# 1. Ambil dua record yang menjadi bukti temuan
target_ids = ["OI00071", "OI00215"]

quantity_check = order_items_clean[
    order_items_clean["order_item_id"].isin(target_ids)
][["order_item_id", "quantity"]]

display(quantity_check)

# 2. Pastikan setiap ID ditemukan tepat satu kali
for item_id in target_ids:
    record_count = (
        order_items_clean["order_item_id"] == item_id
    ).sum()

    assert record_count == 1, (
        f"FAIL: {item_id} tidak ditemukan atau memiliki "
        "lebih dari satu record."
    )

# 3. Pastikan tipe data quantity adalah nullable integer
assert str(order_items_clean["quantity"].dtype) == "Int64", (
    "FAIL: Tipe data quantity bukan Int64."
)

# 4. Ambil hasil quantity berdasarkan ID
quantity_result = quantity_check.set_index(
    "order_item_id"
)["quantity"]

# 5. Pastikan nilai quantity tidak kosong
for item_id in target_ids:
    value = quantity_result.loc[item_id]

    assert pd.notna(value), (
        f"FAIL: Quantity {item_id} bernilai kosong (NA)."
    )

# 6. Validasi hasil koreksi quantity
assert quantity_result.loc["OI00071"] == 2, (
    "FAIL: Quantity OI00071 seharusnya 2."
)

assert quantity_result.loc["OI00215"] == 1, (
    "FAIL: Quantity OI00215 seharusnya 1."
)

# 7. Periksa quantity yang kosong
null_quantity = order_items_clean["quantity"].isna().sum()

print("\nJumlah quantity null:", null_quantity)
print("Tipe data quantity:", order_items_clean["quantity"].dtype)

# 8. Validasi akhir
print("\nCR-11 VALIDATION: PASS")
order_item_id quantity
70 OI00071 2
214 OI00215 1
Jumlah quantity null: 0
Tipe data quantity: Int64

CR-11 VALIDATION: 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([
    {
        "log_id": "CL-01",
        "rule_id": "CR-01",
        "dataset": "customers_raw",
        "affected_records_scope": "customer_id = C0031 (row index 169)",
        "action_performed": "Drop 1 baris exact duplicate — simpan row pertama (index 30), hapus row kedua (index 169)",
        "before_evidence": "C0031 muncul 2 baris identik: Vina Hidayat, vinahidayat31@example.com, North, Loyal",
        "after_evidence": "1 baris unik tersisa: C0031",
        "rows_records_affected": "170 → 169 rows",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-02",
        "rule_id": "CR-02",
        "dataset": "customers_raw",
        "affected_records_scope": 'customer_segment — 2 records: "REGULAR "',
        "action_performed": 'Standardisasi "REGULAR " → "Regular" via .str.strip().str.title()',
        "before_evidence": '"REGULAR " (2 records, 1.18%)',
        "after_evidence": '"Regular"',
        "rows_records_affected": "2 records",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-03",
        "rule_id": "CR-03",
        "dataset": "orders_raw",
        "affected_records_scope": 'order_status — 2 records: "COMPLETED "',
        "action_performed": 'Standardisasi "COMPLETED " → "completed" via .str.strip().str.lower()',
        "before_evidence": '"COMPLETED " (2 records, 0.60%)',
        "after_evidence": '"completed"',
        "rows_records_affected": "2 records",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-04",
        "rule_id": "CR-04",
        "dataset": "payments_raw",
        "affected_records_scope": 'payment_method — 2 records: "E WALLET "',
        "action_performed": 'Standardisasi "E WALLET " → "e_wallet" via .str.strip().str.replace(" ", "_").str.lower()',
        "before_evidence": '"E WALLET " (2 records, 0.56%)',
        "after_evidence": '"e_wallet"',
        "rows_records_affected": "2 records",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-05",
        "rule_id": "CR-05",
        "dataset": "restaurants_raw",
        "affected_records_scope": 'price_tier — 1 record: "MID "',
        "action_performed": 'Standardisasi "MID " → "Mid" via .str.strip().str.title()',
        "before_evidence": '"MID " (1 record, 4.17%)',
        "after_evidence": '"Mid"',
        "rows_records_affected": "1 record",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-06",
        "rule_id": "CR-06",
        "dataset": "restaurants_raw",
        "affected_records_scope": 'price_tier — 1 record: "BUDGET "',
        "action_performed": 'Standardisasi "BUDGET " → "Budget" via .str.strip().str.title()',
        "before_evidence": '"BUDGET " (1 record, 4.17%)',
        "after_evidence": '"Budget"',
        "rows_records_affected": "1 record",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-07",
        "rule_id": "CR-07",
        "dataset": "order_items_raw",
        "affected_records_scope": 'item_category — 2 records: "SNACK "',
        "action_performed": 'Standardisasi "SNACK " → "Snack" via .str.strip().str.title()',
        "before_evidence": '"SNACK " (2 records, 0.21%)',
        "after_evidence": '"Snack"',
        "rows_records_affected": "2 records",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-08",
        "rule_id": "CR-08",
        "dataset": "Orders",
        "affected_records_scope": "O00201 — rekonsiliasi mismatch pada O00049, O00182, dan O00201; O00201 terbukti salah catat",
        "action_performed": "Koreksi subtotal_amount → Rp125.000 dan total_amount → Rp138.000 untuk O00201",
        "before_evidence": "Mismatch terdeteksi via rekonsiliasi; subtotal & total tidak sesuai formula",
        "after_evidence": "Total item Rp125.000 + ongkir Rp18.000 − diskon Rp5.000 = Rp138.000; sesuai pembayaran P00226",
        "rows_records_affected": "1 record (O00201)",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-09",
        "rule_id": "CR-09",
        "dataset": "Order Items",
        "affected_records_scope": "OI00131, order O00049 (item: Iced Tea)",
        "action_performed": "Koreksi line_amount OI00131: Rp34.000 → Rp27.000",
        "before_evidence": "line_amount Rp34.000 — tidak sesuai formula qty × unit_price − line_discount",
        "after_evidence": "Rp27.000 hasil perhitungan ulang; total item O00049 = Rp187.000, total pesanan Rp190.000 sesuai pembayaran",
        "rows_records_affected": "1 record (OI00131)",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-10",
        "rule_id": "CR-10",
        "dataset": "Order Items",
        "affected_records_scope": "OI00511, order O00182 (item: Coffee)",
        "action_performed": "Koreksi line_amount OI00511: Rp31.000 → Rp36.000",
        "before_evidence": "line_amount Rp31.000 — selisih Rp5.000 dari hasil formula; total item sebelumnya Rp217.000",
        "after_evidence": "Rp36.000 sesuai formula; jumlah item O00182 = Rp222.000, total pesanan Rp219.000 sesuai pembayaran",
        "rows_records_affected": "1 record (OI00511)",
        "result": "Done",
        "notes": "-"
    },
    {
        "log_id": "CL-11",
        "rule_id": "CR-11",
        "dataset": "Order Items",
        "affected_records_scope": 'OI00071 ("2 pcs"), OI00215 ("1 pcs") — kolom quantity',
        "action_performed": "Hilangkan teks pcs, bersihkan spasi, konversi quantity ke tipe integer",
        "before_evidence": 'quantity berformat string: "2 pcs", "1 pcs" — menghambat operasi numerik',
        "after_evidence": "OI00071 → 2 (int), OI00215 → 1 (int); tidak ada nilai non-numerik yang belum ditangani",
        "rows_records_affected": "2 records",
        "result": "Done",
        "notes": "-"
    }
], 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_raw customer_id = C0031 (row index 169) Drop 1 baris exact duplicate — simpan row pert... C0031 muncul 2 baris identik: Vina Hidayat, vi... 1 baris unik tersisa: C0031 170 → 169 rows Done -
1 CL-02 CR-02 customers_raw customer_segment — 2 records: "REGULAR " Standardisasi "REGULAR " → "Regular" via .str.... "REGULAR " (2 records, 1.18%) "Regular" 2 records Done -
2 CL-03 CR-03 orders_raw order_status — 2 records: "COMPLETED " Standardisasi "COMPLETED " → "completed" via .... "COMPLETED " (2 records, 0.60%) "completed" 2 records Done -
3 CL-04 CR-04 payments_raw payment_method — 2 records: "E WALLET " Standardisasi "E WALLET " → "e_wallet" via .st... "E WALLET " (2 records, 0.56%) "e_wallet" 2 records Done -
4 CL-05 CR-05 restaurants_raw price_tier — 1 record: "MID " Standardisasi "MID " → "Mid" via .str.strip().... "MID " (1 record, 4.17%) "Mid" 1 record Done -
5 CL-06 CR-06 restaurants_raw price_tier — 1 record: "BUDGET " Standardisasi "BUDGET " → "Budget" via .str.st... "BUDGET " (1 record, 4.17%) "Budget" 1 record Done -
6 CL-07 CR-07 order_items_raw item_category — 2 records: "SNACK " Standardisasi "SNACK " → "Snack" via .str.stri... "SNACK " (2 records, 0.21%) "Snack" 2 records Done -
7 CL-08 CR-08 Orders O00201 — rekonsiliasi mismatch pada O00049, O0... Koreksi subtotal_amount → Rp125.000 dan total_... Mismatch terdeteksi via rekonsiliasi; subtotal... Total item Rp125.000 + ongkir Rp18.000 − disko... 1 record (O00201) Done -
8 CL-09 CR-09 Order Items OI00131, order O00049 (item: Iced Tea) Koreksi line_amount OI00131: Rp34.000 → Rp27.000 line_amount Rp34.000 — tidak sesuai formula qt... Rp27.000 hasil perhitungan ulang; total item O... 1 record (OI00131) Done -
9 CL-10 CR-10 Order Items OI00511, order O00182 (item: Coffee) Koreksi line_amount OI00511: Rp31.000 → Rp36.000 line_amount Rp31.000 — selisih Rp5.000 dari ha... Rp36.000 sesuai formula; jumlah item O00182 = ... 1 record (OI00511) Done -
10 CL-11 CR-11 Order Items OI00071 ("2 pcs"), OI00215 ("1 pcs") — kolom q... Hilangkan teks pcs, bersihkan spasi, konversi ... quantity berformat string: "2 pcs", "1 pcs" — ... OI00071 → 2 (int), OI00215 → 1 (int); tidak ad... 2 records Done -

13. Validation Harness — EDIT + RUN

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

`V-01` sampai `V-15`

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

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

validation_results = []

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

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

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

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

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

13A. Schema & Key Validation — EDIT + RUN

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

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

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

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

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

    # V-01 — Schema validation
    required_columns = {
        "Customers": [
            "customer_id", "customer_segment"
        ],
        "Orders": [
            "order_id", "customer_id", "restaurant_id",
            "order_status", "subtotal_amount", "delivery_fee",
            "discount_amount", "total_amount"
        ],
        "Order Items": [
            "order_item_id", "order_id", "quantity",
            "unit_price", "line_discount", "line_amount",
            "item_category"
        ],
        "Payments": [
            "payment_id", "order_id", "payment_method"
        ],
        "Restaurants": [
            "restaurant_id", "price_tier"
        ],
    }

    schema_results = {}
    for name, df in clean_tables.items():
        missing = [
            col for col in required_columns[name]
            if col not in df.columns
        ]
        schema_results[name] = {
            "missing_columns": missing,
            "passed": len(missing) == 0
        }

    schema_passed = all(
        result["passed"] for result in schema_results.values()
    )

    record_check(
        "V-01", "Schema", "All clean datasets",
        "Apakah seluruh required column masih tersedia?",
        "Required columns available",
        schema_results,
        schema_passed,
        notes=(
            "Seluruh required columns tersedia."
            if schema_passed
            else "Terdapat required columns yang belum tersedia."
        )
    )

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

    record_check(
        "V-02", "Key", "Customers",
        "Apakah customer_id valid sesuai grain customer?",
        "No null key; uniqueness sesuai grain",
        result.to_dict("records")[0],
        passed,
        related_rule_id="CR-01"
    )

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

    record_check(
        "V-03", "Key", "Orders",
        "Apakah order_id valid sesuai grain order?",
        "No null key; one row per order",
        result.to_dict("records")[0],
        passed
    )

    # V-04 — Order Items key
    result = key_check(
        order_items_clean, "order_item_id", "Order Items"
    )
    passed = bool(result.loc[0, "candidate_key_unique"])

    record_check(
        "V-04", "Key", "Order Items",
        "Apakah order_item_id / candidate business key valid?",
        "No invalid duplicate key",
        result.to_dict("records")[0],
        passed
    )

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

    record_check(
        "V-05", "Key", "Payments",
        "Apakah payment_id unique tanpa menghapus repeated order_id yang valid?",
        "payment_id unique; repeated order_id allowed by grain",
        result.to_dict("records")[0],
        passed,
        related_rule_id="CR-04",
        notes=(
            "Pemeriksaan menggunakan payment_id. "
            "Repeated order_id tidak otomatis dianggap duplikat."
        )
    )

else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes:
Customers (169, 7)
Orders (332, 10)
Order Items (944, 9)
Payments (357, 7)
Restaurants (24, 7)
display(
    pd.DataFrame(validation_results)[
        pd.DataFrame(validation_results)["check_id"].isin(
            ["V-01", "V-02", "V-03", "V-04", "V-05"]
        )
    ]
)
check_id validation_level dataset_relationship validation_question expected_criteria actual_result_metric status related_rule_id notes
0 V-01 Schema All clean datasets Apakah seluruh required column masih tersedia? Required columns available {'Customers': {'missing_columns': [], 'passed'... PASS Seluruh required columns tersedia.
1 V-02 Key Customers Apakah customer_id valid sesuai grain customer? No null key; uniqueness sesuai grain {'dataset': 'Customers', 'key_cols': 'customer... PASS CR-01
2 V-03 Key Orders Apakah order_id valid sesuai grain order? No null key; one row per order {'dataset': 'Orders', 'key_cols': 'order_id', ... PASS
3 V-04 Key Order Items Apakah order_item_id / candidate business key ... No invalid duplicate key {'dataset': 'Order Items', 'key_cols': 'order_... PASS
4 V-05 Key Payments Apakah payment_id unique tanpa menghapus repea... payment_id unique; repeated order_id allowed b... {'dataset': 'Payments', 'key_cols': 'payment_i... PASS CR-04 Pemeriksaan menggunakan payment_id. Repeated o...

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.

for col in ["payment_method", "price_tier", "item_category"]:
    df = {
        "payment_method": payments_clean,
        "price_tier": restaurants_clean,
        "item_category": order_items_clean,
    }[col]

    print(f"\n{col}:")
    print(df[col].value_counts(dropna=False).to_dict())

    print("Ada whitespace:",
          bool((df[col].dropna() != df[col].dropna().str.strip()).any()))
payment_method:
{'credit_card': 93, 'e_wallet': 88, 'bank_transfer': 88, 'cash': 88}
Ada whitespace: False

price_tier:
{'Budget': 11, 'Premium': 10, 'Mid': 3}
Ada whitespace: False

item_category:
{'Main': 396, 'Drink': 277, 'Snack': 154, 'Dessert': 115, 'SNACK ': 2}
Ada whitespace: True
display(
    order_items_clean[
        order_items_clean["order_item_id"].isin(
            ["OI00131", "OI00511"]
        )
    ][
        [
            "order_item_id", "order_id", "quantity",
            "unit_price", "line_discount", "line_amount"
        ]
    ]
)
order_item_id order_id quantity unit_price line_discount line_amount
130 OI00131 O00049 3 9000 0 34000
510 OI00511 O00182 2 18000 0 31000
# CR-07 — Standardisasi item_category
order_items_clean["item_category"] = (
    order_items_clean["item_category"].apply(
        lambda x: "Snack"
        if isinstance(x, str) and x.strip().casefold() == "snack"
        else x
    )
)

# CR-09 — Koreksi OI00131
mask_131 = order_items_clean["order_item_id"].eq("OI00131")
assert mask_131.sum() == 1, "OI00131 tidak ditemukan tepat satu kali."
order_items_clean.loc[mask_131, "line_amount"] = 27000

# CR-10 — Koreksi OI00511
mask_511 = order_items_clean["order_item_id"].eq("OI00511")
assert mask_511.sum() == 1, "OI00511 tidak ditemukan tepat satu kali."
order_items_clean.loc[mask_511, "line_amount"] = 36000

# Verifikasi langsung
print("Kategori:", order_items_clean["item_category"].value_counts(dropna=False).to_dict())

display(
    order_items_clean[
        order_items_clean["order_item_id"].isin(["OI00131", "OI00511"])
    ][
        ["order_item_id", "order_id", "quantity", "unit_price",
         "line_discount", "line_amount"]
    ]
)
Kategori: {'Main': 396, 'Drink': 277, 'Snack': 156, 'Dessert': 115}
order_item_id order_id quantity unit_price line_discount line_amount
130 OI00131 O00049 3 9000 0 27000
510 OI00511 O00182 2 18000 0 36000
if DATA_READY:
    # ========================================================
    # EDIT AREA 3B — V-06 s.d. V-14
    # ========================================================

    # Helper: cek null, whitespace, dan variasi penulisan kategori
    def category_quality(df, column):
        s = df[column].astype("string")
        non_null = s.dropna()
        trimmed = non_null.str.strip()

        # Menyamakan kapitalisasi dan spasi untuk mendeteksi
        # variasi label yang seharusnya konsisten
        canonical = trimmed.str.casefold().str.replace(
            r"\s+", "_", regex=True
        )

        variants = (
            pd.DataFrame({
                "original": non_null,
                "canonical": canonical
            })
            .groupby("canonical")["original"]
            .nunique()
        )
        variant_groups = variants[variants > 1]

        null_count = int(s.isna().sum())
        blank_count = int((s.notna() & s.str.strip().eq("")).sum())
        whitespace_count = int(
            (s.notna() & s.ne(s.str.strip())).sum()
        )

        passed = (
            null_count == 0
            and blank_count == 0
            and whitespace_count == 0
            and len(variant_groups) == 0
        )

        return {
            "null_count": null_count,
            "blank_count": blank_count,
            "leading_trailing_whitespace": whitespace_count,
            "inconsistent_label_groups": int(len(variant_groups)),
            "inconsistent_label_examples": variant_groups.index.tolist()[:10],
            "unique_values": sorted(non_null.unique().tolist()),
            "passed": passed
        }

    # --------------------------------------------------------
    # V-06 — Customers category
    # --------------------------------------------------------
    result = category_quality(customers_clean, "customer_segment")

    record_check(
        "V-06", "Category", "Customers",
        "Apakah customer_segment konsisten dan tidak kosong?",
        "No null/blank values, whitespace, or inconsistent labels",
        result,
        result["passed"],
        related_rule_id="CR-02"
    )

    # --------------------------------------------------------
    # V-07 — Orders category
    # --------------------------------------------------------
    result = category_quality(orders_clean, "order_status")

    record_check(
        "V-07", "Category", "Orders",
        "Apakah order_status konsisten dan tidak kosong?",
        "No null/blank values, whitespace, or inconsistent labels",
        result,
        result["passed"],
        related_rule_id="CR-03"
    )

    # --------------------------------------------------------
    # V-08 — Payments, Restaurants, Order Items categories
    # --------------------------------------------------------
    category_specs = [
        ("Payments", payments_clean, "payment_method"),
        ("Restaurants", restaurants_clean, "price_tier"),
        ("Order Items", order_items_clean, "item_category"),
    ]

    category_results = {}
    for name, df, column in category_specs:
        category_results[name] = category_quality(df, column)

    passed = all(
        result["passed"] for result in category_results.values()
    )

    record_check(
        "V-08", "Category",
        "Payments / Restaurants / Order Items",
        "Apakah kategori pada ketiga dataset konsisten?",
        "No null/blank values, whitespace, or inconsistent labels",
        category_results,
        passed,
        related_rule_id="CR-04; CR-05; CR-06; CR-07"
    )

    # --------------------------------------------------------
    # V-09 — Order Items formula validation
    # line_amount = quantity * unit_price - line_discount
    # --------------------------------------------------------
    oi = order_items_clean.copy()

    qty = pd.to_numeric(oi["quantity"], errors="coerce")
    unit_price = pd.to_numeric(oi["unit_price"], errors="coerce")
    discount = pd.to_numeric(oi["line_discount"], errors="coerce")
    actual = pd.to_numeric(oi["line_amount"], errors="coerce")

    expected = qty * unit_price - discount
    valid_numeric = (
        qty.notna()
        & unit_price.notna()
        & discount.notna()
        & actual.notna()
    )
    matches = (actual - expected).abs() <= 0.01
    mismatch = valid_numeric & ~matches
    invalid_numeric = ~valid_numeric

    result = {
        "total_rows": len(oi),
        "invalid_numeric_rows": int(invalid_numeric.sum()),
        "formula_mismatch_rows": int(mismatch.sum()),
        "mismatch_examples": oi.loc[
            mismatch, "order_item_id"
        ].head(10).tolist()
    }
    passed = bool(invalid_numeric.sum() == 0 and mismatch.sum() == 0)

    record_check(
        "V-09", "Formula", "Order Items",
        "Apakah line_amount = quantity × unit_price − line_discount?",
        "All rows numeric and formula-consistent",
        result,
        passed,
        related_rule_id="CR-09; CR-10"
    )

    # --------------------------------------------------------
    # V-10 — Orders total formula validation
    # total_amount = subtotal_amount + delivery_fee - discount_amount
    # --------------------------------------------------------
    od = orders_clean.copy()

    subtotal = pd.to_numeric(od["subtotal_amount"], errors="coerce")
    fee = pd.to_numeric(od["delivery_fee"], errors="coerce")
    order_discount = pd.to_numeric(
        od["discount_amount"], errors="coerce"
    )
    total = pd.to_numeric(od["total_amount"], errors="coerce")

    expected_total = subtotal + fee - order_discount
    valid_numeric = (
        subtotal.notna()
        & fee.notna()
        & order_discount.notna()
        & total.notna()
    )
    mismatch = valid_numeric & (
        (total - expected_total).abs() > 0.01
    )
    invalid_numeric = ~valid_numeric

    result = {
        "total_rows": len(od),
        "invalid_numeric_rows": int(invalid_numeric.sum()),
        "formula_mismatch_rows": int(mismatch.sum()),
        "mismatch_examples": od.loc[
            mismatch, "order_id"
        ].head(10).tolist()
    }
    passed = bool(invalid_numeric.sum() == 0 and mismatch.sum() == 0)

    record_check(
        "V-10", "Formula", "Orders",
        "Apakah total_amount = subtotal_amount + delivery_fee - discount_amount?",
        "All rows numeric and formula-consistent",
        result,
        passed,
        related_rule_id="CR-08"
    )

    # --------------------------------------------------------
    # V-11 — Orders ↔ Order Items reconciliation
    # subtotal_amount = sum(line_amount) per order_id
    # --------------------------------------------------------
    item_amount = pd.to_numeric(
        order_items_clean["line_amount"], errors="coerce"
    )

    item_totals = order_items_clean.assign(
        _line_amount_numeric=item_amount
    ).groupby("order_id", dropna=False).agg(
        items_total=("_line_amount_numeric", "sum"),
        item_count=("order_item_id", "count")
    ).reset_index()

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

    recon["items_total"] = recon["items_total"].fillna(0)
    recon["item_count"] = recon["item_count"].fillna(0)

    recon["subtotal_numeric"] = pd.to_numeric(
        recon["subtotal_amount"], errors="coerce"
    )

    no_items = recon["item_count"].eq(0)
    invalid_subtotal = recon["subtotal_numeric"].isna()
    mismatch = (
        recon["subtotal_numeric"].notna()
        & (
            (recon["subtotal_numeric"] - recon["items_total"]).abs()
            > 0.01
        )
    )

    failed_recon = no_items | invalid_subtotal | mismatch

    result = {
        "total_orders": len(recon),
        "orders_without_items": int(no_items.sum()),
        "invalid_subtotal_rows": int(invalid_subtotal.sum()),
        "subtotal_mismatch_rows": int(mismatch.sum()),
        "failed_order_examples": recon.loc[
            failed_recon, "order_id"
        ].head(10).tolist()
    }
    passed = bool(failed_recon.sum() == 0)

    record_check(
        "V-11", "Reconciliation", "Orders ↔ Order Items",
        "Apakah subtotal_amount sesuai dengan total line_amount setiap order?",
        "Every order has items and item total matches subtotal_amount",
        result,
        passed,
        related_rule_id="CR-08; CR-09; CR-10"
    )

    # --------------------------------------------------------
    # Helper: foreign key validation
    # --------------------------------------------------------
    def foreign_key_check(child_df, child_column, parent_df, parent_column):
        child_values = child_df[child_column]
        parent_values = parent_df[parent_column].dropna()

        invalid = (
            child_values.isna()
            | ~child_values.isin(parent_values)
        )

        return {
            "child_rows": len(child_df),
            "null_foreign_keys": int(child_values.isna().sum()),
            "unmatched_foreign_keys": int(
                (invalid & child_values.notna()).sum()
            ),
            "invalid_key_examples": (
                child_values[invalid].drop_duplicates().head(10).tolist()
            ),
            "passed": bool(invalid.sum() == 0)
        }

    # --------------------------------------------------------
    # V-12 — Orders.customer_id → Customers.customer_id
    # --------------------------------------------------------
    result = foreign_key_check(
        orders_clean, "customer_id",
        customers_clean, "customer_id"
    )

    record_check(
        "V-12", "Foreign Key", "Orders → Customers",
        "Apakah setiap customer_id pada Orders tersedia di Customers?",
        "No null or unmatched customer_id",
        result,
        result["passed"]
    )

    # --------------------------------------------------------
    # V-13 — Orders.restaurant_id → Restaurants.restaurant_id
    # --------------------------------------------------------
    result = foreign_key_check(
        orders_clean, "restaurant_id",
        restaurants_clean, "restaurant_id"
    )

    record_check(
        "V-13", "Foreign Key", "Orders → Restaurants",
        "Apakah setiap restaurant_id pada Orders tersedia di Restaurants?",
        "No null or unmatched restaurant_id",
        result,
        result["passed"]
    )

    # --------------------------------------------------------
    # V-14 — Order Items / Payments → Orders
    # --------------------------------------------------------
    fk_specs = [
        ("Order Items → Orders", order_items_clean, "order_id"),
        ("Payments → Orders", payments_clean, "order_id"),
    ]

    fk_results = {}
    for relationship, child_df, child_column in fk_specs:
        fk_results[relationship] = foreign_key_check(
            child_df, child_column,
            orders_clean, "order_id"
        )

    passed = all(
        result["passed"] for result in fk_results.values()
    )

    record_check(
        "V-14", "Foreign Key",
        "Order Items / Payments → Orders",
        "Apakah order_id pada Order Items dan Payments tersedia di Orders?",
        "No null or unmatched order_id",
        fk_results,
        passed
    )

else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
# F-14 / CR-14: Memastikan riwayat refund tetap dipertahankan
target_orders = [f"O{i:05d}" for i in range(323, 333)]

# Ambil transaksi attempt 2 yang berstatus refunded
raw_refunds = payments_raw[
    payments_raw["order_id"].isin(target_orders)
    & (payments_raw["attempt_number"] == 2)
    & (payments_raw["payment_status"].astype(str).str.strip().str.lower() == "refunded")
].copy()

clean_refunds = payments_clean[
    payments_clean["order_id"].isin(target_orders)
    & (payments_clean["attempt_number"] == 2)
    & (payments_clean["payment_status"].astype(str).str.strip().str.lower() == "refunded")
].copy()

# Pastikan 10 record refund tetap ada setelah cleaning
refund_ids_preserved = (
    "payment_id" in payments_raw.columns
    and "payment_id" in payments_clean.columns
    and set(raw_refunds["payment_id"]).issubset(set(clean_refunds["payment_id"]))
)

refund_count_correct = len(raw_refunds) == 10 and len(clean_refunds) == 10

# Pastikan nominal refund sama dengan pembayaran awal
initial_payments = payments_clean[
    payments_clean["order_id"].isin(target_orders)
    & (payments_clean["attempt_number"] == 1)
    & (payments_clean["payment_status"].astype(str).str.strip().str.lower() == "successful")
]

refund_amounts = clean_refunds.groupby("order_id")["payment_amount"].sum()
initial_amounts = initial_payments.groupby("order_id")["payment_amount"].sum()

amounts_match = (
    refund_amounts.index.isin(initial_amounts.index).all()
    and all(
        refund_amounts[order_id] == initial_amounts[order_id]
        for order_id in refund_amounts.index
    )
)

v14_passed = refund_ids_preserved and refund_count_correct and amounts_match

print("Jumlah refund pada raw:", len(raw_refunds))
print("Jumlah refund pada clean:", len(clean_refunds))
print("Payment ID dipertahankan:", refund_ids_preserved)
print("Jumlah record sesuai:", refund_count_correct)
print("Nominal refund sama dengan pembayaran awal:", amounts_match)
print("Status V-15 F-14:", "PASS" if v14_passed else "FAIL")
Jumlah refund pada raw: 10
Jumlah refund pada clean: 10
Payment ID dipertahankan: True
Jumlah record sesuai: True
Nominal refund sama dengan pembayaran awal: True
Status V-15 F-14: PASS
# F-14 / CR-14: Memastikan riwayat refund tetap dipertahankan
target_orders = [f"O{i:05d}" for i in range(323, 333)]

status_col = "payment_status"
amount_col = "payment_amount"
attempt_col = "attempt_number"

# Pembayaran attempt 2 yang refunded
raw_refunds = payments_raw[
    payments_raw["order_id"].isin(target_orders)
    & (payments_raw[attempt_col] == 2)
    & (payments_raw[status_col].astype(str).str.strip().str.lower() == "refunded")
].copy()

clean_refunds = payments_clean[
    payments_clean["order_id"].isin(target_orders)
    & (payments_clean[attempt_col] == 2)
    & (payments_clean[status_col].astype(str).str.strip().str.lower() == "refunded")
].copy()

# Pembayaran awal attempt 1 yang successful
initial_payments = payments_clean[
    payments_clean["order_id"].isin(target_orders)
    & (payments_clean[attempt_col] == 1)
    & (payments_clean[status_col].astype(str).str.strip().str.lower() == "successful")
].copy()

# Pemeriksaan pelestarian payment ID
refund_ids_preserved = set(raw_refunds["payment_id"]).issubset(
    set(clean_refunds["payment_id"])
)

# Pemeriksaan jumlah refund
refund_count_correct = len(raw_refunds) == 10 and len(clean_refunds) == 10

# Perbandingan nominal pembayaran awal dan refund per order
refund_amounts = clean_refunds.groupby("order_id")[amount_col].sum()
initial_amounts = initial_payments.groupby("order_id")[amount_col].sum()

all_orders_have_both_records = (
    set(target_orders).issubset(set(refund_amounts.index))
    and set(target_orders).issubset(set(initial_amounts.index))
)

amounts_match = (
    all_orders_have_both_records
    and all(
        refund_amounts[order_id] == initial_amounts[order_id]
        for order_id in target_orders
    )
)

v15_f14_passed = (
    refund_ids_preserved
    and refund_count_correct
    and amounts_match
)

print("--- Hasil Validasi F-14 / CR-14 ---")
print("Refund pada raw:", len(raw_refunds))
print("Refund pada clean:", len(clean_refunds))
print("Payment ID dipertahankan:", refund_ids_preserved)
print("Jumlah record sesuai:", refund_count_correct)
print("Seluruh order memiliki pembayaran awal dan refund:", all_orders_have_both_records)
print("Nominal refund sama dengan pembayaran awal:", amounts_match)
print("Status:", "PASS" if v15_f14_passed else "FAIL")
--- Hasil Validasi F-14 / CR-14 ---
Refund pada raw: 10
Refund pada clean: 10
Payment ID dipertahankan: True
Jumlah record sesuai: True
Seluruh order memiliki pembayaran awal dan refund: True
Nominal refund sama dengan pembayaran awal: True
Status: PASS
# F-08 / CR-08: Memastikan promo_code kosong yang legitimate tetap dipertahankan

raw_blank_promo = orders_raw[
    orders_raw["promo_code"].isna()
    | (orders_raw["promo_code"].astype(str).str.strip() == "")
].copy()

clean_blank_promo = orders_clean[
    orders_clean["promo_code"].isna()
    | (orders_clean["promo_code"].astype(str).str.strip() == "")
].copy()

raw_blank_order_ids = set(raw_blank_promo["order_id"])
clean_blank_order_ids = set(clean_blank_promo["order_id"])

f08_preserved = raw_blank_order_ids.issubset(clean_blank_order_ids)
f08_count_correct = len(raw_blank_promo) == 176

v15_passed = (
    f08_preserved
    and f08_count_correct
    and v15_f14_passed
)

print("--- Hasil Validasi V-15: KEEP Findings ---")
print("F-08 | Promo code kosong pada raw:", len(raw_blank_promo))
print("F-08 | Order tanpa promo tetap dipertahankan:", f08_preserved)
print("F-08 | Jumlah sesuai temuan:", f08_count_correct)
print("F-14 | Riwayat refund dipertahankan:", v15_f14_passed)
print("Status V-15:", "PASS" if v15_passed else "FAIL")
--- Hasil Validasi V-15: KEEP Findings ---
F-08 | Promo code kosong pada raw: 176
F-08 | Order tanpa promo tetap dipertahankan: True
F-08 | Jumlah sesuai temuan: True
F-14 | Riwayat refund dipertahankan: True
Status V-15: PASS
# V-15: Validasi bahwa KEEP findings tetap dipertahankan

record_check(
    "V-15",
    "Judgment",
    "KEEP findings: Orders dan Payments",
    "Apakah temuan berstatus KEEP pada F-08 dan F-14 tetap dipertahankan setelah proses cleaning?",
    "176 record promo_code kosong tetap dipertahankan; 10 record refund pada order O00323–O00332 tetap dipertahankan beserta payment ID dan nominalnya.",
    (
        f"F-08: {len(raw_blank_promo)} record promo_code kosong pada raw; "
        f"preserved={f08_preserved}. "
        f"F-14: {len(raw_refunds)} refund pada raw dan {len(clean_refunds)} pada clean; "
        f"payment ID preserved={refund_ids_preserved}; "
        f"nominal match={amounts_match}."
    ),
    v15_passed,
    related_rule_id="CR-08; CR-14",
    notes="Memastikan legitimate missing values dan refund audit trail berstatus KEEP tidak dihapus atau diubah tanpa bukti."
)

validation_df = pd.DataFrame(validation_results)
display(validation_df.tail())
check_id validation_level dataset_relationship validation_question expected_criteria actual_result_metric status related_rule_id notes
10 V-11 Reconciliation Orders ↔ Order Items Apakah subtotal_amount sesuai dengan total lin... Every order has items and item total matches s... {'total_orders': 332, 'orders_without_items': ... PASS CR-08; CR-09; CR-10
11 V-12 Foreign Key Orders → Customers Apakah setiap customer_id pada Orders tersedia... No null or unmatched customer_id {'child_rows': 332, 'null_foreign_keys': 0, 'u... PASS
12 V-13 Foreign Key Orders → Restaurants Apakah setiap restaurant_id pada Orders tersed... No null or unmatched restaurant_id {'child_rows': 332, 'null_foreign_keys': 0, 'u... PASS
13 V-14 Foreign Key Order Items / Payments → Orders Apakah order_id pada Order Items dan Payments ... No null or unmatched order_id {'Order Items → Orders': {'child_rows': 944, '... PASS
14 V-15 Judgment KEEP findings: Orders dan Payments Apakah temuan berstatus KEEP pada F-08 dan F-1... 176 record promo_code kosong tetap dipertahank... F-08: 176 record promo_code kosong pada raw; p... PASS CR-08; CR-14 Memastikan legitimate missing values dan refun...
validation_df = pd.DataFrame(validation_results)

display(
    validation_df[
        validation_df["check_id"].isin([
            "V-06", "V-07", "V-08", "V-09",
            "V-10", "V-11", "V-12", "V-13", "V-14","V-15"
        ])
    ]
)
check_id validation_level dataset_relationship validation_question expected_criteria actual_result_metric status related_rule_id notes
5 V-06 Category Customers Apakah customer_segment konsisten dan tidak ko... No null/blank values, whitespace, or inconsist... {'null_count': 0, 'blank_count': 0, 'leading_t... PASS CR-02
6 V-07 Category Orders Apakah order_status konsisten dan tidak kosong? No null/blank values, whitespace, or inconsist... {'null_count': 0, 'blank_count': 0, 'leading_t... PASS CR-03
7 V-08 Category Payments / Restaurants / Order Items Apakah kategori pada ketiga dataset konsisten? No null/blank values, whitespace, or inconsist... {'Payments': {'null_count': 0, 'blank_count': ... PASS CR-04; CR-05; CR-06; CR-07
8 V-09 Formula Order Items Apakah line_amount = quantity × unit_price − l... All rows numeric and formula-consistent {'total_rows': 944, 'invalid_numeric_rows': 0,... PASS CR-09; CR-10
9 V-10 Formula Orders Apakah total_amount = subtotal_amount + delive... All rows numeric and formula-consistent {'total_rows': 332, 'invalid_numeric_rows': 0,... PASS CR-08
10 V-11 Reconciliation Orders ↔ Order Items Apakah subtotal_amount sesuai dengan total lin... Every order has items and item total matches s... {'total_orders': 332, 'orders_without_items': ... PASS CR-08; CR-09; CR-10
11 V-12 Foreign Key Orders → Customers Apakah setiap customer_id pada Orders tersedia... No null or unmatched customer_id {'child_rows': 332, 'null_foreign_keys': 0, 'u... PASS
12 V-13 Foreign Key Orders → Restaurants Apakah setiap restaurant_id pada Orders tersed... No null or unmatched restaurant_id {'child_rows': 332, 'null_foreign_keys': 0, 'u... PASS
13 V-14 Foreign Key Order Items / Payments → Orders Apakah order_id pada Order Items dan Payments ... No null or unmatched order_id {'Order Items → Orders': {'child_rows': 944, '... PASS
14 V-15 Judgment KEEP findings: Orders dan Payments Apakah temuan berstatus KEEP pada F-08 dan F-1... 176 record promo_code kosong tetap dipertahank... F-08: 176 record promo_code kosong pada raw; p... PASS CR-08; CR-14 Memastikan legitimate missing values dan refun...

13C. KEEP Preservation Check — EDIT + RUN

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

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

# ============================================================
# EDIT AREA 3C — V-15 KEEP PRESERVATION
# ============================================================

if DATA_READY:

    # TODO: isi dengan ID finding yang benar-benar berstatus KEEP
    # berdasarkan Findings Log / Cleaning Rulebook.
    keep_findings = [
        # Contoh format: "F-XX"
    ]

    # Catat validasi awal.
    # Jangan otomatis PASS jika daftar KEEP belum diisi
    # atau belum ada evidence pembanding.
    keep_evidence = {
        "keep_findings": keep_findings,
        "keep_findings_count": len(keep_findings),
        "evidence_available": False,
        "verification_status": (
            "PENDING — daftar finding KEEP dan evidence "
            "preservasi belum dilengkapi."
        )
    }

    record_check(
        "V-15", "Preservation", "KEEP Findings",
        "Apakah record yang diputuskan KEEP tetap dipertahankan "
        "dan tidak diubah tanpa evidence yang memadai?",
        "All KEEP findings preserved with supporting evidence",
        keep_evidence,
        False,
        notes=(
            "Belum dapat dinyatakan PASS sebelum daftar finding "
            "KEEP, record terkait, dan evidence preservasi diverifikasi."
        )
    )

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

Validation Results Table

validation_df = pd.DataFrame(validation_results)

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

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

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

    if missing_validation_ids:
        print("VALIDATION GATE: PENDING")
        print("Missing required checks:", ", ".join(missing_validation_ids))
    elif (validation_df["status"] == "FAIL").any():
        print("VALIDATION GATE: FAIL")
        print("→ Review FAIL sebelum finalisasi.")
    else:
        print("VALIDATION GATE: PASS")
check_id validation_level dataset_relationship validation_question expected_criteria actual_result_metric status related_rule_id notes
0 V-01 Schema All clean datasets Apakah seluruh required column masih tersedia? Required columns available {'Customers': {'missing_columns': [], 'passed'... PASS Seluruh required columns tersedia.
1 V-02 Key Customers Apakah customer_id valid sesuai grain customer? No null key; uniqueness sesuai grain {'dataset': 'Customers', 'key_cols': 'customer... PASS CR-01
2 V-03 Key Orders Apakah order_id valid sesuai grain order? No null key; one row per order {'dataset': 'Orders', 'key_cols': 'order_id', ... PASS
3 V-04 Key Order Items Apakah order_item_id / candidate business key ... No invalid duplicate key {'dataset': 'Order Items', 'key_cols': 'order_... PASS
4 V-05 Key Payments Apakah payment_id unique tanpa menghapus repea... payment_id unique; repeated order_id allowed b... {'dataset': 'Payments', 'key_cols': 'payment_i... PASS CR-04 Pemeriksaan menggunakan payment_id. Repeated o...
5 V-06 Category Customers Apakah customer_segment konsisten dan tidak ko... No null/blank values, whitespace, or inconsist... {'null_count': 0, 'blank_count': 0, 'leading_t... PASS CR-02
6 V-07 Category Orders Apakah order_status konsisten dan tidak kosong? No null/blank values, whitespace, or inconsist... {'null_count': 0, 'blank_count': 0, 'leading_t... PASS CR-03
7 V-08 Category Payments / Restaurants / Order Items Apakah kategori pada ketiga dataset konsisten? No null/blank values, whitespace, or inconsist... {'Payments': {'null_count': 0, 'blank_count': ... PASS CR-04; CR-05; CR-06; CR-07
8 V-09 Formula Order Items Apakah line_amount = quantity × unit_price − l... All rows numeric and formula-consistent {'total_rows': 944, 'invalid_numeric_rows': 0,... PASS CR-09; CR-10
9 V-10 Formula Orders Apakah total_amount = subtotal_amount + delive... All rows numeric and formula-consistent {'total_rows': 332, 'invalid_numeric_rows': 0,... PASS CR-08
10 V-11 Reconciliation Orders ↔ Order Items Apakah subtotal_amount sesuai dengan total lin... Every order has items and item total matches s... {'total_orders': 332, 'orders_without_items': ... PASS CR-08; CR-09; CR-10
11 V-12 Foreign Key Orders → Customers Apakah setiap customer_id pada Orders tersedia... No null or unmatched customer_id {'child_rows': 332, 'null_foreign_keys': 0, 'u... PASS
12 V-13 Foreign Key Orders → Restaurants Apakah setiap restaurant_id pada Orders tersed... No null or unmatched restaurant_id {'child_rows': 332, 'null_foreign_keys': 0, 'u... PASS
13 V-14 Foreign Key Order Items / Payments → Orders Apakah order_id pada Order Items dan Payments ... No null or unmatched order_id {'Order Items → Orders': {'child_rows': 944, '... PASS
14 V-15 Judgment KEEP findings: Orders dan Payments Apakah temuan berstatus KEEP pada F-08 dan F-1... 176 record promo_code kosong tetap dipertahank... F-08: 176 record promo_code kosong pada raw; p... PASS CR-08; CR-14 Memastikan legitimate missing values dan refun...
status
PASS    15
Name: count, dtype: int64
VALIDATION GATE: PASS
# ============================================================
# DISPLAY — ALL VALIDATION RESULTS (V-01 s.d. V-15)
# ============================================================

validation_df = pd.DataFrame(validation_results)

display(
    validation_df.sort_values("check_id").reset_index(drop=True)
)
check_id validation_level dataset_relationship validation_question expected_criteria actual_result_metric status related_rule_id notes
0 V-01 Schema All clean datasets Apakah seluruh required column masih tersedia? Required columns available {'Customers': {'missing_columns': [], 'passed'... PASS Seluruh required columns tersedia.
1 V-02 Key Customers Apakah customer_id valid sesuai grain customer? No null key; uniqueness sesuai grain {'dataset': 'Customers', 'key_cols': 'customer... PASS CR-01
2 V-03 Key Orders Apakah order_id valid sesuai grain order? No null key; one row per order {'dataset': 'Orders', 'key_cols': 'order_id', ... PASS
3 V-04 Key Order Items Apakah order_item_id / candidate business key ... No invalid duplicate key {'dataset': 'Order Items', 'key_cols': 'order_... PASS
4 V-05 Key Payments Apakah payment_id unique tanpa menghapus repea... payment_id unique; repeated order_id allowed b... {'dataset': 'Payments', 'key_cols': 'payment_i... PASS CR-04 Pemeriksaan menggunakan payment_id. Repeated o...
5 V-06 Category Customers Apakah customer_segment konsisten dan tidak ko... No null/blank values, whitespace, or inconsist... {'null_count': 0, 'blank_count': 0, 'leading_t... PASS CR-02
6 V-07 Category Orders Apakah order_status konsisten dan tidak kosong? No null/blank values, whitespace, or inconsist... {'null_count': 0, 'blank_count': 0, 'leading_t... PASS CR-03
7 V-08 Category Payments / Restaurants / Order Items Apakah kategori pada ketiga dataset konsisten? No null/blank values, whitespace, or inconsist... {'Payments': {'null_count': 0, 'blank_count': ... PASS CR-04; CR-05; CR-06; CR-07
8 V-09 Formula Order Items Apakah line_amount = quantity × unit_price − l... All rows numeric and formula-consistent {'total_rows': 944, 'invalid_numeric_rows': 0,... PASS CR-09; CR-10
9 V-10 Formula Orders Apakah total_amount = subtotal_amount + delive... All rows numeric and formula-consistent {'total_rows': 332, 'invalid_numeric_rows': 0,... PASS CR-08
10 V-11 Reconciliation Orders ↔ Order Items Apakah subtotal_amount sesuai dengan total lin... Every order has items and item total matches s... {'total_orders': 332, 'orders_without_items': ... PASS CR-08; CR-09; CR-10
11 V-12 Foreign Key Orders → Customers Apakah setiap customer_id pada Orders tersedia... No null or unmatched customer_id {'child_rows': 332, 'null_foreign_keys': 0, 'u... PASS
12 V-13 Foreign Key Orders → Restaurants Apakah setiap restaurant_id pada Orders tersed... No null or unmatched restaurant_id {'child_rows': 332, 'null_foreign_keys': 0, 'u... PASS
13 V-14 Foreign Key Order Items / Payments → Orders Apakah order_id pada Order Items dan Payments ... No null or unmatched order_id {'Order Items → Orders': {'child_rows': 944, '... PASS
14 V-15 Judgment KEEP findings: Orders dan Payments Apakah temuan berstatus KEEP pada F-08 dan F-1... 176 record promo_code kosong tetap dipertahank... F-08: 176 record promo_code kosong pada raw; p... PASS CR-08; CR-14 Memastikan legitimate missing values dan refun...

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 PASS pada PM1 Data Readiness Gate karena "
    "Raw Integrity Check menunjukkan bahwa seluruh dataset raw "
    "(Customers, Orders, Order Items, Payments, dan Restaurants) "
    "tidak berubah. Seluruh validation ID yang diwajibkan telah "
    "tersedia dan berstatus PASS, tanpa validation FAIL. "
    "Identity gate juga telah terpenuhi. Dengan demikian, dataset "
    "memenuhi kriteria data readiness berdasarkan pemeriksaan "
    "integritas raw, kelengkapan evidence validasi, dan identity gate."
)

print("FINAL JUDGMENT RATIONALE:")
print(FINAL_JUDGMENT_RATIONALE)

PM1 DATA READINESS GATE: PASS
FINAL JUDGMENT RATIONALE:
Dataset dinyatakan PASS pada PM1 Data Readiness Gate karena Raw Integrity Check menunjukkan bahwa seluruh dataset raw (Customers, Orders, Order Items, Payments, dan Restaurants) tidak berubah. Seluruh validation ID yang diwajibkan telah tersedia dan berstatus PASS, tanpa validation FAIL. Identity gate juga telah terpenuhi. Dengan demikian, dataset memenuhi kriteria data readiness berdasarkan pemeriksaan integritas raw, kelengkapan evidence validasi, dan identity gate.

15. Export Clean Data — RUN SETELAH FINAL GATE PASS

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

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

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

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

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

# Hapus tanda # pada baris berikut HANYA setelah FINAL_GATE: PASS
export_clean_data()
CLEAN PACKAGE SAVE: PASS
Saved to: C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
True

16. Export Evidence — RUN SETELAH EVIDENCE LENGKAP

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

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

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

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

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

# Hapus tanda # pada baris berikut setelah evidence sudah diisi:
export_evidence()
EVIDENCE EXPORT: PASS
Saved to: C:\NaurahFile\Data Analyst\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
```