Hana Chajdová: Letní olympijské hry v letech 1886-2008
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.
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
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 2 – volejbal
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:
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.
Komentáře
Okomentovat