Class 26 - Pandas basics (creating, loading, accessing)

Date: November 16, 2026

⚠️Homework

Homework 10 due November 29, 2026

‼️Ending Skills

Last week for skills: 6, 7, 8

🆕New Skills

First week for skills: 9

Reference

For all work with tables, refer to the Tables page in the menu at the top of the page! It covers PLL tables, but is good reference regardless.

Skills: None

Notes (review before class)

Pandas is a popular Python library for tables. Of its many ways to do things, these notes use the ones that match PLL tables. Start your file with (the first run may take a while, as PLL loads pandas):

import pandas as pd

DataFrames and Series

DataFrame: pandas' name for a table, with rows and columns.
Array: a sequence of values accessed by position (0, 1, ..., up to one less than its length). Ordered, like a list, but created with a limit on how many elements it holds.
Series: an array whose positions can also have labels.
A row is a Series whose values are labeled with the column names (like a PLL row, a dict from column names to values); a DataFrame is a series of rows.

Creating and Loading DataFrames

By hand, from a dictionary that maps column names to lists of values (column by column, as with PLL's table_from_columns, rather than row by row):

data = {
    "day": ["mon", "tues", "wed", "thurs", "fri"],
    "max temp": [58, 62, 55, 59, 64],
}
temps = pd.DataFrame(data)

From a CSV file, with read_csv and its file name or web address. These are the event ticket orders from Class 11, with new column names:

EVENTS_URL = "https://raw.githubusercontent.com/neu-pdi/cs2000-public-resources/refs/heads/main/static/support/dcic-events-orig.csv"
events = pd.read_csv(EVENTS_URL, header=0,
                     names=["name", "email", "numtix", "discount", "delivery", "zip"])

header=0: the headers are in the first row (numbered 0). Use header=None if the file has no header row.
names: the column labels to use instead of the file's headers (leave it out to keep them).
Unlike load_table, which reads every value as a string, read_csv converts columns that contain only numbers (like zip) to numbers.

Evaluating events shows:

               name             email numtix  discount delivery    zip
      Josie Zhao       jo@mail.com      2  BIRTHDAY    email  90210
      Sam Ochibe        s@sweb.com      1       NaN   pickup  30301
     Bart Simple  bart@simpson.org      5   STUDENT      yes  60614
  Ernie O'Malley    ernie.mail.com      0      none    email  10001
Alvina Velasquez   alvie@schooledu      3   student    emall  73301
          Zander          zandaman     10       NaN    email   2115
Shweta Chowpatti      snc@this.org  three             pickup   2903

Column labels are across the top. We gave no row labels, so pandas labeled the rows with numbers (on the left). Here a row's label is its position, but that is not always so.
NaN, in some blank discount cells, is the standard Python value for missing information.

Accessing Rows, Columns, and Cells

By label, or by (numeric) position:

events["numtix"]          # the numtix column, as a Series
events["numtix"][2]       # the value in the numtix column, row 2
events.loc[6]             # the row with label 6
events.loc[6]["numtix"]   # the value in the row with label 6, numtix column
events.iloc[6]            # the row at position 6

.loc gets a row by its label; .iloc by its position (i for "integer").
Rows and columns always have positions, and may have labels.
A Series has statistics operations, like .sum() and .mean() (for PLL, t.sum("col") and t.mean("col")).

Filtering Rows with Masks

PLL's filter takes a function from a row to a bool. In pandas, we give a mask: an array of booleans, one per row, and get the rows whose entries are True:

keep = [True, False, True, False, False, False, True]
events[keep]

In square brackets, a column label selects a column; a mask selects rows (by position).
The result keeps the original row labels (0, 2, and 6), while its positions are, as always, 0, 1, 2. (Try: does events itself change?)

We compute masks, rather than writing them by hand, with an expression over a Series:

events[events["delivery"] == "email"]

Lifting: applying an operation to every element of an array. events["delivery"] == "email" doesn't ask whether a Series equals a string: it compares each element to "email", making a new Series of booleans.
The inner events makes the mask; the outer one filters with it.

Cleaning Data

Lifting also normalizes data (as on Class 11), e.g. making all discount codes lowercase:

events["discount"] = events["discount"].str.lower()

lower() is a string operation, not a Series one: .str says to apply the string operation to each element (for one string, just "BIRTHDAY".lower()).
Assigning to events["discount"] replaces the column. Unlike PLL's transform_column, which makes a new table, this mutates events (as on Class 25).

To make every unknown code (none, NaN, the space) the empty string, mask the rows without a known code, then set their discount:

codes = ["birthday", "student"]          # the valid codes
mask = ~events["discount"].isin(codes)   # rows with INVALID codes
events.loc[mask, "discount"] = ""

isin(codes), lifted, checks whether each value is in the list; ~ swaps True and False in a mask.
To update a column in some rows: make a mask of the rows, select the cells with .loc[mask, column], and use =. (Try: change all delivery values of "yes" to "pickup".)
Gotcha: events[mask]["discount"] = "" gives a SettingWithCopyWarning, and events doesn't change (a subtlety of how pandas updates data).

Repairing Values and Column Types

A row label works in place of a mask. To fix the "three" in the last row:

events.loc[6, "numtix"] = 3

events["numtix"].sum() still fails: since numtix had text in it, read_csv read all its values as strings (pandas shows them without quotation marks, so they look like numbers), and the repair changed only one:

type(events["numtix"].loc[0])   # str
type(events["numtix"].loc[6])   # int, for the corrected value

astype converts the values of a Series (like transform_column with int):

events["numtix"] = events["numtix"].astype("int")
events["numtix"].sum()   # now this works

Recap (20 mins)

A restaurant records the date, dish, quantity, and order type of each order. Here is work we did with PLL tables, done in pandas, with the PLL version in the comments:

import pandas as pd

ORDERS_URL = "https://raw.githubusercontent.com/neu-pdi/cs2000-public-resources/refs/heads/main/static/support/restaurant_orders.csv"

# PLL: table(["date", "dish", "quantity", "order_type"],
#            [["2023-07-01", "Pasta", 2, "dine-in"],
#             ["2023-07-01", "Salad", 1, "takeout"],
#             ["2023-07-02", "Burger", 3, "dine-in"]])
first_orders = pd.DataFrame({
    "date": ["2023-07-01", "2023-07-01", "2023-07-02"],
    "dish": ["Pasta", "Salad", "Burger"],
    "quantity": [2, 1, 3],
    "order_type": ["dine-in", "takeout", "dine-in"],
})

# PLL: load_table(ORDERS_URL)
orders = pd.read_csv(ORDERS_URL)

quantities = orders["quantity"]   # a Series; PLL: orders.column("quantity"), a list
total_dishes = quantities.sum()   # PLL: orders.sum("quantity")

def dish_at(t: pd.DataFrame, pos: int) -> str:
    """the dish ordered in the row at position pos of t"""
    return t.iloc[pos]["dish"]    # PLL: t.row(pos)["dish"]

def average_quantity(t: pd.DataFrame) -> float:
    """the average number of dishes in each order in t"""
    return t["quantity"].mean()   # PLL: t.mean("quantity")

def test_dish_at():
    assert dish_at(first_orders, 0) == "Pasta"
    assert dish_at(first_orders, 2) == "Burger"

def test_average_quantity():
    assert average_quantity(first_orders) == 2.0

What is dish_at(first_orders, 1)? What is first_orders["quantity"][2]?
The tests use first_orders, not orders. Why is that a better choice?
Let big = first_orders[first_orders["quantity"] >= 2]. Which rows does big have? Which of dish_at(big, 1) and big.loc[1]["dish"] works, and why?

Class Exercises (35 mins)

Creating and loading tables

Create a DataFrame manually with workout data: columns date, activity, duration. Make at least 5 rows.
Load the CSV from https://raw.githubusercontent.com/neu-pdi/cs2000-public-resources/refs/heads/main/static/support/7-photos.csv into a DataFrame. Print the first 5 rows. If you get URL errors, paste these lines at the top of your file:

import ssl
ssl._create_default_https_context = ssl._create_unverified_context

Accessing data

Get the second row from your workout DataFrame (remember: Python uses 0-based indexing).
Extract the activity column and print all unique activity names.
Get the duration value from the third workout (combining row and column access).
What happens if you try to access a row that doesn't exist? Try it and note the error.
What happens if you try to access a column that doesn't exist? Try it and note the error.

Extracting columns and statistics

Extract the duration column from your workout DataFrame and store it in a variable called durations.
Work with the durations Series to find: .mean(), .sum(), .max(), .min(), .median().
Calculate the range (difference between max and min) of workout durations.

Wrap-up (5 mins)

Pandas DataFrames provide the same basic table operations you learned with PLL tables on Class 7: creating tables, loading from CSV, accessing rows/columns, and computing statistics.
The syntax is different, but the concepts are identical. Tomorrow we'll see filtering, sorting, and column operations.

Skills: None​

Notes (review before class)​

DataFrames and Series​

Creating and Loading DataFrames​

Accessing Rows, Columns, and Cells​

Filtering Rows with Masks​

Cleaning Data​

Repairing Values and Column Types​

Recap (20 mins)​

Class Exercises (35 mins)​

Creating and loading tables​

Accessing data​

Extracting columns and statistics​

Wrap-up (5 mins)​