Tables that share a key
Real data rarely arrives in one table. Your positions are in one, the sector of each symbol in another, the prices in a third. What connects them is a key, here the symbol. left.merge(right, on="key") finds, for every row of left, the rows of right with the same key and puts them side by side. It is the same idea as a join in SQL.
import pandas as pd
positions = pd.DataFrame({"symbol": ["AAPL", "MSFT", "TSLA", "GOOG"], "qty": [100, 50, 20, 10]})
reference = pd.DataFrame({"symbol": ["AAPL", "MSFT", "GOOG", "F"], "sector": ["tech", "tech", "tech", "auto"]})
merged = positions.merge(reference, on="symbol")
print(merged)
# symbol qty sector
# 0 AAPL 100 tech
# 1 MSFT 50 tech
# 2 GOOG 10 techinner, left, right and outer
TSLA vanished from the result above, and so did F. The default, how="inner", keeps only the keys found in both tables. Say what you want instead: how="left" keeps every row of the left table and fills the missing right-hand values with NaN, how="right" does the opposite, and how="outer" keeps the keys of both. For enriching a table you almost always want left, because it never loses a row silently.
# how="left" keeps every position; TSLA has no sector, so it gets NaN
left = positions.merge(reference, on="symbol", how="left")
print(left)
# symbol qty sector
# 0 AAPL 100 tech
# 1 MSFT 50 tech
# 2 TSLA 20 NaN
# 3 GOOG 10 tech
# how="outer" keeps the keys of both tables (sorted); quantities become floats because of the NaN
print(positions.merge(reference, on="symbol", how="outer"))
# symbol qty sector
# 0 AAPL 100.0 tech
# 1 F NaN auto
# 2 GOOG 10.0 tech
# 3 MSFT 50.0 tech
# 4 TSLA 20.0 NaNFinding what did not match
A silent NaN can hide a data problem, so check it. indicator=True adds a column _merge that says, for every row, whether the key was found in left_only, right_only or both. Filtering on it answers questions such as "which positions have no price?". Duplicate keys are the other trap: if a key appears twice on the right, every matching row on the left is repeated. Pass validate="many_to_one" (or "one_to_one") and pandas raises an error instead of quietly changing the number of rows.
# Which positions have no sector?
check = positions.merge(reference, on="symbol", how="left", indicator=True)
print(check)
# symbol qty sector _merge
# 0 AAPL 100 tech both
# 1 MSFT 50 tech both
# 2 TSLA 20 NaN left_only
# 3 GOOG 10 tech both
print(check.loc[check["_merge"] == "left_only", "symbol"].tolist()) # ['TSLA']# Duplicate keys repeat rows: AAPL has two quotes, so the AAPL position appears twice
quotes = pd.DataFrame({"symbol": ["AAPL", "AAPL", "MSFT"], "price": [184.25, 184.5, 410.1]})
print(len(positions.merge(quotes, on="symbol"))) # 3
# validate turns the surprise into an error
try:
positions.merge(quotes, on="symbol", validate="one_to_one")
except Exception as error:
print(type(error).__name__) # MergeErrorDifferent key names and clashing columns
When the key has different names in the two tables, use left_on="symbol", right_on="ticker"; both columns are kept. When both tables have a column with the same name that is not the key (here qty), pandas keeps both and adds the suffixes _x and _y. Choose clearer ones with suffixes=("_a", "_b"). After an outer join the missing quantities are NaN and the column is float: use fillna(0) and then astype("int64") to get whole numbers back.
# Different key names: both columns are kept
book_a = pd.DataFrame({"symbol": ["AAPL", "MSFT"], "qty": [100, 50]})
book_b = pd.DataFrame({"ticker": ["MSFT", "TSLA"], "qty": [-20, 30]})
print(book_a.merge(book_b, left_on="symbol", right_on="ticker", how="outer", suffixes=("_a", "_b")))
# symbol qty_a ticker qty_b
# 0 AAPL 100.0 NaN NaN
# 1 MSFT 50.0 MSFT -20.0
# 2 NaN NaN TSLA 30.0
# Same key name, outer join, suffixes, then back to whole numbers
both = book_a.merge(book_b.rename(columns={"ticker": "symbol"}), on="symbol", how="outer", suffixes=("_a", "_b"))
both[["qty_a", "qty_b"]] = both[["qty_a", "qty_b"]].fillna(0).astype("int64")
print(both)
# symbol qty_a qty_b
# 0 AAPL 100 0
# 1 MSFT 50 -20
# 2 TSLA 0 30len() before and after, use how="left" when you want to keep every row, and add validate= when you believe the keys are unique.left.merge(right, on="key")matches rows of two tables that share a key;left_on/right_onhandle different names.how="inner"(default) keeps keys in both,"left"keeps all left rows,"outer"keeps every key.indicator=Trueshows what did not match;validate=andsuffixes=guard against duplicate keys and clashing names.
Next up: simple returns, turning a price series into the percentage change from one period to the next.
Lab · what's graded
- Add each trade's sector with
attach_sector25 pts - Value the positions that have a price with
value_positions25 pts - Find the positions without a price with
unpriced_symbols25 pts - Join two books into one table with
combine_books25 pts