Lab 7 reference — Summit Gear AR in Python

Keep this open beside your Lab 7 notebook. Colab cannot show code in color; this page can.

This page is your companion to the Lab 7 Colab notebook. It shows the code in color and lists every function's specification in one place. Do the work in the notebook; read the color here.

The standard: what a complete function looks like

Every function in the lab is graded for completeness. A complete function has a clear name, well-named inputs and variables, a docstring, and returns the right value:

def amount_with_tax(amount, tax_rate):
    """Return the amount grossed up by the tax rate (0.0725 means 7.25%)."""
    grossed_up = amount * (1 + tax_rate)
    return grossed_up

Part 1 — your eight functions

For each, the stub is given to you. Replace the empty docstring with one sentence and return None with the real body.

1. is_number(value)

Returns True if the value is an int or float, else False.   Example: is_number(5) → True, is_number("5") → False.
def is_number(value):
    """ """
    return None

2. aging_bucket(days_past_due)

Returns the aging label for a days-past-due value. Labels (zero or less is Not due): Not due, 0-30, 31-60, 61-90, 91-120, 121-150, 151-180, 181+.   Example: aging_bucket(45) → "31-60". Boundaries: 30 is 0-30, 31 starts 31-60.
def aging_bucket(days_past_due):
    """ """
    return None

3. running_total(amounts)

Returns the total of a list, built with a for loop and += (not sum()).   Example: running_total([10, 20, 30]) → 60. An empty list returns 0, not None.
def running_total(amounts):
    """ """
    return None

4. line_total(qty, price) and amount_with_tax(amount, tax_rate)

line_total returns qty * price — line_total(2, 600.0) → 1200.0.
amount_with_tax returns amount * (1 + tax_rate) — amount_with_tax(100, 0.0725) → 107.25.
def line_total(qty, price):
    """ """
    return None

def amount_with_tax(amount, tax_rate):
    """ """
    return None

5. grand_total(pairs)

Returns the total of line_total(qty, price) over a list of (qty, price) tuples.   Example: grand_total([(2, 10.0), (3, 5.0)]) → 35.0.
def grand_total(pairs):
    """ """
    return None

6. total_amount(df)

Returns the sum of the amount column of an invoices DataFrame (df["amount"].sum()).   Example: total_amount(sample_ar) → 12200.0.
def total_amount(df):
    """ """
    return None

7. safe_line_total(qty, price)

Returns qty * price when both are numbers; None if either is not.   Example: safe_line_total(7, 12.5) → 87.5, safe_line_total("oops", 5) → None.
def safe_line_total(qty, price):
    """ """
    return None

Part 2 — the real ledger

large_invoices(df, threshold)

Returns the rows where amount is strictly greater than threshold — a boolean mask.
def large_invoices(df, threshold):
    """ """
    return None

total_by_segment(df)

Returns total amount per segment — the PivotTable, in one line.
def total_by_segment(df):
    """ """
    return None

Validate your aging against the live database

After you rebuild the aging in pandas, run the equivalent SQL on the Week 3 SQL workbench and confirm the bucket totals match your aging_pandas, bucket for bucket.

SELECT
  CASE
    WHEN CAST(julianday('2024-09-30') - julianday(due_date) AS INT) <= 0   THEN 'Not due'
    WHEN CAST(julianday('2024-09-30') - julianday(due_date) AS INT) <= 30  THEN '0-30'
    WHEN CAST(julianday('2024-09-30') - julianday(due_date) AS INT) <= 60  THEN '31-60'
    WHEN CAST(julianday('2024-09-30') - julianday(due_date) AS INT) <= 90  THEN '61-90'
    WHEN CAST(julianday('2024-09-30') - julianday(due_date) AS INT) <= 120 THEN '91-120'
    WHEN CAST(julianday('2024-09-30') - julianday(due_date) AS INT) <= 150 THEN '121-150'
    WHEN CAST(julianday('2024-09-30') - julianday(due_date) AS INT) <= 180 THEN '151-180'
    ELSE '181+'
  END AS aging_bucket,
  ROUND(SUM(amount), 2) AS total_receivable
FROM invoices
GROUP BY aging_bucket
ORDER BY total_receivable DESC;

The Excel bridge

Excel movePython you write this lab
=B2*C2 (a named formula)def line_total(qty, price): return qty * price
running-total column, drag downrunning += a in a for loop
=SUM(D2:D260)df["amount"].sum()
=IF(D2>10000, ...) then filterdf[df["amount"] > 10000]
XLOOKUP customer onto invoicedf.merge(customers, on="customer_id")
PivotTable summing by segmentdf.groupby("segment")["amount"].sum()
Week 5 aging bucketaging_bucket(days) (the same labels)
Remember: do the work in the notebook. This page is just the color and the spec. Try each function by hand first; use AI to explain, not to hand you a line you cannot read back.

Week 7 of ACCTG 5150, Accounting Analytics. Code colorized with highlight.js.