I dati di soldipubblici.gov.it sul tuo portale open data

I portali open data hanno sempre bisogno di essere alimentati con dataset nuovi e soprattutto aggiornati.  Attivare quei meccanismi che consentono di generare file aperti e allo stesso aggiornati, nella Pubblica Amministrazione, è un processo che molte volte richiede tempo e soprattutto un cambio di mentalità; è un discorso questo, che si sente spesso nella PA.

La comunità di opendatasicilia mi ha spesso insegnato che, è mostrando la potenzialità dei dati aperti con il riuso, che quei meccanismi di interesse si possono sbloccare, attirando nuove idee e nuovi dati. Avere quindi dati aperti, aggiornati e attendibili, è cosa rara e complicata, ma quando ne puoi discutere un po’ in gruppo e alimentare le tue fantasie e le tue idee con uno script di poche righe ma efficientissimo, allora la vita sembra prendere una piega del tutto diversa!

Sotto la spinta delle mie esigenze lo script (creato da Andrea Borruso), originariamente nato per scaricare i dati di un singolo ente da soldipubblici.gov.it, ha consentito la pubblicazione di una dataset sul portale open data della Città Metropolitana di Napoli, in due formati (csv e json), con aggiornamento automatico settimanale.

Ma andiamo per gradi.
Il sito soldipubblici.gov.it consente  di consultare e scaricare  i dati della spesa pubblica  ricercando un ente.

È possibile fare il download del CSV, al click su un pulsante, ma nel mio caso avevo bisogno di automatizzare questo processo.

Lo script (accessibile qui) è scritto in bash, sfrutta le utility httpie, jq e csvkit e funziona in questo modo:

  • si imposta il codice dell’ente di interesse (i codici dei vari enti si trovano qui)
  • il dato viene scaricato in formato JSON, e le cifre spese espresse in origine come numero intero (è stata rimossa la parte decimale, moltiplicando per 100), vengono riportate a numero decimale;
  • il file così trasformato viene salvato in JSON e CSV.

Alla versione originale dello script volevo aggiungere la possibilità di rimuovere quei record in cui le colonne con i valori numerici sono non valorizzate (sono “null”). E allora mi è stato suggerito di usare csvgrep, che può filtrare le righe di una tabella a partire da un’espressione regolare ed è stata aggiunta la riga:

Lo script viene eseguito su un server del mio Ente, e l’output è accessibile dall’esterno pubblicamente in http.

Essendo i dati di “soldipubblici” aggiornati settimanalmente, nasceva infine l’esigenza di doverne automatizzare il download senza la necessità di un intervento umano. Così, sempre su indicazioni apprese nello scambio in mailing list, ho modificato il file /etc/crontab (che negli ambienti Linux consente di impostare delle operazioni da eseguire in modo pianificato) aggiungendo:

La stringa “0 4 */7 * *” corrisponde a “ogni 7 giorni alle 4 di mattina”.

Verificato che il tutto funzionasse correttamente e ottenuti i link statici dei file creati dallo script, ho provveduto a caricarli in un dataset sul portale ckan degli Open Data della Città Metropolitana di Napoli.

È nato così il primo caso d’uso specifico dello script SpesaCron, dove grazie all’estrazione di due file csv e json dai dati di soldipubblici.gov.it è stato possibile pubblicare un dataset contenente tutte le voci di spesa di una Pubblica Amministrazione, automaticamente alimentato dallo stesso sito e con frequenza di aggiornamento settimanale.

Leggere, interrogare e trasformare file XML da riga di comando

Intro

Il comune di Palermo ha pubblicato i dati pubblici e aperti sulle elezioni comunali dell’11 giugno 2017 in formato XML, con uno schema descritto in questo file.

Qui sotto ad esempio la struttura di uno degli oltre 200 file pubblicati.

L’XML è uno dei formati classici di pubblicazione di dati aperti, ma non è un formato per tutti. Molti utenti infatti non sono in grado di esaminarli.
Per la sua natura è facilmente leggibile da un calcolatore e ci sono varie modalità per farlo.

E con un’utility specializzata (XMLStarlet) e con un piccolo comando come questo di sotto, è possibile trasformare questo file XML in una “piatta” tabella con tutti i dati sui voti dei candidati di una lista al consiglio comunale, per ogni sezione elettorale (40 candidati per 600 sezioni, quindi 24000 record).

xmlstarlet sel –net -t -m //SV/V0/V1 -v @NUMERO -o , \
-v @VOTIVALIDI_C1 -o ,  -v ../@NUMERO -o , \
-v ../../@NUMERO -n \
http://comunali2017.comune.palermo.it/SEZ_3_82053_L12.xml

 

Qui sotto lo vedete in azione:

I dati sui risultati delle liste elettorali hanno questa struttura:

<SV NUMERO=1 NOME=SEZIONE 1 NOMEBREVE=SEZ. 1 UBICAZIONE= NUM_ZONA= NUM_ZONA2= TOTVOT=540 TOTVOTM=255 TOTVOTF=285 FLZEROVOT=N ELETTORI=1112 ELETTORIM=556 ELETTORIF=556 CONFERMATO=S VOTIVALIDI_C0=54 VOTIVALIDI_C1=37 VCAS_C1=0″ VCNAS_C1=”0″ VOTI_SOLO_C1=”0″ VOTIVALIDI_C2=”0″ VCAS_C2=”0″ VCNAS_C2=”0″ VCNAS_TOT=”0″ VOTI_SOLO_C2=”0″ VOTI_NULLI_SOLO_C2=”0″ VOTI_NULLI=”0″ NULLE=”21″ BIANCHE=”8″ CONFCONS=”S” MAXVOTVAL=”9″>
        <V0 NUMERO=12 VOTIVALIDI_C0=54 TOT_VOTIVALIDI_C1=37 TOT_VOTISOLO_C1=0>
            <V1 NUMERO=1 VOTIVALIDI_C1=5 VOTISOLO_C1=0 VOTCONASS_C1=0 VOTCONNONASS_C1=0 CIFRAIND=59 TOT_VOTIVALIDI_C2=0 TOT_VOTISOLO_C2=0/>
            <V1 NUMERO=2 …/>
            <V1 NUMERO=3 …/>        
        </V0>
</SV>

 

SV è la sezione elettorale con il suo numero identificativo (e altri attributi), che contiene al suo interno i dati su V0 che rappresenta la lista (in questo caso la 12, quella del “Movimento 5 stelle”), che contiene al suo interno V1, ovvero i dati sui candidati al consiglio comunale. Con questa struttura gerarchica SV>V0>V1.

Il comando di sopra nel dettaglio:

  • sel --net -t -m, abilito la selezione (sel) su un file remoto (--net), impostando un template (-t) per “mappare” gli elementi che corrispondono (“matchano” -m) alla seguente query XPATH;
  • "//SV/V0/V1", i candidati al consiglio;
  • "@NUMERO", l’attributo con il numero identificativo del candidato;
  • -o ",", per inserire un separatore di testo;
  • -v "@VOTIVALIDI_C1", l’attributo con i voti validi del candidato;
  • -o ",", per inserire un separatore di testo;
  • -v "../@NUMERO", mi muovo verso l’alto nella gerarchia dell’XML di un gradino con .., quindi vado in V0 (la lista) e recupero l’identificativo numerico della lista;
  • -o ",", per inserire un separatore di testo;
  • -v "../../@NUMERO", mi muovo verso l’alto nella gerarchia dell’XML di due gradini con ../.., quindi vado in SV (il seggio) e recupero l’identificativo numerico del seggio;
  • -n, per inserire un’andata a capo per ogni risultato ottenuto;
  • http://…/SEZ_3_82053_L12.xml è l’URL del file XML.

In output nella shell avrò:

1,5,12,1
2,9,12,1
3,2,12,1
…,…,…,…

Ovvero

numeroCandidato voti numeroLista sezione
1 5 12 1
2 9 12 1
3 2 12 1

Creare dei file CSV con i dati per tutte le liste

Ho pensato che possa essere molto interessante fare un esempio più ricco e completo e creare uno script bash per:

  • scaricare tutti i file XML delle 18 liste;
  • estrarre da ognuno l’anagrafica dei candidati consiglieri;
  • estrarre da ognuno il numero di voti, per ogni sezione, di ogni candidato al consiglio;
  • fare il join – unire – le info sul numero di voti, con l’anagrafica dei candidati consiglieri, e creare un file CSV per ogni lista;
  • unire tutti i file CSV e produrre anche un unico file con il numero di voti di ogni candidato, per ogni lista, per ogni sezione.

Requisiti

Il prodotto finale è uno script BASH, quindi bisogna avere a disposizione un sistema compatibile con questo linguaggio (lo sono essenzialmente tutti).

Richiede tre utility:

  • l’immancabile cURL, per scaricare i file;
  • XMLStarlet per interrogare i file XML e trasformarli in file CSV;
  • csvkit per fare il join e il merge dei CSV scaricati.

Richiede una conoscenza di base (e/o la volontà/possibità di farserla) su:

  • XPATH, per estrarre i dati (per interrogare) i file XML;
  • la linea di comando, perché è un po’ il campo di gioco di queste modalità di accesso e modifica di file;
  • BASH, che è il linguaggio dello script finale;
  • aprire i file XML del comune con un buon editor di testo, guardarli un po’ e comprenderne la struttura.

Lo script

Lo script per intero è più in basso. A seguire un esploso delle varie parti che lo compongono.

La prima cosa che viene eseguita nello script è il download dei file delle liste. Queste sono 18 ed è comodo scaricarle con un ciclo for ... loop che lo fa 18 volte per noi.

for i in {1..18};
  do curl -s http://comunali2017.comune.palermo.it/SEZ_3_82053_L$i.xml > $i.xml;
done

 

Poi da ognuno dei 18 file XML vengono estratti i dati anagrafici e i dati per sezione, sempre con un ciclo for.
A ogni file viene aggiunta anche un’intestazione di colonne.

# scarico l’anagrafica di ogni candidato di ogni lista
for i in {1..18}; do xmlstarlet sel -t -m //C0/C1 -v @NUMERO -o , -v @NOME -o , -v ../@NUMERO -o , -v ../@NOME -n $i.xml > anagraficaLista_$i.txt; sed -i 1s/^/numeroCandidato,nomeCandidato,numeroLista,nomeLista\n/ anagraficaLista_$i.txt & done
 
# scarico i voti di ogni candidato di ogni lista per ogni sezione
for i in {1..18}; do xmlstarlet sel -t -m //SV/V0/V1 -v @NUMERO -o , -v @VOTIVALIDI_C1 -o ,  -v ../@NUMERO -o , -v ../../@NUMERO -n $i.xml > listaSezioni_$i.txt ; sed  -i 1s/^/numeroCandidato,voti,numeroLista,sezione\n/ listaSezioni_$i.txt & done

 

I file di anagrafica hanno questa struttura:

numeroCandidato nomeCandidato numeroLista nomeLista
1 GELARDA IGOR DETTO GERARDA DETTO GERALDA 12 MOVIMENTO 5 STELLE
2 ARGIROFFI GIULIA 12 MOVIMENTO 5 STELLE
3 CAPARROTTA GIANCARLO DETTO CAPAROTTA 12 MOVIMENTO 5 STELLE

Mentre quelli con i dati per sezione:

numeroCandidato voti numeroLista sezione
1 5 12 1
2 9 12 1
3 2 12 1

Poi viene fatto il join tra anagrafica e dati per sezione:

for i in {1..18}; do csvsql –query select * from  listaSezioni_$i LEFT JOIN anagraficaLista_$i ON listaSezioni_$i.numeroCandidato=anagraficaLista_$i.numeroCandidato listaSezioni_$i.txt anagraficaLista_$i.txt > lista_$i.csv; done

Per ogni lista viene prodotto un file con nome lista_NumeroLista.csv, con questa struttura (ci sono delle colonne duplicate, che potrei rimuovere in fase di join):

numeroCandidato voti numeroLista sezione numeroCandidato nomeCandidato numeroLista nomeLista
1 5 12 1 1 GELARDA IGOR DETTO GERARDA DETTO GERALDA 12 MOVIMENTO 5 STELLE
2 9 12 1 2 ARGIROFFI GIULIA 12 MOVIMENTO 5 STELLE
3 2 12 1 3 CAPARROTTA GIANCARLO DETTO CAPAROTTA 12 MOVIMENTO 5 STELLE

E infine viene creato anche un unico file CSV di insieme (scaricabile da qui), con i dati per tutti i consiglieri di tutte le liste, per ogni sezione (senza le colonne duplicate). Sono 638 candidati per 600 sezioni per un totale di 382800 record.

csvstack *.csv | csvcut -c 1,2,3,4,6,8 > liste.csv

Quindi avrò in output 1 file CSV con i dati per ogni lista e quello soprastante, per totale di 19 file CSV (encoding UTF-8 e come separatore la ,).

Lo script di poche righe (al netto dei commenti) è quello di sotto, tutto realizzato con oggetti free e open-source.

#!/bin/bash
 
# Requisiti #
# – avere un sistema in cui è possibile eseguire uno script bash;
# – l’utility XMLStarlet http://xmlstar.sourceforge.net/download.php
# – l’utility csvkit http://csvkit.readthedocs.io/
 
# attivo la modalità di debug
set -x
 
# cancello file csv e xml pre esistenti nella cartella in cui lancio lo script
rm -R *.csv
 
# scarico tutti i dati delle 18 liste
for i in {1..18}; do curl -s http://comunali2017.comune.palermo.it/SEZ_3_82053_L$i.xml > $i.xml;done
 
# scarico l’anagrafica di ogni candidato di ogni lista
for i in {1..18}; do xmlstarlet sel -t -m //C0/C1 -v @NUMERO -o , -v @NOME -o , -v ../@NUMERO -o , -v ../@NOME -n $i.xml > anagraficaLista_$i.txt; sed -i 1s/^/numeroCandidato,nomeCandidato,numeroLista,nomeLista\n/ anagraficaLista_$i.txt & done
 
# scarico i voti di ogni candidato di ogni lista per ogni sezione
for i in {1..18}; do xmlstarlet sel -t -m //SV/V0/V1 -v @NUMERO -o , -v @VOTIVALIDI_C1 -o ,  -v ../@NUMERO -o , -v ../../@NUMERO -n $i.xml > listaSezioni_$i.txt ; sed  -i 1s/^/numeroCandidato,voti,numeroLista,sezione\n/ listaSezioni_$i.txt & done
 
# faccio il join tra i dati per sezione e l’anagrafica dei candidati
# l’output è un file di dettaglio in formato CSV per ogni lista
for i in {1..18}; do csvsql –query select * from  listaSezioni_$i LEFT JOIN anagraficaLista_$i ON listaSezioni_$i.numeroCandidato=anagraficaLista_$i.numeroCandidato listaSezioni_$i.txt anagraficaLista_$i.txt > lista_$i.csv; done
 
# faccio il merge di tutti i CSV e produco un unico file
# con tutti i voti per candidato per sezione di ogni lista
csvstack *.csv | csvcut -c 1,2,3,4,6,8 > liste.csv
 
# cancello file che non mi sono più utili
rm -R *.txt
rm -R *.xml

Libro consigliato

Per entrare nel mondo divertentissimo ed efficiente della “riga di comando” mi sento di consigliare il bel “Data Science at the Command Line” . È un libro per tutti, di facile lettura e pieno di esempi utili.