Pembersihan Data
(Data Cleaning)

import pandas as pd
import numpy as np

# Dataset mahasiswa yang kotor (simulasi data nyata)
data_kotor = {
    'nim'   :['2021001','2021002','2021001','2021003','2021004','2021002'],
    'nama'  :['andi pratama','BUDI','andi pratama','Citra','Dewi','budhi'],
    'nilai' :[85,78,85,92,999,78],    # 999 = outlier/kesalahan input
    'kelas' :['A','a','A','B','B','A'],
    'kota'  :[' jakarta','BANDUNG',' jakarta',np.nan,'Surabaya','BANDUNG']
}
df = pd.DataFrame(data_kotor)

# ── AUDIT AWAL ────────────────────────────────────
print("=== AUDIT AWAL ===")
print(f"Shape        : {df.shape}")
print(f"Duplikat     : {df.duplicated().sum()} baris exact duplikat")
print(f"NIM duplikat : {df['nim'].duplicated().sum()} NIM muncul >1x")
print("\nMissing Values:")
print(df.isnull().sum())
print("\nStatistik nilai:")
print(df['nilai'].describe())

# ── STEP 1: Hapus exact duplicates ───────────────────
df_clean = df.drop_duplicates().reset_index(drop=True)
print(f"[1] Setelah drop_duplicates: {len(df_clean)} baris (dari {len(df)})")

# ── STEP 2: Standarisasi teks ─────────────────────────
df_clean['nama']  = df_clean['nama'].str.strip().str.title()
df_clean['kelas'] = df_clean['kelas'].str.upper()
df_clean['kota']  = df_clean['kota'].str.strip().str.title()
print("[2] Teks distandarisasi (title case + strip)")

# ── STEP 3: Tangani missing values ───────────────────
kota_modus = df_clean['kota'].mode()[0]
df_clean['kota'] = df_clean['kota'].fillna(kota_modus)
print(f"[3] Missing 'kota' diisi modus: '{kota_modus}'")

# ── STEP 4: Tangani outlier nilai (IQR method) ───────
Q1  = df_clean['nilai'].quantile(0.25)
Q3  = df_clean['nilai'].quantile(0.75)
IQR = Q3 - Q1
batas_atas = Q3 + 1.5 * IQR
batas_bawah = Q1 - 1.5 * IQR
outlier_mask = (df_clean['nilai'] > batas_atas) | (df_clean['nilai'] < batas_bawah)
print(f"[4] Outlier nilai: {outlier_mask.sum()} baris (batas: {batas_bawah:.0f}–{batas_atas:.0f})")
df_clean = df_clean[~outlier_mask].reset_index(drop=True)

# ── STEP 5: Tangani partial duplicates (NIM duplikat) ─
nim_dup = df_clean[df_clean.duplicated(subset=['nim'], keep=False)]
print(f"[5] Partial dup (NIM sama): {len(nim_dup)} baris → pertahankan first occurrence")
df_clean = df_clean.drop_duplicates(subset=['nim'], keep='first').reset_index(drop=True)

# ── STEP 6: Validasi akhir ────────────────────────────
print("\n=== HASIL AKHIR ===")
print(df_clean)
print("\nStatistik nilai setelah cleaning:")
print(df_clean['nilai'].describe())
print(f"\nTotal NaN tersisa: {df_clean.isnull().sum().sum()}")

# Dataset mahasiswa dengan kolom tambahan untuk validasi silang
df_val = pd.DataFrame({
    'nim'        : ['2021001','2021002','2021003','2021004'],
    'tgl_lahir'  : pd.to_datetime(['2002-05-17','2001-03-05','2025-11-22','2003-07-01']),
    'tgl_masuk'  : pd.to_datetime(['2021-09-01']*4),
    'ipk'        : [3.75, 4.50, 3.20, 2.85],  # IPK 4.50 tidak valid!
    'nilai_uts'  : [85, 78, 92, -5]            # -5 tidak valid!
})

print("=== LAPORAN VALIDASI ATURAN BISNIS ===")

# 1. Cek IPK rentang valid (0.00-4.00)
ipk_invalid = df_val[df_val['ipk'] > 4.0]
print(f"IPK > 4.0 (tidak valid): {len(ipk_invalid)} baris")
print(ipk_invalid[['nim','ipk']])

# 2. Cek nilai UTS (0-100)
nilai_invalid = df_val[(df_val['nilai_uts'] < 0) | (df_val['nilai_uts'] > 100)]
print(f"\nNilai UTS di luar 0–100: {len(nilai_invalid)} baris")

# 3. Cek tanggal lahir logis (harus < tanggal masuk, umur wajar)
today = pd.Timestamp('today')
umur  = (df_val['tgl_masuk'] - df_val['tgl_lahir']).dt.days / 365.25
umur_aneh = df_val[(umur < 15) | (umur > 60)]
print(f"\nUmur saat masuk tidak wajar (<15 atau >60 th): {len(umur_aneh)} baris")
print(umur_aneh[['nim','tgl_lahir']])