# 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.**
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
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
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
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.
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
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 | 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 |
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,... |
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 |
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 |
Profiling minimal:
- missing / placeholder,
- data type / format,
- category consistency,
- duplicate / key,
- suspicious numeric / formula,
- potential outlier,
- cross-table concern.
Helper berikut menghasilkan evidence awal, bukan automatic judgment.
def basic_profile(df, dataset_name):
return pd.DataFrame([{
"dataset": dataset_name,
"rows": len(df),
"columns": len(df.columns),
"rows_in_exact_duplicate_groups": int(df.duplicated(keep=False).sum()),
"exact_duplicate_extras": int(df.duplicated(keep="first").sum()),
"null_cells": int(df.isna().sum().sum()),
}])
def column_profile(df):
rows = []
for col in df.columns:
s = df[col]
rows.append({
"column": col,
"dtype": str(s.dtype),
"null_count": int(s.isna().sum()),
"null_pct": round(float(s.isna().mean()), 4),
"unique_count": int(s.nunique(dropna=True)),
"sample_values": list(s.dropna().astype(str).unique()[:5]),
})
return pd.DataFrame(rows)
if DATA_READY:
overall_profile = pd.concat(
[basic_profile(df, name) for name, df in raw_tables.items()],
ignore_index=True
)
display(overall_profile)
| dataset | rows | columns | rows_in_exact_duplicate_groups | exact_duplicate_extras | null_cells | |
|---|---|---|---|---|---|---|
| 0 | Customers | 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 |
customers = raw_tables["Customers"]
duplicate_customers = customers[
customers["customer_id"].duplicated(keep=False)
].sort_values("customer_id")
display(duplicate_customers)
| customer_id | full_name | 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
# 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 | 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
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
# 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
# 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
# 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
# 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
def category_review(df, column, top_n=30):
out = (
df[column]
.value_counts(dropna=False)
.rename_axis(column)
.reset_index(name="count")
)
out["pct"] = (out["count"] / len(df)).round(4)
return out.head(top_n)
# Contoh:
# display(category_review(customers_raw, "customer_segment"))
# display(category_review(orders_raw, "order_status"))
# display(category_review(payments_raw, "payment_method"))
# display(category_review(restaurants_raw, "price_tier"))
display(category_review(customers_raw, "customer_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
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
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
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
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
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))
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
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
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 |
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
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 |
|---|
```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 |
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 | [] |
### 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 |
|---|
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 |
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)
```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 |
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.
**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_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... |
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
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... |
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 |
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_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
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
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
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
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
### 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.
```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
```