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

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

IDENTITY_GATE_PASS = bool(name_ok and variant_ok)

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

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

Environment Smoke Test — RUN

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

print("pandas :", pd.__version__)

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

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

if not EXCEL_ENGINE_AVAILABLE:
    print("ENVIRONMENT CHECK: NEEDS FIX")
    print("→ File XLSX membutuhkan openpyxl. Aktifkan environment sesuai setup Sesi 1.")
else:
    print("ENVIRONMENT CHECK: PASS")
pandas : 3.0.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:\Bootcamp\Bootcamp_Data_Analyst\data\raw\pm1...
1 orders swiftbite_orders.xlsx True C:\Bootcamp\Bootcamp_Data_Analyst\data\raw\pm1...
2 order_items swiftbite_order_items.csv True C:\Bootcamp\Bootcamp_Data_Analyst\data\raw\pm1...
3 payments swiftbite_payments.txt True C:\Bootcamp\Bootcamp_Data_Analyst\data\raw\pm1...
4 restaurants swiftbite_restaurants.csv True C:\Bootcamp\Bootcamp_Data_Analyst\data\raw\pm1...
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_method", 
        "payment_status", "payment_amount", "payment_timestamp", "attempt_number"
    },
    "Restaurants": {
        "restaurant_id", "restaurant_name", "cuisine_type",
        "restaurant_zone", "price_tier", "onboarding_date","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 7 7 PASS
4 Restaurants 7 7 PASS
SCHEMA GATE: PASS
DATA READY GATE: PASS

#### Penemuan:
terdapat FAIL pada tabel Customers, Payments, Restaurant

solusi: memeriksa kolom pada tabel2 tersebut, dan menyesuaikan kembali ke syntax

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=(169, 7) ===
customer_id full_name email phone_number signup_date customer_zone customer_segment
0 C0001 Tio Hidayat tiohidayat1@example.com 082376113984 2025-12-03 East Loyal
1 C0002 Eka Hidayat ekahidayat2@example.com 087534238072 2025-08-08 Central Loyal
2 C0003 Yani Permata yanipermata3@example.com 086096547416 2025-12-06 West Loyal
=== Orders | shape=(350, 10) ===
order_id customer_id restaurant_id order_timestamp order_status promo_code subtotal_amount delivery_fee discount_amount total_amount
0 O00001 C0080 R0018 2026-01-19 03:32:00 completed NaN 54000 18000 10000 62000
1 O00002 C0087 R0023 2026-01-10 18:34:00 completed NEWUSER 130000 18000 15000 133000
2 O00003 C0094 R0003 2026-01-12 07:39:00 completed HEMAT15 238000 10000 15000 233000
=== Order Items | shape=(974, 9) ===
order_item_id order_id line_number item_name item_category quantity unit_price line_discount line_amount
0 OI00001 O00001 1 Pudding Dessert 1 16000 2000 14000
1 OI00002 O00001 2 Juice Drink 2 20000 0 40000
2 OI00003 O00002 1 Nasi Ayam Main 4 28000 0 112000
=== Payments | shape=(381, 7) ===
payment_id order_id payment_method payment_status payment_amount payment_timestamp attempt_number
0 P00001 O00001 e_wallet successful 62000 2026-01-19 03:35:00 1
1 P00002 O00002 bank_transfer successful 133000 2026-01-10 18:37:00 1
2 P00003 O00003 credit_card successful 233000 2026-01-12 07:42:00 1
=== Restaurants | shape=(25, 7) ===
restaurant_id restaurant_name cuisine_type restaurant_zone price_tier onboarding_date active_status
0 R0001 SwiftBite Partner 01 Asian Central Premium 2024-09-28 active
1 R0002 SwiftBite Partner 02 Western Central Mid 2024-03-15 active
2 R0003 SwiftBite Partner 03 Fast Food South Budget 2025-03-04 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 169 7 [customer_id, full_name, email, phone_number, ...
1 Orders 350 10 [order_id, customer_id, restaurant_id, order_t...
2 Order Items 974 9 [order_item_id, order_id, line_number, item_na...
3 Payments 381 7 [payment_id, order_id, payment_method, payment...
4 Restaurants 25 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 169 168 0 2 False
1 Orders order_id 350 350 0 0 True
2 Order Items order_item_id 974 974 0 0 True
3 Order Items — candidate business key order_id, line_number 974 974 0 0 True
4 Payments payment_id 381 381 0 0 True
5 Restaurants restaurant_id 25 25 0 0 True

Repeated Reference-Key Review

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

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

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

    print("Payments cardinality")
    display(payment_cardinality.head(10))
Order Items cardinality
order_id item_rows
346 O00347 5
22 O00023 5
336 O00337 5
140 O00141 5
139 O00140 5
252 O00253 5
274 O00275 5
91 O00092 5
172 O00173 5
2 O00003 4
Payments cardinality
order_id payment_rows
86 O00087 2
161 O00162 2
65 O00066 2
126 O00127 2
76 O00077 2
143 O00144 2
147 O00148 2
349 O00350 2
165 O00166 2
80 O00081 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.

Missing / Placeholder

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 169 7 2 1 0
1 Orders 350 10 0 0 189
2 Order Items 974 9 0 0 0
3 Payments 381 7 0 0 0
4 Restaurants 25 7 0 0 0

Evidence Result for F-01

customers = raw_tables["Customers"]

duplicate_customers = customers[
    customers["customer_id"].duplicated(keep=False)
].sort_values("customer_id")

display(duplicate_customers)
customer_id full_name email phone_number signup_date customer_zone customer_segment
30 C0031 Dimas Pratama dimaspratama31@example.com 085368771270 2025-10-30 Central Loyal
168 C0031 Dimas Pratama dimaspratama31@example.com 085368771270 2025-10-30 Central Loyal

#### Overall profile setiap tabel
Customers: memiliki 169 baris, dgn 7 kolom, dgn duplikat baris sebanyak 2, sehingga hanya perlu dihapus 1 baris, dan tidak memiliki missing

Orders: memiliki 350 baris, dgn 10 kolom, dgn duplikat baris sebanyak 0 dan memiliki missing sebanyak 189

order_items: memiliki 974 baris, dgn 9 kolom, dgn duplikat baris sebanyak 0 dan tidak memiliki missing

Payments: memiliki 381 baris, dgn 7 kolom, dgn duplikat baris sebanyak 0 dan tidak memiliki missing

Restaurants: memiliki 25 baris, dgn 7 kolom, dgn duplikat baris sebanyak 0 an tidak memiliki missing

Column-Level Profiling

Profiling Dataset Customers

# 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 168 [C0001, C0002, C0003, C0004, C0005]
1 full_name str 0 0.0 121 [Tio Hidayat, Eka Hidayat, Yani Permata, Galih...
2 email str 0 0.0 168 [tiohidayat1@example.com, ekahidayat2@example....
3 phone_number str 0 0.0 168 [082376113984, 087534238072, 086096547416, 087...
4 signup_date str 0 0.0 126 [2025-12-03, 2025-08-08, 2025-12-06, 2025-10-2...
5 customer_zone str 0 0.0 5 [East, Central, West, South, North]
6 customer_segment str 0 0.0 4 [Loyal, Regular, New, NEW ]

### Temuan
Kolom yang dicek konsistensi : customer_segment

signup_date tipe data berupa string

Mengecek format tanggal

orders = raw_tables["Orders"]

display(
    orders[["order_timestamp"]]
    .assign(
        format_check=orders["order_timestamp"].astype(str).str.strip()
    )
)
order_timestamp format_check
0 2026-01-19 03:32:00 2026-01-19 03:32:00
1 2026-01-10 18:34:00 2026-01-10 18:34:00
2 2026-01-12 07:39:00 2026-01-12 07:39:00
3 2026-02-03 17:42:00 2026-02-03 17:42:00
4 2026-01-17 09:22:00 2026-01-17 09:22:00
... ... ...
345 2026-03-08 09:10:00 2026-03-08 09:10:00
346 2026-02-07 08:48:00 2026-02-07 08:48:00
347 2026-02-11 09:22:00 2026-02-11 09:22:00
348 2026-02-18 23:29:00 2026-02-18 23:29:00
349 2026-03-02 17:37:00 2026-03-02 17:37:00

350 rows × 2 columns

orders = raw_tables["Orders"]

s = orders["order_timestamp"].dropna().astype(str).str.strip()

date_format_check = pd.DataFrame({
    "date_value": s,
    "format_pattern": s.apply(
        lambda x:
            "YYYY-MM-DD" if pd.Series([x]).str.match(r"^\d{4}-\d{2}-\d{2}").iloc[0]
            else "DD/MM/YYYY or MM/DD/YYYY" if pd.Series([x]).str.match(r"^\d{1,2}/\d{1,2}/\d{4}").iloc[0]
            else "Other format"
    )
})

display(date_format_check)
date_value format_pattern
0 2026-01-19 03:32:00 YYYY-MM-DD
1 2026-01-10 18:34:00 YYYY-MM-DD
2 2026-01-12 07:39:00 YYYY-MM-DD
3 2026-02-03 17:42:00 YYYY-MM-DD
4 2026-01-17 09:22:00 YYYY-MM-DD
... ... ...
345 2026-03-08 09:10:00 YYYY-MM-DD
346 2026-02-07 08:48:00 YYYY-MM-DD
347 2026-02-11 09:22:00 YYYY-MM-DD
348 2026-02-18 23:29:00 YYYY-MM-DD
349 2026-03-02 17:37:00 YYYY-MM-DD

350 rows × 2 columns

Profiling Dataset Orders

# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
    display(column_profile(orders_raw))
column dtype null_count null_pct unique_count sample_values
0 order_id str 0 0.00 350 [O00001, O00002, O00003, O00004, O00005]
1 customer_id str 0 0.00 24 [C0080, C0087, C0094, C0101, C0108]
2 restaurant_id str 0 0.00 5 [R0018, R0023, R0003, R0008, R0013]
3 order_timestamp str 0 0.00 349 [2026-01-19 03:32:00, 2026-01-10 18:34:00, 202...
4 order_status str 0 0.00 3 [completed, COMPLETED , cancelled]
5 promo_code str 189 0.54 3 [NEWUSER, HEMAT15, SAVE10]
6 subtotal_amount int64 0 0.00 187 [54000, 130000, 238000, 78000, 123000]
7 delivery_fee int64 0 0.00 5 [18000, 10000, 8000, 12000, 15000]
8 discount_amount int64 0 0.00 4 [10000, 15000, 0, 5000]
9 total_amount int64 0 0.00 198 [62000, 133000, 233000, 76000, 120000]

### Temuan
Kolom yang dicek konsistensi : order_status

order_timestamp bertipe str

Profiling Dataset Order Items

# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
    display(column_profile(order_items_raw))
column dtype null_count null_pct unique_count sample_values
0 order_item_id str 0 0.0 974 [OI00001, OI00002, OI00003, OI00004, OI00005]
1 order_id str 0 0.0 350 [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 [Pudding, Juice, Nasi Ayam, Fries, Milk Tea]
4 item_category str 0 0.0 5 [Dessert, Drink, Main, Snack, MAIN ]
5 quantity str 0 0.0 6 [1, 2, 4, 3, 1 pcs]
6 unit_price int64 0 0.0 11 [16000, 20000, 28000, 18000, 22000]
7 line_discount int64 0 0.0 3 [2000, 0, 5000]
8 line_amount int64 0 0.0 77 [14000, 40000, 112000, 18000, 54000]

### Temuan
Kolom yang dicek konsistensi : quantity

quantity bertipe str

Profiling Dataset Payments

# Ganti object untuk memeriksa dataset lain.
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 381 [P00001, P00002, P00003, P00004, P00005]
1 order_id str 0 0.0 350 [O00001, O00002, O00003, O00004, O00005]
2 payment_method str 0 0.0 6 [e_wallet, bank_transfer, credit_card, cash, C...
3 payment_status str 0 0.0 3 [successful, failed, refunded]
4 payment_amount int64 0 0.0 198 [62000, 133000, 233000, 76000, 120000]
5 payment_timestamp str 0 0.0 380 [2026-01-19 03:35:00, 2026-01-10 18:37:00, 202...
6 attempt_number int64 0 0.0 2 [1, 2]

### Temuan
Kolom yang dicek konsistensi : payment_method

payment_timestamp bertipe str

Profiling Dataset Restaurants

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

### Temuan
Kolom yang dicek konsistensi : price_tier

onboarding_date bertipe str

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

Profiling Konsistensi kategori dari Dataset Customers

display(category_review(customers_raw, "customer_segment"))
customer_segment count pct
0 Regular 69 0.4083
1 New 53 0.3136
2 Loyal 45 0.2663
3 NEW 2 0.0118

### Temuan
kategori kolom cutomer_segmen terdapat ketidakkonsistenan pada New

solusi: kata NEW akan diubah menjadi New

Profiling Konsistensi kategori dari Dataset Orders

display(category_review(orders_raw, "order_status"))
order_status count pct
0 completed 316 0.9029
1 cancelled 32 0.0914
2 COMPLETED 2 0.0057

### Temuan
kategori kolom order_status terdapat ketidakkonsistenan pada completed

solusi: kata COMPLETED akan diubah menjadi completed

Profiling Konsistensi kategori dari Dataset Order Items

display(category_review(order_items_raw, "quantity"))
quantity count pct
0 3 340 0.3491
1 2 328 0.3368
2 1 254 0.2608
3 4 50 0.0513
4 1 pcs 1 0.0010
5 3 pcs 1 0.0010

### Temuan
kategori kolom quantity terdapat ketidakkonsistenan pada isi data

solusi: karena quantity bersifat metrik yang harusnya dapat diagregasi, seharusnya tipe data ini adalah int64, namun terdapat ketidak konsistenan di mana trdpat penyebutan satuan, yaitun pcs sehingga pandas membaca tipe kolom tsb menjadi string

Profiling Konsistensi kategori dari Dataset Payments

display(category_review(payments_raw, "payment_method"))
payment_method count pct
0 cash 97 0.2546
1 bank_transfer 95 0.2493
2 e_wallet 94 0.2467
3 credit_card 93 0.2441
4 CREDIT CARD 1 0.0026
5 CASH 1 0.0026

### Temuan
kategori kolom payment_method terdapat ketidakkonsistenan pada credit_card dan cash

solusi: kata CREDIT CARD dan CASH akan diubah menjadi credit_card dan cash

Profiling Konsistensi kategori dari Dataset Restaurants

display(category_review(restaurants_raw, "price_tier"))
price_tier count pct
0 Budget 12 0.48
1 Mid 7 0.28
2 Premium 4 0.16
3 MID 2 0.08

### Temuan
kategori kolom price_tier terdapat ketidakkonsistenan pada Mid

solusi: kata MID akan diubah menjadi Mid

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

Pengecekan Outlier Dataset Orders

display(numeric_profile(orders_raw))
count mean std min 25% 50% 75% max
subtotal_amount 350.0 147197.142857 86391.548807 4000.0 89000.0 140000.0 190000.0 1096000.0
delivery_fee 350.0 12462.857143 3504.813368 8000.0 10000.0 12000.0 15000.0 18000.0
discount_amount 350.0 5971.428571 5770.122206 0.0 0.0 5000.0 10000.0 15000.0
total_amount 350.0 153688.571429 86437.201715 7000.0 99000.0 143500.0 195000.0 1098000.0
Q1 = orders_raw["subtotal_amount"].quantile(0.25)
Q3 = orders_raw["subtotal_amount"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = orders_raw[
    orders_raw["subtotal_amount"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "subtotal_amount"]])
Batas atas: 341500.0
Jumlah potential outlier: 3
order_id subtotal_amount
22 O00023 395000
83 O00084 1096000
110 O00111 377000

### Temuan
terdapat subtotal_amount yang diindikasikan adanya nilai outlier karna melebihi batas atas yang sebesar 341500

Q1 = orders_raw["delivery_fee"].quantile(0.25)
Q3 = orders_raw["delivery_fee"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = orders_raw[
    orders_raw["delivery_fee"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "delivery_fee"]])
Batas atas: 22500.0
Jumlah potential outlier: 0
order_id delivery_fee
Q1 = orders_raw["discount_amount"].quantile(0.25)
Q3 = orders_raw["discount_amount"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = orders_raw[
    orders_raw["discount_amount"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "discount_amount"]])
Batas atas: 25000.0
Jumlah potential outlier: 0
order_id discount_amount
Q1 = orders_raw["total_amount"].quantile(0.25)
Q3 = orders_raw["total_amount"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = orders_raw[
    orders_raw["total_amount"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "total_amount"]])
Batas atas: 339000.0
Jumlah potential outlier: 4
order_id total_amount
22 O00023 395000
83 O00084 1098000
110 O00111 387000
274 O00275 344000

### Temuan
terdapat otal_amount yang diindikasikan adanya nilai outlier karna melebihi batas atas yang sebesar 339000

Pengecekan Outlier Dataset Order Items

display(numeric_profile(order_items_raw))
count mean std min 25% 50% 75% max
line_number 974.0 2.040041 0.985735 1.0 1.0 2.0 3.0 5.0
unit_price 974.0 24792.607803 17136.058721 9000.0 18000.0 22000.0 28000.0 488000.0
line_discount 974.0 1436.344969 1969.331507 0.0 0.0 0.0 2000.0 5000.0
line_amount 974.0 52886.036961 41813.346686 4000.0 28000.0 48000.0 66750.0 976000.0
Q1 = order_items_raw["line_number"].quantile(0.25)
Q3 = order_items_raw["line_number"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = order_items_raw[
    order_items_raw["line_number"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "line_number"]])
Batas atas: 6.0
Jumlah potential outlier: 0
order_id line_number
Q1 = order_items_raw["unit_price"].quantile(0.25)
Q3 = order_items_raw["unit_price"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = order_items_raw[
    order_items_raw["unit_price"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "unit_price"]])
Batas atas: 43000.0
Jumlah potential outlier: 1
order_id unit_price
232 O00084 488000

### Temuan
terdapat unit_price yang diindikasikan adanya nilai outlier karna melebihi batas atas yang sebesar 43000

Q1 = order_items_raw["line_discount"].quantile(0.25)
Q3 = order_items_raw["line_discount"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = order_items_raw[
    order_items_raw["line_discount"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "line_discount"]])
Batas atas: 5000.0
Jumlah potential outlier: 0
order_id line_discount
Q1 = order_items_raw["line_amount"].quantile(0.25)
Q3 = order_items_raw["line_amount"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = order_items_raw[
    order_items_raw["line_amount"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "line_amount"]])
Batas atas: 124875.0
Jumlah potential outlier: 31
order_id line_amount
23 O00009 126000
56 O00021 126000
75 O00028 166000
90 O00033 126000
232 O00084 976000
268 O00097 126000
297 O00111 168000
299 O00111 126000
476 O00173 126000
548 O00198 126000
551 O00200 166000
571 O00208 126000
573 O00208 126000
603 O00220 126000
609 O00222 126000
618 O00226 126000
633 O00231 126000
642 O00234 126000
661 O00241 168000
662 O00241 126000
688 O00250 126000
689 O00250 126000
691 O00251 126000
790 O00285 126000
802 O00289 126000
807 O00290 126000
820 O00295 126000
843 O00302 126000
883 O00317 126000
897 O00322 126000
940 O00338 126000

### Temuan
terdapat line_amount yang diindikasikan adanya nilai outlier karna melebihi batas atas yang sebesar 124875

Pengecekan Outlier Dataset Payments

display(numeric_profile(payments_raw))
count mean std min 25% 50% 75% max
payment_amount 381.0 152076.115486 84246.986400 7000.0 98000.0 142000.0 192000.0 1098000.0
attempt_number 381.0 1.081365 0.273754 1.0 1.0 1.0 1.0 2.0
Q1 = payments_raw["payment_amount"].quantile(0.25)
Q3 = payments_raw["payment_amount"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = payments_raw[
    payments_raw["payment_amount"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "payment_amount"]])
Batas atas: 333000.0
Jumlah potential outlier: 4
order_id payment_amount
23 O00023 395000
87 O00084 1098000
115 O00111 387000
292 O00275 344000

### Temuan
terdapat outlier di payment_amount sebnayak 4 dgn batas atas 333000

Q1 = payments_raw["attempt_number"].quantile(0.25)
Q3 = payments_raw["attempt_number"].quantile(0.75)

IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR

outlier = payments_raw[
    payments_raw["attempt_number"] > batas_atas
]

print("Batas atas:", batas_atas)
print("Jumlah potential outlier:", len(outlier))

display(outlier[["order_id", "attempt_number"]])
Batas atas: 1.0
Jumlah potential outlier: 31
order_id attempt_number
20 O00020 2
67 O00066 2
79 O00077 2
84 O00081 2
91 O00087 2
129 O00124 2
131 O00125 2
134 O00127 2
152 O00144 2
157 O00148 2
172 O00162 2
177 O00166 2
192 O00180 2
216 O00203 2
228 O00214 2
246 O00231 2
260 O00244 2
285 O00268 2
294 O00276 2
330 O00311 2
360 O00340 2
362 O00341 2
364 O00342 2
366 O00343 2
368 O00344 2
370 O00345 2
372 O00346 2
374 O00347 2
376 O00348 2
378 O00349 2
380 O00350 2

Pengecekan Outlier Dataset Restaurants

display(numeric_profile(restaurants_raw))

data bertipe str smua sehingga tidak ada output

Temuan: pada attempt_number memang memiliki outlier namun untuk ini karna yg unique 2, maka 1 dan 2 masuknya dianggap kategori (str) yg bernilai angka, bisa jadi ordinal juga, tapi yaudah lah nnti aja

7. Candidate Findings Register

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

Jangan isi judgment sebelum verification evidence cukup.

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

findings = pd.DataFrame(columns=FINDING_COLUMNS)
display(findings)
finding_id dataset field_key finding_evidence why_it_matters verification_method verification_evidence_result judgment reason_for_judgment cleaning_rule_id

Template menambahkan finding

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

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

findings = pd.DataFrame([
    {
        "finding_id": "F-01",
        "dataset": "customers",
        "field_key": "customer_id",
        "finding_evidence": "Terdapat 2 baris dalam kelompok duplikat berdasarkan customer_id.",
        "why_it_matters": "Duplikasi dapat menggandakan informasi dan memengaruhi hasil analisis.",
        "verification_method": 'customers[customers["customer_id"].duplicated(keep=False)].sort_values("customer_id")',
        "verification_evidence_result": "Ditemukan 2 baris dalam kelompok duplikat; 1 baris merupakan duplikat tambahan.",
        "judgment": "FIX",
        "reason_for_judgment": "Duplikasi baris perlu ditangani agar informasi tidak terhitung berulang.",
        "cleaning_rule_id": "CR-01",
    },
    {
        "finding_id": "F-02",
        "dataset": "customers",
        "field_key": "customer_segment",
        "finding_evidence": "Terdapat inkonsistensi penulisan kategori NEW.",
        "why_it_matters": "Kategori yang sama dapat terbaca sebagai kategori berbeda saat analisis.",
        "verification_method": 'display(category_review(customers_raw, "customer_segment"))',
        "verification_evidence_result": "Kategori NEW ditemukan dengan perbedaan kapitalisasi dari New.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Menyeragamkan kapitalisasi kategori yang memiliki makna sama.",
        "cleaning_rule_id": "CR-02",
    },
    {
        "finding_id": "F-03",
        "dataset": "customers",
        "field_key": "signup_date",
        "finding_evidence": "Kolom signup_date masih bertipe string.",
        "why_it_matters": "Tipe tanggal yang sesuai memudahkan pengurutan dan analisis berdasarkan waktu.",
        "verification_method": 'customers_raw["signup_date"].dtype',
        "verification_evidence_result": "Tipe data awal berupa string.",
        "judgment": "FIX",
        "reason_for_judgment": "Mengonversi nilai tanggal yang valid ke tipe datetime.",
        "cleaning_rule_id": "CR-03",
    },
    {
        "finding_id": "F-05",
        "dataset": "orders",
        "field_key": "order_timestamp",
        "finding_evidence": "Kolom order_timestamp masih bertipe string.",
        "why_it_matters": "Tipe tanggal dan waktu yang sesuai memudahkan analisis transaksi berdasarkan waktu.",
        "verification_method": 'orders_raw["order_timestamp"].dtype',
        "verification_evidence_result": "Tipe data awal berupa string.",
        "judgment": "FIX",
        "reason_for_judgment": "Mengonversi nilai tanggal dan waktu yang valid ke tipe datetime.",
        "cleaning_rule_id": "CR-04",
    },
    {
        "finding_id": "F-06",
        "dataset": "orders",
        "field_key": "order_status",
        "finding_evidence": "Terdapat inkonsistensi kapitalisasi kategori COMPLETED.",
        "why_it_matters": "Perbedaan kapitalisasi dapat menyebabkan kategori yang sama dihitung terpisah.",
        "verification_method": 'display(category_review(orders_raw, "order_status"))',
        "verification_evidence_result": "Ditemukan variasi penulisan kapitalisasi pada kategori status.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Menyeragamkan penulisan kategori status yang bermakna sama.",
        "cleaning_rule_id": "CR-05",
    },
    {
        "finding_id": "F-07",
        "dataset": "order_items",
        "field_key": "quantity",
        "finding_evidence": "Kolom quantity masih bertipe string.",
        "why_it_matters": "Kuantitas perlu dapat diproses sebagai angka untuk perhitungan dan agregasi.",
        "verification_method": 'order_items_raw["quantity"].dtype',
        "verification_evidence_result": "Tipe data awal berupa string dan terdapat 2 nilai yang mengandung teks pcs.",
        "judgment": "FIX",
        "reason_for_judgment": "Membersihkan dan mengonversi kuantitas yang valid ke tipe numerik setelah nilai berteks diperiksa.",
        "cleaning_rule_id": "CR-06",
    },
    {
        "finding_id": "F-08",
        "dataset": "order_items",
        "field_key": "quantity",
        "finding_evidence": "Terdapat 2 nilai quantity yang mengandung teks pcs.",
        "why_it_matters": "Teks pada kolom kuantitas dapat menghambat konversi numerik dan perhitungan.",
        "verification_method": 'order_items_raw[order_items_raw["quantity"].astype(str).str.contains("pcs", case=False, na=False)]',
        "verification_evidence_result": "Ditemukan 2 nilai quantity yang mengandung pcs; nilai perlu diperiksa sebelum dikonversi.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Menyeragamkan representasi kuantitas setelah memastikan angka dan satuannya tidak mengubah makna data.",
        "cleaning_rule_id": "CR-07",
    },
    {
        "finding_id": "F-09",
        "dataset": "payments",
        "field_key": "payment_timestamp",
        "finding_evidence": "Kolom payment_timestamp masih bertipe string.",
        "why_it_matters": "Tipe tanggal dan waktu yang sesuai mendukung analisis transaksi pembayaran berdasarkan waktu.",
        "verification_method": 'payments_raw["payment_timestamp"].dtype',
        "verification_evidence_result": "Tipe data awal berupa string.",
        "judgment": "FIX",
        "reason_for_judgment": "Mengonversi nilai tanggal dan waktu yang valid ke tipe datetime.",
        "cleaning_rule_id": "CR-08",
    },
    {
            "finding_id": "F-10",
    "dataset": "payments",
    "field_key": "payment_method",
    "finding_evidence": "Kategori kolom payment_method terdapat ketidakkonsistenan pada credit_card dan cash.",
    "why_it_matters": "Ketidakkonsistenan penulisan kategori dapat menyebabkan metode pembayaran yang sama terbaca sebagai kategori berbeda saat analisis.",
    "verification_method": "Memeriksa variasi kategori pada payment_method menggunakan hasil profiling di bagian 6. Profiling.",
    "verification_evidence_result": "Ditemukan ketidakkonsistenan penulisan pada kategori credit_card dan cash.",
    "judgment": "STANDARDIZE",
    "reason_for_judgment": "Penulisan kategori perlu diseragamkan setelah memastikan variasi yang ditemukan memiliki makna yang sama.",
    "cleaning_rule_id": "CR-09",
    },
    {
        "finding_id": "F-11",
        "dataset": "restaurants",
        "field_key": "onboarding_date",
        "finding_evidence": "Kolom onboarding_date masih bertipe string.",
        "why_it_matters": "Tipe tanggal yang sesuai memudahkan analisis berdasarkan waktu onboarding.",
        "verification_method": 'restaurants_raw["onboarding_date"].dtype',
        "verification_evidence_result": "Tipe data awal berupa string.",
        "judgment": "FIX",
        "reason_for_judgment": "Mengonversi nilai tanggal yang valid ke tipe datetime.",
        "cleaning_rule_id": "CR-10",
    },
    {
        "finding_id": "F-12",
        "dataset": "restaurants",
        "field_key": "price_tier",
        "finding_evidence": "Terdapat variasi penulisan kategori price_tier, termasuk Mid.",
        "why_it_matters": "Kategori yang tidak konsisten dapat memengaruhi pengelompokan dan ringkasan analisis.",
        "verification_method": 'display(category_review(restaurants_raw, "price_tier"))',
        "verification_evidence_result": "Ditemukan variasi penulisan kategori price_tier yang perlu diperiksa.",
        "judgment": "STANDARDIZE",
        "reason_for_judgment": "Menyeragamkan kategori setelah memastikan variasi penulisannya memiliki makna sama.",
        "cleaning_rule_id": "CR-11",
    },
], columns=FINDING_COLUMNS)

display(findings)
finding_id dataset field_key finding_evidence why_it_matters verification_method verification_evidence_result judgment reason_for_judgment cleaning_rule_id
0 F-01 customers customer_id Terdapat 2 baris dalam kelompok duplikat berda... Duplikasi dapat menggandakan informasi dan mem... customers[customers["customer_id"].duplicated(... Ditemukan 2 baris dalam kelompok duplikat; 1 b... FIX Duplikasi baris perlu ditangani agar informasi... CR-01
1 F-02 customers customer_segment Terdapat inkonsistensi penulisan kategori NEW. Kategori yang sama dapat terbaca sebagai kateg... display(category_review(customers_raw, "custom... Kategori NEW ditemukan dengan perbedaan kapita... STANDARDIZE Menyeragamkan kapitalisasi kategori yang memil... CR-02
2 F-03 customers signup_date Kolom signup_date masih bertipe string. Tipe tanggal yang sesuai memudahkan pengurutan... customers_raw["signup_date"].dtype Tipe data awal berupa string. FIX Mengonversi nilai tanggal yang valid ke tipe d... CR-03
3 F-05 orders order_timestamp Kolom order_timestamp masih bertipe string. Tipe tanggal dan waktu yang sesuai memudahkan ... orders_raw["order_timestamp"].dtype Tipe data awal berupa string. FIX Mengonversi nilai tanggal dan waktu yang valid... CR-04
4 F-06 orders order_status Terdapat inkonsistensi kapitalisasi kategori C... Perbedaan kapitalisasi dapat menyebabkan kateg... display(category_review(orders_raw, "order_sta... Ditemukan variasi penulisan kapitalisasi pada ... STANDARDIZE Menyeragamkan penulisan kategori status yang b... CR-05
5 F-07 order_items quantity Kolom quantity masih bertipe string. Kuantitas perlu dapat diproses sebagai angka u... order_items_raw["quantity"].dtype Tipe data awal berupa string dan terdapat 2 ni... FIX Membersihkan dan mengonversi kuantitas yang va... CR-06
6 F-08 order_items quantity Terdapat 2 nilai quantity yang mengandung teks... Teks pada kolom kuantitas dapat menghambat kon... order_items_raw[order_items_raw["quantity"].as... Ditemukan 2 nilai quantity yang mengandung pcs... STANDARDIZE Menyeragamkan representasi kuantitas setelah m... CR-07
7 F-09 payments payment_timestamp Kolom payment_timestamp masih bertipe string. Tipe tanggal dan waktu yang sesuai mendukung a... payments_raw["payment_timestamp"].dtype Tipe data awal berupa string. FIX Mengonversi nilai tanggal dan waktu yang valid... CR-08
8 F-10 payments payment_method Kategori kolom payment_method terdapat ketidak... Ketidakkonsistenan penulisan kategori dapat me... Memeriksa variasi kategori pada payment_method... Ditemukan ketidakkonsistenan penulisan pada ka... STANDARDIZE Penulisan kategori perlu diseragamkan setelah ... CR-09
9 F-11 restaurants onboarding_date Kolom onboarding_date masih bertipe string. Tipe tanggal yang sesuai memudahkan analisis b... restaurants_raw["onboarding_date"].dtype Tipe data awal berupa string. FIX Mengonversi nilai tanggal yang valid ke tipe d... CR-10
10 F-12 restaurants price_tier Terdapat variasi penulisan kategori price_tier... Kategori yang tidak konsisten dapat memengaruh... display(category_review(restaurants_raw, "pric... Ditemukan variasi penulisan kategori price_tie... STANDARDIZE Menyeragamkan kategori setelah memastikan vari... CR-11

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 24 0 []
1 Orders → Restaurants 5 0 []
2 Order Items → Orders 350 0 []
3 Payments → Orders 350 0 []

9. Business-Rule Verification

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

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

Cell ini hanya membuat verification view. Tidak melakukan cleaning.

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

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

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

    display(
        order_verify[
            ["order_id", "subtotal_amount", "delivery_fee", "discount_amount",
             "total_amount", "expected_total_amount", "order_formula_match"]
        ].head()
    )
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount line_formula_match
0 OI00001 O00001 1 16000 2000 14000 14000.0 True
1 OI00002 O00001 2 20000 0 40000 40000.0 True
2 OI00003 O00002 4 28000 0 112000 112000.0 True
3 OI00004 O00002 1 18000 0 18000 18000.0 True
4 OI00005 O00003 3 18000 0 54000 54000.0 True
order_id subtotal_amount delivery_fee discount_amount total_amount expected_total_amount order_formula_match
0 O00001 54000 18000 10000 62000 62000 True
1 O00002 130000 18000 15000 133000 133000 True
2 O00003 238000 10000 15000 233000 233000 True
3 O00004 78000 8000 10000 76000 76000 True
4 O00005 123000 12000 15000 120000 120000 True
# Detail baris Order Items yang tidak sesuai rumus V-09
display(
    item_verify.loc[
        item_verify["line_formula_match"] == False,
        [
            "order_item_id", "order_id", "quantity",
            "unit_price", "line_discount", "line_amount",
            "expected_line_amount"
        ]
    ]
)

# Rekonsiliasi subtotal Orders dengan SUM(line_amount) — V-11
item_sum = (
    item_verify.groupby("order_id", as_index=False)["line_amount"]
    .sum()
    .rename(columns={"line_amount": "calculated_subtotal"})
)

order_reconcile = orders_raw.merge(
    item_sum,
    on="order_id",
    how="left"
)

order_reconcile["difference"] = (
    order_reconcile["calculated_subtotal"]
    - pd.to_numeric(order_reconcile["subtotal_amount"], errors="coerce")
)

display(
    order_reconcile.loc[
        order_reconcile["difference"] != 0,
        [
            "order_id", "subtotal_amount",
            "calculated_subtotal", "difference"
        ]
    ]
)
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount
70 OI00071 O00026 1 pcs 18000 0 18000 NaN
130 OI00131 O00048 2 22000 0 51000 44000.0
214 OI00215 O00076 3 pcs 20000 5000 55000 NaN
510 OI00511 O00185 2 42000 2000 77000 82000.0
order_id subtotal_amount calculated_subtotal difference
47 O00048 62000 69000 7000
184 O00185 179000 174000 -5000
200 O00201 183000 173000 -10000
print([name for name in globals() if "order_item" in name.lower()])
print([name for name in globals() if "order" in name.lower() and "clean" in name.lower()])
['order_items_raw', 'order_item_cardinality', 'order_item_recon_raw', 'order_items_clean']
['orders_clean', 'order_items_clean']
# V-09: Validasi rumus line_amount setelah cleaning
item_verify = order_items_clean.copy()

for col in ["quantity", "unit_price", "line_discount", "line_amount"]:
    item_verify[col] = pd.to_numeric(
        item_verify[col].astype(str).str.replace(" pcs", "", regex=False).str.strip(),
        errors="coerce"
    )

item_verify["expected_line_amount"] = (
    item_verify["quantity"] * item_verify["unit_price"]
    - item_verify["line_discount"]
)

item_verify["line_formula_match"] = (
    item_verify["line_amount"] == item_verify["expected_line_amount"]
)

print("V-09 — jumlah baris tidak cocok:",
      (~item_verify["line_formula_match"]).sum())

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

# V-11: Rekonsiliasi subtotal order dengan jumlah line_amount
item_sum = (
    item_verify.groupby("order_id", as_index=False)["line_amount"]
    .sum()
    .rename(columns={"line_amount": "calculated_subtotal"})
)

order_reconcile = orders_clean.merge(
    item_sum, on="order_id", how="left"
)

order_reconcile["difference"] = (
    order_reconcile["calculated_subtotal"]
    - pd.to_numeric(order_reconcile["subtotal_amount"], errors="coerce")
)

print("V-11 — jumlah order dengan subtotal tidak cocok:",
      (order_reconcile["difference"].abs() > 0.001).sum())

display(
    order_reconcile.loc[
        order_reconcile["difference"].abs() > 0.001,
        ["order_id", "subtotal_amount", "calculated_subtotal", "difference"]
    ]
)
V-09 — jumlah baris tidak cocok: 2
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount
130 OI00131 O00048 2 22000 0 51000 44000
510 OI00511 O00185 2 42000 2000 77000 82000
V-11 — jumlah order dengan subtotal tidak cocok: 3
order_id subtotal_amount calculated_subtotal difference
47 O00048 62000 69000 7000
184 O00185 179000 174000 -5000
200 O00201 183000 173000 -10000
new_rules = pd.DataFrame([
    {
        "rule_id": "CR-12",
        "source_finding_id": "F-09",
        "dataset": "Order Items",
        "trigger_condition": "line_amount tidak sama dengan quantity * unit_price - line_discount",
        "decision": "NEEDS_REVIEW",
        "cleaning_action": "KOREKSI_SETELAH_VERIFIKASI",
        "reason_evidence": "V-09 menemukan 2 baris tidak sesuai formula.",
        "implementation_note": "Verifikasi nilai sumber sebelum mengubah line_amount.",
        "validation_criteria": "V-09 mismatch = 0 setelah koreksi yang disetujui.",
        "rule_status": "Draft"
    }
], columns=RULE_COLUMNS)

cleaning_rules = pd.concat(
    [cleaning_rules, new_rules],
    ignore_index=True
)

display(cleaning_rules.tail(1))
rule_id source_finding_id dataset trigger_condition decision cleaning_action reason_evidence implementation_note validation_criteria rule_status
13 CR-12 F-09 Order Items line_amount tidak sama dengan quantity * unit_... NEEDS_REVIEW KOREKSI_SETELAH_VERIFIKASI V-09 menemukan 2 baris tidak sesuai formula. Verifikasi nilai sumber sebelum mengubah line_... V-09 mismatch = 0 setelah koreksi yang disetujui. Draft
# Tampilkan quantity yang mengandung "pcs"
mask_pcs = item_verify["quantity"].astype(str).str.contains(
    "pcs", case=False, na=False
)

display(
    item_verify.loc[
        mask_pcs,
        ["order_item_id", "order_id", "quantity", "unit_price",
         "line_discount", "line_amount", "expected_line_amount",
         "line_formula_match"]
    ]
)
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount line_formula_match
70 OI00071 O00026 1 pcs 18000 0 18000 NaN False
214 OI00215 O00076 3 pcs 20000 5000 55000 NaN False
# Tampilkan quantity yang gagal dikonversi menjadi angka
quantity_check = pd.to_numeric(
    order_items_raw["quantity"],
    errors="coerce"
)

display(
    order_items_raw.loc[
        quantity_check.isna(),
        ["order_item_id", "order_id", "quantity"]
    ]
)
order_item_id order_id quantity
70 OI00071 O00026 1 pcs
214 OI00215 O00076 3 pcs
quantity_demo = order_items_raw[["order_item_id", "quantity"]].copy()

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

display(
    quantity_demo[
        quantity_demo["quantity_numeric"].isna()
    ]
)
order_item_id quantity quantity_numeric
70 OI00071 1 pcs NaN
214 OI00215 3 pcs NaN
# Total quantity setelah dikonversi ke angka
quantity_num = pd.to_numeric(
    order_items_raw["quantity"],
    errors="coerce"
)

print("Total quantity Python:", quantity_num.sum())
print("Jumlah nilai gagal dikonversi:", quantity_num.isna().sum())
Total quantity Python: 2130.0
Jumlah nilai gagal dikonversi: 2

interupsi: Kak sbnrnya ini tuh data quantity kan ada yg str (isinya 1 pcs) trs dia dipaksa ke numerik, nah yg si pcs itu dia gagal dikonversi ke angka, itu gmn ya?

display(
    item_verify.loc[
        item_verify["quantity"].astype(str).str.contains(
            "pcs", case=False, na=False
        ),
        [
            "order_item_id",
            "quantity",
            "unit_price",
            "line_discount",
            "line_amount",
            "expected_line_amount",
            "line_formula_match"
        ]
    ]
)
order_item_id quantity unit_price line_discount line_amount expected_line_amount line_formula_match
70 OI00071 1 pcs 18000 0 18000 NaN False
214 OI00215 3 pcs 20000 5000 55000 NaN False
# Hitung total yang seharusnya
expected_total = (
    orders_raw["subtotal_amount"]
    + orders_raw["delivery_fee"]
    - orders_raw["discount_amount"]
)

# Bandingkan dengan total di data
formula_check = orders_raw[
    orders_raw["total_amount"] != expected_total
]

print("Jumlah baris yang tidak sesuai rumus:", len(formula_check))

display(formula_check[
    ["order_id", "subtotal_amount", "delivery_fee",
     "discount_amount", "total_amount"]
])
Jumlah baris yang tidak sesuai rumus: 0
order_id subtotal_amount delivery_fee discount_amount total_amount

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 54000 54000 54000 True
1 O00002 130000 130000 130000 True
2 O00003 238000 238000 238000 True
3 O00004 78000 78000 78000 True
4 O00005 123000 123000 123000 True
Raw reconciliation mismatch count: 3
display(
    order_item_recon_raw[
        order_item_recon_raw["reconcile_match"] != True
    ][
        [
            "order_id",
            "subtotal_amount",
            "sum_line_amount",
            "subtotal_numeric",
            "reconcile_match"
        ]
    ]
)
order_id subtotal_amount sum_line_amount subtotal_numeric reconcile_match
47 O00048 62000 69000 62000 False
184 O00185 179000 174000 179000 False
200 O00201 183000 173000 183000 False

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)

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

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([
    {
        "rule_id": "CR-01",
        "source_finding_id": "F-01",
        "dataset": "customers",
        "trigger_condition": "Terdapat 2 baris dalam kelompok duplikat customer_id",
        "decision": "FIX",
        "cleaning_action": "Periksa duplikat dan hapus baris duplikat yang terkonfirmasi",
        "reason_evidence": "Duplikasi ID dapat menyebabkan pelanggan dihitung lebih dari sekali",
        "implementation_note": "Pertahankan satu baris untuk setiap customer_id setelah verifikasi",
        "validation_criteria": "customer_id unik setelah pembersihan",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-02",
        "source_finding_id": "F-02",
        "dataset": "customers",
        "trigger_condition": "Terdapat ketidakkonsistenan kategori customer_segment, yaitu New dan NEW",
        "decision": "STANDARDIZE",
        "cleaning_action": "Seragamkan penulisan kategori yang memiliki makna sama",
        "reason_evidence": "Perbedaan kapitalisasi dapat membuat kategori yang sama terbaca sebagai dua kategori",
        "implementation_note": "Ubah NEW menjadi New setelah memastikan maknanya sama",
        "validation_criteria": "Tidak ada lagi variasi NEW dan New",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-03",
        "source_finding_id": "F-03",
        "dataset": "customers",
        "trigger_condition": "Kolom signup_date masih bertipe teks",
        "decision": "FIX",
        "cleaning_action": "Konversi signup_date ke tipe tanggal",
        "reason_evidence": "Tipe tanggal diperlukan untuk pengurutan dan analisis waktu",
        "implementation_note": "Gunakan pd.to_datetime() dan periksa nilai yang gagal dikonversi",
        "validation_criteria": "Nilai tanggal valid berhasil dikonversi dan nilai gagal konversi diperiksa",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-04",
        "source_finding_id": "F-05",
        "dataset": "orders",
        "trigger_condition": "Kolom order_timestamp masih bertipe teks",
        "decision": "FIX",
        "cleaning_action": "Konversi order_timestamp ke tipe tanggal dan waktu",
        "reason_evidence": "Tipe data yang sesuai memudahkan analisis waktu pemesanan",
        "implementation_note": "Gunakan pd.to_datetime() sesuai format data dan periksa hasil konversi",
        "validation_criteria": "Nilai valid berhasil dikonversi dan jumlah baris tetap",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-05",
        "source_finding_id": "F-06",
        "dataset": "orders",
        "trigger_condition": "Terdapat perbedaan kapitalisasi pada order_status, misalnya completed dan COMPLETED",
        "decision": "STANDARDIZE",
        "cleaning_action": "Seragamkan kapitalisasi kategori order_status",
        "reason_evidence": "Variasi penulisan dapat menyebabkan penghitungan status tidak akurat",
        "implementation_note": "Gunakan format kapitalisasi yang konsisten setelah memeriksa maknanya",
        "validation_criteria": "Kategori status tidak lagi terpisah hanya karena perbedaan kapitalisasi",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-06",
        "source_finding_id": "F-07",
        "dataset": "order_items",
        "trigger_condition": "Kolom quantity bertipe teks, padahal seharusnya numerik",
        "decision": "FIX",
        "cleaning_action": "Konversi nilai quantity yang valid ke tipe numerik",
        "reason_evidence": "Tipe numerik diperlukan untuk perhitungan kuantitas barang",
        "implementation_note": "Periksa nilai yang gagal dikonversi sebelum menetapkan hasil akhir",
        "validation_criteria": "Nilai quantity valid bertipe numerik dan nilai gagal konversi diperiksa",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-07",
        "source_finding_id": "F-08",
        "dataset": "order_items",
        "trigger_condition": "Terdapat nilai quantity yang mengandung teks pcs",
        "decision": "STANDARDIZE",
        "cleaning_action": "Hapus akhiran pcs dan konversi bagian angka setelah verifikasi",
        "reason_evidence": "Teks pada kolom quantity menghambat perhitungan numerik",
        "implementation_note": "Bersihkan akhiran satuan tanpa mengubah nilai kuantitas",
        "validation_criteria": "Nilai seperti 2 pcs menjadi angka 2 dan jumlah baris tetap",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-08",
        "source_finding_id": "F-09",
        "dataset": "payments",
        "trigger_condition": "Kolom payment_timestamp masih bertipe teks",
        "decision": "FIX",
        "cleaning_action": "Konversi payment_timestamp ke tipe tanggal dan waktu",
        "reason_evidence": "Tipe data yang sesuai diperlukan untuk analisis waktu transaksi",
        "implementation_note": "Gunakan pd.to_datetime() sesuai format data dan periksa hasil konversi",
        "validation_criteria": "Nilai valid berhasil dikonversi dan jumlah baris tetap",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-09",
        "source_finding_id": "F-10",
        "dataset": "payments",
        "trigger_condition": "Terdapat ketidakkonsistenan kategori payment_method pada credit_card dan cash",
        "decision": "STANDARDIZE",
        "cleaning_action": "Seragamkan penulisan kategori yang memiliki makna sama",
        "reason_evidence": "Ketidakkonsistenan kategori dapat menyebabkan hasil analisis metode pembayaran tidak akurat",
        "implementation_note": "Periksa variasi kategori dan tetapkan format penulisan yang konsisten",
        "validation_criteria": "Variasi penulisan yang tidak perlu sudah diseragamkan tanpa mengubah makna",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-10",
        "source_finding_id": "F-11",
        "dataset": "restaurants",
        "trigger_condition": "Kolom onboarding_date masih bertipe teks",
        "decision": "FIX",
        "cleaning_action": "Konversi onboarding_date ke tipe tanggal",
        "reason_evidence": "Tipe tanggal memudahkan analisis waktu bergabungnya restoran",
        "implementation_note": "Gunakan pd.to_datetime() sesuai format data dan periksa hasil konversi",
        "validation_criteria": "Nilai valid berhasil dikonversi dan jumlah baris tetap",
        "rule_status": "Draft",
    },
    {
        "rule_id": "CR-11",
        "source_finding_id": "F-12",
        "dataset": "restaurants",
        "trigger_condition": "Terdapat variasi penulisan kategori price_tier, termasuk Mid",
        "decision": "STANDARDIZE",
        "cleaning_action": "Seragamkan kategori setelah memastikan label memiliki makna yang sama",
        "reason_evidence": "Variasi penulisan dapat menyebabkan kategori harga terhitung terpisah",
        "implementation_note": "Periksa seluruh kategori dan tentukan label standar berdasarkan hasil verifikasi",
        "validation_criteria": "Tidak ada kategori ganda akibat perbedaan penulisan",
        "rule_status": "Draft",
    },
], columns=RULE_COLUMNS)

display(cleaning_rules)
rule_id source_finding_id dataset trigger_condition decision cleaning_action reason_evidence implementation_note validation_criteria rule_status
0 CR-01 F-01 customers Terdapat 2 baris dalam kelompok duplikat custo... FIX Periksa duplikat dan hapus baris duplikat yang... Duplikasi ID dapat menyebabkan pelanggan dihit... Pertahankan satu baris untuk setiap customer_i... customer_id unik setelah pembersihan Draft
1 CR-02 F-02 customers Terdapat ketidakkonsistenan kategori customer_... STANDARDIZE Seragamkan penulisan kategori yang memiliki ma... Perbedaan kapitalisasi dapat membuat kategori ... Ubah NEW menjadi New setelah memastikan maknan... Tidak ada lagi variasi NEW dan New Draft
2 CR-03 F-03 customers Kolom signup_date masih bertipe teks FIX Konversi signup_date ke tipe tanggal Tipe tanggal diperlukan untuk pengurutan dan a... Gunakan pd.to_datetime() dan periksa nilai yan... Nilai tanggal valid berhasil dikonversi dan ni... Draft
3 CR-04 F-05 orders Kolom order_timestamp masih bertipe teks FIX Konversi order_timestamp ke tipe tanggal dan w... Tipe data yang sesuai memudahkan analisis wakt... Gunakan pd.to_datetime() sesuai format data da... Nilai valid berhasil dikonversi dan jumlah bar... Draft
4 CR-05 F-06 orders Terdapat perbedaan kapitalisasi pada order_sta... STANDARDIZE Seragamkan kapitalisasi kategori order_status Variasi penulisan dapat menyebabkan penghitung... Gunakan format kapitalisasi yang konsisten set... Kategori status tidak lagi terpisah hanya kare... Draft
5 CR-06 F-07 order_items Kolom quantity bertipe teks, padahal seharusny... FIX Konversi nilai quantity yang valid ke tipe num... Tipe numerik diperlukan untuk perhitungan kuan... Periksa nilai yang gagal dikonversi sebelum me... Nilai quantity valid bertipe numerik dan nilai... Draft
6 CR-07 F-08 order_items Terdapat nilai quantity yang mengandung teks pcs STANDARDIZE Hapus akhiran pcs dan konversi bagian angka se... Teks pada kolom quantity menghambat perhitunga... Bersihkan akhiran satuan tanpa mengubah nilai ... Nilai seperti 2 pcs menjadi angka 2 dan jumlah... Draft
7 CR-08 F-09 payments Kolom payment_timestamp masih bertipe teks FIX Konversi payment_timestamp ke tipe tanggal dan... Tipe data yang sesuai diperlukan untuk analisi... Gunakan pd.to_datetime() sesuai format data da... Nilai valid berhasil dikonversi dan jumlah bar... Draft
8 CR-09 F-10 payments Terdapat ketidakkonsistenan kategori payment_m... STANDARDIZE Seragamkan penulisan kategori yang memiliki ma... Ketidakkonsistenan kategori dapat menyebabkan ... Periksa variasi kategori dan tetapkan format p... Variasi penulisan yang tidak perlu sudah diser... Draft
9 CR-10 F-11 restaurants Kolom onboarding_date masih bertipe teks FIX Konversi onboarding_date ke tipe tanggal Tipe tanggal memudahkan analisis waktu bergabu... Gunakan pd.to_datetime() sesuai format data da... Nilai valid berhasil dikonversi dan jumlah bar... Draft
10 CR-11 F-12 restaurants Terdapat variasi penulisan kategori price_tier... STANDARDIZE Seragamkan kategori setelah memastikan label m... Variasi penulisan dapat menyebabkan kategori h... Periksa seluruh kategori dan tentukan label st... Tidak ada kategori ganda akibat perbedaan penu... Draft
new_rules = pd.DataFrame([
    {
        "rule_id": "CR-12",
        "source_finding_id": "F-V09",
        "dataset": "order_items",
        "trigger_condition": (
            "line_amount tidak sesuai dengan "
            "quantity * unit_price - line_discount"
        ),
        "decision": "NEEDS_REVIEW",
        "cleaning_action": (
            "Verifikasi line_amount terhadap sumber transaksi "
            "sebelum melakukan koreksi"
        ),
        "reason_evidence": (
            "Ditemukan 2 baris mismatch pada V-09"
        ),
        "implementation_note": (
            "Jangan mengubah nilai sebelum bukti sumber tersedia"
        ),
        "validation_criteria": (
            "Seluruh mismatch ditangani berdasarkan bukti "
            "dan hasil validasi diulang"
        ),
        "rule_status": "Needs Review",
    },
    {
        "rule_id": "CR-13",
        "source_finding_id": "F-V11",
        "dataset": "orders",
        "trigger_condition": (
            "subtotal_amount tidak sama dengan jumlah line_amount "
            "untuk order terkait"
        ),
        "decision": "NEEDS_REVIEW",
        "cleaning_action": (
            "Rekonsiliasi subtotal dengan detail item "
            "setelah V-09 diverifikasi"
        ),
        "reason_evidence": (
            "Ditemukan 3 order mismatch pada V-11"
        ),
        "implementation_note": (
            "Jangan mengubah subtotal secara otomatis"
        ),
        "validation_criteria": (
            "Seluruh mismatch direkonsiliasi berdasarkan bukti "
            "dan hasil validasi diulang"
        ),
        "rule_status": "Needs Review",
    },
], columns=RULE_COLUMNS)

cleaning_rules = pd.concat(
    [cleaning_rules, new_rules],
    ignore_index=True
)

display(cleaning_rules.tail(2))
rule_id source_finding_id dataset trigger_condition decision cleaning_action reason_evidence implementation_note validation_criteria rule_status
11 CR-12 F-V09 order_items line_amount tidak sesuai dengan quantity * uni... NEEDS_REVIEW Verifikasi line_amount terhadap sumber transak... Ditemukan 2 baris mismatch pada V-09 Jangan mengubah nilai sebelum bukti sumber ter... Seluruh mismatch ditangani berdasarkan bukti d... Needs Review
12 CR-13 F-V11 orders subtotal_amount tidak sama dengan jumlah line_... NEEDS_REVIEW Rekonsiliasi subtotal dengan detail item setel... Ditemukan 3 order mismatch pada V-11 Jangan mengubah subtotal secara otomatis Seluruh mismatch direkonsiliasi berdasarkan bu... Needs Review

11. Create Working Copies

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

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

12. Implement Cleaning Rules — EDIT + RUN

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

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

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

# ============================================================
# EDIT AREA 2 — APPROVED CLEANING CODE
# ============================================================
# Tambahkan code per Rule ID di bawah ini.
#
# Contoh struktur komentar:
# # CR-01 — [judul rule]
# # Source Finding: F-xx
# # Evidence basis: ...
# # Validation criteria: ...
#
# Gunakan hanya dataframe *_clean.
# JANGAN ubah dataframe *_raw.

# TODO: implement approved rules here.

pass
# Pemeriksaan V-09 dan V-11 tanpa mengubah data clean

oi_check = order_items_clean.copy()
od_check = orders_clean.copy()

for col in ["quantity", "unit_price", "line_discount", "line_amount"]:
    oi_check[col] = pd.to_numeric(oi_check[col], errors="coerce")

oi_check["expected_line_amount"] = (
    oi_check["quantity"] * oi_check["unit_price"]
    - oi_check["line_discount"]
)

print("=== V-09: MISMATCH LINE AMOUNT ===")
display(
    oi_check.loc[
        oi_check["line_amount"].sub(
            oi_check["expected_line_amount"]
        ).abs().gt(0.01),
        [
            "order_item_id", "order_id", "quantity",
            "unit_price", "line_discount",
            "line_amount", "expected_line_amount"
        ]
    ]
)

item_totals = (
    oi_check.groupby("order_id", as_index=False)
    .agg(calculated_subtotal=("line_amount", "sum"))
)

subtotal_check = od_check[
    ["order_id", "subtotal_amount"]
].merge(item_totals, on="order_id", how="left")

subtotal_check["difference"] = (
    subtotal_check["calculated_subtotal"]
    - subtotal_check["subtotal_amount"]
)

print("=== V-11: MISMATCH SUBTOTAL ===")
display(
    subtotal_check.loc[
        subtotal_check["difference"].abs().gt(0.01)
    ]
)
=== V-09: MISMATCH LINE AMOUNT ===
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount
130 OI00131 O00048 2 22000 0 51000 44000
510 OI00511 O00185 2 42000 2000 77000 82000
=== V-11: MISMATCH SUBTOTAL ===
order_id subtotal_amount calculated_subtotal difference
47 O00048 62000 69000 7000
184 O00185 179000 174000 -5000
200 O00201 183000 173000 -10000
# ============================================================
# EDIT AREA 2 — APPROVED CLEANING CODE
# ============================================================

import pandas as pd

# Pastikan dataframe *_clean sudah dibuat dari dataframe *_raw.
# Jangan mengubah dataframe *_raw.

# Menyiapkan Cleaning Log
cleaning_log_records = []

def log_cleaning(rule_id, dataset, field, before, after, action):
    cleaning_log_records.append({
        "rule_id": rule_id,
        "dataset": dataset,
        "field": field,
        "before": before,
        "after": after,
        "action": action
    })


# CR-01 — Periksa dan tangani duplikasi customer_id
# Source Finding: F-01
# Evidence basis: Terdapat customer_id yang berulang.
# Validation criteria: customer_id unik setelah penanganan.

before_rows = len(customers_clean)
before_duplicate_ids = int(
    customers_clean["customer_id"].duplicated(keep=False).sum()
)

# Hapus baris yang identik sepenuhnya terlebih dahulu.
customers_clean = customers_clean.drop_duplicates().copy()

# Jika customer_id masih berulang, jangan menghapus baris secara
# otomatis karena isi datanya perlu diperiksa lebih lanjut.
after_rows = len(customers_clean)
after_duplicate_ids = int(
    customers_clean["customer_id"].duplicated(keep=False).sum()
)

log_cleaning(
    "CR-01", "customers", "customer_id",
    f"{before_rows} baris; {before_duplicate_ids} baris dengan ID berulang",
    f"{after_rows} baris; {after_duplicate_ids} baris dengan ID berulang",
    "Menghapus baris identik; duplikasi ID yang tersisa perlu ditinjau"
)


# CR-02 — Standardisasi customer_segment
# Source Finding: F-02
# Validation criteria: NEW diseragamkan menjadi New.

before = int(
    customers_clean["customer_segment"].astype("string").eq("NEW").sum()
)

customers_clean["customer_segment"] = (
    customers_clean["customer_segment"]
    .replace({"NEW": "New"})
)

after = int(
    customers_clean["customer_segment"].astype("string").eq("NEW").sum()
)

log_cleaning(
    "CR-02", "customers", "customer_segment",
    f"{before} nilai NEW",
    f"{after} nilai NEW",
    "Mengubah NEW menjadi New"
)


# CR-03 — Konversi signup_date ke tanggal
# Source Finding: F-03

before = int(customers_clean["signup_date"].notna().sum())

customers_clean["signup_date"] = pd.to_datetime(
    customers_clean["signup_date"], errors="coerce", dayfirst=True
)

after = int(customers_clean["signup_date"].notna().sum())

log_cleaning(
    "CR-03", "customers", "signup_date",
    f"{before} nilai terisi",
    f"{after} nilai berhasil dikonversi",
    "Konversi ke tipe tanggal; periksa nilai yang menjadi NaT"
)


# CR-04 — Konversi order_timestamp ke tanggal dan waktu
# Source Finding: F-05

before = int(orders_clean["order_timestamp"].notna().sum())

orders_clean["order_timestamp"] = pd.to_datetime(
    orders_clean["order_timestamp"], errors="coerce", dayfirst=True
)

after = int(orders_clean["order_timestamp"].notna().sum())

log_cleaning(
    "CR-04", "orders", "order_timestamp",
    f"{before} nilai terisi",
    f"{after} nilai berhasil dikonversi",
    "Konversi ke tipe tanggal dan waktu; periksa nilai NaT"
)


# CR-05 — Standardisasi order_status
# Source Finding: F-06
# Menyamakan kapitalisasi tanpa mengubah makna kategori.

before = orders_clean["order_status"].nunique(dropna=True)

orders_clean["order_status"] = (
    orders_clean["order_status"]
    .apply(lambda x: x.strip().upper() if isinstance(x, str) else x)
)

after = orders_clean["order_status"].nunique(dropna=True)

log_cleaning(
    "CR-05", "orders", "order_status",
    f"{before} kategori",
    f"{after} kategori",
    "Menghapus spasi di tepi dan menyeragamkan kapitalisasi menjadi huruf besar"
)


# CR-06 — Konversi quantity menjadi numerik
# Source Finding: F-07
# Nilai yang masih mengandung satuan ditangani pada CR-07.

before = int(pd.to_numeric(
    order_items_clean["quantity"], errors="coerce"
).notna().sum())

# CR-07 — Hapus akhiran pcs dari quantity
# Source Finding: F-08

order_items_clean["quantity"] = (
    order_items_clean["quantity"]
    .astype("string")
    .str.strip()
    .str.replace(r"\s*pcs\s*$", "", regex=True, case=False)
)

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

after = int(order_items_clean["quantity"].notna().sum())

log_cleaning(
    "CR-06", "order_items", "quantity",
    f"{before} nilai numerik sebelum penanganan satuan",
    f"{after} nilai numerik setelah konversi",
    "Konversi quantity ke numerik setelah penanganan teks pcs"
)

log_cleaning(
    "CR-07", "order_items", "quantity",
    "Nilai quantity berpotensi mengandung akhiran pcs",
    f"{int(order_items_clean['quantity'].isna().sum())} nilai masih kosong atau gagal dikonversi",
    "Menghapus akhiran pcs lalu mengonversi bagian angka; periksa nilai gagal"
)


# CR-08 — Konversi payment_timestamp ke tanggal dan waktu
# Source Finding: F-09

before = int(payments_clean["payment_timestamp"].notna().sum())

payments_clean["payment_timestamp"] = pd.to_datetime(
    payments_clean["payment_timestamp"], errors="coerce", dayfirst=True
)

after = int(payments_clean["payment_timestamp"].notna().sum())

log_cleaning(
    "CR-08", "payments", "payment_timestamp",
    f"{before} nilai terisi",
    f"{after} nilai berhasil dikonversi",
    "Konversi ke tipe tanggal dan waktu; periksa nilai NaT"
)


# CR-09 — Standardisasi payment_method
# Source Finding: F-10
# Seragamkan kapitalisasi dan spasi; pertahankan pemisah underscore.

before = payments_clean["payment_method"].nunique(dropna=True)

payments_clean["payment_method"] = (
    payments_clean["payment_method"]
    .apply(lambda x: x.strip().lower() if isinstance(x, str) else x)
)

after = payments_clean["payment_method"].nunique(dropna=True)

log_cleaning(
    "CR-09", "payments", "payment_method",
    f"{before} kategori",
    f"{after} kategori",
    "Menghapus spasi di tepi dan menyeragamkan kapitalisasi"
)


# CR-10 — Konversi onboarding_date ke tanggal
# Source Finding: F-11

before = int(restaurants_clean["onboarding_date"].notna().sum())

restaurants_clean["onboarding_date"] = pd.to_datetime(
    restaurants_clean["onboarding_date"], errors="coerce", dayfirst=True
)

after = int(restaurants_clean["onboarding_date"].notna().sum())

log_cleaning(
    "CR-10", "restaurants", "onboarding_date",
    f"{before} nilai terisi",
    f"{after} nilai berhasil dikonversi",
    "Konversi ke tipe tanggal; periksa nilai NaT"
)


# CR-11 — Standardisasi kategori price_tier
# Source Finding: F-12
# Samakan kapitalisasi berdasarkan label standar yang umum digunakan.

before = restaurants_clean["price_tier"].nunique(dropna=True)

restaurants_clean["price_tier"] = (
    restaurants_clean["price_tier"]
    .apply(lambda x: x.strip().title() if isinstance(x, str) else x)
)

after = restaurants_clean["price_tier"].nunique(dropna=True)

log_cleaning(
    "CR-11", "restaurants", "price_tier",
    f"{before} kategori",
    f"{after} kategori",
    "Menghapus spasi di tepi dan menyeragamkan kapitalisasi kategori"
)


# Tampilkan Cleaning Log
cleaning_log = pd.DataFrame(cleaning_log_records)

display(cleaning_log)
C:\Users\lucih\AppData\Local\Temp\ipykernel_4160\4198289302.py:101: UserWarning: Parsing dates in %Y-%m-%d %H:%M:%S format when dayfirst=True was specified. Pass `dayfirst=False` or specify a format to silence this warning.
  orders_clean["order_timestamp"] = pd.to_datetime(
C:\Users\lucih\AppData\Local\Temp\ipykernel_4160\4198289302.py:180: UserWarning: Parsing dates in %Y-%m-%d %H:%M:%S format when dayfirst=True was specified. Pass `dayfirst=False` or specify a format to silence this warning.
  payments_clean["payment_timestamp"] = pd.to_datetime(
C:\Users\lucih\AppData\Local\Temp\ipykernel_4160\4198289302.py:220: UserWarning: Parsing dates in %Y-%m-%d format when dayfirst=True was specified. Pass `dayfirst=False` or specify a format to silence this warning.
  restaurants_clean["onboarding_date"] = pd.to_datetime(
rule_id dataset field before after action
0 CR-01 customers customer_id 169 baris; 2 baris dengan ID berulang 168 baris; 0 baris dengan ID berulang Menghapus baris identik; duplikasi ID yang ter...
1 CR-02 customers customer_segment 0 nilai NEW 0 nilai NEW Mengubah NEW menjadi New
2 CR-03 customers signup_date 168 nilai terisi 66 nilai berhasil dikonversi Konversi ke tipe tanggal; periksa nilai yang m...
3 CR-04 orders order_timestamp 350 nilai terisi 350 nilai berhasil dikonversi Konversi ke tipe tanggal dan waktu; periksa ni...
4 CR-05 orders order_status 3 kategori 2 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
5 CR-06 order_items quantity 972 nilai numerik sebelum penanganan satuan 974 nilai numerik setelah konversi Konversi quantity ke numerik setelah penangana...
6 CR-07 order_items quantity Nilai quantity berpotensi mengandung akhiran pcs 0 nilai masih kosong atau gagal dikonversi Menghapus akhiran pcs lalu mengonversi bagian ...
7 CR-08 payments payment_timestamp 381 nilai terisi 381 nilai berhasil dikonversi Konversi ke tipe tanggal dan waktu; periksa ni...
8 CR-09 payments payment_method 6 kategori 5 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
9 CR-10 restaurants onboarding_date 25 nilai terisi 25 nilai berhasil dikonversi Konversi ke tipe tanggal; periksa nilai NaT
10 CR-11 restaurants price_tier 4 kategori 3 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
# Cek hasil cleaning
print("=== JUMLAH BARIS SEBELUM DAN SESUDAH ===")
for name, df in {
    "customers_clean": customers_clean,
    "orders_clean": orders_clean,
    "order_items_clean": order_items_clean,
    "payments_clean": payments_clean,
    "restaurants_clean": restaurants_clean,
}.items():
    print(f"{name}: {len(df)} baris")

print("\n=== CLEANING LOG ===")
display(cleaning_log)

print("\n=== CEK DUPLIKASI CUSTOMER_ID ===")
print(customers_clean["customer_id"].duplicated().sum())

print("\n=== CEK QUANTITY ===")
print("Tipe data:", order_items_clean["quantity"].dtype)
print("Nilai kosong:", order_items_clean["quantity"].isna().sum())

print("\n=== CEK KATEGORI ===")
print("customer_segment:")
display(customers_clean["customer_segment"].value_counts(dropna=False))

print("payment_method:")
display(payments_clean["payment_method"].value_counts(dropna=False))

print("price_tier:")
display(restaurants_clean["price_tier"].value_counts(dropna=False))
=== JUMLAH BARIS SEBELUM DAN SESUDAH ===
customers_clean: 168 baris
orders_clean: 350 baris
order_items_clean: 974 baris
payments_clean: 381 baris
restaurants_clean: 25 baris

=== CLEANING LOG ===
rule_id dataset field before after action
0 CR-01 customers customer_id 169 baris; 2 baris dengan ID berulang 168 baris; 0 baris dengan ID berulang Menghapus baris identik; duplikasi ID yang ter...
1 CR-02 customers customer_segment 0 nilai NEW 0 nilai NEW Mengubah NEW menjadi New
2 CR-03 customers signup_date 168 nilai terisi 66 nilai berhasil dikonversi Konversi ke tipe tanggal; periksa nilai yang m...
3 CR-04 orders order_timestamp 350 nilai terisi 350 nilai berhasil dikonversi Konversi ke tipe tanggal dan waktu; periksa ni...
4 CR-05 orders order_status 3 kategori 2 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
5 CR-06 order_items quantity 972 nilai numerik sebelum penanganan satuan 974 nilai numerik setelah konversi Konversi quantity ke numerik setelah penangana...
6 CR-07 order_items quantity Nilai quantity berpotensi mengandung akhiran pcs 0 nilai masih kosong atau gagal dikonversi Menghapus akhiran pcs lalu mengonversi bagian ...
7 CR-08 payments payment_timestamp 381 nilai terisi 381 nilai berhasil dikonversi Konversi ke tipe tanggal dan waktu; periksa ni...
8 CR-09 payments payment_method 6 kategori 5 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
9 CR-10 restaurants onboarding_date 25 nilai terisi 25 nilai berhasil dikonversi Konversi ke tipe tanggal; periksa nilai NaT
10 CR-11 restaurants price_tier 4 kategori 3 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
=== CEK DUPLIKASI CUSTOMER_ID ===
0

=== CEK QUANTITY ===
Tipe data: Int64
Nilai kosong: 0

=== CEK KATEGORI ===
customer_segment:
customer_segment
Regular    69
New        53
Loyal      44
NEW         2
Name: count, dtype: int64
payment_method:
payment_method
cash             98
bank_transfer    95
e_wallet         94
credit_card      93
credit card       1
Name: count, dtype: int64
price_tier:
price_tier
Budget     12
Mid         9
Premium     4
Name: count, dtype: int64
# Pemeriksaan lanjutan sebelum cleaning dinyatakan selesai

print("=== CR-02: customer_segment ===")
print(customers_clean["customer_segment"].value_counts(dropna=False))

print("\n=== CR-03: signup_date ===")
print("Tipe data:", customers_clean["signup_date"].dtype)
print("Tanggal kosong/NaT:", customers_clean["signup_date"].isna().sum())
print("Contoh nilai tanggal:")
print(customers_clean["signup_date"].head(10))

print("\n=== CR-09: payment_method ===")
print(payments_clean["payment_method"].value_counts(dropna=False))

print("\n=== CR-01: ID customer yang berulang ===")
print(
    customers_raw.loc[
        customers_raw["customer_id"].duplicated(keep=False)
    ].sort_values("customer_id").to_string(index=False)
)
=== CR-02: customer_segment ===
customer_segment
Regular    69
New        53
Loyal      44
NEW         2
Name: count, dtype: int64

=== CR-03: signup_date ===
Tipe data: datetime64[us]
Tanggal kosong/NaT: 102
Contoh nilai tanggal:
0   2025-03-12
1   2025-08-08
2   2025-06-12
3          NaT
4   2025-09-12
5          NaT
6   2025-07-09
7          NaT
8          NaT
9          NaT
Name: signup_date, dtype: datetime64[us]

=== CR-09: payment_method ===
payment_method
cash             98
bank_transfer    95
e_wallet         94
credit_card      93
credit card       1
Name: count, dtype: int64

=== CR-01: ID customer yang berulang ===
customer_id     full_name                      email phone_number signup_date customer_zone customer_segment
      C0031 Dimas Pratama dimaspratama31@example.com 085368771270  2025-10-30       Central            Loyal
      C0031 Dimas Pratama dimaspratama31@example.com 085368771270  2025-10-30       Central            Loyal
# CR-02 — Standardisasi customer_segment
customers_clean["customer_segment"] = (
    customers_clean["customer_segment"]
    .astype("string")
    .str.strip()
    .replace({"NEW": "New"})
)

print(customers_clean["customer_segment"].value_counts(dropna=False))
customer_segment
Regular    69
New        55
Loyal      44
Name: count, dtype: int64[pyarrow]
# Periksa format tanggal dari data RAW, tanpa mengubahnya
print("Contoh signup_date asli:")
print(customers_raw["signup_date"].head(20).to_string(index=True))

print("\nContoh nilai yang gagal dikonversi:")
failed_index = customers_clean.index[
    customers_clean["signup_date"].isna()
]
print(customers_raw.loc[failed_index, ["customer_id", "signup_date"]]
      .head(20).to_string(index=False))
Contoh signup_date asli:
0     2025-12-03
1     2025-08-08
2     2025-12-06
3     2025-10-24
4     2025-12-09
5     2025-06-13
6     2025-09-07
7     2025-12-20
8     2025-11-30
9     2025-08-14
10    2025-11-25
11    2025-09-30
12    2025-10-26
13    2025-12-04
14    2025-10-08
15    2025-11-27
16    2025-07-04
17    2025-09-10
18    2025-11-23
19    2025-09-14

Contoh nilai yang gagal dikonversi:
customer_id signup_date
      C0004  2025-10-24
      C0006  2025-06-13
      C0008  2025-12-20
      C0009  2025-11-30
      C0010  2025-08-14
      C0011  2025-11-25
      C0012  2025-09-30
      C0013  2025-10-26
      C0016  2025-11-27
      C0019  2025-11-23
      C0020  2025-09-14
      C0021  2025-06-30
      C0022  14/09/2025
      C0024  2025-08-28
      C0026  2025-11-26
      C0027  2025-07-18
      C0028  2025-09-16
      C0029  2025-12-15
      C0030  2025-09-24
      C0031  2025-10-30
# CR-03 — Konversi signup_date dari data RAW
# Sumber asli tetap dipertahankan.

customers_clean["signup_date"] = pd.to_datetime(
    customers_raw.loc[customers_clean.index, "signup_date"],
    format="%Y-%m-%d",
    errors="coerce"
)

print("Tipe data:", customers_clean["signup_date"].dtype)
print("Tanggal berhasil dikonversi:", customers_clean["signup_date"].notna().sum())
print("Tanggal gagal dikonversi:", customers_clean["signup_date"].isna().sum())
Tipe data: datetime64[us]
Tanggal berhasil dikonversi: 166
Tanggal gagal dikonversi: 2
# CR-03 — Cari tanggal yang gagal dikonversi

date_check = pd.DataFrame({
    "customer_id": customers_raw.loc[
        customers_clean.index, "customer_id"
    ],
    "signup_date_raw": customers_raw.loc[
        customers_clean.index, "signup_date"
    ],
    "signup_date_clean": customers_clean["signup_date"]
})

display(date_check[date_check["signup_date_clean"].isna()])
customer_id signup_date_raw signup_date_clean
21 C0022 14/09/2025 NaT
68 C0069 Jul 08 2025 NaT
# CR-03 — Perbaiki dua format tanggal yang belum terbaca
# Ambil ulang data dari RAW agar tidak memakai nilai NaT sebelumnya.

raw_dates = customers_raw.loc[
    customers_clean.index, "signup_date"
].astype("string").str.strip()

# Parsing format utama YYYY-MM-DD
parsed_dates = pd.to_datetime(
    raw_dates, format="%Y-%m-%d", errors="coerce"
)

# Parsing format DD/MM/YYYY untuk nilai yang belum terbaca
mask = parsed_dates.isna()
parsed_dates.loc[mask] = pd.to_datetime(
    raw_dates.loc[mask], format="%d/%m/%Y", errors="coerce"
)

# Parsing format nama bulan seperti Jul 08 2025
mask = parsed_dates.isna()
parsed_dates.loc[mask] = pd.to_datetime(
    raw_dates.loc[mask], format="%b %d %Y", errors="coerce"
)

customers_clean["signup_date"] = parsed_dates

print("Tanggal berhasil dikonversi:", customers_clean["signup_date"].notna().sum())
print("Tanggal gagal dikonversi:", customers_clean["signup_date"].isna().sum())
Tanggal berhasil dikonversi: 168
Tanggal gagal dikonversi: 0
print(
    payments_clean["payment_method"]
    .value_counts(dropna=False)
    .to_string()
)
payment_method
cash             98
bank_transfer    95
e_wallet         94
credit_card      93
credit card       1
# CR-09 — Standardisasi payment_method
payments_clean["payment_method"] = (
    payments_clean["payment_method"]
    .astype("string")
    .str.strip()
    .str.lower()
    .replace({"credit card": "credit_card"})
)

print(payments_clean["payment_method"].value_counts(dropna=False).to_string())
payment_method
cash             98
bank_transfer    95
e_wallet         94
credit_card      94
print(
    customers_raw.loc[
        customers_raw["customer_id"].duplicated(keep=False)
    ].sort_values("customer_id").to_string(index=False)
)
customer_id     full_name                      email phone_number signup_date customer_zone customer_segment
      C0031 Dimas Pratama dimaspratama31@example.com 085368771270  2025-10-30       Central            Loyal
      C0031 Dimas Pratama dimaspratama31@example.com 085368771270  2025-10-30       Central            Loyal
# Perbarui hasil akhir pada Cleaning Log

for i, row in cleaning_log.iterrows():
    if row["rule_id"] == "CR-02":
        cleaning_log.loc[i, "after"] = (
            f"{customers_clean['customer_segment'].eq('NEW').sum()} nilai NEW tersisa"
        )

    elif row["rule_id"] == "CR-03":
        cleaning_log.loc[i, "after"] = (
            f"{customers_clean['signup_date'].notna().sum()} berhasil dikonversi; "
            f"{customers_clean['signup_date'].isna().sum()} gagal"
        )

    elif row["rule_id"] == "CR-09":
        cleaning_log.loc[i, "after"] = (
            f"{payments_clean['payment_method'].nunique(dropna=True)} kategori"
        )

display(cleaning_log)
rule_id dataset field before after action
0 CR-01 customers customer_id 169 baris; 2 baris dengan ID berulang 168 baris; 0 baris dengan ID berulang Menghapus baris identik; duplikasi ID yang ter...
1 CR-02 customers customer_segment 0 nilai NEW 0 nilai NEW tersisa Mengubah NEW menjadi New
2 CR-03 customers signup_date 168 nilai terisi 168 berhasil dikonversi; 0 gagal Konversi ke tipe tanggal; periksa nilai yang m...
3 CR-04 orders order_timestamp 350 nilai terisi 350 nilai berhasil dikonversi Konversi ke tipe tanggal dan waktu; periksa ni...
4 CR-05 orders order_status 3 kategori 2 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
5 CR-06 order_items quantity 972 nilai numerik sebelum penanganan satuan 974 nilai numerik setelah konversi Konversi quantity ke numerik setelah penangana...
6 CR-07 order_items quantity Nilai quantity berpotensi mengandung akhiran pcs 0 nilai masih kosong atau gagal dikonversi Menghapus akhiran pcs lalu mengonversi bagian ...
7 CR-08 payments payment_timestamp 381 nilai terisi 381 nilai berhasil dikonversi Konversi ke tipe tanggal dan waktu; periksa ni...
8 CR-09 payments payment_method 6 kategori 4 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
9 CR-10 restaurants onboarding_date 25 nilai terisi 25 nilai berhasil dikonversi Konversi ke tipe tanggal; periksa nilai NaT
10 CR-11 restaurants price_tier 4 kategori 3 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
# Perbaiki catatan Cleaning Log berdasarkan hasil yang sudah diverifikasi

for i, row in cleaning_log.iterrows():
    if row["rule_id"] == "CR-02":
        cleaning_log.loc[i, "before"] = "2 nilai NEW"
        cleaning_log.loc[i, "after"] = (
            f"{customers_clean['customer_segment'].eq('NEW').sum()} nilai NEW tersisa"
        )

    elif row["rule_id"] == "CR-03":
        cleaning_log.loc[i, "before"] = "168 nilai terisi"
        cleaning_log.loc[i, "after"] = (
            f"{customers_clean['signup_date'].notna().sum()} berhasil dikonversi; "
            f"{customers_clean['signup_date'].isna().sum()} gagal"
        )

    elif row["rule_id"] == "CR-09":
        cleaning_log.loc[i, "after"] = (
            f"{payments_clean['payment_method'].nunique(dropna=True)} kategori"
        )

display(cleaning_log)
rule_id dataset field before after action
0 CR-01 customers customer_id 169 baris; 2 baris dengan ID berulang 168 baris; 0 baris dengan ID berulang Menghapus baris identik; duplikasi ID yang ter...
1 CR-02 customers customer_segment 2 nilai NEW 0 nilai NEW tersisa Mengubah NEW menjadi New
2 CR-03 customers signup_date 168 nilai terisi 168 berhasil dikonversi; 0 gagal Konversi ke tipe tanggal; periksa nilai yang m...
3 CR-04 orders order_timestamp 350 nilai terisi 350 nilai berhasil dikonversi Konversi ke tipe tanggal dan waktu; periksa ni...
4 CR-05 orders order_status 3 kategori 2 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
5 CR-06 order_items quantity 972 nilai numerik sebelum penanganan satuan 974 nilai numerik setelah konversi Konversi quantity ke numerik setelah penangana...
6 CR-07 order_items quantity Nilai quantity berpotensi mengandung akhiran pcs 0 nilai masih kosong atau gagal dikonversi Menghapus akhiran pcs lalu mengonversi bagian ...
7 CR-08 payments payment_timestamp 381 nilai terisi 381 nilai berhasil dikonversi Konversi ke tipe tanggal dan waktu; periksa ni...
8 CR-09 payments payment_method 6 kategori 4 kategori Menghapus spasi di tepi dan menyeragamkan kapi...
9 CR-10 restaurants onboarding_date 25 nilai terisi 25 nilai berhasil dikonversi Konversi ke tipe tanggal; periksa nilai NaT
10 CR-11 restaurants price_tier 4 kategori 3 kategori Menghapus spasi di tepi dan menyeragamkan kapi...

Cleaning Log Register

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

cleaning_log = pd.DataFrame(columns=CLEANING_LOG_COLUMNS)
display(cleaning_log)
# ============================================================
# CLEANING LOG — berdasarkan hasil implementasi cleaning
# ============================================================

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",
        "affected_records_scope": "Baris customer dengan customer_id duplikat identik",
        "action_performed": "Menghapus baris duplikat identik menggunakan drop_duplicates()",
        "before_evidence": "169 baris; 2 baris memiliki customer_id berulang",
        "after_evidence": "168 baris; 0 customer_id duplikat tersisa",
        "rows_records_affected": 1,
        "result": "SUCCESS",
        "notes": "Satu baris duplikat identik dihapus. Pastikan bukti baris duplikat tersedia."
    },
    {
        "log_id": "CL-02",
        "rule_id": "CR-02",
        "dataset": "customers",
        "affected_records_scope": "Kolom customer_segment dengan kategori NEW",
        "action_performed": "Menyeragamkan NEW menjadi New",
        "before_evidence": "2 nilai NEW",
        "after_evidence": "0 nilai NEW; kategori New berjumlah 55",
        "rows_records_affected": 2,
        "result": "SUCCESS",
        "notes": "Kategori akhir terdiri dari Regular, New, dan Loyal."
    },
    {
        "log_id": "CL-03",
        "rule_id": "CR-03",
        "dataset": "customers",
        "affected_records_scope": "Seluruh nilai signup_date",
        "action_performed": "Mengonversi tanggal ke tipe datetime dengan beberapa format tanggal",
        "before_evidence": "168 nilai terisi; ditemukan format YYYY-MM-DD, DD/MM/YYYY, dan Mon DD YYYY",
        "after_evidence": "168 tanggal berhasil dikonversi; 0 gagal",
        "rows_records_affected": 168,
        "result": "SUCCESS",
        "notes": "Data diambil kembali dari customers_raw untuk memperbaiki hasil konversi sebelumnya."
    },
    {
        "log_id": "CL-04",
        "rule_id": "CR-04",
        "dataset": "orders",
        "affected_records_scope": "Seluruh nilai order_timestamp",
        "action_performed": "Mengonversi order_timestamp ke tipe datetime",
        "before_evidence": "350 nilai terisi",
        "after_evidence": "350 nilai berhasil dikonversi",
        "rows_records_affected": 350,
        "result": "SUCCESS",
        "notes": "Konversi berhasil; pemeriksaan nilai tanggal setelah konversi tetap disarankan."
    },
    {
        "log_id": "CL-05",
        "rule_id": "CR-05",
        "dataset": "orders",
        "affected_records_scope": "Kolom order_status",
        "action_performed": "Menghapus spasi di tepi dan menyeragamkan kapitalisasi menjadi huruf besar",
        "before_evidence": "3 kategori",
        "after_evidence": "2 kategori",
        "rows_records_affected": "Perlu konfirmasi jumlah baris yang berubah",
        "result": "SUCCESS",
        "notes": "Jumlah kategori berkurang; makna kategori perlu dipastikan tetap konsisten."
    },
    {
        "log_id": "CL-06",
        "rule_id": "CR-06",
        "dataset": "order_items",
        "affected_records_scope": "Kolom quantity",
        "action_performed": "Mengonversi quantity menjadi numerik setelah penanganan teks satuan",
        "before_evidence": "972 nilai dapat dikonversi menjadi numerik sebelum penanganan pcs",
        "after_evidence": "974 nilai numerik",
        "rows_records_affected": 2,
        "result": "SUCCESS",
        "notes": "Dua nilai tambahan berhasil dibaca setelah penanganan teks pcs."
    },
    {
        "log_id": "CL-07",
        "rule_id": "CR-07",
        "dataset": "order_items",
        "affected_records_scope": "Nilai quantity yang mengandung akhiran pcs",
        "action_performed": "Menghapus akhiran pcs lalu mengonversi nilai menjadi numerik",
        "before_evidence": "Ditemukan 2 nilai quantity yang mengandung teks pcs",
        "after_evidence": "0 nilai quantity kosong atau gagal dikonversi",
        "rows_records_affected": 2,
        "result": "SUCCESS",
        "notes": "Quantity telah bertipe Int64; pastikan nilai hasil konversi sesuai nilai angka sumber."
    },
    {
        "log_id": "CL-08",
        "rule_id": "CR-08",
        "dataset": "payments",
        "affected_records_scope": "Seluruh nilai payment_timestamp",
        "action_performed": "Mengonversi payment_timestamp ke tipe datetime",
        "before_evidence": "381 nilai terisi",
        "after_evidence": "381 nilai berhasil dikonversi",
        "rows_records_affected": 381,
        "result": "SUCCESS",
        "notes": "Konversi berhasil; pemeriksaan nilai tanggal setelah konversi tetap disarankan."
    },
    {
        "log_id": "CL-09",
        "rule_id": "CR-09",
        "dataset": "payments",
        "affected_records_scope": "Kolom payment_method",
        "action_performed": "Menghapus spasi, menyeragamkan huruf kecil, dan menyatukan credit card menjadi credit_card",
        "before_evidence": "6 kategori",
        "after_evidence": "4 kategori; credit_card berjumlah 94",
        "rows_records_affected": 1,
        "result": "SUCCESS",
        "notes": "Kategori credit card dan credit_card disatukan berdasarkan asumsi bahwa keduanya bermakna sama."
    },
    {
        "log_id": "CL-10",
        "rule_id": "CR-10",
        "dataset": "restaurants",
        "affected_records_scope": "Seluruh nilai onboarding_date",
        "action_performed": "Mengonversi onboarding_date ke tipe datetime",
        "before_evidence": "25 nilai terisi",
        "after_evidence": "25 tanggal berhasil dikonversi",
        "rows_records_affected": 25,
        "result": "SUCCESS",
        "notes": "Konversi berhasil; pemeriksaan nilai tanggal setelah konversi tetap disarankan."
    },
    {
        "log_id": "CL-11",
        "rule_id": "CR-11",
        "dataset": "restaurants",
        "affected_records_scope": "Kolom price_tier",
        "action_performed": "Menghapus spasi di tepi dan menyeragamkan kapitalisasi menggunakan title()",
        "before_evidence": "4 kategori",
        "after_evidence": "3 kategori",
        "rows_records_affected": "Perlu konfirmasi jumlah baris yang berubah",
        "result": "SUCCESS",
        "notes": "Jumlah kategori berkurang; nilai kategori akhir perlu diperiksa."
    },
], 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 Baris customer dengan customer_id duplikat ide... Menghapus baris duplikat identik menggunakan d... 169 baris; 2 baris memiliki customer_id berulang 168 baris; 0 customer_id duplikat tersisa 1 SUCCESS Satu baris duplikat identik dihapus. Pastikan ...
1 CL-02 CR-02 customers Kolom customer_segment dengan kategori NEW Menyeragamkan NEW menjadi New 2 nilai NEW 0 nilai NEW; kategori New berjumlah 55 2 SUCCESS Kategori akhir terdiri dari Regular, New, dan ...
2 CL-03 CR-03 customers Seluruh nilai signup_date Mengonversi tanggal ke tipe datetime dengan be... 168 nilai terisi; ditemukan format YYYY-MM-DD,... 168 tanggal berhasil dikonversi; 0 gagal 168 SUCCESS Data diambil kembali dari customers_raw untuk ...
3 CL-04 CR-04 orders Seluruh nilai order_timestamp Mengonversi order_timestamp ke tipe datetime 350 nilai terisi 350 nilai berhasil dikonversi 350 SUCCESS Konversi berhasil; pemeriksaan nilai tanggal s...
4 CL-05 CR-05 orders Kolom order_status Menghapus spasi di tepi dan menyeragamkan kapi... 3 kategori 2 kategori Perlu konfirmasi jumlah baris yang berubah SUCCESS Jumlah kategori berkurang; makna kategori perl...
5 CL-06 CR-06 order_items Kolom quantity Mengonversi quantity menjadi numerik setelah p... 972 nilai dapat dikonversi menjadi numerik seb... 974 nilai numerik 2 SUCCESS Dua nilai tambahan berhasil dibaca setelah pen...
6 CL-07 CR-07 order_items Nilai quantity yang mengandung akhiran pcs Menghapus akhiran pcs lalu mengonversi nilai m... Ditemukan 2 nilai quantity yang mengandung tek... 0 nilai quantity kosong atau gagal dikonversi 2 SUCCESS Quantity telah bertipe Int64; pastikan nilai h...
7 CL-08 CR-08 payments Seluruh nilai payment_timestamp Mengonversi payment_timestamp ke tipe datetime 381 nilai terisi 381 nilai berhasil dikonversi 381 SUCCESS Konversi berhasil; pemeriksaan nilai tanggal s...
8 CL-09 CR-09 payments Kolom payment_method Menghapus spasi, menyeragamkan huruf kecil, da... 6 kategori 4 kategori; credit_card berjumlah 94 1 SUCCESS Kategori credit card dan credit_card disatukan...
9 CL-10 CR-10 restaurants Seluruh nilai onboarding_date Mengonversi onboarding_date ke tipe datetime 25 nilai terisi 25 tanggal berhasil dikonversi 25 SUCCESS Konversi berhasil; pemeriksaan nilai tanggal s...
10 CL-11 CR-11 restaurants Kolom price_tier Menghapus spasi di tepi dan menyeragamkan kapi... 4 kategori 3 kategori Perlu konfirmasi jumlah baris yang berubah SUCCESS Jumlah kategori berkurang; nilai kategori akhi...

13. Validation Harness — EDIT + RUN

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

`V-01` sampai `V-15`

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

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

validation_results = []

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

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

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

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

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

13A. Schema & Key Validation — EDIT + RUN

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

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

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

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

    # V-01: Schema validation
    raw_tables = {
        "Customers": customers_raw,
        "Orders": orders_raw,
        "Order Items": order_items_raw,
        "Payments": payments_raw,
        "Restaurants": restaurants_raw,
    }

    missing_columns = {
        name: sorted(set(raw_tables[name].columns) - set(clean_tables[name].columns))
        for name in clean_tables
    }
    missing_columns = {
        name: cols for name, cols in missing_columns.items() if cols
    }

    record_check(
        "V-01", "Schema", "All clean datasets",
        "Apakah seluruh kolom source masih tersedia setelah cleaning?",
        "Tidak ada kolom source yang hilang",
        {"missing_columns": missing_columns},
        len(missing_columns) == 0,
        notes="Membandingkan kolom raw dan clean pada setiap dataset."
    )

    # V-02: Customers key
    nulls = int(customers_clean["customer_id"].isna().sum())
    duplicates = int(customers_clean["customer_id"].duplicated().sum())

    record_check(
        "V-02", "Key", "Customers",
        "Apakah customer_id tidak null dan unik?",
        "Tidak ada null key dan duplicate key",
        {"null_keys": nulls, "duplicate_keys": duplicates},
        nulls == 0 and duplicates == 0,
        related_rule_id="CR-01"
    )

    # V-03: Orders key
    nulls = int(orders_clean["order_id"].isna().sum())
    duplicates = int(orders_clean["order_id"].duplicated().sum())

    record_check(
        "V-03", "Key", "Orders",
        "Apakah order_id tidak null dan satu baris mewakili satu order?",
        "Tidak ada null key dan duplicate key",
        {"null_keys": nulls, "duplicate_keys": duplicates},
        nulls == 0 and duplicates == 0
    )

    # V-04: Order Items key
    nulls = int(order_items_clean["order_item_id"].isna().sum())
    duplicates = int(order_items_clean["order_item_id"].duplicated().sum())

    record_check(
        "V-04", "Key", "Order Items",
        "Apakah order_item_id tidak null dan unik?",
        "Tidak ada null key dan duplicate key",
        {"null_keys": nulls, "duplicate_keys": duplicates},
        nulls == 0 and duplicates == 0
    )

    # V-05: Payments key
    nulls = int(payments_clean["payment_id"].isna().sum())
    duplicates = int(payments_clean["payment_id"].duplicated().sum())
    repeated_orders = int(
        payments_clean["order_id"].duplicated(keep=False).sum()
    )

    record_check(
        "V-05", "Key", "Payments",
        "Apakah payment_id unik tanpa menganggap repeated order_id sebagai duplikat?",
        "payment_id unik; satu order boleh memiliki beberapa payment",
        {
            "null_payment_id": nulls,
            "duplicate_payment_id": duplicates,
            "rows_with_repeated_order_id": repeated_orders
        },
        nulls == 0 and duplicates == 0,
        notes="Repeated order_id tidak otomatis dianggap masalah."
    )

    print("V-01 sampai V-05 selesai dicatat.")

    pass
else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes:
Customers (168, 7)
Orders (350, 10)
Order Items (974, 9)
Payments (381, 7)
Restaurants (25, 7)
V-01 sampai V-05 selesai dicatat.
validation_df = pd.DataFrame(validation_results)

display(validation_df[
    validation_df["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 kolom source masih tersedia set... Tidak ada kolom source yang hilang {'missing_columns': {}} PASS Membandingkan kolom raw dan clean pada setiap ...
1 V-02 Key Customers Apakah customer_id tidak null dan unik? Tidak ada null key dan duplicate key {'null_keys': 0, 'duplicate_keys': 0} PASS CR-01
2 V-03 Key Orders Apakah order_id tidak null dan satu baris mewa... Tidak ada null key dan duplicate key {'null_keys': 0, 'duplicate_keys': 0} PASS
3 V-04 Key Order Items Apakah order_item_id tidak null dan unik? Tidak ada null key dan duplicate key {'null_keys': 0, 'duplicate_keys': 0} PASS
4 V-05 Key Payments Apakah payment_id unik tanpa menganggap repeat... payment_id unik; satu order boleh memiliki beb... {'null_payment_id': 0, 'duplicate_payment_id':... PASS Repeated order_id tidak otomatis dianggap masa...

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.

import numpy as np
if DATA_READY:

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

    # V-06: Customers category
    customer_segments = set(
        customers_clean["customer_segment"]
        .dropna().astype(str).str.strip()
    )
    expected_segments = {"Regular", "New", "Loyal"}

    record_check(
        "V-06", "Value", "Customers / categories",
        "Apakah customer_segment sudah konsisten?",
        "Kategori Regular, New, Loyal; missing sesuai keputusan cleaning",
        {
            "categories_found": sorted(customer_segments),
            "missing_count": int(
                customers_clean["customer_segment"].isna().sum()
            )
        },
        customer_segments.issubset(expected_segments),
        related_rule_id="CR-02"
    )

    # V-07: Orders category
    statuses = set(
        orders_clean["order_status"]
        .dropna().astype(str).str.strip()
    )
    status_is_canonical = all(s == s.upper() for s in statuses)

    record_check(
        "V-07", "Value", "Orders / categories",
        "Apakah order_status sudah menggunakan format konsisten?",
        "Semua status menggunakan huruf kapital",
        {"statuses_found": sorted(statuses)},
        status_is_canonical,
        related_rule_id="CR-05"
    )

    # V-08: Payments and Restaurants categories
    payment_methods = set(
        payments_clean["payment_method"]
        .dropna().astype(str).str.strip()
    )
    price_tiers = set(
        restaurants_clean["price_tier"]
        .dropna().astype(str).str.strip()
    )

    expected_methods = {
        "cash", "bank_transfer", "e_wallet", "credit_card"
    }
    expected_tiers = {"Budget", "Mid", "Premium"}

    record_check(
        "V-08", "Value", "Payments / Restaurants",
        "Apakah kategori payment_method dan price_tier konsisten?",
        "Hanya kategori yang disetujui",
        {
            "payment_methods": sorted(payment_methods),
            "price_tiers": sorted(price_tiers)
        },
        payment_methods.issubset(expected_methods)
        and price_tiers.issubset(expected_tiers),
        related_rule_id="CR-09; CR-11"
    )

    # V-09: Order Items formula
    item_calc = (
        pd.to_numeric(order_items_clean["quantity"], errors="coerce")
        * pd.to_numeric(order_items_clean["unit_price"], errors="coerce")
        - pd.to_numeric(
            order_items_clean["line_discount"], errors="coerce"
        )
    )
    item_actual = pd.to_numeric(
        order_items_clean["line_amount"], errors="coerce"
    )

    item_comparable = item_calc.notna() & item_actual.notna()
    item_mismatch = item_comparable & ~np.isclose(
        item_calc.fillna(0),
        item_actual.fillna(0),
        rtol=0,
        atol=0.01
    )

    record_check(
        "V-09", "Business Rule", "Order Items",
        "Apakah line_amount sesuai quantity × unit_price - line_discount?",
        "Semua baris yang dapat dibandingkan harus cocok",
        {
            "comparable_rows": int(item_comparable.sum()),
            "mismatch_rows": int(item_mismatch.sum()),
            "mismatch_sample": order_items_clean.loc[
                item_mismatch,
                [
                    "order_item_id", "quantity", "unit_price",
                    "line_discount", "line_amount"
                ]
            ].head(10).to_dict("records")
        },
        bool(item_comparable.all() and not item_mismatch.any()),
        related_rule_id="CR-06; CR-07"
    )

    # V-10: Orders total formula
    order_calc = (
        pd.to_numeric(orders_clean["subtotal_amount"], errors="coerce")
        + pd.to_numeric(orders_clean["delivery_fee"], errors="coerce")
        - pd.to_numeric(orders_clean["discount_amount"], errors="coerce")
    )
    order_actual = pd.to_numeric(
        orders_clean["total_amount"], errors="coerce"
    )

    order_comparable = order_calc.notna() & order_actual.notna()
    order_mismatch = order_comparable & ~np.isclose(
        order_calc.fillna(0),
        order_actual.fillna(0),
        rtol=0,
        atol=0.01
    )

    record_check(
        "V-10", "Business Rule", "Orders",
        "Apakah total_amount sesuai subtotal + delivery fee - discount?",
        "Semua baris yang dapat dibandingkan harus cocok",
        {
            "comparable_rows": int(order_comparable.sum()),
            "mismatch_rows": int(order_mismatch.sum()),
            "mismatch_sample": orders_clean.loc[
                order_mismatch,
                [
                    "order_id", "subtotal_amount", "delivery_fee",
                    "discount_amount", "total_amount"
                ]
            ].head(10).to_dict("records")
        },
        bool(order_comparable.all() and not order_mismatch.any())
    )

    # V-11: Orders ↔ Order Items reconciliation
    item_totals = (
        order_items_clean.groupby("order_id")["line_amount"].sum()
    )

    reconciliation = orders_clean[
        ["order_id", "subtotal_amount"]
    ].copy()

    reconciliation["item_total"] = (
        reconciliation["order_id"].map(item_totals)
    )

    recon_comparable = (
        reconciliation["subtotal_amount"].notna()
        & reconciliation["item_total"].notna()
    )

    recon_mismatch = recon_comparable & ~np.isclose(
        pd.to_numeric(
            reconciliation["subtotal_amount"], errors="coerce"
        ).fillna(0),
        pd.to_numeric(
            reconciliation["item_total"], errors="coerce"
        ).fillna(0),
        rtol=0,
        atol=0.01
    )

    record_check(
        "V-11", "Cross-table", "Orders ↔ Order Items",
        "Apakah subtotal order sama dengan jumlah line_amount?",
        "SUM(line_amount) = subtotal_amount",
        {
            "comparable_orders": int(recon_comparable.sum()),
            "mismatch_orders": int(recon_mismatch.sum()),
            "mismatch_sample": reconciliation.loc[
                recon_mismatch
            ].head(10).to_dict("records")
        },
        bool(recon_comparable.all() and not recon_mismatch.any()),
        notes="Mismatch perlu ditinjau; nominal tidak dikoreksi otomatis."
    )

    # Helper untuk foreign key validation
    def unmatched_fk(child, child_key, parent, parent_key):
        parent_keys = set(parent[parent_key].dropna())
        return (
            child[child_key].isna()
            | ~child[child_key].isin(parent_keys)
        )

    # V-12: Orders → Customers
    unmatched = unmatched_fk(
        orders_clean, "customer_id",
        customers_clean, "customer_id"
    )

    record_check(
        "V-12", "Cross-table", "Orders → Customers",
        "Apakah seluruh customer reference valid?",
        "Unmatched customer = 0",
        {
            "unmatched_count": int(unmatched.sum()),
            "sample": orders_clean.loc[
                unmatched, "customer_id"
            ].head(10).tolist()
        },
        int(unmatched.sum()) == 0
    )

    # V-13: Orders → Restaurants
    unmatched = unmatched_fk(
        orders_clean, "restaurant_id",
        restaurants_clean, "restaurant_id"
    )

    record_check(
        "V-13", "Cross-table", "Orders → Restaurants",
        "Apakah seluruh restaurant reference valid?",
        "Unmatched restaurant = 0",
        {
            "unmatched_count": int(unmatched.sum()),
            "sample": orders_clean.loc[
                unmatched, "restaurant_id"
            ].head(10).tolist()
        },
        int(unmatched.sum()) == 0
    )

    # V-14: Order Items / Payments → Orders
    item_unmatched = unmatched_fk(
        order_items_clean, "order_id",
        orders_clean, "order_id"
    )

    payment_unmatched = unmatched_fk(
        payments_clean, "order_id",
        orders_clean, "order_id"
    )

    record_check(
        "V-14", "Cross-table", "Order Items / Payments → Orders",
        "Apakah seluruh order reference valid?",
        "Tidak ada orphan order reference",
        {
            "order_items_unmatched": int(item_unmatched.sum()),
            "payments_unmatched": int(payment_unmatched.sum())
        },
        int(item_unmatched.sum()) == 0
        and int(payment_unmatched.sum()) == 0
    )

    print("V-06 sampai V-14 selesai dicatat.")

else:
    print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
V-06 sampai V-14 selesai dicatat.
validation_df = pd.DataFrame(validation_results)

display(
    validation_df[
        validation_df["check_id"].isin(
            [f"V-{i:02d}" for i in range(6, 15)]
        )
    ]
)
check_id validation_level dataset_relationship validation_question expected_criteria actual_result_metric status related_rule_id notes
5 V-06 Value Customers / categories Apakah customer_segment sudah konsisten? Kategori Regular, New, Loyal; missing sesuai k... {'categories_found': ['Loyal', 'New', 'Regular... PASS CR-02
6 V-07 Value Orders / categories Apakah order_status sudah menggunakan format k... Semua status menggunakan huruf kapital {'statuses_found': ['CANCELLED', 'COMPLETED']} PASS CR-05
7 V-08 Value Payments / Restaurants Apakah kategori payment_method dan price_tier ... Hanya kategori yang disetujui {'payment_methods': ['bank_transfer', 'cash', ... PASS CR-09; CR-11
8 V-09 Business Rule Order Items Apakah line_amount sesuai quantity × unit_pric... Semua baris yang dapat dibandingkan harus cocok {'comparable_rows': 974, 'mismatch_rows': 0, '... PASS CR-06; CR-07
9 V-10 Business Rule Orders Apakah total_amount sesuai subtotal + delivery... Semua baris yang dapat dibandingkan harus cocok {'comparable_rows': 350, 'mismatch_rows': 0, '... PASS
10 V-11 Cross-table Orders ↔ Order Items Apakah subtotal order sama dengan jumlah line_... SUM(line_amount) = subtotal_amount {'comparable_orders': 350, 'mismatch_orders': ... PASS Mismatch perlu ditinjau; nominal tidak dikorek...
11 V-12 Cross-table Orders → Customers Apakah seluruh customer reference valid? Unmatched customer = 0 {'unmatched_count': 0, 'sample': []} PASS
12 V-13 Cross-table Orders → Restaurants Apakah seluruh restaurant reference valid? Unmatched restaurant = 0 {'unmatched_count': 0, 'sample': []} PASS
13 V-14 Cross-table Order Items / Payments → Orders Apakah seluruh order reference valid? Tidak ada orphan order reference {'order_items_unmatched': 0, 'payments_unmatch... PASS
import pandas as pd

oi = order_items_clean.copy()

oi["expected_line_amount"] = (
    pd.to_numeric(oi["quantity"], errors="coerce")
    * pd.to_numeric(oi["unit_price"], errors="coerce")
)

oi["actual_line_amount"] = pd.to_numeric(
    oi["line_amount"], errors="coerce"
)

v09_mismatch = oi[
    ~(
        (oi["expected_line_amount"] - oi["actual_line_amount"])
        .abs() < 0.01
    )
    & oi["expected_line_amount"].notna()
    & oi["actual_line_amount"].notna()
]

print("Jumlah mismatch V-09:", len(v09_mismatch))
print(v09_mismatch[
    ["order_item_id", "order_id", "quantity",
     "unit_price", "line_amount", "expected_line_amount"]
].to_string(index=False))
Jumlah mismatch V-09: 402
order_item_id order_id  quantity  unit_price  line_amount  expected_line_amount
      OI00001   O00001         1       16000        14000                 16000
      OI00006   O00003         3       28000        82000                 84000
      OI00012   O00005         1       28000        23000                 28000
      OI00016   O00006         2       22000        39000                 44000
      OI00017   O00006         3       22000        61000                 66000
      OI00020   O00008         3       20000        58000                 60000
      OI00021   O00008         2       26000        50000                 52000
      OI00022   O00009         1       18000        13000                 18000
      OI00027   O00010         2       32000        62000                 64000
      OI00030   O00011         2       18000        31000                 36000
      OI00035   O00013         3       28000        79000                 84000
      OI00036   O00013         1       22000        17000                 22000
      OI00037   O00013         1       20000        15000                 20000
      OI00040   O00014         3       42000       124000                126000
      OI00044   O00016         3       32000        91000                 96000
      OI00048   O00017         1       20000        15000                 20000
      OI00050   O00017         2       22000        39000                 44000
      OI00055   O00020         1       24000        22000                 24000
      OI00057   O00021         4       32000       126000                128000
      OI00059   O00022         2       20000        38000                 40000
      OI00060   O00022         4       24000        91000                 96000
      OI00062   O00023         3       42000       121000                126000
      OI00064   O00023         3       32000        94000                 96000
      OI00068   O00025         1       18000        13000                 18000
      OI00072   O00026         3       18000        52000                 54000
      OI00074   O00027         3       42000       121000                126000
      OI00076   O00028         4       42000       166000                168000
      OI00078   O00029         1       26000        21000                 26000
      OI00081   O00030         2       28000        51000                 56000
      OI00086   O00031         1       20000        18000                 20000
      OI00087   O00032         1       32000        27000                 32000
      OI00093   O00034         2       26000        50000                 52000
      OI00094   O00034         2       20000        38000                 40000
      OI00097   O00035         3       18000        52000                 54000
      OI00098   O00036         1       20000        15000                 20000
      OI00102   O00037         1       16000        11000                 16000
      OI00103   O00038         2       28000        54000                 56000
      OI00104   O00038         1       20000        15000                 20000
      OI00106   O00039         3       18000        52000                 54000
      OI00110   O00041         1       22000        17000                 22000
      OI00111   O00041         1       42000        40000                 42000
      OI00114   O00042         2       32000        59000                 64000
      OI00115   O00043         2       42000        79000                 84000
      OI00118   O00044         3        9000        22000                 27000
      OI00119   O00044         1       22000        17000                 22000
      OI00122   O00045         3       16000        46000                 48000
      OI00123   O00045         3       32000        91000                 96000
      OI00124   O00046         2       20000        38000                 40000
      OI00127   O00046         3       16000        43000                 48000
      OI00128   O00047         1        9000         7000                  9000
      OI00131   O00048         2       22000        51000                 44000
      OI00132   O00048         1       20000        18000                 20000
      OI00133   O00049         3       22000        64000                 66000
      OI00141   O00052         1       26000        24000                 26000
      OI00145   O00053         2       18000        31000                 36000
      OI00146   O00053         1       26000        24000                 26000
      OI00148   O00054         3       24000        67000                 72000
      OI00149   O00054         2       20000        35000                 40000
      OI00150   O00054         2       22000        42000                 44000
      OI00151   O00055         4       32000       123000                128000
      OI00154   O00056         4       22000        83000                 88000
      OI00155   O00056         3        9000        25000                 27000
      OI00156   O00056         3       28000        79000                 84000
      OI00157   O00056         3       24000        70000                 72000
      OI00160   O00057         3       32000        91000                 96000
      OI00161   O00058         2       42000        79000                 84000
      OI00163   O00059         1       20000        15000                 20000
      OI00164   O00059         3       20000        58000                 60000
      OI00168   O00060         1       24000        19000                 24000
      OI00169   O00060         2       18000        34000                 36000
      OI00173   O00062         2       26000        50000                 52000
      OI00176   O00063         3       28000        82000                 84000
      OI00181   O00065         2       20000        38000                 40000
      OI00182   O00066         1       28000        26000                 28000
      OI00183   O00066         3       22000        61000                 66000
      OI00187   O00068         2       42000        82000                 84000
      OI00191   O00069         1       20000        15000                 20000
      OI00194   O00070         1       32000        30000                 32000
      OI00195   O00070         2       28000        51000                 56000
      OI00201   O00072         3       32000        91000                 96000
      OI00203   O00072         3       18000        49000                 54000
      OI00206   O00073         3       16000        43000                 48000
      OI00215   O00076         3       20000        55000                 60000
      OI00223   O00079         2       24000        43000                 48000
      OI00226   O00081         3       42000       121000                126000
      OI00228   O00082         2       32000        62000                 64000
      OI00229   O00083         3       16000        46000                 48000
      OI00230   O00083         3       18000        52000                 54000
      OI00234   O00085         3       20000        58000                 60000
      OI00237   O00086         1       20000        18000                 20000
      OI00239   O00087         1       32000        27000                 32000
      OI00240   O00087         3       42000       121000                126000
      OI00242   O00089         3       20000        58000                 60000
      OI00246   O00090         1       16000        11000                 16000
      OI00249   O00091         2       22000        42000                 44000
      OI00250   O00091         1       28000        23000                 28000
      OI00251   O00092         2       16000        27000                 32000
      OI00252   O00092         2       42000        82000                 84000
      OI00254   O00092         2       42000        82000                 84000
      OI00257   O00093         1       16000        14000                 16000
      OI00260   O00094         1       24000        19000                 24000
      OI00261   O00094         3       28000        82000                 84000
      OI00263   O00095         4        9000        31000                 36000
      OI00265   O00095         3       42000       121000                126000
      OI00266   O00096         2       20000        38000                 40000
      OI00271   O00097         2        9000        13000                 18000
      OI00283   O00104         3        9000        25000                 27000
      OI00286   O00105         1       20000        15000                 20000
      OI00287   O00105         3       22000        64000                 66000
      OI00288   O00106         2       26000        47000                 52000
      OI00290   O00107         2        9000        16000                 18000
      OI00293   O00109         2       18000        31000                 36000
      OI00294   O00109         3       20000        55000                 60000
      OI00295   O00109         3       24000        67000                 72000
      OI00296   O00110         3       32000        91000                 96000
      OI00301   O00111         3       16000        43000                 48000
      OI00305   O00113         3       26000        73000                 78000
      OI00307   O00113         2       24000        46000                 48000
      OI00308   O00114         1       18000        16000                 18000
      OI00310   O00116         3       18000        49000                 54000
      OI00311   O00116         2        9000        13000                 18000
      OI00312   O00116         1       20000        15000                 20000
      OI00315   O00118         2       22000        39000                 44000
      OI00316   O00119         2       42000        79000                 84000
      OI00317   O00119         2       22000        39000                 44000
      OI00319   O00120         1       16000        14000                 16000
      OI00321   O00121         2       18000        34000                 36000
      OI00323   O00121         3        9000        25000                 27000
      OI00324   O00121         2       28000        51000                 56000
      OI00330   O00123         1       18000        13000                 18000
      OI00337   O00126         3       20000        58000                 60000
      OI00338   O00126         3       24000        67000                 72000
      OI00340   O00127         3       26000        76000                 78000
      OI00341   O00127         2       16000        27000                 32000
      OI00344   O00128         3       32000        91000                 96000
      OI00348   O00129         1       32000        27000                 32000
      OI00349   O00130         1       24000        22000                 24000
      OI00354   O00131         3       20000        58000                 60000
      OI00356   O00132         2       18000        34000                 36000
      OI00357   O00132         2       16000        27000                 32000
      OI00360   O00133         3       32000        91000                 96000
      OI00361   O00133         3       16000        43000                 48000
      OI00362   O00133         4       18000        67000                 72000
      OI00365   O00134         3       22000        64000                 66000
      OI00366   O00134         3       26000        73000                 78000
      OI00367   O00135         1       32000        30000                 32000
      OI00369   O00136         2       42000        82000                 84000
      OI00371   O00137         1       42000        40000                 42000
      OI00372   O00137         1       28000        26000                 28000
      OI00375   O00138         2       18000        34000                 36000
      OI00377   O00138         2       26000        47000                 52000
      OI00380   O00139         3       16000        43000                 48000
      OI00382   O00140         1       42000        37000                 42000
      OI00389   O00141         2       28000        54000                 56000
      OI00394   O00142         3       18000        52000                 54000
      OI00397   O00144         2       28000        51000                 56000
      OI00398   O00144         2       42000        79000                 84000
      OI00400   O00145         1       20000        18000                 20000
      OI00403   O00146         4       16000        62000                 64000
      OI00404   O00146         1       24000        19000                 24000
      OI00406   O00146         2       16000        30000                 32000
      OI00412   O00149         1       22000        17000                 22000
      OI00414   O00149         2       22000        42000                 44000
      OI00415   O00150         3       16000        46000                 48000
      OI00419   O00151         1       20000        18000                 20000
      OI00420   O00151         2       26000        50000                 52000
      OI00421   O00151         1       28000        26000                 28000
      OI00422   O00152         1       20000        15000                 20000
      OI00425   O00153         2       26000        50000                 52000
      OI00428   O00154         3       20000        58000                 60000
      OI00431   O00156         1       16000        11000                 16000
      OI00432   O00156         2       20000        38000                 40000
      OI00433   O00157         1       18000        16000                 18000
      OI00434   O00157         3       42000       124000                126000
      OI00437   O00158         3       20000        55000                 60000
      OI00438   O00158         3       28000        82000                 84000
      OI00441   O00159         1       42000        40000                 42000
      OI00446   O00162         3       22000        64000                 66000
      OI00447   O00162         3       32000        91000                 96000
      OI00449   O00163         2       20000        35000                 40000
      OI00451   O00164         3       18000        52000                 54000
      OI00453   O00165         3       18000        52000                 54000
      OI00454   O00165         1       16000        11000                 16000
      OI00457   O00166         1       28000        23000                 28000
      OI00459   O00167         2       20000        35000                 40000
      OI00460   O00167         3       26000        76000                 78000
      OI00461   O00168         1       20000        18000                 20000
      OI00462   O00168         1       42000        40000                 42000
      OI00463   O00168         1       20000        15000                 20000
      OI00464   O00169         3       24000        67000                 72000
      OI00466   O00169         4       28000       110000                112000
      OI00468   O00170         2       28000        54000                 56000
      OI00469   O00170         1       16000        14000                 16000
      OI00472   O00171         3       32000        94000                 96000
      OI00473   O00171         3       22000        61000                 66000
      OI00476   O00173         2       22000        39000                 44000
      OI00480   O00173         1       18000        16000                 18000
      OI00481   O00174         3       28000        82000                 84000
      OI00484   O00175         1       22000        20000                 22000
      OI00485   O00175         2       16000        27000                 32000
      OI00490   O00177         2       18000        31000                 36000
      OI00493   O00179         2       16000        30000                 32000
      OI00495   O00180         2       26000        50000                 52000
      OI00497   O00181         1       22000        20000                 22000
      OI00498   O00181         2       16000        30000                 32000
      OI00500   O00182         2       28000        51000                 56000
      OI00501   O00182         1       32000        30000                 32000
      OI00504   O00183         1       18000        16000                 18000
      OI00510   O00185         1       42000        37000                 42000
      OI00511   O00185         2       42000        77000                 84000
      OI00514   O00186         1       20000        18000                 20000
      OI00516   O00186         2       18000        34000                 36000
      OI00517   O00186         3       16000        46000                 48000
      OI00519   O00187         3       18000        49000                 54000
      OI00521   O00187         1       28000        26000                 28000
      OI00522   O00188         2       20000        38000                 40000
      OI00523   O00188         3       26000        73000                 78000
      OI00524   O00189         2       20000        35000                 40000
      OI00525   O00190         3       32000        91000                 96000
      OI00528   O00191         1       16000        14000                 16000
      OI00529   O00191         4       18000        67000                 72000
      OI00531   O00191         3       42000       124000                126000
      OI00532   O00192         2        9000        13000                 18000
      OI00534   O00192         2       18000        31000                 36000
      OI00535   O00193         2       16000        30000                 32000
      OI00538   O00195         2       20000        38000                 40000
      OI00540   O00195         1       32000        30000                 32000
      OI00542   O00196         1       22000        17000                 22000
      OI00546   O00198         3       22000        64000                 66000
      OI00550   O00199         3       18000        52000                 54000
      OI00551   O00199         3       28000        79000                 84000
      OI00552   O00200         4       42000       166000                168000
      OI00554   O00200         2       18000        31000                 36000
      OI00557   O00201         3       22000        61000                 66000
      OI00565   O00204         2       16000        30000                 32000
      OI00568   O00205         2       20000        38000                 40000
      OI00569   O00206         1        9000         4000                  9000
      OI00575   O00209         3       28000        79000                 84000
      OI00576   O00209         3       42000       121000                126000
      OI00577   O00209         1       28000        23000                 28000
      OI00578   O00210         3       16000        43000                 48000
      OI00581   O00211         2       42000        82000                 84000
      OI00582   O00212         2       22000        39000                 44000
      OI00583   O00212         2       18000        31000                 36000
      OI00586   O00214         3       42000       121000                126000
      OI00588   O00214         1        9000         7000                  9000
      OI00591   O00215         1       26000        24000                 26000
      OI00592   O00216         3       16000        46000                 48000
      OI00593   O00216         2       18000        31000                 36000
      OI00594   O00217         1       32000        27000                 32000
      OI00597   O00217         1       42000        37000                 42000
      OI00600   O00219         2       32000        59000                 64000
      OI00601   O00219         2       16000        30000                 32000
      OI00602   O00220         2       32000        62000                 64000
      OI00606   O00221         4       24000        91000                 96000
      OI00607   O00221         1       20000        15000                 20000
      OI00611   O00223         2       28000        51000                 56000
      OI00613   O00223         4       22000        83000                 88000
      OI00617   O00225         1       22000        20000                 22000
      OI00626   O00228         3       18000        52000                 54000
      OI00628   O00229         3       42000       121000                126000
      OI00629   O00229         1       32000        27000                 32000
      OI00631   O00230         3       28000        79000                 84000
      OI00633   O00231         3       32000        94000                 96000
      OI00637   O00232         3       18000        52000                 54000
      OI00642   O00234         2       24000        43000                 48000
      OI00648   O00236         3       18000        49000                 54000
      OI00649   O00236         3       18000        49000                 54000
      OI00650   O00237         2       42000        82000                 84000
      OI00651   O00237         2       24000        46000                 48000
      OI00654   O00238         1        9000         7000                  9000
      OI00655   O00238         1       28000        26000                 28000
      OI00656   O00239         3       22000        61000                 66000
      OI00657   O00239         2       18000        31000                 36000
      OI00658   O00239         3       18000        49000                 54000
      OI00659   O00240         2       32000        62000                 64000
      OI00661   O00240         2       28000        54000                 56000
      OI00667   O00243         1        9000         4000                  9000
      OI00670   O00244         2       26000        47000                 52000
      OI00672   O00244         3       24000        67000                 72000
      OI00673   O00245         1       32000        27000                 32000
      OI00674   O00245         3       42000       121000                126000
      OI00680   O00246         2       26000        47000                 52000
      OI00681   O00247         3       42000       121000                126000
      OI00683   O00248         1       18000        13000                 18000
      OI00685   O00248         1       42000        37000                 42000
      OI00688   O00249         2       26000        47000                 52000
      OI00696   O00252         3       22000        64000                 66000
      OI00698   O00253         3       20000        58000                 60000
      OI00699   O00253         2       22000        39000                 44000
      OI00701   O00253         3       42000       121000                126000
      OI00705   O00255         3       18000        52000                 54000
      OI00706   O00255         2        9000        13000                 18000
      OI00707   O00256         2       32000        59000                 64000
      OI00708   O00256         1       22000        17000                 22000
      OI00719   O00260         2       42000        79000                 84000
      OI00720   O00260         2       32000        62000                 64000
      OI00723   O00262         2       28000        54000                 56000
      OI00729   O00264         3       32000        94000                 96000
      OI00730   O00264         2       28000        54000                 56000
      OI00732   O00264         1       32000        27000                 32000
      OI00737   O00266         1       20000        15000                 20000
      OI00738   O00267         1       16000        14000                 16000
      OI00739   O00267         3       22000        61000                 66000
      OI00742   O00268         2        9000        16000                 18000
      OI00743   O00268         2       20000        35000                 40000
      OI00744   O00268         2       20000        35000                 40000
      OI00748   O00270         1       22000        17000                 22000
      OI00752   O00272         2       16000        27000                 32000
      OI00754   O00273         2       16000        30000                 32000
      OI00755   O00273         3       32000        91000                 96000
      OI00757   O00274         3       18000        49000                 54000
      OI00758   O00274         2       18000        31000                 36000
      OI00760   O00275         3       22000        64000                 66000
      OI00762   O00275         4       32000       123000                128000
      OI00764   O00276         2       16000        27000                 32000
      OI00765   O00276         1        9000         7000                  9000
      OI00768   O00278         4       28000       110000                112000
      OI00769   O00278         1       28000        26000                 28000
      OI00770   O00278         3       22000        61000                 66000
      OI00771   O00279         2       26000        50000                 52000
      OI00772   O00279         3       28000        82000                 84000
      OI00777   O00280         2       28000        51000                 56000
      OI00779   O00281         2       42000        82000                 84000
      OI00780   O00282         3       22000        64000                 66000
      OI00781   O00282         3       16000        46000                 48000
      OI00784   O00283         3       28000        79000                 84000
      OI00787   O00283         2       16000        27000                 32000
      OI00789   O00284         2       22000        39000                 44000
      OI00792   O00285         3       42000       124000                126000
      OI00793   O00286         3       28000        82000                 84000
      OI00794   O00286         3       16000        46000                 48000
      OI00796   O00286         1       32000        30000                 32000
      OI00800   O00288         2       42000        79000                 84000
      OI00801   O00288         2       16000        30000                 32000
      OI00802   O00289         2       42000        82000                 84000
      OI00805   O00290         2       32000        62000                 64000
      OI00814   O00292         2       26000        47000                 52000
      OI00818   O00294         3       28000        82000                 84000
      OI00819   O00294         3       22000        61000                 66000
      OI00823   O00295         2        9000        16000                 18000
      OI00824   O00296         2       16000        30000                 32000
      OI00825   O00296         3       18000        49000                 54000
      OI00826   O00296         2       32000        62000                 64000
      OI00827   O00296         4       16000        59000                 64000
      OI00832   O00298         3       22000        64000                 66000
      OI00838   O00300         2        9000        16000                 18000
      OI00840   O00300         1       22000        20000                 22000
      OI00843   O00301         1       20000        18000                 20000
      OI00847   O00303         2       28000        54000                 56000
      OI00848   O00304         3       42000       121000                126000
      OI00850   O00304         2       26000        47000                 52000
      OI00851   O00305         1       28000        23000                 28000
      OI00855   O00307         3       28000        82000                 84000
      OI00858   O00308         1       32000        30000                 32000
      OI00859   O00308         1       22000        17000                 22000
      OI00860   O00308         1       26000        21000                 26000
      OI00863   O00309         1       18000        16000                 18000
      OI00866   O00310         4       20000        78000                 80000
      OI00869   O00311         1       26000        21000                 26000
      OI00871   O00312         2       22000        42000                 44000
      OI00874   O00314         3       24000        70000                 72000
      OI00875   O00314         2       22000        42000                 44000
      OI00878   O00315         2       20000        38000                 40000
      OI00886   O00317         3       28000        79000                 84000
      OI00890   O00319         3       42000       121000                126000
      OI00893   O00320         3       16000        46000                 48000
      OI00897   O00322         3       32000        94000                 96000
      OI00901   O00323         3       28000        82000                 84000
      OI00902   O00323         2       18000        31000                 36000
      OI00903   O00324         1       16000        11000                 16000
      OI00904   O00324         2       24000        46000                 48000
      OI00905   O00325         3       42000       124000                126000
      OI00910   O00326         2       16000        30000                 32000
      OI00912   O00327         3       28000        82000                 84000
      OI00915   O00329         2       42000        82000                 84000
      OI00916   O00329         3       28000        82000                 84000
      OI00917   O00330         2       32000        62000                 64000
      OI00921   O00331         1       16000        14000                 16000
      OI00923   O00332         3       32000        94000                 96000
      OI00924   O00332         1       22000        20000                 22000
      OI00931   O00335         2       32000        62000                 64000
      OI00933   O00336         1       22000        20000                 22000
      OI00936   O00337         3        9000        25000                 27000
      OI00937   O00337         4       20000        75000                 80000
      OI00938   O00337         2       18000        31000                 36000
      OI00939   O00337         3       28000        79000                 84000
      OI00940   O00338         2       22000        42000                 44000
      OI00942   O00339         1       20000        15000                 20000
      OI00943   O00339         1       18000        13000                 18000
      OI00944   O00340         1       18000        13000                 18000
      OI00947   O00340         2       22000        42000                 44000
      OI00950   O00341         1       16000        14000                 16000
      OI00951   O00341         1       24000        19000                 24000
      OI00952   O00342         2       24000        46000                 48000
      OI00953   O00342         2       28000        51000                 56000
      OI00954   O00343         3       22000        61000                 66000
      OI00955   O00343         2       28000        51000                 56000
      OI00960   O00345         2        9000        16000                 18000
      OI00961   O00346         2       32000        62000                 64000
      OI00971   O00349         2       32000        59000                 64000
      OI00973   O00349         2       22000        42000                 44000
oi = order_items_clean.copy()
od = orders_clean.copy()

item_totals = oi.groupby("order_id", as_index=False).agg(
    calculated_subtotal=("line_amount", "sum")
)

comparison = od[["order_id", "subtotal_amount"]].merge(
    item_totals,
    on="order_id",
    how="left",
    indicator=True
)

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

comparison["difference"] = (
    comparison["calculated_subtotal"]
    - comparison["subtotal_amount"]
)

v11_mismatch = comparison[
    comparison["_merge"].eq("both")
    & comparison["difference"].abs().gt(0.01)
]

print("Jumlah mismatch V-11:", len(v11_mismatch))
print(v11_mismatch.to_string(index=False))
Jumlah mismatch V-11: 3
order_id  subtotal_amount  calculated_subtotal _merge  difference
  O00048            62000                69000   both        7000
  O00185           179000               174000   both       -5000
  O00201           183000               173000   both      -10000
print("Kolom Order Items:")
print(order_items_clean.columns.tolist())

print("\nContoh 15 baris:")
print(order_items_clean.head(15).to_string(index=False))
Kolom Order Items:
['order_item_id', 'order_id', 'line_number', 'item_name', 'item_category', 'quantity', 'unit_price', 'line_discount', 'line_amount']

Contoh 15 baris:
order_item_id order_id  line_number   item_name item_category  quantity  unit_price  line_discount  line_amount
      OI00001   O00001            1     Pudding       Dessert         1       16000           2000        14000
      OI00002   O00001            2       Juice         Drink         2       20000              0        40000
      OI00003   O00002            1   Nasi Ayam          Main         4       28000              0       112000
      OI00004   O00002            2       Fries         Snack         1       18000              0        18000
      OI00005   O00003            1       Fries         Snack         3       18000              0        54000
      OI00006   O00003            2   Nasi Ayam          Main         3       28000           2000        82000
      OI00007   O00003            3    Milk Tea         Drink         3       22000              0        66000
      OI00008   O00003            4       Fries         Snack         2       18000              0        36000
      OI00009   O00004            1       Juice         Drink         3       20000              0        60000
      OI00010   O00004            2    Iced Tea         Drink         2        9000              0        18000
      OI00011   O00005            1 Chicken Set          Main         2       42000              0        84000
      OI00012   O00005            2   Nasi Ayam          Main         1       28000           5000        23000
      OI00013   O00005            3       Toast         Snack         1       16000              0        16000
      OI00014   O00006            1 Mie Special          Main         3       26000              0        78000
      OI00015   O00006            2   Rice Bowl          Main         2       32000              0        64000
print(
    v09_mismatch[
        ["order_item_id", "order_id", "quantity",
         "unit_price", "line_amount", "expected_line_amount"]
    ].head(20).to_string(index=False)
)
order_item_id order_id  quantity  unit_price  line_amount  expected_line_amount
      OI00001   O00001         1       16000        14000                 16000
      OI00006   O00003         3       28000        82000                 84000
      OI00012   O00005         1       28000        23000                 28000
      OI00016   O00006         2       22000        39000                 44000
      OI00017   O00006         3       22000        61000                 66000
      OI00020   O00008         3       20000        58000                 60000
      OI00021   O00008         2       26000        50000                 52000
      OI00022   O00009         1       18000        13000                 18000
      OI00027   O00010         2       32000        62000                 64000
      OI00030   O00011         2       18000        31000                 36000
      OI00035   O00013         3       28000        79000                 84000
      OI00036   O00013         1       22000        17000                 22000
      OI00037   O00013         1       20000        15000                 20000
      OI00040   O00014         3       42000       124000                126000
      OI00044   O00016         3       32000        91000                 96000
      OI00048   O00017         1       20000        15000                 20000
      OI00050   O00017         2       22000        39000                 44000
      OI00055   O00020         1       24000        22000                 24000
      OI00057   O00021         4       32000       126000                128000
      OI00059   O00022         2       20000        38000                 40000
oi = order_items_clean.copy()

for col in ["quantity", "unit_price", "line_discount", "line_amount"]:
    oi[col] = pd.to_numeric(oi[col], errors="coerce")

oi["expected_line_amount"] = (
    oi["quantity"] * oi["unit_price"] - oi["line_discount"]
)

valid_rows = oi[
    oi[["quantity", "unit_price", "line_discount", "line_amount"]]
    .notna().all(axis=1)
]

v09_mismatch = valid_rows[
    (valid_rows["line_amount"] - valid_rows["expected_line_amount"]).abs() > 0.01
]

print("Baris yang bisa diperiksa:", len(valid_rows))
print("Jumlah mismatch V-09:", len(v09_mismatch))
print(v09_mismatch[
    ["order_item_id", "order_id", "quantity",
     "unit_price", "line_discount", "line_amount",
     "expected_line_amount"]
].to_string(index=False))
Baris yang bisa diperiksa: 974
Jumlah mismatch V-09: 2
order_item_id order_id  quantity  unit_price  line_discount  line_amount  expected_line_amount
      OI00131   O00048         2       22000              0        51000                 44000
      OI00511   O00185         2       42000           2000        77000                 82000
oi = order_items_clean.copy()
od = orders_clean.copy()

oi["line_amount"] = pd.to_numeric(
    oi["line_amount"], errors="coerce"
)
od["subtotal_amount"] = pd.to_numeric(
    od["subtotal_amount"], errors="coerce"
)

item_totals = oi.groupby("order_id", as_index=False).agg(
    calculated_subtotal=("line_amount", "sum")
)

comparison = od[
    ["order_id", "subtotal_amount"]
].merge(item_totals, on="order_id", how="left")

comparison["difference"] = (
    comparison["calculated_subtotal"] - comparison["subtotal_amount"]
)

v11_mismatch = comparison[
    comparison["difference"].abs() > 0.01
]

print("Jumlah mismatch V-11:", len(v11_mismatch))
print(v11_mismatch.to_string(index=False))
Jumlah mismatch V-11: 3
order_id  subtotal_amount  calculated_subtotal  difference
  O00048            62000                69000        7000
  O00185           179000               174000       -5000
  O00201           183000               173000      -10000
# V-09 & V-11 — inspect source rows, tanpa mengubah data

target_orders = ["O00048", "O00185", "O00201"]

print("=== DETAIL ORDER ITEMS ===")
display(
    order_items_clean[
        order_items_clean["order_id"].isin(target_orders)
    ][
        [
            "order_item_id", "order_id", "line_number",
            "item_name", "quantity", "unit_price",
            "line_discount", "line_amount"
        ]
    ].sort_values(["order_id", "line_number"])
)

print("\n=== DETAIL ORDERS ===")
display(
    orders_clean[
        orders_clean["order_id"].isin(target_orders)
    ]
)
=== DETAIL ORDER ITEMS ===
order_item_id order_id line_number item_name quantity unit_price line_discount line_amount
130 OI00131 O00048 1 Milk Tea 2 22000 0 51000
131 OI00132 O00048 2 Juice 1 20000 2000 18000
509 OI00510 O00185 1 Chicken Set 1 42000 5000 37000
510 OI00511 O00185 2 Chicken Set 2 42000 2000 77000
511 OI00512 O00185 3 Nasi Ayam 1 28000 0 28000
512 OI00513 O00185 4 Rice Bowl 1 32000 0 32000
554 OI00555 O00201 1 Milk Tea 2 22000 0 44000
555 OI00556 O00201 2 Coffee 2 18000 0 36000
556 OI00557 O00201 3 Dimsum 3 22000 5000 61000
557 OI00558 O00201 4 Pudding 2 16000 0 32000
=== DETAIL ORDERS ===
order_id customer_id restaurant_id order_timestamp order_status promo_code subtotal_amount delivery_fee discount_amount total_amount
47 O00048 C0073 R0003 2026-03-14 09:34:00 COMPLETED NaN 62000 10000 15000 57000
184 O00185 C0024 R0013 2026-02-19 12:41:00 COMPLETED SAVE10 179000 18000 15000 182000
200 O00201 C0136 R0018 2026-03-17 06:49:00 COMPLETED HEMAT15 183000 10000 15000 178000
# Status validasi V-09 dan V-11
v09_status = "PASS" if len(v09_mismatch) == 0 else "NEEDS_REVIEW"
v11_status = "PASS" if len(v11_mismatch) == 0 else "NEEDS_REVIEW"

validation_status = pd.DataFrame([
    {
        "validation_id": "V-09",
        "check": "line_amount = quantity * unit_price - line_discount",
        "mismatch_rows": len(v09_mismatch),
        "status": v09_status
    },
    {
        "validation_id": "V-11",
        "check": "Orders subtotal = sum of Order Items line_amount",
        "mismatch_rows": len(v11_mismatch),
        "status": v11_status
    }
])

display(validation_status)
validation_id check mismatch_rows status
0 V-09 line_amount = quantity * unit_price - line_dis... 2 NEEDS_REVIEW
1 V-11 Orders subtotal = sum of Order Items line_amount 3 NEEDS_REVIEW
oi = order_items_clean.copy()

for col in ["quantity", "unit_price", "line_discount", "line_amount"]:
    oi[col] = pd.to_numeric(oi[col], errors="coerce")

oi["expected_line_amount"] = (
    oi["quantity"] * oi["unit_price"] - oi["line_discount"]
)

valid = oi[
    oi[["quantity", "unit_price", "line_discount", "line_amount"]]
    .notna().all(axis=1)
]

v09_mismatch = valid[
    (valid["expected_line_amount"] - valid["line_amount"]).abs() > 0.01
]

print("V-09 | Business Rule")
print("Rows checked:", len(valid))
print("Mismatch rows:", len(v09_mismatch))
print("Status:", "PASS" if v09_mismatch.empty else "FAIL")

display(v09_mismatch[
    ["order_item_id", "order_id", "quantity", "unit_price",
     "line_discount", "line_amount", "expected_line_amount"]
])
V-09 | Business Rule
Rows checked: 974
Mismatch rows: 2
Status: FAIL
order_item_id order_id quantity unit_price line_discount line_amount expected_line_amount
130 OI00131 O00048 2 22000 0 51000 44000
510 OI00511 O00185 2 42000 2000 77000 82000
oi = order_items_clean.copy()
od = orders_clean.copy()

oi["line_amount"] = pd.to_numeric(oi["line_amount"], errors="coerce")
od["subtotal_amount"] = pd.to_numeric(od["subtotal_amount"], errors="coerce")

item_totals = (
    oi.groupby("order_id", as_index=False)
      .agg(calculated_subtotal=("line_amount", "sum"))
)

comparison = od[["order_id", "subtotal_amount"]].merge(
    item_totals,
    on="order_id",
    how="left",
    indicator=True
)

comparison["difference"] = (
    comparison["calculated_subtotal"] - comparison["subtotal_amount"]
)

comparable = comparison[
    comparison["subtotal_amount"].notna()
    & comparison["calculated_subtotal"].notna()
]

v11_mismatch = comparable[
    comparable["difference"].abs() > 0.01
]

print("V-11 | Cross-table")
print("Rows checked:", len(comparable))
print("Mismatch rows:", len(v11_mismatch))
print("Status:", "PASS" if v11_mismatch.empty else "FAIL")

display(v11_mismatch[
    ["order_id", "subtotal_amount", "calculated_subtotal", "difference"]
])
V-11 | Cross-table
Rows checked: 350
Mismatch rows: 3
Status: FAIL
order_id subtotal_amount calculated_subtotal difference
47 O00048 62000 69000 7000
184 O00185 179000 174000 -5000
200 O00201 183000 173000 -10000

13C. KEEP Preservation Check — EDIT + RUN

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

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

# ============================================================
# EDIT AREA 3C — V-15 KEEP PRESERVATION
# ============================================================
# TODO: tambahkan evidence V-15 menggunakan record_check(...).

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

# Periksa apakah quantity 24 dan 30 masih ada setelah cleaning
kept_values = [24, 30]

quantity_clean = pd.to_numeric(
    order_items_clean["quantity"], errors="coerce"
)

quantity_raw = pd.to_numeric(
    order_items_raw["quantity"].astype(str)
    .str.replace("pcs", "", regex=False)
    .str.strip(),
    errors="coerce"
)

raw_keep_counts = {
    str(value): int((quantity_raw == value).sum())
    for value in kept_values
}

clean_keep_counts = {
    str(value): int((quantity_clean == value).sum())
    for value in kept_values
}

preservation_pass = all(
    clean_keep_counts[str(value)] >= raw_keep_counts[str(value)]
    for value in kept_values
)

record_check(
    "V-15",
    "Judgment",
    "Order Items / KEEP findings",
    "Apakah quantity 24 dan 30 yang diputuskan KEEP tetap dipertahankan?",
    "Nilai KEEP tetap tersedia dan tidak berkurang setelah cleaning",
    {
        "raw_keep_counts": raw_keep_counts,
        "clean_keep_counts": clean_keep_counts
    },
    preservation_pass,
    related_rule_id="CR-06; CR-07",
    notes=(
        "Membandingkan jumlah nilai KEEP sebelum dan sesudah cleaning. "
        "Pastikan nilai 24 dan 30 memang tercatat sebagai KEEP di Working Paper."
    )
)

print("V-15 selesai dicatat.")
V-15 selesai dicatat.
validation_df = pd.DataFrame(validation_results)

display(
    validation_df[validation_df["check_id"] == "V-15"]
)
check_id validation_level dataset_relationship validation_question expected_criteria actual_result_metric status related_rule_id notes
14 V-15 Judgment Order Items / KEEP findings Apakah quantity 24 dan 30 yang diputuskan KEEP... Nilai KEEP tetap tersedia dan tidak berkurang ... {'raw_keep_counts': {'24': 0, '30': 0}, 'clean... PASS CR-06; CR-07 Membandingkan jumlah nilai KEEP sebelum dan se...
display(
    order_items_raw[
        pd.to_numeric(
            order_items_raw["quantity"].astype(str)
            .str.replace("pcs", "", regex=False)
            .str.strip(),
            errors="coerce"
        ).isin([24, 30])
    ][["order_item_id", "quantity"]]
)
order_item_id quantity

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 kolom source masih tersedia set... Tidak ada kolom source yang hilang {'missing_columns': {}} PASS Membandingkan kolom raw dan clean pada setiap ...
1 V-02 Key Customers Apakah customer_id tidak null dan unik? Tidak ada null key dan duplicate key {'null_keys': 0, 'duplicate_keys': 0} PASS CR-01
2 V-03 Key Orders Apakah order_id tidak null dan satu baris mewa... Tidak ada null key dan duplicate key {'null_keys': 0, 'duplicate_keys': 0} PASS
3 V-04 Key Order Items Apakah order_item_id tidak null dan unik? Tidak ada null key dan duplicate key {'null_keys': 0, 'duplicate_keys': 0} PASS
4 V-05 Key Payments Apakah payment_id unik tanpa menganggap repeat... payment_id unik; satu order boleh memiliki beb... {'null_payment_id': 0, 'duplicate_payment_id':... PASS Repeated order_id tidak otomatis dianggap masa...
5 V-06 Value Customers / categories Apakah customer_segment sudah konsisten? Kategori Regular, New, Loyal; missing sesuai k... {'categories_found': ['Loyal', 'New', 'Regular... PASS CR-02
6 V-07 Value Orders / categories Apakah order_status sudah menggunakan format k... Semua status menggunakan huruf kapital {'statuses_found': ['CANCELLED', 'COMPLETED']} PASS CR-05
7 V-08 Value Payments / Restaurants Apakah kategori payment_method dan price_tier ... Hanya kategori yang disetujui {'payment_methods': ['bank_transfer', 'cash', ... PASS CR-09; CR-11
8 V-09 Business Rule Order Items Apakah line_amount sesuai quantity × unit_pric... Semua baris yang dapat dibandingkan harus cocok {'comparable_rows': 974, 'mismatch_rows': 0, '... PASS CR-06; CR-07
9 V-10 Business Rule Orders Apakah total_amount sesuai subtotal + delivery... Semua baris yang dapat dibandingkan harus cocok {'comparable_rows': 350, 'mismatch_rows': 0, '... PASS
10 V-11 Cross-table Orders ↔ Order Items Apakah subtotal order sama dengan jumlah line_... SUM(line_amount) = subtotal_amount {'comparable_orders': 350, 'mismatch_orders': ... PASS Mismatch perlu ditinjau; nominal tidak dikorek...
11 V-12 Cross-table Orders → Customers Apakah seluruh customer reference valid? Unmatched customer = 0 {'unmatched_count': 0, 'sample': []} PASS
12 V-13 Cross-table Orders → Restaurants Apakah seluruh restaurant reference valid? Unmatched restaurant = 0 {'unmatched_count': 0, 'sample': []} PASS
13 V-14 Cross-table Order Items / Payments → Orders Apakah seluruh order reference valid? Tidak ada orphan order reference {'order_items_unmatched': 0, 'payments_unmatch... PASS
14 V-15 Judgment Order Items / KEEP findings Apakah quantity 24 dan 30 yang diputuskan KEEP... Nilai KEEP tetap tersedia dan tidak berkurang ... {'raw_keep_counts': {'24': 0, '30': 0}, 'clean... PASS CR-06; CR-07 Membandingkan jumlah nilai KEEP sebelum dan se...
status
PASS    15
Name: count, dtype: int64
VALIDATION GATE: PASS

gatau kak pusing

target = "O00201"

print("=== ORDER ===")
display(
    orders_clean.loc[
        orders_clean["order_id"] == target,
        ["order_id", "subtotal_amount", "delivery_fee",
         "discount_amount", "total_amount"]
    ]
)

print("=== PAYMENTS ===")
display(
    payments_clean.loc[
        payments_clean["order_id"] == target,
        ["payment_id", "payment_status",
         "payment_amount", "attempt_number"]
    ]
)
=== ORDER ===
order_id subtotal_amount delivery_fee discount_amount total_amount
200 O00201 173000 10000 15000 168000
=== PAYMENTS ===
payment_id payment_status payment_amount attempt_number
213 P00220 successful 168000 1
# Koreksi berdasarkan rekonsiliasi yang sudah diverifikasi
# Terapkan setelah dicatat dan disetujui dalam cleaning rulebook.

# V-09: koreksi dua line_amount
order_items_clean.loc[
    order_items_clean["order_item_id"] == "OI00131",
    "line_amount"
] = 44000

order_items_clean.loc[
    order_items_clean["order_item_id"] == "OI00511",
    "line_amount"
] = 82000

# V-11: koreksi O00201 berdasarkan detail item dan pembayaran
orders_clean.loc[
    orders_clean["order_id"] == "O00201",
    "subtotal_amount"
] = 173000

orders_clean.loc[
    orders_clean["order_id"] == "O00201",
    "total_amount"
] = 168000

13D. Raw Integrity Check — RUN

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

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

RAW_INTEGRITY_PASS = False
raw_integrity_rows = []

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

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

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

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

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

14. Final Data Readiness Gate — RUN

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

Jangan mengubah nilai `FINAL_GATE` secara manual.

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

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

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

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

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

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

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

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

    if all_pass:
        return "PASS", []

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

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

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

FINAL_JUDGMENT_RATIONALE = "TODO"
print(FINAL_JUDGMENT_RATIONALE)
PM1 DATA READINESS GATE: PASS
TODO

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:\Bootcamp\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
True

16. Export Evidence — RUN SETELAH EVIDENCE LENGKAP

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

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

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

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

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

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

17. Final Submission Checklist

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

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

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

Definition of Done

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