Popisní statistiky

Python v Excelu

Excel v Pythonu se nachází na kartě vzorce sekce Python:

Knihovny Pythonu v Excelu

Excel má automaticky importované následující knihovny:

• Numpy
• Pandas
• Matplotlib.pyplot
• Statsmodels
• Seaborn
• Excel
• Warnings

Tyto knihovny je možné nalézt pod možností Inicializace v sekci Pythonu:

Momentálně je inicializace pouze pro čtení. Do budoucna bude možné mít změnit výchozí nastavení importovaných knihoven, kdy bude možné si importovat vlastní knihovny jako výchozí.

Knihovny jsou importovány se zkratkou, např. pro numpy je zkratka np, pro pandas pd atd. Zkratka lze vidět za argumentem „as“. Touto zkratkou následně lze referencovat knihovnu v potřebném kódu před funkcemi, které tyto knihovny nabízí.

Pokud je nutné naimportovat vlastní knihovny, je možné importovat přímo v kódu do dané buňky. Kód pro import jiných knihoven má stejnou syntaxi jako výchozí knihovny:

Import [název knihovny]

Nepovinnou částí je pak argument AS [Vlastní zkratka pro knihovnu].

Vysvětlení výchozích knihoven:

Numpy – Knihovna pro práci s číselnými výpočty, více rozměrnými poli, velkými datovými sadami nebo maticemi.

Pandas – Práce s datovými strukturami a nástroje pro analýzu a manipulaci s daty. Jedná se o nejdůležitější knihovnu pro práci s datovými tabulkami v rámci Excelu, jelikož v Excelu se zejména pracuje s datovými tabulkami, které se v pythonu nazývají dataframes.

Matplotlib.pyplot – Knihovna pro tvorbu grafů a vizualizaci dat.

Statsmodels – Knihovna pro vizualizace dat založena na knihovně matplotlib s jednodušším rozhraním a více funkcemi pro analýzu dat. Slouží k pokročilým grafům.

Excel – Knihovna sloužící pro práci se soubory Excelu.

Warnings – Knihovna umožňující zachycení a úpravu chování chyb.

Základní práce s Pythonem v Excelu

Python se do buňky v Excelu volá pomocí =py

Po potvrzení bude u dané buňky i v části vzorce zelený obdélníky s „py“:

Potvrzení kódu je následně potřebné zmáčknout CTRL + ENTER. Pokud je zmáčknutý pouze ENTER, přenese se kurzor na další řádek v python kódu

Pokud je odkázáno na oblast buněk, do buňky s pythonem bude vložen dataframe, tedy celá datová tabulka přímo do jedné buňky.

Způsoby rozevření dat

Pro zobrazení dataframu do oblasti buněk stačí na buňku kliknout pravým tlačítkem a vybrat „výstup Python“ a zvolit Excelovská hodnota.

Druhý způsob je hned v oblasti vzorce:

• Klávesová zkratka CTRL + ALT + SHIFT + M

Pokud je naopak nutné sbalit hodnoty do objektu jedné buňky zpět, zvolí se možnost objekt pythonu namísto excelová hodnota.

Xl Funkce v pythonu v Excelu

Funkce xl v pythonu v Excelu nám dává možnost referencovat buňku nebo oblast buněk do Pythonu z Excelu. Pokud je zavolán python a kliknuto na nějakou další buňku, objeví se funkce xl:

Možné je referencovat více buněk v pythonu v Excelu, např. klasické matematické operace:

Stejně tak je možné referencovat pojmenované buňky, připojení na Power Query tabulky nebo oblasti.

Pokud je nutné vzorec v pythonu rozkopírovat, je nutné buňky zamknout stejně jako u klasické práce v Excelu. Zamykání hodnoty v buňce A2 pro vynásobení této hodnoty s hodnotami v sloupci „B“:

Buňka B2 je zamknutá klasickými dolary.

Možnosti oblastí v Pythonu

Do pythonu v Excelu lze dostat data několika způsoby, buďto z:

• Oblasti
• Tabulky
• Definovaného názvu
• Power Query

1. Z oblasti

Klasická reference na oblast buněk:

2. Tabulky

Data jsou referencovaná jako tabulka jako tabulka (vložena přes kartu vložení → tabulka. Následně referencovat název tabulky:

3. Definovaný název

Nutné označit oblast buněk, které mají být pojmenovány. Změnu názvu lze provést vedle oblasti funkce:

Název je pak referovaný v pythonu:

4. Power Query

Napojení dat na jakýkoliv zdroj pomocí Power Query na kartě data –> načíst data a vybrat požadovaný zdroj, který následně lze referencovat v Pythonu:

V Excelu se opět objeví daný dataframe:

Napojení dat je možné pomocí PQ z:

• Souborů (Excel, csv, txt, xml a další)
• SQL
• ODBC připojení
• Azure
• Webu
• Power platformy
• Jiných zdrojů

Editor & Excel labs

Na kartě vzorce lze v sekci python rozkliknout možnost editor, kdy se ukáže daný editor vpravo v Excelu. (Není vidět editor na kartě Excelu? Je možné naimportovat Excel labs)

Pomocí něj lze vložit kód do buňky a je možné dále tvořit kód přímo něj, jelikož přímo v sekci vzorce může být složité nebo omezující kód psát. Po kliknutí, do jaké buňky má být kód vložen, je možné psát daný kód spojený s danou buňkou:

Nahoře je vidět, v jaké buňce se kód nachází. Je možné jej pomocí diskety uložit, převést na excelovskou oblast (nebo naopak na objekt), zvětšit dané okno ikonou úplně vpravo, nebo python kód vymazat pomocí tlačítka zrušit dole.

Dole je následně možnost přidat kód Pythonu i do dalších buněk v Excelu:

Následně pro každou buňku je možné v editoru vidět daný kód:

V editoru lze používat jakékoliv klávesové zkratky jako v klasických Python editorech jako ve VSCode.

Editor může být stále v beta verzi a pokud není na kartě vzorce vidět, je možné využít Excel labu. Excel lab lze importovat pomocí doplňků → nalezení Excel labu:

Excel labs následně bude na kartě domů:

Při kliknutí je nutné dole vybrat „Python Editor“, popř. zakliknout make default, aby se toto okno již nezobrazovalo:

Excel lab má následně stejné využití jako klasický python editor:

Spouštění Pythonu ve správných buňkách

Exekuce pythonu v Excelu funguje podobně jako reference na některé funkce. Není možné referencovat buňku, která obsahuje python v buňce, která je více nalevo nebo výše, než buňka, ve které se python buňka nachází.

V následujícím případu je python v buňce B2. Pokud bychom chtěli tuto buňku referencovat v nové python buňce, která by byla v buňce A1, naskytl by se error s hodnotou 0, jelikož nemůže být referencována buňka více nalevo a výše, než buňka B2.

Proto je nutné tuto novou python buňku dát od B2 buďto více doprava nebo níže, popř. obojí. Referencovat v pythonu buňku B2 by tak šlo např. v buňce B3, C2 nebo C3 a další dále napravo resp. nižší buňky.

Datové typy

String – Textový řetězec, klasický text

Datový typ	Python	Datový typ z buňky
String:	Ahoj	<class ‚str‘>

Integer – Celé číslo

Datový typ	Python	Datový typ z buňky
Integer:	5	<class ‚int‘>

Float – Desetinné číslo

Datový typ	Python	Datový typ z buňky
Float:	4,5	<class ‚float‘>

Boolean – binární proměnná PRAVDA/NEPRAVA (TRUE/FALSE)

Datový typ	Python	Datový typ z buňky
Boolean:	PRAVDA	<class ‚bool‘>

Date – Datum

Datový typ	Python	Datový typ z buňky
Date:	17.12.2024	<class ‚datetime.date‘>

List – seznam hodnot, mohou být unikátní ale i jedinečná. Přidávají se do hranatých závorek []. Může obsahovat hodnoty stejného datového typu nebo i odlišných typů. Do seznamu lze hodnoty přidávat, měnit nebo je mazat různými funkcemi.

Datový typ	Python	Datový typ z buňky
List:	list	<class ‚list‘>

Příklad listu: li = [1,2,3]

Po rozevření tabulky jsou v listu následující hodnoty:

Dict – slovník hodnot ve formátu klíč: hodnota. Ke každému klíči je přiřazena určitá hodnota. Klíčů a hodnot může být několik. Slovník se udává ve složených závorkách {}. Je indexovatelný.

Příklad slovníku: data = {„jmeno“: „Jana“, „prijmeni“: „Novotna“, „vek“: 35}

Tuple – Uspořádaná kolekce prvků, podobná jako list, ale oproti němu je neměnitelný (nelze hodnoty přidávat, mazat nebo měnit). Může obsahovat hodnoty stejných nebo odlišných datových typů. Hodnoty mohou být duplikované. Je indexovatelný.

Po rozevření buňky jsou v tuple následující hodnoty:

Příklad tuple: tup = (1, „Robin“, True)

Set – Kolekce unikátních, neuspořádaných prvků. Není tedy možné mít více stejných hodnot v jednom setu. Lze hodnoty měnit, přidávat nebo mazat. Nemá indexy.

Příklad setu:

set_type = [2,2,2,1,2,1,1,3,3]

type(set(set_type))

Po rozevření jsou v setu následující hodnoty:

Dataframe – datová tabulka hodnot. Může se jednat o hodnoty z daných buněk, které jsou všechny převedeny do Pythonu.

Datový typ	Python	Datový typ z buňky
Dataframe:	DataFrame	<class‚pandas.core.frame.DataFrame‘>

Image – Obrázek nebo graf.

Datový typ	Python	Datový typ z buňky
Image:	Image	<class ‚PIL.Image.Image‘>

Příklad obrázku:

from PIL import Image, ImageDraw

img = Image.new(mode=“RGB“, size=(50,10), color=(50,50,50))

ctx = ImageDraw.Draw(img)

ctx.text((20,30), „Ahoj“, fill=“white“)

img

Základní popisní statistiky

Uvažujme proměnnou s názvem df, ve které budou uloženy data z Excelu, na kterých se bude provádět základní popis dat.

df = xl(„A1:F105“, headers=True)

Head(n)

Definice

Zobrazí prvních n řádků z dataframe.

Argumenty

n (int, volitelný) – Počet řádků, které mají být zobrazeny. Pokud není uvedeno, výchozí hodnota je 5 řádků.

Použití

Df.head() – Zobrazí prvních 5 řádků z dataframe.

Df.head(10) – Zobrazí prvních 10 řádků z dataframe.

Df.head(-3) – Zobrazí všechny řádky kromě posledních 3 v dataframe.

Tail(n)

Definice

Zobrazí posledních n řádků z dataframe.

Argumenty

n (int, volitelný) – Počet posledních řádků, které mají být zobrazeny. Pokud není uvedeno, výchozí hodnota je 5 posledních řádků.

Použití

Df.tail() – Zobrazí posledních 5 řádků z dataframe.

Df.tail(10) – Zobrazí posledních 10 řádků z dataframe.

Df.tail(-3) – Zobrazí všechny řádky kromě prvních 3 v dataframe.

Info

Definice

Poskytuje přehled o struktuře dat v dataframe, včetně názvu sloupců, datového typu a počtu neprázdných hodnot.

Praktické vysvětlení

Informace se nepropíšou přímo do buňky, místo toho se propíšou informace do editoru nebo do Excel labu:

Informace se nepropíšou přímo do buňky, místo toho se propíšou informace do editoru nebo do Excel labu:

Argumenty

Verbose (bool, volitelný) – zobrazí všechny detaily, výchozí hodnota je true. Pokud je nastaveno na false, zobrazí se pouze základní informace.

Buf (file object, volitelný) – Kam poslat výstup (např. soubor nebo sys.stdout). Defaultně je výstup tištěn do konzole.

Max_cols (int, volitelný) – Maximální počet sloupců, které se zobrazí. Defaultně se zobrazuje vše.

Memory_usage (bool, volitelný) – Zda zobrazit informace o paměťové náročnosti. Pokud je nastaveno na ‚deep‘, zobrazí podrobnější výpočet. Defaultní hodnota je True.

Použití

Df.info() – výchozí nastavení informací.

Df.info(verbose=False) – Zobrazí se pouze základní detaily.

Df.info(max_cols=3) – Zobrazí se 3 sloupce z tabulky info. Tedy sloupec index, názvy sloupců a datový typ. Sloupec počtu nenulových hodnot se nezobrazí, jelikož se jedná o 4 sloupec.

Df.info(memory_usage=“deep“) – Zobrazí se přesný údaj o využití paměťové náročnosti.

Všechny argumenty se mohou jakkoliv kombinovat.

Describe()

Definice

Funkce generuje statistické shrnutí numerických (nebo jiných) sloupců.

Praktické vysvětlení

Vypíše se statistická tabulka přímo do Excelu při zobrazení excelovských hodnot. Pokud jsou v tabulce datumy, i tento sloupec bude v souhrnu zahrnutý, ačkoliv statistické hodnoty nejsou přesné. Funkce nemá žádné povinné argumenty.

Při výchozím nastavení bez argumentů se zobrazí následující sloupce:

• Count – Počet hodnot v jednotlivých sloupcích
• Mean – Průměr z hodnot v dataframu
• Min – minimální hodnota v daném sloupci
• 25% – hodnota prvního kvartilu
• 50% – medián hodnot
• 75% – hodnota třetího kvartilu
• Max – maximální hodnota v sloupci
• Std – směrodatná odchylka

Argumenty

Percentiles (list, volitelný) – jaké kvartily se v tabulce mají zobrazit, výchozí nastavení na 0.25, 0.5, 0.75, stejně jak je vidět v tabulce výše. Percentily, které se mají zobrazit, je nutné zapsat do listu.

Include (list, volitelný) – Specifikuje datové typy, které mají být zahrnuty (např. [‚object‘, ‚number‘]). Při zvolení možnost ‚all‘ do argumentu budou v tabulce zahrnutý všechny typy. Pro nečíselné hodnoty budou vyhozeny errory.

Exclude (list, volitelný) – Specifikuje datové typy, které mají být vyloučeny.

Použití

df.describe() – základní statistiky výchozích sloupců, které jsou automaticky zahrnuty

df.describe(percentiles=[0.1, 0.3, 0.5, 0.8]) – Zobrazení vlastních percentilů, tedy 10%, 30%, medián a 80% namísto výchozích 25%, median, 75%.

df.describe(include=[‚int‘]) – Zahrnutí pouze datového typu int do statistik

df.describe(include=’all‘) – Zahrnutí všech datových typů

df.describe(exclude=[‚object‘, ‚datetime64‘]) – Vyloučení datového typu object a datetime64

Shape

Definice

Vrátí počet řádků a sloupců v daném dataframu.

Praktické vysvětlení

Funkce se nevolá, takže se za ní nezadávají závorky. Hodnoty se vrací v datovém formátu tuple. Při zobrazení do buněk v Excelu se vždy zobrazí 2 řádky v jednom sloupci, kdy v prvním řádku je počet řádků v dataframu a ve druhém řádku počet sloupců v dataframu.

Argumenty

Funkce neobsahuje žádné argumenty.

Použití

Rows, cols = df.shape – Do proměnné „rows“ se zadá počet řádků a do proměnné cols počet sloupců, s kterými lze dále pracovat.

Columns

Definice

Funkce vrátí seznam názvů sloupců dataframe jako index objekt.

Praktické vysvětlení

Funkce se nevolá, takže se za ní nezadávají závorky. Názvy sloupců budou v jednom sloupci pod sebou v pořadí, v jakém se nachází v datové tabulce.

Po rozevření indexu se zobrazí dané sloupce:

Argumenty

Funkce neobsahuje žádné argumenty.

Použití

df.columns – vrátí názvy sloupců z dataframe.

Index

Definice

Vrací index dataframe (např. rozsah nebo konkrétní hodnoty, pokud byl index upraven).

Argumenty

Funkce neobsahuje žádné argumenty, jelikož se nevolá.

Použití

df.index – vrátí index dataframe

Dtypes

Definice

Funkce vrátí datové typy sloupců v dataframe.

Praktické vysvětlení

Do funkce se nevkládá argument, ke kterému sloupci je potřebné vrátit datový typ, vrátí datové typy všech sloupců v dataframu, které se v něm nachází.

Argumenty

Funkce neobsahuje žádné argumenty.

Použití

df.types – vrátí datový typ všech sloupců dataframu v proměnné df.

Memory_usage

Definice

Funkce zobrazuje paměťovou náročnost datové tabulky dle jednotlivých sloupců v bajtech

Praktické vysvětlení

Pokud nejsou nastaveny nepovinné argumenty, tak funkce pro každý sloupec vrátí přibližnou hodnotu použití paměti včetně indexu, který není při zobrazení dataframu vidět. Pokud je potřebné zobrazit přesnou náročnost sloupců, je nutné nastavit argument „deep“ na true.

Funkce vrátí 2 sloupce. V prvním sloupci se nachází názvy všech sloupců v dataframe. V druhém sloupci je paměťová náročnost v bajtech.

Argumenty

Index (bool, volitelný) – Zda zahrnout i paměťovou náročnost indexu (výchozí je nastavení na PRAVDA)

Deep (bool, volitelný) – Pokud je nastaveno na PRAVDA, zobrazí přesnější (hlubší) výpočet paměťové náročnosti.

Použití

Df.memory_usage() – základní informace o paměťové náročnosti.

Df.memory_usage(index=False) – nezobrazí se informace o indexu dataframu.

Df.memory_usage(deep=True) – zobrazí se podrobné informace o paměťové náročnosti.

Df.memory_usage(index=True, deep=False) – stejné nastavení, jako při možnosti df.memory_usage(), akorát jsou argumenty specifikovány i uvnitř funkce.

Isnull

Definice

Funkce vrátí dataframe stejné velikosti s hodnotami „true“ v případě, kdy buňka obsahuje prázdnou hodnotu (NaN) a „false“ pro buňky, které jsou vyplněné.

Praktické vysvětlení

Funkce vrátí pouze stejnou oblast buněk s hodnotami true nebo false.

Pokud je nutné zjistit počet prázdných hodnot v daných sloupcích dataframu, je možné za funkci zadat agregační funkce sum().

Jedná se o opačnou funkci notnull.

Argumenty

Funkce neobsahuje žádné argumenty.

Použití

Df.isnull() – vrátí dataframe o stejné velikosti jako původní dataframe, s hodnotami true/false na základě vyplněných nebo nevyplněných hodnot.

Df.isnull().sum() – vrátí v prvním sloupci názvy sloupců dataframu a v druhém sloupci počet prázdných hodnot v jednotlivých sloupcích.

Notnull

Definice

Funkce vrátí dataframe stejné velikost s hodnotami „true“ pro buňky obsahující vyplněné hodnoty a „false“ pro buňky s prázdnými hodnotami (NaN)

Praktické vysvětlení

Funkce vrátí pouze stejnou oblast buněk s hodnotami true nebo false.

Pokud je nutné zjistit počet prázdných hodnot v daných sloupcích dataframu, je možné za funkci zadat agregační funkce sum().

Jedná se o opačnou funkci isnull. Existuje funkce stejná funkce Notna, která vrátí stejné výsledky jako funkce notnull, jedná se však o starší verzi funkce notnull v dřívější verzi pandas.

Argumenty

Funkce neobsahuje žádné argumenty.

Použití

Df.notnull() – vrátí dataframe o stejné velikosti jako původní dataframe, s hodnotami true/false na základě vyplněných nebo nevyplněných hodnot.

Df.notnull().sum() – vrátí v prvním sloupci názvy sloupců dataframu a v druhém sloupci počet vyplněných hodnot v jednotlivých sloupcích.