insideout Get Pro

02 · Tables of prices

Free

Python for Market Data, lesson 2

Tables of prices

pandas DataFrames: rows of dates, columns of stocks.

A numpy array knows positions, not dates or tickers. pandas puts those labels on the array, and the labels change what your code does. You need the numpy slices and masks from the last lesson.

By the end, you can:

  1. Build a DataFrame of prices, and pick a stock, a day or one price by its labels.
  2. Pick a run of dates with .loc and a run of rows with .iloc, and know which ends each keeps.
  3. Predict what pandas does when two Series with different labels meet.
  4. Turn a long file of dates, tickers and closes into a wide table, and find each stock's best day.

Part 1. A table with labels

Build a DataFrame of prices, and pick a stock, a day or one price by its labels.

An array of prices is just numbers: row 2, column 1. Real data comes with dates and tickers, and you'd rather ask for KITE on 7 January than for position (2, 1). A pandas DataFrame keeps those labels on the edges of the array.

Example 1.1

Five days, two stocks

Build a table of closing prices for GULL and KITE over the five weekdays from Monday 5 January 2026: GULL 100.0, 101.5, 99.8, 102.2, 103.0 and KITE 50.0, 50.4, 51.1, 50.7, 51.9. Then pick KITE's prices, and KITE's close on Wednesday 7 January.

  1. Make the datesdates = pd.bdate_range("2026-01-05", periods=5)

    Five weekdays, Monday 5 to Friday 9 January. Weekdays are a first guess at trading days, and the next lesson fixes the guess.

  2. Put each stock in a columndf = pd.DataFrame({"GULL": [...], "KITE": [...]}, index=dates)

    Each key of the dictionary becomes a column, and index= puts the dates down the side. df.shape is (5, 2).

  3. Pick one stockdf["KITE"] → 5 prices, labelled by date

    Square brackets with a name pick a column.

  4. Pick one pricedf.loc["2026-01-07", "KITE"] → 51.1

    .loc takes a row label, then a column label.

AnswerKITE closed at 51.1 on Wednesday 7 January.

GULLKITE2026-01-05100.050.02026-01-06101.550.42026-01-0799.851.12026-01-08102.250.72026-01-09103.051.9the indexa Seriesdf["KITE"]row labelsthe columnsthe values:a numpy array
Figure 1The values are a numpy array. The index labels the rows and the column names label the columns. One column on its own, df["KITE"], is a Series.

Underneath, the values are still a numpy array, so everything from the last lesson works on them. The labels sit on the two edges: the index down the side, the column names across the top. Pick a column and its index comes with it, which is why df["KITE"] still knows its dates.

DataFrame, index and Series

A DataFrame is pandas' table: a numpy array with labels on both edges. Its row labels are the index, here dates. One column on its own, or one row, is a Series: values with labels.

Square brackets alone pick columns. df["2026-01-07"] looks for a column called 2026-01-07 and raises a KeyError. For rows, use .loc: df.loc["2026-01-07"] is that day's closes for both stocks.

Your turn

df has dates down the side and the columns GULL and KITE. What does df.loc["2026-01-08"] give?

With one label, .loc picks a whole row: a Series of that day's closes, GULL 102.2 and KITE 50.7, labelled by ticker. Add a column label, df.loc["2026-01-08", "GULL"], to get one number.

Part 2. Slicing by label and by position

Pick a run of dates with .loc and a run of rows with .iloc, and know which ends each keeps.

Often you want a stretch of days: a week, a month, everything since a date. pandas slices two ways, by label with .loc and by position with .iloc, and the two disagree about the end.

Example 2.1

Wednesday to Friday, two ways

df has one row per weekday, Monday 5 to Friday 9 January 2026. Pick the rows from Wednesday 7 to Friday 9 January, first by date, then by position.

  1. Slice by datedf.loc["2026-01-07":"2026-01-09"] → 3 rows

    A label slice keeps both ends: the 7th, the 8th and the 9th.

  2. Find the positionsWednesday is 2, Friday is 4

    Positions count from 0, so Monday is 0.

  3. Slice by positiondf.iloc[2:5] → 3 rows

    A position slice works like a Python list and leaves out its end, so it needs 5 to reach Friday.

Answerdf.loc["2026-01-07":"2026-01-09"] and df.iloc[2:5] give the same 3 rows.

Positions are a count, so the list rule fits: iloc[2:5] is 5 − 2 = 3 rows. Dates aren't a count. You name the first and last day you want and get both, and on a sorted index the dates you name needn't be in it: df.loc["2026-01-03":"2026-01-06"] gives Monday and Tuesday.

.loc and .iloc

.loc picks rows and columns by label, and a label slice keeps both ends. .iloc picks by position, counting from 0, and a position slice leaves out its end, like a list.

Two shortcuts you'll use daily. A partial date picks a whole stretch: df.loc["2026-01"] is every row in January 2026. And df.iloc[-1] is the last row, whatever its date: the latest prices.

Your turn

df has one row per weekday. How many rows do df.loc["2026-01-06":"2026-01-08"] and df.iloc[1:3] return?

A label slice keeps both ends: the 6th, the 7th and the 8th. A position slice works like a Python list and leaves out its end: positions 1 and 2 only. Mixing the two up is a classic off-by-one.

Part 3. Labels line up first

Predict what pandas does when two Series with different labels meet.

Here's the rule that keeps pandas safe, and that surprises people coming from numpy. When two Series meet, pandas first matches them by label, and only then does the maths.

Example 3.1

Adding two Series

a holds 1, 2 and 3 for GULL, KITE and WREN. b holds 10, 20 and 30 for WREN, GULL and LARK, in that order. What is a + b?

  1. Match the labelsGULL and WREN are in both

    Wherever they sit, GULL meets GULL and WREN meets WREN.

  2. Add the matched pairsGULL: 1 + 20 = 21. WREN: 3 + 10 = 13

    Each value meets the one with the same label, not the one in the same place.

  3. Fill in the restKITE and LARK → NaN

    A label in only one Series has no partner, so its sum is missing.

AnswerGULL 21, KITE NaN, LARK NaN and WREN 13, sorted by label.

aba + bGULL1KITE2WREN3WREN10GULL20LARK30GULL21KITENaNLARKNaNWREN13By label: GULL meets GULL. KITE and LARK have no partner.By position: 11, 22, 33, every one the wrong pair.
Figure 2pandas matches labels before it adds: GULL meets GULL wherever it sits. KITE and LARK have no partner, so their sums are NaN.

Real data needs this. Stocks list and delist, files arrive in any order, and one source skips a day another has. The raw arrays, a.to_numpy() + b.to_numpy(), add by position instead: 11, 22 and 33, every sum pairing the wrong stocks, with no error at all.

Alignment and NaN

Matching rows and columns by label before doing the maths is alignment. NaN, short for not a number, marks a missing value. Most pandas maths skips it or passes it through.

Alignment is also how yesterday's price reaches today's row. s.shift(1) moves the values down one row and leaves the labels where they were. So s / s.shift(1) - 1 divides each day by the day before: the daily returns, with NaN on the first day. s.pct_change() does the same in one call.

Your turn

a = pd.Series([1, 2, 3], index=["GULL", "KITE", "WREN"]) and b = pd.Series([10, 20, 30], index=["WREN", "GULL", "KITE"]). What is (a + b)["GULL"]?

GULL is 1 in a and 20 in b, so 21. Adding by position would give 1 + 10 = 11, which mixes GULL with WREN.

Part 4. Long tables and wide tables

Turn a long file of dates, tickers and closes into a wide table, and find each stock's best day.

Market data often arrives long: one row per date and ticker, the way a database stores it. Most research wants it wide, with one row per date and one column per ticker. pivot turns one into the other.

Example 4.1

The best day, from a long file

A file has six rows: the closes of GULL and KITE on 5, 6 and 7 January 2026. GULL closed at 100.0, 103.0 and 101.0, KITE at 50.0, 50.5 and 52.0. Make a wide table and find each stock's best day.

  1. Pivotwide = long.pivot(index="date", columns="ticker", values="close")

    Each date becomes a row and each ticker a column: six rows long become 3 × 2 wide.

  2. Take returnsr = wide.pct_change()

    GULL: +3.00%, then −1.94%. KITE: +1.00%, then +2.97%. The first row is NaN, because 5 January has no day before it.

  3. Find the best dayr.idxmax() → GULL 6 Jan, KITE 7 Jan

    max() gives each column's biggest value and idxmax() its label, the date. Both skip NaN.

AnswerGULL's best day was 6 January (+3.00%) and KITE's was 7 January (+2.97%).

datetickerclose5 JanGULL100.05 JanKITE50.06 JanGULL103.06 JanKITE50.57 JanGULL101.07 JanKITE52.0pivotdateGULLKITE5 Jan100.050.06 Jan103.050.57 Jan101.052.0long: 6 rowswide: 3 × 2
Figure 3pivot makes a row for each date and a column for each ticker. KITE's close on 7 January moves from the last long row to the bottom of the KITE column.

In the long file a stock's history is spread over every other row. Wide, each column is one stock in date order, so every tool so far runs down the columns, and each row is one day across the market.

Long and wide

A long table has one row per observation: date, ticker, value. A wide table has one row per date and one column per ticker. pivot goes from long to wide, and melt goes back.

If the file holds two rows for the same date and ticker, say a price and its correction, pivot stops with a ValueError: "Index contains duplicate entries, cannot reshape". Decide which row to keep first, for example with drop_duplicates(subset=["date", "ticker"], keep="last").

Your turn

A long file has one row for each of 250 dates and 40 tickers. What shape is long.pivot(index="date", columns="ticker", values="close")?

One row per date and one column per ticker: (250, 40). The long file itself is (10000, 3): 250 × 40 rows of date, ticker and close.

Where you'll meet it

On the job

Market data usually arrives long: one row per instrument and timestamp, the way a database stores it. Research code often turns it wide, so a whole day is one row and a whole history is one column, and most of this module works on that wide table. Long tables come back when each stock carries extra fields, such as its sector, and for joins.

In interviews

Data rounds often start with a table to reshape and question: pivot it, pick a date range, find the biggest move, join a second table. Interviewers watch for a position used where a label was meant, and for joins that line up the wrong rows without an error.

Parts 3 and 4, in Python
import pandas as pd

a = pd.Series([1.0, 2.0, 3.0], index=["GULL", "KITE", "WREN"])
b = pd.Series([10.0, 20.0, 30.0], index=["WREN", "GULL", "LARK"])
print((a + b).to_dict())
# {'GULL': 21.0, 'KITE': nan, 'LARK': nan, 'WREN': 13.0}

dates = pd.to_datetime(["2026-01-05", "2026-01-06", "2026-01-07"])
long = pd.DataFrame({"date": dates.repeat(2),
                     "ticker": ["GULL", "KITE"] * 3,
                     "close": [100.0, 50.0, 103.0, 50.5, 101.0, 52.0]})
wide = long.pivot(index="date", columns="ticker", values="close")
print(wide)
# ticker       GULL  KITE
# date
# 2026-01-05  100.0  50.0
# 2026-01-06  103.0  50.5
# 2026-01-07  101.0  52.0

r = wide.pct_change()
print(r.max().round(4).to_dict())
# {'GULL': 0.03, 'KITE': 0.0297}
print(r.idxmax().dt.strftime("%d %b").to_dict())
# {'GULL': '06 Jan', 'KITE': '07 Jan'}

Check yourself

Four questions from across the lesson. Try each one before you look back.

1

df holds a year of closes, one row per trading day, oldest first. Which line gives the latest close of every stock?

.iloc[-1] counts from the end, by position. df.loc[-1] looks for a row labelled -1 and df[-1] for a column called -1: both raise a KeyError. df.iloc[:, -1] is the last column, one stock's whole history.

2

s holds five closes, labelled by date. What's in the first row of s / s.shift(1) - 1?

shift(1) moves every value down a row, so the first row of s.shift(1) is empty, NaN, and a sum with NaN in it is NaN. The other four rows are the daily returns.

3

wide has dates down the side and 40 tickers across. What is wide["GULL"]?

Square brackets with a name pick a column, and a column keeps the index: GULL's whole history, with its dates. wide["GULL"].to_numpy() would drop the labels.

4

x has the columns GULL and KITE, and y has KITE and WREN, on the same dates. What does x + y give?

A DataFrame aligns on both edges: rows by date, columns by name. Only KITE is in both, so only KITE gets sums. The result keeps every column either side had, with NaN where a partner is missing.

What to remember

  1. 1A table with labels

    A DataFrame is a numpy array with labels, dates in the index and tickers as columns, so df.loc["2026-01-07", "KITE"] is one price, 51.1.

  2. 2Slicing by label and by position

    .loc slices by label and keeps both ends, .iloc by position and leaves out the end: 7 to 9 January is loc["2026-01-07":"2026-01-09"] or iloc[2:5].

  3. 3Labels line up first

    Maths between Series matches labels first: GULL meets GULL wherever it sits, and a label with no partner gives NaN.

  4. 4Long tables and wide tables

    pivot turns a long file into a wide table: 250 dates of 40 tickers, 10,000 rows long, become (250, 40).

Next lessonDates and alignmentNext, two markets with different holidays, and how to join their prices without pairing the wrong days. Start lesson 3

Python for Market Data overviewSpotted a mistake? Tell us.