pandas Fundamentals: Series and DataFrames

4.1 Setup (20 min)

Step 1 — Activate your environment and install packages

cd /workspaces/macro-data-course
source .venv/bin/activate
pip install pandas pyarrow matplotlib ipykernel
pip freeze > requirements.txt

• pandas: the data-frame library
• pyarrow: lets pandas read and write Parquet files (used by the course notebook)
• matplotlib: plotting (Day 7)
• ipykernel: lets VS Code run Jupyter notebooks with your .venv

Step 2 — Optional: Jupyter notebooks in Codespaces

Notebooks are great for exploration; scripts are better for pipelines. You can use both:

1. Create day04/explore.ipynb.
2. Click Select Kernel (top right) → Python Environments → choose .venv.
3. Type code into a cell and press Shift+Enter.

In a notebook, the last line of a cell displays automatically, so you can type df instead of print(df). The examples below use print() so they work in both.

Step 3 — Download the course data

Create get_course_data.py in the repository root. You don't need to understand all of it yet. On Days 8–10 we rebuild it properly. It uses the same endpoints as the course notebook.

"""get_course_data.py — downloads the raw files used on Days 4–7.

Run once from the repository root:  python get_course_data.py
"""
import io
import os
import zipfile

import pandas as pd
import requests

os.makedirs("data", exist_ok=True)
START = "2006-01-01"

# ── 1. Bank of Canada Valet: one file per series, values kept as raw text ─────
BOC_SERIES = {
    "V39079": "boc_overnight.csv",
    "FXCADUSD": "boc_cad_usd.csv",
    "BD.CDN.2YR.DQ.YLD": "boc_yield_2yr.csv",
    "BD.CDN.10YR.DQ.YLD": "boc_yield_10yr.csv",
}
for code, filename in BOC_SERIES.items():
    url = f"https://www.bankofcanada.ca/valet/observations/{code}/json?start_date={START}"
    obs = requests.get(url, timeout=30).json()["observations"]
    rows = [{"date": o["d"], "value": o.get(code, {}).get("v", "")} for o in obs]
    pd.DataFrame(rows).to_csv(f"data/{filename}", index=False)
    print(f"✓ {filename}: {len(rows)} rows")

# ── 2. FRED: saved exactly as downloaded ─────────────────────────────────────
FRED_SERIES = {"FEDFUNDS": "fred_fedfunds.csv", "DCOILWTICO": "fred_wti.csv"}
for code, filename in FRED_SERIES.items():
    url = f"https://fred.stlouisfed.org/graph/fredgraph.csv?id={code}"
    text = requests.get(url, timeout=30).text
    with open(f"data/{filename}", "w") as f:
        f.write(text)
    print(f"✓ {filename}")


# ── 3. Statistics Canada: full table download, then keep a small subset ──────
def statcan_table(table_id):
    meta_url = ("https://www150.statcan.gc.ca/t1/wds/rest/"
                f"getFullTableDownloadCSV/{table_id}/en")
    meta = requests.get(meta_url, timeout=30).json()
    zipped = requests.get(meta["object"], timeout=180)
    with zipfile.ZipFile(io.BytesIO(zipped.content)) as z:
        name = [f for f in z.namelist()
                if f.endswith(".csv") and not f.endswith("_MetaData.csv")][0]
        df = pd.read_csv(z.open(name), low_memory=False, encoding="utf-8-sig")
    df.columns = df.columns.str.strip()
    return df


print("Downloading StatCan CPI (large file, please wait)...")
cpi = statcan_table("18100004")
keep = ["All-items", "Food", "Shelter", "Transportation",
        "Energy", "Clothing and footwear"]
cpi = cpi[(cpi["GEO"] == "Canada") &
          (cpi["Products and product groups"].isin(keep))]
cpi[["REF_DATE", "GEO", "Products and product groups", "VALUE"]].to_csv(
    "data/statcan_cpi.csv", index=False)
print(f"✓ statcan_cpi.csv: {len(cpi)} rows")

print("Downloading StatCan GDP...")
gdp = statcan_table("36100104")
gdp = gdp[(gdp["Seasonal adjustment"] == "Seasonally adjusted at annual rates") &
          (gdp["Estimates"] == "Gross domestic product at market prices") &
          (gdp["Prices"] == "Chained (2017) dollars")]
gdp[["REF_DATE", "VALUE"]].to_csv("data/statcan_gdp.csv", index=False)
print(f"✓ statcan_gdp.csv: {len(gdp)} rows")

Run it:

python get_course_data.py

Then add data/ to your .gitignore, since raw downloads don't belong in Git:

.venv/
__pycache__/
*.pyc
data/

If a download fails (APIs occasionally time out), run the script again. If it keeps failing, ask your instructor for a zipped copy of the data/ folder.

4.2 Series: a Single Time Series (23 min)

A Series is a column of values with labels, called the index. Create day04/first_series.py.

Example 1 — From a list

import pandas as pd

rates = pd.Series([4.75, 4.50, 4.25, 3.75, 3.25])
print(rates)

Output:

0    4.75
1    4.50
2    4.25
3    3.75
4    3.25
dtype: float64

The left column is the index (0–4 by default); the right column holds the values.

Example 2 — Built-in statistics

print("Mean:", rates.mean())
print("Max:", rates.max())
print("Std dev:", rates.std())
print(rates.describe())

Example 3 — Vectorised arithmetic (no loops!)

# Day 2 way:
# rates_bp = []
# for r in rates:
#     rates_bp.append(r * 100)

# pandas way: the operation applies to every element at once
rates_bp = rates * 100
print(rates_bp)

# Changes between decisions, in basis points
print(rates.diff() * 100)

diff() replaces the Day 2 loop over range(1, len(...)). The first value is NaN ("Not a Number"), pandas' marker for missing, because there's no previous decision.

Example 4 — Meaningful labels

rates = pd.Series(
    [4.75, 4.50, 4.25, 3.75, 3.25],
    index=["2024-06", "2024-07", "2024-09", "2024-10", "2024-12"],
    name="overnight_rate",
)
print(rates)
print(rates["2024-10"])          # look up by label → 3.75
print(rates.iloc[0])             # look up by position → 4.75

Example 5 — Filtering with a condition

print(rates > 4.0)               # a Series of True/False
print(rates[rates > 4.0])        # keep only the True rows

Compare with the Day 2 list comprehension [x for x in rates if x > 4.0]: same idea, shorter syntax.

Try it 4.2

Create a Series of 2022 hike sizes in bp: [25, 50, 50, 100, 75, 50, 50]. (a) Total tightening. (b) Largest single hike. (c) Cumulative path using .cumsum(). (d) Hikes of 50 bp or more.

hikes = pd.Series([25, 50, 50, 100, 75, 50, 50], name="hike_bp")

print("Total:", hikes.sum())            # 400
print("Largest:", hikes.max())          # 100
print(hikes.cumsum())                   # replaces the Day 2 accumulator loop
print(hikes[hikes >= 50])

4.3 DataFrames: a Table of Indicators (23 min)

A DataFrame is a table: several Series sharing one index. Create day04/first_dataframe.py.

Example 1 — From a dictionary of lists

We use the notebook's provincial fiscal table (FY2026, % of provincial GDP).

import pandas as pd

provinces = pd.DataFrame({
    "province": ["BC", "AB", "SK", "MB", "ON", "QC", "NB", "NS", "PE", "NL"],
    "balance_pct_gdp": [-0.8, 0.2, 0.5, -0.9, -1.4, -1.2, -0.5, -0.3, -0.7, 0.1],
    "region": ["West", "West", "Prairies", "Prairies", "Central", "Central",
               "Atlantic", "Atlantic", "Atlantic", "Atlantic"],
})
print(provinces)

Each dictionary key becomes a column. Every list must have the same length.

Example 2 — First look at any DataFrame

Make this your routine for every new dataset:

print(provinces.shape)       # (rows, columns) → (10, 3)
print(provinces.columns)     # column names
print(provinces.dtypes)      # type of each column
print(provinces.head(3))     # first 3 rows
print(provinces.tail(3))     # last 3 rows
provinces.info()             # overview incl. missing values
print(provinces.describe())  # statistics for numeric columns

Text columns may show as object or str depending on your pandas version. Both mean text.

Example 3 — From a list of dictionaries (one per row)

This shape should look familiar from the BoC API on Days 2–3:

decisions = pd.DataFrame([
    {"date": "2025-01-29", "rate": 3.00, "change": "-25bp"},
    {"date": "2025-03-12", "rate": 2.75, "change": "-25bp"},
    {"date": "2025-06-04", "rate": 2.25, "change": "Hold"},
])
print(decisions)

Rule of thumb: a dict of lists is column-oriented; a list of dicts is row-oriented. pandas accepts both.

4.4 Selecting and Filtering (25 min)

Continue in first_dataframe.py.

Example 1 — Selecting columns

balances = provinces["balance_pct_gdp"]              # one column → Series
subset = provinces[["province", "balance_pct_gdp"]]  # list of columns → DataFrame
print(type(balances), type(subset))

Note the double brackets when selecting several columns: the inner brackets are a Python list.

Example 2 — Selecting rows with .loc and .iloc

print(provinces.iloc[0])          # first row, by position
print(provinces.iloc[-1])         # last row
print(provinces.iloc[0:3])        # first three rows

# .loc uses labels: here the index labels are 0..9
print(provinces.loc[4, "province"])                  # "ON"
print(provinces.loc[4, ["province", "balance_pct_gdp"]])

A cleaner approach is to make province the index:

prov = provinces.set_index("province")
print(prov.loc["ON"])                          # whole row for Ontario
print(prov.loc["ON", "balance_pct_gdp"])       # -1.4
print(prov.loc[["ON", "QC"], "balance_pct_gdp"])

Example 3 — Boolean filtering

surplus = provinces[provinces["balance_pct_gdp"] > 0]
print(surplus)

# Two conditions: use & (and) or | (or), with each condition in parentheses
atlantic_deficits = provinces[
    (provinces["region"] == "Atlantic") & (provinces["balance_pct_gdp"] < 0)
]
print(atlantic_deficits)

Common error: writing and instead of &, or forgetting parentheses, gives ValueError: The truth value of a Series is ambiguous. With pandas, always use &, |, ~ and parentheses.

Example 4 — .isin() and .query()

big_four = provinces[provinces["province"].isin(["ON", "QC", "BC", "AB"])]
print(big_four)

# .query reads almost like English (the notebook uses this in fred_fetch)
print(provinces.query("balance_pct_gdp < -1.0"))
print(provinces.query("region == 'West' and balance_pct_gdp < 0"))

Try it 4.4

(a) List Prairie and Western provinces with deficits. (b) Get the balance of Nova Scotia using .loc. (c) How many provinces have a deficit worse than −0.5%?

west = provinces[
    provinces["region"].isin(["West", "Prairies"]) &
    (provinces["balance_pct_gdp"] < 0)
]
print(west)

prov = provinces.set_index("province")
print(prov.loc["NS", "balance_pct_gdp"])        # -0.3

print((provinces["balance_pct_gdp"] < -0.5).sum())   # 5

4.5 Creating New Columns (13 min)

Example 1 — Arithmetic on columns

provinces["balance_bp"] = provinces["balance_pct_gdp"] * 100

Example 2 — Conditional labels with .apply and your own function

You already know how to write classification functions (Day 3):

def fiscal_status(balance):
    if balance > 0:
        return "Surplus"
    elif balance >= -0.5:
        return "Small deficit"
    return "Large deficit"

provinces["status"] = provinces["balance_pct_gdp"].apply(fiscal_status)
print(provinces[["province", "balance_pct_gdp", "status"]])

.apply calls your function once per value, just like a loop, but with less code.

Example 3 — Mapping codes to names with .map

names = {"BC": "British Columbia", "AB": "Alberta", "SK": "Saskatchewan",
         "MB": "Manitoba", "ON": "Ontario", "QC": "Quebec",
         "NB": "New Brunswick", "NS": "Nova Scotia",
         "PE": "Prince Edward Island", "NL": "Newfoundland and Labrador"}

provinces["name"] = provinces["province"].map(names)

4.6 Sorting and Counting (8 min)

# Sort worst to best
print(provinces.sort_values("balance_pct_gdp"))

# Best to worst
print(provinces.sort_values("balance_pct_gdp", ascending=False).head(3))

# How many in each category?
print(provinces["status"].value_counts())
print(provinces["region"].value_counts())

4.7 Reading and Writing Files (15 min)

Example 1 — Reading a CSV

Look at the raw file first. In the terminal: head data/fred_fedfunds.csv

import pandas as pd

ff = pd.read_csv("data/fred_fedfunds.csv")
print(ff.head())
print(ff.dtypes)

FRED's column names vary (DATE or observation_date), so we rename by position, exactly as the notebook's fred_fetch does:

ff = ff.iloc[:, :2]                   # keep the first two columns
ff.columns = ["date", "fed_funds"]    # give them our own names
ff["fed_funds"] = pd.to_numeric(ff["fed_funds"], errors="coerce")
print(ff.tail())

pd.to_numeric(..., errors="coerce") converts text to numbers and turns anything unreadable into NaN. It's the one-line replacement for your Day 2 try/except loop.

Example 2 — Writing CSV and Parquet

import os
os.makedirs("data/processed", exist_ok=True)

ff.to_csv("data/processed/fed_funds.csv", index=False)
ff.to_parquet("data/processed/fed_funds.parquet", index=False)

The course notebook saves everything as Parquet (save_df), and DuckDB (Days 11–20) reads Parquet natively.

4.8 Mini-Project: Exploring the Fed Funds Rate (10 min)

Create day04/fed_funds.py. Using data/fred_fedfunds.csv:

1. Load, keep two columns, rename to date and fed_funds, convert to numeric.
2. Print shape, first and last rows, and describe().
3. Count months with the rate above 5%.
4. Show the 5 highest months.
5. Show months since "2022-01-01" (hint: dates are still text, but ISO dates compare correctly as text).
6. Save to data/processed/fed_funds.parquet.

import os
import pandas as pd

ff = pd.read_csv("data/fred_fedfunds.csv").iloc[:, :2]
ff.columns = ["date", "fed_funds"]
ff["fed_funds"] = pd.to_numeric(ff["fed_funds"], errors="coerce")
ff = ff.dropna()

print(ff.shape)
print(ff.head(2))
print(ff.tail(2))
print(ff["fed_funds"].describe())

print("Months above 5%:", (ff["fed_funds"] > 5).sum())
print(ff.sort_values("fed_funds", ascending=False).head(5))
print(ff[ff["date"] >= "2022-01-01"])

os.makedirs("data/processed", exist_ok=True)
ff.to_parquet("data/processed/fed_funds.parquet", index=False)

Tomorrow we fix step 5 properly by turning text dates into real dates.

Day 4 checklist

• I can create a Series and a DataFrame and inspect them (shape, head, info, describe)
• I can select columns, and rows with .loc, .iloc and boolean filters
• I can add columns with arithmetic, .apply and .map
• I can read a CSV, clean a column with pd.to_numeric, and save CSV/Parquet

git add .
git commit -m "Day 4: pandas Series and DataFrames"
git push