77 lines
3.5 KiB
Python
77 lines
3.5 KiB
Python
import pandas as pd
|
|
import glob
|
|
import sys
|
|
sys.path.append('Backend')
|
|
from app.parsers.column_finder import find_column, get_value
|
|
from app.parsers.mappings import (
|
|
YEAR_CONFIG, IDENTITAS_MAPPING, PEKERJAAN_MAPPING, STUDI_LANJUT_MAPPING,
|
|
KESESUAIAN_MAPPING, KOMPETENSI_LULUS_MAPPING, KOMPETENSI_SAAT_INI_MAPPING,
|
|
METODE_PEMBELAJARAN_MAPPING, WAKTU_CARI_KERJA_MAPPING, CARA_CARI_KERJA_MAPPING,
|
|
LAMARAN_KEAKTIFAN_MAPPING, KESESUAIAN_PEKERJAAN_MAPPING, get_field_code
|
|
)
|
|
from app.parsers.converters import parse_2024_value, parse_date
|
|
|
|
# Inspect max length for every column mapped to tracer_responses
|
|
all_mapping = {}
|
|
all_mapping.update(IDENTITAS_MAPPING)
|
|
all_mapping.update(PEKERJAAN_MAPPING)
|
|
all_mapping.update(STUDI_LANJUT_MAPPING)
|
|
all_mapping.update(KESESUAIAN_MAPPING)
|
|
all_mapping.update(KOMPETENSI_LULUS_MAPPING)
|
|
all_mapping.update(KOMPETENSI_SAAT_INI_MAPPING)
|
|
all_mapping.update(METODE_PEMBELAJARAN_MAPPING)
|
|
all_mapping.update(WAKTU_CARI_KERJA_MAPPING)
|
|
all_mapping.update(CARA_CARI_KERJA_MAPPING)
|
|
all_mapping.update(LAMARAN_KEAKTIFAN_MAPPING)
|
|
all_mapping.update(KESESUAIAN_PEKERJAAN_MAPPING)
|
|
|
|
max_lengths = {}
|
|
problem_values = []
|
|
|
|
for fpath in sorted(glob.glob('Data_Tracerstudy/*2025*.csv')):
|
|
df = pd.read_csv(fpath, sep=';', encoding='utf-8-sig')
|
|
source_cols = list(df.columns)
|
|
for idx, row in df.iterrows():
|
|
nim = get_value(row, source_cols, IDENTITAS_MAPPING['nim'], '')
|
|
if not nim or str(nim).strip() in ['', 'nan']:
|
|
continue
|
|
for field, cols in all_mapping.items():
|
|
if field in ['nim', 'nama', 'tahun_lulus', 'kode_prodi']:
|
|
continue
|
|
val = get_value(row, source_cols, cols)
|
|
if val is not None and not pd.isna(val):
|
|
kode, teks = parse_2024_value(val)
|
|
final_val = teks if teks else (str(kode) if kode is not None else str(val).strip())
|
|
val_len = len(str(final_val))
|
|
if field not in max_lengths or val_len > max_lengths[field]:
|
|
max_lengths[field] = val_len
|
|
|
|
# Check known DB column limits
|
|
# jumlah_perusahaan: VARCHAR(50)
|
|
if field == 'jumlah_perusahaan' and val_len > 50:
|
|
problem_values.append((fpath, idx, field, val_len, final_val))
|
|
# dapet_kerja_6_bulan: VARCHAR(50)
|
|
if field == 'dapet_kerja_6_bulan' and val_len > 50:
|
|
problem_values.append((fpath, idx, field, val_len, final_val))
|
|
# tingkatan_tempat_kerja: VARCHAR(50)
|
|
if field == 'tingkatan_tempat_kerja' and val_len > 50:
|
|
problem_values.append((fpath, idx, field, val_len, final_val))
|
|
# status_saat_ini: VARCHAR(50) or 255
|
|
if field == 'status_saat_ini' and val_len > 50:
|
|
problem_values.append((fpath, idx, field, val_len, final_val))
|
|
# tanggal_masuk_studi
|
|
if field == 'tanggal_masuk_studi':
|
|
parsed = parse_date(val)
|
|
# check if valid date
|
|
if val and not parsed:
|
|
problem_values.append((fpath, idx, field, 'INVALID_DATE', val))
|
|
|
|
print("=== POTENTIAL DB COLUMN OVERFLOWS IN 2025 ===")
|
|
for p in problem_values:
|
|
print(f"File: {p[0].split('/')[-1].split(chr(92))[-1]}, Row {p[1]}, Field {p[2]}, Len/Status: {p[3]}")
|
|
print(f" Value: {repr(p[4])[:100]}")
|
|
|
|
print("\n=== TOP MAX LENGTHS IN 2025 ===")
|
|
for f, l in sorted(max_lengths.items(), key=lambda x: x[1], reverse=True)[:15]:
|
|
print(f" {f}: max length {l}")
|