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.

Máte k článku nějaké otázky nebo připomínky? Klidně mi napište, ráda to s Vámi proberu :-)

Eva Hankusová
Data & Business Consultant
marketing | partnership
LinkedIn

Data Wrangling aneb zkroťte svá data! | Mňamka #260

Data Wrangling aneb zkroťte svá data! | Mňamka #260

Co má společné kovboj a datový detektiv? Jeden krotí dobytek a druhý data! Ne, vážně, říká se tomu data wrangling. Tento datový úkon se skládá ze čtyř kroků, ve kterých se data objevují, transformují, validují a pak publikují. Ať už vás zajímá, jak se vnořit do dat k use case, proč data transformovat a jaké operace do tohoto kroku patří nebo jak data validovat, článek vás rozhodně zaujme. Není čas ztrácet čas, tak šup do článku, ať se dozvíte něco nového a nezapomeňte si dokumentovat vaše kroky!

Osm nejlepších data analytických online kurzů for free | Mňamka #259

Osm nejlepších data analytických online kurzů for free | Mňamka #259

Tento článek je určen hlavně pro lidi, kteří se chtějí v datech posunout trochu dál. Najdete v něm 8 kurzů na různých webech, portfolio začíná u absolutního intra do datové analytiky až po kurzy se zapojením některých knihoven pro Python jako Pandas a NumPy. Každý kurz má popsanou délku, náročnost, potřebné znalosti a také disponuje krátkým popisem. Chtěli jste s daty začít? Právě teď může být ta správná chvíle kdy se k tomu konečně dostanete.

Nástrahy v bludišti data science: jak být dobrým konzultantem, nikoli pouze nerdem | Mňamka #258

Nástrahy v bludišti data science: jak být dobrým konzultantem, nikoli pouze nerdem | Mňamka #258

V dnešním in-house článku se s vámi Marek podělí o trochu data scientist sebereflexe. Jde o vztah mezi manažery a data scientisty. Obě skupiny se navzájem znají a disponují touhou dosáhnout stejných cílů, těmi cíli jsou zodpověděné otázky, které pomohou byznysu a povedou k jeho zlepšení. To bohužel neznamená, že jejich vztah je vždy růžový, ale nezoufejte! Marek vám dá pár tipů k tomu, jak s manažery lépe vyjít. Proč data scientisti mluví divně? Z čeho mají největší radost? A jak to že si často myslí, že cesta je cíl? To a mnohem víc najdete v této mňamce.