What a Great Automation Script Walkthrough Actually Sounds Like

'This script replaced a weekly manual job. Every Monday,
someone downloaded sales files from each branch and
copied them into one Excel sheet, which was slow and
error-prone.

Now Task Scheduler runs the script early on Monday.
It reads every branch CSV in a shared folder, cleans
and combines them, and writes one Excel file with a
summary by branch and product, plus a log of what it
processed.

I will go through it in the order the data flows.'

'So first I import pandas as pd. Then I import
glob. Then I have a variable called path. Then there
is a for loop. In the loop I read the CSV. Then I
append it to a list. Then I concat the list. Then I
rename columns...'

(After a minute, the interviewer still does not know
what problem the script solves or what it produces.)

from pathlib import Path
from datetime import date
import logging
import pandas as pd

IN_DIR = Path('branch_files')
OUT = Path(f'weekly_sales_{date.today():%Y_%m_%d}.xlsx')
REQUIRED = {'date', 'product', 'qty', 'price'}
logging.basicConfig(filename='weekly_sales.log', level=logging.INFO)

def load_branch_files():
    frames = []
    for f in sorted(IN_DIR.glob('*.csv')):
        df = pd.read_csv(f)
        df.columns = df.columns.str.strip().str.lower()
        missing = REQUIRED - set(df.columns)
        if missing:
            raise ValueError(f'{f.name} missing {missing}')
        df['branch'] = f.stem
        frames.append(df)
    if not frames:
        raise FileNotFoundError('No branch files found')
    return pd.concat(frames, ignore_index=True)

def clean(df):
    df['product'] = df['product'].str.strip().str.title()
    df['date'] = pd.to_datetime(df['date'], errors='coerce')
    df = df.dropna(subset=['date'])
    df['revenue'] = df['qty'] * df['price']
    return df

def write_report(df):
    summary = df.pivot_table(index='branch', columns='product',
                             values='revenue', aggfunc='sum',
                             fill_value=0)
    with pd.ExcelWriter(OUT) as xl:
        summary.to_excel(xl, sheet_name='Summary')
        df.to_excel(xl, sheet_name='Detail', index=False)

if __name__ == '__main__':
    data = clean(load_branch_files())
    write_report(data)
    logging.info('rows=%d revenue=%.2f', len(data), data['revenue'].sum())

'The script has three steps: load, clean, write.

Load looks in the branch_files folder for every CSV.
I used pathlib's glob, so a new branch just means a new
file; no code change. Each file's columns are lowercased
and stripped because branches typed headers differently.
If a file is missing a required column, the script stops
and names the file. The file name becomes the branch.

Clean fixes product name spacing and case, parses dates
and calculates revenue as quantity times price.

Write builds a pivot of revenue by branch and product
for the Summary sheet and keeps all rows in Detail, so a
manager can check any total. Finally it logs the row
count and total revenue for that run.'

Interviewer: Why stop the whole run for one bad file?

Candidate: I considered skipping it and continuing.
But the summary would then show all other branches,
and nobody would notice one was missing. A wrong but
complete-looking report is worse than no report.
Stopping with the file name in the error means someone
fixes that file and reruns, and the summary is always
complete when it exists.

'How I know it worked: the log records row count
and total revenue each run, and I compared the first
few runs with the old manual sheet until they matched.

Limits: it drops unreadable dates without counting
them, and it assumes prices are already in rupees.

Next: log the number of dropped rows, add a check that
every expected branch file is present, and email the
file automatically instead of saving to a folder.'

SIX-PART WALKTHROUGH OUTLINE

Purpose (manual task replaced): ___________
Who benefits: ____________________________
Trigger (schedule / manual / event): _____
Input (files, source, format): ___________
Process (3 steps max): ___________________
   1. ______  2. ______  3. ______
Output (file, table, email): _____________
Safeguards (what if input is bad): _______
How I know it worked: ____________________
Limits: __________________________________
Next improvement: ________________________

DECISIONS I CAN DEFEND

Block: ___________
I chose: ___________
Instead of: ___________
Because: ___________

Block: ___________
I chose: ___________
Instead of: ___________
Because: ___________

Prepare at least three. These are your zoom-in answers.

REHEARSAL STEPS

1. Say the overview without looking at code.
   Target: about one minute.
2. Open the code and narrate each block in data
   flow order, not line order.
3. Pick one block and explain its main decision
   and the alternative you rejected.
4. Close with: how I know it worked, limits, next.
5. Record. Count how many times you read a line
   of code aloud. Aim for almost none.