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í.