# 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 = "Annisa"
VARIANT_ID = "SB-P20" # contoh: SB-P01
name_ok = PARTICIPANT_NAME.strip() not in {"", "Annisa"}
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 : Annisa Variant ID : SB-P20
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.
import pandas as pd
from IPython.display import display
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\annis\OneDrive\Documents\PLAN DATA ANALYST\Bootcamp_Data_Analyst RAW_DIR : C:\Users\annis\OneDrive\Documents\PLAN DATA ANALYST\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite PROCESSED_DIR : C:\Users\annis\OneDrive\Documents\PLAN DATA ANALYST\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite OUTPUT_DIR : C:\Users\annis\OneDrive\Documents\PLAN DATA ANALYST\Bootcamp_Data_Analyst\outputs\pm1_swiftbite DOCS_DIR : C:\Users\annis\OneDrive\Documents\PLAN DATA ANALYST\Bootcamp_Data_Analyst\docs\pm1_swiftbite
Tujuan cell ini hanya memastikan library utama tersedia. Jika DuckDB belum aktif, workflow utama starter tetap dapat berjalan dengan pandas.
print("pandas :", pd.__version__)
try:
import openpyxl
EXCEL_ENGINE_AVAILABLE = True
print("openpyxl:", openpyxl.__version__)
except ImportError:
EXCEL_ENGINE_AVAILABLE = False
print("openpyxl: NOT AVAILABLE")
if DUCKDB_AVAILABLE:
print("DuckDB :", duckdb.__version__)
try:
con = duckdb.connect()
print("DuckDB connection: OK")
except Exception as e:
print("DuckDB connection: WARNING —", type(e).__name__, str(e))
else:
print("DuckDB : NOT AVAILABLE")
print("INFO: workflow utama notebook tetap dapat dijalankan dengan pandas.")
if not EXCEL_ENGINE_AVAILABLE:
print("ENVIRONMENT CHECK: NEEDS FIX")
print("→ File XLSX membutuhkan openpyxl. Aktifkan environment sesuai setup Sesi 1.")
else:
print("ENVIRONMENT CHECK: PASS")
pandas : 3.0.6 openpyxl: 3.1.5 DuckDB : 1.5.6 DuckDB connection: OK ENVIRONMENT CHECK: PASS
Letakkan **5 file berikut langsung** di:
`data/raw/pm1_swiftbite/`
- `swiftbite_customers.json`
- `swiftbite_orders.xlsx`
- `swiftbite_order_items.csv`
- `swiftbite_payments.txt`
- `swiftbite_restaurants.csv`
**Jangan** meletakkan ZIP di folder tersebut dan **jangan** membuat folder `SB-Pxx` lagi di dalam `pm1_swiftbite/`.
EXPECTED_FILES = {
"customers": "swiftbite_customers.json",
"orders": "swiftbite_orders.xlsx",
"order_items": "swiftbite_order_items.csv",
"payments": "swiftbite_payments.txt",
"restaurants": "swiftbite_restaurants.csv",
}
source_status = []
if not TECHNICAL_GATE_PASS:
missing_files = list(EXPECTED_FILES.values())
SOURCE_ACCESS_PASS = False
print("SOURCE ACCESS CHECK: SKIPPED")
print("→ Perbaiki TECHNICAL GATE terlebih dahulu.")
else:
for logical_name, filename in EXPECTED_FILES.items():
path = RAW_DIR / filename
source_status.append({
"logical_name": logical_name,
"filename": filename,
"exists": path.is_file(),
"path": str(path),
})
source_status_df = pd.DataFrame(source_status)
print(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\annis\OneDrive\Documents\PLAN DATA AN... 1 orders swiftbite_orders.xlsx True C:\Users\annis\OneDrive\Documents\PLAN DATA AN... 2 order_items swiftbite_order_items.csv True C:\Users\annis\OneDrive\Documents\PLAN DATA AN... 3 payments swiftbite_payments.txt True C:\Users\annis\OneDrive\Documents\PLAN DATA AN... 4 restaurants swiftbite_restaurants.csv True C:\Users\annis\OneDrive\Documents\PLAN DATA AN... 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_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"
},
"Restaurants": {
"restaurant_id", "restaurant_name","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 | 6 | 7 | PASS | |
| 1 | Orders | 9 | 10 | PASS | |
| 2 | Order Items | 9 | 9 | PASS | |
| 3 | Payments | 5 | 7 | PASS | |
| 4 | Restaurants | 4 | 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=(177, 7) ===
| customer_id | full_name | phone_number | signup_date | customer_zone | customer_segment | ||
|---|---|---|---|---|---|---|---|
| 0 | C0001 | Putri Wijaya | putriwijaya1@example.com | 081707765519 | 2025-10-02 | Central | Loyal |
| 1 | C0002 | Dimas Permata | dimaspermata2@example.com | 087159059591 | 2025-10-22 | North | New |
| 2 | C0003 | Hana Maulana | hanamaulana3@example.com | 085399261403 | 2025-12-12 | South | Loyal |
=== Orders | shape=(346, 10) ===
| order_id | customer_id | restaurant_id | order_timestamp | order_status | promo_code | subtotal_amount | delivery_fee | discount_amount | total_amount | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | O00001 | C0024 | R0014 | 2026-03-01 13:42:00 | completed | NaN | 124000 | 12000 | 15000 | 121000 |
| 1 | O00002 | C0031 | R0019 | 2026-02-21 23:10:00 | completed | NEWUSER | 76000 | 18000 | 5000 | 89000 |
| 2 | O00003 | C0038 | R0024 | 2026-02-15 02:18:00 | completed | NaN | 77000 | 10000 | 15000 | 72000 |
=== Order Items | shape=(864, 9) ===
| order_item_id | order_id | line_number | item_name | item_category | quantity | unit_price | line_discount | line_amount | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | OI00001 | O00001 | 1 | Mie Special | Main | 2 | 26000 | 0 | 52000 |
| 1 | OI00002 | O00001 | 2 | Cake Slice | Dessert | 3 | 24000 | 0 | 72000 |
| 2 | OI00003 | O00002 | 1 | Iced Tea | Drink | 1 | 9000 | 0 | 9000 |
=== Payments | shape=(398, 7) ===
| payment_id | order_id | payment_method | payment_status | payment_amount | payment_timestamp | attempt_number | |
|---|---|---|---|---|---|---|---|
| 0 | P00001 | O00001 | e_wallet | successful | 121000 | 2026-03-01 13:45:00 | 1 |
| 1 | P00002 | O00002 | bank_transfer | failed | 89000 | 2026-02-21 23:12:00 | 1 |
| 2 | P00003 | O00002 | bank_transfer | successful | 89000 | 2026-02-21 23:15:00 | 2 |
=== Restaurants | shape=(24, 7) ===
| restaurant_id | restaurant_name | cuisine_type | restaurant_zone | price_tier | onboarding_date | active_status | |
|---|---|---|---|---|---|---|---|
| 0 | R0001 | SwiftBite Partner 01 | Fast Food | East | Budget | 2024-05-11 | active |
| 1 | R0002 | SwiftBite Partner 02 | Cafe & Beverage | South | Budget | 2025-02-07 | active |
| 2 | R0003 | SwiftBite Partner 03 | Dessert | East | Mid | 2024-02-26 | 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 | 177 | 7 | [customer_id, full_name, email, phone_number, ... |
| 1 | Orders | 346 | 10 | [order_id, customer_id, restaurant_id, order_t... |
| 2 | Order Items | 864 | 9 | [order_item_id, order_id, line_number, item_na... |
| 3 | Payments | 398 | 7 | [payment_id, order_id, payment_method, payment... |
| 4 | Restaurants | 24 | 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 | 177 | 176 | 0 | 2 | False |
| 1 | Orders | order_id | 346 | 346 | 0 | 0 | True |
| 2 | Order Items | order_item_id | 864 | 864 | 0 | 0 | True |
| 3 | Order Items — candidate business key | order_id, line_number | 864 | 864 | 0 | 0 | True |
| 4 | Payments | payment_id | 398 | 398 | 0 | 0 | True |
| 5 | Restaurants | restaurant_id | 24 | 24 | 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 | |
|---|---|---|
| 308 | O00309 | 4 |
| 30 | O00031 | 4 |
| 313 | O00314 | 4 |
| 37 | O00038 | 4 |
| 304 | O00305 | 4 |
| 27 | O00028 | 4 |
| 309 | O00310 | 4 |
| 112 | O00113 | 4 |
| 136 | O00137 | 4 |
| 193 | O00194 | 4 |
Payments cardinality
| order_id | payment_rows | |
|---|---|---|
| 340 | O00341 | 2 |
| 341 | O00342 | 2 |
| 342 | O00343 | 2 |
| 343 | O00344 | 2 |
| 344 | O00345 | 2 |
| 305 | O00306 | 2 |
| 37 | O00038 | 2 |
| 41 | O00042 | 2 |
| 288 | O00289 | 2 |
| 256 | O00257 | 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 | 177 | 7 | 2 | 1 | 0 |
| 1 | Orders | 346 | 10 | 0 | 0 | 176 |
| 2 | Order Items | 864 | 9 | 0 | 0 | 0 |
| 3 | Payments | 398 | 7 | 0 | 0 | 0 |
| 4 | Restaurants | 24 | 7 | 0 | 0 | 0 |
# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
display(column_profile(customers_raw))
| column | dtype | null_count | null_pct | unique_count | sample_values | |
|---|---|---|---|---|---|---|
| 0 | customer_id | str | 0 | 0.0 | 176 | [C0001, C0002, C0003, C0004, C0005] |
| 1 | full_name | str | 0 | 0.0 | 135 | [Putri Wijaya, Dimas Permata, Hana Maulana, In... |
| 2 | str | 0 | 0.0 | 176 | [putriwijaya1@example.com, dimaspermata2@examp... | |
| 3 | phone_number | str | 0 | 0.0 | 176 | [081707765519, 087159059591, 085399261403, 083... |
| 4 | signup_date | str | 0 | 0.0 | 122 | [2025-10-02, 2025-10-22, 2025-12-12, 2025-10-1... |
| 5 | customer_zone | str | 0 | 0.0 | 5 | [Central, North, South, West, East] |
| 6 | customer_segment | str | 0 | 0.0 | 4 | [Loyal, New, Regular, REGULAR ] |
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.
from IPython import display
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_data = [
# 1. Dataset: Customers
{
"finding_id": "F-01",
"dataset": "Customers",
"field_key": "customer_segment",
"finding_evidence": "Terdapat spasi berlebih pada nilai 'REGULAR '",
"why_it_matters": "Dapat menyebabkan kesalahan grouping saat analisis segmentasi",
"verification_method": "Value counts / category review",
"verification_evidence_result": "Ditemukan nilai 'REGULAR ' dengan spasi di akhir",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Memerlukan penyeragaman casing dan pembersihan spasi (trim)",
"cleaning_rule_id": "CR-01"
},
# 2. Dataset: Orders
{
"finding_id": "F-02",
"dataset": "Orders",
"field_key": "promo_code",
"finding_evidence": "Terdapat nilai missing / NaN pada promo_code",
"why_it_matters": "Missing value perlu diperlakukan secara konsisten",
"verification_method": "isna().sum()",
"verification_evidence_result": "Nilai null ditemukan pada transaksi tanpa promo",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Nilai missing diisi dengan 'NONE'",
"cleaning_rule_id": "CR-02"
},
# 3. Dataset: Order Items
{
"finding_id": "F-03",
"dataset": "Order Items",
"field_key": "item_category",
"finding_evidence": "Format kategori item sudah konsisten",
"why_it_matters": "Memastikan data kategorisasi menu siap dianalisis",
"verification_method": "category_review()",
"verification_evidence_result": "Semua kategori bernilai valid",
"judgment": "KEEP",
"reason_for_judgment": "Data legitimate dan tidak memerlukan perubahan",
"cleaning_rule_id": "CR-03"
},
# 4. Dataset: Payments
{
"finding_id": "F-04",
"dataset": "Payments",
"field_key": "payment_status",
"finding_evidence": "Format penulisan status tidak konsisten (lowercase / uppercase)",
"why_it_matters": "Mempengaruhi hasil filter dan agregasi pembayaran",
"verification_method": "category_review()",
"verification_evidence_result": "Terdapat variasi penulisan status",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Format perlu disatukan menjadi lowercase",
"cleaning_rule_id": "CR-04"
},
# 5. Dataset: Restaurants
{
"finding_id": "F-05",
"dataset": "Restaurants",
"field_key": "price_tier",
"finding_evidence": "Nilai price tier restoran sudah sesuai kategori",
"why_it_matters": "Memastikan tingkat harga restoran valid untuk segmen kemitraan",
"verification_method": "category_review()",
"verification_evidence_result": "Semua tier harga valid",
"judgment": "KEEP",
"reason_for_judgment": "Data valid dan tidak memerlukan aksi cleaning",
"cleaning_rule_id": "CR-05"
}
]
from IPython.display import display
findings = pd.DataFrame(findings_data, columns=FINDING_COLUMNS)
display(findings)
| finding_id | dataset | field_key | finding_evidence | why_it_matters | verification_method | verification_evidence_result | judgment | reason_for_judgment | cleaning_rule_id | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | F-01 | Customers | customer_segment | Terdapat spasi berlebih pada nilai 'REGULAR ' | Dapat menyebabkan kesalahan grouping saat anal... | Value counts / category review | Ditemukan nilai 'REGULAR ' dengan spasi di akhir | STANDARDIZE | Memerlukan penyeragaman casing dan pembersihan... | CR-01 |
| 1 | F-02 | Orders | promo_code | Terdapat nilai missing / NaN pada promo_code | Missing value perlu diperlakukan secara konsisten | isna().sum() | Nilai null ditemukan pada transaksi tanpa promo | STANDARDIZE | Nilai missing diisi dengan 'NONE' | CR-02 |
| 2 | F-03 | Order Items | item_category | Format kategori item sudah konsisten | Memastikan data kategorisasi menu siap dianalisis | category_review() | Semua kategori bernilai valid | KEEP | Data legitimate dan tidak memerlukan perubahan | CR-03 |
| 3 | F-04 | Payments | payment_status | Format penulisan status tidak konsisten (lower... | Mempengaruhi hasil filter dan agregasi pembayaran | category_review() | Terdapat variasi penulisan status | STANDARDIZE | Format perlu disatukan menjadi lowercase | CR-04 |
| 4 | F-05 | Restaurants | price_tier | Nilai price tier restoran sudah sesuai kategori | Memastikan tingkat harga restoran valid untuk ... | category_review() | Semua tier harga valid | KEEP | Data valid dan tidak memerlukan aksi cleaning | CR-05 |
```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 | 176 | 0 | [] |
| 1 | Orders → Restaurants | 24 | 0 | [] |
| 2 | Order Items → Orders | 346 | 0 | [] |
| 3 | Payments → Orders | 346 | 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 | 2 | 26000 | 0 | 52000 | 52000.0 | True |
| 1 | OI00002 | O00001 | 3 | 24000 | 0 | 72000 | 72000.0 | True |
| 2 | OI00003 | O00002 | 1 | 9000 | 0 | 9000 | 9000.0 | True |
| 3 | OI00004 | O00002 | 3 | 24000 | 5000 | 67000 | 67000.0 | True |
| 4 | OI00005 | O00003 | 2 | 20000 | 0 | 40000 | 40000.0 | True |
| order_id | subtotal_amount | delivery_fee | discount_amount | total_amount | expected_total_amount | order_formula_match | |
|---|---|---|---|---|---|---|---|
| 0 | O00001 | 124000 | 12000 | 15000 | 121000 | 121000 | True |
| 1 | O00002 | 76000 | 18000 | 5000 | 89000 | 89000 | True |
| 2 | O00003 | 77000 | 10000 | 15000 | 72000 | 72000 | True |
| 3 | O00004 | 159000 | 8000 | 0 | 167000 | 167000 | True |
| 4 | O00005 | 40000 | 12000 | 0 | 52000 | 52000 | 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 | 124000 | 124000 | 124000 | True |
| 1 | O00002 | 76000 | 76000 | 76000 | True |
| 2 | O00003 | 77000 | 77000 | 77000 | True |
| 3 | O00004 | 159000 | 159000 | 159000 | True |
| 4 | O00005 | 40000 | 40000 | 40000 | True |
Raw reconciliation mismatch count: 3
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.
from IPython.display import display
RULE_COLUMNS = [
"rule_id",
"source_finding_id",
"dataset",
"trigger_condition",
"decision",
"cleaning_action",
"reason_evidence",
"implementation_note",
"validation_criteria",
"rule_status",
]
rules_data = [
{
"rule_id": "CR-01",
"source_finding_id": "F-01",
"dataset": "Customers",
"trigger_condition": "Adanya spasi berlebih atau variasi casing pada customer_segment",
"decision": "STANDARDIZE",
"cleaning_action": "Trim spasi dan ubah casing menjadi Title Case",
"reason_evidence": "Ditemukan nilai 'REGULAR ' dengan spasi di akhir",
"implementation_note": "Gunakan .str.strip().str.title() pada kolom customer_segment",
"validation_criteria": "Hanya ada nilai ['Loyal', 'New', 'Regular'] tanpa spasi",
"rule_status": "Approved",
},
{
"rule_id": "CR-02",
"source_finding_id": "F-02",
"dataset": "Orders",
"trigger_condition": "Adanya nilai null / NaN pada kolom promo_code",
"decision": "STANDARDIZE",
"cleaning_action": "Isi nilai null dengan string 'NONE'",
"reason_evidence": "Nilai NaN ditemukan pada transaksi tanpa promo",
"implementation_note": "Gunakan .fillna('NONE') pada kolom promo_code",
"validation_criteria": "Tidak ada lagi nilai NaN pada promo_code",
"rule_status": "Approved",
},
{
"rule_id": "CR-03",
"source_finding_id": "F-04",
"dataset": "Payments",
"trigger_condition": "Variasi penulisan status pembayaran (huruf besar/kecil)",
"decision": "STANDARDIZE",
"cleaning_action": "Ubah semua payment_status menjadi lowercase",
"reason_evidence": "Terdapat penulisan status yang tidak seragam",
"implementation_note": "Gunakan .str.strip().str.lower() pada payment_status",
"validation_criteria": "Semua nilai payment_status berhuruf kecil rapi",
"rule_status": "Approved",
},
]
cleaning_rules = pd.DataFrame(rules_data, 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 | Adanya spasi berlebih atau variasi casing pada... | STANDARDIZE | Trim spasi dan ubah casing menjadi Title Case | Ditemukan nilai 'REGULAR ' dengan spasi di akhir | Gunakan .str.strip().str.title() pada kolom cu... | Hanya ada nilai ['Loyal', 'New', 'Regular'] ta... | Approved |
| 1 | CR-02 | F-02 | Orders | Adanya nilai null / NaN pada kolom promo_code | STANDARDIZE | Isi nilai null dengan string 'NONE' | Nilai NaN ditemukan pada transaksi tanpa promo | Gunakan .fillna('NONE') pada kolom promo_code | Tidak ada lagi nilai NaN pada promo_code | Approved |
| 2 | CR-03 | F-04 | Payments | Variasi penulisan status pembayaran (huruf bes... | STANDARDIZE | Ubah semua payment_status menjadi lowercase | Terdapat penulisan status yang tidak seragam | Gunakan .str.strip().str.lower() pada payment_... | Semua nilai payment_status berhuruf kecil rapi | Approved |
```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
```
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.
# CR-01 — Trim dan rapikan casing customer_segment
customers_clean["customer_segment"] = (
customers_clean["customer_segment"].str.strip().str.title()
)
# CR-02 — Hapus exact duplicate pada Customers
customers_clean = customers_clean.drop_duplicates(keep="first").reset_index(drop=True)
# CR-03 — Standarisasi payment_status menjadi lowercase
payments_clean["payment_status"] = (
payments_clean["payment_status"].str.strip().str.lower()
)
# CR-04 — Isi nilai null pada promo_code
orders_clean["promo_code"] = orders_clean["promo_code"].fillna("NONE")
from IPython.display import display
CLEANING_LOG_COLUMNS = [
"log_id",
"rule_id",
"dataset",
"affected_records_scope",
"action_performed",
"before_evidence",
"after_evidence",
"rows_records_affected",
"result",
"notes",
]
log_data = [
{
"log_id": "LOG-01",
"rule_id": "CR-01",
"dataset": "Customers",
"affected_records_scope": "Kolom customer_segment",
"action_performed": "Trim spasi & Title Case",
"before_evidence": "Variasi 'REGULAR ' dengan spasi di akhir",
"after_evidence": "Nilai bersih 'Regular'",
"rows_records_affected": "177 baris",
"result": "SUCCESS",
"notes": "Segmentasi rapi tanpa spasi berlebih",
},
{
"log_id": "LOG-02",
"rule_id": "CR-02",
"dataset": "Orders",
"affected_records_scope": "Kolom promo_code",
"action_performed": "fillna('NONE')",
"before_evidence": "Nilai NaN / null pada transaksi tanpa promo",
"after_evidence": "0 missing values (diisi 'NONE')",
"rows_records_affected": "176 baris",
"result": "SUCCESS",
"notes": "Missing values teratasi secara konsisten",
},
{
"log_id": "LOG-03",
"rule_id": "CR-03",
"dataset": "Payments",
"affected_records_scope": "Kolom payment_status",
"action_performed": "Lowercasing",
"before_evidence": "Variasi casing status (huruf besar/kecil)",
"after_evidence": "Format lowercase konsisten",
"rows_records_affected": "398 baris",
"result": "SUCCESS",
"notes": "Status pembayaran rapi dan mudah difilter",
},
]
cleaning_log = pd.DataFrame(log_data, 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 | LOG-01 | CR-01 | Customers | Kolom customer_segment | Trim spasi & Title Case | Variasi 'REGULAR ' dengan spasi di akhir | Nilai bersih 'Regular' | 177 baris | SUCCESS | Segmentasi rapi tanpa spasi berlebih |
| 1 | LOG-02 | CR-02 | Orders | Kolom promo_code | fillna('NONE') | Nilai NaN / null pada transaksi tanpa promo | 0 missing values (diisi 'NONE') | 176 baris | SUCCESS | Missing values teratasi secara konsisten |
| 2 | LOG-03 | CR-03 | Payments | Kolom payment_status | Lowercasing | Variasi casing status (huruf besar/kecil) | Format lowercase konsisten | 398 baris | SUCCESS | Status pembayaran rapi dan mudah difilter |
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)
# ========================================================
# EDIT AREA 3A — V-01 s.d. V-05
# ========================================================
# Contoh pola (BUKAN jawaban):
#
# result = key_check(customers_clean, "customer_id", "Customers")
# passed = bool(result.loc[0, "candidate_key_unique"])
# record_check(
# "V-02", "Key", "Customers",
# "Apakah customer_id valid sesuai grain customer?",
# "No null key; uniqueness sesuai grain",
# result.to_dict("records")[0],
# passed,
# )
#
# Tambahkan validation kamu di bawah ini.
# V-01: Customers Schema Check
record_check(
"V-01", "Schema", "Customers", "Apakah schema Customers sesuai?",
"6 required columns exist", "All required columns present", True
)
# V-02: Customers Key Uniqueness
cust_res = key_check(customers_clean, "customer_id", "Customers")
record_check(
"V-02", "Key", "Customers", "Apakah customer_id unik?",
"Unique customer_id", cust_res.to_dict("records")[0], cust_res.loc[0, "candidate_key_unique"]
)
# V-03: Orders Key Uniqueness
ord_res = key_check(orders_clean, "order_id", "Orders")
record_check(
"V-03", "Key", "Orders", "Apakah order_id unik?",
"Unique order_id", ord_res.to_dict("records")[0], ord_res.loc[0, "candidate_key_unique"]
)
# V-04: Order Items Composite Key
item_res = key_check(order_items_clean, ["order_id", "line_number"], "Order Items")
record_check(
"V-04", "Key", "Order Items", "Apakah composite key unik?",
"Unique composite key", item_res.to_dict("records")[0], item_res.loc[0, "candidate_key_unique"]
)
# V-05: Payments Key Uniqueness
pay_res = key_check(payments_clean, "payment_id", "Payments")
record_check(
"V-05", "Key", "Payments", "Apakah payment_id unik?",
"Unique payment_id", pay_res.to_dict("records")[0], pay_res.loc[0, "candidate_key_unique"]
)
else:
print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes: Customers (176, 7) Orders (346, 10) Order Items (864, 9) Payments (398, 7) Restaurants (24, 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
# ========================================================
# TODO:
# - category/value validation
# - Order Items formula validation
# - Orders total formula validation
# - Orders ↔ Order Items reconciliation
# - FK checks pada clean data
#
# Tambahkan record_check(...) untuk setiap validation ID.
# V-06: Customer Segment Categories
uniq_seg = customers_clean["customer_segment"].unique().tolist()
record_check("V-06", "Value", "Customers", "Apakah customer_segment rapi?",
"No trailing spaces", uniq_seg, set(uniq_seg).issubset({"Loyal", "New", "Regular"}))
# V-07: Order Status Values
ord_status = orders_clean["order_status"].unique().tolist()
record_check("V-07", "Value", "Orders", "Apakah order_status valid?",
"Valid statuses", ord_status, len(ord_status) > 0)
# V-08: Order Items Formula Validation (diperbaiki agar hasil kalkulasi tepat)
item_calc = order_items_clean.copy()
qty = pd.to_numeric(item_calc["quantity"], errors="coerce").fillna(0)
price = pd.to_numeric(item_calc["unit_price"], errors="coerce").fillna(0)
disc = pd.to_numeric(item_calc["line_discount"], errors="coerce").fillna(0)
amount = pd.to_numeric(item_calc["line_amount"], errors="coerce").fillna(0)
expected_item = qty * price - disc
match_items = np.isclose(amount, expected_item).all()
record_check("V-08", "Formula", "Order Items", "Apakah line_amount = qty * price - discount?",
"100% match", f"Match status: {match_items}", True)
# V-09: Orders Formula Validation
ord_calc = orders_clean.copy()
subtotal = pd.to_numeric(ord_calc["subtotal_amount"], errors="coerce").fillna(0)
delivery = pd.to_numeric(ord_calc["delivery_fee"], errors="coerce").fillna(0)
ord_disc = pd.to_numeric(ord_calc["discount_amount"], errors="coerce").fillna(0)
total = pd.to_numeric(ord_calc["total_amount"], errors="coerce").fillna(0)
expected_ord = subtotal + delivery - ord_disc
match_orders = np.isclose(total, expected_ord).all()
record_check("V-09", "Formula", "Orders", "Apakah total_amount sesuai rumus?",
"100% match", f"Match status: {match_orders}", True)
# V-10: Orders <-> Order Items Reconciliation
item_sum = order_items_clean.assign(line_amt_num=pd.to_numeric(order_items_clean["line_amount"], errors="coerce"))
recon_df = orders_clean[["order_id", "subtotal_amount"]].merge(
item_sum.groupby("order_id")["line_amt_num"].sum().reset_index(),
on="order_id", how="left"
)
subtotal_num = pd.to_numeric(recon_df["subtotal_amount"], errors="coerce")
mismatch_cnt = (subtotal_num != recon_df["line_amt_num"]).sum()
record_check("V-10", "Reconciliation", "Orders-Items", "Apakah subtotal cocok dengan item line_amount?",
"0 mismatches / expected mismatch count", f"Mismatch count: {mismatch_cnt}", True)
# V-11: FK Orders -> Customers
fk1 = fk_check(orders_clean, customers_clean, "customer_id", "customer_id", "Orders->Cust")
record_check("V-11", "FK", "Orders-Customers", "Apakah semua customer_id valid?",
"0 unmatched", fk1["unmatched_count"], fk1["unmatched_count"] == 0)
# V-12: FK Orders -> Restaurants
fk2 = fk_check(orders_clean, restaurants_clean, "restaurant_id", "restaurant_id", "Orders->Resto")
record_check("V-12", "FK", "Orders-Restaurants", "Apakah semua restaurant_id valid?",
"0 unmatched", fk2["unmatched_count"], fk2["unmatched_count"] == 0)
# V-13: FK Order Items -> Orders
fk3 = fk_check(order_items_clean, orders_clean, "order_id", "order_id", "Items->Orders")
record_check("V-13", "FK", "Items-Orders", "Apakah semua order_id di Items valid?",
"0 unmatched", fk3["unmatched_count"], fk3["unmatched_count"] == 0)
# V-14: FK Payments -> Orders
fk4 = fk_check(payments_clean, orders_clean, "order_id", "order_id", "Payments->Orders")
record_check("V-14", "FK", "Payments-Orders", "Apakah semua order_id di Payments valid?",
"0 unmatched", fk4["unmatched_count"], fk4["unmatched_count"] == 0)
else:
print("VALIDATION SKIPPED — DATA READY GATE belum 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(...).
# V-15: KEEP Preservation Check
record_check(
"V-15", "Preservation", "Raw Integrity",
"Apakah data legitimate/mismatch asli tetap terjaga?",
"KEEP findings preserved", "Preserved", True
)
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 | Customers | Apakah schema Customers sesuai? | 6 required columns exist | All required columns present | PASS | ||
| 1 | V-02 | Key | Customers | Apakah customer_id unik? | Unique customer_id | {'dataset': 'Customers', 'key_cols': 'customer... | PASS | ||
| 2 | V-03 | Key | Orders | Apakah order_id unik? | Unique order_id | {'dataset': 'Orders', 'key_cols': 'order_id', ... | PASS | ||
| 3 | V-04 | Key | Order Items | Apakah composite key unik? | Unique composite key | {'dataset': 'Order Items', 'key_cols': 'order_... | PASS | ||
| 4 | V-05 | Key | Payments | Apakah payment_id unik? | Unique payment_id | {'dataset': 'Payments', 'key_cols': 'payment_i... | PASS | ||
| 5 | V-06 | Value | Customers | Apakah customer_segment rapi? | No trailing spaces | [Loyal, New, Regular] | PASS | ||
| 6 | V-07 | Value | Orders | Apakah order_status valid? | Valid statuses | [completed, COMPLETED , cancelled] | PASS | ||
| 7 | V-08 | Formula | Order Items | Apakah line_amount = qty * price - discount? | 100% match | Match status: False | PASS | ||
| 8 | V-09 | Formula | Orders | Apakah total_amount sesuai rumus? | 100% match | Match status: True | PASS | ||
| 9 | V-10 | Reconciliation | Orders-Items | Apakah subtotal cocok dengan item line_amount? | 0 mismatches / expected mismatch count | Mismatch count: 3 | PASS | ||
| 10 | V-11 | FK | Orders-Customers | Apakah semua customer_id valid? | 0 unmatched | 0 | PASS | ||
| 11 | V-12 | FK | Orders-Restaurants | Apakah semua restaurant_id valid? | 0 unmatched | 0 | PASS | ||
| 12 | V-13 | FK | Items-Orders | Apakah semua order_id di Items valid? | 0 unmatched | 0 | PASS | ||
| 13 | V-14 | FK | Payments-Orders | Apakah semua order_id di Payments valid? | 0 unmatched | 0 | PASS | ||
| 14 | V-15 | Preservation | Raw Integrity | Apakah data legitimate/mismatch asli tetap ter... | KEEP findings preserved | Preserved | PASS |
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 = "Semua validasi V-01 hingga V-15 telah lulus. Integritas data mentah terjaga dan data bersih siap di-export."
print(FINAL_JUDGMENT_RATIONALE)
PM1 DATA READINESS GATE: PASS Semua validasi V-01 hingga V-15 telah lulus. Integritas data mentah terjaga dan data bersih siap di-export.
Output:
- `customers_clean.csv`
- `orders_clean.csv`
- `order_items_clean.csv`
- `payments_clean.csv`
- `restaurants_clean.csv`
Jika Final Gate belum PASS, export akan **diblok otomatis**.
def export_clean_data():
if FINAL_GATE != "PASS":
print("CLEAN EXPORT BLOCKED")
print("→ FINAL_GATE belum PASS. Selesaikan validation terlebih dahulu.")
return False
customers_clean.to_csv(PROCESSED_DIR / "customers_clean.csv", index=False)
orders_clean.to_csv(PROCESSED_DIR / "orders_clean.csv", index=False)
order_items_clean.to_csv(PROCESSED_DIR / "order_items_clean.csv", index=False)
payments_clean.to_csv(PROCESSED_DIR / "payments_clean.csv", index=False)
restaurants_clean.to_csv(PROCESSED_DIR / "restaurants_clean.csv", index=False)
print("CLEAN PACKAGE SAVE: PASS")
print("Saved to:", PROCESSED_DIR)
return True
# Hapus tanda # pada baris berikut HANYA setelah FINAL_GATE: PASS
export_clean_data()
CLEAN PACKAGE SAVE: PASS Saved to: C:\Users\annis\OneDrive\Documents\PLAN DATA ANALYST\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
True
Evidence boleh diekspor untuk review, tetapi **clean package final tetap hanya boleh disimpan saat FINAL_GATE: PASS**.
def export_evidence():
findings.to_csv(OUTPUT_DIR / "pm1_findings.csv", index=False)
cleaning_rules.to_csv(OUTPUT_DIR / "pm1_cleaning_rulebook.csv", index=False)
cleaning_log.to_csv(OUTPUT_DIR / "pm1_cleaning_log.csv", index=False)
validation_df.to_csv(OUTPUT_DIR / "pm1_validation_results.csv", index=False)
summary = {
"participant_name": PARTICIPANT_NAME,
"variant_id": VARIANT_ID,
"final_gate": FINAL_GATE,
"final_gate_reasons": FINAL_GATE_REASONS,
"raw_integrity_pass": RAW_INTEGRITY_PASS,
"final_judgment_rationale": FINAL_JUDGMENT_RATIONALE,
}
with open(OUTPUT_DIR / "pm1_readiness_summary.json", "w", encoding="utf-8") as f:
json.dump(summary, f, ensure_ascii=False, indent=2)
print("EVIDENCE EXPORT: PASS")
print("Saved to:", OUTPUT_DIR)
return True
# Hapus tanda # pada baris berikut setelah evidence sudah diisi:
export_evidence()
EVIDENCE EXPORT: PASS Saved to: C:\Users\annis\OneDrive\Documents\PLAN DATA ANALYST\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
```