Jak się dowiedzieć, ile pieniędzy samorządy wydają na transport publiczny?

Czasem próba zmierzenia się z (na pozór) niewinnym pytaniem, prowadzi do bardzo interesujących rozwiązań. Myślę, że dzisiejszy gościnny wpis Michała Kurtysa, będzie tego świetnym przykładem. Aby dowiedzieć się ile samorządy wydają na transport publiczny będziemy musieli zmierzyć się z narzędziami do digitalizacji pdf’ów, rozpoznawania tekstu oraz parsowania tabel. Będzie technicznie, będzie ciekawie.

Ile pieniędzy samorządy wydają na transport publiczny?
Jak wyekstraktować informacje z plików PDF, które zawierają dane w postaci obrazków?
Budżety samorządów.

Michał Kurtys

Ile pieniędzy wydaje moje miasto na transport publiczny? Czy to dużo?
Kiedyś zasłyszałem, że istotnie niemało. Zajrzałem zatem do budżetu miasta na rok 2013.
Wartość bezwzględna tej liczby wydała się faktycznie odpowiadać tej opinii.
Nie znam się jednak na transporcie. Ta kwota, choć spora, mogła być niezbędna. Postanowiłem zobaczyć więc, jak to wygląda na tle innych miejscowości.
Oczywiście odnosząc się do innych miejscowości w dalszym ciągu nie będzie to dokładne porównanie, chociażby z uwagi na różny stan taboru czy cenę biletów, ale zawsze jest to jakiś punkt odniesienia.

W dalszym ciągu zagadnienie transportu publicznego będzie służyło jako przykład.
Doszliśmy do momentu w którym interesować nas będzie ogólny problem zgromadzenia danych z budżetów samorządów.

Budżety zazwyczaj znajdziemy w biuletynie informacji publicznej danego samorządu, samorząd musi takie informacje publikować. Znalazłem stronę na której są odnośniki do BIP-ów poszczególnych jednostek samorządowych (http://www.bip.gov.pl/subjects/index/4600).

Teoretycznie wystarczyłoby wybrać interesujące nas podmioty (np. rady miast w województwie dolnośląskim).
Następnie dla każdego BIP-a odpalić crawler, który znalazłby dla nas ustawę budżetową.
Sam pisałem taki program w pythonie korzystając z biblioteki Scrapy.
Niestety bez rezultatów. Strony są bardzo zróżnicowane i trudno znaleźć odpowiednią regułę dla poszukiwań.

Sytuację trochę (za chwilę okaże się, że nie całkowicie) ratuje dziennik ustaw.
Dla woj. dolnośląskiego znajduje się pod adresem http://edzienniki.duw.pl/duw/ActByMonthYear.aspx#.

Znajdziemy tam ustawy uchwalone przez samorządy terytorialne, wśród nich oczywiście ustawy budżetowe.
Zacząłem od pobrania wszystkich ustaw z roku 2013. Tym razem nie użyłem biblioteki Scrapy.
Strona ładuje zawartość tabeli w sposób dynamiczny i znacznie wygodniej było skorzystać z biblioteki Selenium.
Skrypt do odczytywania danych z użyciem tej biblioteki można znaleźć tutaj. Nie jest napisany najpiękniej ale działa. [dzcrawler.py]

Następnie wybrałem te ustawy, które:

  • zostały uchwalone przez radę miejską lub radę miasta i gminy
  • dotyczyły uchwały budżetowej
  • nie dotyczyły zmian budżetu

Lista ustaw: [miasta2013.csv]

Interesujące mnie dokumenty pobrałem za pomocą prostego skryptu i programu wget. [downloader.py]

Przyglądając się jednak tym, co udało się pobrać, zostałem niemile zaskoczony niekompletnością listy budżetów.
Przykładowo w pobranych plikach brakuje budżetu dla miasta Lubin. W biuletynie informacji publicznej znajdziemy ją wśród uchwał z dnia 26 lutego 2013.
http://www.bip.um-lubin.dolnyslask.pl/dokument.php?iddok=2449&idmp=49&r=o

Dla tego dnia w dzienniku znajdziemy tylko jeden dokument:

Nie wiem dlaczego w dzienniku nie ma wszystkich uchwał.
Co gorsza nie są to pojedyncze przypadki.
W dalszej części będziemy po prostu pracować z mniejszym zbiorem.
Brakujące dokumenty można oczywiście znaleźć i pobrać ręcznie.
Mając w planach automatyzację analiz dla całego kraju, jestem trochę zawiedziony brakiem kompletności i automatyzowalności procesu pozyskiwania budżetów z BIP.

Przyjrzyjmy się ściągniętym danym. Wszystkie ustawy są zapisane w formacie PDF.
Większość dokumentów ma podobną strukturę. Zawierają przede wszystkim bardzo dużo tabel.
Bardzo cieszy fakt, że w prawie każdym dokumencie, znajdziemy tabele, które są niemal identyczne. [patrz rys. 1 ]. Jeszcze bardziej, że jest to pewne podsumowanie. Jeżeli chodzi o problem transportu, to jest w nich wszystko czego potrzebujemy i wszystkie inne moglibyśmy zignorować.

I tu znajdują się wyjątki od reguły. Część dokumentów ma zupełnie oryginalną strukturę np. Wrocław.
Dla każdego takiego przypadku należałoby implementować zmodyfikowany algorytm.
Zajmiemy się zatem tylko większością.

Jak wspomniałem interesujące nas dane są zapisane w tabelach.
Te zaś w praktycznie każdym przypadku są obrazami umieszczonymi w dokumencie, co oznacza, że aby wyciągnąć z dokumentu liczby należy użyć jakiejś biblioteki do rozpoznawania tekstu.
W tym projekcie użyłem silnika Tesseract. Program ten został stworzony przez firmę HP, w 2005 roku jego kod został uwolniony, a teraz jego rozwój sponsoruje Google.
W tej chwili prawdopodobnie jest najlepszym opensourcowym silnikiem OCR. Co ważne w aktualnej wersji potrafi również rozpoznawać język polski bez dodatkowego treningu.
Tesseract udostępnia interfejs dostępny z poziomu linii komend oraz API programisty.
Istnieją również graficzne frontendy zarówno dla systemów Windows jak i Linux.

Zanim jednak odpalimy tesseract potrzebujemy przekonwertować dokument do postaci pliku graficznego takiego jak .png czy .jpg.
Można do tego użyć programu convert z pakietu ImageMagick (wymaga zainstalowanego GhostScripta).
Renderuje on każdą stronę dokumentu z określoną przez użytkownika rozdzielczością (w dpi).

Alternatywą jest użycie pdfimages (poppler-utils), który po prostu wyciąga wszystkie obrazy z pliku PDF.
W tym przypadku jest to lepsza droga – otrzymujemy faktycznie to czego potrzeba, a i obrazy na pewno będą mieć oryginalną rozdzielczość.

Z nieznanych mi przyczyn odczytując budżet Lwówka Śląskiego zarówno convert jak i pdfimages wpadały w nieskończoną pętlę. Sytuacja na szczęście nie powtórzyła się dla innych miast. Po odczytaniu każdego dokumentu otrzymujemy katalogi z plikami graficznymi.
Oczywiście nie wszystkie z nich przedstawiają tabelę. Zwykle jednak nie-tabele to pojedyncze obrazki. Nawet nie usuwałem ich, po prostu po skanowaniu dadzą pusty wynik.

W obecnej wersji tesseract nie radzi sobie ze złożonym układem graficznym strony z tekstem.
Nie możemy uruchomić tesseracta bezpośrednio na pliku graficznym z tabelą. W wyniku dostalibyśmy po prostu mnóstwo śmieci.

Aby temu zaradzić wytniemy każde pole tabeli i przekażemy je do tesseracta.
Zanim pokażę jak podzielić tabelę, chciałbym poruszyć jeszcze jedną kwestię.

Gdy sam wdrożyłem powyższy pomysł w życie, spotkała mnie niemiła niespodzianka. Rezultat był raczej średnio zadowalający – bardzo często występowały “literówki”.
Kiedy spojrzymy w powiększeniu na tekst zobaczymy, że jest lekko rozmyty. Próbowałem różnych filtrów by poprawić jakość, ale bez większego skutku.
Tesseract pracuje wyłącznie na binarnych obrazach (jeżeli dostaje obraz nie-binarny przeprowadza binaryzację). Pomyślałem więc, że może dla antyaliasowanego tekstu warto byłoby napisać własny silnik?
Poruszyłem ten temat na stackoverflow: http://stackoverflow.com/questions/21827854/ocr-on-antialiased-text
Rozwiązanie było trywialne. Tekst był renderowany z użyciem technologii ClearType, dzięki której tekst wygląda lepiej na ekranach LCD.
Działanie polega na sprytnym “zwiększeniu rozdzielczości” wyświetlacza poprzez wykorzystanie horyzontalnego ustawienia subpixeli.
Wiedząc o tym rozbijamy każdy piksel na 3 subpiksele i zapisujemy ich wartość w odcieniach szarości.
Otrzymamy więc obraz o 3x większej poziomej rozdzielczości niż wejściowy. A to wystarczy, żeby OCR działał jak trzeba.
Jeżeli Czytelnik zainteresowany jest szczegółami to polecam wątek w stackoverflow, czy chociażby artykuł w wikipedii.
Przykładowy kod w pythonie i opencv dokonujący tą operację: [unsubpixel.py]

Teraz możemy wrócić do problemu formatu tabeli. Tabele jak zauważyliśmy nie są skanowane, a renderowane w jakimś programie. Oznacza to, że mamy ułatwione zadanie, ponieważ wszystkie linie są idealnie pionowe lub poziome
Pierwszym krokiem jest zatem znalezienie wszystkich odpowiednio długich linii pionowych i poziomych.
Tak znalezione linie nanosimy na czysty obraz, dostając “szkielet” tabeli (+ w rzadkich przypadkach drobne śmieci).

Teraz wystarczy znaleźć punkty przecięcia linii i wyznaczyć odpowiednie pola.
W bibliotece opencv możemy jednak ułatwić sobie zadanie wykorzystując funkcję findContours.
http://docs.opencv.org/modules/imgproc/doc/structural_analysis_and_shape_descriptors.html?highlight=findcontours#cv.FindContours
Mając dany obraz binarny funkcja ta znajduje kontury obiektów.
Ponadto zwraca również hierarchiczną strukturę kształtów w obrazie. Najlepiej zobaczymy to tutaj:

Obraz pochodzi z dokumentacji opencv:
http://docs.opencv.org/trunk/doc/py_tutorials/py_imgproc/py_contours/py_contours_hierarchy/py_contours_hierarchy.html#contours-hierarchy

Biorąc obiekty na samym dnie hierarchii otrzymamy najbardziej wewnętrzne kształty, czyli pola tablicy.

Ostatnią rzeczą, którą należy zrobić to uporządkowanie wyników, które dostaliśmy.
Tutaj “idealność” renderowanej tabeli znowu upraszcza sprawę.
Kontury o tej samej współrzędnej y będą tworzyły jeden wiersz.
Trochę inaczej jest w nagłówkach, ponieważ mają one wielopoziomową strukturę.
Gdy jeszcze raz spojrzymy na przykładową tabelę, możemy zauważyć, że w ostatnim wierszu nagłówków szerokości pól są identyczne jak w pierwszym wierszu danych.
To całkiem dobra heurystyka do oddzielenia danych od nagłówków.

Warto wiedzieć które kolumny to przychody, a które koszty.
Aby nie stracić informacji. Przejdziemy po nagłówkach w dół jak po drzewie.
Kolejne kolumny nazwiemy więc:

i tak dalej.
Dla każdej tablicy tworzymy dwa pliki wyjściowe:
Jeden zawierający współrzędne pola i jego zawartość.
W drugim wyniki zawierające zapisane wierszami.

Wyniki:
Z powodu wszystkich trudności, które wymieniłem w artykule, ostateczna baza miast jest bardzo skromna.
Oczywiście, część z problemów jest do przeskoczenia. Na pewno przetworzenie plików o “specjalnej” strukturze

Transport:
Skorzystamy z tego, że w pliku zawsze istnieje podsumowująca tabela o w miarę niezmiennym kształcie.
W skrypcie przeszukujemy wyniki skanowania tabel o ilości kolumn równej 16,17 lub 18.
Jeżeli znajdziemy identyfikator Transportu Lokalnego równy “60004”, to wypisujemy wartość wydatków na ten cel.

Koniec końców udało się wyciągnąć wielkość wydatków na transport lokalny w różnych miastach województwa dolnośląskiego.

24 myśli na temat “Jak się dowiedzieć, ile pieniędzy samorządy wydają na transport publiczny?”

  1. A właściwie takie pytanie:
    – skąd gwarancja, że oprogramowanie OCR prawidłowo wyciągnęło dane liczbowe?

    Czy to ważne? Owszem. Nawet bardzo ważne. Jeżeli powstanie jakaś analiza na bazie OCR, to może dojść do zmian w finansach gmin/miast na jej podstawie. W razie problemów będą same problemy kto powinien za wadliwe dane wziąć odpowiedzialność (a strzelam w ciemno, że EULA dla OCR stwierdza, że dostawca programu nic nie gwarantuje, a w szczególności nie gwarantuje poprawności danych).

    1. Gwarancji raczej nie ma, choć ciekaw jestem jaka jest skuteczność takiego OCR na cyfrach.
      Najlepiej by oczywiście było, by te dane znajdowały się w bazie danych, a nie trzeba je było wyszarpywać z rastrowych obrazków w pdfach.

  2. Bardzo ciekawy artykuł jeśli chodzi o stronę narzędziową. Ale dane dotyczące Działu 600 i poszczególnych podrozdziałów dla poszczególnych gmin, czy powiatów są dostępne na stronach GUSu w Bazie Danych Lokalnych.

  3. @mbodziak,
    byłoby super odczytać dane z tej bazy, ale jak tam znaleźć odpowiednie dane?

    Gdy na stronie http://stat.gov.pl/bdl/app/strona.html?p_name=indeks wpisuję w ‘szukaj’
    ‘transport’ lub ‘transport lokalny’ nic się nie znajduje.

    Próbując coś wyklikać dostaję sumę wydatków na transport w jakimś regionie, ale nie wiem jak dostać te dane dla każdej gminy osobno.

  4. Można np. tak:

    Baza Danych Lokalnych -> DOCHODY I WYDATKI BUDŻETÓW JEDNOSTEK SAMORZĄDU TERYTORIALNEGO -> WYDATKI BUDŻETÓW GMIN I MIAST NA PRAWACH POWIATU -> Wydatki w Dziale 600 – Transport i łączność

    i dalej w zależności na jakim poziomie szczegółowości potrzebujemy danych -> Wybór jednostek terytorialnych wg poziomu NTS -> np. Powiaty -> wybierając odpowiednie parametry innych wymiarów (rok, rodzaj wydatków, itp.) dostajemy się do tabeli, którą potem można wyeksportować do pliku.

  5. Dziękuję,
    to już znacznie bliżej celu,
    A czy można jakość obejść ograniczenie na 10 000 wierszy?
    obecnie nie mogę pobrać danych dla wszystkich gmin gdy mam zaznaczonych kilka lat i kilka zmiennych.

  6. O dziwne. GUS musiał wprowadzić to ograniczenie w ostatnim roku. Rok temu nie było tego ograniczenia.

    Alternatywą może być wtedy zwrócenie się bezpośrednio do GUSu z zamówieniem danych. GUS przewiduje taką formę.

  7. Najprościej sięgnąć do sprawozdań z wykonania budżetów jst dostępnych na stronie Ministerstwa Finansów. Ale tekst ciekawy, przydatny w wielu innych zastosowaniach. Gratulacje

  8. @Piotr, dzięki wygląda ciekawie,
    ale czy to pracuje z linii poleceń (z opisu wygląda jak coś klikanego) i czy wyciąga dane z rastrowego obrazka?

  9. po pierwsze nie mam pewności, czy zbiorowy i publiczny to dokładnie ten sam wskaźnik. Mojapolis ma go zrobionego dwa razy jako wydatki jst i wydatki gmin.
    Ale mechanizm jest taki, że po wyborze odpowiedniego wskaźnika (wyszukiwarka działa całkiem oki)- dajemy obszar – na szczeblu – gmin i można już pobierać xlsa i csv.

  10. @Mariusz,
    faktycznie interface jest przyjazny i nawet w xls są kody teryt, co bardzo ułatwia mapowanie gmin,
    ale zarówno BDL jak i Mojapolis dużo by zyskały, gdyby wstawiały jakieś łatwe w użyciu API.
    (a czy mojapolis ma jakis zwiazek a mojepanstwo?)

  11. Nie, to dwie różne fundacje/stowarzyszenia.
    Oczywiście zgadzam się, że jakieś proste api bardzo ułatwiałoby pracę na danych. Ale to raczej szerszy problem z danymi publicznymi.

  12. Odbiegając trochę od tematu, @Michal Sitek ma rację. Wszystkie dane o dochodach i wydatkach Jednostek Samorządu Terytorialnego (JTS) są dostępne na stronach Ministerstwa Finansów:

    http://www.mf.gov.pl/ministerstwo-finansow/dzialalnosc/finanse-publiczne/budzety-jednostek-samorzadu-terytorialnego/sprawozdania-budzetowe

    Pewnie można na tej podstawie zrobić jakąś ciekawą analizę przestrzenną w R.

    Tak czy inaczej, artykuł Michała Kurtysa jest bardzo ciekawy.

    1. Dziękuję za link,
      miło ze strony ministerstwa, że udostępnia bazy danych do pobrania jako pliki.
      Szkoda, że słowniki do tych baz danych (struktura sprawozdań) są opisane przez tabele wklejone do worda.
      Ale źródeł z danymi budżetowymi jest coraz więcej, więc kiedyś (może niedługo?) pojawi się też takie źródło, które ma łatwe w użyciu z API.

  13. Dziękuję za wszystkie komentarze.
    Zabawne, że okazało się, że dane można zdobyć bez żmudnego wyciągania z plików pdf.
    Zwłaszcza, że docelowo myślałem o mapie podobnej do tej, którą można zobaczyć na mojapolis.pl
    Mam nadzieję, że sam temat pracy z plikami pdf jest interesujący i może się przydać w innych projektach.

    1. @Paweł,
      świetne, warto by było obudować w R. Powinno być umiarkowanie proste z użyciem httr i RJSON.
      A może jest ktoś chętny by to zrobić?

      1. W sumie nieaktualne,
        zrobiłem w pakiecie SmarterPoland na githubie interface do API MojegoPanstwa

        getBDLtree(debug = 0, raw = FALSE)
        getBDLsearch(query = “”, debug = 0, raw = FALSE)
        getBDLseries(metric_id = “”, slice = NULL, time_range = NULL,
        wojewodztwo_id = NULL, powiat_id = NULL, gmina_id = NULL,
        meta = NULL, debug = 0, raw = FALSE)

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

Możesz użyć następujących tagów oraz atrybutów HTML-a: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">