Python for Automation: A Real-World Project Guide

Python for automation usually starts with a script that works on your machine once. The difference between that and automation people can rely on is everything around the core logic: what happens when the input is wrong, when the job runs twice, when nobody is watching. This guide builds one complete project with those parts and runs it for real.

The task is a weekly Excel report for a bike-sharing operator: total rentals for a given week, casual and registered riders, the busiest day, and the change from the previous week, plus a daily breakdown. The data is the UCI Bike Sharing daily file for Washington D.C.'s Capital Bikeshare system (UCI: Bike Sharing).

import logging
import shutil
from pathlib import Path

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font

CONFIG = {"source": "day.csv", "out_dir": "reports", "week": "2012-W40"}   # in real use: a TOML/JSON file
logging.basicConfig(filename="report.log", level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
shutil.rmtree(CONFIG["out_dir"], ignore_errors=True)                    # clean start so the example repeats
Path("report.log").write_text("")


def load(path):
    df = pd.read_csv(path, parse_dates=["dteday"])
    problems = []
    if df.dteday.duplicated().any():
        problems.append("duplicate dates")
    if df.cnt.lt(0).any() or df.cnt.isna().any():
        problems.append("missing or negative counts")
    if problems:                                                        # stop loudly rather than send a wrong report
        raise ValueError("; ".join(problems))
    return df


def build_report(df, week):
    start = pd.Timestamp.fromisocalendar(int(week[:4]), int(week[-2:]), 1)
    days = df[(df.dteday >= start) & (df.dteday < start + pd.Timedelta(days=7))]
    prev = df[(df.dteday >= start - pd.Timedelta(days=7)) & (df.dteday < start)]
    summary = pd.DataFrame({
        "metric": ["total rentals", "casual riders", "registered riders", "busiest day", "change vs previous week"],
        "value": [int(days.cnt.sum()), int(days.casual.sum()), int(days.registered.sum()),
                  f"{days.loc[days.cnt.idxmax(), 'dteday']:%a %d %b}", f"{days.cnt.sum() / prev.cnt.sum() - 1:+.1%}"]})
    daily = days[["dteday", "casual", "registered", "cnt"]].rename(columns={"dteday": "date", "cnt": "total"})
    return summary, daily


def write(summary, daily, path):
    with pd.ExcelWriter(path, engine="openpyxl") as xl:
        summary.to_excel(xl, sheet_name="Summary", index=False)
        daily.to_excel(xl, sheet_name="Daily", index=False)
    wb = load_workbook(path)
    for ws in wb.worksheets:                                            # light formatting people expect
        for cell in ws[1]:
            cell.font = Font(bold=True)
        ws.column_dimensions["A"].width = 26
    for row in wb["Daily"].iter_rows(min_row=2, min_col=1, max_col=1):
        row[0].number_format = "ddd dd mmm yyyy"
    wb.save(path)


def run(config):
    out = Path(config["out_dir"]) / f"rentals-{config['week']}.xlsx"
    if out.exists():                                                    # idempotent: never overwrite a sent report
        logging.info("skip %s: already exists", out)
        return f"skipped, {out} already exists"
    out.parent.mkdir(parents=True, exist_ok=True)
    summary, daily = build_report(load(config["source"]), config["week"])
    write(summary, daily, out)
    logging.info("wrote %s", out)
    return f"wrote {out}"


print("A1 first run: ", run(CONFIG))
print("A2 second run:", run(CONFIG))

wb = load_workbook(Path(CONFIG["out_dir"]) / f"rentals-{CONFIG['week']}.xlsx")
print("\nA3 reading the workbook back")
print("   sheets:", wb.sheetnames)
for metric, value in wb["Summary"].iter_rows(min_row=2, values_only=True):
    print(f"   {metric:<26}{value}")
print(f"   Daily sheet rows: {wb['Daily'].max_row - 1} | header bold: {wb['Daily']['A1'].font.b}")
print(f"\nA4 log lines written: {len(Path('report.log').read_text().splitlines())}")

broken = pd.read_csv(CONFIG["source"])
broken = pd.concat([broken, broken.tail(1)])
broken.to_csv("day_broken.csv", index=False)
try:
    run({**CONFIG, "source": "day_broken.csv", "week": "2012-W41"})
except ValueError as err:
    print(f"A5 a bad input file stops the run: {err}")

A1 first run:  wrote reports\rentals-2012-W40.xlsx
A2 second run: skipped, reports\rentals-2012-W40.xlsx already exists

A3 reading the workbook back
   sheets: ['Summary', 'Daily']
   total rentals             45948
   casual riders             8025
   registered riders         37923
   busiest day               Fri 05 Oct
   change vs previous week   -13.2%
   Daily sheet rows: 7 | header bold: True

A4 log lines written: 2
A5 a bad input file stops the run: duplicate dates

Part 1: configuration, not hard-coded values

The source file, output folder and week live in one CONFIG dictionary. In a real job they would come from a small TOML or JSON file or command-line arguments, so the same script can produce any week's report without editing code.

Part 2: validate the input and stop loudly

load checks for duplicate dates and missing or negative counts before anything is calculated. When the last row of the file was duplicated, the job refused to run and reported "duplicate dates". An automated report that silently counts a day twice is worse than no report, because people trust it. Decide which problems should stop the job, and check for them first.

Part 3: idempotent output

The output file name includes the week, and the job skips the work if that file already exists. The second run reported "skipped, already exists" instead of overwriting the report. This matters because schedulers retry, people rerun jobs by hand, and a report someone has already sent should not change underneath them. If you genuinely need to regenerate it, add an explicit force option.

Part 4: output people can use

The report has a Summary sheet and a Daily sheet, bold headers, a readable column width and dates formatted as days. openpyxl, which reads and writes Excel xlsx files, handles the formatting after pandas writes the data (openpyxl documentation). Small touches decide whether a report gets used or ignored.

For week 40 of 2012 the report showed 45,948 rentals, of which 37,923 were by registered riders, the busiest day on Friday 5 October, and a 13.2% fall from the previous week.

Part 5: check your own output

The job reads the workbook back and prints its sheets and values. In production you would turn that into assertions: expected sheets present, row counts as expected, totals matching the source. Reading back what you wrote catches a surprising number of bugs, from wrong sheet names to formatting that hides values.

Part 6: log, do not print

Every run writes a line to report.log: one when the report was written, one when it was skipped. Python's logging HOWTO explains when to use logging instead of print and how to send it to a file with basicConfig (Python docs: logging HOWTO). When a scheduled job runs at 6 am, the log is the only witness.

Part 7: schedule it

On Windows, Task Scheduler can run the script weekly; the schtasks /create command sets this up from the command line with a schedule type, a task name and the command to run (Microsoft Learn: schtasks create). On Linux or macOS, cron does the same. Whichever you use, run the script with an absolute path to the right Python environment, and set the working directory, because scheduled tasks do not start where you do.

Extending the job without breaking it

Real automation grows. Someone asks for a chart, a second region, an email with the file attached. Add each feature behind the same structure: a new function that takes data in and returns something out, called from run, with its own check. Keep the order of operations the same: validate, compute, write, verify, log. If a new step can fail, such as sending an email, decide whether its failure should stop the job or only be logged, and write that decision down. Finally, keep a small sample input and the expected output next to the script, so that after any change you can rerun it and compare. That habit turns a fragile script into a small, dependable tool that someone else can take over.

A checklist before you trust a script to run alone

• Inputs validated, with clear errors that stop the run.
• Outputs named so reruns are safe.
• A log with one line per meaningful event.
• Paths and settings outside the code.
• The script runs from a scheduler, not only from your editor.
• Someone is told when it fails, by email, chat message or a monitored log.

For quick scripts that handle everyday chores, see automate boring tasks with Python: 10 scripts. For the wider Python learning path, see learn Python from scratch.

Python for data work, including automation, is taught in the Python Programming course.

Sources

• Logging HOWTO (Python documentation) — Python Software Foundation
• openpyxl documentation — openpyxl
• schtasks create (Microsoft Learn) — Microsoft
• Bike Sharing Dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository