27 lines
1.1 KiB
Python
27 lines
1.1 KiB
Python
import sys
|
|
sys.path.append('Backend')
|
|
from app.config.database import engine
|
|
from sqlalchemy import text
|
|
from app.parsers.converters import konversi_jawaban
|
|
import pandas as pd
|
|
|
|
# Check in DB
|
|
with engine.connect() as conn:
|
|
rows = conn.execute(text("""
|
|
SELECT a.id, a.nim, a.tahun_lulus, tr.rata_pendapatan
|
|
FROM alumni a
|
|
JOIN tracer_responses tr ON a.id = tr.alumni_id
|
|
WHERE a.tahun_lulus IN (2020, 2021, 2022);
|
|
""")).fetchall()
|
|
|
|
print(f"Total respondents in 2020-2022: {len(rows)}")
|
|
null_income = [r for r in rows if r[3] is None]
|
|
print(f"Currently NULL income in DB: {len(null_income)}")
|
|
|
|
# Let's see how many of those NULLs have income in the CSVs:
|
|
for yr in ['2020', '2021', '2022']:
|
|
df = pd.read_csv(f'Data_Tracerstudy/Data_{yr}_Tracer_study.csv', sep=';', encoding='utf-8-sig').dropna(how='all')
|
|
col_f505 = [c for c in df.columns if '505' in c.lower()][0]
|
|
non_null_raw = df[df[col_f505].notna() & (df[col_f505].astype(str).str.strip().isin(['', '0', '0.0']) == False)]
|
|
print(f"Year {yr}: {len(non_null_raw)} rows have valid income in CSV")
|