240 lines
10 KiB
Python
240 lines
10 KiB
Python
import pandas as pd
|
|
|
|
# Fungsi dari import_data.py
|
|
def normalize(col_name):
|
|
if pd.isna(col_name):
|
|
return ""
|
|
return str(col_name).strip().lower()
|
|
|
|
def find_column(source_cols, possible_names):
|
|
source_cols_lower = {normalize(c): c for c in source_cols}
|
|
for name in possible_names:
|
|
if normalize(name) in source_cols_lower:
|
|
return source_cols_lower[normalize(name)]
|
|
return None
|
|
|
|
# YEAR_CONFIG
|
|
YEAR_CONFIG = {
|
|
'2019': {
|
|
'skip_jumlah_perusahaan': True,
|
|
'has_email': True,
|
|
'email_col': 'Email',
|
|
'nama_col': 'Nama',
|
|
'tahun_col': 'Tahun Lulus',
|
|
},
|
|
}
|
|
|
|
# tracer_mapping
|
|
tracer_mapping = {
|
|
'email': ['emailmsmh', 'Email', '6. Alamat Email / emailmsmh'],
|
|
'kode_pt': ['kdptimsmh', 'Kode PT', '1. Kode Perguruan Tinggi / kdptimsmh'],
|
|
'nik': ['NIK'],
|
|
'npwp': ['NPWP'],
|
|
'telepon': ['Telepon', 'No. Telepon'],
|
|
'status_saat_ini': ['F8', 'f8'],
|
|
'bulan_dapat_kerja': ['F502', 'f502'],
|
|
'dapet_kerja_6_bulan': ['F504', 'f504'],
|
|
'rata_pendapatan': ['F505', 'f505'],
|
|
'bulan_dapat_kerja_setelah': ['F506', 'f506'],
|
|
'provinsi_kerja': ['F5a1', 'f5a1'],
|
|
'kab_kota_kerja': ['F5a2', 'f5a2'],
|
|
'jenis_perusahaan_full': ['F1101', 'f1101'],
|
|
'jumlah_perusahaan': ['F1102', 'f1102'],
|
|
'nama_perusahaan': ['F5b', 'f5b'],
|
|
'jabatan': ['F5c', 'f5c'],
|
|
'tingkatan_tempat_kerja': ['F5d', 'f5d'],
|
|
'sumber_biaya_studi': ['F18a', 'f18a'],
|
|
'pt_studi_lanjut': ['F18b', 'f18b'],
|
|
'prodi_studi_lanjut': ['F18c', 'f18c'],
|
|
'tanggal_masuk_studi': ['F18d', 'f18d'],
|
|
'sumber_biaya_kuliah': ['F1201', 'f1201'],
|
|
'keeratan_bidang_studi': ['F14', 'f14'],
|
|
'tingkat_pendidikan_tepat': ['F15', 'f15'],
|
|
'etika_lulus_full': ['F1761', 'f1761'],
|
|
'etika_saat_ini': ['F1762', 'f1762'],
|
|
'keahlian_lulus_full': ['F1763', 'f1763'],
|
|
'keahlian_saat_ini': ['F1764', 'f1764'],
|
|
'bahasa_inggris_lulus_full': ['F1765', 'f1765'],
|
|
'bahasa_inggris_saat_ini': ['F1766', 'f1766'],
|
|
'teknologi_informasi_lulus_full': ['F1767', 'f1767'],
|
|
'teknologi_informasi_saat_ini': ['F1768', 'f1768'],
|
|
'komunikasi_lulus_full': ['F1769', 'f1769'],
|
|
'komunikasi_saat_ini': ['F1770', 'f1770'],
|
|
'kerja_sama_lulus_full': ['F1771', 'f1771'],
|
|
'kerja_sama_saat_ini': ['F1772', 'f1772'],
|
|
'pengembangan_diri_lulus_full': ['F1773', 'f1773'],
|
|
'pengembangan_diri_saat_ini': ['F1774', 'f1774'],
|
|
'metode_perkuliahan': ['F21', 'f21'],
|
|
'metode_demonstrasi': ['F22', 'f22'],
|
|
'metode_proyek_riset': ['F23', 'f23'],
|
|
'metode_magang': ['F24', 'f24'],
|
|
'metode_praktikum': ['F25', 'f25'],
|
|
'metode_kerja_lapangan': ['F26', 'f26'],
|
|
'metode_diskusi': ['F27', 'f27'],
|
|
'mulai_cari_kerja': ['F301', 'f301'],
|
|
'bulan_cari_sebelum_lulus': ['F302', 'f302'],
|
|
'bulan_cari_setelah_wisuda': ['F303', 'f303'],
|
|
'cari_iklan': ['F401', 'f401'],
|
|
'cari_melamar_tanpa_lowongan': ['F402', 'f402'],
|
|
'cari_bursa_pameran': ['F403', 'f403'],
|
|
'cari_internet': ['F404', 'f404'],
|
|
'cari_dihubungi_perusahaan': ['F405', 'f405'],
|
|
'cari_kemenakertrans': ['F406', 'f406'],
|
|
'cari_agen_tenaga_kerja': ['F407', 'f407'],
|
|
'cari_pusat_karir': ['F408', 'f408'],
|
|
'cari_kantor_mahasiswa': ['F409', 'f409'],
|
|
'cari_jejaring': ['F410', 'f410'],
|
|
'cari_relasi': ['F411', 'f411'],
|
|
'cari_bangun_usaha': ['F412', 'f412'],
|
|
'cari_magang': ['F413', 'f413'],
|
|
'cari_tempat_kuliah': ['F414', 'f414'],
|
|
'cari_lainnya': ['F415', 'f415'],
|
|
'jumlah_perusahaan_dilamar': ['F6', 'f6'],
|
|
'jumlah_perusahaan_merespons': ['F7', 'f7'],
|
|
'jumlah_perusahaan_wawancara': ['F7a', 'f7a'],
|
|
'aktif_cari_kerja_4_minggu': ['F1001', 'f1001'],
|
|
'kerja_tidak_sesuai_1': ['F1601', 'f1601'],
|
|
'kerja_tidak_sesuai_2': ['F1602', 'f1602'],
|
|
'alasan_tidak_sesuai_1': ['F1603', 'f1603'],
|
|
'alasan_tidak_sesuai_2': ['F1604', 'f1604'],
|
|
'alasan_tidak_sesuai_3': ['F1605', 'f1605'],
|
|
'alasan_tidak_sesuai_4': ['F1606', 'f1606'],
|
|
'alasan_tidak_sesuai_5': ['F1607', 'f1607'],
|
|
'alasan_tidak_sesuai_6': ['F1608', 'f1608'],
|
|
'alasan_tidak_sesuai_7': ['F1609', 'f1609'],
|
|
'alasan_tidak_sesuai_8': ['F1610', 'f1610'],
|
|
'alasan_tidak_sesuai_9': ['F1611', 'f1611'],
|
|
'alasan_tidak_sesuai_10': ['F1612', 'f1612'],
|
|
'alasan_tidak_sesuai_11': ['F1613', 'f1613'],
|
|
'alasan_tidak_sesuai_lainnya': ['F1614', 'f1614']
|
|
}
|
|
|
|
# Apply year config
|
|
tahun = '2019'
|
|
year_config = YEAR_CONFIG.get(tahun, {})
|
|
if year_config.get('skip_jumlah_perusahaan', False):
|
|
if 'jumlah_perusahaan' in tracer_mapping:
|
|
del tracer_mapping['jumlah_perusahaan']
|
|
|
|
# Konversi jawaban
|
|
JAWABAN_MAPPING = {
|
|
'F8': {1: 'Bekerja (full time/part time)', 2: 'Belum memungkinkan bekerja', 3: 'Wiraswasta', 4: 'Melanjutkan Pendidikan', 5: 'Tidak kerja tetapi sedang mencari kerja'},
|
|
'F505': {1000000: 'Rp 1.000.000', 1500000: 'Rp 1.500.000', 2000000: 'Rp 2.000.000', 2500000: 'Rp 2.500.000', 3000000: 'Rp 3.000.000'},
|
|
'F1101': {1: 'Instansi pemerintah', 2: 'Organisasi non-profit/Lembaga Swadaya Masyarakat', 3: 'Perusahaan swasta', 4: 'Wiraswasta/perusahaan sendiri', 5: 'Lainnya', 6: 'BUMN/BUMD', 7: 'Institusi/Organisasi Multilateral'},
|
|
'F5d': {1: 'Lokal/wilayah/wiraswasta tidak berbadan hukum', 2: 'Nasional/wiraswasta berbadan hukum', 3: 'Multinasional/Internasional'},
|
|
'F5c': {1: 'Founder', 2: 'Co-Founder', 3: 'Staff', 4: 'Freelance/Kerja Lepas'},
|
|
'F18a': {1: 'Biaya Sendiri', 2: 'Bea Siswa'},
|
|
'F1201': {1: 'Biaya Sendiri/Keluarga', 2: 'Beasiswa ADIK', 3: 'Beasiswa BIDIKMISI', 4: 'Beasiswa PPA', 5: 'Beasiswa AFIRMASI', 6: 'Beasiswa Perusahaan/Swasta', 7: 'Lainnya'},
|
|
'F14': {1: 'Sangat Erat', 2: 'Erat', 3: 'Cukup Erat', 4: 'Kurang Erat', 5: 'Tidak sama sekali'},
|
|
'F15': {1: 'Setingkat lebih tinggi', 2: 'Tingkat yang sama', 3: 'Setingkat lebih rendah', 4: 'Tidak perlu pendidikan tinggi'},
|
|
'F301': {1: 'Saya mencari kerja sebelum lulus', 2: 'Saya mencari kerja sesudah wisuda', 3: 'Saya tidak mencari kerja'},
|
|
'F1001': {1: 'Tidak', 2: 'Tidak, tapi saya sedang menunggu hasil lamaran kerja', 3: 'Ya, saya akan mulai bekerja dalam 2 minggu ke depan', 4: 'Ya, tapi saya belum pasti akan bekerja dalam 2 minggu ke depan', 5: 'Lainnya'},
|
|
'F1601': {0: 'Tidak sesuai', 1: 'Pekerjaan saya sekarang sudah sesuai dengan pendidikan saya'},
|
|
'F1602': {0: 'Pekerjaan saya sudah sesuai', 1: 'Saya belum mendapatkan pekerjaan yang lebih sesuai'},
|
|
}
|
|
|
|
KOMPETENSI_LEVEL = {1: 'Sangat Rendah', 2: 'Rendah', 3: 'Netral', 4: 'Tinggi', 5: 'Sangat Tinggi'}
|
|
METODE_LEVEL = {1: 'Sangat Besar', 2: 'Besar', 3: 'Cukup Besar', 4: 'Kurang', 5: 'Tidak Sama Sekali'}
|
|
YA_TIDAK = {0: 'Tidak', 1: 'Ya'}
|
|
|
|
def konversi_jawaban(field_code, nilai):
|
|
if pd.isna(nilai) or nilai == 0 or nilai == '':
|
|
return None
|
|
if isinstance(nilai, float):
|
|
if nilai != int(nilai):
|
|
return None
|
|
nilai = int(nilai)
|
|
nilai_key = nilai
|
|
if isinstance(nilai, (int, float)):
|
|
nilai_key = int(nilai)
|
|
if field_code in ['F1761', 'F1762', 'F1763', 'F1764', 'F1765', 'F1766', 'F1767', 'F1768', 'F1769', 'F1770', 'F1771', 'F1772', 'F1773', 'F1774']:
|
|
return KOMPETENSI_LEVEL.get(nilai_key)
|
|
if field_code in ['F21', 'F22', 'F23', 'F24', 'F25', 'F26', 'F27']:
|
|
return METODE_LEVEL.get(nilai_key)
|
|
if field_code in ['F401', 'F402', 'F403', 'F404', 'F405', 'F406', 'F407', 'F408', 'F409', 'F410', 'F411', 'F412', 'F413', 'F414', 'F415', 'F416']:
|
|
return YA_TIDAK.get(nilai_key)
|
|
if field_code in ['F1603', 'F1604', 'F1605', 'F1606', 'F1607', 'F1608', 'F1609', 'F1610', 'F1611', 'F1612', 'F1613']:
|
|
return YA_TIDAK.get(nilai_key)
|
|
if field_code in JAWABAN_MAPPING:
|
|
return JAWABAN_MAPPING[field_code].get(nilai_key)
|
|
return nilai
|
|
|
|
# Baca CSV
|
|
df = pd.read_csv(r'c:\Users\syifa\TA-PROJEK\Data_Tracerstudy\Data_2019_Tracer_study.csv', sep=';', encoding='utf-8-sig')
|
|
source_cols = list(df.columns)
|
|
row = df.iloc[0]
|
|
|
|
def get_val(mapping_dict, key, default=None):
|
|
cols = mapping_dict.get(key, [])
|
|
col = find_column(source_cols, cols)
|
|
if col and pd.notna(row.get(col)):
|
|
val = row[col]
|
|
if isinstance(val, (int, float)):
|
|
return int(val) if val == int(val) else val
|
|
return str(val).strip()
|
|
return default
|
|
|
|
# Simulasi build tracer_data
|
|
print('=== SIMULASI BUILD TRACER_DATA (2019) ===')
|
|
print()
|
|
|
|
tracer_data = {}
|
|
numeric_fields = ['bulan_dapat_kerja', 'dapet_kerja_6_bulan', 'bulan_dapat_kerja_setelah',
|
|
'jumlah_perusahaan', 'bulan_cari_sebelum_lulus', 'bulan_cari_setelah_wisuda',
|
|
'jumlah_perusahaan_dilamar', 'jumlah_perusahaan_merespons', 'jumlah_perusahaan_wawancara']
|
|
|
|
target_fields = ['nama_perusahaan', 'jabatan', 'jenis_perusahaan_full', 'status_saat_ini', 'email']
|
|
|
|
for field, cols in tracer_mapping.items():
|
|
if field not in target_fields:
|
|
continue
|
|
|
|
print(f'Processing: {field}')
|
|
print(f' cols: {cols}')
|
|
|
|
val = get_val(tracer_mapping, field)
|
|
print(f' val dari get_val: {repr(val)}')
|
|
|
|
if val is None:
|
|
print(f' -> SKIP: val is None')
|
|
print()
|
|
continue
|
|
|
|
if isinstance(val, str) and val.strip() in ['', 'nan', 'None', '-', '_']:
|
|
print(f' -> SKIP: invalid string "{val}"')
|
|
print()
|
|
continue
|
|
|
|
field_code = None
|
|
if cols:
|
|
for col in cols:
|
|
if str(col).upper().startswith('F'):
|
|
field_code = str(col).upper()
|
|
break
|
|
|
|
print(f' field_code: {field_code}')
|
|
|
|
converted_val = konversi_jawaban(field_code, val)
|
|
print(f' converted_val: {repr(converted_val)}')
|
|
|
|
if field in numeric_fields:
|
|
try:
|
|
tracer_data[field] = int(val)
|
|
print(f' -> SAVED as numeric: {int(val)}')
|
|
except (ValueError, TypeError) as e:
|
|
print(f' -> SKIP: cannot convert to int - {e}')
|
|
elif converted_val is not None:
|
|
tracer_data[field] = converted_val
|
|
print(f' -> SAVED: {repr(converted_val)}')
|
|
else:
|
|
print(f' -> SKIP: converted_val is None')
|
|
|
|
print()
|
|
|
|
print('=== HASIL AKHIR ===')
|
|
for field in target_fields:
|
|
if field in tracer_data:
|
|
print(f'{field}: {repr(tracer_data[field])}')
|
|
else:
|
|
print(f'{field}: NOT SAVED')
|