# 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 = "Nurnia"
VARIANT_ID = "SB-P06" # contoh: SB-P01
name_ok = PARTICIPANT_NAME.strip() not in {"", "Isi Nama Kamu"}
variant_ok = (
VARIANT_ID.startswith("SB-P")
and len(VARIANT_ID) == 6
and VARIANT_ID[4:].isdigit()
)
IDENTITY_GATE_PASS = bool(name_ok and variant_ok)
if IDENTITY_GATE_PASS:
print("IDENTITY GATE: PASS")
print("Participant :", PARTICIPANT_NAME)
print("Variant ID :", VARIANT_ID)
else:
print("IDENTITY GATE: FAIL")
print("→ Isi PARTICIPANT_NAME dan VARIANT_ID pada cell ini.")
print("→ Format Variant ID harus seperti SB-P01 atau SB-P28.")
IDENTITY GATE: PASS Participant : Nurnia Variant ID : SB-P06
Notebook akan mencari **Workspace Starter yang sama dari Sesi 1** berdasarkan keberadaan folder `data/` dan `notebooks/`.
**Jangan membuat workspace baru. Jangan copy-paste absolute path.**
Jika output menunjukkan `TECHNICAL GATE: PASS`, lanjut ke cell berikutnya tanpa mengubah path.
from pathlib import Path
import json
import hashlib
import pandas as pd
import numpy as np
try:
import duckdb
DUCKDB_AVAILABLE = True
except ImportError:
duckdb = None
DUCKDB_AVAILABLE = False
pd.set_option("display.max_columns", 100)
pd.set_option("display.max_rows", 100)
pd.set_option("display.width", 180)
def find_workspace_root(start=None):
start = Path(start or Path.cwd()).resolve()
for p in [start, *start.parents]:
if (p / "data").is_dir() and (p / "notebooks").is_dir():
return p
return None
WORKSPACE_ROOT = find_workspace_root()
if WORKSPACE_ROOT is None:
TECHNICAL_GATE_PASS = False
RAW_DIR = Path("__WORKSPACE_NOT_FOUND__")
PROCESSED_DIR = Path("__WORKSPACE_NOT_FOUND__")
OUTPUT_DIR = Path("__WORKSPACE_NOT_FOUND__")
DOCS_DIR = Path("__WORKSPACE_NOT_FOUND__")
print("TECHNICAL GATE: FAIL")
print("→ Workspace Starter tidak ditemukan.")
print("→ Di Antigravity, buka folder Bootcamp_Data_Analyst sebagai workspace.")
print("→ Pastikan folder data/ dan notebooks/ terlihat di Explorer.")
print("→ Setelah itu RUN ulang cell ini. Jangan edit path secara manual.")
else:
TECHNICAL_GATE_PASS = True
RAW_DIR = WORKSPACE_ROOT / "data" / "raw" / "pm1_swiftbite"
PROCESSED_DIR = WORKSPACE_ROOT / "data" / "processed" / "pm1_swiftbite"
OUTPUT_DIR = WORKSPACE_ROOT / "outputs" / "pm1_swiftbite"
DOCS_DIR = WORKSPACE_ROOT / "docs" / "pm1_swiftbite"
for folder in [PROCESSED_DIR, OUTPUT_DIR, DOCS_DIR]:
folder.mkdir(parents=True, exist_ok=True)
print("TECHNICAL GATE: PASS")
print("WORKSPACE_ROOT :", WORKSPACE_ROOT)
print("RAW_DIR :", RAW_DIR)
print("PROCESSED_DIR :", PROCESSED_DIR)
print("OUTPUT_DIR :", OUTPUT_DIR)
print("DOCS_DIR :", DOCS_DIR)
TECHNICAL GATE: PASS WORKSPACE_ROOT : C:\Users\User\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst RAW_DIR : C:\Users\User\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite PROCESSED_DIR : C:\Users\User\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite OUTPUT_DIR : C:\Users\User\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\outputs\pm1_swiftbite DOCS_DIR : C:\Users\User\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\docs\pm1_swiftbite
Tujuan cell ini hanya memastikan library utama tersedia. Jika DuckDB belum aktif, workflow utama starter tetap dapat berjalan dengan pandas.
print("pandas :", pd.__version__)
try:
import openpyxl
EXCEL_ENGINE_AVAILABLE = True
print("openpyxl:", openpyxl.__version__)
except ImportError:
EXCEL_ENGINE_AVAILABLE = False
print("openpyxl: NOT AVAILABLE")
if DUCKDB_AVAILABLE:
print("DuckDB :", duckdb.__version__)
try:
con = duckdb.connect()
print("DuckDB connection: OK")
except Exception as e:
print("DuckDB connection: WARNING —", type(e).__name__, str(e))
else:
print("DuckDB : NOT AVAILABLE")
print("INFO: workflow utama notebook tetap dapat dijalankan dengan pandas.")
if not EXCEL_ENGINE_AVAILABLE:
print("ENVIRONMENT CHECK: NEEDS FIX")
print("→ File XLSX membutuhkan openpyxl. Aktifkan environment sesuai setup Sesi 1.")
else:
print("ENVIRONMENT CHECK: PASS")
pandas : 2.2.3
openpyxl: 3.1.5 DuckDB : 1.5.6 DuckDB connection: OK ENVIRONMENT CHECK: PASS
Letakkan **5 file berikut langsung** di:
`data/raw/pm1_swiftbite/`
- `swiftbite_customers.json`
- `swiftbite_orders.xlsx`
- `swiftbite_order_items.csv`
- `swiftbite_payments.txt`
- `swiftbite_restaurants.csv`
**Jangan** meletakkan ZIP di folder tersebut dan **jangan** membuat folder `SB-Pxx` lagi di dalam `pm1_swiftbite/`.
EXPECTED_FILES = {
"customers": "swiftbite_customers.json",
"orders": "swiftbite_orders.xlsx",
"order_items": "swiftbite_order_items.csv",
"payments": "swiftbite_payments.txt",
"restaurants": "swiftbite_restaurants.csv",
}
source_status = []
if not TECHNICAL_GATE_PASS:
missing_files = list(EXPECTED_FILES.values())
SOURCE_ACCESS_PASS = False
print("SOURCE ACCESS CHECK: SKIPPED")
print("→ Perbaiki TECHNICAL GATE terlebih dahulu.")
else:
for logical_name, filename in EXPECTED_FILES.items():
path = RAW_DIR / filename
source_status.append({
"logical_name": logical_name,
"filename": filename,
"exists": path.is_file(),
"path": str(path),
})
source_status_df = pd.DataFrame(source_status)
display(source_status_df)
missing_files = source_status_df.loc[
~source_status_df["exists"], "filename"
].tolist()
SOURCE_ACCESS_PASS = len(missing_files) == 0
if SOURCE_ACCESS_PASS:
print("SOURCE ACCESS CHECK: PASS")
print("→ Semua 5 raw files ditemukan. Jangan ubah path.")
else:
print("SOURCE ACCESS CHECK: FAIL")
print("File belum ditemukan:", missing_files)
print("→ Extract package peserta.")
print("→ Copy hanya 5 raw files langsung ke:", RAW_DIR)
print("→ Jangan menaruh file di subfolder tambahan.")
| logical_name | filename | exists | path | |
|---|---|---|---|---|
| 0 | customers | swiftbite_customers.json | True | C:\Users\User\Downloads\Bootcamp_Data_Analyst_... |
| 1 | orders | swiftbite_orders.xlsx | True | C:\Users\User\Downloads\Bootcamp_Data_Analyst_... |
| 2 | order_items | swiftbite_order_items.csv | True | C:\Users\User\Downloads\Bootcamp_Data_Analyst_... |
| 3 | payments | swiftbite_payments.txt | True | C:\Users\User\Downloads\Bootcamp_Data_Analyst_... |
| 4 | restaurants | swiftbite_restaurants.csv | True | C:\Users\User\Downloads\Bootcamp_Data_Analyst_... |
SOURCE ACCESS CHECK: PASS → Semua 5 raw files ditemukan. Jangan ubah path.
Cell ini:
1. membaca kelima source secara aman,
2. menampilkan error per file jika ada,
3. memeriksa kolom minimum yang diperlukan Starter,
4. menyimpan **raw hash baseline** untuk pemeriksaan integritas di akhir.
Jika `RAW LOAD GATE: PASS` dan `SCHEMA GATE: PASS`, lanjutkan.
REQUIRED_COLUMNS = {
"Customers": {
"customer_id", "full_name", "email", "phone_number",
"signup_date", "customer_zone", "customer_segment"
},
"Orders": {
"order_id", "customer_id", "restaurant_id", "order_timestamp",
"order_status", "subtotal_amount", "delivery_fee",
"discount_amount", "total_amount"
},
"Order Items": {
"order_item_id", "order_id", "line_number", "item_name",
"item_category", "quantity", "unit_price",
"line_discount", "line_amount"
},
"Payments": {
"payment_id", "order_id", "payment_timestamp",
"payment_method", "payment_status", "payment_amount"
},
"Restaurants": {
"restaurant_id", "restaurant_name", "cuisine_type",
"restaurant_zone", "price_tier", "active_status"
},
}
def sha256_file(path: Path):
h = hashlib.sha256()
with open(path, "rb") as f:
for chunk in iter(lambda: f.read(1024 * 1024), b""):
h.update(chunk)
return h.hexdigest()
def safe_read_source(label, path, reader):
try:
return reader(path), None
except Exception as e:
return None, f"{type(e).__name__}: {e}"
raw_tables = {}
load_errors = {}
RAW_HASH_BASELINE = {}
if not SOURCE_ACCESS_PASS:
RAW_LOAD_GATE_PASS = False
SCHEMA_GATE_PASS = False
DATA_READY = False
print("RAW LOAD GATE: SKIPPED")
print("→ Perbaiki SOURCE ACCESS CHECK terlebih dahulu.")
else:
readers = {
"Customers": lambda p: pd.read_json(p),
"Orders": lambda p: pd.read_excel(p),
"Order Items": lambda p: pd.read_csv(p),
"Payments": lambda p: pd.read_csv(p, sep="|"),
"Restaurants": lambda p: pd.read_csv(p),
}
file_map = {
"Customers": RAW_DIR / EXPECTED_FILES["customers"],
"Orders": RAW_DIR / EXPECTED_FILES["orders"],
"Order Items": RAW_DIR / EXPECTED_FILES["order_items"],
"Payments": RAW_DIR / EXPECTED_FILES["payments"],
"Restaurants": RAW_DIR / EXPECTED_FILES["restaurants"],
}
for label, path in file_map.items():
df, err = safe_read_source(label, path, readers[label])
if err:
load_errors[label] = err
else:
raw_tables[label] = df
RAW_HASH_BASELINE[label] = sha256_file(path)
RAW_LOAD_GATE_PASS = len(load_errors) == 0 and len(raw_tables) == 5
if RAW_LOAD_GATE_PASS:
customers_raw = raw_tables["Customers"]
orders_raw = raw_tables["Orders"]
order_items_raw = raw_tables["Order Items"]
payments_raw = raw_tables["Payments"]
restaurants_raw = raw_tables["Restaurants"]
print("RAW LOAD GATE: PASS")
else:
print("RAW LOAD GATE: FAIL")
for label, err in load_errors.items():
print(f"→ {label}: {err}")
schema_rows = []
if RAW_LOAD_GATE_PASS:
for label, required in REQUIRED_COLUMNS.items():
actual = set(raw_tables[label].columns)
missing_cols = sorted(required - actual)
schema_rows.append({
"dataset": label,
"required_columns": len(required),
"actual_columns": len(actual),
"missing_required_columns": ", ".join(missing_cols) if missing_cols else "",
"status": "PASS" if not missing_cols else "FAIL",
})
schema_check_df = pd.DataFrame(schema_rows)
display(schema_check_df)
SCHEMA_GATE_PASS = (schema_check_df["status"] == "PASS").all()
if SCHEMA_GATE_PASS:
print("SCHEMA GATE: PASS")
else:
print("SCHEMA GATE: FAIL")
print("→ Jangan lanjut cleaning. Periksa apakah package/variant yang digunakan sudah benar.")
else:
SCHEMA_GATE_PASS = False
DATA_READY = bool(
TECHNICAL_GATE_PASS
and SOURCE_ACCESS_PASS
and RAW_LOAD_GATE_PASS
and SCHEMA_GATE_PASS
)
if DATA_READY:
print("DATA READY GATE: PASS")
else:
print("DATA READY GATE: FAIL")
RAW LOAD GATE: PASS
| dataset | required_columns | actual_columns | missing_required_columns | status | |
|---|---|---|---|---|---|
| 0 | Customers | 7 | 7 | PASS | |
| 1 | Orders | 9 | 10 | PASS | |
| 2 | Order Items | 9 | 9 | PASS | |
| 3 | Payments | 6 | 7 | PASS | |
| 4 | Restaurants | 6 | 7 | PASS |
SCHEMA GATE: PASS DATA READY GATE: PASS
Pastikan nama kolom dan contoh record masuk akal. Ini **belum** berarti data sudah bersih.
if DATA_READY:
for name, df in raw_tables.items():
print(f"\n=== {name} | shape={df.shape} ===")
display(df.head(3))
else:
print("PREVIEW SKIPPED — DATA READY GATE belum PASS.")
=== Customers | shape=(159, 7) ===
| customer_id | full_name | phone_number | signup_date | customer_zone | customer_segment | ||
|---|---|---|---|---|---|---|---|
| 0 | C0001 | Wahyu Permata | wahyupermata1@example.com | 084510209970 | 2025-10-30 | East | Regular |
| 1 | C0002 | Ayu Nugroho | ayunugroho2@example.com | 088076065565 | 2025-11-02 | South | Regular |
| 2 | C0003 | Eka Santoso | ekasantoso3@example.com | 085131488452 | 2025-08-07 | North | Regular |
=== Orders | shape=(330, 10) ===
| order_id | customer_id | restaurant_id | order_timestamp | order_status | promo_code | subtotal_amount | delivery_fee | discount_amount | total_amount | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | O00001 | C0058 | R0016 | 2026-01-31 00:19:00 | completed | NaN | 111000 | 12000 | 15000 | 108000 |
| 1 | O00002 | C0065 | R0021 | 2026-03-25 04:52:00 | completed | NEWUSER | 29000 | 8000 | 10000 | 27000 |
| 2 | O00003 | C0072 | R0026 | 2026-01-29 14:16:00 | completed | HEMAT15 | 184000 | 15000 | 15000 | 184000 |
=== Order Items | shape=(872, 9) ===
| order_item_id | order_id | line_number | item_name | item_category | quantity | unit_price | line_discount | line_amount | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | OI00001 | O00001 | 1 | Rice Bowl | Main | 1 | 32000 | 5000 | 27000 |
| 1 | OI00002 | O00001 | 2 | Nasi Ayam | Main | 3 | 28000 | 0 | 84000 |
| 2 | OI00003 | O00002 | 1 | Fries | Snack | 1 | 18000 | 0 | 18000 |
=== Payments | shape=(374, 7) ===
| payment_id | order_id | payment_method | payment_status | payment_amount | payment_timestamp | attempt_number | |
|---|---|---|---|---|---|---|---|
| 0 | P00001 | O00001 | e_wallet | failed | 108000 | 2026-01-31 00:21:00 | 1 |
| 1 | P00002 | O00001 | e_wallet | successful | 108000 | 2026-01-31 00:24:00 | 2 |
| 2 | P00003 | O00002 | bank_transfer | successful | 27000 | 2026-03-25 04:55:00 | 1 |
=== Restaurants | shape=(28, 7) ===
| restaurant_id | restaurant_name | cuisine_type | restaurant_zone | price_tier | onboarding_date | active_status | |
|---|---|---|---|---|---|---|---|
| 0 | R0001 | SwiftBite Partner 01 | Asian | West | Mid | 2024-06-15 | active |
| 1 | R0002 | SwiftBite Partner 02 | Western | East | Premium | 2025-01-15 | active |
| 2 | R0003 | SwiftBite Partner 03 | Fast Food | East | Budget | 2024-10-30 | active |
Catat business meaning, expected grain, candidate key, row/record count, access status, dan key test result.
def intake_summary(df, dataset_name):
return {
"dataset": dataset_name,
"rows": len(df),
"columns": len(df.columns),
"column_names": list(df.columns),
}
if DATA_READY:
intake_df = pd.DataFrame([
intake_summary(customers_raw, "Customers"),
intake_summary(orders_raw, "Orders"),
intake_summary(order_items_raw, "Order Items"),
intake_summary(payments_raw, "Payments"),
intake_summary(restaurants_raw, "Restaurants"),
])
display(intake_df)
| dataset | rows | columns | column_names | |
|---|---|---|---|---|
| 0 | Customers | 159 | 7 | [customer_id, full_name, email, phone_number, ... |
| 1 | Orders | 330 | 10 | [order_id, customer_id, restaurant_id, order_t... |
| 2 | Order Items | 872 | 9 | [order_item_id, order_id, line_number, item_na... |
| 3 | Payments | 374 | 7 | [payment_id, order_id, payment_method, payment... |
| 4 | Restaurants | 28 | 7 | [restaurant_id, restaurant_name, cuisine_type,... |
Repeated `order_id` pada **Order Items** atau **Payments** belum tentu duplicate karena grain-nya berbeda dari Orders.
Gunakan hasil check sebagai **evidence awal**, bukan cleaning action otomatis.
def key_check(df, key_cols, dataset_name):
if isinstance(key_cols, str):
key_cols = [key_cols]
missing_key_rows = int(df[key_cols].isna().any(axis=1).sum())
unique_key_count = int(df[key_cols].drop_duplicates().shape[0])
duplicate_key_rows = int(df.duplicated(subset=key_cols, keep=False).sum())
return pd.DataFrame([{
"dataset": dataset_name,
"key_cols": ", ".join(key_cols),
"rows": len(df),
"unique_key_count": unique_key_count,
"missing_key_rows": missing_key_rows,
"duplicate_key_rows": duplicate_key_rows,
"candidate_key_unique": unique_key_count == len(df) and missing_key_rows == 0,
}])
if DATA_READY:
key_results = pd.concat([
key_check(customers_raw, "customer_id", "Customers"),
key_check(orders_raw, "order_id", "Orders"),
key_check(order_items_raw, "order_item_id", "Order Items"),
key_check(order_items_raw, ["order_id", "line_number"], "Order Items — candidate business key"),
key_check(payments_raw, "payment_id", "Payments"),
key_check(restaurants_raw, "restaurant_id", "Restaurants"),
], ignore_index=True)
display(key_results)
| dataset | key_cols | rows | unique_key_count | missing_key_rows | duplicate_key_rows | candidate_key_unique | |
|---|---|---|---|---|---|---|---|
| 0 | Customers | customer_id | 159 | 158 | 0 | 2 | False |
| 1 | Orders | order_id | 330 | 330 | 0 | 0 | True |
| 2 | Order Items | order_item_id | 872 | 872 | 0 | 0 | True |
| 3 | Order Items — candidate business key | order_id, line_number | 872 | 872 | 0 | 0 | True |
| 4 | Payments | payment_id | 374 | 374 | 0 | 0 | True |
| 5 | Restaurants | restaurant_id | 28 | 28 | 0 | 0 | True |
if DATA_READY:
order_item_cardinality = (
order_items_raw.groupby("order_id")
.size()
.rename("item_rows")
.reset_index()
.sort_values("item_rows", ascending=False)
)
payment_cardinality = (
payments_raw.groupby("order_id")
.size()
.rename("payment_rows")
.reset_index()
.sort_values("payment_rows", ascending=False)
)
print("Order Items cardinality")
display(order_item_cardinality.head(10))
print("Payments cardinality")
display(payment_cardinality.head(10))
Order Items cardinality
| order_id | item_rows | |
|---|---|---|
| 224 | O00225 | 5 |
| 142 | O00143 | 5 |
| 117 | O00118 | 5 |
| 180 | O00181 | 5 |
| 69 | O00070 | 4 |
| 61 | O00062 | 4 |
| 243 | O00244 | 4 |
| 32 | O00033 | 4 |
| 90 | O00091 | 4 |
| 154 | O00155 | 4 |
Payments cardinality
| order_id | payment_rows | |
|---|---|---|
| 0 | O00001 | 2 |
| 134 | O00135 | 2 |
| 229 | O00230 | 2 |
| 228 | O00229 | 2 |
| 217 | O00218 | 2 |
| 181 | O00182 | 2 |
| 169 | O00170 | 2 |
| 164 | O00165 | 2 |
| 153 | O00154 | 2 |
| 130 | O00131 | 2 |
Profiling minimal:
- missing / placeholder,
- data type / format,
- category consistency,
- duplicate / key,
- suspicious numeric / formula,
- potential outlier,
- cross-table concern.
Helper berikut menghasilkan evidence awal, bukan automatic judgment.
def basic_profile(df, dataset_name):
return pd.DataFrame([{
"dataset": dataset_name,
"rows": len(df),
"columns": len(df.columns),
"rows_in_exact_duplicate_groups": int(df.duplicated(keep=False).sum()),
"exact_duplicate_extras": int(df.duplicated(keep="first").sum()),
"null_cells": int(df.isna().sum().sum()),
}])
def column_profile(df):
rows = []
for col in df.columns:
s = df[col]
rows.append({
"column": col,
"dtype": str(s.dtype),
"null_count": int(s.isna().sum()),
"null_pct": round(float(s.isna().mean()), 4),
"unique_count": int(s.nunique(dropna=True)),
"sample_values": list(s.dropna().astype(str).unique()[:5]),
})
return pd.DataFrame(rows)
if DATA_READY:
overall_profile = pd.concat(
[basic_profile(df, name) for name, df in raw_tables.items()],
ignore_index=True
)
display(overall_profile)
| dataset | rows | columns | rows_in_exact_duplicate_groups | exact_duplicate_extras | null_cells | |
|---|---|---|---|---|---|---|
| 0 | Customers | 159 | 7 | 2 | 1 | 0 |
| 1 | Orders | 330 | 10 | 0 | 0 | 161 |
| 2 | Order Items | 872 | 9 | 0 | 0 | 0 |
| 3 | Payments | 374 | 7 | 0 | 0 | 0 |
| 4 | Restaurants | 28 | 7 | 0 | 0 | 0 |
# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
display(column_profile(restaurants_raw))
| column | dtype | null_count | null_pct | unique_count | sample_values | |
|---|---|---|---|---|---|---|
| 0 | restaurant_id | object | 0 | 0.0 | 28 | [R0001, R0002, R0003, R0004, R0005] |
| 1 | restaurant_name | object | 0 | 0.0 | 28 | [SwiftBite Partner 01, SwiftBite Partner 02, S... |
| 2 | cuisine_type | object | 0 | 0.0 | 6 | [Asian, Western, Fast Food, Cafe & Beverage, D... |
| 3 | restaurant_zone | object | 0 | 0.0 | 5 | [West, East, North, South, Central] |
| 4 | price_tier | object | 0 | 0.0 | 5 | [Mid, Premium, Budget, PREMIUM , BUDGET ] |
| 5 | onboarding_date | object | 0 | 0.0 | 28 | [2024-06-15, 2025-01-15, 2024-10-30, 2024-09-2... |
| 6 | active_status | object | 0 | 0.0 | 2 | [active, inactive] |
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"))
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))
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
```
def fk_check(child_df, parent_df, child_key, parent_key, relationship_name):
child_keys = child_df[[child_key]].drop_duplicates()
parent_keys = parent_df[[parent_key]].drop_duplicates()
merged = child_keys.merge(
parent_keys,
left_on=child_key,
right_on=parent_key,
how="left",
indicator=True,
)
unmatched = merged.loc[merged["_merge"] == "left_only", child_key]
return {
"relationship": relationship_name,
"child_distinct_keys": len(child_keys),
"unmatched_count": len(unmatched),
"unmatched_sample": unmatched.head(10).tolist(),
}
if DATA_READY:
relationship_checks = pd.DataFrame([
fk_check(orders_raw, customers_raw, "customer_id", "customer_id", "Orders → Customers"),
fk_check(orders_raw, restaurants_raw, "restaurant_id", "restaurant_id", "Orders → Restaurants"),
fk_check(order_items_raw, orders_raw, "order_id", "order_id", "Order Items → Orders"),
fk_check(payments_raw, orders_raw, "order_id", "order_id", "Payments → Orders"),
])
display(relationship_checks)
| relationship | child_distinct_keys | unmatched_count | unmatched_sample | |
|---|---|---|---|---|
| 0 | Orders → Customers | 158 | 0 | [] |
| 1 | Orders → Restaurants | 28 | 0 | [] |
| 2 | Order Items → Orders | 330 | 0 | [] |
| 3 | Payments → Orders | 330 | 0 | [] |
### Order Items
`line_amount = quantity × unit_price − line_discount`
### Orders
`total_amount = subtotal_amount + delivery_fee − discount_amount`
Cell ini hanya membuat verification view. Tidak melakukan cleaning.
if DATA_READY:
item_verify = order_items_raw.copy()
item_verify["expected_line_amount"] = (
pd.to_numeric(item_verify["quantity"], errors="coerce")
* pd.to_numeric(item_verify["unit_price"], errors="coerce")
- pd.to_numeric(item_verify["line_discount"], errors="coerce")
)
item_verify["line_formula_match"] = (
pd.to_numeric(item_verify["line_amount"], errors="coerce")
== item_verify["expected_line_amount"]
)
display(
item_verify[
["order_item_id", "order_id", "quantity", "unit_price",
"line_discount", "line_amount", "expected_line_amount", "line_formula_match"]
].head()
)
order_verify = orders_raw.copy()
order_verify["expected_total_amount"] = (
pd.to_numeric(order_verify["subtotal_amount"], errors="coerce")
+ pd.to_numeric(order_verify["delivery_fee"], errors="coerce")
- pd.to_numeric(order_verify["discount_amount"], errors="coerce")
)
order_verify["order_formula_match"] = (
pd.to_numeric(order_verify["total_amount"], errors="coerce")
== order_verify["expected_total_amount"]
)
display(
order_verify[
["order_id", "subtotal_amount", "delivery_fee", "discount_amount",
"total_amount", "expected_total_amount", "order_formula_match"]
].head()
)
| order_item_id | order_id | quantity | unit_price | line_discount | line_amount | expected_line_amount | line_formula_match | |
|---|---|---|---|---|---|---|---|---|
| 0 | OI00001 | O00001 | 1 | 32000 | 5000 | 27000 | 27000.0 | True |
| 1 | OI00002 | O00001 | 3 | 28000 | 0 | 84000 | 84000.0 | True |
| 2 | OI00003 | O00002 | 1 | 18000 | 0 | 18000 | 18000.0 | True |
| 3 | OI00004 | O00002 | 1 | 16000 | 5000 | 11000 | 11000.0 | True |
| 4 | OI00005 | O00003 | 1 | 32000 | 0 | 32000 | 32000.0 | True |
| order_id | subtotal_amount | delivery_fee | discount_amount | total_amount | expected_total_amount | order_formula_match | |
|---|---|---|---|---|---|---|---|
| 0 | O00001 | 111000 | 12000 | 15000 | 108000 | 108000 | True |
| 1 | O00002 | 29000 | 8000 | 10000 | 27000 | 27000 | True |
| 2 | O00003 | 184000 | 15000 | 15000 | 184000 | 184000 | True |
| 3 | O00004 | 183000 | 15000 | 0 | 198000 | 198000 | True |
| 4 | O00005 | 134000 | 18000 | 10000 | 142000 | 142000 | True |
Lakukan reconciliation setelah memahami item-level validity. Jangan langsung mengoreksi Orders jika item detail sendiri belum diverifikasi.
if DATA_READY:
item_amount_numeric = pd.to_numeric(order_items_raw["line_amount"], errors="coerce")
item_agg_raw = (
order_items_raw.assign(_line_amount_numeric=item_amount_numeric)
.groupby("order_id", as_index=False)["_line_amount_numeric"]
.sum(min_count=1)
.rename(columns={"_line_amount_numeric": "sum_line_amount"})
)
order_item_recon_raw = orders_raw[
["order_id", "subtotal_amount"]
].merge(item_agg_raw, on="order_id", how="left")
order_item_recon_raw["subtotal_numeric"] = pd.to_numeric(
order_item_recon_raw["subtotal_amount"], errors="coerce"
)
order_item_recon_raw["reconcile_match"] = (
order_item_recon_raw["subtotal_numeric"]
== order_item_recon_raw["sum_line_amount"]
)
display(order_item_recon_raw.head())
print(
"Raw reconciliation mismatch count:",
int((~order_item_recon_raw["reconcile_match"]).sum())
)
| order_id | subtotal_amount | sum_line_amount | subtotal_numeric | reconcile_match | |
|---|---|---|---|---|---|
| 0 | O00001 | 111000 | 111000 | 111000 | True |
| 1 | O00002 | 29000 | 29000 | 29000 | True |
| 2 | O00003 | 184000 | 184000 | 184000 | True |
| 3 | O00004 | 183000 | 183000 | 183000 | True |
| 4 | O00005 | 134000 | 134000 | 134000 | True |
Raw reconciliation mismatch count: 3
if DATA_READY:
mismatch_rows = order_item_recon_raw[
~order_item_recon_raw["reconcile_match"]
]
display(mismatch_rows)
| order_id | subtotal_amount | sum_line_amount | subtotal_numeric | reconcile_match | |
|---|---|---|---|---|---|
| 49 | O00050 | 205000 | 212000 | 205000 | False |
| 190 | O00191 | 199000 | 194000 | 199000 | False |
| 200 | O00201 | 161000 | 151000 | 161000 | False |
if DATA_READY:
duplicate_customers = customers_raw[
customers_raw.duplicated(
subset=["customer_id"],
keep=False
)
].sort_values("customer_id")
display(duplicate_customers)
| customer_id | full_name | phone_number | signup_date | customer_zone | customer_segment | ||
|---|---|---|---|---|---|---|---|
| 30 | C0031 | Farah Hidayat | farahhidayat31@example.com | 089605670796 | 2025-06-11 | North | Regular |
| 158 | C0031 | Farah Hidayat | farahhidayat31@example.com | 089605670796 | 2025-06-11 | North | Regular |
if DATA_READY:
item_formula_check = order_items_raw.copy()
item_formula_check["quantity_numeric"] = pd.to_numeric(
item_formula_check["quantity"],
errors="coerce"
)
item_formula_check["expected_line_amount"] = (
item_formula_check["quantity_numeric"]
* pd.to_numeric(item_formula_check["unit_price"], errors="coerce")
- pd.to_numeric(item_formula_check["line_discount"], errors="coerce")
)
item_formula_check["line_amount_numeric"] = pd.to_numeric(
item_formula_check["line_amount"],
errors="coerce"
)
item_formula_check["line_formula_match"] = (
item_formula_check["line_amount_numeric"]
== item_formula_check["expected_line_amount"]
)
mismatch_items_all = item_formula_check[
item_formula_check["line_formula_match"] == False
]
print("Total baris Order Items:", len(item_formula_check))
print("Jumlah baris tidak sesuai:", len(mismatch_items_all))
print(
"Jumlah baris sesuai:",
int(item_formula_check["line_formula_match"].sum())
)
display(
mismatch_items_all[
[
"order_item_id",
"order_id",
"quantity",
"unit_price",
"line_discount",
"line_amount",
"expected_line_amount",
"line_formula_match"
]
]
)
Total baris Order Items: 872 Jumlah baris tidak sesuai: 4 Jumlah baris sesuai: 868
| order_item_id | order_id | quantity | unit_price | line_discount | line_amount | expected_line_amount | line_formula_match | |
|---|---|---|---|---|---|---|---|---|
| 70 | OI00071 | O00027 | 3 pcs | 20000 | 5000 | 55000 | NaN | False |
| 130 | OI00131 | O00050 | 3 | 20000 | 0 | 67000 | 60000.0 | False |
| 214 | OI00215 | O00083 | 1 pcs | 22000 | 0 | 22000 | NaN | False |
| 510 | OI00511 | O00191 | 3 | 32000 | 5000 | 86000 | 91000.0 | False |
if DATA_READY:
display(
order_items_raw[
order_items_raw["order_item_id"].isin(
["OI00071", "OI00215"]
)
][
[
"order_item_id",
"order_id",
"quantity",
"unit_price",
"line_discount",
"line_amount"
]
]
)
| order_item_id | order_id | quantity | unit_price | line_discount | line_amount | |
|---|---|---|---|---|---|---|
| 70 | OI00071 | O00027 | 3 pcs | 20000 | 5000 | 55000 |
| 214 | OI00215 | O00083 | 1 pcs | 22000 | 0 | 22000 |
if DATA_READY:
print("Jumlah variasi nilai quantity:")
print(order_items_raw["quantity"].astype(str).value_counts().head(30))
print("\nJumlah nilai quantity yang mengandung teks:")
quantity_text_mask = (
order_items_raw["quantity"]
.astype(str)
.str.contains(r"[A-Za-z]", na=False)
)
print(int(quantity_text_mask.sum()))
print("\nBaris dengan quantity yang mengandung teks:")
display(
order_items_raw.loc[
quantity_text_mask,
["order_item_id", "order_id", "quantity", "unit_price",
"line_discount", "line_amount"]
]
)
Jumlah variasi nilai quantity: quantity 2 287 1 280 3 263 4 40 3 pcs 1 1 pcs 1 Name: count, dtype: int64 Jumlah nilai quantity yang mengandung teks: 2 Baris dengan quantity yang mengandung teks:
| order_item_id | order_id | quantity | unit_price | line_discount | line_amount | |
|---|---|---|---|---|---|---|
| 70 | OI00071 | O00027 | 3 pcs | 20000 | 5000 | 55000 |
| 214 | OI00215 | O00083 | 1 pcs | 22000 | 0 | 22000 |
if DATA_READY:
display(
order_items_raw[
order_items_raw["order_item_id"].isin(
["OI00131", "OI00511"]
)
][
[
"order_item_id",
"order_id",
"quantity",
"unit_price",
"line_discount",
"line_amount"
]
]
)
print("\nDetail item untuk kedua pesanan:")
display(
order_items_raw[
order_items_raw["order_id"].isin(
["O00050", "O00191"]
)
][
[
"order_item_id",
"order_id",
"quantity",
"unit_price",
"line_discount",
"line_amount"
]
].sort_values(["order_id", "order_item_id"])
)
| order_item_id | order_id | quantity | unit_price | line_discount | line_amount | |
|---|---|---|---|---|---|---|
| 130 | OI00131 | O00050 | 3 | 20000 | 0 | 67000 |
| 510 | OI00511 | O00191 | 3 | 32000 | 5000 | 86000 |
Detail item untuk kedua pesanan:
| order_item_id | order_id | quantity | unit_price | line_discount | line_amount | |
|---|---|---|---|---|---|---|
| 130 | OI00131 | O00050 | 3 | 20000 | 0 | 67000 |
| 131 | OI00132 | O00050 | 2 | 20000 | 0 | 40000 |
| 132 | OI00133 | O00050 | 2 | 32000 | 2000 | 62000 |
| 133 | OI00134 | O00050 | 3 | 16000 | 5000 | 43000 |
| 508 | OI00509 | O00191 | 2 | 42000 | 2000 | 82000 |
| 509 | OI00510 | O00191 | 1 | 26000 | 0 | 26000 |
| 510 | OI00511 | O00191 | 3 | 32000 | 5000 | 86000 |
if DATA_READY:
display(
orders_raw.loc[
orders_raw["order_id"].isin(["O00050", "O00191"]),
[
"order_id",
"subtotal_amount",
"delivery_fee",
"discount_amount",
"total_amount"
]
].to_string(index=False)
)
'order_id subtotal_amount delivery_fee discount_amount total_amount\n O00050 205000 10000 10000 205000\n O00191 199000 8000 10000 197000'
date_columns = {
"customers": (customers_raw, "signup_date"),
"orders": (orders_raw, "order_timestamp"),
"payments": (payments_raw, "payment_timestamp"),
"restaurants": (restaurants_raw, "onboarding_date"),
}
for dataset_name, (df, column) in date_columns.items():
print(f"\n{'=' * 60}")
print(f"Dataset: {dataset_name} | Kolom: {column}")
print(f"Total baris: {len(df)}")
print(f"Nilai null: {df[column].isna().sum()}")
print(f"Jumlah nilai unik: {df[column].nunique(dropna=True)}")
print("\nContoh nilai:")
display(df[column].dropna().drop_duplicates().head(10).to_frame())
============================================================ Dataset: customers | Kolom: signup_date Total baris: 159 Nilai null: 0 Jumlah nilai unik: 108 Contoh nilai:
| signup_date | |
|---|---|
| 0 | 2025-10-30 |
| 1 | 2025-11-02 |
| 2 | 2025-08-07 |
| 3 | 2025-11-12 |
| 4 | 2025-07-30 |
| 5 | 2025-08-11 |
| 6 | 2025-06-03 |
| 7 | 2025-07-25 |
| 8 | 2025-10-31 |
| 9 | 2025-11-22 |
============================================================ Dataset: orders | Kolom: order_timestamp Total baris: 330 Nilai null: 0 Jumlah nilai unik: 329 Contoh nilai:
| order_timestamp | |
|---|---|
| 0 | 2026-01-31 00:19:00 |
| 1 | 2026-03-25 04:52:00 |
| 2 | 2026-01-29 14:16:00 |
| 3 | 2026-01-02 16:09:00 |
| 4 | 2026-03-10 14:20:00 |
| 5 | 2026-03-14 14:50:00 |
| 6 | 2026-02-18 18:48:00 |
| 7 | 2026-03-29 19:52:00 |
| 8 | 2026-01-14 13:00:00 |
| 9 | 2026-02-12 21:37:00 |
============================================================ Dataset: payments | Kolom: payment_timestamp Total baris: 374 Nilai null: 0 Jumlah nilai unik: 373 Contoh nilai:
| payment_timestamp | |
|---|---|
| 0 | 2026-01-31 00:21:00 |
| 1 | 2026-01-31 00:24:00 |
| 2 | 2026-03-25 04:55:00 |
| 3 | 2026-01-29 14:19:00 |
| 4 | 2026-01-02 16:12:00 |
| 5 | 2026-03-10 14:23:00 |
| 6 | 2026-03-14 14:53:00 |
| 7 | 2026-02-18 18:50:00 |
| 8 | 2026-02-18 18:53:00 |
| 9 | 2026-03-29 19:55:00 |
============================================================ Dataset: restaurants | Kolom: onboarding_date Total baris: 28 Nilai null: 0 Jumlah nilai unik: 28 Contoh nilai:
| onboarding_date | |
|---|---|
| 0 | 2024-06-15 |
| 1 | 2025-01-15 |
| 2 | 2024-10-30 |
| 3 | 2024-09-24 |
| 4 | 2024-11-14 |
| 5 | 2025-04-02 |
| 6 | 2025-01-18 |
| 7 | 2024-05-07 |
| 8 | 2025-03-13 |
| 9 | 2024-06-17 |
date_formats = {
"customers": ("signup_date", "%Y-%m-%d"),
"orders": ("order_timestamp", "%Y-%m-%d %H:%M:%S"),
"payments": ("payment_timestamp", "%Y-%m-%d %H:%M:%S"),
"restaurants": ("onboarding_date", "%Y-%m-%d"),
}
date_results = []
for dataset_name, (column, date_format) in date_formats.items():
df = date_columns[dataset_name][0]
raw = df[column]
# Parse untuk validasi saja; data asli tidak diubah
parsed = pd.to_datetime(
raw,
format=date_format,
errors="coerce"
)
invalid_mask = raw.notna() & parsed.isna()
duplicate_count = raw.notna().sum() - raw.nunique(dropna=True)
date_results.append({
"dataset": dataset_name,
"column": column,
"total_rows": len(raw),
"null_count": raw.isna().sum(),
"invalid_format_or_date": invalid_mask.sum(),
"duplicate_values": duplicate_count,
"earliest_date": parsed.min(),
"latest_date": parsed.max(),
})
if invalid_mask.any():
print(f"\nNilai tanggal tidak valid — {dataset_name}:")
display(df.loc[invalid_mask, [column]].head(20))
date_validation = pd.DataFrame(date_results)
display(date_validation)
Nilai tanggal tidak valid — customers:
| signup_date | |
|---|---|
| 21 | 15/08/2025 |
| 68 | Oct 02 2025 |
| dataset | column | total_rows | null_count | invalid_format_or_date | duplicate_values | earliest_date | latest_date | |
|---|---|---|---|---|---|---|---|---|
| 0 | customers | signup_date | 159 | 0 | 2 | 51 | 2025-06-01 00:00:00 | 2025-12-27 00:00:00 |
| 1 | orders | order_timestamp | 330 | 0 | 0 | 1 | 2026-01-01 17:30:00 | 2026-03-31 00:01:00 |
| 2 | payments | payment_timestamp | 374 | 0 | 0 | 1 | 2026-01-01 17:33:00 | 2026-03-31 00:06:00 |
| 3 | restaurants | onboarding_date | 28 | 0 | 0 | 0 | 2024-01-03 00:00:00 | 2025-05-04 00:00:00 |
customers_raw.loc[[21, 68], ["signup_date"]].copy().assign(
parsed_date=lambda x: [
pd.to_datetime("15/08/2025", format="%d/%m/%Y"),
pd.to_datetime("Oct 02 2025", format="%b %d %Y")
]
)
| signup_date | parsed_date | |
|---|---|---|
| 21 | 15/08/2025 | 2025-08-15 |
| 68 | Oct 02 2025 | 2025-10-02 |
Rulebook dibuat setelah verification dan sebelum coding.
Gunakan hanya `FIX` atau `STANDARDIZE` sebagai cleaning decision. Finding `KEEP` tidak boleh diubah hanya agar data terlihat lebih rapi.
RULE_COLUMNS = [
"rule_id",
"source_finding_id",
"dataset",
"trigger_condition",
"decision",
"cleaning_action",
"reason_evidence",
"implementation_note",
"validation_criteria",
"rule_status",
]
cleaning_rules = pd.DataFrame(columns=RULE_COLUMNS)
display(cleaning_rules)
| rule_id | source_finding_id | dataset | trigger_condition | decision | cleaning_action | reason_evidence | implementation_note | validation_criteria | rule_status |
|---|
```python
new_rule = {
"rule_id": "CR-01",
"source_finding_id": "F-01",
"dataset": "...",
"trigger_condition": "...",
"decision": "FIX / STANDARDIZE",
"cleaning_action": "...",
"reason_evidence": "...",
"implementation_note": "...",
"validation_criteria": "...",
"rule_status": "Approved",
}
cleaning_rules.loc[len(cleaning_rules)] = new_rule
```
new_rules = [
{
"rule_id": "CR-01",
"source_finding_id": "F-01",
"dataset": "customers",
"trigger_condition": "customer_id C0031 muncul dua kali dengan seluruh nilai kolom identik",
"decision": "FIX",
"cleaning_action": "Hapus satu baris duplikat C0031 dan pertahankan satu baris yang identik",
"reason_evidence": "C0031 ditemukan pada indeks 30 dan 158; seluruh nilai pada kedua baris identik",
"implementation_note": "Terapkan pada customers_clean, pertahankan satu baris C0031",
"validation_criteria": "Jumlah baris customers_clean menjadi 158 dan customer_id C0031 hanya muncul satu kali",
"rule_status": "Draft",
},
{
"rule_id": "CR-02",
"source_finding_id": "F-02",
"dataset": "customers",
"trigger_condition": "customer_segment memiliki variasi penulisan Regular, REGULAR, Loyal, dan LOYAL",
"decision": "STANDARDIZE",
"cleaning_action": "Standarkan label menjadi Regular dan Loyal",
"reason_evidence": "Ditemukan variasi huruf kapital dan spasi pada label customer_segment",
"implementation_note": "Terapkan pemetaan label pada customers_clean tanpa mengubah kategori",
"validation_criteria": "Nilai unik customer_segment hanya Regular dan Loyal",
"rule_status": "Draft",
},
{
"rule_id": "CR-03",
"source_finding_id": "F-03",
"dataset": "orders",
"trigger_condition": "order_status memiliki label completed dan COMPLETED dengan spasi di akhir",
"decision": "STANDARDIZE",
"cleaning_action": "Standarkan label COMPLETED menjadi completed",
"reason_evidence": "Variasi kapitalisasi dan spasi ditemukan pada label status yang sama",
"implementation_note": "Terapkan pada orders_clean; pertahankan status cancelled",
"validation_criteria": "Label completed konsisten dan status cancelled tetap dipertahankan",
"rule_status": "Draft",
},
{
"rule_id": "CR-04",
"source_finding_id": "F-06",
"dataset": "order_items",
"trigger_condition": "item_category memiliki variasi Main dan MAIN dengan spasi di akhir",
"decision": "STANDARDIZE",
"cleaning_action": "Standarkan label MAIN menjadi Main",
"reason_evidence": "Ditemukan variasi kapitalisasi dan spasi pada label kategori",
"implementation_note": "Terapkan pada order_items_clean tanpa mengubah makna kategori",
"validation_criteria": "Label kategori Main konsisten dan tidak ada variasi MAIN dengan spasi",
"rule_status": "Draft",
},
{
"rule_id": "CR-05",
"source_finding_id": "F-07",
"dataset": "restaurants",
"trigger_condition": "price_tier memiliki variasi Premium, PREMIUM, Budget, dan BUDGET dengan spasi di akhir",
"decision": "STANDARDIZE",
"cleaning_action": "Standarkan label menjadi Premium dan Budget",
"reason_evidence": "Ditemukan variasi kapitalisasi dan spasi pada label price_tier",
"implementation_note": "Terapkan pemetaan label pada restaurants_clean",
"validation_criteria": "Nilai unik price_tier hanya Premium dan Budget",
"rule_status": "Draft",
},
{
"rule_id": "CR-06",
"source_finding_id": "F-11",
"dataset": "customers",
"trigger_condition": "signup_date memiliki format tanggal yang tidak konsisten, termasuk 15/08/2025 dan Oct 02 2025",
"decision": "STANDARDIZE",
"cleaning_action": "Seragamkan seluruh nilai signup_date yang valid menjadi format YYYY-MM-DD",
"reason_evidence": "Nilai pada indeks 21 berhasil dikonversi menjadi 2025-08-15 dan indeks 68 menjadi 2025-10-02",
"implementation_note": "Terapkan hanya pada customers_clean dan pertahankan tanggal yang direpresentasikan",
"validation_criteria": "Semua nilai signup_date yang tidak kosong mengikuti format YYYY-MM-DD; jumlah baris tetap dan tidak ada nilai kosong baru",
"rule_status": "Draft",
},
{
"rule_id": "CR-07",
"source_finding_id": "F-12",
"dataset": "payments",
"trigger_condition": "payment_method memiliki variasi cash dan CASH , serta credit_card dan CREDIT CARD ",
"decision": "STANDARDIZE",
"cleaning_action": "Standarkan CASH menjadi cash dan CREDIT CARD menjadi credit_card setelah menghapus spasi di akhir label",
"reason_evidence": "Pemeriksaan nilai unik menemukan cash sebanyak 89 baris dan CASH dengan spasi di akhir sebanyak 1 baris, serta credit_card sebanyak 93 baris dan CREDIT CARD dengan spasi di akhir sebanyak 1 baris",
"implementation_note": "Terapkan pemetaan hanya pada payments_clean['payment_method']; jangan menghapus baris atau mengubah kolom lain",
"validation_criteria": "Nilai CASH dan CREDIT CARD dengan spasi di akhir tidak lagi ditemukan; label standar cash dan credit_card tetap ada; jumlah baris tetap 374 dan kolom lain tidak berubah",
"rule_status": "Draft",
},
{
"rule_id": "CR-08",
"source_finding_id": "F-05",
"dataset": "order_items",
"trigger_condition": "Kolom quantity bertipe teks dan mengandung akhiran 'pcs' pada baris OI00071 dan OI00215",
"decision": "STANDARDIZE",
"cleaning_action": "Menghapus teks 'pcs' dan spasi, lalu mengonversi kolom quantity ke integer",
"reason_evidence": "Ditemukan 2 baris non-numerik ('3 pcs' dan '1 pcs') dari 872 baris yang menyebabkan konversi numerik gagal",
"implementation_note": "Terapkan pada order_items_clean['quantity']; pastikan nilai '3 pcs' menjadi 3 dan '1 pcs' menjadi 1",
"validation_criteria": "Tidak ada lagi string 'pcs', seluruh kolom quantity bertipe integer, tidak ada nilai NaN baru",
"rule_status": "Draft",
},
{
"rule_id": "CR-09",
"source_finding_id": "F-10",
"dataset": "order_items",
"trigger_condition": "Dua baris memiliki line_amount yang tidak sesuai rumus (OI00131 selisih 7.000 dan OI00511 selisih 5.000)",
"decision": "FIX",
"cleaning_action": "Menghitung ulang line_amounnt = quantity * unit_price - line_discount",
"reason_evidence": "OI00131 bernilai 67.000 (harus 60.000) dan OI00511 bernilai 86.000 (harus 91.000). Koreksi ini cocok dengan rekonsiliasi subtotal order O00050 dan O00191",
"implementation_note": "Terapkan formula pada order_items_clean setelah quantity distandarkan menjadi angka",
"validation_criteria": "Seluruh baris order_items_clean memenuhi formula line_amount tanpa ada selisih perhitungan",
"rule_status": "Draft",
},
{
"rule_id": "CR-10",
"source_finding_id": "F-08",
"dataset": "orders",
"trigger_condition": "Subtotal amount pada order O00201 tercatat 161.000, selisih 10.000 dengan jumlah item (151.000) dan pembayaran (159.000)",
"decision": "FIX",
"cleaning_action": "Menyesuaikan subtotal_amount menjadi 151.000 dan total_amount menjadi 159.000 pada order O00201",
"reason_evidence": "Tabel payments membuktikan pelanggan membayar 159.000 (151.000 item + 8.000 delivery fee). Subtotal 161.000 adalah salah rekam di sistem order",
"implementation_note": "Perbarui baris O00201 pada orders_clean",
"validation_criteria": "Rekonsiliasi subtotal_amount dengan SUM(line_amount) untuk seluruh order cocok tanpa selisih (mismatch = 0)",
"rule_status": "Draft",
},
]
cleaning_rules = pd.DataFrame(new_rules, columns=RULE_COLUMNS)
display(cleaning_rules)
| rule_id | source_finding_id | dataset | trigger_condition | decision | cleaning_action | reason_evidence | implementation_note | validation_criteria | rule_status | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | CR-01 | F-01 | customers | customer_id C0031 muncul dua kali dengan selur... | FIX | Hapus satu baris duplikat C0031 dan pertahanka... | C0031 ditemukan pada indeks 30 dan 158; seluru... | Terapkan pada customers_clean, pertahankan sat... | Jumlah baris customers_clean menjadi 158 dan c... | Draft |
| 1 | CR-02 | F-02 | customers | customer_segment memiliki variasi penulisan Re... | STANDARDIZE | Standarkan label menjadi Regular dan Loyal | Ditemukan variasi huruf kapital dan spasi pada... | Terapkan pemetaan label pada customers_clean t... | Nilai unik customer_segment hanya Regular dan ... | Draft |
| 2 | CR-03 | F-03 | orders | order_status memiliki label completed dan COMP... | STANDARDIZE | Standarkan label COMPLETED menjadi completed | Variasi kapitalisasi dan spasi ditemukan pada ... | Terapkan pada orders_clean; pertahankan status... | Label completed konsisten dan status cancelled... | Draft |
| 3 | CR-04 | F-06 | order_items | item_category memiliki variasi Main dan MAIN d... | STANDARDIZE | Standarkan label MAIN menjadi Main | Ditemukan variasi kapitalisasi dan spasi pada ... | Terapkan pada order_items_clean tanpa mengubah... | Label kategori Main konsisten dan tidak ada va... | Draft |
| 4 | CR-05 | F-07 | restaurants | price_tier memiliki variasi Premium, PREMIUM, ... | STANDARDIZE | Standarkan label menjadi Premium dan Budget | Ditemukan variasi kapitalisasi dan spasi pada ... | Terapkan pemetaan label pada restaurants_clean | Nilai unik price_tier hanya Premium dan Budget | Draft |
| 5 | CR-06 | F-11 | customers | signup_date memiliki format tanggal yang tidak... | STANDARDIZE | Seragamkan seluruh nilai signup_date yang vali... | Nilai pada indeks 21 berhasil dikonversi menja... | Terapkan hanya pada customers_clean dan pertah... | Semua nilai signup_date yang tidak kosong meng... | Draft |
| 6 | CR-07 | F-12 | payments | payment_method memiliki variasi cash dan CASH ... | STANDARDIZE | Standarkan CASH menjadi cash dan CREDIT CARD m... | Pemeriksaan nilai unik menemukan cash sebanyak... | Terapkan pemetaan hanya pada payments_clean['p... | Nilai CASH dan CREDIT CARD dengan spasi di akh... | Draft |
| 7 | CR-08 | F-05 | order_items | Kolom quantity bertipe teks dan mengandung akh... | STANDARDIZE | Menghapus teks 'pcs' dan spasi, lalu mengonver... | Ditemukan 2 baris non-numerik ('3 pcs' dan '1 ... | Terapkan pada order_items_clean['quantity']; p... | Tidak ada lagi string 'pcs', seluruh kolom qua... | Draft |
| 8 | CR-09 | F-10 | order_items | Dua baris memiliki line_amount yang tidak sesu... | FIX | Menghitung ulang line_amounnt = quantity * uni... | OI00131 bernilai 67.000 (harus 60.000) dan OI0... | Terapkan formula pada order_items_clean setela... | Seluruh baris order_items_clean memenuhi formu... | Draft |
| 9 | CR-10 | F-08 | orders | Subtotal amount pada order O00201 tercatat 161... | FIX | Menyesuaikan subtotal_amount menjadi 151.000 d... | Tabel payments membuktikan pelanggan membayar ... | Perbarui baris O00201 pada orders_clean | Rekonsiliasi subtotal_amount dengan SUM(line_a... | Draft |
print(cleaning_rules.columns.tolist()) display(cleaning_rules[["rule_id", "source_finding_id", "dataset", "decision", "rule_status"]])
['rule_id', 'source_finding_id', 'dataset', 'trigger_condition', 'decision', 'cleaning_action', 'reason_evidence', 'implementation_note', 'validation_criteria', 'rule_status']
| rule_id | source_finding_id | dataset | decision | rule_status | |
|---|---|---|---|---|---|
| 0 | CR-01 | F-01 | customers | FIX | Draft |
| 1 | CR-02 | F-02 | customers | STANDARDIZE | Draft |
| 2 | CR-03 | F-03 | orders | STANDARDIZE | Draft |
| 3 | CR-04 | F-06 | order_items | STANDARDIZE | Draft |
| 4 | CR-05 | F-07 | restaurants | STANDARDIZE | Draft |
| 5 | CR-06 | F-11 | customers | STANDARDIZE | Draft |
| 6 | CR-07 | F-12 | payments | STANDARDIZE | Draft |
| 7 | CR-08 | F-05 | order_items | STANDARDIZE | Draft |
| 8 | CR-09 | F-10 | order_items | FIX | Draft |
| 9 | CR-10 | F-08 | orders | FIX | Draft |
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
CLEANING_LOG_COLUMNS = [
"log_id",
"rule_id",
"dataset",
"affected_records_scope",
"action_performed",
"before_evidence",
"after_evidence",
"rows_records_affected",
"result",
"notes",
]
cleaning_log = pd.DataFrame(columns=CLEANING_LOG_COLUMNS)
display(cleaning_log)
| log_id | rule_id | dataset | affected_records_scope | action_performed | before_evidence | after_evidence | rows_records_affected | result | notes |
|---|
from IPython import display
# ============================================================
# EDIT AREA 2 — APPROVED CLEANING CODE
# ============================================================
CLEANING_LOG_COLUMNS = [
"log_id",
"rule_id",
"dataset",
"affected_records_scope",
"action_performed",
"before_evidence",
"after_evidence",
"rows_records_affected",
"result",
"notes",
]
cleaning_log_rows = []
def add_cleaning_log(
rule_id, dataset, scope, action,
before, after, affected, notes="",
result="Pending",
prefix="CL" # Diselaraskan dengan format Working Paper Excel: CL-01, CL-02, dst.
):
cleaning_log_rows.append({
"log_id": f"{prefix}-{len(cleaning_log_rows) + 1:02d}",
"rule_id": rule_id,
"dataset": dataset,
"affected_records_scope": scope,
"action_performed": action,
"before_evidence": before,
"after_evidence": after,
"rows_records_affected": affected,
"result": result,
"notes": notes,
})
# ------------------------------------------------------------
# CR-01 — Menghapus satu baris customer duplikat
# Source Finding: F-01
# ------------------------------------------------------------
before_rows = len(customers_clean)
before_duplicates = int(
customers_clean["customer_id"].duplicated(keep="first").sum()
)
before_c0031 = int(
(customers_clean["customer_id"] == "C0031").sum()
)
customers_clean = customers_clean.drop_duplicates(
subset=["customer_id"], keep="first"
).copy()
after_rows = len(customers_clean)
after_c0031 = int(
(customers_clean["customer_id"] == "C0031").sum()
)
cr01_pass = (
after_rows == 158
and after_c0031 == 1
)
add_cleaning_log(
"CR-01", "customers", "customer_id C0031",
"Mempertahankan satu baris untuk setiap customer_id duplikat",
f"Baris={before_rows}; C0031={before_c0031}",
f"Baris={after_rows}; C0031={after_c0031}",
before_rows - after_rows,
f"Duplikat customer_id sebelum cleaning={before_duplicates}",
"Validated" if cr01_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-02 — Menyeragamkan customer_segment
# Source Finding: F-02
# ------------------------------------------------------------
before_segment = customers_clean["customer_segment"].copy()
customers_clean["customer_segment"] = (
customers_clean["customer_segment"]
.astype("string")
.str.strip()
.replace({"REGULAR": "Regular", "LOYAL": "Loyal"})
)
changed = int(
before_segment.fillna("<NA>").ne(
customers_clean["customer_segment"].fillna("<NA>")
).sum()
)
segment_values = set(
customers_clean["customer_segment"].dropna().unique()
)
# Kategori sah mencakup Regular, Loyal, dan New
cr02_pass = (
segment_values.issubset({"Regular", "Loyal", "New"})
and not any(v in {"REGULAR", "LOYAL"} for v in segment_values)
)
add_cleaning_log(
"CR-02", "customers", "customer_segment",
"Menghapus spasi berlebih dan menyeragamkan kapitalisasi kategori",
f"Nilai unik sebelum={before_segment.nunique(dropna=True)}",
f"Nilai unik sesudah={customers_clean['customer_segment'].nunique(dropna=True)}",
changed,
f"Nilai unik sesudah={sorted(segment_values)}",
"Validated" if cr02_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-03 — Menyeragamkan order_status
# Source Finding: F-03
# ------------------------------------------------------------
before_status = orders_clean["order_status"].copy()
orders_clean["order_status"] = (
orders_clean["order_status"]
.astype("string")
.str.strip()
.replace({"COMPLETED": "completed"})
)
changed = int(
before_status.fillna("<NA>").ne(
orders_clean["order_status"].fillna("<NA>")
).sum()
)
status_values = set(
orders_clean["order_status"].dropna().unique()
)
# Status kanonikal hanya completed dan cancelled
cr03_pass = (
status_values.issubset({"completed", "cancelled"})
and "completed" in status_values
and "COMPLETED" not in status_values
)
add_cleaning_log(
"CR-03", "orders", "order_status",
"Menghapus spasi berlebih dan menyeragamkan completed",
f"Nilai unik sebelum={before_status.nunique(dropna=True)}",
f"Nilai unik sesudah={orders_clean['order_status'].nunique(dropna=True)}",
changed,
"Status selain completed dipertahankan.",
"Validated" if cr03_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-04 — Menyeragamkan item_category
# Source Finding: F-06
# ------------------------------------------------------------
before_category = order_items_clean["item_category"].copy()
order_items_clean["item_category"] = (
order_items_clean["item_category"]
.astype("string")
.str.strip()
.replace({"MAIN": "Main"})
)
changed = int(
before_category.fillna("<NA>").ne(
order_items_clean["item_category"].fillna("<NA>")
).sum()
)
category_values = set(
order_items_clean["item_category"].dropna().unique()
)
cr04_pass = (
"MAIN" not in category_values
and "Main" in category_values
and not any(v != v.strip() for v in category_values)
)
add_cleaning_log(
"CR-04", "order_items", "item_category",
"Menghapus spasi berlebih dan menyeragamkan kategori Main",
f"Nilai unik sebelum={before_category.nunique(dropna=True)}",
f"Nilai unik sesudah={order_items_clean['item_category'].nunique(dropna=True)}",
changed,
f"Nilai unik sesudah={sorted(category_values)}",
"Validated" if cr04_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-05 — Menyeragamkan price_tier
# Source Finding: F-07
# ------------------------------------------------------------
before_tier = restaurants_clean["price_tier"].copy()
restaurants_clean["price_tier"] = (
restaurants_clean["price_tier"]
.astype("string")
.str.strip()
.replace({"PREMIUM": "Premium", "BUDGET": "Budget"})
)
changed = int(
before_tier.fillna("<NA>").ne(
restaurants_clean["price_tier"].fillna("<NA>")
).sum()
)
tier_values = set(
restaurants_clean["price_tier"].dropna().unique()
)
# Kategori harga hanya Budget, Mid, dan Premium
cr05_pass = (
tier_values.issubset({"Budget", "Mid", "Premium"})
and {"Premium", "Budget"}.issubset(tier_values)
and not any(v in {"PREMIUM", "BUDGET"} for v in tier_values)
and not any(v != v.strip() for v in tier_values)
)
add_cleaning_log(
"CR-05", "restaurants", "price_tier",
"Menghapus spasi berlebih dan menyeragamkan kategori harga",
f"Nilai unik sebelum={before_tier.nunique(dropna=True)}",
f"Nilai unik sesudah={restaurants_clean['price_tier'].nunique(dropna=True)}",
changed,
f"Nilai unik sesudah={sorted(tier_values)}; kategori Mid dipertahankan bila ada.",
"Validated" if cr05_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-06 — Menyeragamkan format signup_date
# Source Finding: F-11
# ------------------------------------------------------------
before_dates = customers_clean["signup_date"].copy()
before_rows = len(customers_clean)
before_nulls = int(before_dates.isna().sum())
def standardize_signup_date(value):
if pd.isna(value):
return pd.NA
for date_format in ("%Y-%m-%d", "%d/%m/%Y", "%b %d %Y"):
parsed = pd.to_datetime(
value, format=date_format, errors="coerce"
)
if not pd.isna(parsed):
return parsed.strftime("%Y-%m-%d")
return pd.NA
customers_clean["signup_date"] = before_dates.map(
standardize_signup_date
).astype("string")
after_rows = len(customers_clean)
after_nulls = int(customers_clean["signup_date"].isna().sum())
changed = int(
before_dates.fillna("<NA>").ne(
customers_clean["signup_date"].fillna("<NA>")
).sum()
)
# Format YYYY-MM-DD diperiksa secara eksplisit
non_null_dates = customers_clean["signup_date"].dropna()
date_format_ok = non_null_dates.str.fullmatch(
r"\d{4}-\d{2}-\d{2}"
).all()
cr06_pass = (
after_rows == before_rows
and after_nulls <= before_nulls
and date_format_ok
)
add_cleaning_log(
"CR-06", "customers", "signup_date",
"Mengonversi tanggal valid ke format YYYY-MM-DD",
f"Contoh indeks 21={before_dates.loc[21] if 21 in before_dates.index else '-'}; "
f"indeks 68={before_dates.loc[68] if 68 in before_dates.index else '-'}; "
f"baris={before_rows}; null={before_nulls}",
f"Indeks 21={customers_clean.loc[21, 'signup_date'] if 21 in customers_clean.index else '-'}; "
f"indeks 68={customers_clean.loc[68, 'signup_date'] if 68 in customers_clean.index else '-'}; "
f"baris={after_rows}; null={after_nulls}",
changed,
"Periksa nilai tanggal yang gagal dikonversi sebelum menyatakan PASS.",
"Validated" if cr06_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-07 — Menyeragamkan payment_method
# Source Finding: F-12
# ------------------------------------------------------------
before_method = payments_clean["payment_method"].copy()
before_rows = len(payments_clean)
before_unique = before_method.value_counts(
dropna=False
).to_dict()
payments_clean["payment_method"] = (
payments_clean["payment_method"]
.astype("string")
.str.strip()
.replace({
"CASH": "cash",
"CREDIT CARD": "credit_card",
})
)
after_method = payments_clean["payment_method"]
after_rows = len(payments_clean)
changed = int(
before_method.fillna("<NA>").ne(
after_method.fillna("<NA>")
).sum()
)
after_unique = after_method.value_counts(
dropna=False
).to_dict()
remaining_variants = {
str(value)
for value in after_method.dropna().unique()
}.intersection({"CASH", "CREDIT CARD"})
cr07_pass = (
after_rows == before_rows == 374
and len(remaining_variants) == 0
and {"cash", "credit_card"}.issubset(
set(after_method.dropna().unique())
)
and not any(v != v.strip() for v in after_method.dropna().unique())
)
add_cleaning_log(
"CR-07", "payments", "payment_method",
"Menghapus spasi berlebih dan menyeragamkan label metode pembayaran",
f"Nilai unik sebelum={before_unique}; baris={before_rows}",
f"Nilai unik sesudah={after_unique}; baris={after_rows}",
changed,
"Kolom lain tidak diubah oleh blok CR-07. Pastikan definisi CREDIT CARD dan credit_card setara.",
"Validated" if cr07_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-08 — Menyeragamkan quantity ke numerik integer
# Source Finding: F-05
# ------------------------------------------------------------
before_qty = order_items_clean["quantity"].copy()
before_non_numeric = int(
pd.to_numeric(before_qty.astype(str).str.strip(), errors="coerce").isna().sum()
)
# Hilangkan 'pcs' dan spasi, lalu ubah ke integer
order_items_clean["quantity"] = (
order_items_clean["quantity"]
.astype(str)
.str.replace("pcs", "", regex=False)
.str.strip()
.astype(int)
)
after_qty = order_items_clean["quantity"]
after_non_numeric = int(
pd.to_numeric(after_qty, errors="coerce").isna().sum()
)
changed_qty = int(
before_qty.astype(str).fillna("<NA>").ne(
after_qty.astype(str).fillna("<NA>")
).sum()
)
cr08_pass = (
after_non_numeric == 0
and changed_qty == 2
and order_items_clean.loc[order_items_clean["order_item_id"] == "OI00071", "quantity"].values[0] == 3
and order_items_clean.loc[order_items_clean["order_item_id"] == "OI00215", "quantity"].values[0] == 1
)
add_cleaning_log(
"CR-08", "order_items", "quantity",
"Menghapus teks 'pcs' dan mengonversi kolom quantity ke integer",
f"Baris non-numerik sebelum={before_non_numeric}",
f"Baris non-numerik sesudah={after_non_numeric}",
changed_qty,
"OI00071 diubah menjadi 3 dan OI00215 diubah menjadi 1.",
"Validated" if cr08_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-09 — Koreksi perhitungan line_amount sesuai formula
# Source Finding: F-10
# ------------------------------------------------------------
before_line_amount = order_items_clean["line_amount"].copy()
expected_calc = (
order_items_clean["quantity"] * order_items_clean["unit_price"]
- order_items_clean["line_discount"]
)
before_mismatch = int(
before_line_amount.ne(expected_calc).sum()
)
# Hitung ulang line_amount berdasarkan formula resmi
order_items_clean["line_amount"] = expected_calc
after_line_amount = order_items_clean["line_amount"]
after_mismatch = int(
after_line_amount.ne(expected_calc).sum()
)
changed_formula = int(
before_line_amount.ne(after_line_amount).sum()
)
cr09_pass = (
after_mismatch == 0
and changed_formula == 2
)
add_cleaning_log(
"CR-09", "order_items", "line_amount",
"Mengoreksi line_amount sesuai formula quantity * unit_price - line_discount",
f"Baris salah hitung sebelum={before_mismatch}",
f"Baris salah hitung sesudah={after_mismatch}",
changed_formula,
"Koreksi dilakukan pada OI00131 (67.000 -> 60.000) dan OI00511 (86.000 -> 91.000).",
"Validated" if cr09_pass else "Needs Review"
)
# ------------------------------------------------------------
# CR-10 — Koreksi subtotal_amount dan total_amount pada O00201
# Source Finding: F-08
# ------------------------------------------------------------
before_subtotal_o201 = orders_clean.loc[
orders_clean["order_id"] == "O00201", "subtotal_amount"
].values[0]
before_total_o201 = orders_clean.loc[
orders_clean["order_id"] == "O00201", "total_amount"
].values[0]
# Hitung subtotal riil dari total item O00201
o201_item_sum = int(
order_items_clean.loc[
order_items_clean["order_id"] == "O00201", "line_amount"
].sum()
)
# Update subtotal_amount dan total_amount agar sesuai dengan item dan payment
orders_clean.loc[orders_clean["order_id"] == "O00201", "subtotal_amount"] = o201_item_sum
orders_clean.loc[orders_clean["order_id"] == "O00201", "total_amount"] = (
o201_item_sum
+ orders_clean.loc[orders_clean["order_id"] == "O00201", "delivery_fee"].values[0]
- orders_clean.loc[orders_clean["order_id"] == "O00201", "discount_amount"].values[0]
)
after_subtotal_o201 = orders_clean.loc[
orders_clean["order_id"] == "O00201", "subtotal_amount"
].values[0]
after_total_o201 = orders_clean.loc[
orders_clean["order_id"] == "O00201", "total_amount"
].values[0]
cr10_pass = (
after_subtotal_o201 == 151000
and after_total_o201 == 159000
)
add_cleaning_log(
"CR-10", "orders", "subtotal_amount, total_amount",
"Menyesuaikan subtotal dan total order O00201 agar cocok dengan rincian item dan pembayaran",
f"O00201 subtotal={before_subtotal_o201}, total={before_total_o201}",
f"O00201 subtotal={after_subtotal_o201}, total={after_total_o201}",
1,
"Koreksi subtotal 161.000 -> 151.000 dan total 169.000 -> 159.000 diverifikasi dari pembayaran P00222 (159.000).",
"Validated" if cr10_pass else "Needs Review"
)
# ------------------------------------------------------------
# BUILD CLEANING LOG
# ------------------------------------------------------------
cleaning_log = pd.DataFrame(
cleaning_log_rows,
columns=CLEANING_LOG_COLUMNS
)
display(cleaning_log)
| log_id | rule_id | dataset | affected_records_scope | action_performed | before_evidence | after_evidence | rows_records_affected | result | notes | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | CL-01 | CR-01 | customers | customer_id C0031 | Mempertahankan satu baris untuk setiap custome... | Baris=159; C0031=2 | Baris=158; C0031=1 | 1 | Validated | Duplikat customer_id sebelum cleaning=1 |
| 1 | CL-02 | CR-02 | customers | customer_segment | Menghapus spasi berlebih dan menyeragamkan kap... | Nilai unik sebelum=5 | Nilai unik sesudah=3 | 2 | Validated | Nilai unik sesudah=['Loyal', 'New', 'Regular'] |
| 2 | CL-03 | CR-03 | orders | order_status | Menghapus spasi berlebih dan menyeragamkan com... | Nilai unik sebelum=3 | Nilai unik sesudah=2 | 2 | Validated | Status selain completed dipertahankan. |
| 3 | CL-04 | CR-04 | order_items | item_category | Menghapus spasi berlebih dan menyeragamkan kat... | Nilai unik sebelum=5 | Nilai unik sesudah=4 | 2 | Validated | Nilai unik sesudah=['Dessert', 'Drink', 'Main'... |
| 4 | CL-05 | CR-05 | restaurants | price_tier | Menghapus spasi berlebih dan menyeragamkan kat... | Nilai unik sebelum=5 | Nilai unik sesudah=3 | 2 | Validated | Nilai unik sesudah=['Budget', 'Mid', 'Premium'... |
| 5 | CL-06 | CR-06 | customers | signup_date | Mengonversi tanggal valid ke format YYYY-MM-DD | Contoh indeks 21=15/08/2025; indeks 68=Oct 02 ... | Indeks 21=2025-08-15; indeks 68=2025-10-02; ba... | 2 | Validated | Periksa nilai tanggal yang gagal dikonversi se... |
| 6 | CL-07 | CR-07 | payments | payment_method | Menghapus spasi berlebih dan menyeragamkan lab... | Nilai unik sebelum={'bank_transfer': 99, 'cred... | Nilai unik sesudah={'bank_transfer': 99, 'cred... | 2 | Validated | Kolom lain tidak diubah oleh blok CR-07. Pasti... |
| 7 | CL-08 | CR-08 | order_items | quantity | Menghapus teks 'pcs' dan mengonversi kolom qua... | Baris non-numerik sebelum=2 | Baris non-numerik sesudah=0 | 2 | Validated | OI00071 diubah menjadi 3 dan OI00215 diubah me... |
| 8 | CL-09 | CR-09 | order_items | line_amount | Mengoreksi line_amount sesuai formula quantity... | Baris salah hitung sebelum=2 | Baris salah hitung sesudah=0 | 2 | Validated | Koreksi dilakukan pada OI00131 (67.000 -> 60.0... |
| 9 | CL-10 | CR-10 | orders | subtotal_amount, total_amount | Menyesuaikan subtotal dan total order O00201 a... | O00201 subtotal=161000, total=169000 | O00201 subtotal=151000, total=159000 | 1 | Validated | Koreksi subtotal 161.000 -> 151.000 dan total ... |
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 — Required columns tersedia
required_columns = {
"Customers": [
"customer_id", "customer_segment", "signup_date"
],
"Orders": [
"order_id", "customer_id", "restaurant_id",
"order_status", "order_timestamp"
],
"Order Items": [
"order_item_id", "order_id", "line_number",
"quantity", "unit_price", "line_discount", "line_amount"
],
"Payments": [
"payment_id", "order_id", "payment_method",
"payment_status", "payment_timestamp", "attempt_number"
],
"Restaurants": [
"restaurant_id", "price_tier", "onboarding_date"
],
}
# Perbaikan: periksa kolom pada tabel yang sesuai
missing_columns = {
name: [
col for col in cols
if col not in clean_tables[name].columns
]
for name, cols in required_columns.items()
}
missing_columns = {
name: cols
for name, cols in missing_columns.items()
if cols
}
passed = len(missing_columns) == 0
record_check(
"V-01", "Schema", "All clean tables",
"Apakah seluruh kolom wajib tersedia?",
"Tidak ada kolom wajib yang hilang",
{"missing_columns": missing_columns},
passed,
)
# V-02 — Customer key
result = key_check(customers_clean, "customer_id", "Customers")
passed = bool(
result.loc[0, "candidate_key_unique"]
) and customers_clean["customer_id"].notna().all()
record_check(
"V-02", "Key", "Customers",
"Apakah customer_id unik dan tidak null?",
"Tidak ada null key dan customer_id unik",
result.to_dict("records")[0],
passed,
)
# V-03 — Order key
result = key_check(orders_clean, "order_id", "Orders")
passed = bool(
result.loc[0, "candidate_key_unique"]
) and orders_clean["order_id"].notna().all()
record_check(
"V-03", "Key", "Orders",
"Apakah order_id unik dan tidak null?",
"Tidak ada null key dan order_id unik",
result.to_dict("records")[0],
passed,
)
# V-04 — Order Items key
result = key_check(
order_items_clean, "order_item_id", "Order Items"
)
composite_unique = not order_items_clean.duplicated(
subset=["order_id", "line_number"]
).any()
composite_not_null = order_items_clean[
["order_id", "line_number"]
].notna().all().all()
passed = (
bool(result.loc[0, "candidate_key_unique"])
and order_items_clean["order_item_id"].notna().all()
and composite_unique
and composite_not_null
)
record_check(
"V-04", "Key", "Order Items",
"Apakah order_item_id valid dan kombinasi order_id + line_number unik?",
"Key tidak null; key tunggal dan kombinasi key sesuai grain",
{
"key_check": result.to_dict("records")[0],
"order_line_combination_unique": bool(composite_unique),
"order_line_combination_not_null": bool(composite_not_null),
},
passed,
)
# V-05 — Payment key
result = key_check(payments_clean, "payment_id", "Payments")
passed = bool(
result.loc[0, "candidate_key_unique"]
) and payments_clean["payment_id"].notna().all()
record_check(
"V-05", "Key", "Payments",
"Apakah payment_id unik dan tidak null?",
"Tidak ada null key dan payment_id unik; order_id boleh berulang",
result.to_dict("records")[0],
passed,
)
else:
print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes: Customers (158, 7) Orders (330, 10) Order Items (872, 9) Payments (374, 7) Restaurants (28, 7)
Gunakan area ini untuk `V-06` sampai `V-14` sesuai Working Paper.
Manfaatkan helper yang sudah tersedia seperti `category_review()`, `fk_check()`, dan perhitungan formula. **Hitung ulang dari clean data**, bukan hanya melihat output raw.
if DATA_READY:
# ========================================================
# EDIT AREA 3B — V-06 s.d. V-14
# Seluruh pemeriksaan menggunakan clean data
# ========================================================
# V-06 — Customers: kategori customer_segment
allowed_segments = {"New", "Regular", "Loyal"}
actual_segments = set(
customers_clean["customer_segment"].dropna().astype(str).unique()
)
customer_category_ok = (
customers_clean["customer_segment"].notna().all()
and customers_clean["customer_segment"].astype(str).str.strip().eq(
customers_clean["customer_segment"].astype(str)
).all()
and actual_segments.issubset(allowed_segments)
)
record_check(
"V-06", "Value", "Customers",
"Apakah kategori customer_segment konsisten?",
"Tidak ada null dan hanya kategori New, Regular, Loyal",
{
"categories": sorted(actual_segments),
"null_count": int(customers_clean["customer_segment"].isna().sum())
},
customer_category_ok,
related_rule_id="CR-02"
)
# V-07 — Orders: kategori order_status
allowed_statuses = {"completed", "cancelled"}
actual_statuses = set(
orders_clean["order_status"].dropna().astype(str).unique()
)
order_category_ok = (
orders_clean["order_status"].notna().all()
and orders_clean["order_status"].astype(str).str.strip().eq(
orders_clean["order_status"].astype(str)
).all()
and actual_statuses.issubset(allowed_statuses)
)
record_check(
"V-07", "Value", "Orders",
"Apakah kategori order_status konsisten?",
"Tidak ada null dan hanya status completed atau cancelled",
{
"categories": sorted(actual_statuses),
"null_count": int(orders_clean["order_status"].isna().sum())
},
order_category_ok,
related_rule_id="CR-03"
)
# V-08 — Payments dan Restaurants: periksa representasi kategori
category_columns = {
"payment_method": payments_clean["payment_method"],
"payment_status": payments_clean["payment_status"],
"price_tier": restaurants_clean["price_tier"],
}
category_evidence = {}
category_representation_ok = True
for col, series in category_columns.items():
values = series.dropna().astype(str)
category_evidence[col] = {
"categories": sorted(values.unique().tolist()),
"null_count": int(series.isna().sum()),
"blank_count": int(values.str.strip().eq("").sum()),
"whitespace_issue_count": int(
values.ne(values.str.strip()).sum()
),
}
if (
series.isna().any()
or values.str.strip().eq("").any()
or values.ne(values.str.strip()).any()
):
category_representation_ok = False
record_check(
"V-08", "Value", "Payments / Restaurants",
"Apakah representasi kategori konsisten dan tidak kosong?",
"Kategori tidak null, tidak kosong, dan tidak memiliki spasi tepi",
category_evidence,
category_representation_ok,
related_rule_id="CR-05, CR-07"
)
# V-09 — Order Items: quantity × unit_price - line_discount
item_check = order_items_clean.copy()
item_check["quantity_numeric"] = pd.to_numeric(
item_check["quantity"].astype(str).str.strip(),
errors="coerce"
)
item_check["unit_price_numeric"] = pd.to_numeric(
item_check["unit_price"], errors="coerce"
)
item_check["line_discount_numeric"] = pd.to_numeric(
item_check["line_discount"], errors="coerce"
)
item_check["line_amount_numeric"] = pd.to_numeric(
item_check["line_amount"], errors="coerce"
)
item_check["expected_line_amount"] = (
item_check["quantity_numeric"]
* item_check["unit_price_numeric"]
- item_check["line_discount_numeric"]
)
item_check["formula_unverifiable"] = item_check[
[
"quantity_numeric",
"unit_price_numeric",
"line_discount_numeric",
"line_amount_numeric",
]
].isna().any(axis=1)
item_check["formula_match"] = (
item_check["line_amount_numeric"]
.eq(item_check["expected_line_amount"])
.fillna(False)
)
item_unverifiable = int(item_check["formula_unverifiable"].sum())
item_mismatch = int(
(
~item_check["formula_match"]
& ~item_check["formula_unverifiable"]
).sum()
)
item_formula_evidence = {
"total_rows": len(item_check),
"confirmed_mismatch_count": item_mismatch,
"unverifiable_count": item_unverifiable,
"mismatch_ids": item_check.loc[
~item_check["formula_match"]
& ~item_check["formula_unverifiable"],
"order_item_id"
].tolist(),
"unverifiable_ids": item_check.loc[
item_check["formula_unverifiable"],
"order_item_id"
].tolist(),
}
record_check(
"V-09", "Business Rule", "Order Items",
"Apakah line_amount = quantity × unit_price - line_discount?",
"Semua baris dapat diverifikasi dan seluruh formula sesuai",
item_formula_evidence,
item_mismatch == 0 and item_unverifiable == 0,
related_rule_id="F-10",
notes="Baris yang belum dapat diverifikasi tidak dianggap valid."
)
# V-10 — Orders: subtotal + delivery_fee - discount_amount
order_check = orders_clean.copy()
order_check["expected_total"] = (
pd.to_numeric(order_check["subtotal_amount"], errors="coerce")
+ pd.to_numeric(order_check["delivery_fee"], errors="coerce")
- pd.to_numeric(order_check["discount_amount"], errors="coerce")
)
order_check["total_numeric"] = pd.to_numeric(
order_check["total_amount"], errors="coerce"
)
order_check["total_unverifiable"] = order_check[
["expected_total", "total_numeric"]
].isna().any(axis=1)
order_check["total_match"] = (
order_check["total_numeric"]
.eq(order_check["expected_total"])
.fillna(False)
)
total_unverifiable = int(order_check["total_unverifiable"].sum())
total_mismatch = int(
(
~order_check["total_match"]
& ~order_check["total_unverifiable"]
).sum()
)
total_evidence = {
"total_rows": len(order_check),
"confirmed_mismatch_count": total_mismatch,
"unverifiable_count": total_unverifiable,
"mismatch_ids": order_check.loc[
~order_check["total_match"]
& ~order_check["total_unverifiable"],
"order_id"
].tolist(),
}
record_check(
"V-10", "Business Rule", "Orders",
"Apakah total_amount = subtotal_amount + delivery_fee - discount_amount?",
"Semua baris dapat diverifikasi dan seluruh formula sesuai",
total_evidence,
total_mismatch == 0 and total_unverifiable == 0
)
# V-11 — Rekonsiliasi Orders dengan Order Items
item_amounts = pd.to_numeric(
order_items_clean["line_amount"], errors="coerce"
)
item_sums = (
order_items_clean.assign(_line_amount=item_amounts)
.groupby("order_id", dropna=False)["_line_amount"]
.sum(min_count=1)
.rename("sum_line_amount")
.reset_index()
)
recon = orders_clean[
["order_id", "subtotal_amount"]
].merge(item_sums, on="order_id", how="left")
recon["subtotal_numeric"] = pd.to_numeric(
recon["subtotal_amount"], errors="coerce"
)
recon["reconcile_unverifiable"] = recon[
["subtotal_numeric", "sum_line_amount"]
].isna().any(axis=1)
recon["reconcile_match"] = recon["subtotal_numeric"].eq(
recon["sum_line_amount"]
).fillna(False)
recon_unverifiable = int(recon["reconcile_unverifiable"].sum())
recon_mismatch = int(
(
~recon["reconcile_match"]
& ~recon["reconcile_unverifiable"]
).sum()
)
recon_evidence = {
"orders_checked": len(recon),
"confirmed_mismatch_count": recon_mismatch,
"unverifiable_count": recon_unverifiable,
"mismatch_order_ids": recon.loc[
~recon["reconcile_match"]
& ~recon["reconcile_unverifiable"],
"order_id"
].tolist(),
}
record_check(
"V-11", "Cross-table", "Orders ↔ Order Items",
"Apakah SUM(line_amount) = subtotal_amount per order?",
"Semua order dapat direkonsiliasi tanpa selisih",
recon_evidence,
recon_mismatch == 0 and recon_unverifiable == 0,
related_rule_id="F-08"
)
# V-12 — Orders → Customers
fk_customer = fk_check(
orders_clean, customers_clean,
"customer_id", "customer_id",
"Orders → Customers"
)
record_check(
"V-12", "Cross-table", "Orders → Customers",
"Apakah seluruh customer_id pada Orders ditemukan di Customers?",
"Unmatched customer = 0",
fk_customer,
fk_customer["unmatched_count"] == 0
)
# V-13 — Orders → Restaurants
fk_restaurant = fk_check(
orders_clean, restaurants_clean,
"restaurant_id", "restaurant_id",
"Orders → Restaurants"
)
record_check(
"V-13", "Cross-table", "Orders → Restaurants",
"Apakah seluruh restaurant_id pada Orders ditemukan di Restaurants?",
"Unmatched restaurant = 0",
fk_restaurant,
fk_restaurant["unmatched_count"] == 0
)
# V-14 — Order Items / Payments → Orders
fk_items = fk_check(
order_items_clean, orders_clean,
"order_id", "order_id",
"Order Items → Orders"
)
fk_payments = fk_check(
payments_clean, orders_clean,
"order_id", "order_id",
"Payments → Orders"
)
fk_combined = {
"order_items_to_orders": fk_items,
"payments_to_orders": fk_payments,
}
record_check(
"V-14", "Cross-table", "Order Items / Payments → Orders",
"Apakah tidak ada referensi order yang orphan?",
"Unmatched order reference = 0 pada kedua relasi",
fk_combined,
(
fk_items["unmatched_count"] == 0
and fk_payments["unmatched_count"] == 0
)
)
print("VALIDATION V-06 s.d. V-14 SELESAI")
display(pd.DataFrame(validation_results).sort_values("check_id"))
else:
print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
VALIDATION V-06 s.d. V-14 SELESAI
| check_id | validation_level | dataset_relationship | validation_question | expected_criteria | actual_result_metric | status | related_rule_id | notes | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | V-01 | Schema | All clean tables | Apakah seluruh kolom wajib tersedia? | Tidak ada kolom wajib yang hilang | {'missing_columns': {}} | PASS | ||
| 1 | V-02 | Key | Customers | Apakah customer_id unik dan tidak null? | Tidak ada null key dan customer_id unik | {'dataset': 'Customers', 'key_cols': 'customer... | PASS | ||
| 2 | V-03 | Key | Orders | Apakah order_id unik dan tidak null? | Tidak ada null key dan order_id unik | {'dataset': 'Orders', 'key_cols': 'order_id', ... | PASS | ||
| 3 | V-04 | Key | Order Items | Apakah order_item_id valid dan kombinasi order... | Key tidak null; key tunggal dan kombinasi key ... | {'key_check': {'dataset': 'Order Items', 'key_... | PASS | ||
| 4 | V-05 | Key | Payments | Apakah payment_id unik dan tidak null? | Tidak ada null key dan payment_id unik; order_... | {'dataset': 'Payments', 'key_cols': 'payment_i... | PASS | ||
| 5 | V-06 | Value | Customers | Apakah kategori customer_segment konsisten? | Tidak ada null dan hanya kategori New, Regular... | {'categories': ['Loyal', 'New', 'Regular'], 'n... | PASS | CR-02 | |
| 6 | V-07 | Value | Orders | Apakah kategori order_status konsisten? | Tidak ada null dan hanya status completed atau... | {'categories': ['cancelled', 'completed'], 'nu... | PASS | CR-03 | |
| 7 | V-08 | Value | Payments / Restaurants | Apakah representasi kategori konsisten dan tid... | Kategori tidak null, tidak kosong, dan tidak m... | {'payment_method': {'categories': ['bank_trans... | PASS | CR-05, CR-07 | |
| 8 | V-09 | Business Rule | Order Items | Apakah line_amount = quantity × unit_price - l... | Semua baris dapat diverifikasi dan seluruh for... | {'total_rows': 872, 'confirmed_mismatch_count'... | PASS | F-10 | Baris yang belum dapat diverifikasi tidak dian... |
| 9 | V-10 | Business Rule | Orders | Apakah total_amount = subtotal_amount + delive... | Semua baris dapat diverifikasi dan seluruh for... | {'total_rows': 330, 'confirmed_mismatch_count'... | PASS | ||
| 10 | V-11 | Cross-table | Orders ↔ Order Items | Apakah SUM(line_amount) = subtotal_amount per ... | Semua order dapat direkonsiliasi tanpa selisih | {'orders_checked': 330, 'confirmed_mismatch_co... | PASS | F-08 | |
| 11 | V-12 | Cross-table | Orders → Customers | Apakah seluruh customer_id pada Orders ditemuk... | Unmatched customer = 0 | {'relationship': 'Orders → Customers', 'child_... | PASS | ||
| 12 | V-13 | Cross-table | Orders → Restaurants | Apakah seluruh restaurant_id pada Orders ditem... | Unmatched restaurant = 0 | {'relationship': 'Orders → Restaurants', 'chil... | PASS | ||
| 13 | V-14 | Cross-table | Order Items / Payments → Orders | Apakah tidak ada referensi order yang orphan? | Unmatched order reference = 0 pada kedua relasi | {'order_items_to_orders': {'relationship': 'Or... | PASS |
Gunakan `V-15` untuk membuktikan bahwa finding yang kamu putuskan **KEEP** tidak ikut berubah akibat over-cleaning.
Jika kamu memiliki lebih dari satu KEEP finding, gunakan evidence yang cukup di Working Paper lalu rangkum hasilnya ke `V-15`.
# ============================================================
# EDIT AREA 3C — V-15 KEEP PRESERVATION
# ============================================================
# TODO: tambahkan evidence V-15 menggunakan record_check(...).
if DATA_READY:
# ========================================================
# EDIT AREA 3C — V-15 KEEP Preservation Check
# ========================================================
keep_evidence = {}
# KEEP F-09: order_timestamp tetap dapat diparse
raw_order_ts = pd.to_datetime(
orders_raw["order_timestamp"],
format="%Y-%m-%d %H:%M:%S",
errors="coerce"
)
clean_order_ts = pd.to_datetime(
orders_clean["order_timestamp"],
format="%Y-%m-%d %H:%M:%S",
errors="coerce"
)
timestamp_preserved = (
len(orders_raw) == len(orders_clean)
and raw_order_ts.notna().all()
and clean_order_ts.notna().all()
and raw_order_ts.reset_index(drop=True).equals(
clean_order_ts.reset_index(drop=True)
)
)
keep_evidence["F-09_order_timestamp"] = {
"raw_rows": len(orders_raw),
"clean_rows": len(orders_clean),
"raw_parse_failures": int(raw_order_ts.isna().sum()),
"clean_parse_failures": int(clean_order_ts.isna().sum()),
"timestamp_values_preserved": timestamp_preserved
}
# KEEP: pengulangan order_id pada Payments dapat sah
# karena satu order bisa memiliki beberapa payment attempts.
payment_order_repeats_raw = int(
payments_raw["order_id"].duplicated().sum()
)
payment_order_repeats_clean = int(
payments_clean["order_id"].duplicated().sum()
)
payment_repeat_preserved = (
payment_order_repeats_raw == payment_order_repeats_clean
and len(payments_raw) == len(payments_clean)
)
keep_evidence["Payments_repeated_order_id"] = {
"raw_rows": len(payments_raw),
"clean_rows": len(payments_clean),
"raw_repeated_order_id_rows": payment_order_repeats_raw,
"clean_repeated_order_id_rows": payment_order_repeats_clean,
"repeat_pattern_preserved": payment_repeat_preserved
}
v15_pass = (
timestamp_preserved
and payment_repeat_preserved
)
record_check(
"V-15",
"KEEP Preservation",
"Orders / Payments",
"Apakah temuan KEEP yang diperiksa tetap terjaga setelah cleaning?",
"Timestamp yang valid tetap terjaga dan pengulangan order_id pada Payments tidak dihapus secara keliru",
keep_evidence,
v15_pass,
related_rule_id="F-09",
notes="Pemeriksaan ini mencakup dua pola KEEP yang disebutkan dalam evidence."
)
print("KEEP PRESERVATION EVIDENCE")
display(pd.DataFrame([
{"finding": key, **value}
for key, value in keep_evidence.items()
]))
print("V-15 STATUS:", "PASS" if v15_pass else "FAIL")
else:
print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
KEEP PRESERVATION EVIDENCE
| finding | raw_rows | clean_rows | raw_parse_failures | clean_parse_failures | timestamp_values_preserved | raw_repeated_order_id_rows | clean_repeated_order_id_rows | repeat_pattern_preserved | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | F-09_order_timestamp | 330 | 330 | 0.0 | 0.0 | True | NaN | NaN | NaN |
| 1 | Payments_repeated_order_id | 374 | 374 | NaN | NaN | NaN | 44.0 | 44.0 | True |
V-15 STATUS: PASS
validation_df = pd.DataFrame(validation_results)
if validation_df.empty:
print("VALIDATION GATE: PENDING")
print("→ Belum ada validation evidence yang dicatat.")
else:
validation_df = validation_df.sort_values("check_id").reset_index(drop=True)
display(validation_df)
present_ids = set(validation_df["check_id"])
missing_validation_ids = [
x for x in REQUIRED_VALIDATION_IDS if x not in present_ids
]
print(validation_df["status"].value_counts(dropna=False))
if missing_validation_ids:
print("VALIDATION GATE: PENDING")
print("Missing required checks:", ", ".join(missing_validation_ids))
elif (validation_df["status"] == "FAIL").any():
print("VALIDATION GATE: FAIL")
print("→ Review FAIL sebelum finalisasi.")
else:
print("VALIDATION GATE: PASS")
| check_id | validation_level | dataset_relationship | validation_question | expected_criteria | actual_result_metric | status | related_rule_id | notes | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | V-01 | Schema | All clean tables | Apakah seluruh kolom wajib tersedia? | Tidak ada kolom wajib yang hilang | {'missing_columns': {}} | PASS | ||
| 1 | V-02 | Key | Customers | Apakah customer_id unik dan tidak null? | Tidak ada null key dan customer_id unik | {'dataset': 'Customers', 'key_cols': 'customer... | PASS | ||
| 2 | V-03 | Key | Orders | Apakah order_id unik dan tidak null? | Tidak ada null key dan order_id unik | {'dataset': 'Orders', 'key_cols': 'order_id', ... | PASS | ||
| 3 | V-04 | Key | Order Items | Apakah order_item_id valid dan kombinasi order... | Key tidak null; key tunggal dan kombinasi key ... | {'key_check': {'dataset': 'Order Items', 'key_... | PASS | ||
| 4 | V-05 | Key | Payments | Apakah payment_id unik dan tidak null? | Tidak ada null key dan payment_id unik; order_... | {'dataset': 'Payments', 'key_cols': 'payment_i... | PASS | ||
| 5 | V-06 | Value | Customers | Apakah kategori customer_segment konsisten? | Tidak ada null dan hanya kategori New, Regular... | {'categories': ['Loyal', 'New', 'Regular'], 'n... | PASS | CR-02 | |
| 6 | V-07 | Value | Orders | Apakah kategori order_status konsisten? | Tidak ada null dan hanya status completed atau... | {'categories': ['cancelled', 'completed'], 'nu... | PASS | CR-03 | |
| 7 | V-08 | Value | Payments / Restaurants | Apakah representasi kategori konsisten dan tid... | Kategori tidak null, tidak kosong, dan tidak m... | {'payment_method': {'categories': ['bank_trans... | PASS | CR-05, CR-07 | |
| 8 | V-09 | Business Rule | Order Items | Apakah line_amount = quantity × unit_price - l... | Semua baris dapat diverifikasi dan seluruh for... | {'total_rows': 872, 'confirmed_mismatch_count'... | PASS | F-10 | Baris yang belum dapat diverifikasi tidak dian... |
| 9 | V-10 | Business Rule | Orders | Apakah total_amount = subtotal_amount + delive... | Semua baris dapat diverifikasi dan seluruh for... | {'total_rows': 330, 'confirmed_mismatch_count'... | PASS | ||
| 10 | V-11 | Cross-table | Orders ↔ Order Items | Apakah SUM(line_amount) = subtotal_amount per ... | Semua order dapat direkonsiliasi tanpa selisih | {'orders_checked': 330, 'confirmed_mismatch_co... | PASS | F-08 | |
| 11 | V-12 | Cross-table | Orders → Customers | Apakah seluruh customer_id pada Orders ditemuk... | Unmatched customer = 0 | {'relationship': 'Orders → Customers', 'child_... | PASS | ||
| 12 | V-13 | Cross-table | Orders → Restaurants | Apakah seluruh restaurant_id pada Orders ditem... | Unmatched restaurant = 0 | {'relationship': 'Orders → Restaurants', 'chil... | PASS | ||
| 13 | V-14 | Cross-table | Order Items / Payments → Orders | Apakah tidak ada referensi order yang orphan? | Unmatched order reference = 0 pada kedua relasi | {'order_items_to_orders': {'relationship': 'Or... | PASS | ||
| 14 | V-15 | KEEP Preservation | Orders / Payments | Apakah temuan KEEP yang diperiksa tetap terjag... | Timestamp yang valid tetap terjaga dan pengula... | {'F-09_order_timestamp': {'raw_rows': 330, 'cl... | PASS | F-09 | Pemeriksaan ini mencakup dua pola KEEP yang di... |
status PASS 15 Name: count, dtype: int64 VALIDATION GATE: PASS
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 = (
"Dataset dinyatakan READY (PASS) untuk analisis lanjutan berdasarkan bukti validasi berikut:\n"
"1. Schema & Key (V-01 s.d. V-05): Seluruh kolom wajib tersedia lengkap. Primary key (customer_id, order_id, "
"order_item_id, payment_id) dan composite key terbukti unik 100% tanpa nilai null.\n"
"2. Standardisasi Nilai (V-06 s.d. V-08): Pembersihan pada customer_segment (CR-02), order_status (CR-03), "
"item_category (CR-04), price_tier (CR-05), dan payment_method (CR-07) berhasil menghilangkan spasi berlebih "
"dan menyeragamkan nilai kanonikal tanpa anomali representasi.\n"
"3. Format & Aturan Bisnis (V-09 s.d. V-11): Kolom quantity berhasil dikonversi ke numerik (CR-08), formula line_amount "
"terkoreksi presisi (CR-09), serta rekonsiliasi subtotal_amount dan total_amount pada order O00201 cocok sempurna "
"dengan detail item dan bukti pembayaran P00222 (CR-10).\n"
"4. Integritas Relasional (V-12 s.d. V-14): Seluruh Foreign Key valid tanpa referensi orphan pada relasi Orders ke Customers, "
"Orders ke Restaurants, maupun Items/Payments ke Orders.\n"
"5. Preservasi KEEP & Raw Integrity (V-15 & Raw Integrity): Nilai sah pada order_timestamp dan pola multiple payment "
"tetap terjaga (tanpa over-cleaning), serta seluruh file sumber raw tidak mengalami perubahan (hash SHA-256 identik)."
)
print(FINAL_JUDGMENT_RATIONALE)
PM1 DATA READINESS GATE: PASS Dataset dinyatakan READY (PASS) untuk analisis lanjutan berdasarkan bukti validasi berikut: 1. Schema & Key (V-01 s.d. V-05): Seluruh kolom wajib tersedia lengkap. Primary key (customer_id, order_id, order_item_id, payment_id) dan composite key terbukti unik 100% tanpa nilai null. 2. Standardisasi Nilai (V-06 s.d. V-08): Pembersihan pada customer_segment (CR-02), order_status (CR-03), item_category (CR-04), price_tier (CR-05), dan payment_method (CR-07) berhasil menghilangkan spasi berlebih dan menyeragamkan nilai kanonikal tanpa anomali representasi. 3. Format & Aturan Bisnis (V-09 s.d. V-11): Kolom quantity berhasil dikonversi ke numerik (CR-08), formula line_amount terkoreksi presisi (CR-09), serta rekonsiliasi subtotal_amount dan total_amount pada order O00201 cocok sempurna dengan detail item dan bukti pembayaran P00222 (CR-10). 4. Integritas Relasional (V-12 s.d. V-14): Seluruh Foreign Key valid tanpa referensi orphan pada relasi Orders ke Customers, Orders ke Restaurants, maupun Items/Payments ke Orders. 5. Preservasi KEEP & Raw Integrity (V-15 & Raw Integrity): Nilai sah pada order_timestamp dan pola multiple payment tetap terjaga (tanpa over-cleaning), serta seluruh file sumber raw tidak mengalami perubahan (hash SHA-256 identik).
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()
Evidence boleh diekspor untuk review, tetapi **clean package final tetap hanya boleh disimpan saat FINAL_GATE: PASS**.
def export_evidence():
findings.to_csv(OUTPUT_DIR / "pm1_findings.csv", index=False)
cleaning_rules.to_csv(OUTPUT_DIR / "pm1_cleaning_rulebook.csv", index=False)
cleaning_log.to_csv(OUTPUT_DIR / "pm1_cleaning_log.csv", index=False)
validation_df.to_csv(OUTPUT_DIR / "pm1_validation_results.csv", index=False)
summary = {
"participant_name": PARTICIPANT_NAME,
"variant_id": VARIANT_ID,
"final_gate": FINAL_GATE,
"final_gate_reasons": FINAL_GATE_REASONS,
"raw_integrity_pass": RAW_INTEGRITY_PASS,
"final_judgment_rationale": FINAL_JUDGMENT_RATIONALE,
}
with open(OUTPUT_DIR / "pm1_readiness_summary.json", "w", encoding="utf-8") as f:
json.dump(summary, f, ensure_ascii=False, indent=2)
print("EVIDENCE EXPORT: PASS")
print("Saved to:", OUTPUT_DIR)
return True
# Hapus tanda # pada baris berikut setelah evidence sudah diisi:
export_evidence()
EVIDENCE EXPORT: PASS Saved to: C:\Users\User\Downloads\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\outputs\pm1_swiftbite
True
### 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
```