📐 Module 06: The Data Analytics Process

         ┌─────────────────────────────────────┐
         │          Business Understanding      │
         │                  ↓                  │
         │          Data Understanding          │
         │                  ↓                  │
         │          Data Preparation            │◄──┐
         │                  ↓                  │   │ Loop back
         │             Modelling               │   │ when needed
         │                  ↓                  │   │
         │             Evaluation              │───┘
         │                  ↓                  │
         │            Deployment               │
         └─────────────────────────────────────┘
                            ↑
                    And then repeat
                  (new question emerges)

╔══════════════════════════════════════════════════════════════════════════════╗
║  Phase 1: BUSINESS UNDERSTANDING                                            ║
║  What are we actually trying to solve?                                      ║
║  • Define the business objective                                            ║
║  • Set success criteria (quantifiable KPIs)                                ║
║  • Identify data requirements                                               ║
╠══════════════════════════════════════════════════════════════════════════════╣
║  Phase 2: DATA UNDERSTANDING                                                ║
║  What data do we have, and is it trustworthy?                               ║
║  • Collect and inventory data sources                                       ║
║  • Profile: shape, types, completeness                                      ║
║  • Identify quality issues                                                  ║
║  • Explore distributions, correlations, outliers                           ║
╠══════════════════════════════════════════════════════════════════════════════╣
║  Phase 3: DATA PREPARATION                                                  ║
║  Transform raw data into analysis-ready data.                               ║
║  • Select relevant columns/rows                                             ║
║  • Handle missing values, duplicates, outliers                             ║
║  • Engineer new features (ratios, flags, date components)                  ║
║  • Encode categoricals, scale numerics                                     ║
╠══════════════════════════════════════════════════════════════════════════════╣
║  Phase 4: MODELLING                                                         ║
║  Apply statistical or ML techniques to the prepared data.                  ║
║  • Select model type (regression, classification, clustering…)             ║
║  • Split data (train/test)                                                  ║
║  • Fit the model                                                            ║
║  • Tune parameters                                                          ║
╠══════════════════════════════════════════════════════════════════════════════╣
║  Phase 5: EVALUATION                                                        ║
║  Does the model actually solve the business problem?                        ║
║  • Measure model performance (RMSE, accuracy, AUC…)                        ║
║  • Check against business success criteria from Phase 1                    ║
║  • Identify limitations; decide whether to proceed                         ║
╠══════════════════════════════════════════════════════════════════════════════╣
║  Phase 6: DEPLOYMENT                                                        ║
║  Turn the insight into action.                                              ║
║  • Automate the analysis (scheduled script)                                 ║
║  • Build a report / dashboard                                               ║
║  • Document findings for audit trail                                        ║
║  • Plan for model monitoring and refresh                                   ║
╚══════════════════════════════════════════════════════════════════════════════╝

df.shape           # rows × columns
df.info()          # column types, non-null counts
df.describe()      # min, max, mean, std, quartiles
df.isnull().sum()  # missing value count per column
df.duplicated().sum()  # duplicate row count
df['col'].value_counts()   # frequency of each category
df.corr()          # correlation matrix

Prepared Data
      │
      ▼
 Train/Test Split   ← Divide data: 80% train, 20% test
      │                (NEVER evaluate on training data)
      ▼
 Feature Selection  ← Which columns to include as inputs?
      │
      ▼
 Model Training     ← Fit the model on training data
      │
      ▼
 Hyperparameter     ← Tune the model's settings
    Tuning               (cross-validation)
      │
      ▼
 Prediction on      ← Generate predictions on unseen test data
   Test Set

# Setup — import all libraries needed for this module
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime, timedelta

%matplotlib inline
plt.rcParams['figure.dpi'] = 100
sns.set_theme(style='whitegrid', palette='deep')

print('Libraries ready.')
print(f'Analysis date: {datetime.today().strftime("%d %B %Y")}')

Libraries ready.
Analysis date: 28 April 2026

# ── Build a realistic invoice / payment dataset ──────────────────────────────
# Simulating 2 years of invoice history for 320 clients (800 invoices)

np.random.seed(42)
n = 800

industries = ['Manufacturing', 'IT Services', 'Trading', 'Healthcare',
              'Real Estate', 'Retail', 'Construction', 'Finance']
ind_weights = [0.20, 0.18, 0.15, 0.12, 0.10, 0.10, 0.08, 0.07]

client_ids = [f'CL{str(i).zfill(4)}' for i in np.random.randint(1, 321, n)]
industries_col = np.random.choice(industries, n, p=ind_weights)

# Simulate invoice amounts by industry (construction & real estate tend to be larger)
base_amounts = {'Manufacturing': 250000, 'IT Services': 180000, 'Trading': 120000,
                'Healthcare': 200000, 'Real Estate': 450000, 'Retail': 80000,
                'Construction': 380000, 'Finance': 150000}
amounts = np.array([base_amounts[ind] * np.random.uniform(0.4, 3.0) for ind in industries_col])
amounts = amounts.round(-3).astype(int)

# Invoice dates spread over 2 years
start_date = pd.Timestamp('2023-04-01')
invoice_dates = pd.to_datetime([
    start_date + timedelta(days=int(d)) for d in np.random.uniform(0, 730, n)
])
due_dates = invoice_dates + pd.to_timedelta(np.random.choice([30, 45, 60], n), unit='D')

# Payment behaviour — industry affects delay probability
delay_prob = {'Manufacturing': 0.30, 'IT Services': 0.20, 'Trading': 0.35,
              'Healthcare': 0.18, 'Real Estate': 0.45, 'Retail': 0.38,
              'Construction': 0.50, 'Finance': 0.15}
is_late = np.array([np.random.random() < delay_prob[ind] for ind in industries_col])

# Actual payment dates
normal_delay = np.random.uniform(0, 15, n).astype(int)
late_delay   = np.random.uniform(61, 180, n).astype(int)
payment_days = np.where(is_late, late_delay, normal_delay)
payment_dates = due_dates + pd.to_timedelta(payment_days, unit='D')

# Client tenure and credit limit
tenure_years = np.random.uniform(0.5, 8.0, n).round(1)
credit_limit = np.array([
    amounts[i] * np.random.uniform(1.5, 4.0) for i in range(n)
]).round(-3).astype(int)

# Previous late payments (out of last 5 invoices)
prev_late_count = np.random.binomial(5, [delay_prob[ind] for ind in industries_col])

# Assemble DataFrame
invoices = pd.DataFrame({
    'Invoice_ID':       [f'INV-{str(i+1).zfill(5)}' for i in range(n)],
    'Client_ID':        client_ids,
    'Industry':         industries_col,
    'Invoice_Amount':   amounts,
    'Credit_Limit':     credit_limit,
    'Tenure_Years':     tenure_years,
    'Invoice_Date':     invoice_dates.date,
    'Due_Date':         due_dates.date,
    'Payment_Date':     payment_dates.date,
    'Prev_Late_Count':  prev_late_count,   # Out of last 5 invoices
})

invoices['Invoice_Date'] = pd.to_datetime(invoices['Invoice_Date'])
invoices['Due_Date']     = pd.to_datetime(invoices['Due_Date'])
invoices['Payment_Date'] = pd.to_datetime(invoices['Payment_Date'])

# Target variable — what we want to predict
invoices['Days_Delayed']   = (invoices['Payment_Date'] - invoices['Due_Date']).dt.days
invoices['Is_Late'] = (invoices['Days_Delayed'] > 60).astype(int)  # 1 = late payer

print(f'Dataset shape: {invoices.shape}')
print(f'\nLate payers: {invoices["Is_Late"].sum()} ({invoices["Is_Late"].mean()*100:.1f}%)')
print(f'On-time:      {(invoices["Is_Late"]==0).sum()} ({(invoices["Is_Late"]==0).mean()*100:.1f}%)')
print()
print(invoices.head())

Dataset shape: (800, 12)

Late payers: 254 (31.8%)
On-time:      546 (68.2%)

  Invoice_ID Client_ID       Industry  Invoice_Amount  Credit_Limit  \
0  INV-00001    CL0103  Manufacturing          730000       2028000   
1  INV-00002    CL0271    IT Services          116000        408000   
2  INV-00003    CL0107    IT Services          387000        629000   
3  INV-00004    CL0072    IT Services          280000        842000   
4  INV-00005    CL0189  Manufacturing          664000       2335000

   Tenure_Years Invoice_Date   Due_Date Payment_Date  Prev_Late_Count  \
0           2.1   2024-11-01 2024-12-01   2025-05-10                3   
1           4.6   2023-04-26 2023-06-10   2023-11-14                1   
2           7.8   2024-05-30 2024-07-14   2024-07-19                1   
3           1.6   2025-03-27 2025-05-11   2025-05-23                0   
4           7.6   2024-12-15 2025-01-14   2025-01-28                1

   Days_Delayed  Is_Late  
0           160        1  
1           157        1  
2             5        0  
3            12        0  
4            14        0

# ── Project Charter — Python documentation ────────────────────────────────────
# This cell is the formal record of Phase 1 decisions.
# It should be the FIRST code cell in any analytics project.

PROJECT_CHARTER = {
    'project_name':     'Late Payment Risk Scoring — Himalayan Advisory Services',
    'analyst':          'Finance Analytics Team',
    'date':             datetime.today().strftime('%d %B %Y'),
    'version':          '1.0',

    'business_question': (
        'Which clients with open invoices are most likely to delay payment '
        'beyond 60 days, so that the collections team can prioritise follow-ups?'
    ),

    'why_it_matters': (
        'NPR 22 millionores overdue beyond 60 days. Working capital pressure '
        'affects payroll and vendor payments.'
    ),

    'success_criteria': {
        'primary':   'Recall >= 75% (catch at least 75% of actual late payers)',
        'secondary': 'Precision >= 50% (half of flagged clients should truly be late)',
        'business':  'Reduce overdue receivables by 20% within 2 months of deployment',
    },

    'data_sources': [
        'ERP invoice register (last 2 years)',
        'Client master file (industry, tenure, credit limit)',
        'Payment log with actual payment dates',
    ],

    'target_variable': 'Is_Late (1 = paid more than 60 days after due date)',

    'output': 'Monthly Excel report: client list ranked by risk score with recommended action',

    'timeline':    '2-week prototype; month-end full deployment',
    'stakeholder': 'CFO — monthly delivery by 3rd of each month',
    'constraints': ['Data stays on internal servers', 'Output must be Excel-readable'],
}

# Print the charter
print('=' * 65)
print(f"  PROJECT CHARTER: {PROJECT_CHARTER['project_name']}")
print('=' * 65)
for k, v in PROJECT_CHARTER.items():
    if k == 'project_name': continue
    if isinstance(v, dict):
        print(f'\n{k.upper().replace("_"," ")}:')
        for sk, sv in v.items():
            print(f'  [{sk}] {sv}')
    elif isinstance(v, list):
        print(f'\n{k.upper().replace("_"," ")}:')
        for item in v: print(f'  • {item}')
    else:
        print(f'\n{k.upper().replace("_"," ")}:')
        print(f'  {v}')
print('\n' + '=' * 65)

=================================================================
  PROJECT CHARTER: Late Payment Risk Scoring — Himalayan Advisory Services
=================================================================

ANALYST:
  Finance Analytics Team

DATE:
  28 April 2026

VERSION:
  1.0

BUSINESS QUESTION:
  Which clients with open invoices are most likely to delay payment beyond 60 days, so that the collections team can prioritise follow-ups?

WHY IT MATTERS:
  NPR 22 millionores overdue beyond 60 days. Working capital pressure affects payroll and vendor payments.

SUCCESS CRITERIA:
  [primary] Recall >= 75% (catch at least 75% of actual late payers)
  [secondary] Precision >= 50% (half of flagged clients should truly be late)
  [business] Reduce overdue receivables by 20% within 2 months of deployment

DATA SOURCES:
  • ERP invoice register (last 2 years)
  • Client master file (industry, tenure, credit limit)
  • Payment log with actual payment dates

TARGET VARIABLE:
  Is_Late (1 = paid more than 60 days after due date)

OUTPUT:
  Monthly Excel report: client list ranked by risk score with recommended action

TIMELINE:
  2-week prototype; month-end full deployment

STAKEHOLDER:
  CFO — monthly delivery by 3rd of each month

CONSTRAINTS:
  • Data stays on internal servers
  • Output must be Excel-readable

=================================================================

# ── Data Profiling — the standard Phase 2 opening ────────────────────────────

print('━' * 60)
print('  DATA PROFILE REPORT')
print(f'  Dataset: Invoice & Payment History')
print(f'  Generated: {datetime.today().strftime("%d %b %Y %H:%M")}')
print('━' * 60)

print(f'\n📐 DIMENSIONS')
print(f'  Rows (invoices): {invoices.shape[0]:,}')
print(f'  Columns:         {invoices.shape[1]}')

print(f'\n📅 DATE RANGE')
print(f'  Invoice dates: {invoices["Invoice_Date"].min().date()} → {invoices["Invoice_Date"].max().date()}')
print(f'  Payment dates: {invoices["Payment_Date"].min().date()} → {invoices["Payment_Date"].max().date()}')

print(f'\n🏷️ COLUMN TYPES')
print(invoices.dtypes.to_string())

print(f'\n🔍 MISSING VALUES')
missing = invoices.isnull().sum()
missing_pct = (invoices.isnull().mean() * 100).round(2)
missing_df = pd.DataFrame({'Count': missing, 'Percent': missing_pct})
print(missing_df[missing_df['Count'] > 0].to_string() if missing_df['Count'].sum() > 0 else '  None ✓')

print(f'\n🔄 DUPLICATE ROWS')
dup_count = invoices.duplicated().sum()
print(f'  Exact duplicates: {dup_count}')

print(f'\n🔑 UNIQUE VALUES (categorical columns)')
for col in ['Industry', 'Is_Late']:
    print(f'  {col}: {invoices[col].nunique()} unique → {invoices[col].unique()[:6]}')

print(f'\n📊 NUMERIC SUMMARY')
print(invoices[['Invoice_Amount','Credit_Limit','Tenure_Years','Prev_Late_Count','Days_Delayed']].describe().round(2).to_string())

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  DATA PROFILE REPORT
  Dataset: Invoice & Payment History
  Generated: 28 Apr 2026 13:30
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

📐 DIMENSIONS
  Rows (invoices): 800
  Columns:         12

📅 DATE RANGE
  Invoice dates: 2023-04-01 → 2025-03-29
  Payment dates: 2023-05-11 → 2025-11-07

🏷️ COLUMN TYPES
Invoice_ID                 object
Client_ID                  object
Industry                   object
Invoice_Amount              int64
Credit_Limit                int64
Tenure_Years              float64
Invoice_Date       datetime64[ns]
Due_Date           datetime64[ns]
Payment_Date       datetime64[ns]
Prev_Late_Count             int64
Days_Delayed                int64
Is_Late                     int64

🔍 MISSING VALUES
  None ✓

🔄 DUPLICATE ROWS
  Exact duplicates: 0

🔑 UNIQUE VALUES (categorical columns)
  Industry: 8 unique → ['Manufacturing' 'IT Services' 'Construction' 'Healthcare' 'Real Estate'
 'Retail']
  Is_Late: 2 unique → [1 0]

📊 NUMERIC SUMMARY
       Invoice_Amount  Credit_Limit  Tenure_Years  Prev_Late_Count  Days_Delayed
count          800.00        800.00        800.00           800.00        800.00
mean        382588.75    1057330.00          4.26             1.56         42.23
std         260797.07     811524.27          2.14             1.16         55.64
min          34000.00      70000.00          0.50             0.00          0.00
25%         188500.00     498000.00          2.50             1.00          5.00
50%         320500.00     825000.00          4.30             1.00         10.50
75%         487750.00    1364500.00          6.00             2.00         83.00
max        1321000.00    4884000.00          8.00             5.00        179.00

# ── Data Quality Assessment ───────────────────────────────────────────────────

print('━' * 60)
print('  DATA QUALITY ASSESSMENT')
print('━' * 60)

issues_found = []

# 1. COMPLETENESS
print('\n1. COMPLETENESS')
total_cells = invoices.shape[0] * invoices.shape[1]
missing_cells = invoices.isnull().sum().sum()
completeness = (1 - missing_cells / total_cells) * 100
print(f'   Overall completeness: {completeness:.1f}%')
print(f'   Missing cells: {missing_cells}')
if missing_cells > 0:
    issues_found.append(f'Missing values in: {list(invoices.columns[invoices.isnull().any()])}')

# 2. ACCURACY — logic checks
print('\n2. ACCURACY (logic checks)')
neg_amount = (invoices['Invoice_Amount'] <= 0).sum()
print(f'   Invoices with non-positive amount: {neg_amount}')
if neg_amount: issues_found.append(f'{neg_amount} invoices with zero/negative amount')

payment_before_due = (invoices['Payment_Date'] < invoices['Invoice_Date']).sum()
print(f'   Payments before invoice date:      {payment_before_due}')
if payment_before_due: issues_found.append(f'{payment_before_due} payments before invoice date')

over_credit = (invoices['Invoice_Amount'] > invoices['Credit_Limit']).sum()
pct = over_credit / len(invoices) * 100
print(f'   Invoices exceeding credit limit:   {over_credit} ({pct:.1f}%) ← investigate')
if pct > 5: issues_found.append(f'{over_credit} invoices exceed client credit limit ({pct:.1f}%)')

# 3. CONSISTENCY
print('\n3. CONSISTENCY')
# Check Days_Delayed matches the computed value
computed_delay = (invoices['Payment_Date'] - invoices['Due_Date']).dt.days
mismatch = (computed_delay != invoices['Days_Delayed']).sum()
print(f'   Days_Delayed column vs computed: {mismatch} mismatches (should be 0)')

prev_late_range = invoices['Prev_Late_Count'].between(0, 5).all()
print(f'   Prev_Late_Count in [0–5]:        {"✓ OK" if prev_late_range else "✗ Values out of range"}')

# 4. UNIQUENESS
print('\n4. UNIQUENESS')
dup_invoices = invoices['Invoice_ID'].duplicated().sum()
print(f'   Duplicate Invoice IDs: {dup_invoices}')

# 5. TIMELINESS
print('\n5. TIMELINESS')
latest_date = invoices['Invoice_Date'].max()
days_old = (pd.Timestamp.today() - latest_date).days
print(f'   Most recent invoice: {latest_date.date()} ({days_old} days ago)')

# Summary
print('\n' + '━' * 60)
print(f'QUALITY ISSUES FOUND: {len(issues_found)}')
for i, issue in enumerate(issues_found, 1):
    print(f'  [{i}] {issue}')
if not issues_found:
    print('  None major — data is suitable for analysis.')

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  DATA QUALITY ASSESSMENT
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. COMPLETENESS
   Overall completeness: 100.0%
   Missing cells: 0

2. ACCURACY (logic checks)
   Invoices with non-positive amount: 0
   Payments before invoice date:      0
   Invoices exceeding credit limit:   0 (0.0%) ← investigate

3. CONSISTENCY
   Days_Delayed column vs computed: 0 mismatches (should be 0)
   Prev_Late_Count in [0–5]:        ✓ OK

4. UNIQUENESS
   Duplicate Invoice IDs: 0

5. TIMELINESS
   Most recent invoice: 2025-03-29 (395 days ago)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
QUALITY ISSUES FOUND: 0
  None major — data is suitable for analysis.

# ── Univariate Analysis ───────────────────────────────────────────────────────
fig, axes = plt.subplots(2, 3, figsize=(16, 10))
fig.suptitle('Univariate Analysis — Invoice & Payment Data', fontsize=15, fontweight='bold')

# 1. Invoice Amount distribution
sns.histplot(invoices['Invoice_Amount'] / 100000, bins=30, kde=True,
             color='steelblue', ax=axes[0, 0])
axes[0, 0].set_title('Invoice Amount Distribution', fontweight='bold')
axes[0, 0].set_xlabel('Invoice Amount (NPR)')
axes[0, 0].set_ylabel('Count')
median_amt = invoices['Invoice_Amount'].median() / 100000
axes[0, 0].axvline(median_amt, color='red', linestyle='--',
                   label=f'Median: NPR {median_amt:.1f}L')
axes[0, 0].legend()

# 2. Industry distribution
industry_counts = invoices['Industry'].value_counts()
axes[0, 1].barh(industry_counts.index, industry_counts.values,
                color=sns.color_palette('deep', len(industry_counts)))
axes[0, 1].set_title('Invoices by Industry', fontweight='bold')
axes[0, 1].set_xlabel('Number of Invoices')
for i, v in enumerate(industry_counts.values):
    axes[0, 1].text(v + 2, i, str(v), va='center', fontsize=9)

# 3. Target variable balance
late_counts = invoices['Is_Late'].value_counts()
colors_target = ['seagreen', 'tomato']
bars = axes[0, 2].bar(['On Time (0)', 'Late >60d (1)'],
                      late_counts.values, color=colors_target, edgecolor='white')
for bar, val in zip(bars, late_counts.values):
    pct = val / len(invoices) * 100
    axes[0, 2].text(bar.get_x() + bar.get_width()/2, bar.get_height() + 5,
                    f'{val}\n({pct:.1f}%)', ha='center', fontweight='bold')
axes[0, 2].set_title('Target Variable: Is_Late', fontweight='bold')
axes[0, 2].set_ylabel('Count')

# 4. Client tenure
sns.histplot(invoices['Tenure_Years'], bins=20, kde=True,
             color='coral', ax=axes[1, 0])
axes[1, 0].set_title('Client Tenure Distribution', fontweight='bold')
axes[1, 0].set_xlabel('Tenure (Years)')

# 5. Previous late payment count
prev_counts = invoices['Prev_Late_Count'].value_counts().sort_index()
axes[1, 1].bar(prev_counts.index, prev_counts.values,
               color=sns.color_palette('YlOrRd', 6), edgecolor='white')
axes[1, 1].set_title('Previous Late Payments (out of 5)', fontweight='bold')
axes[1, 1].set_xlabel('Number of Previous Late Payments')
axes[1, 1].set_ylabel('Count')

# 6. Days delayed distribution
sns.histplot(invoices['Days_Delayed'], bins=40, kde=True,
             color='mediumpurple', ax=axes[1, 2])
axes[1, 2].set_title('Days Delayed Distribution', fontweight='bold')
axes[1, 2].set_xlabel('Days After Due Date')
axes[1, 2].axvline(60, color='red', linestyle='--', linewidth=2, label='60-day threshold')
axes[1, 2].legend()

plt.tight_layout()
plt.show()

# Key observations
print('Key observations:')
print(f'  • Invoice amounts range: NPR {invoices["Invoice_Amount"].min()/100000:.1f}L – NPR {invoices["Invoice_Amount"].max()/100000:.1f}L')
print(f'  • Class imbalance: {invoices["Is_Late"].mean()*100:.0f}% late — model must handle this')
print(f'  • {(invoices["Prev_Late_Count"] >= 3).mean()*100:.0f}% of records have 3+ previous late payments — strong signal')

Key observations:
  • Invoice amounts range: NPR 0.3L – NPR 13.2L
  • Class imbalance: 32% late — model must handle this
  • 21% of records have 3+ previous late payments — strong signal

# ── Bivariate Analysis — each variable vs Is_Late ────────────────────────────
fig, axes = plt.subplots(2, 3, figsize=(16, 10))
fig.suptitle('Bivariate Analysis — Variables vs Is_Late (Late Payment >60 Days)',
             fontsize=14, fontweight='bold')

# 1. Invoice Amount vs Is_Late
sns.boxplot(data=invoices, x='Is_Late', y='Invoice_Amount', palette={'0': 'seagreen', '1': 'tomato'}, ax=axes[0, 0], width=0.45, flierprops=dict(marker='o', markersize=3, alpha=0.4))
axes[0, 0].set_title('Invoice Amount by Payment Status', fontweight='bold')
axes[0, 0].set_xticklabels(['On Time', 'Late >60d'])
axes[0, 0].set_ylabel('Invoice Amount (NPR )')
axes[0, 0].yaxis.set_major_formatter(
    plt.FuncFormatter(lambda y, _: f'NPR {y/100000:.0f}L'))

# 2. Late rate by Industry
industry_late = invoices.groupby(
    'Industry')['Is_Late'].mean().sort_values(ascending=False)
colors = ['tomato' if v > 0.3 else 'steelblue' for v in industry_late.values]
axes[0, 1].barh(industry_late.index, industry_late.values * 100, color=colors)
axes[0, 1].axvline(invoices['Is_Late'].mean() * 100, color='navy',
                   linestyle='--', linewidth=1.5, label='Overall avg')
for i, v in enumerate(industry_late.values * 100):
    axes[0, 1].text(v + 0.5, i, f'{v:.0f}%', va='center', fontsize=9)
axes[0, 1].set_title(
    'Late Payment Rate by Industry(Red = high risk)', fontweight='bold')
axes[0, 1].set_xlabel('% Late Payers')
axes[0, 1].legend()

# 3. Prev_Late_Count vs Is_Late
prev_late_rate = invoices.groupby('Prev_Late_Count')['Is_Late'].mean() * 100
bars = axes[0, 2].bar(prev_late_rate.index, prev_late_rate.values,
                      color=sns.color_palette('YlOrRd', len(prev_late_rate)))
axes[0, 2].set_title(
    'Late Rate by Previous Late Payment History', fontweight='bold')
axes[0, 2].set_xlabel('Previous Late Payments (out of 5)')
axes[0, 2].set_ylabel('% Likely to Pay Late')
for bar, val in zip(bars, prev_late_rate.values):
    axes[0, 2].text(bar.get_x() + bar.get_width()/2, bar.get_height() + 0.5,
                    f'{val:.0f}%', ha='center', fontsize=9, fontweight='bold')

# 4. Tenure vs Is_Late
sns.violinplot(data=invoices, x='Is_Late', y='Tenure_Years',
               palette={'0': 'seagreen', '1': 'tomato'},
               ax=axes[1, 0], inner='quartile')
axes[1, 0].set_title('Client Tenure by Payment Status', fontweight='bold')
axes[1, 0].set_xticklabels(['On Time', 'Late >60d'])
axes[1, 0].set_ylabel('Tenure (Years)')

# 5. Invoice/Credit ratio vs Is_Late
invoices['Invoice_Credit_Ratio'] = invoices['Invoice_Amount'] / \
    invoices['Credit_Limit']
sns.boxplot(data=invoices, x='Is_Late', y='Invoice_Credit_Ratio',
            palette={'0': 'seagreen', '1': 'tomato'},
            ax=axes[1, 1], width=0.45)
axes[1, 1].set_title(
    'Invoice/Credit Limit Ratio by Payment Status', fontweight='bold')
axes[1, 1].set_xticklabels(['On Time', 'Late >60d'])
axes[1, 1].set_ylabel('Invoice Amount / Credit Limit')

# 6. Correlation heatmap
numeric_cols = invoices[['Invoice_Amount', 'Credit_Limit', 'Tenure_Years',
                         'Prev_Late_Count', 'Invoice_Credit_Ratio', 'Is_Late']]
corr = numeric_cols.corr().round(2)
sns.heatmap(corr, annot=True, fmt='.2f', cmap='RdYlGn', center=0,
            square=True, linewidths=0.8, ax=axes[1, 2],
            cbar_kws={'label': 'Correlation'})
axes[1, 2].set_title('Correlation Matrix', fontweight='bold')
axes[1, 2].set_xticklabels(
    axes[1, 2].get_xticklabels(), rotation=30, ha='right')

plt.tight_layout()
plt.show()

# Key findings
print('Key findings from bivariate analysis:')
print(f'  • Construction & Real Estate have highest late rates')
print(f'  • Prev_Late_Count is the strongest predictor — monotonic relationship')
print(f'  • Shorter-tenured clients are more likely to pay late')
print(f'  • Invoice/Credit ratio: late payers use a higher % of their credit limit')
print(f'  • Invoice_Amount alone has weak correlation with Is_Late')

/var/folders/kp/mytrwq8157q9s1tmw7r79fkm0000gn/T/ipykernel_11251/815473773.py:7: FutureWarning:

Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `x` variable to `hue` and set `legend=False` for the same effect.

  sns.boxplot(data=invoices, x='Is_Late', y='Invoice_Amount', palette={'0': 'seagreen', '1': 'tomato'}, ax=axes[0, 0], width=0.45, flierprops=dict(marker='o', markersize=3, alpha=0.4))
/var/folders/kp/mytrwq8157q9s1tmw7r79fkm0000gn/T/ipykernel_11251/815473773.py:9: UserWarning: set_ticklabels() should only be used with a fixed number of ticks, i.e. after set_ticks() or using a FixedLocator.
  axes[0, 0].set_xticklabels(['On Time', 'Late >60d'])
/var/folders/kp/mytrwq8157q9s1tmw7r79fkm0000gn/T/ipykernel_11251/815473773.py:41: FutureWarning:

Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `x` variable to `hue` and set `legend=False` for the same effect.

  sns.violinplot(data=invoices, x='Is_Late', y='Tenure_Years',
/var/folders/kp/mytrwq8157q9s1tmw7r79fkm0000gn/T/ipykernel_11251/815473773.py:45: UserWarning: set_ticklabels() should only be used with a fixed number of ticks, i.e. after set_ticks() or using a FixedLocator.
  axes[1, 0].set_xticklabels(['On Time', 'Late >60d'])
/var/folders/kp/mytrwq8157q9s1tmw7r79fkm0000gn/T/ipykernel_11251/815473773.py:51: FutureWarning:

Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `x` variable to `hue` and set `legend=False` for the same effect.

  sns.boxplot(data=invoices, x='Is_Late', y='Invoice_Credit_Ratio',
/var/folders/kp/mytrwq8157q9s1tmw7r79fkm0000gn/T/ipykernel_11251/815473773.py:56: UserWarning: set_ticklabels() should only be used with a fixed number of ticks, i.e. after set_ticks() or using a FixedLocator.
  axes[1, 1].set_xticklabels(['On Time', 'Late >60d'])

Key findings from bivariate analysis:
  • Construction & Real Estate have highest late rates
  • Prev_Late_Count is the strongest predictor — monotonic relationship
  • Shorter-tenured clients are more likely to pay late
  • Invoice/Credit ratio: late payers use a higher % of their credit limit
  • Invoice_Amount alone has weak correlation with Is_Late

# ── Phase 3 Preview — the preparation steps we will do in Module 07 ───────────

print('DATA PREPARATION PLAN (to be executed in Module 07)')
print('━' * 55)

prep_plan = [
    ('STEP 1', 'Select relevant columns',
     'Drop Invoice_ID, Invoice_Date, Payment_Date (leakage risk)'),

    ('STEP 2', 'Handle over-credit-limit records',
     'Investigate, then cap at 1.0 or add binary flag'),

    ('STEP 3', 'Engineer new features',
     '• invoice_to_credit_ratio (already done)\n'
     '  • credit_utilisation_flag = 1 if ratio > 0.8\n'
     '  • invoice_month (seasonality)\n'
     '  • invoice_quarter\n'
     '  • high_risk_industry flag'),

    ('STEP 4', 'Encode categorical variables',
     'One-hot encode Industry column (8 categories → 8 binary columns)'),

    ('STEP 5', 'Scale numeric features',
     'StandardScaler on Invoice_Amount, Credit_Limit, Tenure_Years'
     '  (needed for models like Logistic Regression, KNN, SVM)'),

    ('STEP 6', 'Handle class imbalance',
     'Is_Late is ~30% — may need SMOTE or class_weight="balanced"'),

    ('STEP 7', 'Train/Test split',
     '80% training (640 rows) / 20% test (160 rows)'
     '  Use stratify=Is_Late to preserve class ratio'),
]

for step, task, detail in prep_plan:
    print(f'\n  {step}: {task}')
    for line in detail.split('\n'):
        print(f'    {line}')

print('\n━' * 55)
print('\nPreview of what the analysis-ready dataset will look like:')

# Quick preview of engineered features
prep_preview = invoices.copy()
prep_preview['Invoice_Credit_Ratio'] = (prep_preview['Invoice_Amount'] /
                                         prep_preview['Credit_Limit']).round(3)
prep_preview['High_Risk_Industry']   = prep_preview['Industry'].isin(
    ['Construction', 'Real Estate', 'Trading', 'Retail']).astype(int)
prep_preview['Invoice_Month']        = prep_preview['Invoice_Date'].dt.month
prep_preview['Invoice_Quarter']      = prep_preview['Invoice_Date'].dt.quarter

feature_cols = ['Invoice_Amount', 'Credit_Limit', 'Tenure_Years',
                'Prev_Late_Count', 'Invoice_Credit_Ratio',
                'High_Risk_Industry', 'Invoice_Month', 'Is_Late']
print(prep_preview[feature_cols].head(6).to_string(index=False))

DATA PREPARATION PLAN (to be executed in Module 07)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  STEP 1: Select relevant columns
    Drop Invoice_ID, Invoice_Date, Payment_Date (leakage risk)

  STEP 2: Handle over-credit-limit records
    Investigate, then cap at 1.0 or add binary flag

  STEP 3: Engineer new features
    • invoice_to_credit_ratio (already done)
      • credit_utilisation_flag = 1 if ratio > 0.8
      • invoice_month (seasonality)
      • invoice_quarter
      • high_risk_industry flag

  STEP 4: Encode categorical variables
    One-hot encode Industry column (8 categories → 8 binary columns)

  STEP 5: Scale numeric features
    StandardScaler on Invoice_Amount, Credit_Limit, Tenure_Years  (needed for models like Logistic Regression, KNN, SVM)

  STEP 6: Handle class imbalance
    Is_Late is ~30% — may need SMOTE or class_weight="balanced"

  STEP 7: Train/Test split
    80% training (640 rows) / 20% test (160 rows)  Use stratify=Is_Late to preserve class ratio

━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━
━

Preview of what the analysis-ready dataset will look like:
 Invoice_Amount  Credit_Limit  Tenure_Years  Prev_Late_Count  Invoice_Credit_Ratio  High_Risk_Industry  Invoice_Month  Is_Late
         730000       2028000           2.1                3                 0.360                   0             11        1
         116000        408000           4.6                1                 0.284                   0              4        1
         387000        629000           7.8                1                 0.615                   0              5        0
         280000        842000           1.6                0                 0.333                   0              3        0
         664000       2335000           7.6                1                 0.284                   0             12        0
         327000        619000           1.9                2                 0.528                   1              4        1

# ── Phases 4–6 Preview ────────────────────────────────────────────────────────
print('CRISP-DM PHASES 4–6 — COMING IN MODULES 09–11')
print('━' * 58)

roadmap = {
    'Phase 4: Modelling (Module 09 & 10)': [
        'Split data: 80% train / 20% test',
        'Baseline model: Logistic Regression (interpretable)',
        'Improved model: Decision Tree / Random Forest',
        'Predict probability of Is_Late for each client',
    ],
    'Phase 5: Evaluation (Modules 09 & 10)': [
        'Confusion matrix: True Positives, False Positives, etc.',
        'Recall: did we catch >= 75% of actual late payers? (our KPI)',
        'Precision: how many flagged clients are truly late?',
        'AUC-ROC: overall ranking ability of the model',
        'Compare vs Phase 1 success criteria',
    ],
    'Phase 6: Deployment (End of Module 10)': [
        'Score new invoices: predict late-payment risk for open invoices',
        'Rank clients by risk score (highest risk first)',
        'Export to Excel: client name, invoice, risk score, action',
        'Schedule monthly refresh with new ERP data',
    ],
}

for phase, steps in roadmap.items():
    print(f'\n  {phase}')
    for s in steps:
        print(f'    ✓ {s}')

print()
print('━' * 58)
print('\nPREVIEW: What the final output report will look like:')

# Simulate a risk-scored output (using Prev_Late_Count as proxy for now)
output_preview = invoices[['Client_ID', 'Industry', 'Invoice_Amount',
                             'Due_Date', 'Prev_Late_Count']].copy()
# Proxy risk score
output_preview['Risk_Score'] = (
    invoices['Prev_Late_Count'] / 5 * 0.5 +
    invoices['Invoice_Credit_Ratio'].clip(0, 1) * 0.3 +
    invoices['Industry'].isin(['Construction','Real Estate']).astype(float) * 0.2
).round(3)
output_preview['Risk_Category'] = pd.cut(output_preview['Risk_Score'],
    bins=[0, 0.3, 0.6, 1.0], labels=['Low', 'Medium', 'High'])
output_preview['Recommended_Action'] = output_preview['Risk_Category'].map({
    'Low':    'No action — standard follow-up',
    'Medium': 'Send reminder 7 days before due date',
    'High':   'Call client 2 weeks before due date; review credit terms',
})
output_preview['Invoice_Amount_Lakh'] = (output_preview['Invoice_Amount']/100000).round(1)
output_preview = output_preview.sort_values('Risk_Score', ascending=False)

print(output_preview[['Client_ID','Industry','Invoice_Amount_Lakh',
                       'Risk_Score','Risk_Category','Recommended_Action']].head(8).to_string(index=False))

CRISP-DM PHASES 4–6 — COMING IN MODULES 09–11
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  Phase 4: Modelling (Module 09 & 10)
    ✓ Split data: 80% train / 20% test
    ✓ Baseline model: Logistic Regression (interpretable)
    ✓ Improved model: Decision Tree / Random Forest
    ✓ Predict probability of Is_Late for each client

  Phase 5: Evaluation (Modules 09 & 10)
    ✓ Confusion matrix: True Positives, False Positives, etc.
    ✓ Recall: did we catch >= 75% of actual late payers? (our KPI)
    ✓ Precision: how many flagged clients are truly late?
    ✓ AUC-ROC: overall ranking ability of the model
    ✓ Compare vs Phase 1 success criteria

  Phase 6: Deployment (End of Module 10)
    ✓ Score new invoices: predict late-payment risk for open invoices
    ✓ Rank clients by risk score (highest risk first)
    ✓ Export to Excel: client name, invoice, risk score, action
    ✓ Schedule monthly refresh with new ERP data

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

PREVIEW: What the final output report will look like:
Client_ID     Industry  Invoice_Amount_Lakh  Risk_Score Risk_Category                                       Recommended_Action
   CL0268 Construction                  4.7       0.879          High Call client 2 weeks before due date; review credit terms
   CL0233 Construction                  3.4       0.843          High Call client 2 weeks before due date; review credit terms
   CL0060 Construction                  5.0       0.806          High Call client 2 weeks before due date; review credit terms
   CL0220  Real Estate                 11.7       0.798          High Call client 2 weeks before due date; review credit terms
   CL0158 Construction                 11.1       0.798          High Call client 2 weeks before due date; review credit terms
   CL0003 Construction                  4.5       0.784          High Call client 2 weeks before due date; review credit terms
   CL0246  Real Estate                 10.4       0.783          High Call client 2 weeks before due date; review credit terms
   CL0278 Construction                 10.8       0.771          High Call client 2 weeks before due date; review credit terms

# Exercise 2: profile_dataframe() function

def profile_dataframe(df):
    """
    Produce a data profile for any DataFrame.
    Returns: DataFrame of numeric column statistics.
    """
    # Write your solution here
    pass

# Test it:
# result = profile_dataframe(invoices)
# print(result)

# Exercise 3: Bivariate analysis
import pandas as pd, numpy as np, seaborn as sns, matplotlib.pyplot as plt

# Your analysis here

# SOLUTION — Exercise 2: profile_dataframe()

def profile_dataframe(df):
    print('=' * 60)
    print(f'DATA PROFILE — {df.shape[0]:,} rows × {df.shape[1]} columns')
    print('=' * 60)

    print('\nCOLUMN TYPES & MISSING VALUES:')
    summary = pd.DataFrame({
        'Type':    df.dtypes,
        'Non-Null': df.notnull().sum(),
        'Missing': df.isnull().sum(),
        'Missing%': (df.isnull().mean() * 100).round(1),
    })
    print(summary.to_string())

    print('\nCATEGORICAL COLUMNS — Top 5 values:')
    for col in df.select_dtypes(include='object').columns:
        top5 = df[col].value_counts().head(5)
        print(f'  {col}:')
        for val, cnt in top5.items():
            print(f'    {val:<25} {cnt:>5} ({cnt/len(df)*100:.1f}%)')

    print('\nNUMERIC SUMMARY:')
    num_summary = df.select_dtypes(include='number').describe().round(2)
    print(num_summary.to_string())

    return num_summary

result = profile_dataframe(invoices)

============================================================
DATA PROFILE — 800 rows × 13 columns
============================================================

COLUMN TYPES & MISSING VALUES:
                                Type  Non-Null  Missing  Missing%
Invoice_ID                    object       800        0       0.0
Client_ID                     object       800        0       0.0
Industry                      object       800        0       0.0
Invoice_Amount                 int64       800        0       0.0
Credit_Limit                   int64       800        0       0.0
Tenure_Years                 float64       800        0       0.0
Invoice_Date          datetime64[ns]       800        0       0.0
Due_Date              datetime64[ns]       800        0       0.0
Payment_Date          datetime64[ns]       800        0       0.0
Prev_Late_Count                int64       800        0       0.0
Days_Delayed                   int64       800        0       0.0
Is_Late                        int64       800        0       0.0
Invoice_Credit_Ratio         float64       800        0       0.0

CATEGORICAL COLUMNS — Top 5 values:
  Invoice_ID:
    INV-00001                     1 (0.1%)
    INV-00538                     1 (0.1%)
    INV-00528                     1 (0.1%)
    INV-00529                     1 (0.1%)
    INV-00530                     1 (0.1%)
  Client_ID:
    CL0099                        9 (1.1%)
    CL0144                        7 (0.9%)
    CL0192                        7 (0.9%)
    CL0160                        7 (0.9%)
    CL0252                        7 (0.9%)
  Industry:
    Manufacturing               170 (21.2%)
    IT Services                 140 (17.5%)
    Trading                     111 (13.9%)
    Healthcare                   97 (12.1%)
    Real Estate                  84 (10.5%)

NUMERIC SUMMARY:
       Invoice_Amount  Credit_Limit  Tenure_Years  Prev_Late_Count  Days_Delayed  Is_Late  Invoice_Credit_Ratio
count          800.00        800.00        800.00           800.00        800.00   800.00                800.00
mean        382588.75    1057330.00          4.26             1.56         42.23     0.32                  0.39
std         260797.07     811524.27          2.14             1.16         55.64     0.47                  0.11
min          34000.00      70000.00          0.50             0.00          0.00     0.00                  0.25
25%         188500.00     498000.00          2.50             1.00          5.00     0.00                  0.29
50%         320500.00     825000.00          4.30             1.00         10.50     0.00                  0.36
75%         487750.00    1364500.00          6.00             2.00         83.00     1.00                  0.46
max        1321000.00    4884000.00          8.00             5.00        179.00     1.00                  0.66

# SOLUTION — Exercise 3: Bivariate Exploration

# Q1: Average invoice amount by payment status
avg_by_status = invoices.groupby('Is_Late')['Invoice_Amount'].agg(['mean','median','count'])
avg_by_status.index = ['On Time', 'Late >60d']
print('Q1 — Invoice Amount by Payment Status:')
print(avg_by_status.round(0).to_string())

# Q2: Industry with lowest late rate
print('\nQ2 — Late Rate by Industry (ascending):')
industry_rates = invoices.groupby('Industry')['Is_Late'].mean().sort_values()
for ind, rate in industry_rates.items():
    print(f'  {ind:<18} {rate*100:.1f}%')
print(f'  → Lowest: {industry_rates.idxmin()} ({industry_rates.min()*100:.1f}%)')

# Q3: Heatmap of average Days_Delayed by Industry × Prev_Late_Count
fig, ax = plt.subplots(figsize=(12, 6))
pivot = invoices.pivot_table(
    values='Days_Delayed', index='Industry',
    columns='Prev_Late_Count', aggfunc='mean'
).round(0)
sns.heatmap(pivot, annot=True, fmt='.0f', cmap='YlOrRd',
            linewidths=0.5, ax=ax, cbar_kws={'label': 'Avg Days Delayed'})
ax.set_title('Average Days Delayed by Industry × Previous Late Count',
             fontsize=13, fontweight='bold')
ax.set_xlabel('Previous Late Payment Count (out of 5)')
plt.tight_layout()
plt.show()

# Q4: Highest correlation with Is_Late
invoices_num = invoices[['Invoice_Amount', 'Credit_Limit', 'Tenure_Years',
                           'Prev_Late_Count', 'Invoice_Credit_Ratio', 'Is_Late']]
corr_with_target = invoices_num.corr()['Is_Late'].drop('Is_Late').sort_values(key=abs, ascending=False)
print('\nQ4 — Correlation with Is_Late:')
print(corr_with_target.round(3).to_string())
top_var = corr_with_target.index[0]
print(f'\n→ Highest: {top_var} (r = {corr_with_target.iloc[0]:.3f})')
print(f'  Meaning: clients with more previous late payments are far more likely to pay late again.')
print(f'  This variable should be a key input feature for the predictive model.')

Q1 — Invoice Amount by Payment Status:
               mean    median  count
On Time    368247.0  311500.0    546
Late >60d  413417.0  344500.0    254

Q2 — Late Rate by Industry (ascending):
  Finance            13.8%
  Healthcare         15.5%
  IT Services        17.1%
  Manufacturing      34.1%
  Trading            37.8%
  Real Estate        40.5%
  Retail             45.7%
  Construction       58.6%
  → Lowest: Finance (13.8%)

Q4 — Correlation with Is_Late:
Prev_Late_Count         0.098
Invoice_Amount          0.081
Credit_Limit            0.071
Invoice_Credit_Ratio   -0.016
Tenure_Years           -0.011

→ Highest: Prev_Late_Count (r = 0.098)
  Meaning: clients with more previous late payments are far more likely to pay late again.
  This variable should be a key input feature for the predictive model.