""" ============================================================= MERGE & UNIFIKASI DATASET TRACER STUDY (2019-2025) ============================================================= Tujuan: - Membaca semua file CSV dari berbagai tahun - Menerapkan column mapping sesuai format setiap tahun - Merge semua data menjadi unified_dataset - Basic cleaning Output: - unified_dataset.csv - summary_merge.txt ============================================================= """ import pandas as pd import json import os from datetime import datetime # Path configuration BASE_DIR = os.path.dirname(os.path.abspath(__file__)) DATA_DIR = os.path.join(BASE_DIR, '..', '..', 'Data_Tracerstudy') OUTPUT_DIR = os.path.join(BASE_DIR, '..', 'data', 'standardized') # Column mapping untuk setiap tahun COLUMN_MAPPING = { "2019": { "kode_prodi": "Kode Prodi", "nim": "NIM/Nomor Mahasiswa", "nama": "Nama", "tahun_lulus": "Tahun Lulus", "status_pekerjaan": "F8", "jenis_perusahaan": "F1101", "nama_perusahaan": "F5b", "jabatan": "F5c" }, "2020": { "kode_prodi": "Kode Prodi", "nim": "NIM/Nomor Mahasiswa", "nama": "Nama", "tahun_lulus": "Tahun Lulus", "status_pekerjaan": "F8", "jenis_perusahaan": "F1101", "nama_perusahaan": "F5b", "jabatan": "F5c" }, "2021": { "kode_prodi": "Kode Prodi", "nim": "NIM", "nama": "Nama", "tahun_lulus": "Tahun Lulus", "status_pekerjaan": "F8", "jenis_perusahaan": "F1101", "nama_perusahaan": "F5b", "jabatan": "F5c" }, "2022": { "kode_prodi": "kdpstmsmh", "nim": "nimhsmsmh", "nama": "nmmhsmsmh", "tahun_lulus": "tahun_lulus", "status_pekerjaan": "f8", "jenis_perusahaan": "f1101", "nama_perusahaan": "f5b", "jabatan": "f5c" }, "2023": { "kode_prodi": "kdpstmsmh", "nim": "nimhsmsmh", "nama": "nmmhsmsmh", "tahun_lulus": "tahun_lulus", "status_pekerjaan": "f8", "jenis_perusahaan": "f1101", "nama_perusahaan": "f5b", "jabatan": "f5c" }, "2024": { "kode_prodi": "2. Kode Program Studi / kdpstmsmh", "nim": "3. NIM / nimhsmsmh (Gunakan Huruf Besar) ", "nama": "4. Nama Lengkap / nmmhsmsmh (Gunakan Huruf Besar)", "tahun_lulus": "7. Tahun Lulus / tahun_lulus", "status_pekerjaan": "1. Jelaskan status Anda saat ini? f8", "jenis_perusahaan": "8. Apa jenis perusahaan/instansi/institusi tempat anda bekerja sekarang? f1101", "nama_perusahaan": "9. Apa nama perusahaan/kantor tempat Anda bekerja? f5b", "jabatan": "10. Bila Anda berwiraswasta, apa posisi/jabatan Anda saat ini ? f5c" }, "2025": { "kode_prodi": "2. Kode Program Studi / kdpstmsmh", "nim": "3. NIM / nimhsmsmh (Gunakan Huruf Besar) ", "nama": "4. Nama Lengkap / nmmhsmsmh (Gunakan Huruf Besar)", "tahun_lulus": "7. Tahun Lulus / tahun_lulus", "status_pekerjaan": "1. Jelaskan status Anda saat ini? f8", "jenis_perusahaan": "7. Apa jenis perusahaan/instansi/institusi tempat anda bekerja sekarang? f1101", "nama_perusahaan": "9. Apa nama perusahaan/kantor tempat Anda bekerja? f5b", "jabatan": "10. Bila Anda berwiraswasta, apa posisi/jabatan Anda saat ini ? f5c *(Wajib diisi) (Jika Memilih Wiraswasta / Jika f8 adalah 3)" } } # File yang akan dibaca DATA_FILES = { "2019": "Data_2019_Tracer_study.csv", "2020": "Data_2020_Tracer_study.csv", "2021": "Data_2021_Tracer_study.csv", "2022": "Data_2022_Tracer_study.csv", "2023": "Data_2023_Tracer_study.csv", "2024": "Data_2024_Tracer_study.csv", "2025": [ "Data_2025_Tracer_study_D3MIF.csv", "Data_2025_Tracer_study_D3TKK.csv", "Data_2025_Tracer_study_D4TIF.csv", "Data_2025_Tracer_study_D4TIFNGANJUK.csv", "Data_2025_Tracer_study_D4TIFSIDOARJO.csv" ] } # Separator untuk setiap tahun SEPARATORS = { "2019": ";", "2020": ";", "2021": ";", "2022": ";", "2023": ";", "2024": ";", "2025": ";" } # Kolom standar output STANDARD_COLUMNS = [ "kode_prodi", "nim", "nama", "tahun_lulus", "status_pekerjaan", "jenis_perusahaan", "nama_perusahaan", "jabatan", "sumber_file" ] def clean_text_value(value): """ Membersihkan nilai teks: - Hapus whitespace berlebih - Handle NaN/None """ if pd.isna(value): return "" if isinstance(value, str): # Hapus whitespace di awal dan akhir value = value.strip() # Ganti multiple spaces dengan single space value = ' '.join(value.split()) return value return str(value) def standardize_status_pekerjaan(value, tahun): """ Menstandardisasi nilai status pekerjaan: - Format berbeda antar tahun - 2019-2022: angka (1, 2, 3, 4, 5) - 2023-2025: teks ("Bekerja (full time/part time) (1)") """ if pd.isna(value) or value == "": return "unknown" value = str(value).strip().lower() # Handle format 2024-2025 if "(" in value and ")" in value: # Extract angka dalam parentheses import re match = re.search(r'\((\d+)\)', value) if match: num = int(match.group(1)) if num == 1: return "bekerja" elif num == 2: return "belum_kerja" elif num == 3: return "wiraswasta" elif num == 4: return "melanjutkan_studi" elif num == 5: return "mencari_kerja" # Handle format angka (2019-2023) if value.isdigit(): num = int(value) if num == 1: return "bekerja" elif num == 2: return "belum_kerja" elif num == 3: return "wiraswasta" elif num == 4: return "melanjutkan_studi" elif num == 5: return "mencari_kerja" # Handle teks if "bekej" in value or "work" in value or "1" in value: return "bekerja" elif "belum" in value or "tidak" in value or "2" in value: return "belum_kerja" elif "wiraswasta" in value or "3" in value: return "wiraswasta" elif "studi" in value or "melanjutkan" in value or "4" in value: return "melanjutkan_studi" elif "mencari" in value or "cari" in value or "5" in value: return "mencari_kerja" return "unknown" def read_and_map_year(tahun, files, separator): """ Baca file CSV dan terapkan column mapping. Parameters: - tahun: string tahun (misal "2019") - files: list nama file atau string tunggal - separator: separator yang digunakan Returns: - DataFrame yang sudah di-mapping """ mapping = COLUMN_MAPPING[tahun] all_dfs = [] # Pastikan files adalah list if isinstance(files, str): files = [files] for file in files: filepath = os.path.join(DATA_DIR, file) if not os.path.exists(filepath): print(f" >> [WARNING] File tidak ditemukan: {file}") continue print(f" >> Membaca: {file}") # Baca CSV dengan separator yang sesuai try: df = pd.read_csv(filepath, sep=separator, encoding='utf-8-sig') except: try: df = pd.read_csv(filepath, sep=separator, encoding='latin-1') except Exception as e: print(f" >> [ERROR] Gagal membaca {file}: {e}") continue # Buat mapping untuk rename kolom rename_mapping = {v: k for k, v in mapping.items()} # Rename kolom df_renamed = df.rename(columns=rename_mapping) # Ambil hanya kolom yang ada di mapping available_cols = [col for col in mapping.values() if col in df.columns] standard_cols_in_df = [rename_mapping[col] for col in available_cols] # Select kolom df_selected = df_renamed[standard_cols_in_df].copy() # Tambahkan sumber file df_selected['sumber_file'] = file all_dfs.append(df_selected) print(f" >> {len(df_selected)} baris") if all_dfs: return pd.concat(all_dfs, ignore_index=True) return pd.DataFrame() def clean_dataset(df): """ Cleaning dataset: - Hapus duplikat - Bersihkan nilai kosong - Standardisasi status pekerjaan """ print("\n>> Melakukan cleaning...") initial_count = len(df) # Hapus duplikat berdasarkan NIM df = df.drop_duplicates(subset=['nim'], keep='first') print(f" >> Duplikat (berdasarkan NIM) dihapus: {initial_count - len(df)}") # Hapus baris dengan NIM kosong df = df[df['nim'].notna() & (df['nim'] != '') & (df['nim'] != 'nan')] print(f" >> Baris dengan NIM kosong dihapus: {initial_count - len(df)}") # Standardisasi status pekerjaan df['status_pekerjaan'] = df['status_pekerjaan'].apply( lambda x: standardize_status_pekerjaan(x, None) ) # Clean text columns text_columns = ['nama', 'jenis_perusahaan', 'nama_perusahaan', 'jabatan'] for col in text_columns: if col in df.columns: df[col] = df[col].apply(clean_text_value) return df def generate_summary(df): """ Generate summary statistics dari merged dataset. """ summary = [] summary.append("=" * 60) summary.append("MERGE SUMMARY - TRACER STUDY ALUMNI") summary.append(f"Tanggal: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") summary.append("=" * 60) summary.append(f"\n>> TOTAL DATA: {len(df)} alumni") summary.append("\n>> Distribusi per Tahun Lulus:") tahun_counts = df['tahun_lulus'].value_counts().sort_index() for tahun, count in tahun_counts.items(): summary.append(f" {tahun}: {count} alumni") summary.append("\n>> Distribusi per Prodi (Kode):") prodi_counts = df['kode_prodi'].value_counts() for prodi, count in prodi_counts.items(): summary.append(f" {prodi}: {count} alumni") summary.append("\n>> Distribusi Status Pekerjaan:") status_counts = df['status_pekerjaan'].value_counts() for status, count in status_counts.items(): pct = (count / len(df)) * 100 summary.append(f" {status}: {count} ({pct:.1f}%)") summary.append("\n>> Sumber File:") sumber_counts = df['sumber_file'].value_counts() for sumber, count in sumber_counts.items(): summary.append(f" {sumber}: {count} alumni") summary.append("\n>> Missing Values:") for col in df.columns: missing = df[col].isna().sum() + (df[col] == '').sum() if missing > 0: pct = (missing / len(df)) * 100 summary.append(f" {col}: {missing} ({pct:.1f}%)") summary.append("\n" + "=" * 60) return "\n".join(summary) def print_msg(msg): """Print message dengan encoding handling.""" print(msg) def main(): """ Main function untuk merge semua dataset. """ print("=" * 60) print("MERGE DATASET TRACER STUDY (2019-2025)") print("=" * 60) all_dataframes = [] # Proses setiap tahun for tahun in ["2019", "2020", "2021", "2022", "2023", "2024", "2025"]: print(f"\n>> Memproses Tahun {tahun}...") files = DATA_FILES[tahun] separator = SEPARATORS[tahun] df = read_and_map_year(tahun, files, separator) if not df.empty: all_dataframes.append(df) print(f" >> Total dari {tahun}: {len(df)} baris") else: print(f" >> Tidak ada data untuk {tahun}") if not all_dataframes: print("\n[ERROR] Tidak ada data yang berhasil dibaca!") return # Merge semua data print("\n>> Merging semua dataset...") unified_df = pd.concat(all_dataframes, ignore_index=True) print(f" >> Total setelah merge: {len(unified_df)} baris") # Cleaning unified_df = clean_dataset(unified_df) # Generate summary summary = generate_summary(unified_df) print("\n" + summary) # Ensure output directory exists os.makedirs(OUTPUT_DIR, exist_ok=True) # Save unified dataset output_path = os.path.join(OUTPUT_DIR, 'unified_dataset.csv') unified_df.to_csv(output_path, index=False, encoding='utf-8-sig') print(f"\n>> Dataset disimpan ke: {output_path}") # Save summary summary_path = os.path.join(OUTPUT_DIR, 'merge_summary.txt') with open(summary_path, 'w', encoding='utf-8') as f: f.write(summary) print(f">> Summary disimpan ke: {summary_path}") print("\n" + "=" * 60) print("[SUCCESS] MERGE SELESAI!") print("=" * 60) return unified_df if __name__ == "__main__": df = main()