Ztracen v Excelu aneb POMOC!!!

Po několika dnech marného snažení jsem se odhodlal k nejhoršímu: svěřit se světu s vlastní neschopností. Něco takového nikdo z nás nečiní rád, takže vám musí být jasné, že jsem opravdu zoufalý. Vlastně mi zbývá poslední naděje: že mi pomůže nekdo z vás.

Můj problém začal před několika měsíci, když mne napadlo, že bych v rámci svého vědeckého snažení mohl prozkoumat politickou aktivitu cizinců v České republice. Když to zjednoduším: od našehp vstupu do Evropské unie mohou podle zákona v komunálních volbách volit i občané jiných členských států, kteří u nás (v dané obci) mají trvalý pobyt. Nikdo ale neví, jestli to doopravdy dělají: potřebná data jsou totiž roztroušená na jednotlivých obecních úřadech, kterých je asi šest a půl tisíce. Pokud chce totiž cizinec volit, musí ještě před volbami zajít právě na obecní (městský) úřad a zapsat se do takzvaného dodatku k voličskému seznamu. Nikam dál se to ale nehlásí.

Nevadí, řekl jsem si, kdyby to bylo snadné, nebyla by to věda. Vybral jsem reprezentativní vzorek, rozeslal datovou schránkou asi 450 žádostí o informace, týdny urgoval mlčící úřady, došlá data zapisoval do tabulky, dohadoval se o úhradě, stěžoval si na nečinnost atd. S detaily vás nebudu unavovat, protože v tomhle problém není: bylo to pracné, ale je to za mnou.

Shromáždil jsem tedy hezká data o tom, kolik cizinců v oslovených obcích chodí volit, jenže nejsou mi k ničemu, když nevím, kolik cizinců tam volit nechodí. Jinými slovy, kolik jich tam žije celkem. Tohle kupodivu obce nevědí, takže mi to ani nemohly říci; evidenci cizinců totiž podle zákona vede cizinecká policie.

Zeptal jsem se tedy na policii a celkem rychle dostal požadovaná data. A tady začínají potíže: dostal jsem toho totiž mnohem (mnohem!) víc, než bych potřeboval, a v úplně jiné struktuře, než se kterou dokážu pracovat. Tabulka, kterou bych chtěl, vypadá zhruba takhle:

Ve skutečnosti však mám (po řadě úprav a čištění původního souboru):

Vtip je v tom, že celá tahle tabulka má asi 45 tisíc řádků, což poněkud komplikuje zpracování. Zkusil jsem z ní data vytáhnout prostřednictvím funkce DSUM, rozkopírované k názvu každé obce… což bylo to poslední, co jsem před dvěma dny na svém počítači udělal. Od té doby Excel počítá a počítá, 98 % procesoru zabírá, ale nikam se nedostal. Zatím ho stále nechávám běžet, ale všem nám je jasné, že tenhle běžec do cíle nedoběhne.

Mezitím jsem na jiném stroji zkusil různé triky, které by tu úlohu zjednodušily: například data setřídit a rozdělit na deset menších hromádek, takže by nebylo nutné při výpočtu neustále pracovat se všemi daty, ale prohledávat jen 10× menší množinu. Výsledek se dostavil: výpočet je sice stále neuvěřitelně pomalý, zato je ale mnohem pracnější na lidskou obsluhu. Učinil jsem totiž zajímavé pozorování: když ty vzorce kopíruju jednotlivě, jednu obec po druhé, trvá výpočet řekněme 2 sekundy na každou. Když to ale zkusím kopírovat v blocích, výrazně se zpomaluje: deset buněk netrvá 20 sekund, nýbrž minutu, dvacet buněk netrvá čtyřicet sekund, ba ani dvě minuty, nýbrž spíš tak čtyři. Kolik by trvalo těch 6 tisíc obcí ani nechci počítat.

Zkrátka: po třech dnech snahy jsem dospěl do bodu, kdy je mi jasné, že s tím už nehnu. Chyba je zjevně mezi židlí a klávesnicí, ale bohužel i za ní: Excel se s touhle úlohou nedovede poprat a já ho neumím líp zaúkolovat ani použít vhodnější nástroj.

A tak tedy: pomoc!!!!! Když mi někdo dokážete poradit, jak mám dosáhnout výsledku, královsky se vám odměním.:-)

---

HOTOVO!

Přátelé, děkuju moc. Zase jednou můžu říci, že Internet je báječná věc. Zhruba v půl druhé jsem dopsal tenhle spot a poslal o něm echo na Twitter a na Facebook. Hned poté jsem – trochu otráveně – vypnul počítač na fakultě a vyrazil domů, abych se najedl a přišel na jiné myšlenky. Než jsem sešel to jedno patro a vyšel před budovu, už jsem měl první odpověď. A za chvílí už se sypaly další a další, ze všech kanálů, do kterých jsem dal vědět. Než vám řeknu, jak jsem to nakonec provedl, zkusím ty nápady shrnout; třeba se to ještě někdy bude hodit:

1. Relační databáze – Tohle byla asi nejčastější a nejlogičtější rada: vyexportuj to z Excelu do CSV souboru, načti to do vcelku libovolné databáze, a pak už je to hračka. Přestože je to asi nejlepší řešení, nakonec jsem se k němu nepřiklonil, a to hlavně kvůli tomu, že nemám žádnou databázi snadno k dispozici (mám jen verzi Office bez Accessu) a musel bych ji kvůli tomu shánět, instalovat (a učit se).
2. Prožeň to skriptem v perlu – Tahle rada mi připomněla, jak jsem kdysi pravidelně v němém úžasu pozoroval, co dokáže s perlem jeden můj nejmenovaný kolega: v podstatě všechno. Pevně věřím, že by si poradil i s tímhle mým problémem, ale sám jsem se do toho pustit bál (i když se mi tu ještě někde válí učebnice perlu). @ptica byl sice tak hodný, že mi rovnou napsal skript a poslal odkaz, který by stačilo spustit, jenže můj problém řešil jen částečně. V textu jsem to nenapsal úplně jasně, ale nešlo mi jen o „sečtení“ čísel na různých řádcích, byla tam navíc podmínka, že daná země patří do EU. To bych sice mohl nějak vyřešit i na straně zdrojových dat, ale zdálo se mi to trochu složité.
3. Použij Gooddata.com – O tomhle řešení jsem trochu přemýšlel už předtím, než jsem napsal svůj zoufalý spot: donedávna tam totiž pracoval můj dobrý kamarád, od kterého zhruba tuším, že něco takového ta služba dělá. Ale až když jste mi to tu doporučili jsem se odhodlal se tam podívat a zkusit data naimportovat. Opravdu to bylo poměrně snadné a s trochu snahy bych tam problém záhy vyřešil, narazil jsem ale na potíž s kódováním češtiny: názvy obcí a okresů se rozsypaly. I tohle bych asi dokázal nějak vyřešit ve zdrojových datech, např. nahradit názvy číselným identifikátorem, prohnat Good Data, a pak to zase vrátit zpátky, nicméně pro daný okamžik mne to odradilo.
4. Použij Google Fusion Table nebo Google Refine – Tušil jsem, že někde v aplikacích Googlu bude něco, co by se pro můj problém dalo použít, ale z iracionálních důvodů k nim mám trochu ostych. Vtipné je, že mi to doporučil @codeas, který Google Apps školí; zrovna včera jsem přemýšlel o tom, že bych na to školení měl zajít, a teď se mi to potvrdilo.:-)
5. Pošli mi ten export a já Ti to zpracuju – Přátelé, během dvou hodin v sobotu odpoledne mi šest lidí, z nichž dobrou polovinu jsem nikdy neviděl, nabídlo, ať jim to prostě pošlu a že to udělají za mne. Dobří lidé nevymřeli; děkuju vám! Nakonec jsem ale žádné z těch nabídek nevyužil: sice jsem si vás schovával jako poslední možnost, to přiznávám, ale chtěl jsem se napřed pokusit sám. Protože kdyby to někdo udělal za mě, nic se nenaučím a příště budu zase v loji.
6. „Kontingenčka to nezvládne?“ – Abych byl upřímný, tahle rada se mi původně moc nosná nezdála. Jak jsem psal ve spotu, zkoušel jsem to vyřešit funkcí DSUM, což je v podstatě totéž, co dělá kontingenční tabulka. Ale když mi pak něco podobného na Facebooku napsal druhý člověk, řekl jsem si, že bych to asi zkusit měl – a hle, ono to funguje! Vyžadovalo to sice jisté úpravy dat (sloučit obec a okres do jednoho řetězce s oddělovačem uprostřed, prohnat kontingenční tabulkou a pak zase rozdělit zpátky), ale to jsou detaily. Podstatné je, že co při mém neumném pokusu Excelu trvalo hodiny (a dny :-), to dokázal v kontingenční tabulce spočítat za pár vteřin. Zřejmě to dělá jinak než přes DSUM, resp. to má nějak optimalizované. Takže: heuréka, data konečně vypadají tak, jak mají, a vlastně to ani moc nebolelo.

Teď mne ještě čeká spojit data z dvou tabulek do jedné, což nebude úplně jednoduché, protože mohu použít maximálně textovou shodu názvu obce a okresu, a ta není zcela spolehlivá. Jenže: teď už jde jen o nějakých 370 řádek, a s tím se člověk nakonec přinejhorším popere i ručně. 

A poučení? Přeci jen to byl nakonec problém mezi židlí a klávesnicí; jako obvykle.:-)