VoT MMR Data
Cleaning & Analysis

  Kobo Excel (.xlsx)
         │
         ▼
   ┌──────────────┐
   │ 1–2 Ingest   │  in_df  (raw rows, chosen columns)
   │   + subset   │
   └──────┬───────┘
          ▼
   ┌──────────────┐
   │ 3 Clean      │  vot_df / df  (no junk columns, deduped)
   └──────┬───────┘
          ▼
   ┌──────────────┐
   │ 4 Map        │  df_mapped → df_mapped_2  (labels, not codes)
   └──────┬───────┘
          ▼
   ┌──────────────┐
   │ 5 Analyse    │  12 modules → files in _VoT/tables/
   └──────┬───────┘
          ▼
   ┌──────────────┐
   │ 6 Export     │  dated workbook in _VoT/output/
   └──────────────┘

1. Ingest  — Read Excel (KoboToolbox export), normalise column names to lowercase
2. Subset  — Keep screening columns listed in processing_cols (drop the rest for this pass)
3. Clean   — Rename group paths → short names, drop system/metadata columns, missing-value report, deduplicate
4. Map     — Binary 0/1 → Yes/No; country codes → nationality labels; optional filters (e.g. African nationals)
5. Analyse — Run the 12 modules in sequence; each writes tables under _VoT/tables/
6. Export  — Save dated cleaned workbook to _VoT/output/; copy to Working/ if needed

# Run inside a Jupyter cell (note the ! prefix for shell commands)
!pip3 install gdown --quiet
!pip3 install -U scikit-learn scipy matplotlib --quiet
!pip3 install seaborn --quiet
!pip3 install openpyxl --quiet

import pandas as pd
import numpy as np
import seaborn as sbn
import matplotlib.pyplot as plt
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter
from openpyxl import load_workbook
import os, re
from pathlib import Path
from datetime import datetime
from collections import Counter

def lowerCaseContent(df):
    """Lowercase all string values in object columns."""
    for col in df.select_dtypes(['object']):
        df[col] = df[col].str.lower().str.strip()
    return df

def integerToFloat(df):
    """Convert integer columns to float for consistent numeric handling."""
    for col in df.select_dtypes(['int64']):
        df[col] = df[col].astype(float)
    return df

def getListOfColsIsNaN(df):
    """Return columns with missing values sorted by count."""
    missing = df.isnull().sum()
    return missing[missing > 0].sort_values(ascending=False)

def returnUniqueValues(df):
    """Print unique values for all categorical columns."""
    for col in df.select_dtypes(['object']):
        print(f"\n=== {col} ===")
        print(df[col].unique())

# Input — raw KoboToolbox export
infile    = "_VoT/input/VoT_MMR_Raw.xlsx"
out_dir   = "_VoT/output/"
table_dir = "_VoT/tables/"

# Create output folders if they don't exist yet
os.makedirs(out_dir,   exist_ok=True)
os.makedirs(table_dir, exist_ok=True)

# Read specific sheet exported from KoboToolbox
in_df = pd.read_excel(infile, sheet_name="data_pxd_25")

# Normalize all column names to lowercase
in_df.columns = [col.lower() for col in in_df.columns]

# Quick sanity check
print(f"Dataset: {in_df.shape[0]} rows × {in_df.shape[1]} columns")
in_df.head(2)

processing_cols = [
    'interviewer_name', 'interviewer_org', 'interview_date',
    'gender', 'age', 'citizenship', 'education_level',
    'passport_owned', 'visa_type', 'travel_route', 'airline_used',
    'recruitment_contact', 'expected_work_type', 'exploitation_type',
    'scam_type', 'means_of_control', 'ransom_amount',
    # Add or remove columns based on your Form v6 export
]

in_df = in_df[[c for c in processing_cols if c in in_df.columns]]

# key = KoboToolbox column name (already lowercased)
# value = analysis-friendly name
rename_dict = {
    'group_demographics/q_gender':     'gender',
    'group_demographics/q_age':        'age',
    'group_demographics/q_citizenship': 'citizenship',
    'group_travel/q_passport':         'passport_owned',
    'group_travel/q_visa':             'visa_type',
    # extend with all relevant columns from your form
}

in_df.rename(
    columns={k: v for k, v in rename_dict.items() if k in in_df.columns},
    inplace=True
)

cols_to_drop = [
    'survey_start_time', 'survey_end_time', 'record_id',
    'record_uuid', 'submission_time', 'validation_status',
    'system_notes', 'record_status', 'submitted_by',
    'form_version', 'record_tags',
]

vot_df = in_df.drop(
    columns=[c for c in cols_to_drop if c in in_df.columns]
)

total   = in_df.isnull().sum().sort_values(ascending=False)
pct     = (total / len(in_df) * 100).round(2)
missing = pd.concat([total, pct], axis=1, keys=['Missing Count', 'Missing %'])
missing = missing[missing['Missing Count'] > 0]

with pd.ExcelWriter(
    "_VoT/tables/VoT_MMR_MissingValues.xlsx", engine='openpyxl'
) as writer:
    missing.to_excel(writer, sheet_name='Missing Values')

print(missing)

n_dup = in_df.duplicated().sum()
print(f"Duplicate rows: {n_dup}")

if n_dup > 0:
    in_df = in_df.drop_duplicates()
    print(f"After dedup: {in_df.shape[0]} rows")

def apply_individual_mappings_notnull(df):
    """Map 0/1 binary columns to Yes/No — only on non-null rows."""
    yes_no_cols = [
        'passport_owned', 'visa_obtained', 'consent_given',
        'debt_bondage', 'document_confiscated', 'physical_abuse',
        # add all binary columns from your Form v6
    ]
    binary_map = {0: 'No', 1: 'Yes', 0.0: 'No', 1.0: 'Yes'}

    for col in yes_no_cols:
        if col in df.columns:
            mask = df[col].notna()
            df.loc[mask, col] = df.loc[mask, col].map(binary_map)
    return df

df = apply_individual_mappings_notnull(in_df)

def apply_nationality_mappings(df_mapped):
    nationality_map = {
        'mmr': 'Myanmar', 'myanmar': 'Myanmar',
        'chn': 'China',   'tha': 'Thailand',
        'khm': 'Cambodia', 'lao': 'Laos',
        'vnm': 'Vietnam',
        # extend for all nationalities in your dataset
    }
    if 'citizenship' in df_mapped.columns:
        df_mapped['citizenship'] = (
            df_mapped['citizenship'].str.lower().str.strip()
            .map(nationality_map)
            .fillna(df_mapped['citizenship'])
        )
    return df_mapped

df_mapped_2 = apply_nationality_mappings(df)

# UN official list of 54 African countries
african_countries = [
    'Algeria', 'Angola', 'Benin', 'Botswana', 'Burkina Faso',
    'Burundi', 'Cameroon', 'Central African Republic', 'Chad',
    # ... all 54 (add the full list)
]

df_african = df_mapped_2[df_mapped_2['citizenship'].isin(african_countries)]
print(f"African nationals: {len(df_african)}")

outfile = f"_VoT/output/VoT_MMR_Cleaned_{datetime.today().strftime('%Y%m%d')}.xlsx"

with pd.ExcelWriter(outfile, engine='openpyxl') as writer:
    df_mapped_2.to_excel(writer, sheet_name='Cleaned_Data', index=False)

# Apply header formatting
wb = load_workbook(outfile)
ws = wb['Cleaned_Data']
header_fill = PatternFill(start_color="1F4E79", fill_type="solid")

for cell in ws[1]:
    cell.font      = Font(bold=True, color="FFFFFF")
    cell.fill      = header_fill
    cell.alignment = Alignment(horizontal="center")
    ws.column_dimensions[cell.column_letter].width = 18

wb.save(outfile)
print(f"Saved → {outfile}")

import shutil

def copy_files(source_folder, dest_folder, extension=".xlsx"):
    os.makedirs(dest_folder, exist_ok=True)
    copied = 0
    for f in Path(source_folder).glob(f"*{extension}"):
        shutil.copy2(f, dest_folder)
        copied += 1
    print(f"Copied {copied} file(s) → {dest_folder}")

copy_files(source_folder="_VoT/output/", dest_folder="Working/")

import os
print("Exists:", os.path.exists(infile))

print(sorted(in_df.columns.tolist()))