ERP KPI Snapshot to Excel Python Script Generator

# Requires: pip install pandas openpyxl
import pandas as pd
from datetime import date

SOURCE = 'order_lines.csv'
GRACE_DAYS = 0

df = pd.read_csv(SOURCE, parse_dates=['promise_date', 'ship_date'])

# Only lines that have shipped count towards on time delivery
shipped = df[df['ship_date'].notna()].copy()
shipped['late_days'] = (shipped['ship_date'] - shipped['promise_date']).dt.days
shipped['on_time'] = shipped['late_days'] <= GRACE_DAYS

# Recompute from totals per plant, do not average percentages
by_plant = shipped.groupby('plant').agg(
    lines=('on_time', 'size'),
    on_time_lines=('on_time', 'sum'),
    shipped_value=('line_value', 'sum'),
)
by_plant['otd_pct'] = (by_plant['on_time_lines'] / by_plant['lines'] * 100).round(1)

total_lines = int(by_plant['lines'].sum())
total_on_time = int(by_plant['on_time_lines'].sum())
company_otd = round(total_on_time / total_lines * 100, 1) if total_lines else 0.0

summary = pd.DataFrame({
    'KPI': ['On time delivery percent (lines)', 'Shipped lines', 'Shipped value'],
    'Value': [company_otd, total_lines, float(by_plant['shipped_value'].sum())],
})

out_name = 'kpi_snapshot_' + date.today().isoformat() + '.xlsx'
with pd.ExcelWriter(out_name, engine='openpyxl') as writer:
    summary.to_excel(writer, sheet_name='Summary', index=False)
    by_plant.reset_index().to_excel(writer, sheet_name='By plant', index=False)
    shipped.sort_values('late_days', ascending=False).head(50).to_excel(writer, sheet_name='Worst 50 late lines', index=False)

print('Wrote', out_name)