Nezastavujeme, máme zpoždění!
Autorky: Kateřina Turková & Adéla Brajerová
Mentorky: Ivana Kozak & Barbora Pospíchalová (Accenture)
Poznání parťačky a Meet Your Mentor
Obě jsme se poznaly na prerekvizitním kurzu Pythonu. Už tam jsme našly společnou řeč a na první lekci akademie bylo jasné, že budeme spolupracovat na projektu od začátku do konce.
Na akci Meet Your Mentor Iva s Bárou byly nadšené z našeho tématu, a tak jsme byly moc rády, že jsme si je vybraly do týmu a ony nás.
Výběr tématu a získávání dat
Téma veřejné dopravy nám bylo oběma blízké a hromadnou dopravu využívá statisíce lidí denně. Data jsme získaly přímo z ROPIDu, který si data o zpoždění začali interně shromažďovat pro svoje vlastní zpracování a vyhodnocovaní. K datasetu jsme obdržely základní informace o struktuře, názvy sloupců a jaký je jejich hrubý obsah. Stěžejní informace pro nás byla výběr správného GPS záznamu pro odjezd autobusu ze zastávky a příjezd tramvaje do zastávky. Přínosem také bylo, že při zpracování projektu nám byly zaměstnanci k dispozici pro otázky, na které jsme potřebovali odpověď.
Cíle projektu a stanovení hypotéz
Naše kroky vedly ke stanovení toho, pro koho by byla analýza zpoždění užitečná. Ubraly jsme se dvěma směry a to analýzou, která by byla užitečná pro samotné cestující a pro analytiky (pracovníky ROPIDu). Stěžejní prací bylo vytvořit interaktivní mapy. Stanovily jsme si za cíl zjistit, jaká je infrastruktura veřejné dopravy v Praze. Zajímaly nás otázky, jaká jsou zpoždění na zastávkách MHD, je vyšší zpoždění u autobusů nebo tramvají, mění se nebo existují rozdíly ve zpoždění v jednotlivých dnech, mají autobusy a tramvaje menší zpoždění ve všedni den než o víkendu?
Požadavky klienta (ROPIDu) bylo vytvořit graf s boxploty se zdrojovou tabulkou, která bude obsahovat časy příjezdů do jednotlivých zastávek dle linek a směru. Výsledné grafy seřadit podle směru linky a zastávky pojmenovat pro každý směr zvlášť. Velkým přínosem je generovat hodnoty zpoždění pro jednotlivé dny a časy vybrané ve filtru. Výstup by měl sloužit k identifikování míst, kde by bylo vhodné upravit jízdní dobu. Projekt by bylo možné dále rozšířit o funkcionalitu, která by umožňovala detekovat typy dnů a časové období v nichž by měly být odlišné jízdní doby.
Analýza dat v SQLiteStudio
Základem bylo prvotní prozkoumaní poskytnutých dat o zpoždění autobusových a tramvajových linek městské a příměstské dopravy Prahy a dopravy ve Středních Čechách.
Dataset byl velice rozsáhlý, obsahoval celkem 90 csv souborů o zpoždění pro každý den za měsíce listopad, prosinec 2022 a leden 2023. Každý csv soubor obsahoval 3–3,5 mil řádků z GPS signálu. Dále jsme měly k dispozici txt soubory k 1.1. každého měsíce, ze kterých jsme použily stops a trips, které obsahovaly informace o názvech zastávek, jejich číselné označení, souřadnice atd. S dalšími txt soubory jsme se také seznámily, ale nakonec jsme je pro naší práci nepoužily.
Začaly jsme nejprve zkoumat jeden csv soubor tzn. jeden den zpoždění, jednu autobusovou linku, jednu zastávku a čas s oficiálním jízdním řádem na dpp.cz. Bylo jasné, že kapacita Microsoft office prohlížečů nebude dostatečná a taky při pokusu o otevření se spousta dat ztratila (naštěstí ne napořád). Pro prozkoumávání dat jsme použily program SQLiteStudio.
Výsledky zkoumání jsme zaznamenávaly do excelu, kde jsme popisovaly jednotlivé sloupce a zjišťovaly, co budeme dále potřebovat a co nikoliv. V této fázi projektu vyvstala myšlenka návrhu tří vlastních dopravních uzlů ve formě křižovatek, na kterých se střetává velké množství linek, identifikovat jejich zpoždění, závislost polohy tzn. centrum vs. okraj Prahy.
Architektura a návrh datového modelu
Pro implemetaci dat jsme si zvolily třívrstvou architekturu, která se skládá ze stage vrstvy L0, kde jsou uložena raw data v poměru 1:1. Další vrstva je integrační vrstva L1, která propojuje stage vrstvu s poslední reportovací vrstvou L2. Reportovací vrstva vytváří podklad pro analýzu a tvorbu reportů. Pro jednotlivé návrhy vytváříme datové modely v Luccidchartu.
Návrh struktury stage vrstvy L0
Při návrhu vrstvy L0 jsme začaly analýzou a následným popisem konkrétních atributů ve zdrojových datech.
Zdroje pro vrstvu L0 jsou nejen z datasetu, který máme k dispozici. Doplnily jsme zdroje o naše vlastní číselníky. Ve vrstvě L0 jsou tři hlavní tabulky zpoždění, za každý měsíc jedna v rozmezí listopad 2022 až ledna 2023. Do budoucna by mohla být v L0 pouze jedna tabulka pro všechny zpoždění. K těmto tabulkám jsou číselníky Stops a Trips opět za každý měsíc. I tady by se dalo v budoucnu uvažovat o jednom číselníku Stops a Trips. Bylo nutné manuálně dopracovat číselníky ve formátu csv pro Node (uzle) , Route Type ( typ dopravy), State position (příjezd, výjezd ze zastávky), Period (období), Day of the week (dny v týdnu).
Návrh struktury integrační vrstvy L1
Pro tabulky v integrační vrstvě L1 jsme navrhly primární a cizí klíče a definovali pravidla pro data ve formě constrains. Stanovily jsme si, že všechny názvy tabulek a atributů budou v anglickém jazyce. Podle jednotlivých hodnoch a jejich významu jsme definovali datové typy všech tabulek.
Návrh struktury reportovací vrstvy L2
V L2 jsme navrhly tabulky, které jsou podkladem pro vytváření reportů. Návrh odpovídá formě star schéma. Vytvořily jsem pomocnou tabulku a z ní pak finální faktovou tabulku (resp. tabulku faktů), ve které jsou pouze potřebné sloupce pro report.
Transformace a zpracovaní dat mezi jednotlivými vrstvami
V dalším zpracování dat jsme použili transformace pro čištění, manipulaci dat, výběr pouze požadovaných sloupců, výpočet a agregace, spojování dat, ošetření duplicit. Transformace jsou klíčová pro správné fungování a integraci dat z původní formy do formy očekávané. Transformace jsme použili při konverzi dat do intergační i do reportovací vrstvy.
Zpracovaní a nahrání dat ze zdrojových souborů do stage vrstvy L0
Technicky a časově nejnáročnější byla automatizace v Python pro nahrávání zdrojových dat ve formátu csv pro konkrétní dni. Data, která jsme měly k dispozici byla velmi obsáhlá a bylo jasné, že standartní nahrání pomocí Google Drive pro naše účely nepřipadá v úvahu. K dispozici tedy zůstaly tři varianty — FTP server, nahrání dat pomoci python scriptu s využitím API nebo manuálně. Naše rozhodnutí bylo jasné. V rámci digitální akademie se učíme Python, tak bude logické ho použít i do zpracování projektu. S napsáním Python scriptu jsme poprosili o pomoc našeho lektora Jirku a po několika pokusech jsme úspěšně data nahrály.
Bylo nutné si pořádně promyslet, co všechno python script má obsahovat, protože jedno jeho zpracování trvalo i více jak 2 hodiny. V rámci python scriptu bylo nutné do csv souboru přidat nový sloupec s datumem, tak aby bylo jasně identifikovatelné, z jakého csv byla data o zpoždění nahrána. Součástí scriptu je i příkaz pro vytvoření tabulek v L0 . Jednotlivé příkazy jsme vložily do cyklu, aby bylo možné opakované nahrávání příkazu. Fungování scriptu jsme si postupně ověřovaly printy v průběhu spouštění. Po nahrání prvního csv souboru za listopad jsme zjistily, že nemáme kontrolu nad tím, kolik řádků a jestli všechny řádky byly nahrány správně. Znamenalo to přidat ještě část pro napočítávání počtu řádků.
Transformace do vrstvy L1
V dalším zpracování dat jsme použili transformace pro čištění, manipulaci dat, výběr pouze požadovaných sloupců, výpočet a agregace, spojování dat, ošetření duplicit. Transformace jsou klíčová pro správné fungování a integraci dat z původní formy do formy očekávané. Transformace jsme použili při konverzi dat do intergační i do reportovací vrstvy.
Pro data ze zdrojových souborů ve formátu csv a txt jsme v stage vrstvě L0 pro každý měsíc navrhly separátní tabulku. V integrační vstve jsme tyto data spojili do jedné tabulky, reprezentující zpožděni. Tabulka L1_DELAY tak obsahuje 285 834 555 záznamů. Při tvorbě vrstvy L1 jsme začaly s čištěním dat.
Validace dat
Při nahrávání dat do integrační vrstvy jsme současně data validovali. Kontrolovali jsme duplicity, navržené datové typy, integritu referenčních klíčů.
Konkrétní problémy a jejich řešení při budování vrstvy L1:
Při validaci dat ve vrstvě L1 jsme zjistili, že se počet záznamů ztrojnásobil v rámci tabulek L1_STOP a L1_TRIP. Řešením bylo znovu definovat primární klíče jako složené klíče v obou tabulkách. Pro přepočtu dat s nově navrženými klíči byl již výsledek v pořádku.
Při definování datových typů bylo potřebné ověřit správnou délku jednotlivých hodnot v příslušných sloupcích.
Ve sloupci Delay, který byl pro projekt stěžejní jsme zjistily markantní počet prázdných hodnot, které byly zřejmě způsobeny špatným zpracováním systémového zapisování GPS signálu. Tento problém jsme vyřešili dopočítáním hodnot zpoždění ze sloupců Current departure a Timestamp.
K řešení problémů jsme hodně využili Snowflake dokumentaci, dále pak vyhledávání v Googlu a ChatGPT.
Transformace do vrstvy L2
Při návrhu reportovací vrstvy jsme navrhly pomocnou tabulku, která obsahovala všechny záznamy tabulky Delay z vrstvy L1, co představuje 285 834 555 řádků.
V transformaci do finální faktové tabulky jsme vyseparovali jenom záznamy splňující podmínky pro STATE_POSITION a TIMESTAMP. Ke každému TIMESTAMP existovalo více záznamů, ale pro naše účely byl stěžejní jeden řádek z jednoho bloku záznamů. Pro výběr konkrétních záznamů byla důležitá informace v atribtu State Position .V případě autobusů jsme State Position volily jako výjezd ze zastávky a v případě tramvají jako příjezd do zastávky. Pro tento cíl nám posloužila window funkce s podmínkou where, kde jsme náš výběr omezily ještě na autobusy a tramvaje v Praze. Tyto vybrané bloky záznamů jsme znovu přes union funkci spojili do finální tabulky.
Pro jednotlivé dimenze jsme vygenerovaly umělé klíče pomocí autoincrementu.
Původní plán byl vytvořit agregace v reportovací vrstvě L2, ale pro zachování vysokého detailu dat jsme se rozhodly tento krok udělat až v rámci vizualizací v Tableau.
Tableau
Jedním z cílů v této fázi projektu bylo rozčlenit metriku DELAY_SECOND do několika agregací pomocí kalkulovaných polí.
Další kroky v Tableau nás vedly k rozdělení analýz pro cestující a pro analytiky z řad ROPIDu.
Vytvořily jsme interaktivní mapu o zpoždění pro uživatele z řad cestujících.
Interaktivní mapa umožňuje uživatelům snadno filtrovat a vyhledávat data a informace podle různých kritérií. Po najetí myší na mapu s vybranou tečkou se v Tooltipu zobrazí název zastávky, průměrné zpoždění a procento zpoždění. Mapa je užitečná pro cestující, kteří chtějí lépe plánovat své cesty veřejnou dopravou a minimalizovat zpoždění.
Zpoždění na určitých zastávkách MHD v Praze je výraznější a častější než u ostatních zastávek?
Tímto grafem jsme potvrdily hypotézu, že zpoždění na určitých zastávkách je výraznější než na ostatních, jedná se zejména o zastávku Seifertova, která je prozatím nestandartní zastávkou v rámci jízdního řádu, ale do budoucnosti bude součástí optimalizace umístění zastávek v ulici Seifertova. Graf poskytuje informace o tom, jaké zastávky mají největší problém s obslužností a zpožděním, lze tak identifikovat klíčové oblasti, kde je třeba zlepšit provoz a snížit zpoždění.
Je zpoždění linek tramvaje výraznější než u linek autobusů.
Tato hypotéza se nepotvrdila, v top 10 zpoždění jsou pouze trojmístná čísla začínající dvojkou, což je označení autobusových linek v rámci Prahy.
Zpoždění veřejné dopravy v Praze se v průběhu týdne mění a existuje rozdíl mezi jednotlivými dny. Autobusy a tramvaje mají menší zpoždění ve všední den než o víkendu.
Potvrzení hypotézy, že v průběhu týdne jsou zpoždění rozdílná. V obou případech je největší zpoždění ve středu a poté klesá. O víkendu jsou zpoždění nejnižší. Vůbec nejnižší hodnoty jsou v neděli. O něco menší jsou zpoždění tramvají než autobusů.
Je obslužnost MHD v Praze dostatečná?
Tato hypotéza se potvrdila. Vyznačením bodů zastávek v rámci Prahy je infastruktura velice hustá. Linky MHD vedou celou Prahou, není tak problém jet z jednoho konce na druhý. Dále z grafů výše nízké hodnoty průměrného zpoždění u jednotlivých zastávek, linek a v jednotlivých dnech v týdnu znamenají, že obslužnost je dobrá.
Analýza zpoždění pro zaměstnance ROPIDu
Vizualizace zahrnuje interaktivní mapu zpoždění, v které je možno si zvolit zastávku, linku, den v týdnu a čas odjezdu. Mapa zobrazí u daného výběru průměrné zpoždění, minimální zpoždění, maximální zpoždění a procento zpoždění.
Dle zadání od klienta jsme vytvořily i boxplot s možností volby zastávky se zobrazením kódu zastávky. Ve vybraném filtru lze zadat dny v týdnu. Pro boxplot jsme využily agregované hodnoty (průměr).
Analýzu jsme doplnily grafem, který zobrazuje počet zpoždění podle skupin na vybrané zastávce.
V případě volby zastávky Albertov boxplot zobrazuje rozptyl průměru zpoždění, tři zobrazení v grafu odpovídají třem zastávkám Albertov, jejichž kódy jsou rozlišeny podle směru kde se nacházejí. Největší rozptyl hodnot má zastávka U876Z2P, u této zastávky lze najít odlehlé hodnoty, ovšem tyto hodnoty nejsou výrazně odlehlé. Umístění mediánu naznačuje, že u dvou ze tří zastávek jsou delší průměrné zpoždění. Rozpětí mezi kvartily udává rozsah hodnot na dané zastávce, nevětší variabilita hodnot průměrného zpoždění zde vychází opět na zastávce U876Z2P. Při zobrazení tooltipu na mapě lze zjistit o jakou zastávku. Zde lze uvažovat o úpravě jízdní doby.
Závěr
Pokud se chcete někam vydat pomocí veřejné dopravy, neděle je pro to nejlepší den, jeďte tramvají a pokuste se vyhnout zastávce Seifertova a autobusové lince 269!
Vzhledem k nejvyšším průměrným zpožděním tramvají i autobusů ve středu, je lepší zůstat doma na home office.
Překvapením byl maly rozptyl průměrných zpoždění, jak na linkách autobusů, tak na linkách tramvají.
Přínos projektu
Jedním z hlavních přínosů projektu je informace pro cestujícího s jakým průměrným zpožděním může na své zastávce a lince v daný čas počítat.
Pro analytika je přínos projektu identifikace slabých míst v dopravě. Analýza zpoždění MHD umožňuje identifikovat konkrétní zastávky, linky nebo časová období, ve kterých dochází k významným zpožděním. Lze získat informace pro plánování a řízení dopravy např. upravit trasování linek, změnit intervaly mezi spoji nebo optimalizovat přestupní body pro minimalizaci zpoždění a zvýšení spolehlivosti MHD.
Přínosem pro nás obě bylo získání dovedností, zkušeností a znalostí v oblasti analýzy dat, vyhodnocování a jejich interpretace. Dále pak příjemná, ale intenzivní spolupráce na projektu od A do Z.
Možnosti rozšíření projektu
Ke konci projektu jsme přicházely na možná rozšíření projektu, ale vzhledem k náročnosti celého procesu jsme se rozhodly tato rozšíření nezapracovat do našich analýz. Lze připojit další číselníky označující směr jízdy dopravního prostředku a číselné označení zastávek pro určení posloupnosti zastávek. Lze rozšířit číselník NODE, který nyní čítá 3 uzle, o další číselníky, které by sám zpracovatel navrhl. Lze identifikovat v datasetu nestandartní vzory linek a poté očistit data o tyto extrémní hodnoty. Vzhledem k automatizaci nahrávání dat prostřednictvím Python scriptu lze přidávat další data.
Poděkování
Děkujeme našem mentorkám za vedení našeho projektu, za čas, který nám věnovaly, za nápady, rady a připomínky, bez kterých by tento projekt nemohl vzniknout. Děkujeme týmu Czechitas za skvělou příležitost, kterou jsme dostaly, za výbornou atmosféru při výuce a za to, co jsme se během relativně krátké doby mohly naučit. V neposlední řadě patří naše poděkování našim blízkým za jejich podporu během trvání celé akademie.