UWAGA: Ten tekst nie jest o polityce ale o [elementarnej] statystyce.
Media informowały, że posłowie PiS Adam Hofman, Mariusz A. Kamiński i Adam Rogacki wzięli na podróż do Madrytu na posiedzenie komisji Zgromadzenia Parlamentarnego Rady Europy po kilkanaście tysięcy złotych zaliczki, zgłaszając wyjazd samochodem; w rzeczywistości polecieli tanimi liniami lotniczymi. Ponieważ kontrola wydatków posłów jest iluzoryczna różnica pomiędzy kosztem podróży samochodem a samolotem [za dużo mniejsze pieniądze] miała stanowić dodatkowy przychód wyżej wymienionych. Według prokuratury, która wszczęła śledztwo, zachodzi podejrzenie popełnienia oszustwa.
Łapiąc wiatr w żagle [sprawa się upubliczniła tuż przed ostatnimi wyborami samorządowymi] koalicja rządząca w osobie Marszałka Sejmu RP Sikorskiego zarządziła audyt, którego efektem było udostępnienie m.in. dokumentu pn. Wyjazdy zagraniczne posłów VII kadencja (kopia jest tutaj).
Jak przystało na kraj, w którym od lat działa Ministerstwo cyfryzacji zestawienie jest w formacie PDF, zatem pierwszym ruchem była zamiana na coś przetwarzalnego. Wpisanie w google PDF+Excel+conversion skutkuje ogromną listą potencjalnych konwerterów. Bagatelizując skalę problemu spróbowałem dokonać konwersji narzędziami dostępnymi on-line, ale z marnym rezultatem (za duży dokument przykładowo; serwis za free jest tylko dla PDFów mniejszych niż 50 stron). W przypadku Convert PDF to EXCEL online & free coś tam skonwertował, nawet wyglądało toto na pierwszy rzut oka OK ale na drugi już nie: dokument niekompletny oraz nieprawidłowo zamienione niektóre liczby (przykładowo zamiast 837,50 zł w arkuszu jest 83750 -- 100 razy więcej!).
Ostatecznie skończyło się na ściągnięciu 30 dniowej wersji Adobe Acrobata Pro XI, który faktycznie sprawdził się w roli konwertera PDF→XLSX. Do konwersji wykorzystałem służbowego laptopa Elki wyposażonego w legalny Office 2010, na którym zainstalowałem ww. AA Pro XI. OOffice niby czyta XLSX, ale z koszmarnymi błędami, więc żeby dalej móc obrabiać arkusz w Linuksie wczytałem wynikowy XLSX do Excela 2010 po czym zapisałem go w (starszym) formacie XLS. Ten plik wyświetlił się w OO Calcu bez problemu.
Arkusz jest tak sformatowany, że 4 pierwsze komórki oraz są często wielowierszowe i scalone, zawierają bowiem liczbę porządkową, datę, miejsce i cel wyjazdu delegacji posłów. Po zamianie na plik CSV zawartość komórek scalonych pojawi się w pierwszym wierszu, a pozostałe będą puste. Prostym skryptem Perlowym mogę wypełnić puste komórki wg. algorytmu: jeżeli cztery pierwsze pola są puste, to skopiuj wartości ostatnich niepustych:
if ($tmp[0] eq '' && $tmp[1] eq '' && $tmp[2] eq '' && $tmp[3] eq '' ) { ... }
Pierwszy problem: wielowierszowe komórki z kolumn 1--4 nie zawsze są scalone. Czasem tekst jest podzielony na wiersze co psuje konwersję. Ręcznie scalam niescalone komórki (trochę to trwa). Przed scaleniem usuwam z kolumn 1--4 końce wiersza.
Drugi problem: część liczb nie jest liczbami z uwagi na użycie separatora tysięcy, który się zamienił w PDFie na odstęp (spację). Zatem zaznaczam kolumny zawierające różne pozycje kosztów po czym:
- Edytuj→Znajdź i zamień
- usuwam odstępy, tj. zamieniam spację na pusty napis
- Format→Komórki
- wybieram numer z dwoma miejscami po przecinku.
Po uporządkowaniu arkusza, zapisuję go w formacie CSV. Następnie prostym skryptem Perlowym zamieniam na taki plik CSV, w którym puste komórki są wypełniane zawartością z poprzednich wierszy. Kolumna Państwo - miasto jest kopiowana. Kopia jest zmieniana na jednoznaczne: Państwo, miasto (pierwszy-kraj, przecinek, pierwsze miasto z listy celów podróży -- żeby geokoderowi było łatwiej.)
Innym skryptem Perlowym dodaję do pliku CSV 3 kolumny, które zawierają:
współrzędne celu podróży (w tym celu zamieniam adres Państwo, miasto na współrzędne geograficzne korzystając z geokodera Google);
odległość w kilometrach pomiędzy punktem o współrzędnych 21.028075/52.225208 (W-wa, Wiejska 1) a celem podróży (obliczoną przy wykorzystaniu pakietu GIS::Distance);
-
linię zdefiniowana w formacie KML o końcach 21.028075/52.225208--współrzędne-celu-podróży (do ewentualnego wykorzystania z Google Fusion Tables).
#!/usr/bin/perl
#
use Storable;
use Google::GeoCoder::Smart;
use GIS::Distance;
$geo = Google::GeoCoder::Smart->new();
my $gis = GIS::Distance->new();
my $GeoCodeCacheName = 'geocode.cache';
my $NewCoordinatesFetched=0; # global flag
my $SLEEP_TIME = 2 ;
my $coords_okr = "21.028075,52.225208"; # Warszawa = środek świata
my %GeoCodeCache = %{ retrieve("$GeoCodeCacheName") } if ( -f "$GeoCodeCacheName" ) ;
my ($wwa_lng, $wwa_lat) = split (",", $coords_okr);
my $linesNo = 0 ;
my $GCtotaluse = 1; # laczna liczba wywolan geocodera
while (<>) {
$linesNo++;
chomp(); $_ =~ s/[ \t]+;[ \t]+/;/g; ## usuń ew. niepotrzebne spacje
@line = split ";", $_; print STDERR "**$linesNo = $line[3] ... ";
# geokodowanie (uwaga na limit)
# Poprawki dla miejsc, których nie zna Google:
$line[3] =~ s/Erewań/Erywań/; ##
$line[3] =~ s/Sowayma/Madaba/; ## najbliższe miasto
$line[3] =~ s/Bołszowce/Iwano-Frankiwsk/; ## najbliższe miasto
my $coords = addr2coords( $line[3] );
($tmp_lat, $tmp_lng, $gcuse) = split " ", $coords;
if ($gcuse > 0) {$GCtotaluse++ ; }
$distance = $gis->distance($tmp_lat,$tmp_lng => $wwa_lat,$wwa_lng );
$distance_t = sprintf ("%.1f", $distance);
my $kml_line = "<LineString><coordinates>$tmp_lng,$tmp_lat $coords_okr</coordinates></LineString>";
print "$_;\"$coords\";$distance_t;\"$kml_line\"\n";
print STDERR "\n";
if ($GCtotaluse % 100 == 0 ) {# store every 100 geocoder calls
store(\%GeoCodeCache, "$GeoCodeCacheName");
print STDERR "\n... Cache stored. ***\n";
}
}
##
store(\%GeoCodeCache, "$GeoCodeCacheName");
## ## ## ####
sub addr2coords {
my $a = shift ;
my $r = shift || 'n';
my ($lat, $lng) ;
my $GCuse = 0;
##consult cache first
if (exists $GeoCodeCache{"$a"} ) {
print STDERR "Coordinates catched ... $a ";
($lat,$lng) = split (" ", $GeoCodeCache{"$a"} );
}
else {
print STDERR "Geocoding ... $a ";
my ($resultnum, $error, @results, $returncontent) = $geo->geocode("address" => "$a");
$GCuse = 1;
sleep $SLEEP_TIME; ## make short pause
$resultnum--;
$resultNo=$resultnum ;
if (resultNo > 0) { print STDERR "** Location $a occured more than once! **" }
if ($error eq 'OK') {
$NewCoordinatesFetched=1;
for $num(0 .. $resultnum) {
$lat = $results[$num]{geometry}{location}{lat};
$lng = $results[$num]{geometry}{location}{lng};
##print "*** LAT/LNG:$lat $lng ERROR: $error RES: $resultNo ***\n";
}
$GeoCodeCache{"$a"} = "$lat $lng"; ## store in cache
} else { print STDERR "** Location $a not found! due to $error **" }
}
if ($r eq 'r' ) { return "$lng,$lat,$GCuse"; } # w formacie KML
else { return "$lat $lng $GCuse"; }
}
Gotowy plik CSV zawierający zestawienie podróży jest dostępny tutaj.
Na podstawie zestawienia i z użyciem pakietu ggplot2 generują się takie oto śliczne wykresy.
Wszystkie podróże z zestawienie (N=1874; odpowiednio: koszt łączny, koszt transportu, długość w tys km):
Tylko podróże dla których koszt transportu był niezerowy (N=1423; odpowiednio: koszt łączny, koszt transportu, długość w tys km):
Poniższy skrypt R sumuje i drukuje wszystkie podróże każdego posła:
require(plyr)
d <- read.csv("W7RR_podroze_by_podroz1.csv", sep = ';', dec = ",", header=T, na.string="NA");
# Dodaj kolumnę której wartości to konkatenacja: "Poseł|Klub"
d[,"PosKlub"] <- do.call(paste, c(d[c("Posel", "Klub")], sep = "|"));
# Usuń wszystko za wyjątkiem tego co potrzeba:
d <- d[ c("PosKlub", "Klacznie", "Ktransp", "Dist") ];
# Sumowanie po PosKlub
PSums <- as.data.frame ( ddply(d, .(PosKlub), numcolwise(sum)) );
# Z powrotem rozdziel kolumnę "Poseł|Klub" na dwie
PSums <- as.data.frame ( within(PSums, PosKlub <-data.frame( do.call('rbind',
strsplit(as.character(PosKlub), '|', fixed=TRUE)))) )
# Drukuj
PSums;
Z pliku .Rout kopiuję zestawienie łącznych wydatków posłów oraz łącznej pokonanej przez nich odległości:
PosKlub.X1 PosKlub.X2 KlacznieT KtranspT DistT
1 Adam Abramowicz PiS 4.02599 2.64595 1.3153
2 Adam Hofman PiS 119.55271 59.53315 26.1716
3 Adam Kępiński SLD 10.15754 7.93882 3.8069
4 Adam Kępiński TR 12.63098 8.02327 2.2107
...
Uwaga: kilkanaście nazwisk się powtarza ponieważ posłowie zmienili przynależność klubową w czasie trwania kadencji [Aby uwzględnić takich posłów sumowanie odbywało się po wartościach zmiennej zawierającej połączone napisy Poseł|Klub.]
Na podstawie takiego z kolei zestawienia i znowu z użyciem ggplot2 generują inne śliczne wykresy.
Uwaga: sumowane tylko podróże, dla których koszt transportu był niezerowy (N=1423; odpowiednio: koszt łączny, koszt transportu, długość w tys km):
Link do tabeli zawierającej zestawienie podróży w formacie Google Fusion Tables jest tutaj.
Dane + skrypty dostępne są także w: github.com/hrpunio/Data.
