Build Derived Columns and Reusable Transformations

Module 4: Combining and Summarizing Data · Lecture 13 of 32

Turn a written metric contract into small, reviewable transformations. Derive monetary columns with vector expressions and reusable functions while keeping the input table intact for comparison and testing.

What you will learn

• Express revenue rules as vectorized transformations.
• Write a function that returns a new result without mutating its input.
• Check row-level invariants before aggregating.

Understand the problem

Translate each clause

The contract says gross equals units times unit price; discounts and refunds reduce merchandise revenue; cancellations contribute zero. Implement these as named intermediate columns so a reviewer can reconcile one row by hand. A dense one-line total hides which part of the contract is wrong when a result disagrees.

Prefer column expressions for column rules

Arithmetic, comparisons, where, mask, and string or datetime methods often describe a rule directly. A row-wise apply is useful when a genuinely irregular operation is required, but it should not be the default for arithmetic that already has a vector form. Compare semantic clarity and measured cost rather than turning “never use apply” into a slogan.

Make dependencies explicit

A reusable function accepts a table and returns a table. It should not depend on an unrelated notebook variable named df or overwrite the raw CSV as a side effect. Small pure transformations are easier to test, compose with pipe, and reuse in a command-line pipeline. Output writing belongs at a deliberate boundary after validation.

Preserve exactness until presentation

Derive gross, discount, refund, and net in integer paise. Ratios and averages can use floating-point values, but record their numerators and denominators. Check nonnegativity and refund bounds at row level. A grand total can still look reasonable when one negative row offsets one inflated positive row, so aggregate reconciliation alone is insufficient.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 13 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

def derive_revenue(frame):
    result = frame.assign(gross_check=frame['units'] * frame['unit_price_paise'])
    after_adjustments = result['gross_check'] - result['discount_paise'] - result['refunded_paise']
    return result.assign(net_check=after_adjustments.mask(result['status'].eq('cancelled'), 0))

orders = load_orders()
before = orders.copy(deep=True)
derived = orders.pipe(derive_revenue)
pd.testing.assert_frame_equal(orders, before)
assert derived['gross_check'].eq(derived['gross_paise']).all()
assert derived['net_check'].eq(derived['net_paise']).all()
assert derived['net_check'].ge(0).all()
print(derived[['order_id', 'gross_check', 'discount_paise', 'refunded_paise', 'net_check']].head(6).to_string(index=False))

Verified output

order_id  gross_check  discount_paise  refunded_paise  net_check
   O0001        19900            1990               0          0
   O0002        39800               0               0      39800
   O0003        59700               0               0      59700
   O0004       119600               0               0     119600
   O0005        29900               0               0      29900
   O0006        59800               0               0      59800

Walk through the result

The first assign creates a named gross value. The second assign uses that value and applies the cancellation policy in one visible expression. pipe passes the table to the function without hiding where it came from. Equality against the shared cleaner verifies that the independent derivation agrees with the established contract. The deep-copy comparison verifies an ownership property rather than merely checking the final total.

Common mistakes and diagnosis

Do not reuse a column name such as revenue for different definitions in different cells. Do not subtract an order-level discount once per item after a line-item join. A function that logs, writes files, mutates a global table, and calculates a ratio is hard to reuse safely; split transformation and delivery responsibilities. Avoid rounding each row into whole rupees before adding the result.

Practice assignment

Add a refund_ratio column defined only for non-cancelled orders with positive discounted gross. Leave the ratio missing when it is undefined. Assert that all observed ratios lie between zero and one.

base_amount = orders['gross_paise'] - orders['discount_paise']
defined = orders['status'].ne('cancelled') & base_amount.gt(0)
ratios = orders.assign(refund_ratio=(orders['refunded_paise'] / base_amount).where(defined))
assert ratios.loc[defined, 'refund_ratio'].between(0, 1).all()
assert ratios.loc[~defined, 'refund_ratio'].isna().all()

Check your understanding

Why retain intermediate columns when the final expression can be written in one line?

Reference and next step

• pandas assign
• pandas pipe

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.