Hana Chajdová: Letní olympijské hry v letech 1886-2008

(V tomto příspěvku jsou jen strohá data a postupy. Co se dělo a děje v pozadí mého snažení v DA, mám rozepsáno. Nechci míchat dohromady moje postřehy, pocity a okolnosti, za kterých jsem se DA účastnila. Proto jsem se rozhodla napsat příspěvky dva. Tento stručný a druhý zcela lidský a ukecaný. Doufám, že dostanu svolení publikovat na tomto blogu i druhý příspěvek. :-)

Takže nyní k věci:
          Moje výchozí pozice týkající se znalostí z IT či datové analýzy by šla charakterizovat jako nepopsaný list. Proto jsem původně vymyslela téma týkající se hodnocení života seniorů v některých krajích ČR. Naštěstí, než jsem téma nahlásila, ukázalo se, že problémem bylo, že údaje z Českého statistického úřadu nejsou ve formátu, ve kterém bych je mohla dále zpracovávat. Nikoli tedy „surová data“, ale tabulky ve formátu .pdf, ze kterých bych musela potřebná data náročným způsobem získávat. Podle kvalifikovaného odhadu jedné z lektorek bych je nebyla schopná vyčistit ani do termínu odevzdání závěrečného projektu.

Pro svůj závěrečný projekt jsem si tedy vybrala zpracování dat o Letních Olympijských hrách z let 1886 – 2008 pomocí znalostí nabytých v DA. Téma sportu je mi blízké, neboť sama rekreačně hraji volejbal, plážový volejbal a tenis.

Při vypracování projektu jsem si ověřila a použila tyto znalosti:

Excel – jako formát zdrojového datového setu a nástroj pro čištění dat

MsSQL - tvorba databáze, vytvoření nového uživatele a hesla, připojení k serveru, import zdrojových dat pomocí příkazového řádku a příprava tabulek z importovaného dokumentu (použití příkazů CREATE TABLE, TRUNCATE TABLE, DROP TABLE, INSERT INTO, UPDATE, SELECT, SELECT DISTINCT, UPDATE, SET, JOIN, …)

PowerBI – naimportování dat z SQL databáze, úprava relací mezi tabulkami, vizualizace pomocí grafů a tabulek, filtrování dat

Python – skript obsahující začátek hymny ČR (na základě naimportování knihoven WINSOUND a TIME a příkazu pro čtení textového souboru s vypsanými notami)

Přesahem do budoucna s využitím měl být proklik na stránky Kiwi.com hlídající cenu letenek do Tokia, kde se budou v roce 2020 konat další Letní olympijské hry.

Základem pro moji práci byla data uvedená v tabulkách ve formátu .xlsx, tedy v Excelu, která jsem našla jako volně přístupná na internetu na adrese

https://docs.google.com/spreadsheets/d/1zeeZQzFoHE2j_ZrqDkVJK9eF7OH1yvg75c8S-aBcxaU/edit#gid=0.

            Prvním krokem bylo podrobné seznámení se s údaji, které soubor obsahoval. Jednalo se o několik listů, z nichž jsem využila 2 tabulky:

Tabulku č. 1 obsahující sloupce s názvy měst, v nichž se Letní olympijské hry konaly, rok, kategorii sportu (např. vodní, atletická, atd.), sportovní disciplínu (např. plavání, atletika), jméno medailisty (ve formátu PŘÍJMENÍ, Jméno), kód země, kterou medailista reprezentoval, jeho pohlaví, název konkrétního závodu nebo soutěže, za který medaili získal (např. 100m volný způsob), kategorii závodu (mužská, ženská, smíšená) a název medaile.

Tabulku č. 2 obsahující název země, zkratku země podle mezinárodního olympijského výboru (noc) a zkratku země ISO_code. (Tato tabulka mi posloužila jako základ pro vytvoření číselníku zemí v databázi).

Nejprve jsem provedla čištění dat v Tabulce č. 1, kde bylo nutné rozdělit příjmení a jméno medailistů do samostatných sloupců. K tomu jsem využila funkce v Excelu, kdy jsem nejdříve určila pozici čárky mezi příjmením a jménem a oddělila jsem pomocí funkce ZLEVA do samostatného sloupce příjmení. V několika málo případech bylo v původní tabulce uvedeno pouze samotné příjmení, proto se při použití funkce ZLEVA, která vyžadovala pozici čárky, objevila chyba, protože žádná čárka se tam nevyskytovala. Vytvořila jsem proto další sloupec, kde jsem nadefinovala, aby se v případě výskytu chyby do řádku opsal text ze sloupce s původním příjmením, tedy ze sloupce E. Poté jsem určila celkovou délku příjmení a jména v původním sloupci. Pro oddělení a zapsání samostatného jména jsem použila opět funkce v Excelu, a sice funkci ZPRAVA, pomocí které jsem pak odečetla pozici čárky a ještě jedno místo, které zaujímala mezera. Vytvořila jsem další sloupec, kde jsem pomocí funkce IF nadefinovala, aby se v případě výskytu chyby v řádku (není k dispozici pozice čárky) nechal řádek prázdný.

S mentorkou jsem vypracovala model databáze obsahující tabulky, které jsem později vytvořila v databázi MSSQL a naplnila daty z tabulek.

Dalším krokem byl import dat z tabulek do databáze. Před tím jsem vytvořila v rámci serveru SQL databázi nazvanou Zaverecny_projekt. Tabulky z Excelu jsem vyexportovala do csv. souboru a v textovém editoru jsem změnila kódování na „unicode“. Poté jsem se je pomocí příkazového řádku pokusila naimportovat. Při importu se několikrát objevily chyby, které byly způsobeny např. použitím obráceného lomítka, špatně umístěnou mezerou, špatným typem (varchar namísto nvarchar) a také malým počtem znaků uvedených u typu v závorce. Po úspěšném importu jsem již mohla začít v MicrosoftSQL pracovat.

Zde je ukázka postupu vytváření tabulek v databázi:

select * from import_medallists

DELETE FROM import_medallists WHERE city = 'City'

CREATE TABLE import_zemi(

country NVARCHAR(130),

int_olympic_committee_code NVARCHAR(30),

ISO_code NVARCHAR(30)

)

select distinct country from import_zemi

DELETE FROM import_zemi WHERE country = 'country' --smazání prvního řádku, který byl dvakrát

CREATE TABLE category (

id_category INT identity (1,1),

category NVARCHAR(30))

INSERT INTO category (category)

SELECT DISTINCT sport

FROM import_medallists

SELECT * FROM category

SELECT DISTINCT category FROM category

CREATE TABLE competition(

id_comp INT identity (1,1),

name_comp NVARCHAR(100),

id_olymp INT,

id_disc INT,

id_group INT

)

INSERT INTO competition (name_comp)

SELECT DISTINCT competition

FROM import_medallists

SELECT * FROM competition

SELECT DISTINCT name_comp FROM competition

CREATE TABLE discipline (

id_disc INT identity (1,1),

name_disc NVARCHAR(100),

id_category INT,

)

INSERT INTO discipline (name_disc)

SELECT DISTINCT discipline

FROM import_medallists

SELECT * FROM competition

SELECT * FROM olympics

CREATE TABLE olympics (

id_olymp INT identity (1,1),

year INT,

country NVARCHAR(80),

city NVARCHAR(80)

)

INSERT INTO olympics (year, city)

SELECT (edition),

(city)

FROM import_medallists

SELECT DISTINCT city FROM import_medallists

SELECT * FROM olympics

CREATE TABLE medallists (

id_medallist INT identity (1,1),

surname NVARCHAR(100),

name NVARCHAR(50),

gender NVARCHAR(20),

noc NVARCHAR(10)

)

INSERT INTO medallists (surname, name, gender, noc)

SELECT distinct (surname),

(name),

(gender),

(noc)

FROM import_medallists

SELECT * FROM medallists

CREATE TABLE medal(

id_comp INT,

id_medallist INT,

medal NVARCHAR(100)

)

Select distinct  * from import_medallists i inner join medallists m ON m.surname = i.surname and m.name = i.name

select * from import_medallists

select * from medallists

SELECT * FROM competition JOIN discipline ON competition.id_disc = discipline.id_disc

INSERT INTO olympics(year, city) --vložení vyfiltrovaných dat do sloupečků (26 řádků)

SELECT DISTINCT edition,

city

FROM import_medallists

SELECT * FROM olympics

Update olympics set country = 'GRE' where city ='Athens'

Update olympics set country = 'MEX' where city ='Mexico'

Update olympics set country = 'GRE' where city ='Athens'

Update olympics set country = 'GBR' where city ='London'

Update olympics set country = 'CAN' where city ='Montreal'

Update olympics set country = 'FRA' where city ='Paris'

Update olympics set country = 'KOR' where city ='Seoul'

Update olympics set country = 'JPN' where city ='Tokyo'

Update olympics set country = 'USA' where city ='Atlanta'

Update olympics set country = 'BEL' where city ='Antwerp'

Update olympics set country = 'GER' where city ='Berlin'

Update olympics set country = 'FIN' where city ='Helsinki'

Update olympics set country = 'RUS' where city ='Moscow'

Update olympics set country = 'NED' where city ='Amsterdam'

Update olympics set country = 'CHN' where city ='Beijing'

Update olympics set country = 'USA' where city ='Los Angeles'

Update olympics set country = 'AUS' where city ='Sydney'

Update olympics set country = 'SWE' where city ='Stockholm'

Update olympics set country = 'USA' where city ='St Louis'

Update olympics set country = 'ESP' where city ='Barcelona'

Update olympics set country = 'ITA' where city ='Rome'

Update olympics set country = 'GER' where city ='Munich'

Update olympics set country = 'AUS / SWE' where city ='Melbourne / Stockholm'

SELECT DISTINCT city

FROM import_medallists

select * from import_zemi 

select distinct noc from import_zemi

select * from olympics join import_zemi on import_zemi.noc = olympics.country

Select * from discipline

select * from category

select sport, discipline from import_medallists

select distinct name_disc from discipline

--začaly jsme od kategorie, protože ta nemá žádný cizí klíč

/** na ni má vazbu tabulka discipline, kde jsme z importované tabulky musely zjistit název kategorie a poté podle názvu kategorie vyhledat id kategorie v tabulce kategorií, kterou už jsme měly nainsertovanou**/

Insert into discipline (name_disc,id_category)

Select distinct i.discipline,c.id_category from import_medallists i join category c ON c.category = i.sport

select * from discipline

--vytvoření číselníku pro medaile

CREATE TABLE cis_medal (value INT, name_medal NVARCHAR(10)

)

INSERT INTO cis_medal (value, name_medal) values (3,'gold')

INSERT INTO cis_medal (value, name_medal) values (2,'silver')

INSERT INTO cis_medal (value, name_medal) values (1,'bronze')

SELECT * FROM cis_medal

SELECT * FROM import_medallists

-- změníme ve sloupečku medal v importované tabulce textovou hodnotu na číselnou, aby byla vidět v číselníku

UPDATE import_medallists set medal = 3 where medal = 'gold'

UPDATE import_medallists set medal = 2 where medal = 'silver'

UPDATE import_medallists set medal = 1 where medal = 'bronze'

SELECT * FROM import_medallists

--vypsat jedinečné hodnoty pro edition a id olympiády podle olympiády z importované tabulky

SELECT DISTINCT i.edition, o.id_olymp from import_medallists i join olympics o ON i.edition = o.year

--vypsat id disciplíny k výše uvedeným záznamům

SELECT DISTINCT i.edition,o.id_olymp,d.id_disc from import_medallists i join olympics o ON i.edition = o.year join discipline d ON i.discipline = d.name_disc

--vypsat rok, disciplínu a konkrétní závod

SELECT DISTINCT i.competition,i.edition, i.discipline, o.id_olymp from import_medallists i join olympics o ON i.edition = o.year

--chci dopsat hodnoty do tabulky competition

select * from competition

--zjistila jsem, že ještě nemám číselník pro ženskou, mužskou a smíšenou kategorii (cis_comp_gender)

CREATE TABLE cis_comp_gender(

code_gender NVARCHAR(10),

name_gender NVARCHAR(20)

)

INSERT INTO cis_comp_gender (code_gender, name_gender) values ('M','man')

INSERT INTO cis_comp_gender (code_gender, name_gender) values ('W','woman')

INSERT INTO cis_comp_gender (code_gender, name_gender) values ('X','mixed')

select * from competition

INSERT INTO competition (name_comp, id_olymp, id_disc, comp_gender) Select distinct i.competition, o.id_olymp, d.id_disc, i.comp_gender from import_medallists i join olympics o ON i.edition = o.year

join discipline d ON i.discipline = d.name_disc

select * from cis_comp_gender

select * from competition

insert into medal

select i.medal,c.id_comp,m.id_medallist from import_medallists i

join competition c on c.name_comp = i.competition and c.comp_gender = i.comp_gender

 join olympics o on o.year = i.edition and c.id_olymp= o.id_olymp

 join medallists m  on m.name = i.name and m.surname = i.surname and m.noc = i.noc and m.gender = i.gender

 CREATE TABLE medal(

medal NVARCHAR(100),

id_comp INT,

id_medallist INT,

)

select * from medal

Po vytvoření databáze s tabulkami v SQL jsem začala pracovat na vizualizaci v PowerBI. Pomocí tlačítka Get Data se zobrazila nabídka, našla jsem tam možnost SQL server a zobrazilo se okénko:

Zadala jsem název serveru a databáze, vybrala jsem potřebné tabulky a chtěla jsem začít s grafy. Musela jsem však nejdříve upravit některé vazby mezi jednotlivými tabulkami do podoby uvedené na str. 3.  Teprve pak jsem mohla začít s vizualizací.

Graf č. 1 ukazuje, jak v průběhu času přibývá jednotlivých medailistů, neboť se soutěží v mnohem více závodech a disciplínách, než dříve. Medailisty jsem rozdělila podle kategorií, ve kterých soutěžili, takže křivky zobrazují kategorii mužskou, ženskou a smíšenou. Je jasně vidět nárůst ženských kategorií v posledních letech.

Graf č. 2 ukazuje přesná čísla pro medailisty v ženské kategorii v průběhu let. Zaujal mě fakt, že první ženy se zúčastnily Olympiády v roce 1900, že medailistek bylo jen 10 a zajímalo mě, ve kterých disciplínách soutěžily. Tuto analýzu jsem si zobrazila v Tabulce 1.

Všimla jsem si, že v tabulce se vyskytovala zkratka země BOH, tedy BOHEMIA. Mezi prvními medailistkami na Olympiádě tedy byla česká sportovkyně. Viz tabulka s textem z Wikipedie:

Graf č. 3 zobrazuje, jak se měnil počet a zastoupení různých sportovních kategorií a disciplín v čase. Vzhledem k tomu, že se jedná pouze o printscreen, není vidět, že graf je v PowerBI interaktivní: při najetí kurzoru do grafu se zobrazí linie pro daný rok a seznam názvů aktuálního zastoupení jednotlivých sportovních kategorií a počet disciplín, které se v této kategorii vyskytují. Zajímavý na tomto grafu je fakt, že vodní kategorie sportů (zobrazena zeleně) má od začátku pořádání Olympijských her stejný počet disciplín a je v programu zastoupena nepřetržitě v celé jejich historii.

Závěrečné dvě tabulky jsou věnované mým oblíbeným disciplínám: volejbalu a tenisu. Vypsala jsem si jména československých medailistů v uvedených disciplínách:

Tabulka 2 – volejbal

Tabulka 3 – tenis

Protože jsem chtěla do svého projektu zahrnout i programování v Pythonu, ale nevěděla jsem, jak ho do daného tématu zakomponovat, vymyslela jsem si, že naprogramuji hymny některých zemí. Nakonec jsem napsala skripty pro začátky české hymny a hymny USA, které uvádím níže:

Aby skript fungoval, musí být ve stejné složce, jako soubor „hymna_CR.py“ uložen i textový soubor „hymna_CR.txt“, který má tuto podobu:

Skript pro začátek hymny USA:

Aby skript fungoval, musí být ve stejné složce, jako soubor „hymna_USA.py“, uložen i textový soubor „hymna_USA.txt“, který má tuto podobu:

Závěr:

Procvičila jsem si znalosti nabyté v DA týkající se Excelu, MSSQL, PowerBI a Pythonu.

Jako přesah do budoucna jsem si původně vymyslela, že bude možné ze stránky s projektem prokliknout na stránku firmy Kiwi.com, kde bude nastaveno hlídání ceny letenek z Prahy do Tokia. V Tokiu se totiž budou v roce 2020 konat další Letní olympijské hry. Svůj záměr jsem konzultovala s koučem Vaškem Sauerem po lekci programování v Kiwi. Řekl mi, že by toto bylo možné, kdyby se parametry letu objevovaly ihned v URL adrese, ale vyhledávání letů na stránkách Kiwi.com funguje na jiném principu. Nastavení parametrů letu se musí udělat přímo na stránkách manuálně, proto nelze proklik přímo na vybraný let zrealizovat.

Vymyslela jsem proto alespoň náhradní strategii:

Na základě zhlédnutí (návštěv) stránek zabývajících se sportovní tematikou nebo dřívější návštěvy LOH (zjištěno z diskusí na sociálních sítích nebo ze sdílených fotografií) nebo na základě poptávky po vstupenkách na LOH v Tokiu (spolupráce s distributory?) posílat online nabídky na letenky do Tokia.

Vzhledem ke zdravotním problémům jsem již nestihla navrhovanou strategii konzultovat.