# 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 = "Yoreta"
VARIANT_ID = "SB-P28" # contoh: SB-P01
name_ok = PARTICIPANT_NAME.strip() not in {"", "YoretaKartika"}
variant_ok = (
VARIANT_ID.startswith("SB-P28")
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 : Yoreta Variant ID : SB-P28
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 : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst RAW_DIR : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite PROCESSED_DIR : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite OUTPUT_DIR : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\outputs\pm1_swiftbite DOCS_DIR : D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\docs\pm1_swiftbite
Tujuan cell ini hanya memastikan library utama tersedia. Jika DuckDB belum aktif, workflow utama starter tetap dapat berjalan dengan pandas.
print("pandas :", pd.__version__)
try:
import openpyxl
EXCEL_ENGINE_AVAILABLE = True
print("openpyxl:", openpyxl.__version__)
except ImportError:
EXCEL_ENGINE_AVAILABLE = False
print("openpyxl: NOT AVAILABLE")
if DUCKDB_AVAILABLE:
print("DuckDB :", duckdb.__version__)
try:
con = duckdb.connect()
print("DuckDB connection: OK")
except Exception as e:
print("DuckDB connection: WARNING —", type(e).__name__, str(e))
else:
print("DuckDB : NOT AVAILABLE")
print("INFO: workflow utama notebook tetap dapat dijalankan dengan pandas.")
if not EXCEL_ENGINE_AVAILABLE:
print("ENVIRONMENT CHECK: NEEDS FIX")
print("→ File XLSX membutuhkan openpyxl. Aktifkan environment sesuai setup Sesi 1.")
else:
print("ENVIRONMENT CHECK: PASS")
pandas : 3.0.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 | D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data... |
| 1 | orders | swiftbite_orders.xlsx | True | D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data... |
| 2 | order_items | swiftbite_order_items.csv | True | D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data... |
| 3 | payments | swiftbite_payments.txt | True | D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data... |
| 4 | restaurants | swiftbite_restaurants.csv | True | D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data... |
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", "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", "amount"
},
"Restaurants": {
"restaurant_id", "restaurant_name", "cuisine",
"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 | 9 | PASS | |
| 2 | Order Items | 9 | 9 | PASS | |
| 3 | Payments | 6 | 6 | PASS | |
| 4 | Restaurants | 6 | 6 | 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=(90, 7) ===
| customer_id | full_name | phone_number | signup_date | zone | customer_segment | ||
|---|---|---|---|---|---|---|---|
| 0 | C28-001 | SwiftBite User P28-001 | p28.user001@example.test | 82800000001 | 2025-07-09 | Jakarta Timur | Premium |
| 1 | C28-002 | SwiftBite User P28-002 | p28.user002@example.test | 82800000002 | 2025-10-12 | Jakarta Utara | Premium |
| 2 | C28-003 | SwiftBite User P28-003 | p28.user003@example.test | 82800000003 | 2026-02-19 | Jakarta Barat | Gold |
=== Orders | shape=(140, 9) ===
| order_id | customer_id | restaurant_id | order_timestamp | order_status | subtotal_amount | delivery_fee | discount_amount | total_amount | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | O28-0001 | C28-080 | R28-11 | 2026-02-22 12:44:00 | DELIVERED | 50000 | 8000 | 15000 | 43000 |
| 1 | O28-0002 | C28-079 | R28-05 | 2026-03-14 12:15:00 | DELIVERED | 299000 | 10000 | 0 | 309000 |
| 2 | O28-0003 | C28-057 | R28-09 | 2026-02-10 18:11:00 | DELIVERED | 410000 | 15000 | 0 | 425000 |
=== Order Items | shape=(374, 9) ===
| order_item_id | order_id | line_number | item_name | item_category | quantity | unit_price | line_discount | line_amount | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | OI28-00001 | O28-0001 | 1 | Noodle Soup | Main | 1 | 35000 | 3000 | 32000 |
| 1 | OI28-00002 | O28-0001 | 2 | Noodle Soup | Snack | 1 | 18000 | 0 | 18000 |
| 2 | OI28-00003 | O28-0002 | 1 | Fried Rice | Main | 4 | 35000 | 0 | 140000 |
=== Payments | shape=(155, 6) ===
| payment_id | order_id | payment_timestamp | payment_method | payment_status | amount | |
|---|---|---|---|---|---|---|
| 0 | P28-00001 | O28-0001 | 2026-01-06 10:09:00 | GoPay | SUCCESS | 43000 |
| 1 | P28-00002 | O28-0002 | 2026-01-07 10:10:00 | Credit Card | SUCCESS | 309000 |
| 2 | P28-00003 | O28-0003 | 2026-01-08 10:11:00 | GoPay | SUCCESS | 425000 |
=== Restaurants | shape=(18, 6) ===
| restaurant_id | restaurant_name | cuisine | zone | price_tier | active_status | |
|---|---|---|---|---|---|---|
| 0 | R28-01 | SwiftBite Kitchen P28-01 | Indonesian | Jakarta Barat | Budget | Active |
| 1 | R28-02 | SwiftBite Kitchen P28-02 | Japanese | Jakarta Selatan | Mid | Active |
| 2 | R28-03 | SwiftBite Kitchen P28-03 | Korean | Jakarta Timur | Premium | 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 | 90 | 7 | [customer_id, full_name, email, phone_number, ... |
| 1 | Orders | 140 | 9 | [order_id, customer_id, restaurant_id, order_t... |
| 2 | Order Items | 374 | 9 | [order_item_id, order_id, line_number, item_na... |
| 3 | Payments | 155 | 6 | [payment_id, order_id, payment_timestamp, paym... |
| 4 | Restaurants | 18 | 6 | [restaurant_id, restaurant_name, cuisine, zone... |
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 | 90 | 90 | 0 | 0 | True |
| 1 | Orders | order_id | 140 | 140 | 0 | 0 | True |
| 2 | Order Items | order_item_id | 374 | 373 | 0 | 2 | False |
| 3 | Order Items — candidate business key | order_id, line_number | 374 | 373 | 0 | 2 | False |
| 4 | Payments | payment_id | 155 | 154 | 0 | 2 | False |
| 5 | Restaurants | restaurant_id | 18 | 18 | 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 | |
|---|---|---|
| 55 | O28-0056 | 5 |
| 2 | O28-0003 | 4 |
| 11 | O28-0012 | 4 |
| 10 | O28-0011 | 4 |
| 9 | O28-0010 | 4 |
| 30 | O28-0031 | 4 |
| 38 | O28-0039 | 4 |
| 41 | O28-0042 | 4 |
| 16 | O28-0017 | 4 |
| 18 | O28-0019 | 4 |
Payments cardinality
| order_id | payment_rows | |
|---|---|---|
| 8 | O28-0009 | 2 |
| 134 | O28-0135 | 2 |
| 107 | O28-0108 | 2 |
| 98 | O28-0099 | 2 |
| 44 | O28-0045 | 2 |
| 53 | O28-0054 | 2 |
| 71 | O28-0072 | 2 |
| 62 | O28-0063 | 2 |
| 35 | O28-0036 | 2 |
| 25 | O28-0026 | 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 | 90 | 7 | 0 | 0 | 3 |
| 1 | Orders | 140 | 9 | 0 | 0 | 0 |
| 2 | Order Items | 374 | 9 | 2 | 1 | 0 |
| 3 | Payments | 155 | 6 | 2 | 1 | 0 |
| 4 | Restaurants | 18 | 6 | 0 | 0 | 0 |
# Ganti object untuk memeriksa dataset lain.
if DATA_READY:
display(column_profile(orders_raw))
| column | dtype | null_count | null_pct | unique_count | sample_values | |
|---|---|---|---|---|---|---|
| 0 | order_id | str | 0 | 0.0 | 140 | [O28-0001, O28-0002, O28-0003, O28-0004, O28-0... |
| 1 | customer_id | str | 0 | 0.0 | 75 | [C28-080, C28-079, C28-057, C28-076, C28-021] |
| 2 | restaurant_id | str | 0 | 0.0 | 18 | [R28-11, R28-05, R28-09, R28-03, R28-01] |
| 3 | order_timestamp | str | 0 | 0.0 | 140 | [2026-02-22 12:44:00, 2026-03-14 12:15:00, 202... |
| 4 | order_status | str | 0 | 0.0 | 4 | [DELIVERED, CANCELLED, Delivered, DELIVERED ] |
| 5 | subtotal_amount | int64 | 0 | 0.0 | 111 | [50000, 299000, 410000, 39000, 79000] |
| 6 | delivery_fee | int64 | 0 | 0.0 | 5 | [8000, 10000, 15000, 18000, 12000] |
| 7 | discount_amount | int64 | 0 | 0.0 | 4 | [15000, 0, 10000, 5000] |
| 8 | total_amount | int64 | 0 | 0.0 | 122 | [43000, 309000, 425000, 54000, 79000] |
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)
display(category_review(customers_raw, "customer_segment"))
display(category_review(orders_raw, "order_status"))
display(category_review(order_items_raw, "quantity"))
display(category_review(payments_raw, "payment_method"))
display(category_review(restaurants_raw, "price_tier"))
| customer_segment | count | pct | |
|---|---|---|---|
| 0 | Gold | 22 | 0.2444 |
| 1 | Silver | 22 | 0.2444 |
| 2 | Bronze | 21 | 0.2333 |
| 3 | Premium | 20 | 0.2222 |
| 4 | NaN | 3 | 0.0333 |
| 5 | gold | 1 | 0.0111 |
| 6 | Silver | 1 | 0.0111 |
| order_status | count | pct | |
|---|---|---|---|
| 0 | DELIVERED | 128 | 0.9143 |
| 1 | CANCELLED | 10 | 0.0714 |
| 2 | Delivered | 1 | 0.0071 |
| 3 | DELIVERED | 1 | 0.0071 |
| quantity | count | pct | |
|---|---|---|---|
| 0 | 4 | 113 | 0.3021 |
| 1 | 3 | 102 | 0.2727 |
| 2 | 1 | 90 | 0.2406 |
| 3 | 2 | 67 | 0.1791 |
| 4 | 3 pcs | 1 | 0.0027 |
| 5 | 18 | 1 | 0.0027 |
| payment_method | count | pct | |
|---|---|---|---|
| 0 | Credit Card | 34 | 0.2194 |
| 1 | GoPay | 33 | 0.2129 |
| 2 | QRIS | 32 | 0.2065 |
| 3 | OVO | 30 | 0.1935 |
| 4 | Bank Transfer | 25 | 0.1613 |
| 5 | GoPay | 1 | 0.0065 |
| price_tier | count | pct | |
|---|---|---|---|
| 0 | Mid | 6 | 0.3333 |
| 1 | Premium | 6 | 0.3333 |
| 2 | Budget | 5 | 0.2778 |
| 3 | mid | 1 | 0.0556 |
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
display(numeric_profile(orders_raw))
display(numeric_profile(order_items_raw))
| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| subtotal_amount | 140.0 | 253492.857143 | 147683.527887 | 15000.0 | 143250.0 | 249500.0 | 346500.0 | 729000.0 |
| delivery_fee | 140.0 | 12714.285714 | 3664.501525 | 8000.0 | 10000.0 | 12000.0 | 15000.0 | 18000.0 |
| discount_amount | 140.0 | 6392.857143 | 6059.295052 | 0.0 | 0.0 | 5000.0 | 10000.0 | 15000.0 |
| total_amount | 140.0 | 259814.285714 | 147402.428927 | 23000.0 | 150000.0 | 249000.0 | 353500.0 | 724000.0 |
| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| line_number | 374.0 | 2.074866 | 1.040602 | 1.0 | 1.0 | 2.0 | 3.0 | 4.0 |
| unit_price | 374.0 | 36649.732620 | 14265.118500 | 18000.0 | 25000.0 | 35000.0 | 48000.0 | 65000.0 |
| line_discount | 374.0 | 1550.802139 | 2019.034595 | 0.0 | 0.0 | 0.0 | 3000.0 | 5000.0 |
| line_amount | 374.0 | 95227.272727 | 58783.049157 | 13000.0 | 50000.0 | 86500.0 | 126000.0 | 324000.0 |
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([
{
"finding_id": "F-01",
"dataset": "swiftbite_customers.json",
"field_key": "customer_segment",
"finding_evidence": "3 dari 90 baris bernilai null: C28-012, C28-045, C28-078.",
"why_it_matters": "Analisis pelanggan per segmen dapat tidak lengkap.",
"verification_method": "Hitung null dan periksa sumber/master yang menentukan segmen pelanggan.",
"verification_evidence_result": "3 null terkonfirmasi; nilai segmen pengganti belum diketahui.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Keberadaan nilai kosong terbukti, tetapi nilai yang benar tidak dapat disimpulkan dari file ini.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-02",
"dataset": "swiftbite_customers.json",
"field_key": "customer_segment",
"finding_evidence": "Ada Gold=22, gold=1, dan Silver dengan spasi akhir=1.",
"why_it_matters": "Label yang sama dapat dihitung sebagai kategori terpisah.",
"verification_method": "Periksa nilai unik dan konfirmasi daftar kategori segmen resmi.",
"verification_evidence_result": "Variasi kapitalisasi dan spasi teridentifikasi; kesetaraan semantik perlu dikonfirmasi.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Jika label resmi mengonfirmasi kesetaraan, trim spasi dan gunakan kapitalisasi kanonis.",
"cleaning_rule_id": "CR-CUST-001",
},
{
"finding_id": "F-03",
"dataset": "swiftbite_customers.json",
"field_key": "phone_number",
"finding_evidence": "Kolom phone_number terbaca sebagai int64.",
"why_it_matters": "Nomor telepon merupakan identifier, bukan besaran numerik; format awal dapat hilang.",
"verification_method": "Periksa panjang/pola nomor, lalu konversi ke string tanpa mengubah digit.",
"verification_evidence_result": "90 nomor dibaca sebagai integer; aturan format nomor sumber belum dikonfirmasi.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Tipe teks lebih sesuai untuk menyimpan identifier; jangan mengubah digit tanpa bukti.",
"cleaning_rule_id": "CR-CUST-002",
},
{
"finding_id": "F-04",
"dataset": "swiftbite_orders.xlsx",
"field_key": "order_status",
"finding_evidence": "DELIVERED=128, CANCELLED=10, Delivered=1, DELIVERED dengan spasi akhir=1.",
"why_it_matters": "Agregasi status dapat memisahkan label yang sama.",
"verification_method": "Periksa nilai unik dan konfirmasi kamus status resmi.",
"verification_evidence_result": "Variasi kapitalisasi/spasi teridentifikasi; pemetaan perlu mengikuti label resmi.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Seragamkan trim dan kapitalisasi setelah memastikan label memiliki arti sama.",
"cleaning_rule_id": "CR-ORD-001",
},
{
"finding_id": "F-05",
"dataset": "swiftbite_orders.xlsx",
"field_key": "order_timestamp",
"finding_evidence": "Format timestamp bercampur; contoh 22/03/2026 13:32 dan 05-01-2026 18:05.",
"why_it_matters": "Parsing yang salah dapat mengubah urutan waktu dan periode laporan.",
"verification_method": "Konfirmasi format tanggal sumber dan validasi nilai ambigu sebelum parsing final.",
"verification_evidence_result": "Beberapa format ditemukan; 05-01-2026 belum dapat ditafsirkan dengan pasti tanpa konvensi sumber.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Format belum aman diseragamkan sebelum arti tanggal ambigu dikonfirmasi.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-06",
"dataset": "swiftbite_orders.xlsx",
"field_key": "subtotal_amount; total_amount",
"finding_evidence": "O28-0131 memiliki subtotal 729000 dan total 724000; kandidat outlier IQR.",
"why_it_matters": "Outlier dapat menandakan kesalahan input, tetapi juga dapat merupakan transaksi sah.",
"verification_method": "Hitung subtotal + delivery_fee - discount_amount dan cocokkan total dengan pembayaran sukses.",
"verification_evidence_result": "729000 + 10000 - 15000 = 724000; pembayaran sukses terkait juga 724000.",
"judgment": "KEEP",
"reason_for_judgment": "Rumus dan pembayaran mendukung konsistensi nilai; nilai tinggi saja tidak membuktikan kesalahan.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-07",
"dataset": "swiftbite_order_items.csv",
"field_key": "order_item_id; order_id; line_amount",
"finding_evidence": "OI28-00146 muncul dua kali identik pada O28-0056; line_amount 126000.",
"why_it_matters": "Duplikasi dapat membuat jumlah item dan subtotal terhitung berlebihan.",
"verification_method": "Cari ID berulang, bandingkan semua kolom, dan cocokkan dengan sumber transaksi.",
"verification_evidence_result": "Duplikasi identik terkonfirmasi; total item O28-0056=533000 versus subtotal=407000, selisih 126000.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Bukti duplikasi kuat, tetapi sumber transaksi perlu memastikan apakah baris harus dihapus.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-08",
"dataset": "swiftbite_order_items.csv",
"field_key": "quantity",
"finding_evidence": 'OI28-00192 memiliki quantity "3 pcs".',
"why_it_matters": "Teks satuan dalam kolom kuantitas dapat mengganggu operasi numerik.",
"verification_method": "Periksa sumber item dan pastikan pcs hanya satuan, kemudian parsing angka.",
"verification_evidence_result": "Satu nilai mengandung teks satuan; bagian numeriknya 3 dan rumus line_amount tetap cocok setelah parsing.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Simpan kuantitas sebagai numerik setelah arti suffix dikonfirmasi.",
"cleaning_rule_id": "CR-ITEM-001",
},
{
"finding_id": "F-09",
"dataset": "swiftbite_order_items.csv",
"field_key": "quantity",
"finding_evidence": "Quantity 18 pada OI28-00293, order O28-0111, item Korean Chicken.",
"why_it_matters": "Salah input kuantitas dapat memengaruhi nilai penjualan dan analisis permintaan.",
"verification_method": "Cocokkan kuantitas dengan tiket atau catatan pesanan sumber.",
"verification_evidence_result": "Nilai 18 terdeteksi sebagai kandidat outlier; kewajaran bisnis belum dikonfirmasi.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Nilai tinggi dapat sah untuk pesanan besar; tidak ada dasar untuk mengubahnya otomatis.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-10",
"dataset": "swiftbite_order_items.csv",
"field_key": "item_name; item_category",
"finding_evidence": "Satu baris Gyoza diberi kategori Drink.",
"why_it_matters": "Laporan penjualan menurut kategori bisa salah jika klasifikasi keliru.",
"verification_method": "Bandingkan nama item dengan master menu atau kamus kategori resmi.",
"verification_evidence_result": "Kombinasi nama-kategori teridentifikasi; aturan kategori bisnis belum tersedia.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Nama item saja belum cukup untuk membuktikan kategori yang benar.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-11",
"dataset": "swiftbite_order_items.csv",
"field_key": "quantity; unit_price; line_discount; line_amount",
"finding_evidence": "Periksa rumus line_amount = quantity * unit_price - line_discount.",
"why_it_matters": "Rumus yang tidak cocok dapat menandakan nilai transaksi yang salah.",
"verification_method": "Hitung ulang line_amount setelah mengekstrak bagian numerik quantity.",
"verification_evidence_result": "0 dari 374 baris berbeda dari hasil rumus setelah parsing kuantitas.",
"judgment": "KEEP",
"reason_for_judgment": "Pemeriksaan aritmetika mendukung nilai line_amount; duplikasi record tetap ditangani sebagai temuan terpisah.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-12",
"dataset": "swiftbite_payments.txt",
"field_key": "payment_id; order_id; amount",
"finding_evidence": "P28-00028 muncul dua kali identik; FAILED, O28-0026, amount 39000.",
"why_it_matters": "Duplikasi dapat menggelembungkan jumlah catatan atau percobaan pembayaran.",
"verification_method": "Cari payment_id berulang dan cocokkan dengan log pembayaran sumber.",
"verification_evidence_result": "Dua baris identik ditemukan; belum ada bukti sumber yang memastikan baris kedua tidak sah.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Perlu memastikan apakah ini duplikasi pemuatan data atau catatan percobaan yang valid.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-13",
"dataset": "swiftbite_payments.txt",
"field_key": "payment_method",
"finding_evidence": "GoPay muncul 33 kali dan GoPay dengan spasi akhir muncul 1 kali.",
"why_it_matters": "Analisis metode pembayaran dapat memisahkan label yang sama.",
"verification_method": "Periksa nilai unik dan pastikan variasi hanya whitespace.",
"verification_evidence_result": "Satu variasi dengan spasi akhir ditemukan.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Trim spasi tepi dan gunakan label resmi setelah kesetaraan dikonfirmasi.",
"cleaning_rule_id": "CR-PAY-001",
},
{
"finding_id": "F-14",
"dataset": "swiftbite_payments.txt",
"field_key": "payment_timestamp",
"finding_evidence": "P28-00068 memakai format 2026/03/08 11:10.",
"why_it_matters": "Format yang tidak seragam dapat mengganggu parsing dan analisis waktu.",
"verification_method": "Parse timestamp dan validasi tanggal/waktu terhadap konvensi sumber.",
"verification_evidence_result": "Satu variasi format ditemukan; format target perlu ditetapkan.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Seragamkan representasi setelah parsing berhasil dan tanggal sumber tervalidasi.",
"cleaning_rule_id": "CR-PAY-002",
},
{
"finding_id": "F-15",
"dataset": "swiftbite_payments.txt",
"field_key": "payment_timestamp; order_timestamp",
"finding_evidence": "87 dari 155 baris tampak memiliki waktu pembayaran sebelum waktu pesanan setelah parsing.",
"why_it_matters": "Urutan waktu yang salah dapat merusak analisis proses transaksi.",
"verification_method": "Validasi format tanggal, zona waktu, dan definisi timestamp sebelum membandingkan.",
"verification_evidence_result": "87 kandidat anomali waktu terdeteksi; interpretasi tanggal dan aturan waktu belum dikonfirmasi.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Belum cukup bukti untuk menyimpulkan timestamp salah sebelum konvensi sumber divalidasi.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-16",
"dataset": "swiftbite_payments.txt",
"field_key": "amount",
"finding_evidence": "P28-00144 memiliki amount 724000, kandidat outlier IQR.",
"why_it_matters": "Outlier pembayaran dapat menandakan kesalahan, tetapi juga bisa merupakan transaksi sah.",
"verification_method": "Cocokkan amount dengan total pesanan dan status pembayaran.",
"verification_evidence_result": "Status SUCCESS; amount 724000 sama dengan total O28-0131 dan rumus total konsisten.",
"judgment": "KEEP",
"reason_for_judgment": "Nilai didukung oleh total pesanan dan pembayaran sukses; tidak perlu diubah hanya karena besar.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-17",
"dataset": "swiftbite_payments.txt",
"field_key": "order_id; payment_status",
"finding_evidence": "14 pesanan memiliki setidaknya satu SUCCESS dan satu FAILED.",
"why_it_matters": "Menganggap setiap baris sebagai hasil pembayaran akhir dapat salah menghitung status transaksi.",
"verification_method": "Kelompokkan pembayaran per order_id dan tinjau urutan percobaan di log pembayaran.",
"verification_evidence_result": "14 order memiliki status SUCCESS dan FAILED; pola retry belum dikonfirmasi.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Kombinasi status bisa merupakan retry yang sah; aturan bisnis diperlukan sebelum mengubah data.",
"cleaning_rule_id": None,
},
{
"finding_id": "F-18",
"dataset": "swiftbite_restaurants.csv",
"field_key": "price_tier",
"finding_evidence": "Ada 6 Mid dan 1 mid dengan spasi akhir.",
"why_it_matters": "Penghitungan restoran per tingkat harga dapat memisahkan label yang sama.",
"verification_method": "Periksa nilai unik dan konfirmasi daftar price_tier resmi.",
"verification_evidence_result": "Satu variasi kapitalisasi/spasi ditemukan.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Gunakan label kanonis setelah memastikan mid dan Mid memiliki arti sama.",
"cleaning_rule_id": "CR-REST-001",
},
{
"finding_id": "F-19",
"dataset": "swiftbite_restaurants.csv",
"field_key": "active_status",
"finding_evidence": "Ada 16 Active, 1 active, dan 1 Inactive.",
"why_it_matters": "Filter status aktif dapat melewatkan kategori karena perbedaan kapitalisasi.",
"verification_method": "Periksa nilai unik dan konfirmasi label status resmi.",
"verification_evidence_result": "Satu variasi kapitalisasi ditemukan.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Samakan kapitalisasi jika active dan Active merupakan label yang sama.",
"cleaning_rule_id": "CR-REST-002",
},
], 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 | swiftbite_customers.json | customer_segment | 3 dari 90 baris bernilai null: C28-012, C28-04... | Analisis pelanggan per segmen dapat tidak leng... | Hitung null dan periksa sumber/master yang men... | 3 null terkonfirmasi; nilai segmen pengganti b... | INVESTIGATE | Keberadaan nilai kosong terbukti, tetapi nilai... | NaN |
| 1 | F-02 | swiftbite_customers.json | customer_segment | Ada Gold=22, gold=1, dan Silver dengan spasi a... | Label yang sama dapat dihitung sebagai kategor... | Periksa nilai unik dan konfirmasi daftar kateg... | Variasi kapitalisasi dan spasi teridentifikasi... | STANDARDIZE | Jika label resmi mengonfirmasi kesetaraan, tri... | CR-CUST-001 |
| 2 | F-03 | swiftbite_customers.json | phone_number | Kolom phone_number terbaca sebagai int64. | Nomor telepon merupakan identifier, bukan besa... | Periksa panjang/pola nomor, lalu konversi ke s... | 90 nomor dibaca sebagai integer; aturan format... | STANDARDIZE | Tipe teks lebih sesuai untuk menyimpan identif... | CR-CUST-002 |
| 3 | F-04 | swiftbite_orders.xlsx | order_status | DELIVERED=128, CANCELLED=10, Delivered=1, DELI... | Agregasi status dapat memisahkan label yang sama. | Periksa nilai unik dan konfirmasi kamus status... | Variasi kapitalisasi/spasi teridentifikasi; pe... | STANDARDIZE | Seragamkan trim dan kapitalisasi setelah memas... | CR-ORD-001 |
| 4 | F-05 | swiftbite_orders.xlsx | order_timestamp | Format timestamp bercampur; contoh 22/03/2026 ... | Parsing yang salah dapat mengubah urutan waktu... | Konfirmasi format tanggal sumber dan validasi ... | Beberapa format ditemukan; 05-01-2026 belum da... | INVESTIGATE | Format belum aman diseragamkan sebelum arti ta... | NaN |
| 5 | F-06 | swiftbite_orders.xlsx | subtotal_amount; total_amount | O28-0131 memiliki subtotal 729000 dan total 72... | Outlier dapat menandakan kesalahan input, teta... | Hitung subtotal + delivery_fee - discount_amou... | 729000 + 10000 - 15000 = 724000; pembayaran su... | KEEP | Rumus dan pembayaran mendukung konsistensi nil... | NaN |
| 6 | F-07 | swiftbite_order_items.csv | order_item_id; order_id; line_amount | OI28-00146 muncul dua kali identik pada O28-00... | Duplikasi dapat membuat jumlah item dan subtot... | Cari ID berulang, bandingkan semua kolom, dan ... | Duplikasi identik terkonfirmasi; total item O2... | INVESTIGATE | Bukti duplikasi kuat, tetapi sumber transaksi ... | NaN |
| 7 | F-08 | swiftbite_order_items.csv | quantity | OI28-00192 memiliki quantity "3 pcs". | Teks satuan dalam kolom kuantitas dapat mengga... | Periksa sumber item dan pastikan pcs hanya sat... | Satu nilai mengandung teks satuan; bagian nume... | STANDARDIZE | Simpan kuantitas sebagai numerik setelah arti ... | CR-ITEM-001 |
| 8 | F-09 | swiftbite_order_items.csv | quantity | Quantity 18 pada OI28-00293, order O28-0111, i... | Salah input kuantitas dapat memengaruhi nilai ... | Cocokkan kuantitas dengan tiket atau catatan p... | Nilai 18 terdeteksi sebagai kandidat outlier; ... | INVESTIGATE | Nilai tinggi dapat sah untuk pesanan besar; ti... | NaN |
| 9 | F-10 | swiftbite_order_items.csv | item_name; item_category | Satu baris Gyoza diberi kategori Drink. | Laporan penjualan menurut kategori bisa salah ... | Bandingkan nama item dengan master menu atau k... | Kombinasi nama-kategori teridentifikasi; atura... | INVESTIGATE | Nama item saja belum cukup untuk membuktikan k... | NaN |
| 10 | F-11 | swiftbite_order_items.csv | quantity; unit_price; line_discount; line_amount | Periksa rumus line_amount = quantity * unit_pr... | Rumus yang tidak cocok dapat menandakan nilai ... | Hitung ulang line_amount setelah mengekstrak b... | 0 dari 374 baris berbeda dari hasil rumus sete... | KEEP | Pemeriksaan aritmetika mendukung nilai line_am... | NaN |
| 11 | F-12 | swiftbite_payments.txt | payment_id; order_id; amount | P28-00028 muncul dua kali identik; FAILED, O28... | Duplikasi dapat menggelembungkan jumlah catata... | Cari payment_id berulang dan cocokkan dengan l... | Dua baris identik ditemukan; belum ada bukti s... | INVESTIGATE | Perlu memastikan apakah ini duplikasi pemuatan... | NaN |
| 12 | F-13 | swiftbite_payments.txt | payment_method | GoPay muncul 33 kali dan GoPay dengan spasi ak... | Analisis metode pembayaran dapat memisahkan la... | Periksa nilai unik dan pastikan variasi hanya ... | Satu variasi dengan spasi akhir ditemukan. | STANDARDIZE | Trim spasi tepi dan gunakan label resmi setela... | CR-PAY-001 |
| 13 | F-14 | swiftbite_payments.txt | payment_timestamp | P28-00068 memakai format 2026/03/08 11:10. | Format yang tidak seragam dapat mengganggu par... | Parse timestamp dan validasi tanggal/waktu ter... | Satu variasi format ditemukan; format target p... | STANDARDIZE | Seragamkan representasi setelah parsing berhas... | CR-PAY-002 |
| 14 | F-15 | swiftbite_payments.txt | payment_timestamp; order_timestamp | 87 dari 155 baris tampak memiliki waktu pembay... | Urutan waktu yang salah dapat merusak analisis... | Validasi format tanggal, zona waktu, dan defin... | 87 kandidat anomali waktu terdeteksi; interpre... | INVESTIGATE | Belum cukup bukti untuk menyimpulkan timestamp... | NaN |
| 15 | F-16 | swiftbite_payments.txt | amount | P28-00144 memiliki amount 724000, kandidat out... | Outlier pembayaran dapat menandakan kesalahan,... | Cocokkan amount dengan total pesanan dan statu... | Status SUCCESS; amount 724000 sama dengan tota... | KEEP | Nilai didukung oleh total pesanan dan pembayar... | NaN |
| 16 | F-17 | swiftbite_payments.txt | order_id; payment_status | 14 pesanan memiliki setidaknya satu SUCCESS da... | Menganggap setiap baris sebagai hasil pembayar... | Kelompokkan pembayaran per order_id dan tinjau... | 14 order memiliki status SUCCESS dan FAILED; p... | INVESTIGATE | Kombinasi status bisa merupakan retry yang sah... | NaN |
| 17 | F-18 | swiftbite_restaurants.csv | price_tier | Ada 6 Mid dan 1 mid dengan spasi akhir. | Penghitungan restoran per tingkat harga dapat ... | Periksa nilai unik dan konfirmasi daftar price... | Satu variasi kapitalisasi/spasi ditemukan. | STANDARDIZE | Gunakan label kanonis setelah memastikan mid d... | CR-REST-001 |
| 18 | F-19 | swiftbite_restaurants.csv | active_status | Ada 16 Active, 1 active, dan 1 Inactive. | Filter status aktif dapat melewatkan kategori ... | Periksa nilai unik dan konfirmasi label status... | Satu variasi kapitalisasi ditemukan. | STANDARDIZE | Samakan kapitalisasi jika active dan Active me... | CR-REST-002 |
```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 | 75 | 0 | [] |
| 1 | Orders → Restaurants | 18 | 0 | [] |
| 2 | Order Items → Orders | 140 | 0 | [] |
| 3 | Payments → Orders | 140 | 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 | OI28-00001 | O28-0001 | 1 | 35000 | 3000 | 32000 | 32000.0 | True |
| 1 | OI28-00002 | O28-0001 | 1 | 18000 | 0 | 18000 | 18000.0 | True |
| 2 | OI28-00003 | O28-0002 | 4 | 35000 | 0 | 140000 | 140000.0 | True |
| 3 | OI28-00004 | O28-0002 | 3 | 22000 | 3000 | 63000 | 63000.0 | True |
| 4 | OI28-00005 | O28-0002 | 3 | 32000 | 0 | 96000 | 96000.0 | True |
| order_id | subtotal_amount | delivery_fee | discount_amount | total_amount | expected_total_amount | order_formula_match | |
|---|---|---|---|---|---|---|---|
| 0 | O28-0001 | 50000 | 8000 | 15000 | 43000 | 43000 | True |
| 1 | O28-0002 | 299000 | 10000 | 0 | 309000 | 309000 | True |
| 2 | O28-0003 | 410000 | 15000 | 0 | 425000 | 425000 | True |
| 3 | O28-0004 | 39000 | 15000 | 0 | 54000 | 54000 | True |
| 4 | O28-0005 | 79000 | 15000 | 15000 | 79000 | 79000 | 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 | O28-0001 | 50000 | 50000 | 50000 | True |
| 1 | O28-0002 | 299000 | 299000 | 299000 | True |
| 2 | O28-0003 | 410000 | 410000 | 410000 | True |
| 3 | O28-0004 | 39000 | 39000 | 39000 | True |
| 4 | O28-0005 | 79000 | 79000 | 79000 | True |
Raw reconciliation mismatch count: 1
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.
import pandas as pd
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-02",
"dataset": "swiftbite_customers.json",
"trigger_condition": "Ada Gold=22, gold=1, dan Silver dengan spasi akhir=1",
"decision": "STANDARDIZE",
"cleaning_action": "strip() lalu title(); NULL tidak diubah",
"reason_evidence": "gold dan Silver setara Gold dan Silver",
"implementation_note": "Konfirmasi label kanonis dahulu; simpan kolom asli",
"validation_criteria": "Hanya Gold/Silver/Bronze/Premium + 3 NULL; 90 baris tetap",
"rule_status": "Validated",
},
{
"rule_id": "CR-02",
"source_finding_id": "F-03",
"dataset": "swiftbite_customers.json",
"trigger_condition": "Kolom phone_number terbaca sebagai int64",
"decision": "STANDARDIZE",
"cleaning_action": "Ubah tipe phone_number menjadi string tanpa mengubah digit",
"reason_evidence": "Kolom phone_number terbaca sebagai int64",
"implementation_note": "Validasi panjang nomor dan kemungkinan nol di depan; jangan membuat digit baru",
"validation_criteria": "Semua nilai non-null tersimpan sebagai teks dan digit sumber tetap terjaga",
"rule_status": "Validated",
},
{
"rule_id": "CR-03",
"source_finding_id": "F-04",
"dataset": "swiftbite_customers.json",
"trigger_condition": "DELIVERED=128, CANCELLED=10, Delivered=1, DELIVERED dengan spasi akhir=1",
"decision": "STANDARDIZE",
"cleaning_action": "Trim spasi dan petakan status ke label resmi.",
"reason_evidence": "Ditemukan Delivered dan DELIVERED dengan spasi akhir.",
"implementation_note": "Gunakan mapping eksplisit berdasarkan kamus status; jangan hanya mengandalkan kapitalisasi",
"validation_criteria": "Semua status valid sesuai daftar resmi dan jumlah baris tetap 140.",
"rule_status": "Approved",
},
{
"rule_id": "CR-04",
"source_finding_id": "F-05",
"dataset": "swiftbite_orders.xlsx",
"trigger_condition": "Format timestamp bercampur; contoh 22/03/2026 13:32 dan 05-01-2026 18:05",
"decision": "STANDARDIZE",
"cleaning_action": "Konfirmasi format sumber; parse hanya nilai yang interpretasinya jelas.",
"reason_evidence": "Contoh format berbeda termasuk 05-01-2026 18:05.",
"implementation_note": "Jangan menetapkan day-first atau month-first tanpa bukti sumber",
"validation_criteria": "Semua timestamp berhasil diparsing sesuai konvensi yang disepakati",
"rule_status": "Approved",
},
{
"rule_id": "CR-05",
"source_finding_id": "F-07",
"dataset": "swiftbite_order_items.csv",
"trigger_condition": "OI28-00146 muncul dua kali identik pada O28-0056",
"decision": "FIX",
"cleaning_action": "Bandingkan baris duplikat dengan sumber transaksi sebelum memilih baris yang akan dihapus",
"reason_evidence": "OI28-00146 muncul dua kali",
"implementation_note": "Jangan drop_duplicates langsung pada data final",
"validation_criteria": "Keputusan duplikasi didukung sumber; setelah tindakan, jumlah item dan subtotal dapat direkonsiliasi.",
"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-02 | swiftbite_customers.json | Ada Gold=22, gold=1, dan Silver dengan spasi a... | STANDARDIZE | strip() lalu title(); NULL tidak diubah | gold dan Silver setara Gold dan Silver | Konfirmasi label kanonis dahulu; simpan kolom ... | Hanya Gold/Silver/Bronze/Premium + 3 NULL; 90 ... | Validated |
| 1 | CR-02 | F-03 | swiftbite_customers.json | Kolom phone_number terbaca sebagai int64 | STANDARDIZE | Ubah tipe phone_number menjadi string tanpa me... | Kolom phone_number terbaca sebagai int64 | Validasi panjang nomor dan kemungkinan nol di ... | Semua nilai non-null tersimpan sebagai teks da... | Validated |
| 2 | CR-03 | F-04 | swiftbite_customers.json | DELIVERED=128, CANCELLED=10, Delivered=1, DELI... | STANDARDIZE | Trim spasi dan petakan status ke label resmi. | Ditemukan Delivered dan DELIVERED dengan spasi... | Gunakan mapping eksplisit berdasarkan kamus st... | Semua status valid sesuai daftar resmi dan jum... | Approved |
| 3 | CR-04 | F-05 | swiftbite_orders.xlsx | Format timestamp bercampur; contoh 22/03/2026 ... | STANDARDIZE | Konfirmasi format sumber; parse hanya nilai ya... | Contoh format berbeda termasuk 05-01-2026 18:05. | Jangan menetapkan day-first atau month-first t... | Semua timestamp berhasil diparsing sesuai konv... | Approved |
| 4 | CR-05 | F-07 | swiftbite_order_items.csv | OI28-00146 muncul dua kali identik pada O28-0056 | FIX | Bandingkan baris duplikat dengan sumber transa... | OI28-00146 muncul dua kali | Jangan drop_duplicates langsung pada data final | Keputusan duplikasi didukung sumber; setelah t... | 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
# ============================================================
# Hanya mengubah dataframe *_clean. Dataframe *_raw tidak disentuh.
# CR-01 — Standarisasi customer_segment
# Source Finding: F-02
# Evidence basis: "gold" dan "Silver " setara Gold dan Silver
# Validation criteria: hanya Gold/Silver/Bronze/Premium + 3 NULL
customers_clean["customer_segment"] = (
customers_clean["customer_segment"].str.strip().str.title()
) # NULL tetap NULL (F-01 tidak diimputasi)
# CR-02 — phone_number dijaga sebagai teks 12 digit
# Source Finding: F-03
# Evidence basis: raw JSON berupa teks berawalan 0; pd.read_json mengubahnya ke int64
# Validation criteria: 90/90 panjang 12
customers_clean["phone_number"] = (
customers_clean["phone_number"].astype(str).str.zfill(12)
)
# CR-03 — Standarisasi order_status
# Source Finding: F-04
# Evidence basis: "Delivered" dan "DELIVERED " setara DELIVERED
# Validation criteria: hanya DELIVERED dan CANCELLED
orders_clean["order_status"] = (
orders_clean["order_status"].str.strip().str.upper()
)
# CR-04 — Seragamkan order_timestamp ke YYYY-MM-DD HH:MM:SS
# Source Finding: F-05
# Evidence basis: format non-ISO day-first; 05-01-2026 = 5 Jan 2026 (rentang tanggal data)
# Validation criteria: 140/140 format ISO, 0 gagal parse
def parse_order_ts(value):
s = str(value).strip()
for fmt in ("%Y-%m-%d %H:%M:%S", "%d/%m/%Y %H:%M",
"%Y/%m/%d %H:%M", "%d-%m-%Y %H:%M"):
try:
return pd.to_datetime(s, format=fmt).strftime("%Y-%m-%d %H:%M:%S")
except ValueError:
pass
raise ValueError(f"Format order_timestamp tidak dikenali: {s}")
orders_clean["order_timestamp"] = orders_clean["order_timestamp"].map(parse_order_ts)
# CR-05 — quantity "3 pcs" menjadi angka
# Source Finding: F-08
# Evidence basis: line_amount 66.000 = 3 x 22.000
# Validation criteria: quantity numerik; V-09 mismatch 0
order_items_clean["quantity"] = (
order_items_clean["quantity"].astype(str).str.extract(r"(\d+)")[0].astype(int)
)
# CR-06 — Hapus duplikat persis di order_items (FIX)
# Source Finding: F-07
# Evidence basis: OI28-00146 identik di seluruh kolom; subtotal O28-0056 hanya cocok tanpa salinan kedua
# Validation criteria: order_item_id unik; V-11 mismatch 0
order_items_clean = order_items_clean.drop_duplicates(keep="first").reset_index(drop=True)
# CR-07 — Trim payment_method
# Source Finding: F-13
# Evidence basis: "GoPay " setara GoPay
# Validation criteria: 5 metode pembayaran
payments_clean["payment_method"] = payments_clean["payment_method"].str.strip()
# CR-08 — Seragamkan payment_timestamp ke YYYY-MM-DD HH:MM:SS
# Source Finding: F-14
# Evidence basis: P28-00068 "2026/03/08 11:10" cocok dengan urutan payment_id tetangga
# Validation criteria: 154/154 format ISO
payments_clean["payment_timestamp"] = pd.to_datetime(
payments_clean["payment_timestamp"].astype(str).str.replace("/", "-", regex=False),
format="mixed",
).dt.strftime("%Y-%m-%d %H:%M:%S")
# CR-09 — Hapus duplikat persis di payments (FIX)
# Source Finding: F-12
# Evidence basis: P28-00028 identik di 6 kolom dan berada di akhir file
# Validation criteria: payment_id unik; repeated order_id (retry) TIDAK dihapus
payments_clean = payments_clean.drop_duplicates(keep="first").reset_index(drop=True)
# CR-10 — Standarisasi price_tier
# Source Finding: F-15
# Evidence basis: "mid " setara Mid
# Validation criteria: hanya Mid, Premium, Budget
restaurants_clean["price_tier"] = restaurants_clean["price_tier"].str.strip().str.title()
# CR-11 — Standarisasi active_status
# Source Finding: F-16
# Evidence basis: "active" setara Active
# Validation criteria: hanya Active dan Inactive
restaurants_clean["active_status"] = restaurants_clean["active_status"].str.strip().str.title()
print("Cleaning selesai (customers, orders, order_items, payments, restaurants):",
len(customers_clean), len(orders_clean), len(order_items_clean),
len(payments_clean), len(restaurants_clean))
Cleaning selesai (customers, orders, order_items, payments, restaurants): 90 140 373 154 18
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 = [
{
"log_id": "CL-01",
"rule_id": "CR-01",
"dataset": "swiftbite_customers.json",
"affected_records_scope": "C28-023, C28-064",
"action_performed": "Trim + title case pada customer_segment",
"before_evidence": "gold 1; \"Silver \" 1; Gold 22; Silver 22; NULL 3",
"after_evidence": "Gold 23; Silver 23; Bronze 21; Premium 20; NULL 3",
"rows_records_affected": 2,
"result": "Validated",
"notes": "3 NULL (C28-012, C28-045, C28-078) sengaja tidak diubah (F-01)",
},
{
"log_id": "CL-02",
"rule_id": "CR-02",
"dataset": "swiftbite_customers.json",
"affected_records_scope": "Seluruh 90 baris",
"action_performed": "Phone dijadikan teks 12 digit (zero-pad)",
"before_evidence": "int64, mis. 82800000001 (11 digit)",
"after_evidence": "Teks, mis. 082800000001; 90/90 panjang 12",
"rows_records_affected": 90,
"result": "Validated",
"notes": "Dihitung terhadap hasil pd.read_json Starter; di raw JSON nilainya sudah teks",
},
{
"log_id": "CL-03",
"rule_id": "CR-03",
"dataset": "swiftbite_orders.xlsx",
"affected_records_scope": "O28-0048, O28-0096",
"action_performed": "Trim + upper pada order_status",
"before_evidence": "DELIVERED 128; Delivered 1; \"DELIVERED \" 1; CANCELLED 10",
"after_evidence": "DELIVERED 130; CANCELLED 10",
"rows_records_affected": 2,
"result": "Validated",
"notes": "",
},
{
"log_id": "CL-04",
"rule_id": "CR-04",
"dataset": "swiftbite_orders.xlsx",
"affected_records_scope": "O28-0027, O28-0084, O28-0119",
"action_performed": "Standarisasi order_timestamp ke YYYY-MM-DD HH:MM:SS",
"before_evidence": "22/03/2026 13:32; 2026/02/25 19:05; 05-01-2026 18:05",
"after_evidence": "2026-03-22 13:32:00; 2026-02-25 19:05:00; 2026-01-05 18:05:00",
"rows_records_affected": 3,
"result": "Validated",
"notes": "O28-0119 dibaca day-first (asumsi berbasis rentang tanggal, F-05)",
},
{
"log_id": "CL-05",
"rule_id": "CR-05",
"dataset": "swiftbite_order_items.csv",
"affected_records_scope": "OI28-00192 (O28-0073)",
"action_performed": "Ekstrak digit dari quantity",
"before_evidence": "quantity \"3 pcs\" (teks)",
"after_evidence": "quantity 3 (integer)",
"rows_records_affected": 1,
"result": "Validated",
"notes": "line_amount 66.000 = 3 x 22.000",
},
{
"log_id": "CL-06",
"rule_id": "CR-06",
"dataset": "swiftbite_order_items.csv",
"affected_records_scope": "OI28-00146 (O28-0056), baris ke-374",
"action_performed": "Hapus salinan kedua yang identik",
"before_evidence": "374 baris; SUM item O28-0056 = 533.000 vs subtotal 407.000",
"after_evidence": "373 baris; SUM item O28-0056 = 407.000 = subtotal",
"rows_records_affected": 1,
"result": "Validated",
"notes": "Mismatch rekonsiliasi item vs subtotal turun dari 1 menjadi 0",
},
{
"log_id": "CL-07",
"rule_id": "CR-07",
"dataset": "swiftbite_payments.txt",
"affected_records_scope": "P28-00097 (O28-0088)",
"action_performed": "Trim pada payment_method",
"before_evidence": "GoPay 33; \"GoPay \" 1",
"after_evidence": "GoPay 34",
"rows_records_affected": 1,
"result": "Validated",
"notes": "",
},
{
"log_id": "CL-08",
"rule_id": "CR-08",
"dataset": "swiftbite_payments.txt",
"affected_records_scope": "P28-00068 (O28-0062)",
"action_performed": "Standarisasi payment_timestamp",
"before_evidence": "2026/03/08 11:10",
"after_evidence": "2026-03-08 11:10:00",
"rows_records_affected": 1,
"result": "Validated",
"notes": "",
},
{
"log_id": "CL-09",
"rule_id": "CR-09",
"dataset": "swiftbite_payments.txt",
"affected_records_scope": "P28-00028 (O28-0026), baris ke-155",
"action_performed": "Hapus salinan kedua yang identik",
"before_evidence": "155 baris; payment_id unik 154; FAILED 25",
"after_evidence": "154 baris; payment_id unik 154; FAILED 24",
"rows_records_affected": 1,
"result": "Validated",
"notes": "order_id berulang (14 order retry) tidak disentuh",
},
{
"log_id": "CL-10",
"rule_id": "CR-10",
"dataset": "swiftbite_restaurants.csv",
"affected_records_scope": "R28-07",
"action_performed": "Trim + title case pada price_tier",
"before_evidence": "Mid 6; \"mid \" 1",
"after_evidence": "Mid 7; Premium 6; Budget 5",
"rows_records_affected": 1,
"result": "Validated",
"notes": "",
},
{
"log_id": "CL-11",
"rule_id": "CR-11",
"dataset": "swiftbite_restaurants.csv",
"affected_records_scope": "R28-15",
"action_performed": "Title case pada active_status",
"before_evidence": "Active 16; active 1; Inactive 1",
"after_evidence": "Active 17; Inactive 1",
"rows_records_affected": 1,
"result": "Validated",
"notes": "",
},
]
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 | swiftbite_customers.json | C28-023, C28-064 | Trim + title case pada customer_segment | gold 1; "Silver " 1; Gold 22; Silver 22; NULL 3 | Gold 23; Silver 23; Bronze 21; Premium 20; NULL 3 | 2 | Validated | 3 NULL (C28-012, C28-045, C28-078) sengaja tid... |
| 1 | CL-02 | CR-02 | swiftbite_customers.json | Seluruh 90 baris | Phone dijadikan teks 12 digit (zero-pad) | int64, mis. 82800000001 (11 digit) | Teks, mis. 082800000001; 90/90 panjang 12 | 90 | Validated | Dihitung terhadap hasil pd.read_json Starter; ... |
| 2 | CL-03 | CR-03 | swiftbite_orders.xlsx | O28-0048, O28-0096 | Trim + upper pada order_status | DELIVERED 128; Delivered 1; "DELIVERED " 1; CA... | DELIVERED 130; CANCELLED 10 | 2 | Validated | |
| 3 | CL-04 | CR-04 | swiftbite_orders.xlsx | O28-0027, O28-0084, O28-0119 | Standarisasi order_timestamp ke YYYY-MM-DD HH:... | 22/03/2026 13:32; 2026/02/25 19:05; 05-01-2026... | 2026-03-22 13:32:00; 2026-02-25 19:05:00; 2026... | 3 | Validated | O28-0119 dibaca day-first (asumsi berbasis ren... |
| 4 | CL-05 | CR-05 | swiftbite_order_items.csv | OI28-00192 (O28-0073) | Ekstrak digit dari quantity | quantity "3 pcs" (teks) | quantity 3 (integer) | 1 | Validated | line_amount 66.000 = 3 x 22.000 |
| 5 | CL-06 | CR-06 | swiftbite_order_items.csv | OI28-00146 (O28-0056), baris ke-374 | Hapus salinan kedua yang identik | 374 baris; SUM item O28-0056 = 533.000 vs subt... | 373 baris; SUM item O28-0056 = 407.000 = subtotal | 1 | Validated | Mismatch rekonsiliasi item vs subtotal turun d... |
| 6 | CL-07 | CR-07 | swiftbite_payments.txt | P28-00097 (O28-0088) | Trim pada payment_method | GoPay 33; "GoPay " 1 | GoPay 34 | 1 | Validated | |
| 7 | CL-08 | CR-08 | swiftbite_payments.txt | P28-00068 (O28-0062) | Standarisasi payment_timestamp | 2026/03/08 11:10 | 2026-03-08 11:10:00 | 1 | Validated | |
| 8 | CL-09 | CR-09 | swiftbite_payments.txt | P28-00028 (O28-0026), baris ke-155 | Hapus salinan kedua yang identik | 155 baris; payment_id unik 154; FAILED 25 | 154 baris; payment_id unik 154; FAILED 24 | 1 | Validated | order_id berulang (14 order retry) tidak disentuh |
| 9 | CL-10 | CR-10 | swiftbite_restaurants.csv | R28-07 | Trim + title case pada price_tier | Mid 6; "mid " 1 | Mid 7; Premium 6; Budget 5 | 1 | Validated | |
| 10 | CL-11 | CR-11 | swiftbite_restaurants.csv | R28-15 | Title case pada active_status | Active 16; active 1; Inactive 1 | Active 17; Inactive 1 | 1 | Validated |
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
# ========================================================
# V-01 — Schema
schema_rows = []
for label, required in REQUIRED_COLUMNS.items():
missing_cols = sorted(required - set(clean_tables[label].columns))
schema_rows.append({
"dataset": label,
"rows": len(clean_tables[label]),
"missing_required_columns": ", ".join(missing_cols),
})
record_check(
"V-01", "Schema", "All clean datasets",
"Apakah seluruh required column masih tersedia?",
"Required columns available",
schema_rows,
all(r["missing_required_columns"] == "" for r in schema_rows),
)
# V-02 — Customers key
result = key_check(customers_clean, "customer_id", "Customers")
record_check(
"V-02", "Key", "Customers",
"Apakah customer_id valid sesuai grain customer?",
"No null key; uniqueness sesuai grain",
result.to_dict("records")[0],
bool(result.loc[0, "candidate_key_unique"]),
)
# V-03 — Orders key
result = key_check(orders_clean, "order_id", "Orders")
record_check(
"V-03", "Key", "Orders",
"Apakah order_id valid sesuai grain order?",
"No null key; one row per order",
result.to_dict("records")[0],
bool(result.loc[0, "candidate_key_unique"]),
)
# V-04 — Order Items key (CR-06)
result_id = key_check(order_items_clean, "order_item_id", "Order Items")
result_bk = key_check(order_items_clean, ["order_id", "line_number"], "Order Items business key")
record_check(
"V-04", "Key", "Order Items",
"Apakah order_item_id / candidate business key valid?",
"No invalid duplicate key",
{"order_item_id": result_id.to_dict("records")[0],
"order_id+line_number": result_bk.to_dict("records")[0]},
bool(result_id.loc[0, "candidate_key_unique"] and result_bk.loc[0, "candidate_key_unique"]),
related_rule_id="CR-06",
notes="Sebelum cleaning: OI28-00146 muncul dua kali.",
)
# V-05 — Payments key (CR-09); repeated order_id valid menurut grain
result = key_check(payments_clean, "payment_id", "Payments")
repeated_order_rows = int(payments_clean["order_id"].duplicated().sum())
record_check(
"V-05", "Key", "Payments",
"Apakah payment_id unique tanpa menghapus repeated order_id yang valid?",
"payment_id unique; repeated order_id allowed by grain",
{**result.to_dict("records")[0], "repeated_order_id_rows": repeated_order_rows},
bool(result.loc[0, "candidate_key_unique"]) and repeated_order_rows > 0,
related_rule_id="CR-09",
notes="Sebelum cleaning: P28-00028 muncul dua kali. Retry payment per order_id tetap dipertahankan.",
)
else:
print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes: Customers (90, 7) Orders (140, 9) Order Items (373, 9) Payments (154, 6) Restaurants (18, 6)
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
# ========================================================
ISO_TS = r"^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$"
# V-06 — Customers: kategori dan representasi missing (CR-01, CR-02)
seg_values = set(customers_clean["customer_segment"].dropna().unique())
seg_null = int(customers_clean["customer_segment"].isna().sum())
phone_len_ok = bool((customers_clean["phone_number"].astype(str).str.len() == 12).all())
record_check(
"V-06", "Value", "Customers / categories",
"Apakah category dan missing representation sudah konsisten?",
"No unexpected canonical-category issue",
{"segment_values": sorted(seg_values), "segment_null": seg_null, "phone_len_12_all": phone_len_ok},
seg_values == {"Gold", "Silver", "Bronze", "Premium"} and phone_len_ok,
related_rule_id="CR-01, CR-02",
notes="3 NULL customer_segment sengaja tidak diimputasi (F-01).",
)
# V-07 — Orders: status dan format timestamp (CR-03, CR-04)
status_values = orders_clean["order_status"].value_counts().to_dict()
ts_iso = int(orders_clean["order_timestamp"].astype(str).str.match(ISO_TS).sum())
record_check(
"V-07", "Value", "Orders / categories",
"Apakah order status dan representation sudah konsisten?",
"Only approved canonical values",
{"order_status": status_values, "order_timestamp_iso": ts_iso, "rows": len(orders_clean)},
set(status_values) == {"DELIVERED", "CANCELLED"} and ts_iso == len(orders_clean),
related_rule_id="CR-03, CR-04",
)
# V-08 — Payments / Restaurants (CR-07, CR-08, CR-10, CR-11)
pay_methods = set(payments_clean["payment_method"].unique())
tiers = set(restaurants_clean["price_tier"].unique())
actives = set(restaurants_clean["active_status"].unique())
pay_ts_iso = int(payments_clean["payment_timestamp"].astype(str).str.match(ISO_TS).sum())
record_check(
"V-08", "Value", "Payments / Restaurants",
"Apakah category representation sudah konsisten?",
"Only approved canonical values",
{"payment_method": sorted(pay_methods), "price_tier": sorted(tiers),
"active_status": sorted(actives), "payment_timestamp_iso": pay_ts_iso},
pay_methods == {"GoPay", "Credit Card", "QRIS", "OVO", "Bank Transfer"}
and tiers == {"Budget", "Mid", "Premium"}
and actives == {"Active", "Inactive"}
and pay_ts_iso == len(payments_clean),
related_rule_id="CR-07, CR-08, CR-10, CR-11",
)
# V-09 — Order Items formula (CR-05)
qty = pd.to_numeric(order_items_clean["quantity"], errors="coerce")
expected_line = (
qty * pd.to_numeric(order_items_clean["unit_price"], errors="coerce")
- pd.to_numeric(order_items_clean["line_discount"], errors="coerce")
)
line_mismatch = int((expected_line != pd.to_numeric(order_items_clean["line_amount"], errors="coerce")).sum())
record_check(
"V-09", "Business Rule", "Order Items",
"Apakah line_amount memenuhi formula item?",
"quantity × unit_price - line_discount = line_amount",
{"rows": len(order_items_clean), "mismatch": line_mismatch},
line_mismatch == 0,
related_rule_id="CR-05",
)
# V-10 — Orders formula
expected_total = (
orders_clean["subtotal_amount"] + orders_clean["delivery_fee"] - orders_clean["discount_amount"]
)
total_mismatch = int((expected_total != orders_clean["total_amount"]).sum())
record_check(
"V-10", "Business Rule", "Orders",
"Apakah total_amount memenuhi formula order?",
"subtotal + delivery_fee - discount_amount = total_amount",
{"rows": len(orders_clean), "mismatch": total_mismatch},
total_mismatch == 0,
)
# V-11 — Orders ↔ Order Items reconciliation (CR-06)
item_sum = order_items_clean.groupby("order_id")["line_amount"].sum()
recon = orders_clean.set_index("order_id")["subtotal_amount"].sub(item_sum, fill_value=0)
recon_mismatch = int((recon != 0).sum())
record_check(
"V-11", "Cross-table", "Orders ↔ Order Items",
"Apakah subtotal order konsisten dengan detail item?",
"SUM(line_amount) = subtotal_amount",
{"orders": len(orders_clean), "mismatch": recon_mismatch},
recon_mismatch == 0,
related_rule_id="CR-06",
notes="Sebelum cleaning: 1 mismatch (O28-0056, selisih -126.000).",
)
# V-12 s.d. V-14 — Foreign key checks pada clean data
fk_cust = fk_check(orders_clean, customers_clean, "customer_id", "customer_id", "Orders → Customers")
record_check(
"V-12", "Cross-table", "Orders → Customers",
"Apakah seluruh customer reference valid?",
"Unmatched customer = 0",
fk_cust, fk_cust["unmatched_count"] == 0,
)
fk_rest = fk_check(orders_clean, restaurants_clean, "restaurant_id", "restaurant_id", "Orders → Restaurants")
record_check(
"V-13", "Cross-table", "Orders → Restaurants",
"Apakah seluruh restaurant reference valid?",
"Unmatched restaurant = 0",
fk_rest, fk_rest["unmatched_count"] == 0,
)
fk_items = fk_check(order_items_clean, orders_clean, "order_id", "order_id", "Order Items → Orders")
fk_pay = fk_check(payments_clean, orders_clean, "order_id", "order_id", "Payments → Orders")
record_check(
"V-14", "Cross-table", "Order Items / Payments → Orders",
"Apakah seluruh order reference valid?",
"Orphan order reference = 0",
{"order_items": fk_items, "payments": fk_pay},
fk_items["unmatched_count"] == 0 and fk_pay["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
# ============================================================
if DATA_READY:
o_raw = orders_raw.set_index("order_id")
o_clean = orders_clean.set_index("order_id")
p_raw = payments_raw.drop_duplicates().set_index("payment_id")
p_clean = payments_clean.set_index("payment_id")
def status_sets(df):
return df.groupby("order_id")["payment_status"].agg(set)
raw_sets, clean_sets = status_sets(payments_raw.drop_duplicates()), status_sets(payments_clean)
retry_raw = int(sum(s == {"SUCCESS", "FAILED"} for s in raw_sets))
retry_clean = int(sum(s == {"SUCCESS", "FAILED"} for s in clean_sets))
only_failed_clean = int(sum(s == {"FAILED"} for s in clean_sets))
keep_checks = {
# F-06 — O28-0131 (outlier order yang konsisten)
"F-06 O28-0131 subtotal/total utuh": (
o_clean.loc["O28-0131", ["subtotal_amount", "total_amount"]].tolist()
== o_raw.loc["O28-0131", ["subtotal_amount", "total_amount"]].tolist()
== [729000, 724000]
),
# F-11 — P28-00144 (outlier payment yang konsisten)
"F-11 P28-00144 amount utuh": (
int(p_clean.loc["P28-00144", "amount"]) == int(p_raw.loc["P28-00144", "amount"]) == 724000
),
# F-19 — retry payment (order_id berulang) tidak dihapus
"F-19 14 order retry + 10 order only-FAILED utuh": (
retry_raw == retry_clean == 14 and only_failed_clean == 10
),
# F-20 — 8 order ke restoran Inactive tidak dihapus
"F-20 8 order R28-18 utuh": (
int((orders_clean["restaurant_id"] == "R28-18").sum())
== int((orders_raw["restaurant_id"] == "R28-18").sum()) == 8
and len(orders_clean) == len(orders_raw)
),
# INVESTIGATE (F-01, F-09, F-10) tidak diubah
"F-09 quantity 18 (OI28-00293) tidak diubah": (
int(order_items_clean.loc[order_items_clean["order_item_id"] == "OI28-00293", "quantity"].iloc[0]) == 18
),
"F-10 kategori item tidak diubah (hanya duplikat dihapus)": (
int(((order_items_clean["item_name"] == "Gyoza") & (order_items_clean["item_category"] == "Drink")).sum())
== int(((order_items_raw["item_name"] == "Gyoza") & (order_items_raw["item_category"] == "Drink")).sum()) - 1
),
"F-01 3 NULL customer_segment tidak diimputasi": (
int(customers_clean["customer_segment"].isna().sum()) == 3
),
}
record_check(
"V-15", "Judgment", "KEEP findings",
"Apakah legitimate anomaly yang diputuskan KEEP masih dipertahankan?",
"KEEP findings remain unless new evidence justifies change",
keep_checks,
all(keep_checks.values()),
notes="Mencakup F-06, F-11, F-19, F-20 (KEEP) dan memastikan item INVESTIGATE tidak ikut diubah.",
)
else:
print("VALIDATION SKIPPED — DATA READY GATE belum 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 datasets | Apakah seluruh required column masih tersedia? | Required columns available | [{'dataset': 'Customers', 'rows': 90, 'missing... | PASS | ||
| 1 | V-02 | Key | Customers | Apakah customer_id valid sesuai grain customer? | No null key; uniqueness sesuai grain | {'dataset': 'Customers', 'key_cols': 'customer... | PASS | ||
| 2 | V-03 | Key | Orders | Apakah order_id valid sesuai grain order? | No null key; one row per order | {'dataset': 'Orders', 'key_cols': 'order_id', ... | PASS | ||
| 3 | V-04 | Key | Order Items | Apakah order_item_id / candidate business key ... | No invalid duplicate key | {'order_item_id': {'dataset': 'Order Items', '... | PASS | CR-06 | Sebelum cleaning: OI28-00146 muncul dua kali. |
| 4 | V-05 | Key | Payments | Apakah payment_id unique tanpa menghapus repea... | payment_id unique; repeated order_id allowed b... | {'dataset': 'Payments', 'key_cols': 'payment_i... | PASS | CR-09 | Sebelum cleaning: P28-00028 muncul dua kali. R... |
| 5 | V-06 | Value | Customers / categories | Apakah category dan missing representation sud... | No unexpected canonical-category issue | {'segment_values': ['Bronze', 'Gold', 'Premium... | PASS | CR-01, CR-02 | 3 NULL customer_segment sengaja tidak diimputa... |
| 6 | V-07 | Value | Orders / categories | Apakah order status dan representation sudah k... | Only approved canonical values | {'order_status': {'DELIVERED': 130, 'CANCELLED... | PASS | CR-03, CR-04 | |
| 7 | V-08 | Value | Payments / Restaurants | Apakah category representation sudah konsisten? | Only approved canonical values | {'payment_method': ['Bank Transfer', 'Credit C... | PASS | CR-07, CR-08, CR-10, CR-11 | |
| 8 | V-09 | Business Rule | Order Items | Apakah line_amount memenuhi formula item? | quantity × unit_price - line_discount = line_a... | {'rows': 373, 'mismatch': 0} | PASS | CR-05 | |
| 9 | V-10 | Business Rule | Orders | Apakah total_amount memenuhi formula order? | subtotal + delivery_fee - discount_amount = to... | {'rows': 140, 'mismatch': 0} | PASS | ||
| 10 | V-11 | Cross-table | Orders ↔ Order Items | Apakah subtotal order konsisten dengan detail ... | SUM(line_amount) = subtotal_amount | {'orders': 140, 'mismatch': 0} | PASS | CR-06 | Sebelum cleaning: 1 mismatch (O28-0056, selisi... |
| 11 | V-12 | Cross-table | Orders → Customers | Apakah seluruh customer reference valid? | Unmatched customer = 0 | {'relationship': 'Orders → Customers', 'child_... | PASS | ||
| 12 | V-13 | Cross-table | Orders → Restaurants | Apakah seluruh restaurant reference valid? | Unmatched restaurant = 0 | {'relationship': 'Orders → Restaurants', 'chil... | PASS | ||
| 13 | V-14 | Cross-table | Order Items / Payments → Orders | Apakah seluruh order reference valid? | Orphan order reference = 0 | {'order_items': {'relationship': 'Order Items ... | PASS | ||
| 14 | V-15 | Judgment | KEEP findings | Apakah legitimate anomaly yang diputuskan KEEP... | KEEP findings remain unless new evidence justi... | {'F-06 O28-0131 subtotal/total utuh': True, 'F... | PASS | Mencakup F-06, F-11, F-19, F-20 (KEEP) dan mem... |
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 = (
"Seluruh V-01 sampai V-15 PASS dan Raw Integrity PASS. 11 cleaning rule (9 STANDARDIZE, 2 FIX) dijalankan hanya"
" pada working copy dan dapat ditelusuri dari Finding ID ke Rule ID, Cleaning Log, lalu Validation. Hasilnya: 2"
" baris duplikat dihapus (OI28-00146 dan P28-00028), 1 mismatch subtotal order terselesaikan, serta penulisan k"
"ategori, tipe data, dan format waktu diseragamkan. Finding KEEP (F-06, F-11, F-19, F-20) tetap utuh. Finding I"
"NVESTIGATE (F-01, F-09, F-10, F-17, F-18) tidak dikoreksi karena tidak ada evidence untuk menentukan nilai yan"
"g benar; data tetap dipakai dengan catatan keterbatasan (3 segment NULL, quantity 18, kategori item tidak stab"
"il, urutan waktu payment vs order, dan 6 order sebelum signup). Asumsi: O28-0119 dibaca day-first (5 Januari 2"
"026) berdasarkan rentang tanggal data."
)
print(FINAL_JUDGMENT_RATIONALE)
PM1 DATA READINESS GATE: PASS Seluruh V-01 sampai V-15 PASS dan Raw Integrity PASS. 11 cleaning rule (9 STANDARDIZE, 2 FIX) dijalankan hanya pada working copy dan dapat ditelusuri dari Finding ID ke Rule ID, Cleaning Log, lalu Validation. Hasilnya: 2 baris duplikat dihapus (OI28-00146 dan P28-00028), 1 mismatch subtotal order terselesaikan, serta penulisan kategori, tipe data, dan format waktu diseragamkan. Finding KEEP (F-06, F-11, F-19, F-20) tetap utuh. Finding INVESTIGATE (F-01, F-09, F-10, F-17, F-18) tidak dikoreksi karena tidak ada evidence untuk menentukan nilai yang benar; data tetap dipakai dengan catatan keterbatasan (3 segment NULL, quantity 18, kategori item tidak stabil, urutan waktu payment vs order, dan 6 order sebelum signup). Asumsi: O28-0119 dibaca day-first (5 Januari 2026) berdasarkan rentang tanggal data.
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: D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
True
Evidence boleh diekspor untuk review, tetapi **clean package final tetap hanya boleh disimpan saat FINAL_GATE: PASS**.
def export_evidence():
findings.to_csv(OUTPUT_DIR / "pm1_findings.csv", index=False)
cleaning_rules.to_csv(OUTPUT_DIR / "pm1_cleaning_rulebook.csv", index=False)
cleaning_log.to_csv(OUTPUT_DIR / "pm1_cleaning_log.csv", index=False)
validation_df.to_csv(OUTPUT_DIR / "pm1_validation_results.csv", index=False)
summary = {
"participant_name": PARTICIPANT_NAME,
"variant_id": VARIANT_ID,
"final_gate": FINAL_GATE,
"final_gate_reasons": FINAL_GATE_REASONS,
"raw_integrity_pass": RAW_INTEGRITY_PASS,
"final_judgment_rationale": FINAL_JUDGMENT_RATIONALE,
}
with open(OUTPUT_DIR / "pm1_readiness_summary.json", "w", encoding="utf-8") as f:
json.dump(summary, f, ensure_ascii=False, indent=2)
print("EVIDENCE EXPORT: PASS")
print("Saved to:", OUTPUT_DIR)
return True
# Hapus tanda # pada baris berikut setelah evidence sudah diisi:
export_evidence()
EVIDENCE EXPORT: PASS Saved to: D:\PT PLan Bootcamp\Bootcamp_Data_Analyst\outputs\pm1_swiftbite
True
### Technical
- [ ] `IDENTITY GATE: PASS`
- [ ] `TECHNICAL GATE: PASS`
- [ ] `SOURCE ACCESS CHECK: PASS`
- [ ] `RAW LOAD GATE: PASS`
- [ ] `SCHEMA GATE: PASS`
- [ ] `RAW INTEGRITY CHECK: PASS`
### Intake, Profiling & Judgment
- [ ] 5 source berhasil dibaca.
- [ ] Grain dan candidate key sudah diperiksa.
- [ ] Relationship utama sudah diperiksa.
- [ ] Material findings sudah diverifikasi.
- [ ] FIX / STANDARDIZE / KEEP / INVESTIGATE memiliki evidence dan alasan.
- [ ] Finding yang menghasilkan cleaning memiliki Rule ID.
### Cleaning & Validation
- [ ] Rulebook dibuat sebelum implementation.
- [ ] Cleaning hanya dilakukan pada working copy.
- [ ] Cleaning Log lengkap.
- [ ] `V-01` sampai `V-15` sudah dicatat.
- [ ] `PM1 DATA READINESS GATE: PASS`
- [ ] 5 clean CSV berhasil diekspor.
```text
Raw Data
↓
Technical & Source Gate
↓
Understand Grain & Key
↓
Profile → Find → Verify → Judge
↓
Define Cleaning Rule
↓
Clean Working Copy
↓
Validate V-01 ... V-15
↓
Raw Integrity Check
↓
FINAL GATE
↓
Export Clean Package
```