427 lines
13 KiB
Python
427 lines
13 KiB
Python
"""
|
|
=============================================================
|
|
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()
|