Objavljeno:

Povezave med podjetji prenesenimi na DUTB (odprti podatki, 1. del)

S tokratnim besedilom začenjamo serijo prispevkov na temo odprtih podatkov – gre za podatke države oziroma javnega sektorja, ki so dostopni kot informacija javnega značaja.

V pričujoči seriji prispevkov o odprtih podatkih si bomo ogledali kje in kako dobiti odprte podatke ter kako jih obdelati in predstaviti s pomočjo odprtokodnih orodij.

Sicer je kar nekaj podatkov javnega sektorja že dostopnih na internetu, a najpomembnejše orodje za pridobivanje odprtih podatkov je Zakon o dostopu do informacij javnega značaja, ki ga vsekakor velja podrobno preučiti. V prvem prispevku si bomo ogledali dve podatkovni bazi – prva je seznam podjetij prenesenih na DUTB oz. tim. slabo banko, druga pa je PRS poslovni register, ki ga vzdržuje in upravlja Ajpes.

Kot rečeno bomo za analizo uporabili različna odprtokodna orodja. Glavno med njimi bo PostgreSQL baza podatkov, ki jo v operacijskem sistemu Ubuntu Linux namestimo takole:

sudo apt-get install postgresql

Nato za uporabnika matej ustvarimo novo bazo podatkov podjetja:

sudo su
su - postgres
psql
CREATE ROLE matej WITH LOGIN;
CREATE DATABASE podjetja WITH OWNER matej;

Druga možnost je, da v ukazni vrstici izvedemo naslednja dva ukaza:

createuser matej
createdb -E utf-8 --lc-collate=sl_SI.UTF-8 --lc-ctype=sl_SI.UTF-8 -O matej podjetja

Pozor, pomembno je, da ima baza vključeno UTF-8 podporo. Nato se odjavimo iz svojega računa in se ponovno povežemo vanj. Zdaj kot uporabnik matej lahko vstopimo v bazo:

psql podjetja

Krediti podjetij prenesenih na DUTB

DUTB je pred časom na svoji spletni strani objavila informacije javnega značaja, povezane s krediti podjetij, prenesenih na DUTB s strani NLB ter NKBM, kasneje pa še s strani Abanke Vipa. Za namen pričujočega prikaza smo uporabili seznam podjetij kreditiranih s strani NLB in NKBM.

Objavljen je seznam podjetij, ki vsebuje naziv podjetja, sedež ter bruto izpostavljenost po stanju na dan 30. 9. 2013. Kljub temu da so podatki objavljeni na vizualno pregleden način, pa za neposredne analize niso preveč uporabni, saj niso na voljo v strojno berljivi obliki, predvsem pa jim manjkajo ustrezni identifikatorji, ki bi omogočali povezovanje z drugimi bazami podatkov.

Da bi bili podatki zares uporabni, bi jih bilo smiselno shraniti v enotno bazo (za obe banki) ter jim dodati davčne in matične številke podjetij. Na srečo je podatke na tak način že obogatil Gašper Žejn in jih v obliki CSV datoteke objavil na svoji spletni strani, kako to naredimo sami, pa si bomo pogledali v kakšnem drugem prispevku. Naj dodamo, da so podatki prečiščeni, torej med temi podatki ni podjetij, ki so bile na seznam uvrščene zaradi administrativnih napak.

Podatke torej prenesemo na svoj računalnik ter uvozimo v PostgreSQL bazo podatkov. Kot uporabnik matej vstopimo v bazo:

psql podjetja

Vnesemo ukaza:

create table dutb_podjetja (naziv text, naslov text, znesek_text text, znesek decimal(16,2), banka text, naziv_clean text, posta text, postna_stevilka text, maticna text, davcna text);
\COPY dutb_podjetja from 'dutb.csv' with csv header delimiter ',' quote '"';

S tem so podatki uvoženi v bazo in lahko jih pregledamo:

select count(*) from dutb_podjetja;
select * from dutb_podjetja;

Ajpes PRS register

PRS – poslovni register podjetij je zbirka podatkov o vseh poslovnih subjektih v Republiki Sloveniji. Zbirko vodi in upravlja Ajpes, ki je sicer tudi zavezanec za Zakon o dostopu do informacij javnega značaja. Ajpes podatke iz PRS registra sicer komercialno prodaja, vendar ji je po ZDIJZ za neprofitni namen dolžan posredovati prosilcem brezplačno.

Tako sem 19. avgusta 2014 na Ajpes naslovil zahtevo za dostop do informacij javnega značaja, kjer sem jih prosil za elektronsko kopijo izvoza celotne baze Poslovnega registra Slovenije, in sicer širši nabor ter vključno z zgodovinskimi podatki, v MS Access (MDB) obliki ali v drugem znanem formatu. V zahtevi sem navedel, da podatke želim pridobiti za ponovno uporabo v nepridobitne namene, zlasti za namene informiranja.

Kot kaže Ajpes ni ravno navdušen nad brezplačnim posredovanjem podatkov, ki jih sicer komercialno trži. Zato sem 11. septembra 2014 od Ajpesa po elektronski pošti prejel poziv za dopolnitev svoje zahteve. Ajpes me je pozval, da natančneje opredelim navedbo “ukvarjanje z analizami podatkov” ter predvideni način ponovne uporabe ter opišem pogoje posredovanja informacij javnosti.

Po mojem mnenju Ajpes (niti katerikoli drug organ) nima zakonske podlage, s katero bi prosilce lahko pozival na dopolnitev zahteve na takšen način. Vprašanje ponovne uporabe informacij javnega značaja (pridobitni oziroma nepridobitni namen) je relevantno izključno zaradi morebitnega zaračunavanja podatkov za ponovno uporabo. Organ pa je v postopku odločanja o zahtevi za ponovno uporabo vezan na vsebino zahteve, kot jo navede prosilec. Zato po mojem mnenju noben organ nima pravne podlage, da sam “prekvalificira” zahtevo prosilca in jo obravnava, kot da gre za pridobitni namen. Zakon namreč ne določa, da organ o tem, ali gre za pridobitni ali nepridobitni namen odloči glede na vsebino zahteve, pač pa predvideva, da prosilec sam navede predviden način ponovne uporabe (pridobitni ali nepridobitni). V primeru, da bi prosilec podatke pridobil za nepridobitni namen, pa bi jih potem uporabil za pridobitni namen, pa so v zakonu predvideni ustrezni prekrškovni postopki. Glede na navedeno je bila po mojem mnenju zahteva, naj natančneje navedem predvideni način ponovne uporabe informacij za nepridobitne namene, nezakonita.

Ker sem poleg tega ocenil, da gre v tem primeru morda celo za namerno zavlačevanje postopka in ne za napačno uporabo predpisov s strani Ajpesa, sem se pritožil Informacijskemu pooblaščencu. Ajpesu je bila namreč dne 27. 1. 2014 s strani Informacijskega pooblaščenca izdana odločba št. 090-263/2013/5, kjer je Informacijski pooblaščenec v podobnem primeru ugotovil napačno ravnanje in Ajpesu izdal ureditveno odločbo, v kateri mu je naložil, da prosilcu, ki je na podoben način zahteval PRS register, le-tega tudi posreduje.

Kasneje sem sicer izvedel, da je omenjena odločba trenutno še predmet spora na Upravnem sodišču, a očitno so moja pojasnila zalegla in CD s podatki sem na svoj naslov dobil 25. septembra 2014, torej dober mesec po posredovani zahtevi. Ajpes sicer moji zahtevi ni ugodil v celoti, saj ni posredoval zgodovinskih podatkov v širšem naboru. To je trenutno še predmet pritožbe, ki jo bo reševal Informacijski pooblaščenec.

Posredovani podatki so bili v MDB (MS Access) formatu. V nadaljevanju si bomo pogledali kako jih uvoziti v PostgreSQL.

Uvoz PRS registra v MS Access obliki v PostgreSQL

Za uvoz potrebujemo orodje mdb2csv, ki teče v operacijskem sistemu Windows. Orodje je napisal Gašper Žejn in je izdano pod odprtokodno licenco. V operacijskem sistemu Windows z nameščenim MS Office torej najprej namestimo Python 2.7 (v našem primeru python-2.7.4.msi) in pypyodbc (v našem primeru pypyodbc-1.3.3.zip). Nato si na računalnik prenesemo MDB datoteko (v našem primeru Prs.mdb) ter ustvarimo podimenik prs2014 ter v Windows ukazni vrstici poženemo naslednji ukaz:

python mdb2csv.py Prs.mdb prs2014

Orodje za vsako tabelo ustvari SQL ter CSV datoteko. SQL datoteke vsebujejo opis strukture posamezne tabele, CSV datoteke pa podatke iz tabele. Ker je bilo SQL datoteke potrebno ročno nekoliko popraviti (zaradi slabe kvalitete podatkov je večina podatkovnih tipov nastavljena na text), objavljamo popravljene SQL datoteke na voljo za prost prenos. CSV datoteke vsebujejo podatke, ki jih v naslednjem koraku lahko uvozimo v PostgreSQL bazo.

Najprej torej s pomočjo SQL datotek ustvarimo ustrezne tabele. Iz Linux ukazne vrstice poženemo:

cat *.sql | psql podjetja

Nato vstopimo v bazo…

psql podjetja

…in vnesemo naslednje ukaze s katerimi podatke iz CSV datotek uvozimo v bazo:

\COPY prs_delez_subj from 'prs_delez_subj.csv' with csv header delimiter ',';
\COPY prs_delez_ustanovitelja from 'prs_delez_ustanovitelja.csv' with csv header delimiter ',';
\COPY prs_druge_dejavnosti from 'prs_druge_dejavnosti.csv' with csv header delimiter ',';
\COPY prsexportlog from 'prsexportlog.csv' with csv header delimiter ',';
\COPY prs_enota_rs from 'prs_enota_rs.csv' with csv header delimiter ',';
\COPY prs_nadzor_svet from 'prs_nadzor_svet.csv' with csv header delimiter ',';
\COPY prs_poreklo_kapitala_drzava from 'prs_poreklo_kapitala_drzava.csv' with csv header delimiter ',';
\COPY prs_razni_sklepi from 'prs_razni_sklepi.csv' with csv header delimiter ',';
\COPY prs_subjekt_ime_rs from 'prs_subjekt_ime_rs.csv' with csv header delimiter ',';
\COPY prs_telekom from 'telekom.csv' with csv header delimiter ',';
\COPY prs_trans_rac from 'trans_rac.csv' with csv header delimiter ',';
\COPY prs_ustanovitelji from 'prs_ustanovitelji.csv' with csv header delimiter ',';
\COPY prs_zastopniki from 'prs_zastopniki.csv' with csv header delimiter ',';

S tem je PRS register je uvožen v PostgreSQL bazo podatkov.

PRS register v PostgreSQL bazi podatkov.

PRS register v PostgreSQL bazi podatkov.

Naj omenimo, da se na tem mestu (še) ne bomo ukvarjali z optimizacijami baze, niti ne bomo podrobneje pojasnjevali same strukture PRS baze.

Povezovanje PRS registra s seznamom podjetij prenesenih na DUTB

Ko imamo podatke iz PRS registra ter podatke o podjetjih prenesenih na DUTB shranjene v PostgreSQL bazi, jih lahko pričnemo povezovati ter analizirati. Primer najbolj enostavne povezave obeh seznamov, ki združi podatke iz obeh seznamov:

select * from dutb_podjetja, prs_enota_rs where (dutb_podjetja.maticna = prs_enota_rs.maticna);

Za namen naše analize najprej izpišemo seznam zastopnikov (direktorjev, prokuristov,…) teh podjetij, skupaj z datumi njihovega vpisa in izbrisa iz PRS registra:

select prs_zastopniki.ime, prs_zastopniki.priimek, prs_zastopniki.datum_vpisa, prs_zastopniki.datum_izbrisa, dutb_podjetja.naziv, dutb_podjetja.znesek, dutb_podjetja.davcna, dutb_podjetja.maticna from prs_enota_rs, prs_zastopniki, dutb_podjetja where (prs_enota_rs.enota = prs_zastopniki.enota) and (dutb_podjetja.maticna = prs_enota_rs.maticna);
Zastopniki podjetij prenesenih na DUTB.

Zastopniki podjetij prenesenih na DUTB.

Izris grafa povezav med podjetji preko zastopnikov

V nadaljevanju bomo izrisali graf povezav med podjetji, ki so bila prenesena na DUTB. Pri tem velja, da povezava med podjetji obstaja, če je imelo podjetje kadarkoli v svoji zgodovini istega zastopnika. Če sta torej imeli podjetji A in B kadarkoli v preteklosti istega zastopnika torej velja, da sta povezani. Potrebno se je zavedati tudi določenih omejitev analize in sicer dejstva, da imamo na voljo zgolj imena in priimke zastopnikov, ne pa njihovih unikatnih identifikatorjev. Zato se v primeru, da imata dve različni osebi isto ime in priimek lahko zgodi, da povezava med podjetji v resnici ne obstaja.

V prvem koraku bomo podatke o zastopnikih in podjetjih shranili v novo tabelo z imenom dutb_osebe. Pri tem je potrebno nekaj opomb. Podjetja v PRS registru imajo tim. kratko ime ter popolno ime. V določenih primerih podjetja nimajo kratkega imena – v tem primeru je v bazi prisoten vnos “/”. Zato bomo v našo (novo tabelo) v teh primerih namesto kratkega imena shranili dolgo ime. To storimo z ukazom case … then … else … end. Poleg tega iz izpisa izločimo vse stečajne in likvidacijske upravitelje vrsta_zastopnika 09 ter 10. V tabelo tudi vpišemo tip osebe – v našem primeru je to zastopnik.

select initcap(trim(prs_zastopniki.ime)) as ime, initcap(trim(prs_zastopniki.priimek)) as priimek, case when prs_enota_rs.kratko_ime = '/' then trim(prs_enota_rs.popolno_ime) else trim(prs_enota_rs.kratko_ime) end as podjetje, 'zastopnik'::text as tip into dutb_osebe from prs_enota_rs, prs_zastopniki, dutb_podjetja where (prs_enota_rs.enota = prs_zastopniki.enota) and (dutb_podjetja.maticna = prs_enota_rs.maticna) and not (prs_zastopniki.vrsta_zastopnika in ('09', '10'));

Sledi čiščenje imen podjetij – iz imen odstranimo dodatke “v stečaju”, “v likvidaciji” ter različne nepotrebne presledke:

update dutb_osebe set podjetje = replace(podjetje,'"','');
update dutb_osebe set podjetje = replace(podjetje,'''','');
update dutb_osebe set podjetje = replace(podjetje, ' ',' ');
update dutb_osebe set podjetje = replace(podjetje, 'v stečaju','');
update dutb_osebe set podjetje = replace(podjetje, 'V STEČAJU','');
update dutb_osebe set podjetje = replace(podjetje, 'v likvidaciji','');
update dutb_osebe set podjetje = replace(podjetje, ' ',' ');
update dutb_osebe set podjetje = trim(podjetje);
update dutb_osebe set podjetje = trim(trailing '-' from podjetje);

Podatke sedaj lahko pregledamo:

select * from dutb_osebe;

Ker z PostgreSQL bazo ne delamo kot tim. administrator podatkovnega strežnika (ang. database superuser), ki ima edini pravico izvažanja podatkov z ukazom COPY, moramo iz baze izstopiti (Ctrl-d), iz Linux ukazne vrstice pa nato podatke iz omenjene tabele izvozimo v TXT datoteko v GraphViz obliki:

psql -U matej -d podjetja -c "select distinct '\"' || ime || ' ' || priimek || '\"->\"' || podjetje || '\"' from dutb_osebe;" > dutb_graf.txt
Podatke izvozimo na način, da odstranimo podvojene vnose (torej osebe, ki so bile večkrat zastopniki posameznega podjetja). To storimo z ukazom distinct. S pomočjo dvojne navpičnice (   , dvojna navpičnica je operator združevanja nizov znakov) izpis podatkov iz baze prilagodimo tako, da bo v obliki:
"ime priimek"->"podjetje"

Na začetek in konec datoteke dodamo ustrezno besedilo, s katerim zagotovimo pravilno strukturo .DOT datoteke, iz datoteke pa tudi odstranimo prazne vrstice ter nepotrebne presledke na začetku:

echo "digraph {" > dutb_graf.dot
tail -n +3 dutb_graf.txt | head -n -2 | sed 's/^ *//; s/ *$//; /^$/d' >> dutb_graf.dot
echo "}" >> dutb_graf.dot
Datoteka v GraphViz zapisu.

Datoteka v GraphViz zapisu.

Rezultat je datoteka dutb_graf.dot, ki jo lahko narišemo o GraphVizovim orodjem fdp. Graphviz (oz. Graph Visualization Software) je zbirka odprtokodnih orodij za risanje grafov s pomočjo ukaznega jezika DOT. Poganjamo ga iz ukazne vrstice, zato je to orodje zelo uporabno za poganjanje v skriptah. O orodju smo na kratko pisali že v enem starejših prispevkov. Našo sliko izrišemo z naslednjim ukazom:

fdp -Tpng -GK=1 -o dutb_graf.png dutb_graf.dot
Zastopniki podjetij prenesenih na DUTB. Vir: Javne informacije Slovenije - Agencija Republike Slovenije za javnopravne evidence in storitve, datum prevzema PRS registra: 25. september 2014  ter Informacije javnega značaja Družbe za upravljanje terjatev bank (DUTB), d.d..

Zastopniki podjetij prenesenih na DUTB. Vir: Javne informacije Slovenije – Agencija Republike Slovenije za javnopravne evidence in storitve, datum prevzema PRS registra: 25. september 2014 ter Informacije javnega značaja Družbe za upravljanje terjatev bank (DUTB), d.d.. Datoteka je velika 10,4 MB.

Z orodjem fdp smo tako podatke iz DOT datoteke prikazali v grafu v PNG datoteki, datoteko pa smo nato še nekoliko grafično obdelali z odprtokodnim orodjem GIMP. Podatki na sliki so anonimizirani, razen podatki o največji skupini podjetij, ki so povezana preko zastopnikov.

Zaključek

V tokratnem prispevku smo prikazali kako je mogoče s pomočjo zakona o dostopu do informacij javnega značaja uspešno pridobiti Ajpesov PRS register in kako ga povezati s podatki o podjetjih prenesenih na DUTB. Pri tem smo uporabili izključno prosto dostopna iz brezplačna orodja.

Naj opozorimo, da je za pravilno interpretacijo potrebno razumeti podatke in njihove obdelave. Povezave med podjetji smo definirali preko zastopnikov, pri čemer velja, da povezava med podjetji obstaja, če je imelo podjetje kadarkoli v svoji zgodovini istega zastopnika. Če sta torej imeli podjetji A in B kadarkoli v preteklosti istega zastopnika torej velja, da sta povezani. Povezavo med podjetji bi lahko definirali tudi preko članov nadzornih svetov in/ali lastnikov, kar bo morda vsebina kakšnega prihodnjega prispevka.

Naslednja pomembna omejitev, ki se jo je potrebno zavedati je dejstvo, da imamo v PRS registru na voljo zgolj imena in priimke zastopnikov, ne pa njihovih unikatnih identifikatorjev. Zato se v primeru, da imata dve različni osebi isto ime in priimek lahko zgodi, da povezava med podjetji v resnici ne obstaja. PRS register vsebuje tudi določene napake pri vpisih imen in priimkovStevanović” ter “Stevanovič“. Računalnik bo to razumel kot dve različni osebi, čeprav gre morda v resnici za eno. Enako velja za osebe, ki so si spremenile ime ali priimek (npr. zaradi poroke).

Kot smo že omenili, pa v analizo niso vključeni podatki o podjetjih prenesenih na DUTB s strani Abanke Vipa.

Podatkovna analiza seveda nikakor ne more biti stoodstotno zanesljiva. Nam pa iz množice podatkov izlušči tiste, ki jim lahko posvetimo nekaj dodatne pozornosti.

* * *

Viri podatkov:

  • Javne informacije Slovenije – Agencija Republike Slovenije za javnopravne evidence in storitve, datum prevzema PRS registra: 25. september 2014
  • Informacije javnega značaja Družbe za upravljanje terjatev bank (DUTB), d.d..

Orodja za podatkovno analizo:

 Zahvaljujem se Ajpesu, ki mi je s posredovanjem podatkov iz PRS registra omogočil izdelavo te analize.

Kategorije: Odprti podatki
Ključne besede: baze podatkov, odprti podatki, PostgreSQL, ZDIJZ