# Praktik Mandiri 1 — Data Readiness Check & Cleaning Validation
## Participant Starter Notebook — Guided Technical Version
Notebook ini membantu kamu menjalankan workflow PM1 dengan **technical guardrails** agar tidak salah folder, salah source, atau salah export.
**Alur PM1:**
**Setup → Technical Check → Load → Intake → Grain & Key → Profiling → Findings → Verification → Rulebook → Cleaning → Validation → Raw Integrity → Export → Final Gate**
### Sebelum RUN
1. Buka **folder `Bootcamp_Data_Analyst` sebagai workspace** di Antigravity.
2. Simpan notebook ini di folder `notebooks/`.
3. Extract package `SB-Pxx`, lalu letakkan **5 raw files langsung** di `data/raw/pm1_swiftbite/`.
4. Buka `PM1_Data_Readiness_Working_Paper.xlsx` sebagai tempat mencatat decision & evidence.
5. Jalankan notebook **dari atas ke bawah**.
> Jika `TECHNICAL GATE: PASS`, **jangan mengubah path** di notebook.
### Guardrails
- Raw/source **tidak boleh diubah atau ditimpa**.
- Jangan menghapus row hanya karena terlihat aneh.
- Repeated `order_id` belum tentu duplicate.
- Finding belum otomatis menjadi cleaning action.
- Material change harus dapat ditelusuri: **Finding ID → Rule ID → Cleaning Log → Validation Evidence**.
- Notebook membantu sisi teknis. **Judgment, cleaning rule, dan interpretation tetap pekerjaan peserta.**
0. Identitas Praktik — EDIT BAGIAN INI
Ubah hanya **Nama** dan **Variant ID** sesuai package yang kamu terima.
Contoh Variant ID: `SB-P01`, `SB-P12`, `SB-P28`.
# ============================================================
# EDIT AREA 1 — IDENTITAS PESERTA
# ============================================================
PARTICIPANT_NAME = "Naurah"
VARIANT_ID = "SB-P02"
name_ok = PARTICIPANT_NAME.strip() != ""
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 : Naurah
Variant ID : SB-P02
1. Technical Check — Workspace & Folder
Notebook akan mencari **Workspace Starter yang sama dari Sesi 1** berdasarkan keberadaan folder `data/` dan `notebooks/`.
**Jangan membuat workspace baru. Jangan copy-paste absolute path.**
Jika output menunjukkan `TECHNICAL GATE: PASS`, lanjut ke cell berikutnya tanpa mengubah path.
from pathlib import Path
import json
import hashlib
import pandas as pd
import numpy as np
try:
import duckdb
DUCKDB_AVAILABLE = True
except ImportError:
duckdb = None
DUCKDB_AVAILABLE = False
pd.set_option("display.max_columns", 100)
pd.set_option("display.max_rows", 100)
pd.set_option("display.width", 180)
def find_workspace_root(start=None):
start = Path(start or Path.cwd()).resolve()
for p in [start, *start.parents]:
if (p / "data").is_dir() and (p / "notebooks").is_dir():
return p
return None
WORKSPACE_ROOT = find_workspace_root()
if WORKSPACE_ROOT is None:
TECHNICAL_GATE_PASS = False
RAW_DIR = Path("__WORKSPACE_NOT_FOUND__")
PROCESSED_DIR = Path("__WORKSPACE_NOT_FOUND__")
OUTPUT_DIR = Path("__WORKSPACE_NOT_FOUND__")
DOCS_DIR = Path("__WORKSPACE_NOT_FOUND__")
print("TECHNICAL GATE: FAIL")
print("→ Workspace Starter tidak ditemukan.")
print("→ Di Antigravity, buka folder Bootcamp_Data_Analyst sebagai workspace.")
print("→ Pastikan folder data/ dan notebooks/ terlihat di Explorer.")
print("→ Setelah itu RUN ulang cell ini. Jangan edit path secara manual.")
else:
TECHNICAL_GATE_PASS = True
RAW_DIR = WORKSPACE_ROOT / "data" / "raw" / "pm1_swiftbite"
PROCESSED_DIR = WORKSPACE_ROOT / "data" / "processed" / "pm1_swiftbite"
OUTPUT_DIR = WORKSPACE_ROOT / "outputs" / "pm1_swiftbite"
DOCS_DIR = WORKSPACE_ROOT / "docs" / "pm1_swiftbite"
for folder in [PROCESSED_DIR, OUTPUT_DIR, DOCS_DIR]:
folder.mkdir(parents=True, exist_ok=True)
print("TECHNICAL GATE: PASS")
print("WORKSPACE_ROOT :", WORKSPACE_ROOT)
print("RAW_DIR :", RAW_DIR)
print("PROCESSED_DIR :", PROCESSED_DIR)
print("OUTPUT_DIR :", OUTPUT_DIR)
print("DOCS_DIR :", DOCS_DIR)
TECHNICAL GATE: PASS
WORKSPACE_ROOT : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst
RAW_DIR : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\raw\pm1_swiftbite
PROCESSED_DIR : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
OUTPUT_DIR : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\outputs\pm1_swiftbite
DOCS_DIR : C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\docs\pm1_swiftbite
Environment Smoke Test — RUN
Tujuan cell ini hanya memastikan library utama tersedia. Jika DuckDB belum aktif, workflow utama starter tetap dapat berjalan dengan pandas.
print("pandas :", pd.__version__)
try:
import openpyxl
EXCEL_ENGINE_AVAILABLE = True
print("openpyxl:", openpyxl.__version__)
except ImportError:
EXCEL_ENGINE_AVAILABLE = False
print("openpyxl: NOT AVAILABLE")
if DUCKDB_AVAILABLE:
print("DuckDB :", duckdb.__version__)
try:
con = duckdb.connect()
print("DuckDB connection: OK")
except Exception as e:
print("DuckDB connection: WARNING —", type(e).__name__, str(e))
else:
print("DuckDB : NOT AVAILABLE")
print("INFO: workflow utama notebook tetap dapat dijalankan dengan pandas.")
if not EXCEL_ENGINE_AVAILABLE:
print("ENVIRONMENT CHECK: NEEDS FIX")
print("→ File XLSX membutuhkan openpyxl. Aktifkan environment sesuai setup Sesi 1.")
else:
print("ENVIRONMENT CHECK: PASS")
pandas : 3.0.6
openpyxl: 3.1.5
DuckDB : 1.5.6
DuckDB connection: OK
ENVIRONMENT CHECK: PASS
2. Verify Source Package — RUN
Letakkan **5 file berikut langsung** di:
`data/raw/pm1_swiftbite/`
- `swiftbite_customers.json`
- `swiftbite_orders.xlsx`
- `swiftbite_order_items.csv`
- `swiftbite_payments.txt`
- `swiftbite_restaurants.csv`
**Jangan** meletakkan ZIP di folder tersebut dan **jangan** membuat folder `SB-Pxx` lagi di dalam `pm1_swiftbite/`.
EXPECTED_FILES = {
"customers": "swiftbite_customers.json",
"orders": "swiftbite_orders.xlsx",
"order_items": "swiftbite_order_items.csv",
"payments": "swiftbite_payments.txt",
"restaurants": "swiftbite_restaurants.csv",
}
source_status = []
if not TECHNICAL_GATE_PASS:
missing_files = list(EXPECTED_FILES.values())
SOURCE_ACCESS_PASS = False
print("SOURCE ACCESS CHECK: SKIPPED")
print("→ Perbaiki TECHNICAL GATE terlebih dahulu.")
else:
for logical_name, filename in EXPECTED_FILES.items():
path = RAW_DIR / filename
source_status.append({
"logical_name": logical_name,
"filename": filename,
"exists": path.is_file(),
"path": str(path),
})
source_status_df = pd.DataFrame(source_status)
display(source_status_df)
missing_files = source_status_df.loc[
~source_status_df["exists"], "filename"
].tolist()
SOURCE_ACCESS_PASS = len(missing_files) == 0
if SOURCE_ACCESS_PASS:
print("SOURCE ACCESS CHECK: PASS")
print("→ Semua 5 raw files ditemukan. Jangan ubah path.")
else:
print("SOURCE ACCESS CHECK: FAIL")
print("File belum ditemukan:", missing_files)
print("→ Extract package peserta.")
print("→ Copy hanya 5 raw files langsung ke:", RAW_DIR)
print("→ Jangan menaruh file di subfolder tambahan.")
|
logical_name |
filename |
exists |
path |
| 0 |
customers |
swiftbite_customers.json |
True |
C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy... |
| 1 |
orders |
swiftbite_orders.xlsx |
True |
C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy... |
| 2 |
order_items |
swiftbite_order_items.csv |
True |
C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy... |
| 3 |
payments |
swiftbite_payments.txt |
True |
C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy... |
| 4 |
restaurants |
swiftbite_restaurants.csv |
True |
C:\NaurahFile\Data Analyst\Bootcamp_Data_Analy... |
SOURCE ACCESS CHECK: PASS
→ Semua 5 raw files ditemukan. Jangan ubah path.
3. Load Raw Data & Schema Gate — RUN
Cell ini:
1. membaca kelima source secara aman,
2. menampilkan error per file jika ada,
3. memeriksa kolom minimum yang diperlukan Starter,
4. menyimpan **raw hash baseline** untuk pemeriksaan integritas di akhir.
Jika `RAW LOAD GATE: PASS` dan `SCHEMA GATE: PASS`, lanjutkan.
REQUIRED_COLUMNS = {
"Customers": {
"customer_id", "full_name", "email", "phone_number",
"signup_date", "customer_zone", "customer_segment"
},
"Orders": {
"order_id", "customer_id", "restaurant_id", "order_timestamp",
"order_status", "subtotal_amount", "delivery_fee",
"discount_amount", "total_amount"
},
"Order Items": {
"order_item_id", "order_id", "line_number", "item_name",
"item_category", "quantity", "unit_price",
"line_discount", "line_amount"
},
"Payments": {
"payment_id", "order_id", "payment_timestamp",
"payment_method", "payment_status", "payment_amount"
},
"Restaurants": {
"restaurant_id", "restaurant_name", "cuisine_type",
"restaurant_zone", "price_tier", "active_status"
},
}
def sha256_file(path: Path):
h = hashlib.sha256()
with open(path, "rb") as f:
for chunk in iter(lambda: f.read(1024 * 1024), b""):
h.update(chunk)
return h.hexdigest()
def safe_read_source(label, path, reader):
try:
return reader(path), None
except Exception as e:
return None, f"{type(e).__name__}: {e}"
raw_tables = {}
load_errors = {}
RAW_HASH_BASELINE = {}
if not SOURCE_ACCESS_PASS:
RAW_LOAD_GATE_PASS = False
SCHEMA_GATE_PASS = False
DATA_READY = False
print("RAW LOAD GATE: SKIPPED")
print("→ Perbaiki SOURCE ACCESS CHECK terlebih dahulu.")
else:
readers = {
"Customers": lambda p: pd.read_json(p),
"Orders": lambda p: pd.read_excel(p),
"Order Items": lambda p: pd.read_csv(p),
"Payments": lambda p: pd.read_csv(p, sep="|"),
"Restaurants": lambda p: pd.read_csv(p),
}
file_map = {
"Customers": RAW_DIR / EXPECTED_FILES["customers"],
"Orders": RAW_DIR / EXPECTED_FILES["orders"],
"Order Items": RAW_DIR / EXPECTED_FILES["order_items"],
"Payments": RAW_DIR / EXPECTED_FILES["payments"],
"Restaurants": RAW_DIR / EXPECTED_FILES["restaurants"],
}
for label, path in file_map.items():
df, err = safe_read_source(label, path, readers[label])
if err:
load_errors[label] = err
else:
raw_tables[label] = df
RAW_HASH_BASELINE[label] = sha256_file(path)
RAW_LOAD_GATE_PASS = len(load_errors) == 0 and len(raw_tables) == 5
if RAW_LOAD_GATE_PASS:
customers_raw = raw_tables["Customers"]
orders_raw = raw_tables["Orders"]
order_items_raw = raw_tables["Order Items"]
payments_raw = raw_tables["Payments"]
restaurants_raw = raw_tables["Restaurants"]
print("RAW LOAD GATE: PASS")
else:
print("RAW LOAD GATE: FAIL")
for label, err in load_errors.items():
print(f"→ {label}: {err}")
schema_rows = []
if RAW_LOAD_GATE_PASS:
for label, required in REQUIRED_COLUMNS.items():
actual = set(raw_tables[label].columns)
missing_cols = sorted(required - actual)
schema_rows.append({
"dataset": label,
"required_columns": len(required),
"actual_columns": len(actual),
"missing_required_columns": ", ".join(missing_cols) if missing_cols else "",
"status": "PASS" if not missing_cols else "FAIL",
})
schema_check_df = pd.DataFrame(schema_rows)
display(schema_check_df)
SCHEMA_GATE_PASS = (schema_check_df["status"] == "PASS").all()
if SCHEMA_GATE_PASS:
print("SCHEMA GATE: PASS")
else:
print("SCHEMA GATE: FAIL")
print("→ Jangan lanjut cleaning. Periksa apakah package/variant yang digunakan sudah benar.")
else:
SCHEMA_GATE_PASS = False
DATA_READY = bool(
TECHNICAL_GATE_PASS
and SOURCE_ACCESS_PASS
and RAW_LOAD_GATE_PASS
and SCHEMA_GATE_PASS
)
if DATA_READY:
print("DATA READY GATE: PASS")
else:
print("DATA READY GATE: FAIL")
RAW LOAD GATE: PASS
|
dataset |
required_columns |
actual_columns |
missing_required_columns |
status |
| 0 |
Customers |
7 |
7 |
|
PASS |
| 1 |
Orders |
9 |
10 |
|
PASS |
| 2 |
Order Items |
9 |
9 |
|
PASS |
| 3 |
Payments |
6 |
7 |
|
PASS |
| 4 |
Restaurants |
6 |
7 |
|
PASS |
SCHEMA GATE: PASS
DATA READY GATE: PASS
#Check Error "SCHEMA GATE: FAIL
#→ Jangan lanjut cleaning. Periksa apakah package/variant yang digunakan sudah benar.
#DATA READY GATE: FAIL"
print("Customers:", customers_raw.columns.tolist())
print("Payments:", payments_raw.columns.tolist())
print("Restaurants:", restaurants_raw.columns.tolist())
#Terdapat ketidaksesuaian nama antara kolom aktual dengan required kolom, sudah dibenarkan. statu sudah PASS
Customers: ['customer_id', 'full_name', 'email', 'phone_number', 'signup_date', 'customer_zone', 'customer_segment']
Payments: ['payment_id', 'order_id', 'payment_method', 'payment_status', 'payment_amount', 'payment_timestamp', 'attempt_number']
Restaurants: ['restaurant_id', 'restaurant_name', 'cuisine_type', 'restaurant_zone', 'price_tier', 'onboarding_date', 'active_status']
Quick Source Preview — RUN
Pastikan nama kolom dan contoh record masuk akal. Ini **belum** berarti data sudah bersih.
if DATA_READY:
for name, df in raw_tables.items():
print(f"\n=== {name} | shape={df.shape} ===")
display(df.head(3))
else:
print("PREVIEW SKIPPED — DATA READY GATE belum PASS.")
=== Customers | shape=(170, 7) ===
|
customer_id |
full_name |
email |
phone_number |
signup_date |
customer_zone |
customer_segment |
| 0 |
C0001 |
Eka Wijaya |
ekawijaya1@example.com |
082114273585 |
2025-08-14 |
Central |
New |
| 1 |
C0002 |
Hana Kusuma |
hanakusuma2@example.com |
085043192435 |
2025-11-18 |
Central |
New |
| 2 |
C0003 |
Adi Saputra |
adisaputra3@example.com |
084202997840 |
2025-09-17 |
Central |
Loyal |
=== Orders | shape=(332, 10) ===
|
order_id |
customer_id |
restaurant_id |
order_timestamp |
order_status |
promo_code |
subtotal_amount |
delivery_fee |
discount_amount |
total_amount |
| 0 |
O00001 |
C0040 |
R0020 |
2026-02-12 19:26:00 |
completed |
NEWUSER |
367000 |
12000 |
0 |
379000 |
| 1 |
O00002 |
C0047 |
R0001 |
2026-01-14 14:38:00 |
completed |
HEMAT15 |
163000 |
15000 |
0 |
178000 |
| 2 |
O00003 |
C0054 |
R0006 |
2026-01-31 23:31:00 |
completed |
SAVE10 |
240000 |
10000 |
10000 |
240000 |
=== Order Items | shape=(944, 9) ===
|
order_item_id |
order_id |
line_number |
item_name |
item_category |
quantity |
unit_price |
line_discount |
line_amount |
| 0 |
OI00001 |
O00001 |
1 |
Cake Slice |
Dessert |
4 |
24000 |
0 |
96000 |
| 1 |
OI00002 |
O00001 |
2 |
Dimsum |
Snack |
2 |
22000 |
0 |
44000 |
| 2 |
OI00003 |
O00001 |
3 |
Chicken Set |
Main |
4 |
42000 |
5000 |
163000 |
=== Payments | shape=(357, 7) ===
|
payment_id |
order_id |
payment_method |
payment_status |
payment_amount |
payment_timestamp |
attempt_number |
| 0 |
P00001 |
O00001 |
e_wallet |
successful |
379000 |
2026-02-12 19:29:00 |
1 |
| 1 |
P00002 |
O00002 |
bank_transfer |
successful |
178000 |
2026-01-14 14:41:00 |
1 |
| 2 |
P00003 |
O00003 |
credit_card |
successful |
240000 |
2026-01-31 23:34:00 |
1 |
=== 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 |
Central |
Premium |
2025-01-30 |
active |
| 1 |
R0002 |
SwiftBite Partner 02 |
Cafe & Beverage |
Central |
Premium |
2024-10-27 |
active |
| 2 |
R0003 |
SwiftBite Partner 03 |
Dessert |
South |
Budget |
2025-01-02 |
active |
4. Intake Summary — Working Paper Sheet `01_Intake_Profiling`
Catat business meaning, expected grain, candidate key, row/record count, access status, dan key test result.
def intake_summary(df, dataset_name):
return {
"dataset": dataset_name,
"rows": len(df),
"columns": len(df.columns),
"column_names": list(df.columns),
}
if DATA_READY:
intake_df = pd.DataFrame([
intake_summary(customers_raw, "Customers"),
intake_summary(orders_raw, "Orders"),
intake_summary(order_items_raw, "Order Items"),
intake_summary(payments_raw, "Payments"),
intake_summary(restaurants_raw, "Restaurants"),
])
display(intake_df)
|
dataset |
rows |
columns |
column_names |
| 0 |
Customers |
170 |
7 |
[customer_id, full_name, email, phone_number, ... |
| 1 |
Orders |
332 |
10 |
[order_id, customer_id, restaurant_id, order_t... |
| 2 |
Order Items |
944 |
9 |
[order_item_id, order_id, line_number, item_na... |
| 3 |
Payments |
357 |
7 |
[payment_id, order_id, payment_method, payment... |
| 4 |
Restaurants |
24 |
7 |
[restaurant_id, restaurant_name, cuisine_type,... |
5. Grain & Candidate Key Check — RUN
Repeated `order_id` pada **Order Items** atau **Payments** belum tentu duplicate karena grain-nya berbeda dari Orders.
Gunakan hasil check sebagai **evidence awal**, bukan cleaning action otomatis.
def key_check(df, key_cols, dataset_name):
if isinstance(key_cols, str):
key_cols = [key_cols]
missing_key_rows = int(df[key_cols].isna().any(axis=1).sum())
unique_key_count = int(df[key_cols].drop_duplicates().shape[0])
duplicate_key_rows = int(df.duplicated(subset=key_cols, keep=False).sum())
return pd.DataFrame([{
"dataset": dataset_name,
"key_cols": ", ".join(key_cols),
"rows": len(df),
"unique_key_count": unique_key_count,
"missing_key_rows": missing_key_rows,
"duplicate_key_rows": duplicate_key_rows,
"candidate_key_unique": unique_key_count == len(df) and missing_key_rows == 0,
}])
if DATA_READY:
key_results = pd.concat([
key_check(customers_raw, "customer_id", "Customers"),
key_check(orders_raw, "order_id", "Orders"),
key_check(order_items_raw, "order_item_id", "Order Items"),
key_check(order_items_raw, ["order_id", "line_number"], "Order Items — candidate business key"),
key_check(payments_raw, "payment_id", "Payments"),
key_check(restaurants_raw, "restaurant_id", "Restaurants"),
], ignore_index=True)
display(key_results)
|
dataset |
key_cols |
rows |
unique_key_count |
missing_key_rows |
duplicate_key_rows |
candidate_key_unique |
| 0 |
Customers |
customer_id |
170 |
169 |
0 |
2 |
False |
| 1 |
Orders |
order_id |
332 |
332 |
0 |
0 |
True |
| 2 |
Order Items |
order_item_id |
944 |
944 |
0 |
0 |
True |
| 3 |
Order Items — candidate business key |
order_id, line_number |
944 |
944 |
0 |
0 |
True |
| 4 |
Payments |
payment_id |
357 |
357 |
0 |
0 |
True |
| 5 |
Restaurants |
restaurant_id |
24 |
24 |
0 |
0 |
True |
#Melihat data duplikat pada dataset customer, customer_id
duplicate_customers = customers_raw[
customers_raw.duplicated(
subset=["customer_id"],
keep=False
)
].sort_values("customer_id")
display(duplicate_customers)
|
customer_id |
full_name |
email |
phone_number |
signup_date |
customer_zone |
customer_segment |
| 30 |
C0031 |
Vina Hidayat |
vinahidayat31@example.com |
082060687029 |
2025-11-12 |
North |
Loyal |
| 169 |
C0031 |
Vina Hidayat |
vinahidayat31@example.com |
082060687029 |
2025-11-12 |
North |
Loyal |
Repeated Reference-Key Review
if DATA_READY:
order_item_cardinality = (
order_items_raw.groupby("order_id")
.size()
.rename("item_rows")
.reset_index()
.sort_values("item_rows", ascending=False)
)
payment_cardinality = (
payments_raw.groupby("order_id")
.size()
.rename("payment_rows")
.reset_index()
.sort_values("payment_rows", ascending=False)
)
print("Order Items cardinality")
display(order_item_cardinality.head(10))
print("Payments cardinality")
display(payment_cardinality.head(10))
Order Items cardinality
|
order_id |
item_rows |
| 14 |
O00015 |
5 |
| 0 |
O00001 |
5 |
| 291 |
O00292 |
5 |
| 42 |
O00043 |
5 |
| 295 |
O00296 |
5 |
| 114 |
O00115 |
5 |
| 107 |
O00108 |
5 |
| 181 |
O00182 |
5 |
| 167 |
O00168 |
5 |
| 168 |
O00169 |
5 |
Payments cardinality
|
order_id |
payment_rows |
| 298 |
O00299 |
2 |
| 282 |
O00283 |
2 |
| 266 |
O00267 |
2 |
| 254 |
O00255 |
2 |
| 249 |
O00250 |
2 |
| 236 |
O00237 |
2 |
| 206 |
O00207 |
2 |
| 155 |
O00156 |
2 |
| 130 |
O00131 |
2 |
| 110 |
O00111 |
2 |
6. Profiling
Profiling minimal:
- missing / placeholder,
- data type / format,
- category consistency,
- duplicate / key,
- suspicious numeric / formula,
- potential outlier,
- cross-table concern.
Helper berikut menghasilkan evidence awal, bukan automatic judgment.
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 |
170 |
7 |
2 |
1 |
0 |
| 1 |
Orders |
332 |
10 |
0 |
0 |
176 |
| 2 |
Order Items |
944 |
9 |
0 |
0 |
0 |
| 3 |
Payments |
357 |
7 |
0 |
0 |
0 |
| 4 |
Restaurants |
24 |
7 |
0 |
0 |
0 |
#Melihat data duplikat pada dataset customer, customer_id
duplicate_customers = customers_raw[
customers_raw.duplicated(
subset=["customer_id"],
keep=False
)
].sort_values("customer_id")
display(duplicate_customers)
|
customer_id |
full_name |
email |
phone_number |
signup_date |
customer_zone |
customer_segment |
| 30 |
C0031 |
Vina Hidayat |
vinahidayat31@example.com |
082060687029 |
2025-11-12 |
North |
Loyal |
| 169 |
C0031 |
Vina Hidayat |
vinahidayat31@example.com |
082060687029 |
2025-11-12 |
North |
Loyal |
null_cols = orders_raw.columns[orders_raw.isna().any()]
display(orders_raw.loc[orders_raw.isna().any(axis=1), null_cols])
|
promo_code |
| 3 |
NaN |
| 4 |
NaN |
| 6 |
NaN |
| 8 |
NaN |
| 10 |
NaN |
| ... |
... |
| 323 |
NaN |
| 326 |
NaN |
| 328 |
NaN |
| 329 |
NaN |
| 330 |
NaN |
176 rows × 1 columns
Column-Level Profiling
# 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 |
169 |
[C0001, C0002, C0003, C0004, C0005] |
| 1 |
full_name |
str |
0 |
0.0 |
136 |
[Eka Wijaya, Hana Kusuma, Adi Saputra, Jihan K... |
| 2 |
email |
str |
0 |
0.0 |
169 |
[ekawijaya1@example.com, hanakusuma2@example.c... |
| 3 |
phone_number |
str |
0 |
0.0 |
169 |
[082114273585, 085043192435, 084202997840, 084... |
| 4 |
signup_date |
str |
0 |
0.0 |
118 |
[2025-08-14, 2025-11-18, 2025-09-17, 2025-09-1... |
| 5 |
customer_zone |
str |
0 |
0.0 |
5 |
[Central, West, East, South, North] |
| 6 |
customer_segment |
str |
0 |
0.0 |
4 |
[New, Loyal, Regular, REGULAR ] |
if DATA_READY:
display(column_profile(orders_raw))
|
column |
dtype |
null_count |
null_pct |
unique_count |
sample_values |
| 0 |
order_id |
str |
0 |
0.0000 |
332 |
[O00001, O00002, O00003, O00004, O00005] |
| 1 |
customer_id |
str |
0 |
0.0000 |
169 |
[C0040, C0047, C0054, C0061, C0068] |
| 2 |
restaurant_id |
str |
0 |
0.0000 |
24 |
[R0020, R0001, R0006, R0011, R0016] |
| 3 |
order_timestamp |
str |
0 |
0.0000 |
331 |
[2026-02-12 19:26:00, 2026-01-14 14:38:00, 202... |
| 4 |
order_status |
str |
0 |
0.0000 |
3 |
[completed, COMPLETED , cancelled] |
| 5 |
promo_code |
str |
176 |
0.5301 |
3 |
[NEWUSER, HEMAT15, SAVE10] |
| 6 |
subtotal_amount |
int64 |
0 |
0.0000 |
193 |
[367000, 163000, 240000, 68000, 24000] |
| 7 |
delivery_fee |
int64 |
0 |
0.0000 |
5 |
[12000, 15000, 10000, 8000, 18000] |
| 8 |
discount_amount |
int64 |
0 |
0.0000 |
4 |
[0, 10000, 5000, 15000] |
| 9 |
total_amount |
int64 |
0 |
0.0000 |
195 |
[379000, 178000, 240000, 73000, 34000] |
if DATA_READY:
display(column_profile(order_items_raw))
|
column |
dtype |
null_count |
null_pct |
unique_count |
sample_values |
| 0 |
order_item_id |
str |
0 |
0.0 |
944 |
[OI00001, OI00002, OI00003, OI00004, OI00005] |
| 1 |
order_id |
str |
0 |
0.0 |
332 |
[O00001, O00002, O00003, O00004, O00005] |
| 2 |
line_number |
int64 |
0 |
0.0 |
5 |
[1, 2, 3, 4, 5] |
| 3 |
item_name |
str |
0 |
0.0 |
14 |
[Cake Slice, Dimsum, Chicken Set, Toast, Iced ... |
| 4 |
item_category |
str |
0 |
0.0 |
5 |
[Dessert, Snack, Main, Drink, SNACK ] |
| 5 |
quantity |
str |
0 |
0.0 |
6 |
[4, 2, 1, 3, 2 pcs] |
| 6 |
unit_price |
int64 |
0 |
0.0 |
11 |
[24000, 22000, 42000, 16000, 9000] |
| 7 |
line_discount |
int64 |
0 |
0.0 |
3 |
[0, 5000, 2000] |
| 8 |
line_amount |
int64 |
0 |
0.0 |
82 |
[96000, 44000, 163000, 22000, 42000] |
if DATA_READY:
display(column_profile(payments_raw))
|
column |
dtype |
null_count |
null_pct |
unique_count |
sample_values |
| 0 |
payment_id |
str |
0 |
0.0 |
357 |
[P00001, P00002, P00003, P00004, P00005] |
| 1 |
order_id |
str |
0 |
0.0 |
332 |
[O00001, O00002, O00003, O00004, O00005] |
| 2 |
payment_method |
str |
0 |
0.0 |
5 |
[e_wallet, bank_transfer, credit_card, cash, E... |
| 3 |
payment_status |
str |
0 |
0.0 |
3 |
[successful, failed, refunded] |
| 4 |
payment_amount |
int64 |
0 |
0.0 |
195 |
[379000, 178000, 240000, 73000, 34000] |
| 5 |
payment_timestamp |
str |
0 |
0.0 |
356 |
[2026-02-12 19:29:00, 2026-01-14 14:41:00, 202... |
| 6 |
attempt_number |
int64 |
0 |
0.0 |
2 |
[1, 2] |
if DATA_READY:
display(column_profile(restaurants_raw))
|
column |
dtype |
null_count |
null_pct |
unique_count |
sample_values |
| 0 |
restaurant_id |
str |
0 |
0.0 |
24 |
[R0001, R0002, R0003, R0004, R0005] |
| 1 |
restaurant_name |
str |
0 |
0.0 |
24 |
[SwiftBite Partner 01, SwiftBite Partner 02, S... |
| 2 |
cuisine_type |
str |
0 |
0.0 |
6 |
[Fast Food, Cafe & Beverage, Dessert, Indonesi... |
| 3 |
restaurant_zone |
str |
0 |
0.0 |
5 |
[Central, South, East, North, West] |
| 4 |
price_tier |
str |
0 |
0.0 |
5 |
[Premium, Budget, MID , Mid, BUDGET ] |
| 5 |
onboarding_date |
str |
0 |
0.0 |
23 |
[2025-01-30, 2024-10-27, 2025-01-02, 2024-06-2... |
| 6 |
active_status |
str |
0 |
0.0 |
2 |
[active, inactive] |
Category Review Helper
def category_review(df, column, top_n=30):
out = (
df[column]
.value_counts(dropna=False)
.rename_axis(column)
.reset_index(name="count")
)
out["pct"] = (out["count"] / len(df)).round(4)
return out.head(top_n)
# Contoh:
display(category_review(customers_raw, "customer_segment"))
display(category_review(orders_raw, "order_status"))
display(category_review(payments_raw, "payment_method"))
display(category_review(restaurants_raw, "price_tier"))
display(category_review(customers_raw, "customer_zone"))
display(category_review(orders_raw, "promo_code"))
display(category_review(payments_raw, "payment_status"))
display(category_review(order_items_raw, "item_name"))
display(category_review(order_items_raw, "item_category"))
display(category_review(restaurants_raw, "restaurant_name"))
display(category_review(restaurants_raw, "cuisine_type"))
display(category_review(restaurants_raw, "restaurant_zone"))
display(category_review(restaurants_raw, "active_status"))
|
customer_segment |
count |
pct |
| 0 |
Regular |
73 |
0.4294 |
| 1 |
New |
55 |
0.3235 |
| 2 |
Loyal |
40 |
0.2353 |
| 3 |
REGULAR |
2 |
0.0118 |
|
order_status |
count |
pct |
| 0 |
completed |
300 |
0.9036 |
| 1 |
cancelled |
30 |
0.0904 |
| 2 |
COMPLETED |
2 |
0.0060 |
|
payment_method |
count |
pct |
| 0 |
credit_card |
93 |
0.2605 |
| 1 |
bank_transfer |
88 |
0.2465 |
| 2 |
cash |
88 |
0.2465 |
| 3 |
e_wallet |
86 |
0.2409 |
| 4 |
E WALLET |
2 |
0.0056 |
|
price_tier |
count |
pct |
| 0 |
Premium |
10 |
0.4167 |
| 1 |
Budget |
10 |
0.4167 |
| 2 |
Mid |
2 |
0.0833 |
| 3 |
MID |
1 |
0.0417 |
| 4 |
BUDGET |
1 |
0.0417 |
|
customer_zone |
count |
pct |
| 0 |
Central |
44 |
0.2588 |
| 1 |
North |
38 |
0.2235 |
| 2 |
West |
31 |
0.1824 |
| 3 |
South |
29 |
0.1706 |
| 4 |
East |
28 |
0.1647 |
|
promo_code |
count |
pct |
| 0 |
NaN |
176 |
0.5301 |
| 1 |
SAVE10 |
56 |
0.1687 |
| 2 |
HEMAT15 |
51 |
0.1536 |
| 3 |
NEWUSER |
49 |
0.1476 |
|
payment_status |
count |
pct |
| 0 |
successful |
312 |
0.8739 |
| 1 |
failed |
35 |
0.0980 |
| 2 |
refunded |
10 |
0.0280 |
|
item_name |
count |
pct |
| 0 |
Chicken Set |
111 |
0.1176 |
| 1 |
Rice Bowl |
100 |
0.1059 |
| 2 |
Nasi Ayam |
97 |
0.1028 |
| 3 |
Mie Special |
88 |
0.0932 |
| 4 |
Milk Tea |
79 |
0.0837 |
| 5 |
Coffee |
75 |
0.0794 |
| 6 |
Juice |
64 |
0.0678 |
| 7 |
Dimsum |
62 |
0.0657 |
| 8 |
Iced Tea |
59 |
0.0625 |
| 9 |
Toast |
48 |
0.0508 |
| 10 |
Cake Slice |
47 |
0.0498 |
| 11 |
Fries |
46 |
0.0487 |
| 12 |
Pudding |
34 |
0.0360 |
| 13 |
Ice Cream |
34 |
0.0360 |
|
item_category |
count |
pct |
| 0 |
Main |
396 |
0.4195 |
| 1 |
Drink |
277 |
0.2934 |
| 2 |
Snack |
154 |
0.1631 |
| 3 |
Dessert |
115 |
0.1218 |
| 4 |
SNACK |
2 |
0.0021 |
|
restaurant_name |
count |
pct |
| 0 |
SwiftBite Partner 01 |
1 |
0.0417 |
| 1 |
SwiftBite Partner 02 |
1 |
0.0417 |
| 2 |
SwiftBite Partner 03 |
1 |
0.0417 |
| 3 |
SwiftBite Partner 04 |
1 |
0.0417 |
| 4 |
SwiftBite Partner 05 |
1 |
0.0417 |
| 5 |
SwiftBite Partner 06 |
1 |
0.0417 |
| 6 |
SwiftBite Partner 07 |
1 |
0.0417 |
| 7 |
SwiftBite Partner 08 |
1 |
0.0417 |
| 8 |
SwiftBite Partner 09 |
1 |
0.0417 |
| 9 |
SwiftBite Partner 10 |
1 |
0.0417 |
| 10 |
SwiftBite Partner 11 |
1 |
0.0417 |
| 11 |
SwiftBite Partner 12 |
1 |
0.0417 |
| 12 |
SwiftBite Partner 13 |
1 |
0.0417 |
| 13 |
SwiftBite Partner 14 |
1 |
0.0417 |
| 14 |
SwiftBite Partner 15 |
1 |
0.0417 |
| 15 |
SwiftBite Partner 16 |
1 |
0.0417 |
| 16 |
SwiftBite Partner 17 |
1 |
0.0417 |
| 17 |
SwiftBite Partner 18 |
1 |
0.0417 |
| 18 |
SwiftBite Partner 19 |
1 |
0.0417 |
| 19 |
SwiftBite Partner 20 |
1 |
0.0417 |
| 20 |
SwiftBite Partner 21 |
1 |
0.0417 |
| 21 |
SwiftBite Partner 22 |
1 |
0.0417 |
| 22 |
SwiftBite Partner 23 |
1 |
0.0417 |
| 23 |
SwiftBite Partner 24 |
1 |
0.0417 |
|
cuisine_type |
count |
pct |
| 0 |
Fast Food |
4 |
0.1667 |
| 1 |
Cafe & Beverage |
4 |
0.1667 |
| 2 |
Dessert |
4 |
0.1667 |
| 3 |
Indonesian |
4 |
0.1667 |
| 4 |
Asian |
4 |
0.1667 |
| 5 |
Western |
4 |
0.1667 |
|
restaurant_zone |
count |
pct |
| 0 |
East |
8 |
0.3333 |
| 1 |
South |
6 |
0.2500 |
| 2 |
Central |
5 |
0.2083 |
| 3 |
North |
3 |
0.1250 |
| 4 |
West |
2 |
0.0833 |
|
active_status |
count |
pct |
| 0 |
active |
22 |
0.9167 |
| 1 |
inactive |
2 |
0.0833 |
customers_raw[
customers_raw["customer_segment"].isin(["Regular", "REGULAR "])
][["customer_id", "full_name", "customer_segment"]]
|
customer_id |
full_name |
customer_segment |
| 3 |
C0004 |
Jihan Kusuma |
Regular |
| 4 |
C0005 |
Sinta Saputra |
Regular |
| 8 |
C0009 |
Raka Permata |
REGULAR |
| 10 |
C0011 |
Tio Permata |
Regular |
| 11 |
C0012 |
Eka Kusuma |
Regular |
| 12 |
C0013 |
Hana Maulana |
Regular |
| 13 |
C0014 |
Kevin Nugroho |
Regular |
| 16 |
C0017 |
Jihan Santoso |
Regular |
| 17 |
C0018 |
Laras Hidayat |
Regular |
| 20 |
C0021 |
Eka Pratama |
Regular |
| 21 |
C0022 |
Bima Utami |
Regular |
| 22 |
C0023 |
Ayu Sari |
Regular |
| 23 |
C0024 |
Wahyu Hidayat |
Regular |
| 24 |
C0025 |
Yani Hidayat |
Regular |
| 25 |
C0026 |
Citra Kusuma |
Regular |
| 28 |
C0029 |
Dimas Wijaya |
Regular |
| 29 |
C0030 |
Ayu Hidayat |
Regular |
| 31 |
C0032 |
Tio Wijaya |
Regular |
| 35 |
C0036 |
Nadia Sari |
Regular |
| 38 |
C0039 |
Farah Lestari |
Regular |
| 39 |
C0040 |
Raka Santoso |
Regular |
| 41 |
C0042 |
Dimas Lestari |
Regular |
| 43 |
C0044 |
Oka Sari |
Regular |
| 45 |
C0046 |
Laras Kusuma |
REGULAR |
| 46 |
C0047 |
Ayu Wijaya |
Regular |
| 47 |
C0048 |
Wahyu Sari |
Regular |
| 48 |
C0049 |
Raka Permata |
Regular |
| 52 |
C0053 |
Zaki Nugroho |
Regular |
| 58 |
C0059 |
Vina Utami |
Regular |
| 59 |
C0060 |
Farah Kusuma |
Regular |
| 60 |
C0061 |
Bima Wijaya |
Regular |
| 63 |
C0064 |
Hana Pratama |
Regular |
| 64 |
C0065 |
Ayu Santoso |
Regular |
| 65 |
C0066 |
Adi Permata |
Regular |
| 68 |
C0069 |
Putri Maulana |
Regular |
| 69 |
C0070 |
Maya Kusuma |
Regular |
| 71 |
C0072 |
Laras Sari |
Regular |
| 75 |
C0076 |
Kevin Sari |
Regular |
| 76 |
C0077 |
Indra Pratama |
Regular |
| 77 |
C0078 |
Hana Nugroho |
Regular |
| 78 |
C0079 |
Dimas Permata |
Regular |
| 79 |
C0080 |
Laras Nugroho |
Regular |
| 80 |
C0081 |
Adi Utami |
Regular |
| 81 |
C0082 |
Indra Santoso |
Regular |
| 83 |
C0084 |
Oka Maulana |
Regular |
| 86 |
C0087 |
Eka Santoso |
Regular |
| 87 |
C0088 |
Adi Nugroho |
Regular |
| 93 |
C0094 |
Raka Saputra |
Regular |
| 96 |
C0097 |
Oka Maulana |
Regular |
| 98 |
C0099 |
Adi Nugroho |
Regular |
| 99 |
C0100 |
Wahyu Sari |
Regular |
| 104 |
C0105 |
Vina Saputra |
Regular |
| 105 |
C0106 |
Maya Pratama |
Regular |
| 106 |
C0107 |
Kevin Maulana |
Regular |
| 107 |
C0108 |
Indra Lestari |
Regular |
| 109 |
C0110 |
Bima Utami |
Regular |
| 114 |
C0115 |
Yani Kusuma |
Regular |
| 116 |
C0117 |
Maya Utami |
Regular |
| 118 |
C0119 |
Adi Hidayat |
Regular |
| 120 |
C0121 |
Kevin Santoso |
Regular |
| 124 |
C0125 |
Laras Lestari |
Regular |
| 125 |
C0126 |
Galih Hidayat |
Regular |
| 126 |
C0127 |
Nadia Santoso |
Regular |
| 127 |
C0128 |
Putri Sari |
Regular |
| 132 |
C0133 |
Raka Lestari |
Regular |
| 137 |
C0138 |
Zaki Sari |
Regular |
| 139 |
C0140 |
Raka Kusuma |
Regular |
| 145 |
C0146 |
Kevin Hidayat |
Regular |
| 151 |
C0152 |
Zaki Permata |
Regular |
| 154 |
C0155 |
Kevin Saputra |
Regular |
| 161 |
C0162 |
Bima Wijaya |
Regular |
| 163 |
C0164 |
Citra Wijaya |
Regular |
| 165 |
C0166 |
Galih Santoso |
Regular |
| 167 |
C0168 |
Nadia Hidayat |
Regular |
| 168 |
C0169 |
Adi Nugroho |
Regular |
print(orders_raw.columns.tolist())
['order_id', 'customer_id', 'restaurant_id', 'order_timestamp', 'order_status', 'promo_code', 'subtotal_amount', 'delivery_fee', 'discount_amount', 'total_amount']
orders_raw[
orders_raw["order_status"] == "COMPLETED "
][["order_id", "customer_id", "order_status"]]
|
order_id |
customer_id |
order_status |
| 17 |
O00018 |
C0159 |
COMPLETED |
| 56 |
O00057 |
C0094 |
COMPLETED |
print(payments_raw.columns.tolist())
['payment_id', 'order_id', 'payment_method', 'payment_status', 'payment_amount', 'payment_timestamp', 'attempt_number']
payments_raw[
payments_raw["payment_method"] == "E WALLET "
][["payment_id", "payment_method"]]
|
payment_id |
payment_method |
| 35 |
P00041 |
E WALLET |
| 79 |
P00089 |
E WALLET |
print(restaurants_raw.columns.tolist())
['restaurant_id', 'restaurant_name', 'cuisine_type', 'restaurant_zone', 'price_tier', 'onboarding_date', 'active_status']
restaurants_raw[
restaurants_raw["price_tier"] == "BUDGET "
][["restaurant_id", "price_tier"]]
|
restaurant_id |
price_tier |
| 12 |
R0013 |
BUDGET |
Numeric Review Helper
def numeric_profile(df):
numeric_cols = df.select_dtypes(include="number").columns.tolist()
if not numeric_cols:
return pd.DataFrame()
return df[numeric_cols].describe().T
# Contoh:
display(numeric_profile(orders_raw))
display(numeric_profile(order_items_raw))
|
count |
mean |
std |
min |
25% |
50% |
75% |
max |
| subtotal_amount |
332.0 |
154572.289157 |
130672.525643 |
16000.0 |
91000.0 |
135500.0 |
194000.0 |
2094000.0 |
| delivery_fee |
332.0 |
12765.060241 |
3541.584937 |
8000.0 |
10000.0 |
12000.0 |
15000.0 |
18000.0 |
| discount_amount |
332.0 |
5391.566265 |
5738.273577 |
0.0 |
0.0 |
5000.0 |
10000.0 |
15000.0 |
| total_amount |
332.0 |
161945.783133 |
130797.357440 |
23000.0 |
96750.0 |
146000.0 |
202250.0 |
2097000.0 |
|
count |
mean |
std |
min |
25% |
50% |
75% |
max |
| line_number |
944.0 |
2.083686 |
1.027918 |
1.0 |
1.0 |
2.0 |
3.0 |
5.0 |
| unit_price |
944.0 |
24965.042373 |
17914.011838 |
9000.0 |
18000.0 |
22000.0 |
28000.0 |
508000.0 |
| line_discount |
944.0 |
1506.355932 |
2028.942718 |
0.0 |
0.0 |
0.0 |
2000.0 |
5000.0 |
| line_amount |
944.0 |
54353.813559 |
71114.442040 |
4000.0 |
27750.0 |
48000.0 |
70000.0 |
2032000.0 |
# Order yang ingin diperiksa
target_order = "O00084"
# 1. Seluruh kolom order
print("=== ORDERS_RAW ===")
display(
orders_raw[
orders_raw["order_id"] == target_order
].T
)
# 2. Rincian seluruh item
print("=== ORDER_ITEMS_RAW ===")
display(
order_items_raw[
order_items_raw["order_id"] == target_order
]
)
# 3. Total rincian item
items = order_items_raw[
order_items_raw["order_id"] == target_order
]
print("Total line_amount:", items["line_amount"].sum())
# 4. Data pembayaran
print("=== PAYMENTS_RAW ===")
display(
payments_raw[
payments_raw["order_id"] == target_order
]
)
=== ORDERS_RAW ===
|
83 |
| order_id |
O00084 |
| customer_id |
C0114 |
| restaurant_id |
R0003 |
| order_timestamp |
2026-01-19 01:47:00 |
| order_status |
completed |
| promo_code |
HEMAT15 |
| subtotal_amount |
2094000 |
| delivery_fee |
18000 |
| discount_amount |
15000 |
| total_amount |
2097000 |
=== ORDER_ITEMS_RAW ===
|
order_item_id |
order_id |
line_number |
item_name |
item_category |
quantity |
unit_price |
line_discount |
line_amount |
| 230 |
OI00231 |
O00084 |
1 |
Rice Bowl |
Main |
2 |
32000 |
2000 |
62000 |
| 231 |
OI00232 |
O00084 |
2 |
Coffee |
Drink |
4 |
508000 |
0 |
2032000 |
Total line_amount: 2094000
=== PAYMENTS_RAW ===
|
payment_id |
order_id |
payment_method |
payment_status |
payment_amount |
payment_timestamp |
attempt_number |
| 86 |
P00096 |
O00084 |
cash |
successful |
2097000 |
2026-01-19 01:50:00 |
1 |
coffee_check = order_items_raw[
order_items_raw["item_name"].str.strip().str.lower() == "coffee"
]
display(
coffee_check[
["order_id", "item_name", "quantity", "unit_price", "line_amount"]
].sort_values("unit_price", ascending=False).head(20)
)
print("Statistik harga satuan Coffee:")
display(coffee_check["unit_price"].describe())
|
order_id |
item_name |
quantity |
unit_price |
line_amount |
| 231 |
O00084 |
Coffee |
4 |
508000 |
2032000 |
| 15 |
O00006 |
Coffee |
2 |
18000 |
31000 |
| 11 |
O00003 |
Coffee |
2 |
18000 |
34000 |
| 83 |
O00032 |
Coffee |
1 |
18000 |
16000 |
| 110 |
O00043 |
Coffee |
3 |
18000 |
49000 |
| 124 |
O00047 |
Coffee |
2 |
18000 |
36000 |
| 128 |
O00049 |
Coffee |
2 |
18000 |
34000 |
| 134 |
O00051 |
Coffee |
3 |
18000 |
54000 |
| 147 |
O00056 |
Coffee |
2 |
18000 |
36000 |
| 187 |
O00068 |
Coffee |
3 |
18000 |
52000 |
| 19 |
O00007 |
Coffee |
2 |
18000 |
36000 |
| 198 |
O00072 |
Coffee |
2 |
18000 |
31000 |
| 200 |
O00072 |
Coffee |
2 |
18000 |
36000 |
| 208 |
O00076 |
Coffee |
3 |
18000 |
54000 |
| 201 |
O00073 |
Coffee |
3 |
18000 |
54000 |
| 229 |
O00083 |
Coffee |
3 |
18000 |
49000 |
| 232 |
O00085 |
Coffee |
3 |
18000 |
49000 |
| 240 |
O00088 |
Coffee |
2 |
18000 |
36000 |
| 247 |
O00090 |
Coffee |
1 |
18000 |
16000 |
| 312 |
O00114 |
Coffee |
1 |
18000 |
18000 |
Statistik harga satuan Coffee:
count 75.000000
mean 24533.333333
std 56580.326381
min 18000.000000
25% 18000.000000
50% 18000.000000
75% 18000.000000
max 508000.000000
Name: unit_price, dtype: float64
# 1. Cek status 10 pesanan
target_orders = [f"O{i:05d}" for i in range(323, 333)]
print("=== ORDERS_RAW ===")
display(
orders_raw[
orders_raw["order_id"].isin(target_orders)
].sort_values("order_id")
)
# 2. Cek seluruh baris pembayaran untuk 10 pesanan
print("=== PAYMENTS_RAW ===")
payment_check = payments_raw[
payments_raw["order_id"].isin(target_orders)
].sort_values(["order_id", "attempt_number"])
display(
payment_check[
[
"payment_id",
"order_id",
"payment_method",
"payment_status",
"payment_amount",
"payment_timestamp",
"attempt_number"
]
]
)
# 3. Ringkasan jumlah baris pembayaran per pesanan dan statusnya
print("=== RINGKASAN PEMBAYARAN ===")
display(
payment_check.groupby(
["order_id", "payment_status"]
).agg(
jumlah_baris=("payment_id", "count"),
total_nominal=("payment_amount", "sum")
).reset_index()
)
=== ORDERS_RAW ===
|
order_id |
customer_id |
restaurant_id |
order_timestamp |
order_status |
promo_code |
subtotal_amount |
delivery_fee |
discount_amount |
total_amount |
| 322 |
O00323 |
C0097 |
R0022 |
2026-03-17 05:50:00 |
cancelled |
SAVE10 |
323000 |
10000 |
10000 |
323000 |
| 323 |
O00324 |
C0104 |
R0003 |
2026-03-03 12:46:00 |
cancelled |
NaN |
288000 |
10000 |
5000 |
293000 |
| 324 |
O00325 |
C0111 |
R0008 |
2026-02-22 22:26:00 |
cancelled |
NEWUSER |
49000 |
18000 |
15000 |
52000 |
| 325 |
O00326 |
C0118 |
R0013 |
2026-01-02 02:16:00 |
cancelled |
HEMAT15 |
212000 |
18000 |
5000 |
225000 |
| 326 |
O00327 |
C0125 |
R0018 |
2026-03-17 10:27:00 |
cancelled |
NaN |
73000 |
12000 |
0 |
85000 |
| 327 |
O00328 |
C0132 |
R0023 |
2026-02-07 06:48:00 |
cancelled |
SAVE10 |
124000 |
18000 |
5000 |
137000 |
| 328 |
O00329 |
C0139 |
R0004 |
2026-03-27 19:28:00 |
cancelled |
NaN |
110000 |
18000 |
0 |
128000 |
| 329 |
O00330 |
C0146 |
R0009 |
2026-03-10 03:21:00 |
cancelled |
NaN |
109000 |
8000 |
0 |
117000 |
| 330 |
O00331 |
C0153 |
R0014 |
2026-02-23 15:34:00 |
cancelled |
NaN |
107000 |
10000 |
0 |
117000 |
| 331 |
O00332 |
C0160 |
R0019 |
2026-02-11 21:51:00 |
cancelled |
HEMAT15 |
364000 |
18000 |
0 |
382000 |
=== PAYMENTS_RAW ===
|
payment_id |
order_id |
payment_method |
payment_status |
payment_amount |
payment_timestamp |
attempt_number |
| 337 |
P00356 |
O00323 |
credit_card |
successful |
323000 |
2026-03-17 05:52:00 |
1 |
| 338 |
P00357 |
O00323 |
credit_card |
refunded |
323000 |
2026-03-17 06:15:00 |
2 |
| 339 |
P00358 |
O00324 |
cash |
successful |
293000 |
2026-03-03 12:48:00 |
1 |
| 340 |
P00359 |
O00324 |
cash |
refunded |
293000 |
2026-03-03 13:11:00 |
2 |
| 341 |
P00360 |
O00325 |
e_wallet |
successful |
52000 |
2026-02-22 22:28:00 |
1 |
| 342 |
P00361 |
O00325 |
e_wallet |
refunded |
52000 |
2026-02-22 22:51:00 |
2 |
| 343 |
P00362 |
O00326 |
bank_transfer |
successful |
225000 |
2026-01-02 02:18:00 |
1 |
| 344 |
P00363 |
O00326 |
bank_transfer |
refunded |
225000 |
2026-01-02 02:41:00 |
2 |
| 345 |
P00364 |
O00327 |
credit_card |
successful |
85000 |
2026-03-17 10:29:00 |
1 |
| 346 |
P00365 |
O00327 |
credit_card |
refunded |
85000 |
2026-03-17 10:52:00 |
2 |
| 347 |
P00366 |
O00328 |
cash |
successful |
137000 |
2026-02-07 06:50:00 |
1 |
| 348 |
P00367 |
O00328 |
cash |
refunded |
137000 |
2026-02-07 07:13:00 |
2 |
| 349 |
P00368 |
O00329 |
e_wallet |
successful |
128000 |
2026-03-27 19:30:00 |
1 |
| 350 |
P00369 |
O00329 |
e_wallet |
refunded |
128000 |
2026-03-27 19:53:00 |
2 |
| 351 |
P00370 |
O00330 |
bank_transfer |
successful |
117000 |
2026-03-10 03:23:00 |
1 |
| 352 |
P00371 |
O00330 |
bank_transfer |
refunded |
117000 |
2026-03-10 03:46:00 |
2 |
| 353 |
P00372 |
O00331 |
credit_card |
successful |
117000 |
2026-02-23 15:36:00 |
1 |
| 354 |
P00373 |
O00331 |
credit_card |
refunded |
117000 |
2026-02-23 15:59:00 |
2 |
| 355 |
P00374 |
O00332 |
cash |
successful |
382000 |
2026-02-11 21:53:00 |
1 |
| 356 |
P00375 |
O00332 |
cash |
refunded |
382000 |
2026-02-11 22:16:00 |
2 |
=== RINGKASAN PEMBAYARAN ===
|
order_id |
payment_status |
jumlah_baris |
total_nominal |
| 0 |
O00323 |
refunded |
1 |
323000 |
| 1 |
O00323 |
successful |
1 |
323000 |
| 2 |
O00324 |
refunded |
1 |
293000 |
| 3 |
O00324 |
successful |
1 |
293000 |
| 4 |
O00325 |
refunded |
1 |
52000 |
| 5 |
O00325 |
successful |
1 |
52000 |
| 6 |
O00326 |
refunded |
1 |
225000 |
| 7 |
O00326 |
successful |
1 |
225000 |
| 8 |
O00327 |
refunded |
1 |
85000 |
| 9 |
O00327 |
successful |
1 |
85000 |
| 10 |
O00328 |
refunded |
1 |
137000 |
| 11 |
O00328 |
successful |
1 |
137000 |
| 12 |
O00329 |
refunded |
1 |
128000 |
| 13 |
O00329 |
successful |
1 |
128000 |
| 14 |
O00330 |
refunded |
1 |
117000 |
| 15 |
O00330 |
successful |
1 |
117000 |
| 16 |
O00331 |
refunded |
1 |
117000 |
| 17 |
O00331 |
successful |
1 |
117000 |
| 18 |
O00332 |
refunded |
1 |
382000 |
| 19 |
O00332 |
successful |
1 |
382000 |
# 1. Cek semua pembayaran yang statusnya refunded
refunded_check = payments_raw[
payments_raw["payment_status"].str.lower().str.strip() == "refunded"
].sort_values(["order_id", "attempt_number"])
display(
refunded_check[
[
"payment_id",
"order_id",
"payment_status",
"payment_amount",
"attempt_number"
]
]
)
# 2. Ringkasan: status pembayaran berdasarkan attempt
display(
payments_raw.groupby(
["attempt_number", "payment_status"]
).size().reset_index(name="jumlah_baris")
)
|
payment_id |
order_id |
payment_status |
payment_amount |
attempt_number |
| 338 |
P00357 |
O00323 |
refunded |
323000 |
2 |
| 340 |
P00359 |
O00324 |
refunded |
293000 |
2 |
| 342 |
P00361 |
O00325 |
refunded |
52000 |
2 |
| 344 |
P00363 |
O00326 |
refunded |
225000 |
2 |
| 346 |
P00365 |
O00327 |
refunded |
85000 |
2 |
| 348 |
P00367 |
O00328 |
refunded |
137000 |
2 |
| 350 |
P00369 |
O00329 |
refunded |
128000 |
2 |
| 352 |
P00371 |
O00330 |
refunded |
117000 |
2 |
| 354 |
P00373 |
O00331 |
refunded |
117000 |
2 |
| 356 |
P00375 |
O00332 |
refunded |
382000 |
2 |
|
attempt_number |
payment_status |
jumlah_baris |
| 0 |
1 |
failed |
35 |
| 1 |
1 |
successful |
297 |
| 2 |
2 |
refunded |
10 |
| 3 |
2 |
successful |
15 |
attempt2_check = payments_raw[
payments_raw["attempt_number"] == 2
].merge(
orders_raw[["order_id", "order_status"]],
on="order_id",
how="left"
)
display(
attempt2_check[
[
"order_id",
"order_status",
"payment_status",
"payment_amount",
"attempt_number"
]
].sort_values("order_id")
)
|
order_id |
order_status |
payment_status |
payment_amount |
attempt_number |
| 0 |
O00005 |
completed |
successful |
34000 |
2 |
| 1 |
O00014 |
completed |
successful |
76000 |
2 |
| 2 |
O00033 |
completed |
successful |
149000 |
2 |
| 3 |
O00100 |
completed |
successful |
75000 |
2 |
| 4 |
O00110 |
completed |
successful |
68000 |
2 |
| 5 |
O00111 |
completed |
successful |
117000 |
2 |
| 6 |
O00131 |
completed |
successful |
158000 |
2 |
| 7 |
O00156 |
completed |
successful |
86000 |
2 |
| 8 |
O00207 |
completed |
successful |
67000 |
2 |
| 9 |
O00237 |
completed |
successful |
113000 |
2 |
| 10 |
O00250 |
completed |
successful |
54000 |
2 |
| 11 |
O00255 |
completed |
successful |
31000 |
2 |
| 12 |
O00267 |
completed |
successful |
205000 |
2 |
| 13 |
O00283 |
completed |
successful |
98000 |
2 |
| 14 |
O00299 |
completed |
successful |
68000 |
2 |
| 15 |
O00323 |
cancelled |
refunded |
323000 |
2 |
| 16 |
O00324 |
cancelled |
refunded |
293000 |
2 |
| 17 |
O00325 |
cancelled |
refunded |
52000 |
2 |
| 18 |
O00326 |
cancelled |
refunded |
225000 |
2 |
| 19 |
O00327 |
cancelled |
refunded |
85000 |
2 |
| 20 |
O00328 |
cancelled |
refunded |
137000 |
2 |
| 21 |
O00329 |
cancelled |
refunded |
128000 |
2 |
| 22 |
O00330 |
cancelled |
refunded |
117000 |
2 |
| 23 |
O00331 |
cancelled |
refunded |
117000 |
2 |
| 24 |
O00332 |
cancelled |
refunded |
382000 |
2 |
# Cek total master restoran dan restoran inactive
print("Total master restoran:", len(restaurants_raw))
display(
restaurants_raw[
restaurants_raw["restaurant_id"].isin(["R0019", "R0022"])
]
)
Total master restoran: 24
|
restaurant_id |
restaurant_name |
cuisine_type |
restaurant_zone |
price_tier |
onboarding_date |
active_status |
| 18 |
R0019 |
SwiftBite Partner 19 |
Fast Food |
South |
Premium |
2025-01-18 |
inactive |
| 21 |
R0022 |
SwiftBite Partner 22 |
Indonesian |
East |
Budget |
2024-08-01 |
inactive |
# Cek pesanan dari kedua restoran
display(
orders_raw[
orders_raw["restaurant_id"].isin(["R0019", "R0022"])
].groupby(
["restaurant_id", "order_status"]
).size().reset_index(name="jumlah_pesanan")
)
|
restaurant_id |
order_status |
jumlah_pesanan |
| 0 |
R0019 |
cancelled |
2 |
| 1 |
R0019 |
completed |
12 |
| 2 |
R0022 |
cancelled |
1 |
| 3 |
R0022 |
completed |
13 |
7. Candidate Findings Register
Gunakan judgment:
- `FIX`
- `STANDARDIZE`
- `KEEP`
- `INVESTIGATE`
Jangan isi judgment sebelum verification evidence cukup.
FINDING_COLUMNS = [
"finding_id",
"dataset",
"field_key",
"finding_evidence",
"why_it_matters",
"verification_method",
"verification_evidence_result",
"judgment",
"reason_for_judgment",
"cleaning_rule_id",
]
findings = pd.DataFrame(columns=FINDING_COLUMNS)
display(findings)
|
finding_id |
dataset |
field_key |
finding_evidence |
why_it_matters |
verification_method |
verification_evidence_result |
judgment |
reason_for_judgment |
cleaning_rule_id |
import pandas as pd
findings_data = []
new_finding = {
"finding_id": "F-01",
"dataset": "customers_raw",
"field_key": "customer_id",
"finding_evidence": "Customer ID C0031 teridentifikasi sebagai exact duplicate.",
"why_it_matters": "Duplikasi dapat menyebabkan pelanggan dihitung lebih dari satu kali.",
"verification_method": "Memeriksa duplikasi customer_id dan membandingkan seluruh kolom pada baris duplikat.",
"verification_evidence_result": "C0031 teridentifikasi sebagai exact duplicate.",
"judgment": "FIX",
"reason_for_judgment": "Duplikasi exact tidak menambah informasi baru dan berpotensi menggandakan perhitungan.",
"cleaning_rule_id": "CR-01",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-02",
"dataset": "customers_raw",
"field_key": "customer_segment",
"finding_evidence": "Terdapat variasi penulisan Regular dan REGULAR.",
"why_it_matters": "Kategori yang sama dapat terhitung sebagai kategori berbeda.",
"verification_method": "Memeriksa nilai unik customer_segment dan membandingkan kapitalisasi serta spasi.",
"verification_evidence_result": "Regular dan REGULAR menunjukkan inkonsistensi format.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Format kategori perlu diseragamkan tanpa mengubah maknanya.",
"cleaning_rule_id": "CR-02",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-03",
"dataset": "orders_raw",
"field_key": "order_status",
"finding_evidence": "Terdapat variasi penulisan completed dan COMPLETED.",
"why_it_matters": "Perbedaan kapitalisasi dapat menyebabkan agregasi status pesanan tidak akurat.",
"verification_method": "Memeriksa nilai unik order_status.",
"verification_evidence_result": "completed dan COMPLETED merupakan variasi format status yang sama.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Format status perlu diseragamkan untuk menjaga konsistensi analisis.",
"cleaning_rule_id": "CR-03",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-04",
"dataset": "payments_raw",
"field_key": "payment_method",
"finding_evidence": "Terdapat variasi penulisan e_wallet dan E WALLET.",
"why_it_matters": "Kategori metode pembayaran dapat terpecah dalam laporan.",
"verification_method": "Memeriksa nilai unik payment_method.",
"verification_evidence_result": "e_wallet dan E WALLET menunjukkan inkonsistensi format.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Format kategori metode pembayaran perlu diseragamkan.",
"cleaning_rule_id": "CR-04",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-05",
"dataset": "restaurants_raw",
"field_key": "price_tier",
"finding_evidence": "Terdapat variasi penulisan Mid dan MID.",
"why_it_matters": "Kategori tingkat harga yang sama dapat dihitung sebagai dua kategori berbeda.",
"verification_method": "Memeriksa nilai unik price_tier.",
"verification_evidence_result": "Mid dan MID berbeda dalam kapitalisasi.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Penulisan kategori perlu diseragamkan.",
"cleaning_rule_id": "CR-05",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-06",
"dataset": "restaurants_raw",
"field_key": "price_tier",
"finding_evidence": "Terdapat variasi penulisan Budget dan BUDGET.",
"why_it_matters": "Kategori tingkat harga yang sama dapat terhitung sebagai kategori berbeda.",
"verification_method": "Memeriksa nilai unik price_tier.",
"verification_evidence_result": "Budget dan BUDGET berbeda dalam kapitalisasi.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Penulisan kategori perlu diseragamkan.",
"cleaning_rule_id": "CR-06",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-07",
"dataset": "order_items_raw",
"field_key": "item_category",
"finding_evidence": "Terdapat variasi penulisan Snack dan SNACK.",
"why_it_matters": "Kategori item dapat terhitung sebagai dua kategori berbeda.",
"verification_method": "Memeriksa nilai unik item_category.",
"verification_evidence_result": "Snack dan SNACK berbeda dalam kapitalisasi.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Penulisan kategori item perlu diseragamkan.",
"cleaning_rule_id": "CR-07",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-08",
"dataset": "orders_raw",
"field_key": "promo_code",
"finding_evidence": "Sebanyak 176 baris memiliki promo_code kosong.",
"why_it_matters": "Nilai kosong perlu dibedakan antara data hilang dan pesanan yang memang tidak menggunakan promo.",
"verification_method": "Menghitung nilai kosong promo_code dan memeriksa apakah penggunaan promo bersifat opsional.",
"verification_evidence_result": "Ketiadaan kode promo dapat merepresentasikan pesanan tanpa promo.",
"judgment": "KEEP",
"reason_for_judgment": "Nilai kosong dapat valid jika pesanan tidak menggunakan promo.",
"cleaning_rule_id": "CR-08",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-09",
"dataset": "orders_raw",
"field_key": "subtotal_amount, total_amount",
"finding_evidence": "Rekonsiliasi nominal menemukan ketidaksesuaian pada order O00049, O00182, dan O00201.",
"why_it_matters": "Ketidaksesuaian nominal dapat memengaruhi akurasi laporan transaksi.",
"verification_method": "Membandingkan subtotal dengan jumlah line_amount dan memeriksa perhitungan total.",
"verification_evidence_result": "Ditemukan mismatch pada O00049, O00182, dan O00201. Subtotal O00201 yang diharapkan Rp125.000 dan total Rp138.000.",
"judgment": "FIX",
"reason_for_judgment": "Nilai yang tidak sesuai perlu dikoreksi berdasarkan hasil rekonsiliasi terverifikasi.",
"cleaning_rule_id": "CR-09",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-10",
"dataset": "order_items_raw",
"field_key": "line_amount",
"finding_evidence": "OI00131 pada order O00049 untuk Iced Tea tercatat Rp34.000, sedangkan nilai yang diharapkan Rp27.000.",
"why_it_matters": "Kesalahan line_amount dapat menyebabkan subtotal pesanan tidak akurat.",
"verification_method": "Menghitung ulang line_amount berdasarkan quantity, unit_price, dan diskon yang berlaku.",
"verification_evidence_result": "OI00131 memiliki line_amount tercatat Rp34.000, sedangkan hasil perhitungan yang diharapkan Rp27.000.",
"judgment": "FIX",
"reason_for_judgment": "Nilai perlu dikoreksi setelah formula dan bukti transaksi diverifikasi.",
"cleaning_rule_id": "CR-10",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-11",
"dataset": "order_items_raw",
"field_key": "line_amount",
"finding_evidence": "OI00511 pada order O00182 untuk Coffee tercatat Rp31.000, sedangkan nilai yang diharapkan Rp36.000.",
"why_it_matters": "Kesalahan line_amount dapat menyebabkan subtotal pesanan tidak akurat.",
"verification_method": "Menghitung ulang line_amount berdasarkan quantity, unit_price, dan diskon yang berlaku.",
"verification_evidence_result": "OI00511 memiliki line_amount tercatat Rp31.000, sedangkan hasil perhitungan yang diharapkan Rp36.000.",
"judgment": "FIX",
"reason_for_judgment": "Nilai perlu dikoreksi setelah formula dan bukti transaksi diverifikasi.",
"cleaning_rule_id": "CR-11",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-12",
"dataset": "order_items_raw",
"field_key": "quantity",
"finding_evidence": "OI00071 dan OI00215 memiliki quantity berbentuk teks, yaitu '2 pcs' dan '1 pcs'.",
"why_it_matters": "Nilai quantity berbentuk teks dapat menghambat perhitungan numerik.",
"verification_method": "Memeriksa tipe data quantity dan mengidentifikasi nilai yang mengandung satuan teks.",
"verification_evidence_result": "OI00071 dan OI00215 mengandung satuan pcs yang perlu dipisahkan dari nilai numerik.",
"judgment": "STANDARDIZE",
"reason_for_judgment": "Quantity perlu dikonversi ke format numerik.",
"cleaning_rule_id": "CR-12",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-13",
"dataset": "order_items_raw, orders_raw, payments_raw",
"field_key": "unit_price, quantity, line_amount, subtotal_amount, total_amount",
"finding_evidence": "Order O00084 memiliki item Coffee dengan unit_price Rp508.000 dan quantity 4, sehingga line_amount Rp2.032.000. Harga Coffee yang umum ditemukan sekitar Rp18.000.",
"why_it_matters": "Nilai ekstrem dapat berasal dari kesalahan input atau transaksi khusus dan memengaruhi analisis harga.",
"verification_method": "Membandingkan unit_price dengan harga Coffee lainnya serta memeriksa rekonsiliasi nominal dan pembayaran.",
"verification_evidence_result": "Subtotal Rp2.094.000, biaya pengiriman Rp18.000, diskon Rp15.000, dan total Rp2.097.000 konsisten secara aritmetika; pembayaran juga cocok.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Nilai ekstrem belum tentu salah karena nominal transaksi konsisten. Perlu verifikasi sumber transaksi sebelum koreksi.",
"cleaning_rule_id": "CR-13",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-14",
"dataset": "payments_raw, orders_raw",
"field_key": "order_id, payment_attempt, payment_status, order_status",
"finding_evidence": "Sepuluh order cancelled O00323–O00332 memiliki payment attempt pertama successful dan attempt kedua refunded dengan nominal sama. Dari 25 baris attempt kedua, 15 successful terkait order completed dan 10 refunded terkait order cancelled.",
"why_it_matters": "Riwayat pembayaran berulang dapat disalahartikan sebagai duplikasi jika konteks payment attempt diabaikan.",
"verification_method": "Menghubungkan payments_raw dengan orders_raw dan membandingkan payment attempt, status pembayaran, status pesanan, serta nominal.",
"verification_evidence_result": "Status attempt kedua selaras dengan status order: successful pada order completed dan refunded pada order cancelled.",
"judgment": "KEEP",
"reason_for_judgment": "Baris tersebut dapat merepresentasikan riwayat pembayaran dan refund yang valid, bukan duplikasi yang perlu dihapus.",
"cleaning_rule_id": "CR-14",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
new_finding = {
"finding_id": "F-15",
"dataset": "restaurants_raw, orders_raw",
"field_key": "restaurant_id, active_status, order_timestamp, order_status",
"finding_evidence": "Dua dari 24 restoran berstatus inactive, yaitu R0019 dan R0022. R0019 memiliki 12 order completed dan 2 cancelled, sedangkan R0022 memiliki 13 order completed dan 1 cancelled.",
"why_it_matters": "Restoran inactive yang memiliki riwayat pesanan perlu diperiksa agar status operasional tidak disalahartikan.",
"verification_method": "Menghubungkan restaurants_raw dengan orders_raw berdasarkan restaurant_id dan memeriksa status pesanan serta ketersediaan tanggal perubahan status.",
"verification_evidence_result": "R0019 dan R0022 memiliki riwayat pesanan, tetapi tidak tersedia timestamp perubahan status untuk memastikan kapan restoran dinonaktifkan.",
"judgment": "INVESTIGATE",
"reason_for_judgment": "Pesanan mungkin terjadi sebelum restoran dinonaktifkan sehingga perlu konfirmasi sebelum menyimpulkan adanya kesalahan.",
"cleaning_rule_id": "CR-15",
}
findings_data.append(new_finding)
findings.loc[len(findings)] = new_finding
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_raw |
customer_id |
Customer ID C0031 teridentifikasi sebagai exac... |
Duplikasi dapat menyebabkan pelanggan dihitung... |
Memeriksa duplikasi customer_id dan membanding... |
C0031 teridentifikasi sebagai exact duplicate. |
FIX |
Duplikasi exact tidak menambah informasi baru ... |
CR-01 |
| 1 |
F-02 |
customers_raw |
customer_segment |
Terdapat variasi penulisan Regular dan REGULAR. |
Kategori yang sama dapat terhitung sebagai kat... |
Memeriksa nilai unik customer_segment dan memb... |
Regular dan REGULAR menunjukkan inkonsistensi ... |
STANDARDIZE |
Format kategori perlu diseragamkan tanpa mengu... |
CR-02 |
| 2 |
F-03 |
orders_raw |
order_status |
Terdapat variasi penulisan completed dan COMPL... |
Perbedaan kapitalisasi dapat menyebabkan agreg... |
Memeriksa nilai unik order_status. |
completed dan COMPLETED merupakan variasi form... |
STANDARDIZE |
Format status perlu diseragamkan untuk menjaga... |
CR-03 |
| 3 |
F-04 |
payments_raw |
payment_method |
Terdapat variasi penulisan e_wallet dan E WALLET. |
Kategori metode pembayaran dapat terpecah dala... |
Memeriksa nilai unik payment_method. |
e_wallet dan E WALLET menunjukkan inkonsistens... |
STANDARDIZE |
Format kategori metode pembayaran perlu disera... |
CR-04 |
| 4 |
F-05 |
restaurants_raw |
price_tier |
Terdapat variasi penulisan Mid dan MID. |
Kategori tingkat harga yang sama dapat dihitun... |
Memeriksa nilai unik price_tier. |
Mid dan MID berbeda dalam kapitalisasi. |
STANDARDIZE |
Penulisan kategori perlu diseragamkan. |
CR-05 |
| 5 |
F-06 |
restaurants_raw |
price_tier |
Terdapat variasi penulisan Budget dan BUDGET. |
Kategori tingkat harga yang sama dapat terhitu... |
Memeriksa nilai unik price_tier. |
Budget dan BUDGET berbeda dalam kapitalisasi. |
STANDARDIZE |
Penulisan kategori perlu diseragamkan. |
CR-06 |
| 6 |
F-07 |
order_items_raw |
item_category |
Terdapat variasi penulisan Snack dan SNACK. |
Kategori item dapat terhitung sebagai dua kate... |
Memeriksa nilai unik item_category. |
Snack dan SNACK berbeda dalam kapitalisasi. |
STANDARDIZE |
Penulisan kategori item perlu diseragamkan. |
CR-07 |
| 7 |
F-08 |
orders_raw |
promo_code |
Sebanyak 176 baris memiliki promo_code kosong. |
Nilai kosong perlu dibedakan antara data hilan... |
Menghitung nilai kosong promo_code dan memerik... |
Ketiadaan kode promo dapat merepresentasikan p... |
KEEP |
Nilai kosong dapat valid jika pesanan tidak me... |
CR-08 |
| 8 |
F-09 |
orders_raw |
subtotal_amount, total_amount |
Rekonsiliasi nominal menemukan ketidaksesuaian... |
Ketidaksesuaian nominal dapat memengaruhi akur... |
Membandingkan subtotal dengan jumlah line_amou... |
Ditemukan mismatch pada O00049, O00182, dan O0... |
FIX |
Nilai yang tidak sesuai perlu dikoreksi berdas... |
CR-09 |
| 9 |
F-10 |
order_items_raw |
line_amount |
OI00131 pada order O00049 untuk Iced Tea terca... |
Kesalahan line_amount dapat menyebabkan subtot... |
Menghitung ulang line_amount berdasarkan quant... |
OI00131 memiliki line_amount tercatat Rp34.000... |
FIX |
Nilai perlu dikoreksi setelah formula dan bukt... |
CR-10 |
| 10 |
F-11 |
order_items_raw |
line_amount |
OI00511 pada order O00182 untuk Coffee tercata... |
Kesalahan line_amount dapat menyebabkan subtot... |
Menghitung ulang line_amount berdasarkan quant... |
OI00511 memiliki line_amount tercatat Rp31.000... |
FIX |
Nilai perlu dikoreksi setelah formula dan bukt... |
CR-11 |
| 11 |
F-12 |
order_items_raw |
quantity |
OI00071 dan OI00215 memiliki quantity berbentu... |
Nilai quantity berbentuk teks dapat menghambat... |
Memeriksa tipe data quantity dan mengidentifik... |
OI00071 dan OI00215 mengandung satuan pcs yang... |
STANDARDIZE |
Quantity perlu dikonversi ke format numerik. |
CR-12 |
| 12 |
F-13 |
order_items_raw, orders_raw, payments_raw |
unit_price, quantity, line_amount, subtotal_am... |
Order O00084 memiliki item Coffee dengan unit_... |
Nilai ekstrem dapat berasal dari kesalahan inp... |
Membandingkan unit_price dengan harga Coffee l... |
Subtotal Rp2.094.000, biaya pengiriman Rp18.00... |
INVESTIGATE |
Nilai ekstrem belum tentu salah karena nominal... |
CR-13 |
| 13 |
F-14 |
payments_raw, orders_raw |
order_id, payment_attempt, payment_status, ord... |
Sepuluh order cancelled O00323–O00332 memiliki... |
Riwayat pembayaran berulang dapat disalahartik... |
Menghubungkan payments_raw dengan orders_raw d... |
Status attempt kedua selaras dengan status ord... |
KEEP |
Baris tersebut dapat merepresentasikan riwayat... |
CR-14 |
| 14 |
F-15 |
restaurants_raw, orders_raw |
restaurant_id, active_status, order_timestamp,... |
Dua dari 24 restoran berstatus inactive, yaitu... |
Restoran inactive yang memiliki riwayat pesana... |
Menghubungkan restaurants_raw dengan orders_ra... |
R0019 dan R0022 memiliki riwayat pesanan, teta... |
INVESTIGATE |
Pesanan mungkin terjadi sebelum restoran dinon... |
CR-15 |
Template menambahkan finding
```python
new_finding = {
"finding_id": "F-01",
"dataset": "...",
"field_key": "...",
"finding_evidence": "...",
"why_it_matters": "...",
"verification_method": "...",
"verification_evidence_result": "...",
"judgment": "FIX / STANDARDIZE / KEEP / INVESTIGATE",
"reason_for_judgment": "...",
"cleaning_rule_id": "CR-01 atau kosong",
}
findings.loc[len(findings)] = new_finding
```
8. Cross-Table Verification
def fk_check(child_df, parent_df, child_key, parent_key, relationship_name):
child_keys = child_df[[child_key]].drop_duplicates()
parent_keys = parent_df[[parent_key]].drop_duplicates()
merged = child_keys.merge(
parent_keys,
left_on=child_key,
right_on=parent_key,
how="left",
indicator=True,
)
unmatched = merged.loc[merged["_merge"] == "left_only", child_key]
return {
"relationship": relationship_name,
"child_distinct_keys": len(child_keys),
"unmatched_count": len(unmatched),
"unmatched_sample": unmatched.head(10).tolist(),
}
if DATA_READY:
relationship_checks = pd.DataFrame([
fk_check(orders_raw, customers_raw, "customer_id", "customer_id", "Orders → Customers"),
fk_check(orders_raw, restaurants_raw, "restaurant_id", "restaurant_id", "Orders → Restaurants"),
fk_check(order_items_raw, orders_raw, "order_id", "order_id", "Order Items → Orders"),
fk_check(payments_raw, orders_raw, "order_id", "order_id", "Payments → Orders"),
])
display(relationship_checks)
|
relationship |
child_distinct_keys |
unmatched_count |
unmatched_sample |
| 0 |
Orders → Customers |
169 |
0 |
[] |
| 1 |
Orders → Restaurants |
24 |
0 |
[] |
| 2 |
Order Items → Orders |
332 |
0 |
[] |
| 3 |
Payments → Orders |
332 |
0 |
[] |
9. Business-Rule Verification
### Order Items
`line_amount = quantity × unit_price − line_discount`
### Orders
`total_amount = subtotal_amount + delivery_fee − discount_amount`
Cell ini hanya membuat verification view. Tidak melakukan cleaning.
if DATA_READY:
item_verify = order_items_raw.copy()
item_verify["expected_line_amount"] = (
pd.to_numeric(item_verify["quantity"], errors="coerce")
* pd.to_numeric(item_verify["unit_price"], errors="coerce")
- pd.to_numeric(item_verify["line_discount"], errors="coerce")
)
item_verify["line_formula_match"] = (
pd.to_numeric(item_verify["line_amount"], errors="coerce")
== item_verify["expected_line_amount"]
)
display(
item_verify[
["order_item_id", "order_id", "quantity", "unit_price",
"line_discount", "line_amount", "expected_line_amount", "line_formula_match"]
].head()
)
order_verify = orders_raw.copy()
order_verify["expected_total_amount"] = (
pd.to_numeric(order_verify["subtotal_amount"], errors="coerce")
+ pd.to_numeric(order_verify["delivery_fee"], errors="coerce")
- pd.to_numeric(order_verify["discount_amount"], errors="coerce")
)
order_verify["order_formula_match"] = (
pd.to_numeric(order_verify["total_amount"], errors="coerce")
== order_verify["expected_total_amount"]
)
display(
order_verify[
["order_id", "subtotal_amount", "delivery_fee", "discount_amount",
"total_amount", "expected_total_amount", "order_formula_match"]
].head()
)
|
order_item_id |
order_id |
quantity |
unit_price |
line_discount |
line_amount |
expected_line_amount |
line_formula_match |
| 0 |
OI00001 |
O00001 |
4 |
24000 |
0 |
96000 |
96000.0 |
True |
| 1 |
OI00002 |
O00001 |
2 |
22000 |
0 |
44000 |
44000.0 |
True |
| 2 |
OI00003 |
O00001 |
4 |
42000 |
5000 |
163000 |
163000.0 |
True |
| 3 |
OI00004 |
O00001 |
1 |
22000 |
0 |
22000 |
22000.0 |
True |
| 4 |
OI00005 |
O00001 |
2 |
22000 |
2000 |
42000 |
42000.0 |
True |
|
order_id |
subtotal_amount |
delivery_fee |
discount_amount |
total_amount |
expected_total_amount |
order_formula_match |
| 0 |
O00001 |
367000 |
12000 |
0 |
379000 |
379000 |
True |
| 1 |
O00002 |
163000 |
15000 |
0 |
178000 |
178000 |
True |
| 2 |
O00003 |
240000 |
10000 |
10000 |
240000 |
240000 |
True |
| 3 |
O00004 |
68000 |
10000 |
5000 |
73000 |
73000 |
True |
| 4 |
O00005 |
24000 |
10000 |
0 |
34000 |
34000 |
True |
# Tampilkan Order Items yang formula-nya mismatch (False)
item_mismatch = item_verify[
item_verify["line_formula_match"] == False
]
print("Total baris yang diperiksa:", len(item_verify))
print("Jumlah formula mismatch:", len(item_mismatch))
display(
item_mismatch[
[
"order_item_id",
"order_id",
"quantity",
"unit_price",
"line_discount",
"line_amount",
"expected_line_amount",
"line_formula_match"
]
]
)
Total baris yang diperiksa: 944
Jumlah formula mismatch: 4
|
order_item_id |
order_id |
quantity |
unit_price |
line_discount |
line_amount |
expected_line_amount |
line_formula_match |
| 70 |
OI00071 |
O00027 |
2 pcs |
9000 |
2000 |
16000 |
NaN |
False |
| 130 |
OI00131 |
O00049 |
3 |
9000 |
0 |
34000 |
27000.0 |
False |
| 214 |
OI00215 |
O00078 |
1 pcs |
32000 |
5000 |
27000 |
NaN |
False |
| 510 |
OI00511 |
O00182 |
2 |
18000 |
0 |
31000 |
36000.0 |
False |
# 1. Lihat semua variasi nilai quantity dan frekuensinya
print("Ragam nilai quantity:")
display(
order_items_raw["quantity"]
.value_counts(dropna=False)
.rename_axis("quantity")
.reset_index(name="jumlah")
)
Ragam nilai quantity:
|
quantity |
jumlah |
| 0 |
3 |
331 |
| 1 |
2 |
309 |
| 2 |
1 |
248 |
| 3 |
4 |
54 |
| 4 |
2 pcs |
1 |
| 5 |
1 pcs |
1 |
# 1. Periksa seluruh item dari order O00201
order_id_target = "O00201"
display(
item_verify[
item_verify["order_id"].astype("string").str.strip()
== order_id_target
][[
"order_item_id",
"order_id",
"quantity",
"unit_price",
"line_discount",
"line_amount",
"expected_line_amount",
"line_formula_match"
]]
)
# 2. Periksa data order O00201
display(
order_verify[
order_verify["order_id"].astype("string").str.strip()
== order_id_target
][[
"order_id",
"subtotal_amount",
"delivery_fee",
"discount_amount",
"total_amount",
"expected_total_amount",
"order_formula_match"
]]
)
|
order_item_id |
order_id |
quantity |
unit_price |
line_discount |
line_amount |
expected_line_amount |
line_formula_match |
| 564 |
OI00565 |
O00201 |
3 |
24000 |
0 |
72000 |
72000.0 |
True |
| 565 |
OI00566 |
O00201 |
1 |
26000 |
5000 |
21000 |
21000.0 |
True |
| 566 |
OI00567 |
O00201 |
1 |
32000 |
0 |
32000 |
32000.0 |
True |
|
order_id |
subtotal_amount |
delivery_fee |
discount_amount |
total_amount |
expected_total_amount |
order_formula_match |
| 200 |
O00201 |
135000 |
18000 |
5000 |
148000 |
148000 |
True |
# Hitung subtotal berdasarkan seluruh item
item_subtotal = (
item_verify.groupby("order_id")["line_amount"]
.sum()
.rename("calculated_subtotal")
)
# Gabungkan dengan data order
subtotal_check = order_verify.merge(
item_subtotal,
on="order_id",
how="left"
)
# Bandingkan subtotal aktual dengan subtotal hasil agregasi
subtotal_check["subtotal_match"] = (
pd.to_numeric(
subtotal_check["subtotal_amount"], errors="coerce"
).round(2)
==
pd.to_numeric(
subtotal_check["calculated_subtotal"], errors="coerce"
).round(2)
)
# Periksa order O00201
display(
subtotal_check[
subtotal_check["order_id"] == "O00201"
][[
"order_id",
"subtotal_amount",
"calculated_subtotal",
"subtotal_match"
]]
)
|
order_id |
subtotal_amount |
calculated_subtotal |
subtotal_match |
| 200 |
O00201 |
135000 |
125000 |
False |
target_order = "O00201"
# Tampilkan semua item milik order tersebut
target_items = item_verify[
item_verify["order_id"].astype("string").str.strip()
== target_order
]
display(target_items[[
"order_item_id",
"quantity",
"unit_price",
"line_discount",
"line_amount",
"expected_line_amount",
"line_formula_match"
]])
print("Jumlah baris item:", len(target_items))
print(
"Total line_amount:",
pd.to_numeric(target_items["line_amount"], errors="coerce").sum()
)
|
order_item_id |
quantity |
unit_price |
line_discount |
line_amount |
expected_line_amount |
line_formula_match |
| 564 |
OI00565 |
3 |
24000 |
0 |
72000 |
72000.0 |
True |
| 565 |
OI00566 |
1 |
26000 |
5000 |
21000 |
21000.0 |
True |
| 566 |
OI00567 |
1 |
32000 |
0 |
32000 |
32000.0 |
True |
Jumlah baris item: 3
Total line_amount: 125000
# Cek baris mentah order O00201
display(
orders_raw[
orders_raw["order_id"].astype("string").str.strip()
== "O00201"
][[
"order_id",
"subtotal_amount",
"delivery_fee",
"discount_amount",
"total_amount"
]]
)
# Cek jumlah item dan subtotal dari data mentah
target_items_raw = order_items_raw[
order_items_raw["order_id"].astype("string").str.strip()
== "O00201"
]
print("Jumlah item:", len(target_items_raw))
print(
"Total line_amount mentah:",
pd.to_numeric(
target_items_raw["line_amount"], errors="coerce"
).sum()
)
display(target_items_raw)
|
order_id |
subtotal_amount |
delivery_fee |
discount_amount |
total_amount |
| 200 |
O00201 |
135000 |
18000 |
5000 |
148000 |
Jumlah item: 3
Total line_amount mentah: 125000
|
order_item_id |
order_id |
line_number |
item_name |
item_category |
quantity |
unit_price |
line_discount |
line_amount |
| 564 |
OI00565 |
O00201 |
1 |
Cake Slice |
Dessert |
3 |
24000 |
0 |
72000 |
| 565 |
OI00566 |
O00201 |
2 |
Mie Special |
Main |
1 |
26000 |
5000 |
21000 |
| 566 |
OI00567 |
O00201 |
3 |
Rice Bowl |
Main |
1 |
32000 |
0 |
32000 |
if DATA_READY:
# Hitung subtotal dari seluruh item per order
item_subtotal = (
item_verify.groupby("order_id")["line_amount"]
.sum(min_count=1)
.rename("calculated_subtotal")
)
# Gabungkan hasil agregasi ke data order
order_verify = order_verify.merge(
item_subtotal,
on="order_id",
how="left",
validate="one_to_one"
)
# Validasi subtotal dengan total item
order_verify["subtotal_match"] = (
pd.to_numeric(
order_verify["subtotal_amount"], errors="coerce"
).round(2)
==
pd.to_numeric(
order_verify["calculated_subtotal"], errors="coerce"
).round(2)
)
# Tampilkan order dengan subtotal tidak cocok
subtotal_mismatch = order_verify[
order_verify["subtotal_match"] == False
]
display(
subtotal_mismatch[[
"order_id",
"subtotal_amount",
"calculated_subtotal",
"subtotal_match"
]]
)
|
order_id |
subtotal_amount |
calculated_subtotal |
subtotal_match |
| 48 |
O00049 |
187000 |
194000 |
False |
| 181 |
O00182 |
222000 |
217000 |
False |
| 200 |
O00201 |
135000 |
125000 |
False |
display(
payments_raw[
payments_raw["order_id"] == "O00201"
].T
)
|
208 |
| payment_id |
P00226 |
| order_id |
O00201 |
| payment_method |
e_wallet |
| payment_status |
successful |
| payment_amount |
138000 |
| payment_timestamp |
2026-03-11 02:19:00 |
| attempt_number |
1 |
display(
orders_raw[
orders_raw["order_id"] == "O00201"
].T
)
|
200 |
| order_id |
O00201 |
| customer_id |
C0088 |
| restaurant_id |
R0012 |
| order_timestamp |
2026-03-11 02:16:00 |
| order_status |
completed |
| promo_code |
NaN |
| subtotal_amount |
135000 |
| delivery_fee |
18000 |
| discount_amount |
5000 |
| total_amount |
148000 |
display(
order_items_raw[
order_items_raw["order_id"] == "O00201"
]
)
|
order_item_id |
order_id |
line_number |
item_name |
item_category |
quantity |
unit_price |
line_discount |
line_amount |
| 564 |
OI00565 |
O00201 |
1 |
Cake Slice |
Dessert |
3 |
24000 |
0 |
72000 |
| 565 |
OI00566 |
O00201 |
2 |
Mie Special |
Main |
1 |
26000 |
5000 |
21000 |
| 566 |
OI00567 |
O00201 |
3 |
Rice Bowl |
Main |
1 |
32000 |
0 |
32000 |
target_orders = ["O00049", "O00182", "O00201"]
display(
item_verify[
item_verify["order_id"].astype("string").str.strip()
.isin(target_orders)
][[
"order_item_id",
"order_id",
"line_number",
"item_name",
"quantity",
"unit_price",
"line_discount",
"line_amount",
"expected_line_amount",
"line_formula_match"
]].sort_values(["order_id", "line_number"])
)
|
order_item_id |
order_id |
line_number |
item_name |
quantity |
unit_price |
line_discount |
line_amount |
expected_line_amount |
line_formula_match |
| 128 |
OI00129 |
O00049 |
1 |
Coffee |
2 |
18000 |
2000 |
34000 |
34000.0 |
True |
| 129 |
OI00130 |
O00049 |
2 |
Chicken Set |
3 |
42000 |
0 |
126000 |
126000.0 |
True |
| 130 |
OI00131 |
O00049 |
3 |
Iced Tea |
3 |
9000 |
0 |
34000 |
27000.0 |
False |
| 507 |
OI00508 |
O00182 |
1 |
Chicken Set |
2 |
42000 |
5000 |
79000 |
79000.0 |
True |
| 508 |
OI00509 |
O00182 |
2 |
Cake Slice |
1 |
24000 |
5000 |
19000 |
19000.0 |
True |
| 509 |
OI00510 |
O00182 |
3 |
Coffee |
2 |
18000 |
2000 |
34000 |
34000.0 |
True |
| 510 |
OI00511 |
O00182 |
4 |
Coffee |
2 |
18000 |
0 |
31000 |
36000.0 |
False |
| 511 |
OI00512 |
O00182 |
5 |
Nasi Ayam |
2 |
28000 |
2000 |
54000 |
54000.0 |
True |
| 564 |
OI00565 |
O00201 |
1 |
Cake Slice |
3 |
24000 |
0 |
72000 |
72000.0 |
True |
| 565 |
OI00566 |
O00201 |
2 |
Mie Special |
1 |
26000 |
5000 |
21000 |
21000.0 |
True |
| 566 |
OI00567 |
O00201 |
3 |
Rice Bowl |
1 |
32000 |
0 |
32000 |
32000.0 |
True |
# 1. Formula item yang mismatch
item_mismatch = item_verify[
item_verify["line_formula_match"] == False
]
# 2. Formula total order yang mismatch
order_formula_mismatch = order_verify[
order_verify["order_formula_match"] == False
]
# 3. Subtotal yang tidak cocok dengan jumlah line_amount
subtotal_mismatch = order_verify[
order_verify["subtotal_match"] == False
]
print("=== RINGKASAN VALIDASI ===")
print("Item formula mismatch:", len(item_mismatch))
print("Order formula mismatch:", len(order_formula_mismatch))
print("Subtotal mismatch:", len(subtotal_mismatch))
print("\n=== ITEM BERMASALAH ===")
display(
item_mismatch[
[
"order_item_id",
"order_id",
"line_amount",
"expected_line_amount"
]
]
)
print("\n=== SUBTOTAL BERMASALAH ===")
display(
subtotal_mismatch[
[
"order_id",
"subtotal_amount",
"calculated_subtotal",
"subtotal_match"
]
]
)
=== RINGKASAN VALIDASI ===
Item formula mismatch: 4
Order formula mismatch: 0
Subtotal mismatch: 3
=== ITEM BERMASALAH ===
|
order_item_id |
order_id |
line_amount |
expected_line_amount |
| 70 |
OI00071 |
O00027 |
16000 |
NaN |
| 130 |
OI00131 |
O00049 |
34000 |
27000.0 |
| 214 |
OI00215 |
O00078 |
27000 |
NaN |
| 510 |
OI00511 |
O00182 |
31000 |
36000.0 |
=== SUBTOTAL BERMASALAH ===
|
order_id |
subtotal_amount |
calculated_subtotal |
subtotal_match |
| 48 |
O00049 |
187000 |
194000 |
False |
| 181 |
O00182 |
222000 |
217000 |
False |
| 200 |
O00201 |
135000 |
125000 |
False |
Orders ↔ Order Items Reconciliation
Lakukan reconciliation setelah memahami item-level validity. Jangan langsung mengoreksi Orders jika item detail sendiri belum diverifikasi.
if DATA_READY:
item_amount_numeric = pd.to_numeric(order_items_raw["line_amount"], errors="coerce")
item_agg_raw = (
order_items_raw.assign(_line_amount_numeric=item_amount_numeric)
.groupby("order_id", as_index=False)["_line_amount_numeric"]
.sum(min_count=1)
.rename(columns={"_line_amount_numeric": "sum_line_amount"})
)
order_item_recon_raw = orders_raw[
["order_id", "subtotal_amount"]
].merge(item_agg_raw, on="order_id", how="left")
order_item_recon_raw["subtotal_numeric"] = pd.to_numeric(
order_item_recon_raw["subtotal_amount"], errors="coerce"
)
order_item_recon_raw["reconcile_match"] = (
order_item_recon_raw["subtotal_numeric"]
== order_item_recon_raw["sum_line_amount"]
)
display(order_item_recon_raw.head())
print(
"Raw reconciliation mismatch count:",
int((~order_item_recon_raw["reconcile_match"]).sum())
)
|
order_id |
subtotal_amount |
sum_line_amount |
subtotal_numeric |
reconcile_match |
| 0 |
O00001 |
367000 |
367000 |
367000 |
True |
| 1 |
O00002 |
163000 |
163000 |
163000 |
True |
| 2 |
O00003 |
240000 |
240000 |
240000 |
True |
| 3 |
O00004 |
68000 |
68000 |
68000 |
True |
| 4 |
O00005 |
24000 |
24000 |
24000 |
True |
Raw reconciliation mismatch count: 3
# Tampilkan order dengan subtotal yang tidak sesuai
raw_mismatch = order_item_recon_raw[
order_item_recon_raw["reconcile_match"] == False
].copy()
raw_mismatch["difference"] = (
raw_mismatch["subtotal_numeric"]
- raw_mismatch["sum_line_amount"]
)
display(raw_mismatch)
|
order_id |
subtotal_amount |
sum_line_amount |
subtotal_numeric |
reconcile_match |
difference |
| 48 |
O00049 |
187000 |
194000 |
187000 |
False |
-7000 |
| 181 |
O00182 |
222000 |
217000 |
222000 |
False |
5000 |
| 200 |
O00201 |
135000 |
125000 |
135000 |
False |
10000 |
# Order yang akan diinvestigasi
target_orders = ["O00049", "O00182"]
# 1. Ringkasan order
print("=== ORDERS_RAW ===")
display(
orders_raw[
orders_raw["order_id"].isin(target_orders)
].T
)
# 2. Rincian item
print("=== ORDER_ITEMS_RAW ===")
display(
order_items_raw[
order_items_raw["order_id"].isin(target_orders)
]
)
# 3. Pembayaran
print("=== PAYMENTS_RAW ===")
display(
payments_raw[
payments_raw["order_id"].isin(target_orders)
]
)
=== ORDERS_RAW ===
|
48 |
181 |
| order_id |
O00049 |
O00182 |
| customer_id |
C0038 |
C0124 |
| restaurant_id |
R0020 |
R0013 |
| order_timestamp |
2026-03-14 09:50:00 |
2026-01-17 13:54:00 |
| order_status |
completed |
completed |
| promo_code |
NaN |
NEWUSER |
| subtotal_amount |
187000 |
222000 |
| delivery_fee |
8000 |
12000 |
| discount_amount |
5000 |
15000 |
| total_amount |
190000 |
219000 |
=== ORDER_ITEMS_RAW ===
|
order_item_id |
order_id |
line_number |
item_name |
item_category |
quantity |
unit_price |
line_discount |
line_amount |
| 128 |
OI00129 |
O00049 |
1 |
Coffee |
Drink |
2 |
18000 |
2000 |
34000 |
| 129 |
OI00130 |
O00049 |
2 |
Chicken Set |
Main |
3 |
42000 |
0 |
126000 |
| 130 |
OI00131 |
O00049 |
3 |
Iced Tea |
Drink |
3 |
9000 |
0 |
34000 |
| 507 |
OI00508 |
O00182 |
1 |
Chicken Set |
Main |
2 |
42000 |
5000 |
79000 |
| 508 |
OI00509 |
O00182 |
2 |
Cake Slice |
Dessert |
1 |
24000 |
5000 |
19000 |
| 509 |
OI00510 |
O00182 |
3 |
Coffee |
Drink |
2 |
18000 |
2000 |
34000 |
| 510 |
OI00511 |
O00182 |
4 |
Coffee |
Drink |
2 |
18000 |
0 |
31000 |
| 511 |
OI00512 |
O00182 |
5 |
Nasi Ayam |
Main |
2 |
28000 |
2000 |
54000 |
=== PAYMENTS_RAW ===
|
payment_id |
order_id |
payment_method |
payment_status |
payment_amount |
payment_timestamp |
attempt_number |
| 51 |
P00058 |
O00049 |
e_wallet |
successful |
190000 |
2026-03-14 09:53:00 |
1 |
| 189 |
P00204 |
O00182 |
bank_transfer |
successful |
219000 |
2026-01-17 13:57:00 |
1 |
10. Cleaning Rulebook
Rulebook dibuat setelah verification dan sebelum coding.
Gunakan hanya `FIX` atau `STANDARDIZE` sebagai cleaning decision. Finding `KEEP` tidak boleh diubah hanya agar data terlihat lebih rapi.
RULE_COLUMNS = [
"rule_id",
"source_finding_id",
"dataset",
"trigger_condition",
"decision",
"cleaning_action",
"reason_evidence",
"implementation_note",
"validation_criteria",
"rule_status",
]
cleaning_rules = pd.DataFrame(columns=RULE_COLUMNS)
display(cleaning_rules)
|
rule_id |
source_finding_id |
dataset |
trigger_condition |
decision |
cleaning_action |
reason_evidence |
implementation_note |
validation_criteria |
rule_status |
Rulebook Template
```python
new_rule = {
"rule_id": "CR-01",
"source_finding_id": "F-01",
"dataset": "...",
"trigger_condition": "...",
"decision": "FIX / STANDARDIZE",
"cleaning_action": "...",
"reason_evidence": "...",
"implementation_note": "...",
"validation_criteria": "...",
"rule_status": "Approved",
}
cleaning_rules.loc[len(cleaning_rules)] = new_rule
```
new_rules = [
{
"rule_id": "CR-01",
"source_finding_id": "F-01",
"dataset": "Customers",
"trigger_condition": "customer_id duplikat dan seluruh atribut pada baris identik; C0031 muncul pada row 30 dan 169.",
"decision": "FIX",
"cleaning_action": "Hapus satu baris dari pasangan exact duplicate.",
"reason_evidence": "Dua baris memiliki customer_id dan seluruh atribut identik; jumlah baris 170 sedangkan jumlah unique key 169.",
"implementation_note": "Pertahankan satu record C0031 dan dokumentasikan baris yang dihapus.",
"validation_criteria": "Jumlah baris menjadi 169; customer_id unik; tidak ada exact duplicate yang tersisa.",
"rule_status": "Draft"
},
{
"rule_id": "CR-02",
"source_finding_id": "F-02",
"dataset": "Customers",
"trigger_condition": "customer_segment memiliki variasi Regular, REGULAR, atau REGULAR karena kapitalisasi dan spasi.",
"decision": "STANDARDIZE",
"cleaning_action": "Hapus spasi di awal/akhir dan ubah label menjadi Title Case.",
"reason_evidence": "Label menunjukkan kategori yang sama, tetapi penulisannya tidak konsisten.",
"implementation_note": "Terapkan str.strip().str.title() pada kolom customer_segment.",
"validation_criteria": "Hanya satu label standar Regular untuk kategori tersebut; jumlah record tetap.",
"rule_status": "Draft"
},
{
"rule_id": "CR-03",
"source_finding_id": "F-03",
"dataset": "Orders",
"trigger_condition": "order_status memiliki variasi completed dan COMPLETED.",
"decision": "STANDARDIZE",
"cleaning_action": "Hapus spasi ekstra dan seragamkan kapitalisasi menjadi Completed.",
"reason_evidence": "Perbedaan kapitalisasi menyebabkan kategori yang sama terhitung sebagai label berbeda.",
"implementation_note": "Terapkan trimming dan Title Case pada order_status; pastikan label lain tetap valid.",
"validation_criteria": "Tidak ada variasi kapitalisasi untuk status yang sama; jumlah record tetap.",
"rule_status": "Draft"
},
{
"rule_id": "CR-04",
"source_finding_id": "F-04",
"dataset": "Payments",
"trigger_condition": "payment_method memiliki variasi e_wallet, E WALLET, dan E WALLET .",
"decision": "STANDARDIZE",
"cleaning_action": "Hapus spasi ekstra, samakan kapitalisasi, dan petakan E WALLET menjadi E_WALLET.",
"reason_evidence": "Perbedaan spasi, kapitalisasi, dan pemisah menyebabkan inkonsistensi kategori.",
"implementation_note": "Gunakan pemetaan label eksplisit ke e_wallet atau label kanonis yang disepakati.",
"validation_criteria": "Seluruh variasi yang telah dikonfirmasi ekuivalen menggunakan satu label kanonis; jumlah record tetap.",
"rule_status": "Draft"
},
{
"rule_id": "CR-05",
"source_finding_id": "F-05",
"dataset": "Restaurants",
"trigger_condition": "price_tier memiliki variasi Mid dan MID.",
"decision": "STANDARDIZE",
"cleaning_action": "Hapus spasi ekstra dan ubah menjadi Mid.",
"reason_evidence": "Variasi kapitalisasi mengakibatkan kategori harga terpecah saat agregasi.",
"implementation_note": "Terapkan trimming dan Title Case pada price_tier.",
"validation_criteria": "Label Mid konsisten dan jumlah record tetap.",
"rule_status": "Draft"
},
{
"rule_id": "CR-06",
"source_finding_id": "F-06",
"dataset": "Restaurants",
"trigger_condition": "price_tier memiliki variasi Budget dan BUDGET.",
"decision": "STANDARDIZE",
"cleaning_action": "Hapus spasi ekstra dan ubah menjadi Budget.",
"reason_evidence": "Variasi kapitalisasi mengakibatkan kategori harga terpecah saat agregasi.",
"implementation_note": "Terapkan trimming dan Title Case pada price_tier.",
"validation_criteria": "Label Budget konsisten dan jumlah record tetap.",
"rule_status": "Draft"
},
{
"rule_id": "CR-07",
"source_finding_id": "F-07",
"dataset": "Order Items",
"trigger_condition": "item_category memiliki variasi Snack dan SNACK .",
"decision": "STANDARDIZE",
"cleaning_action": "Hapus spasi ekstra dan ubah menjadi Snack.",
"reason_evidence": "Record OI00091 dan OI00331 memiliki label kategori yang berbeda secara format, tetapi bermakna sama.",
"implementation_note": "Terapkan trimming dan Title Case pada item_category.",
"validation_criteria": "Tidak ada variasi kapitalisasi/spasi untuk kategori Snack; jumlah record tetap.",
"rule_status": "Draft"
},
{
"rule_id": "CR-08",
"source_finding_id": "F-09",
"dataset": "Orders",
"trigger_condition": "Rekonsiliasi transaksi menunjukkan mismatch pada O00049, O00182, dan O00201; O00201 terbukti mengalami kesalahan pencatatan.",
"decision": "FIX",
"cleaning_action": "Koreksi subtotal_amount menjadi Rp125.000 dan total_amount menjadi Rp138.000 untuk O00201.",
"reason_evidence": "Total item Rp125.000 ditambah ongkir Rp18.000 dikurangi diskon Rp5.000 menghasilkan Rp138.000, sesuai pembayaran berhasil P00226.",
"implementation_note": "Terapkan koreksi hanya pada record yang didukung bukti rekonsiliasi; periksa dua order lainnya secara terpisah.",
"validation_criteria": "Untuk O00201, subtotal sama dengan jumlah item dan total sama dengan perhitungan subtotal + ongkir - diskon serta pembayaran berhasil.",
"rule_status": "Draft"
},
{
"rule_id": "CR-09",
"source_finding_id": "F-10",
"dataset": "Order Items",
"trigger_condition": "line_amount tidak sesuai formula quantity × unit_price - line_discount, khususnya OI00131 pada O00049.",
"decision": "FIX",
"cleaning_action": "Koreksi line_amount OI00131 dari Rp34.000 menjadi Rp27.000.",
"reason_evidence": "Perhitungan ulang menghasilkan Rp27.000; koreksi membuat total item sesuai subtotal Rp187.000.",
"implementation_note": "Hitung ulang formula dan bandingkan dengan subtotal serta pembayaran; pertahankan nilai asli dalam audit log.",
"validation_criteria": "Formula cocok untuk record yang diperbaiki; total item O00049 menjadi Rp187.000 dan total pesanan Rp190.000 sesuai pembayaran berhasil.",
"rule_status": "Draft"
},
{
"rule_id": "CR-10",
"source_finding_id": "F-11",
"dataset": "Order Items",
"trigger_condition": "line_amount pada OI00511, order O00182, tidak sesuai hasil perhitungan.",
"decision": "FIX",
"cleaning_action": "Koreksi line_amount dari Rp31.000 menjadi Rp36.000.",
"reason_evidence": "Hasil formula menunjukkan selisih Rp5.000; total item berubah dari Rp217.000 menjadi Rp222.000, sesuai subtotal.",
"implementation_note": "Hitung ulang formula berdasarkan kuantitas, harga satuan, dan diskon; simpan nilai sebelum koreksi.",
"validation_criteria": "line_amount sesuai formula; jumlah item O00182 menjadi Rp222.000; total pesanan Rp219.000 sesuai pembayaran berhasil.",
"rule_status": "Draft"
},
{
"rule_id": "CR-11",
"source_finding_id": "F-12",
"dataset": "Order Items",
"trigger_condition": "quantity mengandung teks satuan seperti 2 pcs pada OI00071 dan 1 pcs pada OI00215.",
"decision": "STANDARDIZE",
"cleaning_action": "Hilangkan teks pcs, bersihkan spasi, lalu konversi quantity menjadi integer.",
"reason_evidence": "Nilai kuantitas dasar valid, tetapi format teks menghambat operasi numerik.",
"implementation_note": "Ekstrak angka setelah validasi format; jangan mengubah nilai yang tidak dapat dikonversi tanpa investigasi.",
"validation_criteria": "OI00071 menjadi integer 2, OI00215 menjadi integer 1; tidak ada nilai nonnumerik yang belum ditangani.",
"rule_status": "Draft"
}
]
# Tambahkan seluruh rule ke cleaning_rules
cleaning_rules = pd.concat(
[cleaning_rules, pd.DataFrame(new_rules)],
ignore_index=True
)
# Periksa hasil
print(cleaning_rules.tail(11))
print("Total rules:", len(cleaning_rules))
rule_id source_finding_id dataset trigger_condition decision cleaning_action \
0 CR-01 F-01 Customers customer_id duplikat dan seluruh atribut pada ... FIX Hapus satu baris dari pasangan exact duplicate.
1 CR-02 F-02 Customers customer_segment memiliki variasi Regular, REG... STANDARDIZE Hapus spasi di awal/akhir dan ubah label menja...
2 CR-03 F-03 Orders order_status memiliki variasi completed dan CO... STANDARDIZE Hapus spasi ekstra dan seragamkan kapitalisasi...
3 CR-04 F-04 Payments payment_method memiliki variasi e_wallet, E WA... STANDARDIZE Hapus spasi ekstra, samakan kapitalisasi, dan ...
4 CR-05 F-05 Restaurants price_tier memiliki variasi Mid dan MID. STANDARDIZE Hapus spasi ekstra dan ubah menjadi Mid.
5 CR-06 F-06 Restaurants price_tier memiliki variasi Budget dan BUDGET. STANDARDIZE Hapus spasi ekstra dan ubah menjadi Budget.
6 CR-07 F-07 Order Items item_category memiliki variasi Snack dan SNACK . STANDARDIZE Hapus spasi ekstra dan ubah menjadi Snack.
7 CR-08 F-09 Orders Rekonsiliasi transaksi menunjukkan mismatch pa... FIX Koreksi subtotal_amount menjadi Rp125.000 dan ...
8 CR-09 F-10 Order Items line_amount tidak sesuai formula quantity × un... FIX Koreksi line_amount OI00131 dari Rp34.000 menj...
9 CR-10 F-11 Order Items line_amount pada OI00511, order O00182, tidak ... FIX Koreksi line_amount dari Rp31.000 menjadi Rp36...
10 CR-11 F-12 Order Items quantity mengandung teks satuan seperti 2 pcs ... STANDARDIZE Hilangkan teks pcs, bersihkan spasi, lalu konv...
reason_evidence implementation_note validation_criteria rule_status
0 Dua baris memiliki customer_id dan seluruh atr... Pertahankan satu record C0031 dan dokumentasik... Jumlah baris menjadi 169; customer_id unik; ti... Draft
1 Label menunjukkan kategori yang sama, tetapi p... Terapkan str.strip().str.title() pada kolom cu... Hanya satu label standar Regular untuk kategor... Draft
2 Perbedaan kapitalisasi menyebabkan kategori ya... Terapkan trimming dan Title Case pada order_st... Tidak ada variasi kapitalisasi untuk status ya... Draft
3 Perbedaan spasi, kapitalisasi, dan pemisah men... Gunakan pemetaan label eksplisit ke e_wallet a... Seluruh variasi yang telah dikonfirmasi ekuiva... Draft
4 Variasi kapitalisasi mengakibatkan kategori ha... Terapkan trimming dan Title Case pada price_tier. Label Mid konsisten dan jumlah record tetap. Draft
5 Variasi kapitalisasi mengakibatkan kategori ha... Terapkan trimming dan Title Case pada price_tier. Label Budget konsisten dan jumlah record tetap. Draft
6 Record OI00091 dan OI00331 memiliki label kate... Terapkan trimming dan Title Case pada item_cat... Tidak ada variasi kapitalisasi/spasi untuk kat... Draft
7 Total item Rp125.000 ditambah ongkir Rp18.000 ... Terapkan koreksi hanya pada record yang diduku... Untuk O00201, subtotal sama dengan jumlah item... Draft
8 Perhitungan ulang menghasilkan Rp27.000; korek... Hitung ulang formula dan bandingkan dengan sub... Formula cocok untuk record yang diperbaiki; to... Draft
9 Hasil formula menunjukkan selisih Rp5.000; tot... Hitung ulang formula berdasarkan kuantitas, ha... line_amount sesuai formula; jumlah item O00182... Draft
10 Nilai kuantitas dasar valid, tetapi format tek... Ekstrak angka setelah validasi format; jangan ... OI00071 menjadi integer 2, OI00215 menjadi int... Draft
Total rules: 11
11. Create Working Copies
# ============================================================
# RESET — WORKING COPIES
# ============================================================
if DATA_READY:
customers_clean = customers_raw.copy(deep=True)
orders_clean = orders_raw.copy(deep=True)
order_items_clean = order_items_raw.copy(deep=True)
payments_clean = payments_raw.copy(deep=True)
restaurants_clean = restaurants_raw.copy(deep=True)
print("Working copies reset successfully.")
else:
print("ERROR: Raw datasets are not ready.")
Working copies reset successfully.
12. Implement Cleaning Rules — EDIT + RUN
**Edit hanya cell Cleaning Code di bawah ini.**
Aturan:
- implementasikan hanya rule yang sudah kamu setujui di Working Paper;
- gunakan `customers_clean`, `orders_clean`, `order_items_clean`, `payments_clean`, atau `restaurants_clean`;
- jangan pernah overwrite object dengan suffix `_raw`;
- beri komentar `CR-xx` untuk setiap rule;
- setelah implementasi, catat before/after pada Cleaning Log.
Starter sengaja **tidak memberikan affected record ID atau jawaban** agar evidence tetap berasal dari variant kamu.
# ============================================================
# EDIT AREA 2 — APPROVED CLEANING CODE
# ============================================================
# Tambahkan code per Rule ID di bawah ini.
#
# Contoh struktur komentar:
# # CR-01 — [judul rule]
# # Source Finding: F-xx
# # Evidence basis: ...
# # Validation criteria: ...
#
# Gunakan hanya dataframe *_clean.
# JANGAN ubah dataframe *_raw.
# TODO: implement approved rules here.
pass
# ============================================================
# EDIT AREA 2 — APPROVED CLEANING CODE
# ============================================================
# Gunakan hanya dataframe *_clean.
# JANGAN ubah dataframe *_raw.
import pandas as pd
# ------------------------------------------------------------
# CR-01 — Remove Exact Duplicate Customer
# Source Finding: F-01
# Evidence basis: C0031 muncul identik pada row 30 dan 169.
# Validation criteria: 169 rows, unique customer_id.
# ------------------------------------------------------------
customers_clean = customers_clean.drop_duplicates(
subset=customers_clean.columns.tolist(),
keep="first"
).copy()
assert customers_clean["customer_id"].is_unique
# ============================================================
# VALIDATION — CR-01
# ============================================================
# 1. Cek jumlah baris setelah cleaning
print("Jumlah baris customers_clean:", len(customers_clean))
# 2. Cek jumlah unique customer_id
print("Jumlah unique customer_id:", customers_clean["customer_id"].nunique())
# 3. Cek apakah masih ada customer_id duplikat
print(
"Jumlah baris dengan customer_id duplikat:",
customers_clean["customer_id"].duplicated().sum()
)
# 4. Cek apakah masih ada exact duplicate
print(
"Jumlah exact duplicate:",
customers_clean.duplicated().sum()
)
# 5. Tampilkan semua customer_id yang masih duplikat, jika ada
duplicate_customers = customers_clean[
customers_clean["customer_id"].duplicated(keep=False)
].sort_values("customer_id")
if duplicate_customers.empty:
print("PASS: Tidak ada customer_id duplikat.")
else:
print("FAIL: Masih ditemukan customer_id duplikat.")
display(duplicate_customers)
# 6. Validasi hasil berdasarkan kriteria CR-01
if (
len(customers_clean) == 169
and customers_clean["customer_id"].is_unique
and not customers_clean.duplicated().any()
):
print("CR-01 VALIDATION: PASS")
else:
print("CR-01 VALIDATION: FAIL — Periksa kembali hasil cleaning.")
Jumlah baris customers_clean: 169
Jumlah unique customer_id: 169
Jumlah baris dengan customer_id duplikat: 0
Jumlah exact duplicate: 0
PASS: Tidak ada customer_id duplikat.
CR-01 VALIDATION: PASS
# ------------------------------------------------------------
# CR-02 — Standardize Customer Segment
# Source Finding: F-02
# Evidence basis: Variasi Regular, REGULAR, dan label dengan spasi.
# Validation criteria: Semua variasi tersebut menjadi Regular.
# ------------------------------------------------------------
customers_clean["customer_segment"] = (
customers_clean["customer_segment"]
.apply(
lambda x: "Regular"
if isinstance(x, str) and x.strip().casefold() == "regular"
else x
)
)
# ============================================================
# VALIDATION — CR-02
# ============================================================
# Validasi hasil
print("Kategori customer_segment:")
print(sorted(customers_clean["customer_segment"].dropna().unique()))
print("\nJumlah setiap kategori:")
print(customers_clean["customer_segment"].value_counts(dropna=False))
# Pastikan variasi Regular sudah tidak tersisa
remaining_variants = customers_clean["customer_segment"].dropna().apply(
lambda x: x.strip().casefold() == "regular"
and x != "Regular"
if isinstance(x, str)
else False
)
assert not remaining_variants.any(), "Masih ada variasi label Regular!"
print("\nCR-02 VALIDATION: PASS")
Kategori customer_segment:
['Loyal', 'New', 'Regular']
Jumlah setiap kategori:
customer_segment
Regular 75
New 55
Loyal 39
Name: count, dtype: int64
CR-02 VALIDATION: PASS
# ------------------------------------------------------------
# CR-03 — Standardize Order Status
# Source Finding: F-03
# Evidence basis: completed and COMPLETED represent the same status.
# Validation criteria: All equivalent labels become completed.
# ------------------------------------------------------------
orders_clean["order_status"] = orders_clean["order_status"].apply(
lambda x: "completed"
if isinstance(x, str) and x.strip().casefold() == "completed"
else x
)
# ============================================================
# VALIDATION — CR-03
# ============================================================
# Validasi hasil
print("Kategori order_status:")
print(sorted(orders_clean["order_status"].dropna().unique()))
print("\nJumlah setiap status:")
print(orders_clean["order_status"].value_counts(dropna=False))
# Pastikan variasi completed sudah menjadi lowercase
remaining_variants = orders_clean["order_status"].dropna().apply(
lambda x: x.strip().casefold() == "completed" and x != "completed"
if isinstance(x, str)
else False
)
assert not remaining_variants.any(), "Masih ada variasi completed!"
print("\nCR-03 VALIDATION: PASS")
Kategori order_status:
['cancelled', 'completed']
Jumlah setiap status:
order_status
completed 302
cancelled 30
Name: count, dtype: int64
CR-03 VALIDATION: PASS
# ------------------------------------------------------------
# CR-04 — Standardize Payment Method
# Source Finding: F-04
# Evidence basis: e_wallet, E WALLET, and E WALLET .
# Validation criteria: Equivalent labels use canonical e_wallet.
# ------------------------------------------------------------
payment_method_map = {
"e_wallet": "e_wallet",
"e wallet": "e_wallet",
}
payments_clean["payment_method"] = (
payments_clean["payment_method"]
.str.strip()
.str.lower()
.replace(payment_method_map)
)
# ============================================================
# VALIDATION — CR-04
# ============================================================
# Validasi hasil
print("Kategori payment_method:")
print(sorted(payments_clean["payment_method"].dropna().unique()))
print("\nJumlah setiap metode pembayaran:")
print(payments_clean["payment_method"].value_counts(dropna=False))
# Pastikan variasi e_wallet sudah seragam
e_wallet_values = payments_clean["payment_method"].dropna()
remaining_variants = e_wallet_values[
e_wallet_values.str.strip().str.lower().isin(
["e_wallet", "e wallet"]
)
]
assert (remaining_variants == "e_wallet").all(), \
"Masih ada variasi payment_method yang belum distandardisasi!"
print("\nCR-04 VALIDATION: PASS")
Kategori payment_method:
['bank_transfer', 'cash', 'credit_card', 'e_wallet']
Jumlah setiap metode pembayaran:
payment_method
credit_card 93
e_wallet 88
bank_transfer 88
cash 88
Name: count, dtype: int64
CR-04 VALIDATION: PASS
# ------------------------------------------------------------
# CR-05 — Standardize Restaurant Price Tier: Mid
# Source Finding: F-05
# Evidence basis: Mid (2 data) and MID (1 data) are equivalent.
# Validation criteria: All equivalent labels become Mid.
# ------------------------------------------------------------
restaurants_clean["price_tier"] = restaurants_clean["price_tier"].apply(
lambda x: "Mid"
if isinstance(x, str) and x.strip().casefold() == "mid"
else x
)
# ============================================================
# VALIDATION — CR-05
# ============================================================
# Validasi hasil
print("Kategori price_tier:")
print(sorted(restaurants_clean["price_tier"].dropna().unique()))
print("\nJumlah setiap kategori:")
print(restaurants_clean["price_tier"].value_counts(dropna=False))
# Pastikan tidak ada variasi Mid yang tersisa
mid_values = restaurants_clean["price_tier"].dropna()
remaining_variants = mid_values[
mid_values.str.strip().str.casefold() == "mid"
]
assert (remaining_variants == "Mid").all(), \
"Masih ada variasi label Mid!"
print("\nCR-05 VALIDATION: PASS")
Kategori price_tier:
['BUDGET ', 'Budget', 'Mid', 'Premium']
Jumlah setiap kategori:
price_tier
Premium 10
Budget 10
Mid 3
BUDGET 1
Name: count, dtype: int64
CR-05 VALIDATION: PASS
# ------------------------------------------------------------
# CR-06 — Standardize Restaurant Price Tier: Budget
# Source Finding: F-06
# Evidence basis: Budget and BUDGET are equivalent labels.
# Validation criteria: Standard label is Budget.
# ------------------------------------------------------------
# Covered by CR-05 using the same column transformation.
restaurants_clean["price_tier"] = restaurants_clean["price_tier"].apply(
lambda x: "Budget"
if isinstance(x, str) and x.strip().casefold() == "budget"
else x
)
# ============================================================
# VALIDATION — CR-06
# ============================================================
# Validasi hasil
print("Kategori price_tier:")
print(sorted(restaurants_clean["price_tier"].dropna().unique()))
print("\nJumlah setiap kategori:")
print(restaurants_clean["price_tier"].value_counts(dropna=False))
# Pastikan tidak ada variasi Budget yang tersisa
budget_values = restaurants_clean["price_tier"].dropna()
remaining_variants = budget_values[
budget_values.str.strip().str.casefold() == "budget"
]
assert (remaining_variants == "Budget").all(), \
"Masih ada variasi label Budget!"
print("\nCR-06 VALIDATION: PASS")
Kategori price_tier:
['Budget', 'Mid', 'Premium']
Jumlah setiap kategori:
price_tier
Budget 11
Premium 10
Mid 3
Name: count, dtype: int64
CR-06 VALIDATION: PASS
# ------------------------------------------------------------
# CR-07 — Standardize Item Category
# Source Finding: F-07
# Evidence basis: Snack and SNACK have inconsistent formatting.
# Validation criteria: Standard label is Snack.
# ------------------------------------------------------------
# ============================================================
# CR-07 — Standardize Item Category
# ============================================================
order_items_clean["item_category"] = (
order_items_clean["item_category"].apply(
lambda x: "Snack"
if isinstance(x, str) and x.strip().casefold() == "snack"
else x
)
)
# ============================================================
# VALIDATION — CR-07
# ============================================================
print("Kategori item_category:")
print(sorted(order_items_clean["item_category"].dropna().unique()))
print("\nJumlah setiap kategori:")
print(order_items_clean["item_category"].value_counts(dropna=False))
snack_values = order_items_clean["item_category"].dropna()
remaining_variants = snack_values[
snack_values.str.strip().str.casefold() == "snack"
]
assert (remaining_variants == "Snack").all(), \
"Masih ada variasi label Snack!"
print("\nCR-07 VALIDATION: PASS")
Kategori item_category:
['Dessert', 'Drink', 'Main', 'Snack']
Jumlah setiap kategori:
item_category
Main 396
Drink 277
Snack 156
Dessert 115
Name: count, dtype: int64
CR-07 VALIDATION: PASS
# ============================================================
# VALIDATION — CR-07
# ============================================================
# Validasi hasil
print("Kategori item_category:")
print(sorted(order_items_clean["item_category"].dropna().unique()))
print("\nJumlah setiap kategori:")
print(order_items_clean["item_category"].value_counts(dropna=False))
# Pastikan tidak ada variasi Snack yang tersisa
snack_values = order_items_clean["item_category"].dropna()
remaining_variants = snack_values[
snack_values.str.strip().str.casefold() == "snack"
]
assert (remaining_variants == "Snack").all(), \
"Masih ada variasi label Snack!"
print("\nCR-07 VALIDATION: PASS")
Kategori item_category:
['Dessert', 'Drink', 'Main', 'Snack']
Jumlah setiap kategori:
item_category
Main 396
Drink 277
Snack 156
Dessert 115
Name: count, dtype: int64
CR-07 VALIDATION: PASS
# ------------------------------------------------------------
# CR-08 — Correct Order O00201
# Source Finding: F-09
# Evidence basis: Reconciliation with successful payment P00226.
# Validation criteria: Subtotal 125000 and total 138000.
# ------------------------------------------------------------
orders_clean.loc[
orders_clean["order_id"].eq("O00201"),
"subtotal_amount"
] = 125000
orders_clean.loc[
orders_clean["order_id"].eq("O00201"),
"total_amount"
] = 138000
# ============================================================
# VALIDATION — CR-08
# ============================================================
# 1. Ambil data order O00201
order_check = orders_clean[
orders_clean["order_id"] == "O00201"
]
# 2. Tampilkan hasil koreksi
print("Hasil koreksi O00201:")
display(order_check)
# 3. Periksa apakah order ditemukan tepat satu kali
assert len(order_check) == 1, \
"FAIL: O00201 tidak ditemukan atau memiliki lebih dari satu record."
# 4. Validasi subtotal dan total
assert order_check["subtotal_amount"].iloc[0] == 125000, \
"FAIL: subtotal_amount belum sesuai."
assert order_check["total_amount"].iloc[0] == 138000, \
"FAIL: total_amount belum sesuai."
print("Subtotal O00201:", order_check["subtotal_amount"].iloc[0])
print("Total O00201:", order_check["total_amount"].iloc[0])
print("\nCR-09 VALIDATION: PASS")
Hasil koreksi O00201:
|
order_id |
customer_id |
restaurant_id |
order_timestamp |
order_status |
promo_code |
subtotal_amount |
delivery_fee |
discount_amount |
total_amount |
| 200 |
O00201 |
C0088 |
R0012 |
2026-03-11 02:16:00 |
completed |
NaN |
125000 |
18000 |
5000 |
138000 |
Subtotal O00201: 125000
Total O00201: 138000
CR-09 VALIDATION: PASS
# ------------------------------------------------------------
# CR-09 — Correct Line Amount for OI00131
# Source Finding: F-10
# Evidence basis: Recalculation gives 27000.
# Validation criteria: Corrected line amount is 27000.
# ------------------------------------------------------------
order_items_clean.loc[
order_items_clean["order_item_id"].eq("OI00131"),
"line_amount"
] = 27000
# ============================================================
# VALIDATION — CR-09
# ============================================================
import pandas as pd
# 1. Periksa tipe data kolom numerik
numeric_cols = [
"quantity",
"unit_price",
"line_discount",
"line_amount"
]
print("Tipe data sebelum konversi:")
print(order_items_clean[numeric_cols].dtypes)
# 2. Konversi kolom menjadi numerik
for col in numeric_cols:
order_items_clean[col] = pd.to_numeric(
order_items_clean[col],
errors="coerce"
)
# 3. Periksa apakah ada nilai yang gagal dikonversi
invalid_values = order_items_clean[numeric_cols].isna().sum()
print("\nJumlah nilai NaN setelah konversi:")
print(invalid_values)
# 4. Ambil record OI00131
item_check = order_items_clean[
order_items_clean["order_item_id"] == "OI00131"
]
# 5. Pastikan record ditemukan tepat satu kali
assert len(item_check) == 1, (
"FAIL: OI00131 tidak ditemukan atau memiliki lebih "
"dari satu record."
)
# 6. Tampilkan hasil koreksi
display(item_check)
# 7. Validasi line_amount
item = item_check.iloc[0]
assert pd.notna(item["line_amount"]), (
"FAIL: line_amount OI00131 bukan nilai numerik yang valid."
)
assert item["line_amount"] == 27000, (
"FAIL: line_amount OI00131 belum menjadi Rp27.000."
)
# 8. Hitung ulang berdasarkan formula
calculated_amount = (
item["quantity"] * item["unit_price"]
- item["line_discount"]
)
print("\nLine amount pada dataset:", item["line_amount"])
print("Hasil perhitungan formula:", calculated_amount)
assert pd.notna(calculated_amount), (
"FAIL: Hasil perhitungan mengandung NaN."
)
assert calculated_amount == item["line_amount"], (
"FAIL: line_amount tidak sesuai formula."
)
# 9. Validasi subtotal item order O00049
order_items_o00049 = order_items_clean[
order_items_clean["order_id"] == "O00049"
]
assert not order_items_o00049.empty, (
"FAIL: Tidak ditemukan item untuk order O00049."
)
assert order_items_o00049["line_amount"].notna().all(), (
"FAIL: Terdapat line_amount yang tidak valid pada O00049."
)
total_items_o00049 = order_items_o00049["line_amount"].sum()
print("\nTotal item O00049:", total_items_o00049)
assert total_items_o00049 == 187000, (
"FAIL: Total item O00049 belum menjadi Rp187.000."
)
print("\nCR-09 VALIDATION: PASS")
Tipe data sebelum konversi:
quantity str
unit_price int64
line_discount int64
line_amount int64
dtype: object
Jumlah nilai NaN setelah konversi:
quantity 2
unit_price 0
line_discount 0
line_amount 0
dtype: int64
|
order_item_id |
order_id |
line_number |
item_name |
item_category |
quantity |
unit_price |
line_discount |
line_amount |
| 130 |
OI00131 |
O00049 |
3 |
Iced Tea |
Drink |
3.0 |
9000 |
0 |
27000 |
Line amount pada dataset: 27000
Hasil perhitungan formula: 27000.0
Total item O00049: 187000
CR-09 VALIDATION: PASS
# ------------------------------------------------------------
# CR-10 — Correct Line Amount for OI00511
# Source Finding: F-11
# Evidence basis: Recalculation gives 36000.
# Validation criteria: Corrected line amount is 36000.
# ------------------------------------------------------------
order_items_clean.loc[
order_items_clean["order_item_id"].eq("OI00511"),
"line_amount"
] = 36000
# ============================================================
# VALIDATION — CR-10
# ============================================================
# 1. Ambil record OI00511
item_check = order_items_clean[
order_items_clean["order_item_id"] == "OI00511"
]
# 2. Pastikan record ditemukan tepat satu kali
assert len(item_check) == 1, \
"FAIL: OI00511 tidak ditemukan atau memiliki lebih dari satu record."
# 3. Tampilkan hasil koreksi
display(item_check)
# 4. Validasi line_amount = Rp36.000
item = item_check.iloc[0]
assert item["line_amount"] == 36000, \
"FAIL: line_amount belum menjadi Rp36.000."
# 5. Hitung ulang formula
calculated_amount = (
item["quantity"] * item["unit_price"]
- item["line_discount"]
)
print("Line amount pada dataset:", item["line_amount"])
print("Hasil perhitungan formula:", calculated_amount)
assert calculated_amount == item["line_amount"], \
"FAIL: line_amount tidak sesuai formula."
# 6. Validasi total item order O00182
order_items_o00182 = order_items_clean[
order_items_clean["order_id"] == "O00182"
]
total_items_o00182 = order_items_o00182["line_amount"].sum()
print("\nTotal item O00182:", total_items_o00182)
assert total_items_o00182 == 222000, \
"FAIL: Total item O00182 belum menjadi Rp222.000."
print("\nCR-11 VALIDATION: PASS")
|
order_item_id |
order_id |
line_number |
item_name |
item_category |
quantity |
unit_price |
line_discount |
line_amount |
| 510 |
OI00511 |
O00182 |
4 |
Coffee |
Drink |
2.0 |
18000 |
0 |
36000 |
Line amount pada dataset: 36000
Hasil perhitungan formula: 36000.0
Total item O00182: 222000
CR-11 VALIDATION: PASS
# ------------------------------------------------------------
# CR-11 — Convert Quantity to Integer
# Source Finding: F-12
# Evidence basis: Quantity contains text such as "2 pcs".
# Validation criteria: OI00071 = 2 and OI00215 = 1.
# ------------------------------------------------------------
import pandas as pd
# Ambil ulang working copy dari raw
order_items_clean = order_items_raw.copy(deep=True)
# Bersihkan format quantity dari raw
quantity_text = (
order_items_raw["quantity"]
.astype("string")
.str.strip()
)
# Ekstrak angka dari format seperti "2 pcs" atau "1"
quantity_extracted = quantity_text.str.extract(
r"^(\d+)\s*(?:pcs)?$",
flags=0,
expand=False
)
# Periksa nilai yang tidak sesuai format sebelum konversi
invalid_mask = quantity_text.notna() & quantity_extracted.isna()
if invalid_mask.any():
print("Ditemukan format quantity yang belum tertangani:")
display(
order_items_raw.loc[
invalid_mask,
["order_item_id", "quantity"]
]
)
raise ValueError("Cleaning dihentikan: periksa format quantity.")
# Konversi menjadi nullable integer
order_items_clean["quantity"] = pd.to_numeric(
quantity_extracted,
errors="raise"
).astype("Int64")
# Validasi hasil CR-11
check = order_items_clean.loc[
order_items_clean["order_item_id"].isin(
["OI00071", "OI00215"]
),
["order_item_id", "quantity"]
]
display(check)
results = check.set_index("order_item_id")["quantity"]
assert len(check) == 2, "FAIL: Record tidak ditemukan tepat dua."
assert results.loc["OI00071"] == 2, (
"FAIL: OI00071 seharusnya memiliki quantity 2."
)
assert results.loc["OI00215"] == 1, (
"FAIL: OI00215 seharusnya memiliki quantity 1."
)
assert str(order_items_clean["quantity"].dtype) == "Int64"
print("CR-11 VALIDATION: PASS")
|
order_item_id |
quantity |
| 70 |
OI00071 |
2 |
| 214 |
OI00215 |
1 |
CR-11 VALIDATION: PASS
# ============================================================
# INVESTIGATION — RAW VS CLEAN (CR-11 / CR-12)
# ============================================================
import pandas as pd
target_ids = ["OI00071", "OI00215"]
# Ambil data dari raw dan clean
raw_check = order_items_raw.loc[
order_items_raw["order_item_id"].isin(target_ids),
["order_item_id", "quantity"]
].copy()
clean_check = order_items_clean.loc[
order_items_clean["order_item_id"].isin(target_ids),
["order_item_id", "quantity"]
].copy()
print("=== RAW DATA ===")
display(raw_check)
print("Raw quantity dtype:", order_items_raw["quantity"].dtype)
print("\n=== CLEAN DATA ===")
display(clean_check)
print("Clean quantity dtype:", order_items_clean["quantity"].dtype)
# Bandingkan nilai mentah secara persis
print("\n=== DETAIL NILAI RAW ===")
for _, row in raw_check.iterrows():
print(
row["order_item_id"],
"| value:", repr(row["quantity"]),
"| type:", type(row["quantity"]).__name__
)
print("\n=== DETAIL NILAI CLEAN ===")
for _, row in clean_check.iterrows():
print(
row["order_item_id"],
"| value:", repr(row["quantity"]),
"| type:", type(row["quantity"]).__name__
)
=== RAW DATA ===
|
order_item_id |
quantity |
| 70 |
OI00071 |
2 pcs |
| 214 |
OI00215 |
1 pcs |
Raw quantity dtype: str
=== CLEAN DATA ===
|
order_item_id |
quantity |
| 70 |
OI00071 |
2 |
| 214 |
OI00215 |
1 |
Clean quantity dtype: Int64
=== DETAIL NILAI RAW ===
OI00071 | value: '2 pcs' | type: str
OI00215 | value: '1 pcs' | type: str
=== DETAIL NILAI CLEAN ===
OI00071 | value: 2 | type: int
OI00215 | value: 1 | type: int
# ============================================================
# VALIDATION — CR-11
# ============================================================
import pandas as pd
# 1. Ambil dua record yang menjadi bukti temuan
target_ids = ["OI00071", "OI00215"]
quantity_check = order_items_clean[
order_items_clean["order_item_id"].isin(target_ids)
][["order_item_id", "quantity"]]
display(quantity_check)
# 2. Pastikan setiap ID ditemukan tepat satu kali
for item_id in target_ids:
record_count = (
order_items_clean["order_item_id"] == item_id
).sum()
assert record_count == 1, (
f"FAIL: {item_id} tidak ditemukan atau memiliki "
"lebih dari satu record."
)
# 3. Pastikan tipe data quantity adalah nullable integer
assert str(order_items_clean["quantity"].dtype) == "Int64", (
"FAIL: Tipe data quantity bukan Int64."
)
# 4. Ambil hasil quantity berdasarkan ID
quantity_result = quantity_check.set_index(
"order_item_id"
)["quantity"]
# 5. Pastikan nilai quantity tidak kosong
for item_id in target_ids:
value = quantity_result.loc[item_id]
assert pd.notna(value), (
f"FAIL: Quantity {item_id} bernilai kosong (NA)."
)
# 6. Validasi hasil koreksi quantity
assert quantity_result.loc["OI00071"] == 2, (
"FAIL: Quantity OI00071 seharusnya 2."
)
assert quantity_result.loc["OI00215"] == 1, (
"FAIL: Quantity OI00215 seharusnya 1."
)
# 7. Periksa quantity yang kosong
null_quantity = order_items_clean["quantity"].isna().sum()
print("\nJumlah quantity null:", null_quantity)
print("Tipe data quantity:", order_items_clean["quantity"].dtype)
# 8. Validasi akhir
print("\nCR-11 VALIDATION: PASS")
|
order_item_id |
quantity |
| 70 |
OI00071 |
2 |
| 214 |
OI00215 |
1 |
Jumlah quantity null: 0
Tipe data quantity: Int64
CR-11 VALIDATION: PASS
Cleaning Log Register
CLEANING_LOG_COLUMNS = [
"log_id",
"rule_id",
"dataset",
"affected_records_scope",
"action_performed",
"before_evidence",
"after_evidence",
"rows_records_affected",
"result",
"notes",
]
cleaning_log = pd.DataFrame([
{
"log_id": "CL-01",
"rule_id": "CR-01",
"dataset": "customers_raw",
"affected_records_scope": "customer_id = C0031 (row index 169)",
"action_performed": "Drop 1 baris exact duplicate — simpan row pertama (index 30), hapus row kedua (index 169)",
"before_evidence": "C0031 muncul 2 baris identik: Vina Hidayat, vinahidayat31@example.com, North, Loyal",
"after_evidence": "1 baris unik tersisa: C0031",
"rows_records_affected": "170 → 169 rows",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-02",
"rule_id": "CR-02",
"dataset": "customers_raw",
"affected_records_scope": 'customer_segment — 2 records: "REGULAR "',
"action_performed": 'Standardisasi "REGULAR " → "Regular" via .str.strip().str.title()',
"before_evidence": '"REGULAR " (2 records, 1.18%)',
"after_evidence": '"Regular"',
"rows_records_affected": "2 records",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-03",
"rule_id": "CR-03",
"dataset": "orders_raw",
"affected_records_scope": 'order_status — 2 records: "COMPLETED "',
"action_performed": 'Standardisasi "COMPLETED " → "completed" via .str.strip().str.lower()',
"before_evidence": '"COMPLETED " (2 records, 0.60%)',
"after_evidence": '"completed"',
"rows_records_affected": "2 records",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-04",
"rule_id": "CR-04",
"dataset": "payments_raw",
"affected_records_scope": 'payment_method — 2 records: "E WALLET "',
"action_performed": 'Standardisasi "E WALLET " → "e_wallet" via .str.strip().str.replace(" ", "_").str.lower()',
"before_evidence": '"E WALLET " (2 records, 0.56%)',
"after_evidence": '"e_wallet"',
"rows_records_affected": "2 records",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-05",
"rule_id": "CR-05",
"dataset": "restaurants_raw",
"affected_records_scope": 'price_tier — 1 record: "MID "',
"action_performed": 'Standardisasi "MID " → "Mid" via .str.strip().str.title()',
"before_evidence": '"MID " (1 record, 4.17%)',
"after_evidence": '"Mid"',
"rows_records_affected": "1 record",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-06",
"rule_id": "CR-06",
"dataset": "restaurants_raw",
"affected_records_scope": 'price_tier — 1 record: "BUDGET "',
"action_performed": 'Standardisasi "BUDGET " → "Budget" via .str.strip().str.title()',
"before_evidence": '"BUDGET " (1 record, 4.17%)',
"after_evidence": '"Budget"',
"rows_records_affected": "1 record",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-07",
"rule_id": "CR-07",
"dataset": "order_items_raw",
"affected_records_scope": 'item_category — 2 records: "SNACK "',
"action_performed": 'Standardisasi "SNACK " → "Snack" via .str.strip().str.title()',
"before_evidence": '"SNACK " (2 records, 0.21%)',
"after_evidence": '"Snack"',
"rows_records_affected": "2 records",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-08",
"rule_id": "CR-08",
"dataset": "Orders",
"affected_records_scope": "O00201 — rekonsiliasi mismatch pada O00049, O00182, dan O00201; O00201 terbukti salah catat",
"action_performed": "Koreksi subtotal_amount → Rp125.000 dan total_amount → Rp138.000 untuk O00201",
"before_evidence": "Mismatch terdeteksi via rekonsiliasi; subtotal & total tidak sesuai formula",
"after_evidence": "Total item Rp125.000 + ongkir Rp18.000 − diskon Rp5.000 = Rp138.000; sesuai pembayaran P00226",
"rows_records_affected": "1 record (O00201)",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-09",
"rule_id": "CR-09",
"dataset": "Order Items",
"affected_records_scope": "OI00131, order O00049 (item: Iced Tea)",
"action_performed": "Koreksi line_amount OI00131: Rp34.000 → Rp27.000",
"before_evidence": "line_amount Rp34.000 — tidak sesuai formula qty × unit_price − line_discount",
"after_evidence": "Rp27.000 hasil perhitungan ulang; total item O00049 = Rp187.000, total pesanan Rp190.000 sesuai pembayaran",
"rows_records_affected": "1 record (OI00131)",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-10",
"rule_id": "CR-10",
"dataset": "Order Items",
"affected_records_scope": "OI00511, order O00182 (item: Coffee)",
"action_performed": "Koreksi line_amount OI00511: Rp31.000 → Rp36.000",
"before_evidence": "line_amount Rp31.000 — selisih Rp5.000 dari hasil formula; total item sebelumnya Rp217.000",
"after_evidence": "Rp36.000 sesuai formula; jumlah item O00182 = Rp222.000, total pesanan Rp219.000 sesuai pembayaran",
"rows_records_affected": "1 record (OI00511)",
"result": "Done",
"notes": "-"
},
{
"log_id": "CL-11",
"rule_id": "CR-11",
"dataset": "Order Items",
"affected_records_scope": 'OI00071 ("2 pcs"), OI00215 ("1 pcs") — kolom quantity',
"action_performed": "Hilangkan teks pcs, bersihkan spasi, konversi quantity ke tipe integer",
"before_evidence": 'quantity berformat string: "2 pcs", "1 pcs" — menghambat operasi numerik',
"after_evidence": "OI00071 → 2 (int), OI00215 → 1 (int); tidak ada nilai non-numerik yang belum ditangani",
"rows_records_affected": "2 records",
"result": "Done",
"notes": "-"
}
], columns=CLEANING_LOG_COLUMNS)
display(cleaning_log)
|
log_id |
rule_id |
dataset |
affected_records_scope |
action_performed |
before_evidence |
after_evidence |
rows_records_affected |
result |
notes |
| 0 |
CL-01 |
CR-01 |
customers_raw |
customer_id = C0031 (row index 169) |
Drop 1 baris exact duplicate — simpan row pert... |
C0031 muncul 2 baris identik: Vina Hidayat, vi... |
1 baris unik tersisa: C0031 |
170 → 169 rows |
Done |
- |
| 1 |
CL-02 |
CR-02 |
customers_raw |
customer_segment — 2 records: "REGULAR " |
Standardisasi "REGULAR " → "Regular" via .str.... |
"REGULAR " (2 records, 1.18%) |
"Regular" |
2 records |
Done |
- |
| 2 |
CL-03 |
CR-03 |
orders_raw |
order_status — 2 records: "COMPLETED " |
Standardisasi "COMPLETED " → "completed" via .... |
"COMPLETED " (2 records, 0.60%) |
"completed" |
2 records |
Done |
- |
| 3 |
CL-04 |
CR-04 |
payments_raw |
payment_method — 2 records: "E WALLET " |
Standardisasi "E WALLET " → "e_wallet" via .st... |
"E WALLET " (2 records, 0.56%) |
"e_wallet" |
2 records |
Done |
- |
| 4 |
CL-05 |
CR-05 |
restaurants_raw |
price_tier — 1 record: "MID " |
Standardisasi "MID " → "Mid" via .str.strip().... |
"MID " (1 record, 4.17%) |
"Mid" |
1 record |
Done |
- |
| 5 |
CL-06 |
CR-06 |
restaurants_raw |
price_tier — 1 record: "BUDGET " |
Standardisasi "BUDGET " → "Budget" via .str.st... |
"BUDGET " (1 record, 4.17%) |
"Budget" |
1 record |
Done |
- |
| 6 |
CL-07 |
CR-07 |
order_items_raw |
item_category — 2 records: "SNACK " |
Standardisasi "SNACK " → "Snack" via .str.stri... |
"SNACK " (2 records, 0.21%) |
"Snack" |
2 records |
Done |
- |
| 7 |
CL-08 |
CR-08 |
Orders |
O00201 — rekonsiliasi mismatch pada O00049, O0... |
Koreksi subtotal_amount → Rp125.000 dan total_... |
Mismatch terdeteksi via rekonsiliasi; subtotal... |
Total item Rp125.000 + ongkir Rp18.000 − disko... |
1 record (O00201) |
Done |
- |
| 8 |
CL-09 |
CR-09 |
Order Items |
OI00131, order O00049 (item: Iced Tea) |
Koreksi line_amount OI00131: Rp34.000 → Rp27.000 |
line_amount Rp34.000 — tidak sesuai formula qt... |
Rp27.000 hasil perhitungan ulang; total item O... |
1 record (OI00131) |
Done |
- |
| 9 |
CL-10 |
CR-10 |
Order Items |
OI00511, order O00182 (item: Coffee) |
Koreksi line_amount OI00511: Rp31.000 → Rp36.000 |
line_amount Rp31.000 — selisih Rp5.000 dari ha... |
Rp36.000 sesuai formula; jumlah item O00182 = ... |
1 record (OI00511) |
Done |
- |
| 10 |
CL-11 |
CR-11 |
Order Items |
OI00071 ("2 pcs"), OI00215 ("1 pcs") — kolom q... |
Hilangkan teks pcs, bersihkan spasi, konversi ... |
quantity berformat string: "2 pcs", "1 pcs" — ... |
OI00071 → 2 (int), OI00215 → 1 (int); tidak ad... |
2 records |
Done |
- |
13. Validation Harness — EDIT + RUN
Validation harus membuktikan hasil cleaning memenuhi **15 check pada Working Paper**:
`V-01` sampai `V-15`
Starter menyediakan fungsi `record_check()`. Kamu tetap harus menentukan evidence dan hasil check berdasarkan variant kamu.
> `FINAL_GATE` tidak akan PASS jika check wajib belum lengkap.
validation_results = []
REQUIRED_VALIDATION_IDS = [f"V-{i:02d}" for i in range(1, 16)]
def record_check(check_id, level, dataset_relationship, question,
expected_criteria, actual_result, passed,
related_rule_id="", notes=""):
if check_id not in REQUIRED_VALIDATION_IDS:
print(f"WARNING — {check_id} bukan ID validation V-01 s.d. V-15.")
# Jika cell di-run ulang, replace check dengan ID yang sama agar tidak double.
validation_results[:] = [
row for row in validation_results if row["check_id"] != check_id
]
validation_results.append({
"check_id": check_id,
"validation_level": level,
"dataset_relationship": dataset_relationship,
"validation_question": question,
"expected_criteria": expected_criteria,
"actual_result_metric": actual_result,
"status": "PASS" if bool(passed) else "FAIL",
"related_rule_id": related_rule_id,
"notes": notes,
})
print("VALIDATION HARNESS READY")
print("Required IDs:", ", ".join(REQUIRED_VALIDATION_IDS))
VALIDATION HARNESS READY
Required IDs: V-01, V-02, V-03, V-04, V-05, V-06, V-07, V-08, V-09, V-10, V-11, V-12, V-13, V-14, V-15
13A. Schema & Key Validation — EDIT + RUN
Gunakan area ini untuk mencatat check seperti `V-01` sampai `V-05` sesuai Working Paper.
Kamu boleh memakai kembali helper `key_check()` dari bagian sebelumnya. Jangan mengganti nilai menjadi PASS tanpa evidence.
if DATA_READY:
clean_tables = {
"Customers": customers_clean,
"Orders": orders_clean,
"Order Items": order_items_clean,
"Payments": payments_clean,
"Restaurants": restaurants_clean,
}
print("Clean dataset shapes:")
for name, df in clean_tables.items():
print(name, df.shape)
# ========================================================
# EDIT AREA 3A — V-01 s.d. V-05
# ========================================================
# V-01 — Schema validation
required_columns = {
"Customers": [
"customer_id", "customer_segment"
],
"Orders": [
"order_id", "customer_id", "restaurant_id",
"order_status", "subtotal_amount", "delivery_fee",
"discount_amount", "total_amount"
],
"Order Items": [
"order_item_id", "order_id", "quantity",
"unit_price", "line_discount", "line_amount",
"item_category"
],
"Payments": [
"payment_id", "order_id", "payment_method"
],
"Restaurants": [
"restaurant_id", "price_tier"
],
}
schema_results = {}
for name, df in clean_tables.items():
missing = [
col for col in required_columns[name]
if col not in df.columns
]
schema_results[name] = {
"missing_columns": missing,
"passed": len(missing) == 0
}
schema_passed = all(
result["passed"] for result in schema_results.values()
)
record_check(
"V-01", "Schema", "All clean datasets",
"Apakah seluruh required column masih tersedia?",
"Required columns available",
schema_results,
schema_passed,
notes=(
"Seluruh required columns tersedia."
if schema_passed
else "Terdapat required columns yang belum tersedia."
)
)
# V-02 — Customers key
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,
related_rule_id="CR-01"
)
# V-03 — Orders key
result = key_check(
orders_clean, "order_id", "Orders"
)
passed = bool(result.loc[0, "candidate_key_unique"])
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],
passed
)
# V-04 — Order Items key
result = key_check(
order_items_clean, "order_item_id", "Order Items"
)
passed = bool(result.loc[0, "candidate_key_unique"])
record_check(
"V-04", "Key", "Order Items",
"Apakah order_item_id / candidate business key valid?",
"No invalid duplicate key",
result.to_dict("records")[0],
passed
)
# V-05 — Payments key
result = key_check(
payments_clean, "payment_id", "Payments"
)
passed = bool(result.loc[0, "candidate_key_unique"])
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],
passed,
related_rule_id="CR-04",
notes=(
"Pemeriksaan menggunakan payment_id. "
"Repeated order_id tidak otomatis dianggap duplikat."
)
)
else:
print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Clean dataset shapes:
Customers (169, 7)
Orders (332, 10)
Order Items (944, 9)
Payments (357, 7)
Restaurants (24, 7)
display(
pd.DataFrame(validation_results)[
pd.DataFrame(validation_results)["check_id"].isin(
["V-01", "V-02", "V-03", "V-04", "V-05"]
)
]
)
|
check_id |
validation_level |
dataset_relationship |
validation_question |
expected_criteria |
actual_result_metric |
status |
related_rule_id |
notes |
| 0 |
V-01 |
Schema |
All clean datasets |
Apakah seluruh required column masih tersedia? |
Required columns available |
{'Customers': {'missing_columns': [], 'passed'... |
PASS |
|
Seluruh required columns tersedia. |
| 1 |
V-02 |
Key |
Customers |
Apakah customer_id valid sesuai grain customer? |
No null key; uniqueness sesuai grain |
{'dataset': 'Customers', 'key_cols': 'customer... |
PASS |
CR-01 |
|
| 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 |
{'dataset': 'Order Items', 'key_cols': 'order_... |
PASS |
|
|
| 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-04 |
Pemeriksaan menggunakan payment_id. Repeated o... |
13B. Value, Formula & Cross-Table Validation — EDIT + RUN
Gunakan area ini untuk `V-06` sampai `V-14` sesuai Working Paper.
Manfaatkan helper yang sudah tersedia seperti `category_review()`, `fk_check()`, dan perhitungan formula. **Hitung ulang dari clean data**, bukan hanya melihat output raw.
for col in ["payment_method", "price_tier", "item_category"]:
df = {
"payment_method": payments_clean,
"price_tier": restaurants_clean,
"item_category": order_items_clean,
}[col]
print(f"\n{col}:")
print(df[col].value_counts(dropna=False).to_dict())
print("Ada whitespace:",
bool((df[col].dropna() != df[col].dropna().str.strip()).any()))
payment_method:
{'credit_card': 93, 'e_wallet': 88, 'bank_transfer': 88, 'cash': 88}
Ada whitespace: False
price_tier:
{'Budget': 11, 'Premium': 10, 'Mid': 3}
Ada whitespace: False
item_category:
{'Main': 396, 'Drink': 277, 'Snack': 154, 'Dessert': 115, 'SNACK ': 2}
Ada whitespace: True
display(
order_items_clean[
order_items_clean["order_item_id"].isin(
["OI00131", "OI00511"]
)
][
[
"order_item_id", "order_id", "quantity",
"unit_price", "line_discount", "line_amount"
]
]
)
|
order_item_id |
order_id |
quantity |
unit_price |
line_discount |
line_amount |
| 130 |
OI00131 |
O00049 |
3 |
9000 |
0 |
34000 |
| 510 |
OI00511 |
O00182 |
2 |
18000 |
0 |
31000 |
# CR-07 — Standardisasi item_category
order_items_clean["item_category"] = (
order_items_clean["item_category"].apply(
lambda x: "Snack"
if isinstance(x, str) and x.strip().casefold() == "snack"
else x
)
)
# CR-09 — Koreksi OI00131
mask_131 = order_items_clean["order_item_id"].eq("OI00131")
assert mask_131.sum() == 1, "OI00131 tidak ditemukan tepat satu kali."
order_items_clean.loc[mask_131, "line_amount"] = 27000
# CR-10 — Koreksi OI00511
mask_511 = order_items_clean["order_item_id"].eq("OI00511")
assert mask_511.sum() == 1, "OI00511 tidak ditemukan tepat satu kali."
order_items_clean.loc[mask_511, "line_amount"] = 36000
# Verifikasi langsung
print("Kategori:", order_items_clean["item_category"].value_counts(dropna=False).to_dict())
display(
order_items_clean[
order_items_clean["order_item_id"].isin(["OI00131", "OI00511"])
][
["order_item_id", "order_id", "quantity", "unit_price",
"line_discount", "line_amount"]
]
)
Kategori: {'Main': 396, 'Drink': 277, 'Snack': 156, 'Dessert': 115}
|
order_item_id |
order_id |
quantity |
unit_price |
line_discount |
line_amount |
| 130 |
OI00131 |
O00049 |
3 |
9000 |
0 |
27000 |
| 510 |
OI00511 |
O00182 |
2 |
18000 |
0 |
36000 |
if DATA_READY:
# ========================================================
# EDIT AREA 3B — V-06 s.d. V-14
# ========================================================
# Helper: cek null, whitespace, dan variasi penulisan kategori
def category_quality(df, column):
s = df[column].astype("string")
non_null = s.dropna()
trimmed = non_null.str.strip()
# Menyamakan kapitalisasi dan spasi untuk mendeteksi
# variasi label yang seharusnya konsisten
canonical = trimmed.str.casefold().str.replace(
r"\s+", "_", regex=True
)
variants = (
pd.DataFrame({
"original": non_null,
"canonical": canonical
})
.groupby("canonical")["original"]
.nunique()
)
variant_groups = variants[variants > 1]
null_count = int(s.isna().sum())
blank_count = int((s.notna() & s.str.strip().eq("")).sum())
whitespace_count = int(
(s.notna() & s.ne(s.str.strip())).sum()
)
passed = (
null_count == 0
and blank_count == 0
and whitespace_count == 0
and len(variant_groups) == 0
)
return {
"null_count": null_count,
"blank_count": blank_count,
"leading_trailing_whitespace": whitespace_count,
"inconsistent_label_groups": int(len(variant_groups)),
"inconsistent_label_examples": variant_groups.index.tolist()[:10],
"unique_values": sorted(non_null.unique().tolist()),
"passed": passed
}
# --------------------------------------------------------
# V-06 — Customers category
# --------------------------------------------------------
result = category_quality(customers_clean, "customer_segment")
record_check(
"V-06", "Category", "Customers",
"Apakah customer_segment konsisten dan tidak kosong?",
"No null/blank values, whitespace, or inconsistent labels",
result,
result["passed"],
related_rule_id="CR-02"
)
# --------------------------------------------------------
# V-07 — Orders category
# --------------------------------------------------------
result = category_quality(orders_clean, "order_status")
record_check(
"V-07", "Category", "Orders",
"Apakah order_status konsisten dan tidak kosong?",
"No null/blank values, whitespace, or inconsistent labels",
result,
result["passed"],
related_rule_id="CR-03"
)
# --------------------------------------------------------
# V-08 — Payments, Restaurants, Order Items categories
# --------------------------------------------------------
category_specs = [
("Payments", payments_clean, "payment_method"),
("Restaurants", restaurants_clean, "price_tier"),
("Order Items", order_items_clean, "item_category"),
]
category_results = {}
for name, df, column in category_specs:
category_results[name] = category_quality(df, column)
passed = all(
result["passed"] for result in category_results.values()
)
record_check(
"V-08", "Category",
"Payments / Restaurants / Order Items",
"Apakah kategori pada ketiga dataset konsisten?",
"No null/blank values, whitespace, or inconsistent labels",
category_results,
passed,
related_rule_id="CR-04; CR-05; CR-06; CR-07"
)
# --------------------------------------------------------
# V-09 — Order Items formula validation
# line_amount = quantity * unit_price - line_discount
# --------------------------------------------------------
oi = order_items_clean.copy()
qty = pd.to_numeric(oi["quantity"], errors="coerce")
unit_price = pd.to_numeric(oi["unit_price"], errors="coerce")
discount = pd.to_numeric(oi["line_discount"], errors="coerce")
actual = pd.to_numeric(oi["line_amount"], errors="coerce")
expected = qty * unit_price - discount
valid_numeric = (
qty.notna()
& unit_price.notna()
& discount.notna()
& actual.notna()
)
matches = (actual - expected).abs() <= 0.01
mismatch = valid_numeric & ~matches
invalid_numeric = ~valid_numeric
result = {
"total_rows": len(oi),
"invalid_numeric_rows": int(invalid_numeric.sum()),
"formula_mismatch_rows": int(mismatch.sum()),
"mismatch_examples": oi.loc[
mismatch, "order_item_id"
].head(10).tolist()
}
passed = bool(invalid_numeric.sum() == 0 and mismatch.sum() == 0)
record_check(
"V-09", "Formula", "Order Items",
"Apakah line_amount = quantity × unit_price − line_discount?",
"All rows numeric and formula-consistent",
result,
passed,
related_rule_id="CR-09; CR-10"
)
# --------------------------------------------------------
# V-10 — Orders total formula validation
# total_amount = subtotal_amount + delivery_fee - discount_amount
# --------------------------------------------------------
od = orders_clean.copy()
subtotal = pd.to_numeric(od["subtotal_amount"], errors="coerce")
fee = pd.to_numeric(od["delivery_fee"], errors="coerce")
order_discount = pd.to_numeric(
od["discount_amount"], errors="coerce"
)
total = pd.to_numeric(od["total_amount"], errors="coerce")
expected_total = subtotal + fee - order_discount
valid_numeric = (
subtotal.notna()
& fee.notna()
& order_discount.notna()
& total.notna()
)
mismatch = valid_numeric & (
(total - expected_total).abs() > 0.01
)
invalid_numeric = ~valid_numeric
result = {
"total_rows": len(od),
"invalid_numeric_rows": int(invalid_numeric.sum()),
"formula_mismatch_rows": int(mismatch.sum()),
"mismatch_examples": od.loc[
mismatch, "order_id"
].head(10).tolist()
}
passed = bool(invalid_numeric.sum() == 0 and mismatch.sum() == 0)
record_check(
"V-10", "Formula", "Orders",
"Apakah total_amount = subtotal_amount + delivery_fee - discount_amount?",
"All rows numeric and formula-consistent",
result,
passed,
related_rule_id="CR-08"
)
# --------------------------------------------------------
# V-11 — Orders ↔ Order Items reconciliation
# subtotal_amount = sum(line_amount) per order_id
# --------------------------------------------------------
item_amount = pd.to_numeric(
order_items_clean["line_amount"], errors="coerce"
)
item_totals = order_items_clean.assign(
_line_amount_numeric=item_amount
).groupby("order_id", dropna=False).agg(
items_total=("_line_amount_numeric", "sum"),
item_count=("order_item_id", "count")
).reset_index()
recon = orders_clean[
["order_id", "subtotal_amount"]
].merge(item_totals, on="order_id", how="left")
recon["items_total"] = recon["items_total"].fillna(0)
recon["item_count"] = recon["item_count"].fillna(0)
recon["subtotal_numeric"] = pd.to_numeric(
recon["subtotal_amount"], errors="coerce"
)
no_items = recon["item_count"].eq(0)
invalid_subtotal = recon["subtotal_numeric"].isna()
mismatch = (
recon["subtotal_numeric"].notna()
& (
(recon["subtotal_numeric"] - recon["items_total"]).abs()
> 0.01
)
)
failed_recon = no_items | invalid_subtotal | mismatch
result = {
"total_orders": len(recon),
"orders_without_items": int(no_items.sum()),
"invalid_subtotal_rows": int(invalid_subtotal.sum()),
"subtotal_mismatch_rows": int(mismatch.sum()),
"failed_order_examples": recon.loc[
failed_recon, "order_id"
].head(10).tolist()
}
passed = bool(failed_recon.sum() == 0)
record_check(
"V-11", "Reconciliation", "Orders ↔ Order Items",
"Apakah subtotal_amount sesuai dengan total line_amount setiap order?",
"Every order has items and item total matches subtotal_amount",
result,
passed,
related_rule_id="CR-08; CR-09; CR-10"
)
# --------------------------------------------------------
# Helper: foreign key validation
# --------------------------------------------------------
def foreign_key_check(child_df, child_column, parent_df, parent_column):
child_values = child_df[child_column]
parent_values = parent_df[parent_column].dropna()
invalid = (
child_values.isna()
| ~child_values.isin(parent_values)
)
return {
"child_rows": len(child_df),
"null_foreign_keys": int(child_values.isna().sum()),
"unmatched_foreign_keys": int(
(invalid & child_values.notna()).sum()
),
"invalid_key_examples": (
child_values[invalid].drop_duplicates().head(10).tolist()
),
"passed": bool(invalid.sum() == 0)
}
# --------------------------------------------------------
# V-12 — Orders.customer_id → Customers.customer_id
# --------------------------------------------------------
result = foreign_key_check(
orders_clean, "customer_id",
customers_clean, "customer_id"
)
record_check(
"V-12", "Foreign Key", "Orders → Customers",
"Apakah setiap customer_id pada Orders tersedia di Customers?",
"No null or unmatched customer_id",
result,
result["passed"]
)
# --------------------------------------------------------
# V-13 — Orders.restaurant_id → Restaurants.restaurant_id
# --------------------------------------------------------
result = foreign_key_check(
orders_clean, "restaurant_id",
restaurants_clean, "restaurant_id"
)
record_check(
"V-13", "Foreign Key", "Orders → Restaurants",
"Apakah setiap restaurant_id pada Orders tersedia di Restaurants?",
"No null or unmatched restaurant_id",
result,
result["passed"]
)
# --------------------------------------------------------
# V-14 — Order Items / Payments → Orders
# --------------------------------------------------------
fk_specs = [
("Order Items → Orders", order_items_clean, "order_id"),
("Payments → Orders", payments_clean, "order_id"),
]
fk_results = {}
for relationship, child_df, child_column in fk_specs:
fk_results[relationship] = foreign_key_check(
child_df, child_column,
orders_clean, "order_id"
)
passed = all(
result["passed"] for result in fk_results.values()
)
record_check(
"V-14", "Foreign Key",
"Order Items / Payments → Orders",
"Apakah order_id pada Order Items dan Payments tersedia di Orders?",
"No null or unmatched order_id",
fk_results,
passed
)
else:
print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
# F-14 / CR-14: Memastikan riwayat refund tetap dipertahankan
target_orders = [f"O{i:05d}" for i in range(323, 333)]
# Ambil transaksi attempt 2 yang berstatus refunded
raw_refunds = payments_raw[
payments_raw["order_id"].isin(target_orders)
& (payments_raw["attempt_number"] == 2)
& (payments_raw["payment_status"].astype(str).str.strip().str.lower() == "refunded")
].copy()
clean_refunds = payments_clean[
payments_clean["order_id"].isin(target_orders)
& (payments_clean["attempt_number"] == 2)
& (payments_clean["payment_status"].astype(str).str.strip().str.lower() == "refunded")
].copy()
# Pastikan 10 record refund tetap ada setelah cleaning
refund_ids_preserved = (
"payment_id" in payments_raw.columns
and "payment_id" in payments_clean.columns
and set(raw_refunds["payment_id"]).issubset(set(clean_refunds["payment_id"]))
)
refund_count_correct = len(raw_refunds) == 10 and len(clean_refunds) == 10
# Pastikan nominal refund sama dengan pembayaran awal
initial_payments = payments_clean[
payments_clean["order_id"].isin(target_orders)
& (payments_clean["attempt_number"] == 1)
& (payments_clean["payment_status"].astype(str).str.strip().str.lower() == "successful")
]
refund_amounts = clean_refunds.groupby("order_id")["payment_amount"].sum()
initial_amounts = initial_payments.groupby("order_id")["payment_amount"].sum()
amounts_match = (
refund_amounts.index.isin(initial_amounts.index).all()
and all(
refund_amounts[order_id] == initial_amounts[order_id]
for order_id in refund_amounts.index
)
)
v14_passed = refund_ids_preserved and refund_count_correct and amounts_match
print("Jumlah refund pada raw:", len(raw_refunds))
print("Jumlah refund pada clean:", len(clean_refunds))
print("Payment ID dipertahankan:", refund_ids_preserved)
print("Jumlah record sesuai:", refund_count_correct)
print("Nominal refund sama dengan pembayaran awal:", amounts_match)
print("Status V-15 F-14:", "PASS" if v14_passed else "FAIL")
Jumlah refund pada raw: 10
Jumlah refund pada clean: 10
Payment ID dipertahankan: True
Jumlah record sesuai: True
Nominal refund sama dengan pembayaran awal: True
Status V-15 F-14: PASS
# F-14 / CR-14: Memastikan riwayat refund tetap dipertahankan
target_orders = [f"O{i:05d}" for i in range(323, 333)]
status_col = "payment_status"
amount_col = "payment_amount"
attempt_col = "attempt_number"
# Pembayaran attempt 2 yang refunded
raw_refunds = payments_raw[
payments_raw["order_id"].isin(target_orders)
& (payments_raw[attempt_col] == 2)
& (payments_raw[status_col].astype(str).str.strip().str.lower() == "refunded")
].copy()
clean_refunds = payments_clean[
payments_clean["order_id"].isin(target_orders)
& (payments_clean[attempt_col] == 2)
& (payments_clean[status_col].astype(str).str.strip().str.lower() == "refunded")
].copy()
# Pembayaran awal attempt 1 yang successful
initial_payments = payments_clean[
payments_clean["order_id"].isin(target_orders)
& (payments_clean[attempt_col] == 1)
& (payments_clean[status_col].astype(str).str.strip().str.lower() == "successful")
].copy()
# Pemeriksaan pelestarian payment ID
refund_ids_preserved = set(raw_refunds["payment_id"]).issubset(
set(clean_refunds["payment_id"])
)
# Pemeriksaan jumlah refund
refund_count_correct = len(raw_refunds) == 10 and len(clean_refunds) == 10
# Perbandingan nominal pembayaran awal dan refund per order
refund_amounts = clean_refunds.groupby("order_id")[amount_col].sum()
initial_amounts = initial_payments.groupby("order_id")[amount_col].sum()
all_orders_have_both_records = (
set(target_orders).issubset(set(refund_amounts.index))
and set(target_orders).issubset(set(initial_amounts.index))
)
amounts_match = (
all_orders_have_both_records
and all(
refund_amounts[order_id] == initial_amounts[order_id]
for order_id in target_orders
)
)
v15_f14_passed = (
refund_ids_preserved
and refund_count_correct
and amounts_match
)
print("--- Hasil Validasi F-14 / CR-14 ---")
print("Refund pada raw:", len(raw_refunds))
print("Refund pada clean:", len(clean_refunds))
print("Payment ID dipertahankan:", refund_ids_preserved)
print("Jumlah record sesuai:", refund_count_correct)
print("Seluruh order memiliki pembayaran awal dan refund:", all_orders_have_both_records)
print("Nominal refund sama dengan pembayaran awal:", amounts_match)
print("Status:", "PASS" if v15_f14_passed else "FAIL")
--- Hasil Validasi F-14 / CR-14 ---
Refund pada raw: 10
Refund pada clean: 10
Payment ID dipertahankan: True
Jumlah record sesuai: True
Seluruh order memiliki pembayaran awal dan refund: True
Nominal refund sama dengan pembayaran awal: True
Status: PASS
# F-08 / CR-08: Memastikan promo_code kosong yang legitimate tetap dipertahankan
raw_blank_promo = orders_raw[
orders_raw["promo_code"].isna()
| (orders_raw["promo_code"].astype(str).str.strip() == "")
].copy()
clean_blank_promo = orders_clean[
orders_clean["promo_code"].isna()
| (orders_clean["promo_code"].astype(str).str.strip() == "")
].copy()
raw_blank_order_ids = set(raw_blank_promo["order_id"])
clean_blank_order_ids = set(clean_blank_promo["order_id"])
f08_preserved = raw_blank_order_ids.issubset(clean_blank_order_ids)
f08_count_correct = len(raw_blank_promo) == 176
v15_passed = (
f08_preserved
and f08_count_correct
and v15_f14_passed
)
print("--- Hasil Validasi V-15: KEEP Findings ---")
print("F-08 | Promo code kosong pada raw:", len(raw_blank_promo))
print("F-08 | Order tanpa promo tetap dipertahankan:", f08_preserved)
print("F-08 | Jumlah sesuai temuan:", f08_count_correct)
print("F-14 | Riwayat refund dipertahankan:", v15_f14_passed)
print("Status V-15:", "PASS" if v15_passed else "FAIL")
--- Hasil Validasi V-15: KEEP Findings ---
F-08 | Promo code kosong pada raw: 176
F-08 | Order tanpa promo tetap dipertahankan: True
F-08 | Jumlah sesuai temuan: True
F-14 | Riwayat refund dipertahankan: True
Status V-15: PASS
# V-15: Validasi bahwa KEEP findings tetap dipertahankan
record_check(
"V-15",
"Judgment",
"KEEP findings: Orders dan Payments",
"Apakah temuan berstatus KEEP pada F-08 dan F-14 tetap dipertahankan setelah proses cleaning?",
"176 record promo_code kosong tetap dipertahankan; 10 record refund pada order O00323–O00332 tetap dipertahankan beserta payment ID dan nominalnya.",
(
f"F-08: {len(raw_blank_promo)} record promo_code kosong pada raw; "
f"preserved={f08_preserved}. "
f"F-14: {len(raw_refunds)} refund pada raw dan {len(clean_refunds)} pada clean; "
f"payment ID preserved={refund_ids_preserved}; "
f"nominal match={amounts_match}."
),
v15_passed,
related_rule_id="CR-08; CR-14",
notes="Memastikan legitimate missing values dan refund audit trail berstatus KEEP tidak dihapus atau diubah tanpa bukti."
)
validation_df = pd.DataFrame(validation_results)
display(validation_df.tail())
|
check_id |
validation_level |
dataset_relationship |
validation_question |
expected_criteria |
actual_result_metric |
status |
related_rule_id |
notes |
| 10 |
V-11 |
Reconciliation |
Orders ↔ Order Items |
Apakah subtotal_amount sesuai dengan total lin... |
Every order has items and item total matches s... |
{'total_orders': 332, 'orders_without_items': ... |
PASS |
CR-08; CR-09; CR-10 |
|
| 11 |
V-12 |
Foreign Key |
Orders → Customers |
Apakah setiap customer_id pada Orders tersedia... |
No null or unmatched customer_id |
{'child_rows': 332, 'null_foreign_keys': 0, 'u... |
PASS |
|
|
| 12 |
V-13 |
Foreign Key |
Orders → Restaurants |
Apakah setiap restaurant_id pada Orders tersed... |
No null or unmatched restaurant_id |
{'child_rows': 332, 'null_foreign_keys': 0, 'u... |
PASS |
|
|
| 13 |
V-14 |
Foreign Key |
Order Items / Payments → Orders |
Apakah order_id pada Order Items dan Payments ... |
No null or unmatched order_id |
{'Order Items → Orders': {'child_rows': 944, '... |
PASS |
|
|
| 14 |
V-15 |
Judgment |
KEEP findings: Orders dan Payments |
Apakah temuan berstatus KEEP pada F-08 dan F-1... |
176 record promo_code kosong tetap dipertahank... |
F-08: 176 record promo_code kosong pada raw; p... |
PASS |
CR-08; CR-14 |
Memastikan legitimate missing values dan refun... |
validation_df = pd.DataFrame(validation_results)
display(
validation_df[
validation_df["check_id"].isin([
"V-06", "V-07", "V-08", "V-09",
"V-10", "V-11", "V-12", "V-13", "V-14","V-15"
])
]
)
|
check_id |
validation_level |
dataset_relationship |
validation_question |
expected_criteria |
actual_result_metric |
status |
related_rule_id |
notes |
| 5 |
V-06 |
Category |
Customers |
Apakah customer_segment konsisten dan tidak ko... |
No null/blank values, whitespace, or inconsist... |
{'null_count': 0, 'blank_count': 0, 'leading_t... |
PASS |
CR-02 |
|
| 6 |
V-07 |
Category |
Orders |
Apakah order_status konsisten dan tidak kosong? |
No null/blank values, whitespace, or inconsist... |
{'null_count': 0, 'blank_count': 0, 'leading_t... |
PASS |
CR-03 |
|
| 7 |
V-08 |
Category |
Payments / Restaurants / Order Items |
Apakah kategori pada ketiga dataset konsisten? |
No null/blank values, whitespace, or inconsist... |
{'Payments': {'null_count': 0, 'blank_count': ... |
PASS |
CR-04; CR-05; CR-06; CR-07 |
|
| 8 |
V-09 |
Formula |
Order Items |
Apakah line_amount = quantity × unit_price − l... |
All rows numeric and formula-consistent |
{'total_rows': 944, 'invalid_numeric_rows': 0,... |
PASS |
CR-09; CR-10 |
|
| 9 |
V-10 |
Formula |
Orders |
Apakah total_amount = subtotal_amount + delive... |
All rows numeric and formula-consistent |
{'total_rows': 332, 'invalid_numeric_rows': 0,... |
PASS |
CR-08 |
|
| 10 |
V-11 |
Reconciliation |
Orders ↔ Order Items |
Apakah subtotal_amount sesuai dengan total lin... |
Every order has items and item total matches s... |
{'total_orders': 332, 'orders_without_items': ... |
PASS |
CR-08; CR-09; CR-10 |
|
| 11 |
V-12 |
Foreign Key |
Orders → Customers |
Apakah setiap customer_id pada Orders tersedia... |
No null or unmatched customer_id |
{'child_rows': 332, 'null_foreign_keys': 0, 'u... |
PASS |
|
|
| 12 |
V-13 |
Foreign Key |
Orders → Restaurants |
Apakah setiap restaurant_id pada Orders tersed... |
No null or unmatched restaurant_id |
{'child_rows': 332, 'null_foreign_keys': 0, 'u... |
PASS |
|
|
| 13 |
V-14 |
Foreign Key |
Order Items / Payments → Orders |
Apakah order_id pada Order Items dan Payments ... |
No null or unmatched order_id |
{'Order Items → Orders': {'child_rows': 944, '... |
PASS |
|
|
| 14 |
V-15 |
Judgment |
KEEP findings: Orders dan Payments |
Apakah temuan berstatus KEEP pada F-08 dan F-1... |
176 record promo_code kosong tetap dipertahank... |
F-08: 176 record promo_code kosong pada raw; p... |
PASS |
CR-08; CR-14 |
Memastikan legitimate missing values dan refun... |
13C. KEEP Preservation Check — EDIT + RUN
Gunakan `V-15` untuk membuktikan bahwa finding yang kamu putuskan **KEEP** tidak ikut berubah akibat over-cleaning.
Jika kamu memiliki lebih dari satu KEEP finding, gunakan evidence yang cukup di Working Paper lalu rangkum hasilnya ke `V-15`.
# ============================================================
# EDIT AREA 3C — V-15 KEEP PRESERVATION
# ============================================================
if DATA_READY:
# TODO: isi dengan ID finding yang benar-benar berstatus KEEP
# berdasarkan Findings Log / Cleaning Rulebook.
keep_findings = [
# Contoh format: "F-XX"
]
# Catat validasi awal.
# Jangan otomatis PASS jika daftar KEEP belum diisi
# atau belum ada evidence pembanding.
keep_evidence = {
"keep_findings": keep_findings,
"keep_findings_count": len(keep_findings),
"evidence_available": False,
"verification_status": (
"PENDING — daftar finding KEEP dan evidence "
"preservasi belum dilengkapi."
)
}
record_check(
"V-15", "Preservation", "KEEP Findings",
"Apakah record yang diputuskan KEEP tetap dipertahankan "
"dan tidak diubah tanpa evidence yang memadai?",
"All KEEP findings preserved with supporting evidence",
keep_evidence,
False,
notes=(
"Belum dapat dinyatakan PASS sebelum daftar finding "
"KEEP, record terkait, dan evidence preservasi diverifikasi."
)
)
else:
print("VALIDATION SKIPPED — DATA READY GATE belum PASS.")
Validation Results Table
validation_df = pd.DataFrame(validation_results)
if validation_df.empty:
print("VALIDATION GATE: PENDING")
print("→ Belum ada validation evidence yang dicatat.")
else:
validation_df = validation_df.sort_values("check_id").reset_index(drop=True)
display(validation_df)
present_ids = set(validation_df["check_id"])
missing_validation_ids = [
x for x in REQUIRED_VALIDATION_IDS if x not in present_ids
]
print(validation_df["status"].value_counts(dropna=False))
if missing_validation_ids:
print("VALIDATION GATE: PENDING")
print("Missing required checks:", ", ".join(missing_validation_ids))
elif (validation_df["status"] == "FAIL").any():
print("VALIDATION GATE: FAIL")
print("→ Review FAIL sebelum finalisasi.")
else:
print("VALIDATION GATE: PASS")
|
check_id |
validation_level |
dataset_relationship |
validation_question |
expected_criteria |
actual_result_metric |
status |
related_rule_id |
notes |
| 0 |
V-01 |
Schema |
All clean datasets |
Apakah seluruh required column masih tersedia? |
Required columns available |
{'Customers': {'missing_columns': [], 'passed'... |
PASS |
|
Seluruh required columns tersedia. |
| 1 |
V-02 |
Key |
Customers |
Apakah customer_id valid sesuai grain customer? |
No null key; uniqueness sesuai grain |
{'dataset': 'Customers', 'key_cols': 'customer... |
PASS |
CR-01 |
|
| 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 |
{'dataset': 'Order Items', 'key_cols': 'order_... |
PASS |
|
|
| 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-04 |
Pemeriksaan menggunakan payment_id. Repeated o... |
| 5 |
V-06 |
Category |
Customers |
Apakah customer_segment konsisten dan tidak ko... |
No null/blank values, whitespace, or inconsist... |
{'null_count': 0, 'blank_count': 0, 'leading_t... |
PASS |
CR-02 |
|
| 6 |
V-07 |
Category |
Orders |
Apakah order_status konsisten dan tidak kosong? |
No null/blank values, whitespace, or inconsist... |
{'null_count': 0, 'blank_count': 0, 'leading_t... |
PASS |
CR-03 |
|
| 7 |
V-08 |
Category |
Payments / Restaurants / Order Items |
Apakah kategori pada ketiga dataset konsisten? |
No null/blank values, whitespace, or inconsist... |
{'Payments': {'null_count': 0, 'blank_count': ... |
PASS |
CR-04; CR-05; CR-06; CR-07 |
|
| 8 |
V-09 |
Formula |
Order Items |
Apakah line_amount = quantity × unit_price − l... |
All rows numeric and formula-consistent |
{'total_rows': 944, 'invalid_numeric_rows': 0,... |
PASS |
CR-09; CR-10 |
|
| 9 |
V-10 |
Formula |
Orders |
Apakah total_amount = subtotal_amount + delive... |
All rows numeric and formula-consistent |
{'total_rows': 332, 'invalid_numeric_rows': 0,... |
PASS |
CR-08 |
|
| 10 |
V-11 |
Reconciliation |
Orders ↔ Order Items |
Apakah subtotal_amount sesuai dengan total lin... |
Every order has items and item total matches s... |
{'total_orders': 332, 'orders_without_items': ... |
PASS |
CR-08; CR-09; CR-10 |
|
| 11 |
V-12 |
Foreign Key |
Orders → Customers |
Apakah setiap customer_id pada Orders tersedia... |
No null or unmatched customer_id |
{'child_rows': 332, 'null_foreign_keys': 0, 'u... |
PASS |
|
|
| 12 |
V-13 |
Foreign Key |
Orders → Restaurants |
Apakah setiap restaurant_id pada Orders tersed... |
No null or unmatched restaurant_id |
{'child_rows': 332, 'null_foreign_keys': 0, 'u... |
PASS |
|
|
| 13 |
V-14 |
Foreign Key |
Order Items / Payments → Orders |
Apakah order_id pada Order Items dan Payments ... |
No null or unmatched order_id |
{'Order Items → Orders': {'child_rows': 944, '... |
PASS |
|
|
| 14 |
V-15 |
Judgment |
KEEP findings: Orders dan Payments |
Apakah temuan berstatus KEEP pada F-08 dan F-1... |
176 record promo_code kosong tetap dipertahank... |
F-08: 176 record promo_code kosong pada raw; p... |
PASS |
CR-08; CR-14 |
Memastikan legitimate missing values dan refun... |
status
PASS 15
Name: count, dtype: int64
VALIDATION GATE: PASS
# ============================================================
# DISPLAY — ALL VALIDATION RESULTS (V-01 s.d. V-15)
# ============================================================
validation_df = pd.DataFrame(validation_results)
display(
validation_df.sort_values("check_id").reset_index(drop=True)
)
|
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 |
{'Customers': {'missing_columns': [], 'passed'... |
PASS |
|
Seluruh required columns tersedia. |
| 1 |
V-02 |
Key |
Customers |
Apakah customer_id valid sesuai grain customer? |
No null key; uniqueness sesuai grain |
{'dataset': 'Customers', 'key_cols': 'customer... |
PASS |
CR-01 |
|
| 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 |
{'dataset': 'Order Items', 'key_cols': 'order_... |
PASS |
|
|
| 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-04 |
Pemeriksaan menggunakan payment_id. Repeated o... |
| 5 |
V-06 |
Category |
Customers |
Apakah customer_segment konsisten dan tidak ko... |
No null/blank values, whitespace, or inconsist... |
{'null_count': 0, 'blank_count': 0, 'leading_t... |
PASS |
CR-02 |
|
| 6 |
V-07 |
Category |
Orders |
Apakah order_status konsisten dan tidak kosong? |
No null/blank values, whitespace, or inconsist... |
{'null_count': 0, 'blank_count': 0, 'leading_t... |
PASS |
CR-03 |
|
| 7 |
V-08 |
Category |
Payments / Restaurants / Order Items |
Apakah kategori pada ketiga dataset konsisten? |
No null/blank values, whitespace, or inconsist... |
{'Payments': {'null_count': 0, 'blank_count': ... |
PASS |
CR-04; CR-05; CR-06; CR-07 |
|
| 8 |
V-09 |
Formula |
Order Items |
Apakah line_amount = quantity × unit_price − l... |
All rows numeric and formula-consistent |
{'total_rows': 944, 'invalid_numeric_rows': 0,... |
PASS |
CR-09; CR-10 |
|
| 9 |
V-10 |
Formula |
Orders |
Apakah total_amount = subtotal_amount + delive... |
All rows numeric and formula-consistent |
{'total_rows': 332, 'invalid_numeric_rows': 0,... |
PASS |
CR-08 |
|
| 10 |
V-11 |
Reconciliation |
Orders ↔ Order Items |
Apakah subtotal_amount sesuai dengan total lin... |
Every order has items and item total matches s... |
{'total_orders': 332, 'orders_without_items': ... |
PASS |
CR-08; CR-09; CR-10 |
|
| 11 |
V-12 |
Foreign Key |
Orders → Customers |
Apakah setiap customer_id pada Orders tersedia... |
No null or unmatched customer_id |
{'child_rows': 332, 'null_foreign_keys': 0, 'u... |
PASS |
|
|
| 12 |
V-13 |
Foreign Key |
Orders → Restaurants |
Apakah setiap restaurant_id pada Orders tersed... |
No null or unmatched restaurant_id |
{'child_rows': 332, 'null_foreign_keys': 0, 'u... |
PASS |
|
|
| 13 |
V-14 |
Foreign Key |
Order Items / Payments → Orders |
Apakah order_id pada Order Items dan Payments ... |
No null or unmatched order_id |
{'Order Items → Orders': {'child_rows': 944, '... |
PASS |
|
|
| 14 |
V-15 |
Judgment |
KEEP findings: Orders dan Payments |
Apakah temuan berstatus KEEP pada F-08 dan F-1... |
176 record promo_code kosong tetap dipertahank... |
F-08: 176 record promo_code kosong pada raw; p... |
PASS |
CR-08; CR-14 |
Memastikan legitimate missing values dan refun... |
13D. Raw Integrity Check — RUN
Cell ini membandingkan SHA-256 raw source saat pertama kali di-load dengan file raw saat ini.
Target: **tidak ada raw/source yang berubah**.
RAW_INTEGRITY_PASS = False
raw_integrity_rows = []
if not DATA_READY:
print("RAW INTEGRITY CHECK: SKIPPED")
print("→ DATA READY GATE belum PASS.")
else:
file_map_integrity = {
"Customers": RAW_DIR / EXPECTED_FILES["customers"],
"Orders": RAW_DIR / EXPECTED_FILES["orders"],
"Order Items": RAW_DIR / EXPECTED_FILES["order_items"],
"Payments": RAW_DIR / EXPECTED_FILES["payments"],
"Restaurants": RAW_DIR / EXPECTED_FILES["restaurants"],
}
for label, path in file_map_integrity.items():
current_hash = sha256_file(path)
baseline_hash = RAW_HASH_BASELINE.get(label)
raw_integrity_rows.append({
"dataset": label,
"unchanged": current_hash == baseline_hash,
})
raw_integrity_df = pd.DataFrame(raw_integrity_rows)
display(raw_integrity_df)
RAW_INTEGRITY_PASS = bool(raw_integrity_df["unchanged"].all())
if RAW_INTEGRITY_PASS:
print("RAW INTEGRITY CHECK: PASS")
else:
print("RAW INTEGRITY CHECK: FAIL")
print("→ Raw/source berubah. Kembalikan source asli sebelum finalisasi.")
|
dataset |
unchanged |
| 0 |
Customers |
True |
| 1 |
Orders |
True |
| 2 |
Order Items |
True |
| 3 |
Payments |
True |
| 4 |
Restaurants |
True |
RAW INTEGRITY CHECK: PASS
14. Final Data Readiness Gate — RUN
Final Gate hanya boleh:
- `PASS` jika **IDENTITY PASS + semua V-01 s.d. V-15 PASS + RAW INTEGRITY PASS**;
- `NOT READY` jika ada critical FAIL atau raw source berubah;
- `PENDING` jika validation belum lengkap.
Jangan mengubah nilai `FINAL_GATE` secara manual.
def derive_final_gate(validation_df, raw_integrity_pass, identity_gate_pass):
if not identity_gate_pass:
return "PENDING", ["Identity belum lengkap."]
if validation_df.empty:
return "PENDING", ["Validation evidence masih kosong."]
present_ids = set(validation_df["check_id"])
missing_ids = [x for x in REQUIRED_VALIDATION_IDS if x not in present_ids]
required_rows = validation_df[
validation_df["check_id"].isin(REQUIRED_VALIDATION_IDS)
].copy()
if (required_rows["status"] == "FAIL").any():
failed_ids = required_rows.loc[
required_rows["status"] == "FAIL", "check_id"
].tolist()
return "NOT READY", [f"Validation FAIL: {', '.join(failed_ids)}"]
if not raw_integrity_pass:
return "NOT READY", ["Raw Integrity Check FAIL."]
if missing_ids:
return "PENDING", [f"Validation belum lengkap: {', '.join(missing_ids)}"]
all_pass = (
len(required_rows) == len(REQUIRED_VALIDATION_IDS)
and (required_rows["status"] == "PASS").all()
)
if all_pass:
return "PASS", []
return "PENDING", ["Review validation evidence."]
FINAL_GATE, FINAL_GATE_REASONS = derive_final_gate(
validation_df,
RAW_INTEGRITY_PASS,
IDENTITY_GATE_PASS,
)
print("PM1 DATA READINESS GATE:", FINAL_GATE)
if FINAL_GATE_REASONS:
for reason in FINAL_GATE_REASONS:
print("→", reason)
FINAL_JUDGMENT_RATIONALE = (
"Dataset dinyatakan PASS pada PM1 Data Readiness Gate karena "
"Raw Integrity Check menunjukkan bahwa seluruh dataset raw "
"(Customers, Orders, Order Items, Payments, dan Restaurants) "
"tidak berubah. Seluruh validation ID yang diwajibkan telah "
"tersedia dan berstatus PASS, tanpa validation FAIL. "
"Identity gate juga telah terpenuhi. Dengan demikian, dataset "
"memenuhi kriteria data readiness berdasarkan pemeriksaan "
"integritas raw, kelengkapan evidence validasi, dan identity gate."
)
print("FINAL JUDGMENT RATIONALE:")
print(FINAL_JUDGMENT_RATIONALE)
PM1 DATA READINESS GATE: PASS
FINAL JUDGMENT RATIONALE:
Dataset dinyatakan PASS pada PM1 Data Readiness Gate karena Raw Integrity Check menunjukkan bahwa seluruh dataset raw (Customers, Orders, Order Items, Payments, dan Restaurants) tidak berubah. Seluruh validation ID yang diwajibkan telah tersedia dan berstatus PASS, tanpa validation FAIL. Identity gate juga telah terpenuhi. Dengan demikian, dataset memenuhi kriteria data readiness berdasarkan pemeriksaan integritas raw, kelengkapan evidence validasi, dan identity gate.
15. Export Clean Data — RUN SETELAH FINAL GATE PASS
Output:
- `customers_clean.csv`
- `orders_clean.csv`
- `order_items_clean.csv`
- `payments_clean.csv`
- `restaurants_clean.csv`
Jika Final Gate belum PASS, export akan **diblok otomatis**.
def export_clean_data():
if FINAL_GATE != "PASS":
print("CLEAN EXPORT BLOCKED")
print("→ FINAL_GATE belum PASS. Selesaikan validation terlebih dahulu.")
return False
customers_clean.to_csv(PROCESSED_DIR / "customers_clean.csv", index=False)
orders_clean.to_csv(PROCESSED_DIR / "orders_clean.csv", index=False)
order_items_clean.to_csv(PROCESSED_DIR / "order_items_clean.csv", index=False)
payments_clean.to_csv(PROCESSED_DIR / "payments_clean.csv", index=False)
restaurants_clean.to_csv(PROCESSED_DIR / "restaurants_clean.csv", index=False)
print("CLEAN PACKAGE SAVE: PASS")
print("Saved to:", PROCESSED_DIR)
return True
# Hapus tanda # pada baris berikut HANYA setelah FINAL_GATE: PASS
export_clean_data()
CLEAN PACKAGE SAVE: PASS
Saved to: C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\data\processed\pm1_swiftbite
True
16. Export Evidence — RUN SETELAH EVIDENCE LENGKAP
Evidence boleh diekspor untuk review, tetapi **clean package final tetap hanya boleh disimpan saat FINAL_GATE: PASS**.
def export_evidence():
findings.to_csv(OUTPUT_DIR / "pm1_findings.csv", index=False)
cleaning_rules.to_csv(OUTPUT_DIR / "pm1_cleaning_rulebook.csv", index=False)
cleaning_log.to_csv(OUTPUT_DIR / "pm1_cleaning_log.csv", index=False)
validation_df.to_csv(OUTPUT_DIR / "pm1_validation_results.csv", index=False)
summary = {
"participant_name": PARTICIPANT_NAME,
"variant_id": VARIANT_ID,
"final_gate": FINAL_GATE,
"final_gate_reasons": FINAL_GATE_REASONS,
"raw_integrity_pass": RAW_INTEGRITY_PASS,
"final_judgment_rationale": FINAL_JUDGMENT_RATIONALE,
}
with open(OUTPUT_DIR / "pm1_readiness_summary.json", "w", encoding="utf-8") as f:
json.dump(summary, f, ensure_ascii=False, indent=2)
print("EVIDENCE EXPORT: PASS")
print("Saved to:", OUTPUT_DIR)
return True
# Hapus tanda # pada baris berikut setelah evidence sudah diisi:
export_evidence()
EVIDENCE EXPORT: PASS
Saved to: C:\NaurahFile\Data Analyst\Bootcamp_Data_Analyst_Workspace_Starter\Bootcamp_Data_Analyst\outputs\pm1_swiftbite
True
17. Final Submission Checklist
### Technical
- [ ] `IDENTITY GATE: PASS`
- [ ] `TECHNICAL GATE: PASS`
- [ ] `SOURCE ACCESS CHECK: PASS`
- [ ] `RAW LOAD GATE: PASS`
- [ ] `SCHEMA GATE: PASS`
- [ ] `RAW INTEGRITY CHECK: PASS`
### Intake, Profiling & Judgment
- [ ] 5 source berhasil dibaca.
- [ ] Grain dan candidate key sudah diperiksa.
- [ ] Relationship utama sudah diperiksa.
- [ ] Material findings sudah diverifikasi.
- [ ] FIX / STANDARDIZE / KEEP / INVESTIGATE memiliki evidence dan alasan.
- [ ] Finding yang menghasilkan cleaning memiliki Rule ID.
### Cleaning & Validation
- [ ] Rulebook dibuat sebelum implementation.
- [ ] Cleaning hanya dilakukan pada working copy.
- [ ] Cleaning Log lengkap.
- [ ] `V-01` sampai `V-15` sudah dicatat.
- [ ] `PM1 DATA READINESS GATE: PASS`
- [ ] 5 clean CSV berhasil diekspor.
Definition of Done
```text
Raw Data
↓
Technical & Source Gate
↓
Understand Grain & Key
↓
Profile → Find → Verify → Judge
↓
Define Cleaning Rule
↓
Clean Working Copy
↓
Validate V-01 ... V-15
↓
Raw Integrity Check
↓
FINAL GATE
↓
Export Clean Package
```