Pandas - spojování tabulek #
Tato lekce se nese ve znamení mnohosti a propojování - naučíš se pracovat s více tabulkami najednou. Při tom společně projdeme (ne poprvé a ne naposledy) čištění reálných datových sad.
# Importy jako obvykle
import pandas as pd
Spojování tabulek #
V lekci, kde jsme zpracovávali data o počasí, jsme ti ukázali, že je pomocí funkce concat možné slepit dohromady několik objektů DataFrame či Series, pokud mají "kompatibilní" index. Nyní se na problematiku podíváme trochu blíže a ukážeme si, jak spojovat tabulky na základě různých sloupců, a co dělat, když řádky z jedné tabulky nepasují přesně na tabulku druhou.
Obecně pro spojování pandas nabízí tři funkce / metody, z nichž každá má svoje typické využití (možnostmi se ovšem překrývají):
concatje univerzální funkce pro slepování dvou či více tabulek / sloupců - pod sebe, vedle sebe, s přihlédnutím k indexům i bez něj.mergeje univerzální funkce pro spojování tabulek na základě vazby mezi indexy nebo sloupci.join(metoda) zjednodušuje práci, když chceš spojit dvě tabulky na základě indexu.
Detailní rozbor toho, co která umí, najdeš v dokumentaci. My si je také postupně ukážeme.
Jednoduché skládání #
a = pd.Series(["jedna", "dvě", "tři"])
b = pd.Series(["čtyři", "pět", "šest"])
pd.concat([a, b])
💡 Vidíš, že se index opakuje? Vytvořili jsme dvě Series, u kterých jsme index neřešili. Jenže pandas na rozdíl od nás ano, a tak poslušně oba indexy spojil, i za cenu duplicitních hodnot. Za cenu použití dodatečného argumentu ignore_index=True se tomu lze vyhnout, což si ukážeme na příklady spojování dvou tabulek o stejných sloupcích:
pd.concat([a, a, a, a, a], ignore_index=True)
Vedle sebe #
Toto asi použijete zřídka, ale když chceme "lepit" doprava (třeba deset Series), stačí přidat nám dobře známý argument axis:
pd.concat([a, a, a, a, a], axis="columns")
Příklad: Jak co nejrychleji "nakreslit prázdnou šachovnici" (obě slova jsou v uvozovkách)?
sachy = pd.concat(
[
pd.concat(
[pd.DataFrame([["⬜", "⬛"], ["⬛", "⬜"]])] * 4,
axis=1)
] * 4
)
sachy.index = list(range(8, 0, -1))
sachy.columns = list("ABCDEFGH")
sachy
Spojování různorodých tabulek #
🎦 Pro spojování heterogenních dat (v datové hantýrce "joinování") sáhneme po trochu komplexnějších filmových datech...
Máme staženo několik souborů, načteme si je (zatím hrubě, "raw") - s přihlédnutím k tomu, že první dva nejsou v pravém slova smyslu "comma-separated", ale používají k oddělení hodnot tabulátor (tady pomůže argument sep). Také zohledníme, že v nich řetězec "\N" představuje chybějící hodnoty (pomůže argument na_values):
imdb_titles_raw = pd.read_csv("title.basics.tsv.gz", sep="\t", na_values="\\N")
imdb_ratings_raw = pd.read_csv("title.ratings.tsv.gz", sep="\t", na_values="\\N")
boxoffice_raw = pd.read_csv("boxoffice_march_2019.csv.gz")
rotten_tomatoes_raw = pd.read_csv("rotten_tomatoes_top_movies_2019-01-15.csv")
Co který soubor obsahuje?
První dva soubory obsahují volně dostupná (byť "jen" pro nekomerční použití) data o filmech z IMDb (Internet Movie Database). My jsme si zvolili obecné informace a uživatelská (číselná) hodnocení. Detailní popis souborů, stejně jako odkazy na další soubory, najdeš na https://www.imdb.com/interfaces/. Z důvodů paměťové náročnosti jsme datovou sadu ořezali o epizody seriálů, protože nás nebudou zajímat a s trochu štěstí přežijeme i na počítačích s menší operační pamětí.
Soubor
boxoffice_march_2019.csv.gzobsahuje informace o výdělcích jednotlivých filmů. Pochází z ukázkového datasetu pro soutěž "TMDB Box Office Prediction" na serveru Kaggle: https://www.kaggle.com/c/tmdb-box-office-prediction/dataSoubor
rotten_tomatoes_top_movies_2019-01-15.csvobsahuje procentuální hodnocení filmů ze serveru Rotten Tomatoes, které se počítá jako podíl pozitivních hodnoceních od filmových kritiku (je to tedy jiný princip než na IMDb). Staženo z: https://data.world/prasert/rotten-tomatoes-top-movies-by-genre
Pojďme se podívat na nedostatky těchto souborů a postupně je skládat dohromady. Zajímalo by nás (a snad i tebe!), jak souvisí hodnocení s komerční úspěšností filmu, jak se liší hodnocení rotten tomatoes od těch na IMDb.
imdb_titles_raw
# Kolik tabulka zabírá megabajtů paměti? (1 MB = 2**20 bajtů)
imdb_titles_raw.memory_usage(deep=True).sum() / 2**20
Jistě budeme chtít převést sloupce na správné typy. Jaké jsou v základu?
imdb_titles_raw.dtypes
Na co budeme převádět?
tconstje řetězec, který posléze použijeme jako index, protože představuje unikátní identifikátor v databázi IMDb.titleType:
imdb_titles_raw["titleType"].value_counts()
Jen devět různých hodnot ve skoro 2 milionech řádků? To je ideální kandidát na převedení na typ "category".
primaryTitleaoriginalTitlevypadají jako obyčejné řetězce (pokud možno anglický a pokud možno původní název)isAdulturčuje, zda se jedná o dílo pro dospělé. Tento sloupec bychom nejspíše měli převést nabool.
imdb_titles_raw["isAdult"].value_counts()
startYearaendYearobsahují roky, t.j. celá čísla, ovšem kvůli chybějícím hodnotám je pro ně zvolen typfloat64. Vpandasraději zvolíme tzv. "nullable integer", který se zapisuje s velkým "I". Když nevíš, jaký podtyp konkrétně, sáhni poInt64.- totéž platí pro
runtimeMinutes.
imdb_titles_raw[["startYear", "endYear", "runtimeMinutes"]].max()
Mimochodem všimli jste si, že máme díla z budoucnosti (rok 2115)?
imdb_titles_raw["startYear"].plot.hist()
imdb_titles_raw.query("startYear > 2019")["startYear"].value_counts().sort_index()
Takhle nějak by přetypování mohlo vypadat:
(
imdb_titles_raw
.assign(
titleType=imdb_titles_raw["titleType"].astype("category"),
startYear=imdb_titles_raw["startYear"].astype("Int64"),
endYear=imdb_titles_raw["endYear"].astype("Int64"),
isAdult=imdb_titles_raw["isAdult"].astype(bool),
runtimeMinutes=imdb_titles_raw["runtimeMinutes"].astype("Int64")
)
).dtypes
Takhle už by to mohlo být. Jen si ještě:
- pro přehlednost přejmenujeme některé sloupce (pomocí metody
rename) - použijeme
tconstjako index
A tabulka imdb_titles bude připravená k použití!
imdb_titles = (
imdb_titles_raw
.assign(
titleType=imdb_titles_raw["titleType"].astype("category"),
startYear=imdb_titles_raw["startYear"].astype("Int64"),
endYear=imdb_titles_raw["endYear"].astype("Int64"),
isAdult=imdb_titles_raw["isAdult"].astype(bool),
runtimeMinutes=imdb_titles_raw["runtimeMinutes"].astype("Int64")
)
.rename({
"primaryTitle": "title",
"originalTitle": "original_title",
"titleType": "title_type",
"runtimeMinutes": "length",
"startYear": "start_year",
"endYear": "end_year",
"isAdult": "is_adult",
}, axis="columns")
.set_index("tconst")
)
imdb_titles
# Kolik tabulka zabírá megabajtů paměti?
imdb_titles.memory_usage(deep=True).sum() / 2**20 # O chlup méně, zase tolik jsme si ale nepomohli.
Připravíme si ještě speciální tabulku jenom pro filmy, protože další datové sady se zabývají jenom jimi.
U této tabulky navíc vyhodíme zbytečné sloupce title_type, end_year a přejmenujeme start_year prostě na year:
movies = (
imdb_titles
.query("title_type == 'movie'")
.drop(["title_type", "end_year"], axis="columns")
.rename({"start_year": "year"}, axis="columns")
)
movies
print(movies.shape)
print(movies.dtypes)
Nyní se podíváme na zoubek hodnocením z IMDb, na tabulky imdb_ratings_raw:
imdb_ratings_raw
imdb_ratings_raw.dtypes
To by vlastně skoro mohlo být!
Tak jen nastavíme index (opět tconst) a přejmenujeme sloupce:
ratings = (imdb_ratings_raw
.rename({
"averageRating": "imdb_rating",
"numVotes": "imdb_votes"
}, axis="columns")
.set_index("tconst")
)
ratings
První join #
Máme připravené dvě krásné tabulky, které sdílejí stejný index, a můžeme vesele spojovat. Protože pomocí join, merge a concat lze volbou vhodných parametrů dosáhnout identického výsledku (což je jedním z nešvarů knihovny pandas), ukážeme si všechny tři alternativy podle subjektivního pořadí vhodnosti.
movies.join(ratings)
K tabulce se nenápadně přidaly dva sloupce z tabulky ratings, a to takovým způsobem, že se porovnaly hodnoty indexu (tedy tconst) a spárovaly se ty části řádku, kde se tento index shoduje.
💡 Uvědom si (ačkoliv z volání funkcí v pandas to není úplně zřejmé), že se tady děje něco fundamentálně odlišného od "nalepení doprava" - tabulky tu nejsou chápány jako čtverečky, které jde skládat jako lego, nýbrž jako zdroj údajů o jednotlivých objektech, které je potřeba spojit sémanticky.
Jak ale vidíš, tabulka obsahuje spoustu řádků, kde ve sloupcích s hodnocením chybí hodnoty (respektive nachází se NaN). To vychází ze způsobu, jakým metoda join ve výchozím nastavení "joinuje" - použije všechny řádky z levé tabulky bez ohledu na to, jestli jim odpovídá nějaký protějšek v tabulce pravé. Naštěstí lze pomocí argumentu how specifikovat i jiné způsoby spojování:
left(výchozí pro metodujoin) - vezmou se všechny prvky z levé tabulky a jim odpovídající prvky z pravé tabulky (kde nejsou, doplní seNaN)right- vezmou se všechny prvky z pravé tabulky a jim odpovídající prvky z levé tabulky (kde nejsou, doplní seNaN)inner(výchozí pro funkcimerge) - vezmou se jen ty prvky, které jsou v levé i pravé tabulce.outer(výchozí pro funkciconcat) - vezmou se všechny prvky, z levé i pravé tabulky, kde něco chybí, doplní seNaN.
V podobě Vennově diagramu, kde kruhy představují množiny řádků v obou zdrojových tabulkách a modrou barvou jsou zvýrazněny řádky v tabulce cílové:
Obrázek adaptován z https://upload.wikimedia.org/wikipedia/commons/9/9d/SQL_Joins.svg (autor: Arbeck)
💡 Až budeme probírat databáze, tyto čtyři typu joinů se nám znovu vynoří.
Následující výpis ukáže, kolik řádků bychom dostali při použití různých hodnot how:
for how in ["left", "right", "inner", "outer"]:
print(f"movies.join(ratings, how=\"{how}\"):", movies.join(ratings, how=how).shape[0], "řádků.")
A teď tedy ty tři alternativy:
# Alternativa 1 (preferovaná)
movies_with_rating = movies.join(ratings, how="inner")
movies_with_rating
# Alternativa 2 (taky dobrá)
pd.merge(movies, ratings, left_index=True, right_index=True)
# Alternativa 3 (méně "sémantická")
pd.concat([movies, ratings], axis="columns", join="inner")
Zkusme si zreprodukovat pořadí 250 nejlepších filmů z IMDb (viz https://www.imdb.com/chart/top/?ref_=nv_mv_250):
# Ty nejlepší (do června 2019)
(movies_with_rating
.query("imdb_votes > 25000") # Berou se jen filmy s více než 25000 hlasy
.sort_values("imdb_rating", ascending=False) # IMDb tu použivá i váhu jednotlivých hlasů (kterou neznáme)
.reset_index(drop=True)
).iloc[:250]
Do výčtu se nám dostaly filmy, které hranici hlasů nepřekračují o moc. Máme důvodné podezření, že toto kritérium dávno změnili. S požadovanými 250 000 hlasy se už blížíme:
(movies_with_rating
.query("imdb_votes > 250000")
.sort_values("imdb_rating", ascending=False)
.reset_index(drop=True)
).iloc[:250]
Druhý join #
Co tabulka s výdělky (boxoffice_raw)?
boxoffice_raw
boxoffice_raw.dtypes
S tím bychom v podstatně mohli být spokojení, jen přejmenujeme rank, abychom při joinování věděli, odkud daný sloupec pochází.
boxoffice = (boxoffice_raw
.rename({
"rank": "boxoffice_rank"
}, axis="columns")
)
A zkusíme joinovat. V tomto případě se nemůžeme opřít o index (boxoffice pochází z jiného zdroje a o nějakém ID filmu z IMDb nemá ani tuchy), ale explicitně specifikujeme, který sloupec (či sloupce) se musí shodovat - na to slouží argument on:
pd.merge(
movies_with_rating,
boxoffice,
suffixes=[" (imdb)", " (boxoffice)"],
on="title"
).query("title == 'Pinocchio'") # "Jeden" ukázkový film
Jejda, to jsme asi nechtěli. Existuje spousta různých Pinocchiů a ke každému z nich se připojili vždy oba snímky tohoto jména z boxoffice. Z toho vyplývá poučení, že při joinování je dobré se zamyslet nad jedinečností hodnot ve sloupci, který používáme jako klíč. Jméno filmu takové očividně není.
V našem konkrétním případě jsme si problému všimli sami, ale pokud bude duplikátní klíč utopen někde v milionech hodnot, rádi bychom, aby to počítač poznal za nás. K tomu slouží argument validate - podle toho, jaký vztah mezi tabulkami očekáš, jsou přípustné hodnoty "one_to_one", "one_to_many", "many_to_one" nebo "many_to_many":
pd.merge(
movies_with_rating,
boxoffice,
on="title",
suffixes=[" (imdb)", " (boxoffice)"],
# validate="one_to_one" # Odkomentuj a vyskočí chyba!
)
Řešení je jednoduché - budeme joinovat přes dva různé sloupce (argument on to unese ;-)). Při té příležitosti navíc zjišťujeme, že nedává smysl spojovat filmy, které rok vůbec uvedený nemají, a proto je vyhodíme:
(
pd.merge(
movies_with_rating.dropna(subset=["year"]), # Vyhoď všechny řádky bez roku
boxoffice,
on=["title", "year"],
validate="many_to_one", # movies_with_rating pořád nejsou unikátní!
)
).query("title == 'Playback'")
Pořád nejsou unikátní! Co s tím?
Hypotéza: Vstupujeme na nebezpečnou půdu a zkusíme spekulovat, že informace o ziscích budeme mít nejspíš jen o nejpopulárnějších filmech. Možná máme pravdu, možná ne a nejspíš nějakou drobnou nepřesnost zaneseme, ale dobrat se tady skutečné pravdy je "drahé" (a možná i skutečně drahé), z nabízených datových sad to věrohodně možné není.
Abychom se co nejvíc přiblížili realitě, z každé opakující se dvojice (název, rok) vybereme film s nejvyšším imdb_votes. Nejdříve si pomocí sort_values srovnáme všechny filmy a pak zavoláme drop_duplicates(..., keep="first"), což nám ponechá vždy jen jeden z řady duplikátů:
movies_with_rating_and_boxoffice = (
pd.merge(
movies_with_rating
.dropna(subset=["year"])
.sort_values("imdb_votes", ascending=False)
.drop_duplicates(
subset=["title", "year"],
keep="first"
),
boxoffice,
on=["title", "year"],
validate="one_to_one",
)
)
movies_with_rating_and_boxoffice
# To už by šlo!
movies_with_rating_and_boxoffice.query("title == 'Playback'")
Úkol: Seřaď filmy podle toho, kolik vydělaly (nabízí se hned dvě možnosti).
Otázka: Které filmy nám vypadly a proč?
Třetí join #
rotten_tomatoes_raw
rotten_tomatoes_raw["Title"].value_counts()
A zase duplicity, některé názvy se nám opakují :-(
Otázka: Dokážeš zjistit proč? Nápověda: podívej se na nějaký konkrétní film.
Naštěstí už víme, jak na to - použijeme metodu drop_duplicates, tentokrát přes sloupec "Title". (Poznámka: druhou možností by bylo sloučit všechny různé žánry daného filmu do jedné buňky).
rotten_tomatoes_nodup = (
rotten_tomatoes_raw
.drop_duplicates(
subset="Title",
keep="first" # Vybereme první výskyt, lze i "last" (anebo False => vyhodit všechny)
)
.drop("Genres", axis="columns") # Informační hodnotu jsme už ztratili
.drop("Rank", axis="columns") # Mělo smysl jen v rámci žánru
)
rotten_tomatoes_nodup
# Ready to merge?
pd.merge(imdb_titles, rotten_tomatoes_nodup, left_on="title", right_on="Title")
0 řádků!
Dosud jsme manipulovali s řádky a sloupci jako celky, nicméně teď musíme zasahovat přímo do hodnot v buňkách. I to se při slučování dat z různých zdrojů nezřídka stává. Stojíme před úkolem převést řetězce typu "Black Panther (2018)" na dvě hodnoty: název "Black Panther" a rok 2018 (jeden sloupec na dva).
Naštěstí si ty sloupce umíme jednoduše vyrobit pomocí řetězcové metody .str.slice, která z každého řetězce vyřízne nějakou jeho část (a zase pracuje na celém sloupci - výsledkem bude nový sloupec s funkcí aplikovanou na každou z hodnot). Budeme věřit, že předposlední čtyři znaky představují rok a zbytek, až na nějaké ty závorky, tvoří skutečný název:
rotten_tomatoes_beta = (rotten_tomatoes_nodup
.assign(
title=rotten_tomatoes_nodup["Title"].str.slice(0, -7),
year=rotten_tomatoes_nodup["Title"].str.slice(-5, -1).astype(int)
)
.rename({
"RatingTomatometer": "tomatoes_rating",
"No. of Reviews": "tomatoes_votes",
}, axis="columns")
.drop(["Title"], axis="columns")
)
rotten_tomatoes_beta
Závorková odysea nekončí, někdo nám proaktivně do závorek nacpal i originální název naanglickojazyčných filmů. Pojďme se o tom přesvědčit pomocí metody .str.contains (protože tato metoda ve výchozím stavu používá pro vyhledávání regulární výrazy, které jsme se zatím nenaučili používat, musíme jí to explicitně zakázat argumentem regex=False):
rotten_tomatoes_beta[rotten_tomatoes_beta["title"].str.contains(")", regex=False)]
V rámci zjednodušení proto ještě odstraníme všechny takové závorky. K tomu pomůže funkce .str.rsplit, která rozdělí zprava řetězec na několik částí podle oddělovače a vloží je do seznamu - my za ten oddělovač zvolíme levou závorku "(", omezíme počet částí na jednu až dvě (n=1):
split_title = (
rotten_tomatoes_beta["title"]
.str.rsplit("(", n=1)
)
split_title.loc[[41, 61, 81]] # Některé seznamy obsahují jeden prvek, jiné dva
A jak teď vybrat první prvek z každého seznamu?
💡 Metoda apply umožňuje použít libovolnou transformaci (definovanou jako funkci) na každý řádek v tabulce či hodnotu v Series. Obvykle se bez ní obejdeme a měli bychom (proto se jí tolik speciálně nevěnujeme), protože není příliš výpočetně efektivní. Tady nám ale usnadní pochopení, co se vlastně dělá, t.j. vybírá první prvek nějakého seznamu:
def take_first(a_list): # Funkce, kterou použijeme v apply
return a_list[0]
rotten_tomatoes = (rotten_tomatoes_beta
.assign(
title=split_title.apply(take_first)
)
)
rotten_tomatoes
# Zbavení se duplikátů hned na začátku nám zachovalo filmy se stejným jménem :-)
rotten_tomatoes.query("title == 'The Magnificent Seven'")
pd.merge(
movies.dropna(subset=["year"]),
rotten_tomatoes,
on=["title", "year"],
how="inner"
)
Když sloučíme filmy a hodnocení na Rotten Tomatoes, z 947 filmů se nám skoro tři sta ztratí. Bohužel zde je na vině především nestejnost zápisu názvu, různé uřčité členy, interpunkce, podnázvy apod. Coby řešení se tady nabízí spousta a spousta manuální práce, případně nějaká heuristika, která by na sebe pasovala "hodně podobné" názvy.
Mimochodem, obtížnost manuální práce se mezi vývojáři někdy přeceňuje: Opravit 288 názvů filmů může být práce na hodinu až dvě, zatímco psát algoritmus na "řešení problému" může trvat stejně dlouho, ne-li déle.
Čtvrtý (a poslední) join #
Dokončíme slučování všech čtyř tabulek:
movies_complete = pd.merge(
movies_with_rating_and_boxoffice,
rotten_tomatoes,
on=["title", "year"],
how="inner"
)
movies_complete.sort_values("boxoffice_rank").reset_index(drop=True)
A přišli jsme o dalších 175 filmů.
Co dál? Pokud by toto byl skutečný úkol, museli bychom se s tím nějak vypořádat - zkoumat, proč které řádky nesedí, v čem se liší názvy stejného filmu v různých datových sadách, jinými slovy manuální práce, práce, práce...
Naštěstí to je úkol jen ukázkový, a my můžeme být spokojeni, že máme sice neúplnou, ale přesto použitelnou datovou sadu :-)