Jak spojit dvě tabulky když… | Mňamka #252

Jojo, ten stav, kdy máme tabulku s objednávkami za rok 2019 a jinou tabulku s objednávkami, ale tentokrát za rok 2020. No a potřebujete si vyfiltrovat, kolik bylo za oba roky objednáno třeba triček. 

Tabulka může vypadat různě. Může být v Excelu, může být ale taky ve formátu CSV (v tomto formátu dostaneme často export z kdejakého systému, který používáme). No a je i obsahově jiná - má jiný počet, jiné názvy sloupců, nedejbože i jiné datové typy, duplikace, překlepy, chybějící hodnoty atd… Ale to už moc zahýbám.

Tenhle článek by měl být hlavně o základech a měl by odpovídat na otázku: “Jak spojit dvě tabulky, když…?”

1. Mám dvě a více tabulek v Excelu a dají se otevřít … 

Tak super. Tady je návod jak na to, o tom to dnes úplně taky nebude, to už je krásně popsáno všude možně :-D 

2. Mám dvě tabulky, stejné sloupce a v Excelu je neotevřu

Jak už jsem zmiňovala v úvodu - občas dostaneme tabulku ve formátu .csv. Ten Excel taky často otevře, ale jsou případy, kdy ne. Jeden z takových případů je velikost souboru. Prostě jsou tabulky, které kvůli jejich velikosti nemáte šanci v Excelu otevřít, takže je v Excelu ani nespojíte. Prekérka prostě. 

Pokud máte dvě stejné tabulky - mají stejné názvy a počty sloupců, ale jedna obsahuje řádky s objednávkami za rok 2019 a druhá za 2020, existuje super trik, jak se spojit pomocí příkazového řádku. 

Postup: 

1) otevřete si příkazový řádek (na Macu, který mám já ho najdeme pod názvem “Terminal”)

2) je fajn si zkontrolovat názvy a nějak zjednodušit, například tabulka1.csv a tabulka2.csv (jsou to normálně uložené tabulky v počítači).

3) do příkazového řádku napíšeme:

cat tabulka1.csv > vyslednatabulka.csv (= tímhle je tam první tabulka)

tail -n +2 cat tabulka2.csv >> vyslednatabulka.csv (= tím tam dostaneme tu druhou tabulku, +2 jakože od druhé řádky, tail je mezera, >> znamená dosadit k první tabulce, kdyby tam byla jedna > tak se to smaže)

No a je to :-) Pak už stačí mít jen program, který umí otevřít CSV.

3. Mám dvě tabulky a chci se na ně podívat nějak jinak…

Tohle není přímo návod, jak dvě tabulky spojit, aby vznikla jedna. Ale spíš jedna z cest, jak se na obě podívat najednou a ještě si z nich vytvořit třeba nějaké grafíky:-) Pokud máte k dispozici Tableau nebo třeba PowerBI (nebo máte nějaký free trial), tak oba tyhle tooly umožňují dvě tabulky spojit a rovnou se na ně kouknout v grafech. Jako hodně tu bude záležet na tom, v jakém stavu jsou data v těch tabulkách, datové typy si v Tableau třeba upravíte, ale překlepy, prostě obecně “bordel v datech” už ne… 

Jak na to pro Tableau, třeba zde.

4. Mám dvě a více tabulek, různé sloupce a v Excelu je neotevřu

Ajaj, no tak tady už bude potřeba trochu toho kódění, ale nebojte se. Překecala jsem svého kolegu Tomáše Votavu, aby pro vás napsal takovou malou Python šablonu. Než se na to vrhneme, je potřeba mít v počítači nainstalovaný Python a stažený prográmek, ve kterém to budete psát, např. Atom nebo Virtual Studio Code (oboje je opensource). Vypadá to děsivě, ale nebojte se toho :-D

Malé vysvětlivky a návod:
“”” - to co je v těch 3 závorkách, je komentář k tomu, co to má dělat

Kód vložíte třeba do Atomu. Tabulka output-name je ta vaše výsledná. A teď záleží, kolik vstupních tabulek máte - podle toho input1, input2 atd. Takže si je podle toho i v počítači nejprve přejmenujte.

"""Make a union of multiple (2 to n) csv files into a single csv file

Usage:

python union.py <output-name> <input1> <input2> ... <inputN>

"""

import os

import sys

import csv

import logging

from typing import Sequence

logging.basicConfig(level=logging.INFO)

logger = logging.getLogger(__name__)

def die(*args, **kwargs):

    """Print and die"""

    logger.error(*args, **kwargs)

    sys.exit(1)

def get_header(fname: str) -> Sequence[str]:

    """Get header of csv file"""

    with open(fname, "r", encoding="utf-8", newline="") as fid:

        reader = csv.DictReader(fid)

        return reader.fieldnames or []

def join_csv(output: str, *inputs: str):

    """Join csv inputs into single output"""

    fieldnames = get_header(inputs[0])

    with open(output, "w", encoding="utf-8", newline="") as outfid:

        writer = csv.DictWriter(outfid, fieldnames=fieldnames, dialect=csv.unix_dialect, extrasaction="ignore")

        writer.writeheader()

        for infile in inputs:

            logger.info("Processing %s", infile)

            with open(infile, "r", encoding="utf-8", newline="") as infid:

                reader = csv.DictReader(infid)

                writer.writerows(reader)

def main():

    if len(sys.argv) < 4:

        die("Usage: %s <output-name> <input1> <input2> ... <inputN>", sys.argv[0])

    output = sys.argv[1]

    inputs = set(sys.argv[2:])

    if not len(inputs) >= 2:

        die("At least 2 distinct csv files must be provided")

    if os.path.exists(output):

        die("Output file %s already exists", output)

    if not all((os.path.exists(_input) for _input in inputs)):

        die("All input files must exist")

    join_csv(output, *inputs)

if __name__ == "__main__":

    main()

5. Mám dvě a více tabulek, různé sloupce, různé hodnoty v řádcích, v nich překlepy, duplikace a jiný bordel :-)

Tak takhle zaručeně vypadají snad všechny tabulky, co jsem měla možnost vidět. Teda pokud nemají jen 10 řádků, že, takže si to nejen otevřu v Excelu, ale i případně ručně opravím :-) V tento moment už nastupuje vyšší dívčí. Pokud máte zálusk na datovou analýzu, tady přesně bude gro vaší práce. 

Pro spojení tabulek se používají v SQL tzv. JOIN - jde o způsob, jak tabulky spojovat hlavně podle toho, jak chcete aby výsledná tabulka vypadala.

A jak dobře připomněl Honza Houdek, náš pravidelný čtenář: "Pokud bych měl dvě tabulky s objednávkami za rok 2019 a 2020 a potřeboval bych je spojit bylo by vhodnější použít spojení prostřednictvím UNION resp. UNION ALL. Použití klauzule JOIN bych volil v případě, pokud je mezi tabulkami nějaký vztah (na základě primárního/cizího klíče), tedy například spojit tabulku zákazníků s tabulkou objednávek na základě unikátního ID zákazníka." 

No, ale taky je hlavně potřeba mít je KDE spojit - čili kam je natáhnout, kde napsat JOIN a tak:-) My v BizzTreat na to používáme buď nástroj Keboola - super je jejich Pay as you Go program.  Anebo naše Bizzflow (dokumentace here), které je taky free v komunity edition a můžete ho nastavit na Amazonu AWS, Google Cloup Platform nebo MS Azure. Prostě tady už bohužel žádná jednoduchá rada není, tady už je to práce pro datového analytika. 

Na závěr malé shrnutí. Prostě, když máte malé tabulky, co chcete spojit, Excel je na to ideální. Jakmile je v excelu neotevřete, nemáte Excel, máte CSV, tabulky jsou veliké atd. je potřeba přistoupit třeba ke způsobům výše. Vždycky ale pozor na to, že data v tabulkách na vstupu obsahují hodně chyb (překlepy, duplikace, špatné formáty, mezery, nedoplněné hodnoty,...) nebo je například potřeba je na sebe namapovat, pak platí zlaté pravidlo “shit in = shit out”, takže pokud chcete koukat na ta správná čísla a rozhodovat se podle nich, bez datového analytika se neobejdete.