Calculated columns and dates

01The problem

Kolanut's orders file has no revenue column, and its dates are text. Before Bisi can answer "how much did we sell in each quarter?" or "do Saturdays sell more?", she needs two things the raw file doesn't have:

• a revenue figure on every line, after discount, and
• real dates, so she can ask for the year, quarter, month or weekday of each order.

In Excel you'd write a formula in the first row and drag it down 4,266 rows. In pandas you write it once for the whole column.

02The concept

Column arithmetic works on every row at once

orders["revenue"] = orders["quantity"] * orders["unit_price"] * (1 - orders["discount_pct"] / 100)

There's no loop: pandas multiplies the columns row by row for you. This is called vectorised code, and it's both shorter and far faster than looping over rows yourself. Assigning to a name that doesn't exist yet (orders["revenue"]) creates the column; assigning to one that exists replaces it.

Text to dates: pd.to_datetime

orders["order_date"] = pd.to_datetime(orders["order_date"])

After this, orders.info() shows datetime64[ns], and the .dt accessor gives you the parts of each date:

Dates written day-first, like 14/03/2026, need pd.to_datetime(col, dayfirst=True). Dates in other layouts need a format, such as format="%d-%b-%Y" for 14-Mar-2026. Always check a few converted dates against the original text: a day/month mix-up is silent and expensive.

Real dates filter by real ranges

q1_2026 = orders[(orders["order_date"] >= "2026-01-01") & (orders["order_date"] < "2026-04-01")]

Using < "2026-04-01" rather than <= "2026-03-31" is a good habit: it still works when timestamps include a time of day.

Categories from conditions

• Two outcomes: np.where(condition, value_if_true, value_if_false), from the numpy library (imported as np), which pandas is built on.
• Several bands: pd.cut(column, bins=[...], labels=[...]). With bins=[0, 9, 19, 30], a value goes in the band whose range includes it: 1–9, 10–19 or 20–30. By default the left edge is excluded and the right edge included.

Tidy number columns

• .round(2) rounds; .astype(int) converts to whole numbers.
• Money is often stored in whole naira. If a column of whole numbers shows as float64, a missing value is usually the reason.

03Example

Add revenue, convert the dates, and look at revenue by year:

import numpy as np
import pandas as pd

orders = pd.read_csv("https://academy.cloudtechanalytics.com/datasets/sales/orders.csv")

orders["revenue"] = orders["quantity"] * orders["unit_price"] * (1 - orders["discount_pct"] / 100)
orders["order_date"] = pd.to_datetime(orders["order_date"])
orders["year"] = orders["order_date"].dt.year

print(f"Total revenue: ₦{orders['revenue'].sum():,.0f}")
print(f"2025: ₦{orders.loc[orders['year'] == 2025, 'revenue'].sum():,.0f}")
print(f"2026 (Jan-Jun): ₦{orders.loc[orders['year'] == 2026, 'revenue'].sum():,.0f}")

Total revenue: ₦830,541,245
2025: ₦539,810,790
2026 (Jan-Jun): ₦290,730,455

Now size bands for each line, and the weekday:

orders["size"] = pd.cut(orders["quantity"], bins=[0, 9, 19, 30], labels=["Small", "Medium", "Large"])
orders["discounted"] = np.where(orders["discount_pct"] > 0, "Yes", "No")
orders["weekday"] = orders["order_date"].dt.day_name()
orders[["order_date", "weekday", "quantity", "size", "discounted", "revenue"]].head()

  order_date    weekday  quantity    size discounted   revenue
0 2025-01-01  Wednesday        14  Medium         No  260400.0
1 2025-01-01  Wednesday         7   Small         No   92400.0
2 2025-01-01  Wednesday         4   Small         No   14400.0
3 2025-01-01  Wednesday        28   Large        Yes  159600.0
4 2025-01-01  Wednesday        19  Medium        Yes  189525.0

04Walkthrough

1. Load the orders and run the revenue line. Check one row by hand: line 0 is 14 × ₦18,600 with no discount = ₦260,400.
2. Run orders.dtypes before and after pd.to_datetime to see order_date change from object to datetime64[ns].
3. Add the year column and run the Example's three totals.
4. Compare like with like: h1 = orders[orders["order_date"].dt.month <= 6], then h1.groupby("year")["revenue"].sum(). (You'll learn groupby properly in lesson 7.)
5. Add size, discounted and weekday as in the Example.
6. Count lines per size band with orders["size"].value_counts().
7. Try a deliberate mistake: orders["order_date"].dt.year on a fresh copy of the file, before converting. The error, "Can only use .dt accessor with datetimelike values", means the column is still text.

05Practice

06Challenge

07More practice

Optional drills on the legal dataset: Ashgrove Chambers' invoices. Load them with invoices = pd.read_csv("https://academy.cloudtechanalytics.com/datasets/legal/invoices.csv").

08Check your understanding