Konsolidace a čištění
Od T-Mobilu jsme dostaly následující strukturovaná data:
BTS_sig - časové záznamy pro srpen 2018 v podobě timestamps o všech sim_id na vybraných BTSkách, které pokrývají území Českého Krumlova. Je to tabulka o více jak 30 miliónech řádků. Záznam vznikne v okamžiku, kdy s vaším telefonem provedete nějakou aktivitu - zavoláte, pošlete SMS, připojíte se na internet, ale také jednou za půl hodiny v případě, kdy máte telefon aktivní a nepoužíváte jej.
SIM_CRM - tabulka s pro naši analýzu relevantními údaji o českých sim_id od T-Mobile - marketingový segment (prepaid, postpaid, business), kategorie hardwaru (vyloučíme všechna zařízení, co nejsou mobilní telefon), u paušálních zákazníků pak pohlaví a věk.
CELLS2 - tabulka, která nám u českých zákazníků, kteří byli v srpnu v Českém Krumlově, ukazuje pro dané sim_id domácí BTSku, BTSku pracovní a BTSku víkendovou - údaje dopočítává T-Mobile podle vlastní logiky. Nemáme však všechny údaje pro každé české sim_id.
CELLS_CONFIG_REGIO - tabulka se všemi BTSkami v republice a geografickými údaji o nich, ze které použijeme cell_id a názvy měst, okresů a krajů, ve kterých se jednotlivé BTSky vyskytují.
Dodatečně jsme si vyžádaly data z BTSek pokrývající kempy na Vltavě, data o BTSkách, na kterých byli přihlášeni naši turisté po opuštění Českého Krumlova
DA_kempy_sig - timestampy a sim_id z vybraných BTSek pokrývajících vodácké kempy a ID vysílačů.
turistikamjeli - tabulka se sim_id turistů a ID vysílačů, na kterých byli přihlášeni v den odjezdu z Krumlova večer a druhý den ráno
Máme údaje o českých sim_id, musíme ale také identifikovat ty zahraniční. První 3 čísla každého IMSI určují zemi, ze které SIM karta pochází. Informace o kódech zemí jsme získaly na této webové stránce: https://mcc-mnc.com/ . Použily jsme Python pro webscraping a získaly tak tabulku s kódy a zeměmi.
Nejprve jsme pomocí funkce get zavolaly stránku, ze které jsme potřebovaly vyscrapovat první 3 čísla sim_id.
Poté jsme si vytvořily proměnnou seznamHodnot a do ní jsme uložily data z webové stránky https://mcc-mnc.com/. Číslo MCC, MNC, ISO, Country, Country Code, Network... aneb celou tabulku, která je zde dostupná.
A takto to pak vypadalo.. dlouhý seznam elementů 'td'. My ho však potřebujeme rozdělený po šesti (jak již bylo popsáno výše - Číslo MCC, MNC, ISO, Country, Country Code, Network) ... takže JDEME NA TO!
Bingo.. elementy máme rozdělené po šesti !
Teď bychom ještě rády ony potřebné informace, a ne elementy 'td'!
JDEME NA TO!
Nyní už jen vytvoříme soubor csv.
Zde je ukázka dat z výsledného souboru:
i v souboru csv :) :
Obrázek výchozích dat před čištěním (magentová jsou data od T-Mobilu, zelená od nás, highlightované jsou údaje, se kterými budeme v naší analýze počítat) ... ještě doplním o další tabulky
Hurá, data máme, nahrajeme si je do společného projektu v Keboole a protože už už chceme něco v našich datech vidět, tak se cvičně podíváme se na to, odkud jsou všechna sim_id, která se v srpnu v Krumlově objevila. Ze sim_id z první tabulky odřízneme první tři čísla a přes MCC na ni napojíme sloupec se zeměmi z tabulky Countries a jednoduše vypočítáme počty sim_id pro jednotlivé země.
To, že v pořadí národů vládne Německo a Rakousko nás nepřekvapuje. Do obou zemí je to co bys kamenem dohodil. Asijské velmoci hned následují. Kdo v poslední době Krumlov navštívil, tak se ani nediví. Co úplně nesedí, je 1720 unikátních sim_id z Guamu. Guam je ostrov v Tichém oceánu někde na půl cestě mezi Japonskem a Papuou Novou Guineou, který obývá cca 160 000 lidí. Pravděpodobnost toho, že by se každý stý Guaman v srpnu objevil v Českém Krumlově je prakticky nulová. Díky tomu jsme zjistily, že několik MCC kódů z naší tabulky zemí není unikátních a je třeba zbavit ji duplicit. S minoritními zeměmi prostě v naší analýze nepočítáme. Guam je USA, Palestina Izrael, bývalé kolonie Holandska jsou Curacao, atd. Pokud je mezi čtenáři tohoto pojednání rodilý Guaman, který v srpnu navštívil Český Krumlov a my ho započetly jako občana USA, tak se mu omlouváme... Jasně, stačilo si tabulku na duplicity zkontrolovat hned, ale to bychom zas nezískaly skvělý tip, kam jet na dovolenou!
POUČENÍ: používat selský rozum a v průběhu práce si dílčí výsledky kontrolovat!
Čistit data o duplicity jsme musely i v tabulce SIM_CRM, protože se nám tam dublovaly sim_id v případě, že zrovna v tomto měsíci přecházely mezi zákaznickými segmenty. Ponechávaly jsme ty, které mají na sobě navázáno nejvíce údajů, tj. segment postpaid. Využily jsme první window funkce, juchů, a upravily kategorie hardwaru, abychom neměly údaje, co nám v podstatě říkají to samé, ve třech různých kategoriích.
Příklady selectů.
Tabulku CELLS_CONFIG_REGIO jsme dostaly ve formátu, který nešel nahrát do Kebooly, pomocí Pythonu jsme ji upravily tak, aby byl Keboolou schroustatelný. Potřebovaly jsme odstranit dělící znak '|' a nahradit ho za jiný, nejlépe čárku.. :
Takže JDEME NA TO!
Upravenou novou tabulku cells_upravene1 jsme spojily přes cell_id s tabulkou SIM_LOC, abychom získaly geografické informace k našim českým sim_id z Krumlova.
Teď už máme upravené všechny vstupy a můžeme začít s transformací.