Python Automation: Real Workflows That Replace Manual Processes

Manual trigger → Python script → Data in → Transform → Output → Scheduled

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from datetime import datetime

def generate_weekly_report(source_files, output_path):
    # Combine all sources
    frames = [pd.read_excel(f) for f in source_files]
    df = pd.concat(frames, ignore_index=True)

    # Clean
    df = df.dropna(subset=["revenue"])
    df["date"] = pd.to_datetime(df["date"])

    # Aggregate
    summary = (
        df.groupby("department")
        .agg(
            total_revenue=("revenue", "sum"),
            total_orders=("order_id", "count"),
            avg_order=("revenue", "mean"),
        )
        .reset_index()
    )

    # Write formatted Excel
    wb = Workbook()
    ws = wb.active
    ws.title = "Weekly Summary"

    headers = ["Department", "Revenue", "Orders", "Avg Order"]
    header_fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")

    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=h)
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = header_fill

    for i, row in summary.iterrows():
        ws.cell(row=i + 2, column=1, value=row["department"])
        ws.cell(row=i + 2, column=2, value=round(row["total_revenue"], 2))
        ws.cell(row=i + 2, column=3, value=row["total_orders"])
        ws.cell(row=i + 2, column=4, value=round(row["avg_order"], 2))

    wb.save(output_path)
    return output_path

# Run
generate_weekly_report(
    ["sales_north.xlsx", "sales_south.xlsx", "sales_west.xlsx"],
    f"weekly_report_{datetime.now().strftime('%Y%m%d')}.xlsx",
)

import requests
import pandas as pd
from datetime import datetime, timedelta

def fetch_daily_metrics(api_url, api_key, days_back=1):
    """Fetch metrics from API and return clean DataFrame."""
    since = (datetime.now() - timedelta(days=days_back)).isoformat()

    response = requests.get(
        f"{api_url}/metrics",
        headers={"Authorization": f"Bearer {api_key}"},
        params={"since": since},
        timeout=30,
    )
    response.raise_for_status()
    data = response.json()

    df = pd.DataFrame(data["results"])
    df["date"] = pd.to_datetime(df["timestamp"]).dt.date
    df = df[["date", "page_views", "conversions", "revenue"]]

    return df

def append_to_tracker(new_data, tracker_path):
    """Append new data to existing tracker spreadsheet."""
    try:
        existing = pd.read_excel(tracker_path)
        combined = pd.concat([existing, new_data], ignore_index=True)
        combined = combined.drop_duplicates(subset=["date"], keep="last")
    except FileNotFoundError:
        combined = new_data

    combined.to_excel(tracker_path, index=False)
    return len(new_data)

# Run
metrics = fetch_daily_metrics("https://api.example.com", "your-api-key")
rows_added = append_to_tracker(metrics, "kpi_tracker.xlsx")
print(f"Added {rows_added} rows to tracker")

Database (SQL) ──┐
                 ├── Python ── Clean ── Merge ── Aggregate ── Report
API (JSON)    ───┘
Excel (manual) ──┘

import pandas as pd
import sqlite3

def build_combined_report():
    # Source 1: Database
    conn = sqlite3.connect("operations.db")
    db_data = pd.read_sql("SELECT * FROM orders WHERE date >= date('now', '-30 days')", conn)
    conn.close()

    # Source 2: Excel (manually maintained by another team)
    manual_data = pd.read_excel("team_targets.xlsx")

    # Clean each source
    db_data["date"] = pd.to_datetime(db_data["date"])
    db_data = db_data.dropna(subset=["amount"])

    manual_data.columns = manual_data.columns.str.strip().str.lower()

    # Merge on shared key
    combined = db_data.merge(manual_data, on="department", how="left")

    # Calculate vs target
    summary = (
        combined.groupby("department")
        .agg(actual=("amount", "sum"), target=("monthly_target", "first"))
        .reset_index()
    )
    summary["achievement"] = (summary["actual"] / summary["target"] * 100).round(1)

    # Output
    summary.to_excel("monthly_performance.xlsx", index=False)
    return summary

report = build_combined_report()
print(report.to_string(index=False))

 department   actual   target  achievement
    Finance  45200.0  50000.0         90.4
 Operations  62800.0  60000.0        104.7
      Sales  78500.0  75000.0        104.7

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email import encoders
import os

def send_report(filepath, recipients, subject):
    """Send report as email attachment via SMTP."""
    msg = MIMEMultipart()
    msg["Subject"] = subject
    msg["From"] = os.environ["SMTP_FROM"]
    msg["To"] = ", ".join(recipients)

    body = MIMEText(
        f"Automated report attached.\nGenerated: {os.path.basename(filepath)}",
        "plain",
    )
    msg.attach(body)

    with open(filepath, "rb") as f:
        part = MIMEBase("application", "octet-stream")
        part.set_payload(f.read())
        encoders.encode_base64(part)
        part.add_header(
            "Content-Disposition",
            f"attachment; filename={os.path.basename(filepath)}",
        )
        msg.attach(part)

    with smtplib.SMTP(os.environ["SMTP_HOST"], int(os.environ["SMTP_PORT"])) as server:
        server.starttls()
        server.login(os.environ["SMTP_USER"], os.environ["SMTP_PASS"])
        server.send_message(msg)