Pandas Practical & Real Use Cases

Security notice: Run these scripts only in a controlled practice environment. Do not execute them in production or institutional systems without authorization, backups, testing, least-privilege permissions, change control, and compliance with organizational protocols.

Aviso de seguridad: Ejecuta estos scripts únicamente en un ambiente controlado de práctica. No los corras en producción ni en sistemas institucionales sin autorización, respaldos, pruebas, permisos mínimos, control de cambios y cumplimiento de los protocolos organizacionales.

Business Purpose

Propósito de negocio

Create a compact executive KPI package from the cleaned and analyzed service-request data.

Este caso continúa el flujo real de trabajo con solicitudes de servicios municipales y documenta cada transformación de forma trazable.

Input and Output

Entrada y salida

Inputcity_service_requests_clean_master.csv

Outputexecutive_city_service_summary.xlsx

SeriesUse Case #20 of 20

Results can vary because the source dataset was generated synthetically and randomly. Run the code to obtain the exact values from your local file.

Los resultados pueden variar porque el dataset de origen fue generado de forma sintética y aleatoria. Ejecuta el código para obtener los valores exactos de tu archivo local.

Step 1 — Load the clean master

import pandas as pd
from pathlib import Path

base = Path.home() / "Documents" / "Pandas_Use_Cases"
input_file = base / "city_service_requests_clean_master.csv"
output_file = base / "executive_city_service_summary.xlsx"

df = pd.read_csv(
    input_file,
    parse_dates=["Created_Date", "Closed_Date"]
)

Expected Result / Interpretation

Resultado esperado / Interpretación

The executive summary uses the final trusted dataset.

Step 2 — Calculate headline KPIs

total_requests = len(df)
closed_requests = df["Status"].isin(["Closed", "Resolved"]).sum()
closure_rate = closed_requests / total_requests * 100
avg_resolution = df["Resolution_Days"].mean()
avg_satisfaction = df["Satisfaction_Score"].mean()
total_cost = df["Estimated_Cost"].sum()

kpis = pd.DataFrame({
    "KPI": [
        "Total Requests",
        "Closed Requests",
        "Closure Rate Percent",
        "Average Resolution Days",
        "Average Satisfaction",
        "Total Estimated Cost"
    ],
    "Value": [
        total_requests,
        closed_requests,
        round(closure_rate, 2),
        round(avg_resolution, 2),
        round(avg_satisfaction, 2),
        round(total_cost, 2)
    ]
})

print(kpis.to_string(index=False))

Expected Result / Interpretation

Resultado esperado / Interpretación

Six headline KPIs summarize demand, completion, speed, satisfaction, and cost.

Step 3 — Create executive supporting tables

by_department = (
    df.groupby("Department")
      .agg(
          Request_Count=("Request_ID", "count"),
          Average_Resolution_Days=("Resolution_Days", "mean"),
          Average_Satisfaction=("Satisfaction_Score", "mean")
      )
      .round(2)
      .sort_values("Request_Count", ascending=False)
)

by_service = (
    df.groupby("Service_Type")
      .size()
      .rename("Request_Count")
      .sort_values(ascending=False)
      .to_frame()
)

by_city = (
    df.groupby("City")
      .size()
      .rename("Request_Count")
      .sort_values(ascending=False)
      .to_frame()
)

Expected Result / Interpretation

Resultado esperado / Interpretación

Supporting tables explain where demand and performance are concentrated.

Step 4 — Create data quality snapshot

quality_snapshot = pd.DataFrame({
    "Metric": [
        "Rows with Any Quality Issue",
        "Average Data Quality Score",
        "Excellent Quality Records",
        "Critical Quality Records"
    ],
    "Value": [
        int(df["Has_Any_Data_Quality_Issue"].sum()),
        round(df["Data_Quality_Score"].mean(), 2),
        int((df["Data_Quality_Band"] == "Excellent").sum()),
        int((df["Data_Quality_Band"] == "Critical").sum())
    ]
})

print(quality_snapshot.to_string(index=False))

Expected Result / Interpretation

Resultado esperado / Interpretación

Executives see both operational KPIs and the reliability of the underlying information.

Step 5 — Export a multi-sheet Excel package

with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
    kpis.to_excel(writer, sheet_name="Executive KPIs", index=False)
    by_department.to_excel(writer, sheet_name="By Department")
    by_service.to_excel(writer, sheet_name="By Service Type")
    by_city.to_excel(writer, sheet_name="By City")
    quality_snapshot.to_excel(
        writer,
        sheet_name="Data Quality",
        index=False
    )

print(f"Saved: {output_file}")

Expected Result / Interpretation

Resultado esperado / Interpretación

A multi-sheet executive workbook is created for decision-making and presentation.

Use Case #20 Completed

Caso de uso #20 completado

The output of this module becomes the controlled input for the next stage of the 20-case Pandas workflow.

La salida de este módulo se convierte en la entrada controlada para la siguiente etapa del flujo de 20 casos de Pandas.

More Community & Local Opportunity Resources

Más recursos comunitarios y oportunidades locales

Explore additional resources connected to local training, practical skills, employment opportunities, and community growth.

Explora recursos adicionales relacionados con capacitación local, habilidades prácticas, oportunidades de empleo y crecimiento comunitario.

HialeahOficios

Community-focused resources for trades, skills, local opportunities, and practical career development.

Recursos comunitarios enfocados en oficios, habilidades, oportunidades locales y desarrollo profesional práctico.

hialeahoficios.com

MiamiOficios

Miami-oriented resources for practical learning, employment pathways, and workforce development.

Recursos orientados a Miami para aprendizaje práctico, rutas de empleo y desarrollo de la fuerza laboral.

miamioficios.com