Building and Automating Recurring Reports with pandas: Scheduling Scripts to Run Without You

Introduction

Picture this: every Monday morning, your manager expects a sales summary report in their inbox. Every morning before 9 AM, someone needs to check whether yesterday's data loaded correctly. Every month, finance needs a reconciliation spreadsheet. Right now, you're that someone — manually running a script, exporting a file, sending an email, and repeating this process until the end of time.

There's a better way. A well-structured Python script, scheduled to run automatically, can handle all of that without you touching a keyboard. The data gets pulled, processed, and delivered while you're asleep, in a meeting, or working on something that actually needs your brain. This isn't advanced engineering — it's a practical skill any data professional can build in an afternoon.

By the end of this lesson, you'll have a complete, working automation pipeline: a pandas script that generates a report, saves output files, and is scheduled to run on its own — on both Windows and Mac/Linux. We'll also cover how to make your scripts robust enough that when something goes wrong at 3 AM, you'll know about it the next morning instead of discovering it when someone complains.

What you'll learn:

• How to structure a pandas script for reliable, repeatable automation
• How to use Python's logging module to capture errors and execution history
• How to parameterize scripts with dynamic dates so they always process the right data
• How to schedule scripts to run automatically using Windows Task Scheduler and cron (Mac/Linux)
• How to send email notifications when a report completes or fails

---

Prerequisites

You should be comfortable loading data into pandas and doing basic transformations. If you haven't yet, read Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data and Grouping and Aggregating in pandas: groupby as the PivotTable Replacement first — we'll be doing both in this lesson.

You'll also want a working Python environment. If you need to set one up, see Setting Up Python for Data Analysis: Python, VS Code, Jupyter, and Virtual Environments.

---

The Problem with Notebook-Based Workflows

Jupyter notebooks are excellent for exploration. You run a cell, look at the output, tweak something, run it again. But notebooks are terrible candidates for automation — they require a human to click "Run All," they don't have built-in error handling, and they're hard to schedule reliably.

For automation, you want a Python script: a plain .py file that runs from top to bottom, produces output, and exits. Think of it like the difference between cooking a meal interactively in the kitchen versus writing a recipe that a catering crew can follow without you present.

The good news is that everything you write in a notebook translates directly to a script. The difference is in structure: instead of scattered cells, you organize code into functions, add error handling, and give the script a clear entry point.

---

Building a Script That's Worth Automating

Let's build something realistic. Suppose you work with daily sales transaction data — a CSV file lands in a folder each night from an upstream system, named by date (e.g., sales_2024_01_15.csv). Your job is to read it, compute a daily summary by product category, flag anything unusual, and save both a summary CSV and a formatted Excel report.

We'll build this up in layers, starting with the core logic and adding robustness as we go.

Step 1: Parameterize With Today's Date

A hard-coded filename like "sales_2024_01_15.csv" is useless in an automated script — it would process the same file every single day. Instead, derive the filename dynamically from today's date (or yesterday's, if the data arrives overnight).

from datetime import date, timedelta
import pandas as pd
import os

# If the script runs in the morning and processes yesterday's data:
report_date = date.today() - timedelta(days=1)
date_str = report_date.strftime("%Y_%m_%d")

data_dir = "/data/sales/"
filename = f"sales_{date_str}.csv"
filepath = os.path.join(data_dir, filename)

print(f"Looking for file: {filepath}")

strftime("%Y_%m_%d") converts a date object into a string like "2024_01_15". The os.path.join() function builds file paths correctly regardless of whether you're on Windows or Mac — it handles the slash direction automatically, which matters when your script runs on a server.

Step 2: Wrap the Core Logic in Functions

Rather than writing a flat script from top to bottom, wrap each logical stage in a function. This makes it easier to test individual pieces, add error handling, and read the code months later when you've forgotten what it does.

import pandas as pd
import os
from datetime import date, timedelta

def load_sales_data(filepath):
    """Load and validate the daily sales CSV."""
    if not os.path.exists(filepath):
        raise FileNotFoundError(f"Expected data file not found: {filepath}")
    
    df = pd.read_csv(filepath, parse_dates=["transaction_date"])
    
    required_columns = {"transaction_date", "category", "revenue", "units_sold"}
    missing = required_columns - set(df.columns)
    if missing:
        raise ValueError(f"Missing expected columns: {missing}")
    
    return df


def build_category_summary(df):
    """Aggregate sales by category."""
    summary = (
        df.groupby("category")
        .agg(
            total_revenue=("revenue", "sum"),
            total_units=("units_sold", "sum"),
            transaction_count=("revenue", "count"),
            avg_order_value=("revenue", "mean"),
        )
        .reset_index()
        .sort_values("total_revenue", ascending=False)
    )
    return summary


def flag_anomalies(df, revenue_threshold=10000):
    """Flag individual transactions with unusually high revenue."""
    df = df.copy()
    df["is_anomaly"] = df["revenue"] > revenue_threshold
    return df

Notice the validation in load_sales_data. When this runs at 3 AM and the upstream file never arrived, you want a clear error message — not a cryptic KeyError three functions later. Checking for the file's existence and the expected columns upfront is called defensive programming, and it's what separates scripts that are actually reliable from scripts that technically work until they don't.

If you need a refresher on how groupby and agg work here, see Grouping and Aggregating in pandas: groupby as the PivotTable Replacement.

Step 3: Save Output Files

Your script needs to produce artifacts that someone (or something) can actually use. Let's save both a summary CSV and a formatted Excel workbook.

def save_outputs(summary_df, raw_df, output_dir, date_str):
    """Save the summary CSV and formatted Excel report."""
    os.makedirs(output_dir, exist_ok=True)
    
    # Save summary CSV
    csv_path = os.path.join(output_dir, f"sales_summary_{date_str}.csv")
    summary_df.to_csv(csv_path, index=False)
    print(f"Summary CSV saved: {csv_path}")
    
    # Save Excel workbook with two sheets
    excel_path = os.path.join(output_dir, f"sales_report_{date_str}.xlsx")
    with pd.ExcelWriter(excel_path, engine="openpyxl") as writer:
        summary_df.to_excel(writer, sheet_name="Category Summary", index=False)
        raw_df.to_excel(writer, sheet_name="Raw Transactions", index=False)
    
    print(f"Excel report saved: {excel_path}")
    return csv_path, excel_path

os.makedirs(output_dir, exist_ok=True) creates the output folder if it doesn't already exist — so the first time the script runs in a new environment, it won't crash because the folder is missing. For more on formatting Excel output, see Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work and Exporting and Sharing Analysis Results: Writing CSV, Excel, and JSON Files from pandas.

---

Adding Logging So You Know What Happened

When a script runs while you sleep, print() statements disappear into the void. Logging is the professional alternative: it writes timestamped messages to a file (and optionally to the console), capturing what happened, when, and at what severity level.

Python's built-in logging module gives you four levels you'll use regularly:

• logging.info() — normal progress messages ("File loaded successfully")
• logging.warning() — something unexpected but not fatal ("File has 0 rows")
• logging.error() — something went wrong but the script continues
• logging.exception() — like error, but also captures the full traceback

import logging
import os
from datetime import date

def setup_logging(log_dir):
    """Configure file and console logging."""
    os.makedirs(log_dir, exist_ok=True)
    log_date = date.today().strftime("%Y_%m_%d")
    log_file = os.path.join(log_dir, f"report_{log_date}.log")
    
    logging.basicConfig(
        level=logging.INFO,
        format="%(asctime)s  %(levelname)s  %(message)s",
        handlers=[
            logging.FileHandler(log_file),
            logging.StreamHandler(),  # also print to console
        ],
    )
    logging.info(f"Logging initialized. Log file: {log_file}")

Call setup_logging() at the very start of your script, before anything else. Now every logging.info("...") call writes a line like:

2024-01-15 06:00:03  INFO  File loaded: 4,218 rows
2024-01-15 06:00:04  INFO  Category summary built: 12 categories
2024-01-15 06:00:05  INFO  Outputs saved successfully

If something breaks, you'll see the exact timestamp and a full stack trace instead of guessing what happened.

---

Sending Email Notifications on Completion or Failure

A report that runs silently is only slightly better than one that doesn't run. Adding a brief email notification — success or failure — closes the loop. Python's smtplib and email modules are built-in and handle this without any extra packages.

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

def send_notification(subject, body, to_address, from_address, smtp_host, smtp_port, smtp_user, smtp_password):
    """Send a plain-text email notification."""
    msg = MIMEMultipart()
    msg["From"] = from_address
    msg["To"] = to_address
    msg["Subject"] = subject
    msg.attach(MIMEText(body, "plain"))
    
    with smtplib.SMTP(smtp_host, smtp_port) as server:
        server.starttls()
        server.login(smtp_user, smtp_password)
        server.send_message(msg)

For Gmail, you'd use smtp_host="smtp.gmail.com" and smtp_port=587. Many organizations have an internal SMTP server you can use without authentication.

---

Putting It All Together: The Main Entry Point

Now we assemble the pieces into a complete script with a main() function and the standard if __name__ == "__main__": guard. This guard means the script only executes when you run it directly — not when another script imports functions from it.

import logging
import os
from datetime import date, timedelta

# (assume all functions defined above are in this file)

def main():
    # Configuration
    DATA_DIR = "/data/sales/"
    OUTPUT_DIR = "/reports/sales/"
    LOG_DIR = "/logs/sales/"
    NOTIFY_EMAIL = "manager@yourcompany.com"

    setup_logging(LOG_DIR)
    
    report_date = date.today() - timedelta(days=1)
    date_str = report_date.strftime("%Y_%m_%d")
    filepath = os.path.join(DATA_DIR, f"sales_{date_str}.csv")
    
    logging.info(f"Starting daily sales report for {report_date}")
    
    try:
        df = load_sales_data(filepath)
        logging.info(f"Data loaded: {len(df):,} rows")
        
        df = flag_anomalies(df)
        anomaly_count = df["is_anomaly"].sum()
        logging.info(f"Anomaly check complete: {anomaly_count} flagged transactions")
        
        summary = build_category_summary(df)
        logging.info(f"Summary built: {len(summary)} categories")
        
        csv_path, excel_path = save_outputs(summary, df, OUTPUT_DIR, date_str)
        
        success_msg = (
            f"Daily sales report for {report_date} completed successfully.\n"
            f"Categories: {len(summary)}\n"
            f"Transactions: {len(df):,}\n"
            f"Anomalies flagged: {anomaly_count}\n"
            f"Files saved to: {OUTPUT_DIR}"
        )
        logging.info(success_msg)
        send_notification(
            subject=f"✅ Sales Report {date_str} — Complete",
            body=success_msg,
            to_address=NOTIFY_EMAIL,
            from_address=os.environ.get("REPORT_FROM_EMAIL"),
            smtp_host="smtp.yourcompany.com",
            smtp_port=587,
            smtp_user=os.environ.get("SMTP_USER"),
            smtp_password=os.environ.get("SMTP_PASSWORD"),
        )

    except Exception as e:
        logging.exception(f"Report failed: {e}")
        send_notification(
            subject=f"❌ Sales Report {date_str} — FAILED",
            body=f"The daily sales report failed with the following error:\n\n{e}\n\nCheck the log at {LOG_DIR}",
            to_address=NOTIFY_EMAIL,
            from_address=os.environ.get("REPORT_FROM_EMAIL"),
            smtp_host="smtp.yourcompany.com",
            smtp_port=587,
            smtp_user=os.environ.get("SMTP_USER"),
            smtp_password=os.environ.get("SMTP_PASSWORD"),
        )


if __name__ == "__main__":
    main()

The try/except Exception block wraps the entire pipeline. If anything fails — missing file, bad data, network timeout — the exception is caught, logged with a full traceback, and an alert email goes out. The script exits cleanly instead of hanging or crashing silently.

---

Scheduling the Script to Run Automatically

Now that the script works, let's make it run without you.

On Windows: Task Scheduler

Windows Task Scheduler is a built-in tool that runs programs on a schedule. Here's how to set it up:

1. Press the Windows key, type "Task Scheduler," and open it.
2. In the right-hand Actions panel, click Create Basic Task.
3. Give the task a name like "Daily Sales Report" and a description, then click Next.
4. Choose your trigger — for a daily report, select Daily, then set the time (e.g., 6:00 AM) and click Next.
5. Choose Start a program as the action and click Next.
6. In the Program/script field, enter the full path to your Python executable. Find this by opening a command prompt and typing where python — copy the result (e.g., C:\Users\YourName\AppData\Local\Programs\Python\Python312\python.exe).
7. In Add arguments, enter the full path to your script: C:\reports\sales_report.py.
8. In Start in, enter the directory containing your script: C:\reports\.
9. Click Finish.

One important detail: the Python that Task Scheduler uses must be the one where your packages (pandas, openpyxl, etc.) are installed. If you're using a virtual environment, point to the Python executable inside it — something like C:\projects\myenv\Scripts\python.exe.

On Mac/Linux: cron

On Mac and Linux, cron is the standard tool for scheduling. It's configured through a file called the crontab (cron table).

Open your crontab for editing by running this in a terminal:

crontab -e

This opens a text editor. Each line is a scheduled job with five time fields followed by the command to run:

# MIN  HOUR  DAY  MONTH  WEEKDAY  COMMAND
  0    6     *    *      *        /usr/bin/python3 /home/user/reports/sales_report.py

This runs the script every day at 6:00 AM. The * means "every" — so * * * on days, months, and weekdays means every day of every month.

Common cron patterns:

0 6 * * *       # Daily at 6:00 AM
0 6 * * 1       # Every Monday at 6:00 AM (1 = Monday)
0 6 1 * *       # First of every month at 6:00 AM
*/15 * * * *    # Every 15 minutes

To redirect output to a log file from within cron (as a backup to your logging module):

0 6 * * * /home/user/venv/bin/python3 /home/user/reports/sales_report.py >> /home/user/logs/cron_output.log 2>&1

The >> logfile 2>&1 part appends both standard output and error output to the log file.

---

Hands-On Exercise

Build an automated weekly summary script using this scenario:

You have a CSV file at /data/transactions.csv with columns: date, region, product, revenue, returns.

Your script should:

1. Load the file and filter to only the previous week's transactions (Monday through Sunday). Use date.today() and timedelta to compute these boundaries. Working with date filtering in pandas is covered in Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows.

2. Compute a summary grouped by region and product showing total revenue, total returns, and net revenue (revenue minus returns).

3. Save the output as weekly_summary_YYYY_WW.csv where WW is the ISO week number. (Hint: date.today().strftime("%Y_%W") gives you the week number.)

4. Add logging so each step records a timestamped message.

5. Wrap everything in a try/except block so failures are caught and logged.

6. Schedule the script to run every Monday at 7:00 AM using either Task Scheduler or cron.

Test it by running it manually first and checking that the output file appears and the log shows all expected messages.

---

Common Mistakes & Troubleshooting

"The script works when I run it manually but fails in Task Scheduler/cron." Almost always a path issue. Scheduled tasks don't inherit your shell environment. Use absolute paths everywhere — for input files, output files, log files, and your Python executable. Print os getcwd() at the top of your script during debugging to see where it thinks it's running from.

"pandas isn't found when the scheduler runs it." Your scheduler is using a different Python than the one where you installed pandas. Confirm by adding import sys; logging.info(sys.executable) at the top of your script — this logs which Python binary is running.

"The log file is empty even though the script ran." If you set up logging after an early crash, nothing gets written. Make setup_logging() the very first call in main(), before any imports that might fail or any file operations.

"My email notifications are working locally but fail in the scheduler." Environment variables (like SMTP_PASSWORD) aren't automatically available to scheduled tasks. On Windows, set them as System Environment Variables through Control Panel → System → Advanced System Settings → Environment Variables. On Linux, add them to /etc/environment or explicitly export them in the cron environment using VARNAME=value lines at the top of your crontab.

"The script processes the same data it processed yesterday." Check your date logic. If report_date = date.today() and the data file is named with today's date but doesn't exist yet because it arrives at 9 AM, your script (running at 6 AM) will crash — or worse, if you have fallback logic, it'll silently use yesterday's file again. Be explicit: log which date you're computing and what filepath you're looking for.

---

Summary & Next Steps

You've built something genuinely useful here. You now know how to:

• Structure a pandas pipeline as a proper script with functions and a main() entry point
• Dynamically parameterize inputs with today's date so the script always processes fresh data
• Add logging that writes a durable audit trail to disk
• Wrap the pipeline in error handling that notifies you when things break
• Schedule the script to run automatically on Windows (Task Scheduler) and Mac/Linux (cron)

The next natural step is making your scripts more reusable and maintainable. Structuring a Reusable Data Analysis Project: Functions, Modules, Notebooks, and Scripts covers how to split scripts across multiple files, manage configuration cleanly, and build pipelines you can actually maintain. And if your reports draw from a database rather than CSV files, Reading from SQL Databases into pandas with SQLAlchemy will show you how to pull fresh data directly from the source.

Automation is a force multiplier. A report you build once and schedule correctly keeps delivering value every day without costing you another minute.