Tabellen zusammenführen und verknüpfen

Tabellen mit gemeinsamem Schlüssel

Echte Daten kommen selten in einer Tabelle. Ihre Positionen stehen in einer, der Sektor jedes Symbols in einer anderen, die Preise in einer dritten. Verbunden werden sie durch einen Schlüssel, hier das Symbol. left.merge(right, on="schlüssel") findet zu jeder Zeile von left die Zeilen von right mit demselben Schlüssel und stellt sie nebeneinander. Es ist dieselbe Idee wie ein 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   tech

inner, left, right und outer

TSLA ist aus dem Ergebnis oben verschwunden, und F auch. Die Vorgabe how="inner" behält nur die Schlüssel, die in beiden Tabellen vorkommen. Sagen Sie, was Sie wollen: how="left" behält alle Zeilen der linken Tabelle und füllt fehlende rechte Werte mit NaN, how="right" macht das Gegenteil, und how="outer" behält die Schlüssel beider. Zum Anreichern einer Tabelle will man fast immer left, weil es nie stillschweigend eine Zeile verliert.

# 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    NaN

Finden, was nicht passt

Ein stilles NaN kann ein Datenproblem verbergen, prüfen Sie es also. indicator=True fügt eine Spalte _merge hinzu, die für jede Zeile sagt, ob der Schlüssel in left_only, right_only oder both gefunden wurde. Danach zu filtern beantwortet Fragen wie „Welche Positionen haben keinen Preis?“. Doppelte Schlüssel sind die andere Falle: Kommt ein Schlüssel rechts zweimal vor, wird jede passende linke Zeile wiederholt. Mit validate="many_to_one" (oder "one_to_one") wirft pandas einen Fehler, statt die Zeilenzahl unbemerkt zu ändern.

# 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__)   # MergeError

Unterschiedliche Schlüsselnamen und kollidierende Spalten

Heißt der Schlüssel in den beiden Tabellen verschieden, nutzen Sie left_on="symbol", right_on="ticker"; beide Spalten bleiben erhalten. Haben beide Tabellen eine gleichnamige Spalte, die nicht der Schlüssel ist (hier qty), behält pandas beide und hängt die Suffixe _x und _y an. Wählen Sie deutlichere mit suffixes=("_a", "_b"). Nach einem Outer Join sind fehlende Mengen NaN und die Spalte ist ein Float: Mit fillna(0) und astype("int64") erhalten Sie wieder ganze Zahlen.

# 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     30

• left.merge(right, on="schlüssel") ordnet Zeilen zweier Tabellen mit gemeinsamem Schlüssel zu; left_on / right_on decken verschiedene Namen ab.
• how="inner" (Vorgabe) behält Schlüssel beider Seiten, "left" alle linken Zeilen, "outer" jeden Schlüssel.
• indicator=True zeigt, was nicht passt; validate= und suffixes= schützen vor doppelten Schlüsseln und kollidierenden Namen.

Als Nächstes: einfache Renditen, die eine Preisreihe in die prozentuale Veränderung von einer Periode zur nächsten verwandeln.

Lab: Was bewertet wird

• Mit attach_sector den Sektor jedes Trades ergänzen25 Pkt.
• Mit value_positions die Positionen mit Preis bewerten25 Pkt.
• Mit unpriced_symbols die Positionen ohne Preis finden25 Pkt.
• Mit combine_books zwei Bücher zu einer Tabelle verbinden25 Pkt.