Skip to main content

Class 9 - Adding columns

Date: September 28, 2026

⚠️Homework
Homework 3 due October 4, 2026
🆕New Skills
First week for skills: 1, 2, 10, 11

Reference

For all work with tables, refer to the Tables page in the menu at the top of the page!

Skills: 0, 1, 3​

Notes (review before class)​

Adding Columns: add_column​

add_column copies a table with a new column at the right end, given the column's name and a function from a row to the column's value, which it calls on every row:

employees = table(
    ["name", "hourly-wage", "hours-worked"],
    [
        ["Harley", 15, 40],
        ["Obi", 20, 45],
        ["Anjali", 18, 39],
        ["Miyako", 18, 40],
    ],
)

def compute_wages(r: dict) -> float:
    """compute total wages based on wage and hours worked"""
    return r["hourly-wage"] * r["hours-worked"]

def test_compute_wages():
    assert compute_wages(employees.row(0)) == 15 * 40
    assert compute_wages(employees.row(2)) == 18 * 39

employees.add_column("total-wage", compute_wages)
  • The helper takes the whole row, not compute_wages(15, 40), so add_column can call it on each row, rather than our calling it in every row.

Changing a Column: transform_column​

transform_column is given an existing column's name, and a function from a value in that column to its new value:

def new_rate(rate: float) -> float:
    """raise rates under 20 by 10%"""
    if rate < 20:
        return rate * 1.1
    else:
        return rate

def test_new_rate():
    assert new_rate(20) == 20
    assert new_rate(10) == 11
    assert new_rate(0) == 0

def give_raises(t: Table) -> Table:
    """give a 10% raise to anyone making under 20"""
    return t.transform_column("hourly-wage", new_rate)
  • The type annotation for tables is Table.
  • Like all table operations, it makes a new table, leaving the original intact (try give_raises(employees), then look at Miyako's wage in employees), so a mistake can't lose the original. Name the new table you want.

Libraries, **, and pytest.approx​

  • A library is a collection of extra functions: import it at the top of the file (after the #level line), then use its name and a dot: math.sqrt(9) is 3.0. More in Class 13.
  • ** is exponentiation: n ** 2 is n squared.
  • floats are mostly approximate (Class 1), so results like math.sqrt(2) may be very slightly off. Test them with pytest.approx, which checks that a number is very close to what we expect:
import math
import pytest

def test_sqrt_approx():
    assert math.sqrt(2) ** 2 != 2  # it's 2.0000000000000004
    assert math.sqrt(2) ** 2 == pytest.approx(2)

Recap (15 mins)​

In a fantasy game, a table holds the items near the player, with coordinates measured from the player, at the origin. Read through the program, which finds how far away each item is (the player can pick up close ones), and then moves the player:

import math
import pytest

items = table(
    ["item", "x-coordinate", "y-coordinate"],
    [
        ["Sword of Dawn",           23,  -87],
        ["Healing Potion",         -45,   12],
        ["Dragon Shield",           78,  -56],
        ["Magic Staff",             -9,   64],
        ["Elixir of Strength",      51,  -33],
        ["Cloak of Invisibility",  -66,    5],
        ["Ring of Fire",            38,  -92],
        ["Boots of Swiftness",     -17,   49],
        ["Amulet of Protection",    82,  -74],
        ["Orb of Wisdom",          -29,  -21],
    ],
)

def calc_distance(r: dict) -> float:
    """finds distance to origin from fields 'x-coordinate' and 'y-coordinate'"""
    return math.sqrt(r["x-coordinate"]**2 + r["y-coordinate"]**2)

def test_calc_distance():
    assert calc_distance(items.row(0)) == pytest.approx(math.sqrt(23**2 + (-87)**2))
    assert calc_distance(items.row(3)) == pytest.approx(math.sqrt((-9)**2 + 64**2))

items_with_dist = items.add_column("distance", calc_distance)

# the player moves 1 in the increasing x direction,
# so every item's x-coordinate is 1 smaller
def subtract_1(n: float) -> float:
    """subtracts 1 from input"""
    return n - 1

def test_subtract_1():
    assert subtract_1(10) == 9
    assert subtract_1(0) == -1
    assert subtract_1(-3.5) == -4.5

moved_items = items.transform_column("x-coordinate", subtract_1)
  • How many columns does items_with_dist have? How many times does add_column call calc_distance?
  • Why do the tests of calc_distance use pytest.approx, but the tests of subtract_1 don't?
  • What is moved_items.row(1)["x-coordinate"]? What is items.row(1)["x-coordinate"], after moved_items is made?

Class Exercise (40 mins)​

  1. Imagine all the items were pulled closer to the player by scaling both the x and y coordinates down by 10% (this doesn't make the distance 10% less, but is simpler). Create a new table that has new x & y coordinates for all the items.
  2. Extract the name of the item that is closest to the player.
  3. You want to "obfuscate" the list of items, displaying, rather than the name, a string that is a sequence of "X"s of the same length. i.e., "Sword of Dawn" becomes "XXXXXXXXXXXXX". Create a new table that is so transformed.
  4. Get a dataset from the City of Boston Employee Earnings Report, and load it via the CSV url.
  5. Calculate the total earnings excluding the "DETAIL" column, since that is private compensation (police & fire employees hired for private events). You might notice, in doing this, that the columns are not numbers, they are strings! With commas! Furthermore, some of the data has no values for certain rows which are loaded as empty strings "". That means float by itself won't work: it causes an error for strings like "1,234" or "". For now you can use transform_column with this helper (you don't need to understand how try and except work yet: float causes an error, called a ValueError, when given a string that isn't a number, and try / except ValueError means "if that error happens, do this instead"): 
    def earnings_to_number(s: str) -> float:
        """Converts a possibly comma-formatted earnings string to a number, using 0 if empty or invalid"""
        try:
            return float(s.replace(",", ""))
        except ValueError:
            return 0

    def test_earnings_to_number():
        assert earnings_to_number("1234") == 1234
        assert earnings_to_number("1,234") == 1234
        assert earnings_to_number("-1.3") == -1.3
        assert earnings_to_number("hello") == 0
  6. Now sort by your new total compensation to see the highest paid employees in the city. Sort the other way to see who is the lowest paid.

Wrap-up (5 mins)​

  • Transforming data is key to programming with tables. The original source of data may not have what you need in the right format, but we can use operations like add_column or transform_column to create new tables that do.