Diagrammi Sankey con Google Sheets

Lo script per fare i diagrammi sankey è stato fatto da
Bruce MacPherson http://ramblings.mcpher.com/Home/excelquirks/addons/sankeyaddon
e si trova su github: https://github.com/brucemcpherson/SankeySnip/blob/master/Sankey.gs

Occorre copiarlo e incollarlo nella pagina degli script del foglio GSheets (Strumenti-> Editor di script). Salvare.
Poi aggiornare la pagina: appare nel menu dei componenti aggiuntivi.
Lanciarlo:

la prima volta verrà chiesta l'autorizzazione ; darla.

A questo punto si può imparare ad usarlo con video o con le istruzioni del sito stesso:
definire le 3 colonne intestandole (esempio Entrate-Uscite-euro)
in tab Setting specificare il loro ruolo (Source-Target-Weight)
metterci dei contenuti di prova
in tab Chart generare il grafico Sankey (Insert):

Una volta appurato che il grafico viene creato, fare un po' di prove:

osservare che modificando le colonne, si aggiorna in tempo reale il grafico sulla destra in Chart:

e passando sopra col mouse si leggono gli euro.

Non c'è però controllo di congruità, ma questo non dovrebbe servire quando si sarà agganciato il sankey al bilancio vero del Comune.

Cancellando 2 righe, si aggiorna senza problemi:

 Ora non resta 😂 che andare a prendere i dati sul sito (E entrate U uscite) e riorganizzarli in base ai reparti di spesa o di entrate.

https://docs.google.com/spreadsheets/d/1Skerybug7kP-7uhpNsXqmZBYSlrenieDZ_d8161Xdew/edit?usp=sharing

treemap : confronto con il caso del bilancio del comune di Isso

Ora vediamo come migliora la vista treemap se su SOCRATA/Regione Lombardia il Comune di Crema caricasse colonne parlanti e non un semplice codice. Tipo Comune di Isso.

(per Crema si parte dal vecchio post: https://bilanciocremaformatoaperto.blogspot.com/2019/02/rappresentazione-treemap-del-bilancio.html che generava l'orribile treemap: http://lucascandelli.x10host.com/prove/bilanci/prev_2019/treemapCrema2019.html)


Link al bilancio prev 2019 del comune di Isso: https://www.dati.lombardia.it/Trasparenza/COMUNE-DI-ISSO-Bilancio-di-previsione-2019-spese-/6xap-rz4h

link al formato csv:
 https://www.dati.lombardia.it/api/views/6xap-rz4h/rows.csv

Si osserva che il file di Isso è "parlante": oltre ai codici MEF c'è anche il significato del codice,
pertanto nel treemap si potrà mettere il significato del codice invece del codice, così da renderlo più comprensibile agli "umani".

Il generatore del file i output sarà così (Isso usa le virgole nei campi, per cui usare tsv, non csv; inoltre usa i punti con decimali e esprime le cifre in euro non in centesimi, ci vuole FLOAT):
import csv
import requests
from string import split
url="https://www.dati.lombardia.it/api/views/6xap-rz4h/rows.tsv"
     
sito=requests.get(url)

listarighe=split(sito.text,"\n");

riga=[split(s,"\t") for s in listarighe]

f=open("outputisso.txt","w")
r=1
numerorighe=len(riga)
while r<len(riga)-1:
    rig=riga[r]
    bilanciostrut = riga[r][2]+"\t"+riga[r][4]+"\t"+riga[r][9]+"\t"+str(float(riga[r][10])/1)+"\n"
    print(str(r)+" di "+ str(numerorighe)+"\t" + bilanciostrut+"\n")
    f.write(bilanciostrut.encode('ascii', 'ignore'))
    r +=1
f.close()

Il generatore del file html, ricavato da quello di Crema, è così:

import csv
with open('outputisso.txt', 'rb') as csvfile:
    reader = csv.reader(csvfile, delimiter='\t', quotechar='|')
    varTreeMap="var data = { \n '"
    numrig = 0
    currentc1=""
    currentc2=""
    previousc1=""
    previousc2=""
    for row in reader:
        colnum = 0
        for col in row:
            a=row[colnum]
            a=a.replace("'","`")
            row[colnum]=a
            colnum += 1
        if not row[3] == '0':
            if numrig == 0:
                #inizializzo var data
                print row[1]
                currentc1=row[0]
                currentc2=row[1]
                previousc1=row[0]
                previousc2=row[1]
                varTreeMap+=currentc1
                varTreeMap+=" ':{ \n \t \t'"
                varTreeMap+=currentc2
                varTreeMap+=" ':{ \n \t \t \t '"
                varTreeMap+=row[2]
                varTreeMap+=" ': '"
                varTreeMap+=row[3]
                varTreeMap+=" ', \n "
            else:
                #tutte le altre righe
                currentc1=row[0]
                currentc2=row[1]
                if currentc1==previousc1:
                    if currentc2==previousc2:
                            #è un'altra foglia
                            varTreeMap+="\t \t \t  '"
                            varTreeMap+=row[2]
                            varTreeMap+=" ': '"
                            varTreeMap+=row[3]
                            varTreeMap+=" ', \n "
                    else:
                            #cambio rametto c2
                            varTreeMap+="\t \t \t 'altro':'0'},\n"
                            varTreeMap+="\t \t '"
                            varTreeMap+=currentc2
                            varTreeMap+=" ':{ \n \t \t \t '"
                            varTreeMap+=row[2]
                            varTreeMap+=" ': '"
                            varTreeMap+=row[3]
                            varTreeMap+=" ', \n "
                else:
                     #cambio missione c1
                    varTreeMap+="\t \t \t 'altro':'0' }\n"
                    varTreeMap+="\t  },\n '"
                    varTreeMap+=currentc1
                    varTreeMap+=" ':{ \n \t '"
                    varTreeMap+=currentc2
                    varTreeMap+=" ':{ \n \t \t '"
                    varTreeMap+=row[2]
                    varTreeMap+=" ': '"
                    varTreeMap+=row[2]
                    varTreeMap+=" ', \n "
                previousc1=currentc1
                previousc2=currentc2
            numrig += 1
    varTreeMap+="\t\t\t'altro':'0' }\n\t\t}\n},"

    f=open('vartreemap.txt','w')
    f.write(varTreeMap.encode('ascii', 'ignore'))
    f.close()
    
filenames = ['primopezzoissohtml.txt', 'vartreemap.txt', 'secondopezzohtml.txt']
with open('treemapIsso2019.html', 'w') as outfile:
    for fname in filenames:
        with open(fname) as infile:
            for line in infile:
                outfile.write(line)

e il treemap di Isso è molto più leggibile: http://lucascandelli.x10host.com/prove/bilanci/prev_2019/treemapIsso2019.html

rispetto a Crema: http://lucascandelli.x10host.com/prove/bilanci/prev_2019/treemapCrema2019.html

Tutta un'altra cosa....

rappresentazione treemap del bilancio comunale, analisi caso Milano

1. cos'è la treemap
2. esempio Milano  (http://dati.comune.milano.it/openbilancio/spese2019)
3. analisi del codice html/js, con riferimento alla struttura dati
4. collegamento con Socrata
5. suggerimenti alle Amministrazioni


1. cos'è la treemap

2. esempio Milano  (http://dati.comune.milano.it/openbilancio/spese2019)

il sorgente come al solito si guarda qui:
view-source:dati.comune.milano.it/openbilancio/spese2019


3. analisi del codice html/js, con riferimento alla struttura dati
...
var data = {
'DIRITTI SOCIALI, POLITICHE SOCIALI E FAMIGLIA' :{
'INTERVENTI PER L`INFANZIA E I MINORI E PER ASILI NIDO' :{
'CONTRATTI DI SERVIZIO PUBBLICO' :'108140720',
'RETRIBUZIONI IN DENARO' :'47197600',
'CONTRIBUTI SOCIALI EFFETTIVI A CARICO DELL`ENTE' :'13760700',
'UTENZE E CANONI' :'3819410',
'MANUTENZIONE ORDINARIA E RIPARAZIONI' :'3049760',
'ALTRO' :'4523820'},
'INTERVENTI PER GLI ANZIANI' :{
'CONTRATTI DI SERVIZIO PUBBLICO' :'66118870',
'INTERVENTI ASSISTENZIALI' :'4862260',
'ALTRI TRASFERIMENTI A FAMIGLIE' :'3400000',
'RETRIBUZIONI IN DENARO' :'1988990',
'ALTRO' :'2096600'},
'INTERVENTI PER LA DISABILITA`' :{
'CONTRATTI DI SERVIZIO PUBBLICO' :'47249980',
'TRASFERIMENTI CORRENTI A AMMINISTRAZIONI CENTRALI' :'7253480',
'RETRIBUZIONI IN DENARO' :'6053250',
'INTERVENTI ASSISTENZIALI' :'2543870',
'CONTRIBUTI SOCIALI EFFETTIVI A CARICO DELL`ENTE' :'1750230',
'TRASFERIMENTI CORRENTI A ISTITUZIONI SOCIALI PRIVATE' :'1250000',
'ALTRO' :'2007100'},
etc...
come si vede i dati devono essere in qualche modo 'annidati' con le parentesi graffe

i programmini python servono a creare la variabile data che deve rimpiazzare quella di Milano con i dati di Crema (magari un giorno anche tutto html...).

4. collegamento con Socrata
il json del bilancio  è molto grezzo, non annida le voci in base alle missioni e programmi
occorre un programmino python per riorganizzarlo in base al codice ministeriale:

appunti su come accedere a Socrata, spezzare i campi e ricostruirli con python (esempio riga 101):

Nota : lo split in base al trattino "-" del campo descrizione darà liste di lunghezza 1 invece che 2  perché non sempre presente nelle righe; da gestire

per prima cosa occorre riorganizzare i campi (soprattutto quelli del codice MEF) come NON vuole fare l'Amministrazione, poi creare l'albero.

import requests
from string import split
url="https://www.dati.lombardia.it/api/views/gy48-yges/rows.csv"
      
sito=requests.get(url)

listarighe=split(sito.text,"\n");

riga=[split(s,",") for s in listarighe]

f=open("output.txt","w")
r=1

while r<len(riga):
    
    codiceMEF=riga[r][1]
    listacodiceMEF=split(riga[r][1],".")
    
    #gestione trattino non sempre presente o troppo presente, maledizione
    listadescrizione=split(riga[r][2],"-")
    if len(listadescrizione)==1:
        campodescr1=listadescrizione[0]
        campodescr2=" "
    else:
        campodescr1=listadescrizione[0]
        campodescr2=' '.join(listadescrizione[1:])
    bilanciostrut = riga[r][0]+"\t"+listacodiceMEF[0]+"\t"+listacodiceMEF[1]+"\t"+listacodiceMEF[2][0:5]+"\t"+listacodiceMEF[2][5:8]+"\t"+campodescr1+"\t"+campodescr2+"\t"+riga[r][4]+"\n"
    
    
   #solo uscite U, E per entrate
    if riga[r][0]== "U":
        #ignorare codici no ascii , per evitare casini
        print(str(r)+"\t" + bilanciostrut+"\n")
        f.write(bilanciostrut.encode('ascii', 'ignore'))
        
    r +=1
f.close()
#chiude il file, salvando modifiche ecc

il file output.txt si presenta così, coi campi separati:

ACHTUNG: Crema usa gli apici per gli accenti (') questo è drammatico per javascript, highcharts; occorre sostituire gli apici con un altro tipo di apice come fanno a Milano  (`)

Passiamo alla creazione dell'albero con le parentesi graffe.
Si percorre in modo ricorsivo ciascuna colonna di output.txt generato sopra  da sinistra a destra  pregando che i codici siano giusti...




import csv
with open('output.txt', 'rb') as csvfile:
    reader = csv.reader(csvfile, delimiter='\t', quotechar='|')
    varTreeMap="var data = { \n '"
    numrig = 0
    currentc1=""
    currentc2=""
    previousc1=""
    previousc2=""
    for row in reader:
        colnum = 0
        for col in row:
            a=row[colnum]
            a=a.replace("'","`")
            row[colnum]=a
            colnum += 1
        if not row[7] == '0':
            if numrig == 0:
                #inizializzo var data
                print row[1]
                currentc1=row[1]
                currentc2=row[2]
                previousc1=row[1]
                previousc2=row[2]
                varTreeMap+=currentc1
                varTreeMap+=" ':{ \n \t \t'"
                varTreeMap+=currentc2
                varTreeMap+=" ':{ \n \t \t \t '"
                varTreeMap+=row[6]
                varTreeMap+=" ': '"
                varTreeMap+=row[7]
                varTreeMap+=" ', \n "
            else:
                #tutte le altre righe
                currentc1=row[1]
                currentc2=row[2]
                if currentc1==previousc1:
                    if currentc2==previousc2:
                            #è un'altra foglia
                            varTreeMap+="\t \t \t  '"
                            varTreeMap+=row[6]
                            varTreeMap+=" ': '"
                            varTreeMap+=row[7]
                            varTreeMap+=" ', \n "
                    else:
                            #cambio rametto c2
                            varTreeMap+="\t \t \t 'altro':'0'},\n"
                            varTreeMap+="\t \t '"
                            varTreeMap+=currentc2
                            varTreeMap+=" ':{ \n \t \t \t '"
                            varTreeMap+=row[6]
                            varTreeMap+=" ': '"
                            varTreeMap+=row[7]
                            varTreeMap+=" ', \n "
                else:
                     #cambio missione c1
                    varTreeMap+="\t \t \t 'altro':'0' }\n"
                    varTreeMap+="\t  },\n '"
                    varTreeMap+=currentc1
                    varTreeMap+=" ':{ \n \t '"
                    varTreeMap+=currentc2
                    varTreeMap+=" ':{ \n \t \t '"
                    varTreeMap+=row[6]
                    varTreeMap+=" ': '"
                    varTreeMap+=row[7]
                    varTreeMap+=" ', \n "
                previousc1=currentc1
                previousc2=currentc2
            numrig += 1
    varTreeMap+="\t\t\t'altro':'0' }\n\t\t}\n},"

    f=open('vartreemap.txt','w')
    f.write(varTreeMap.encode('ascii', 'ignore'))
    f.close()
    
filenames = ['primopezzohtml.txt', 'vartreemap.txt', 'secondopezzohtml.txt']
with open('treemapCrema2019.html', 'w') as outfile:
    for fname in filenames:
        with open(fname) as infile:
            for line in infile:
                outfile.write(line)

 


A questo punto nel file  vartreemap.txt c'è la variabile da copiare tale e quale nel codice html di Milano al posto della loro

il file con la sola variabile da copiare

e il gioco è fatto:
treemapCrema2019.html

Ovviamente i pezzi di python andranno messi insieme uno dopo l'altro , inclusa l'inclusione finale di vardata fra i due pezzi (il prima primopezzohtml.txt e il dopo  secondopezzohtml.txt, preparati all'uopo; ultime 6 righe del programma) di html.


5. suggerimenti alle Amministrazioni
a. usare apici diversi da quelli di javascript  (`) altrimenti uno è costretto a lavorarci
b. controllare formato numerico caricato su Socrata (al momento in centesimi di euro, per cui il valore in output.txt va diviso per 100 - per ora - per avere numeri coretti nel treemap:
bilanciostrut = riga[r][0]+"\t"+listacodiceMEF[0]+"\t"+listacodiceMEF[1]+"\t"+listacodiceMEF[2][0:5]+"\t"+listacodiceMEF[2][5:8]+"\t"+campodescr1+"\t"+campodescr2+"\t"+str(int(riga[r][5])/100)+"\n"

)

c. divulgare una tabella di corrispondenze fra codice di campo e significato, in modo che sul treemap appaiano parole invece che numeri
d. un codice di costo è sbagliato...., e non appare nel treemap
'50024 ':{ 
  '01 ':{ 
  '  ': '17897586000 ', 
  'altro':'0'},
'03 ':{ 
  '  ': '1557266300 ', 
    '  ': '61169600 ', 
    '  ': '54138100 ', 
    '  ': '1510858200 ', 
  'altro':'0' }
  },

dai dati alla app

Gli appunti di questo post si riferiscono a come una app android può visualizzare i dati del Bilancio.
Riferimento principale: https://community.thunkable.com/t/introduction-to-apis/12067
Sito nel quale sviluppare la app, da PC: http://app.thunkable.com
Sito del bilancio in formato json: https://www.dati.lombardia.it/resource/jy22-j8r3.json
(Bilancio di previsione 2018-2020 Comune di Crema).

Entrare in  http://app.thunkable.com (cosa migliore: con le credenziali account google)
Una volta creato il NuovoProgetto (Progetti ==> Nuovo Progetto), si devono fare due cose:
1. preparare la schermata per l'utilizzatore , con i vari elementi (bottoni, immagini, testi, tabelle, etc): "Progettazione" in alto a sinistra.
2. preparare il programma che lavora associato agli elementi della schermata ("Blocchi").

Progettazione
Trascinare dalla lista a sinistra alla figura dello smartphone (Sotto screen1 per intenderci) un Pulsante, una Etichetta e un Web (Web è sotto Connettività e, trascinato nella immagine dello smartphone finirà in basso).

schermata con gli elementi dell'interfaccia-utente

Impostare Web1 cliccandoci sopra per far apparire a destra le sue proprietà e inserire l'indirizzo della Regione Lombardia che punta al file in versione  json del Bilancio e che estrae al massimo 5 righe del bilancio 2019 (tanto per cominciare):

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$query=SELECT%20descrizione,preventivo_2019%20WHERE%20preventivo_2019%20>%200%20LIMIT%205

(oppure uno a piacere degli esempi json del post: 

https://bilanciocremaformatoaperto.blogspot.com/2019/02/estrazione-dati-direttamente-via-url.html   )

Ora passiamo a "Blocchi" per il programma. 

Cosa vogliamo dal programma? Partiamo dalla cosa più semplice, senza fronzoli.

Vogliamo per prima cosa far apparire i dati sul telefono quando si preme il Pulsante.

I dati verranno visualizzati nella Etichetta.

Blocchi

Cliccando su Blocchi ci si trova una pagina vuota:

Cliccando Pulsante1 appaiono le cose che si possono fare col pulsante (toccarlo, tenerci sopra il dito, etc): siccome ci interessa fare apparire i dati se tocchiamo/clicchiamo il pulsante, scegliamo il blocco in alto:

una volta deposto nella pagina, ci interessa che al click venga mandata una richiesta al web

Quindi sempre a sinistra scegliamo web e dalla lista scegliamo eseguiWeb1Ottieni 

e incastriamolo nel blocco che dice cosa fare se si clicca il Pulsante1:

A questo punto, quando si toccherà il pulsante, la richiesta verrà inviata e RegioneLombardia risponderà: occorre scrivere il programma che mostra i dati una volta che Regione Lombardia li ha rispediti allo smartphone.
Quindi da Web1 bisogna scegliere "quando lettura file completata"):

e, dopo averlo depositato sulla pagina, incastrarci dentro il blocchetto imposta_etichetta1_testo a :

e , una volta incastrato nel blocco quando lettura completata, sorvolare col mouse Contenuto risposta in modo da far apparire le due possibilità e scegliere "valore di ContenutoRisposta":

e trascinarlo per  incastrarlo nell'Imposta EtichettaTesto :

Fine.
Il programma è fatto in pratica da questi due elementi:
1. quando clicchi il pulsante, manda richiesta  e ottieni i dati
2. quando arriva la risposta, mostrala nell'etichetta

Ora bisogna provarlo con lo smartphone. Ci sono diverse possibilità. 

Una è creare il file apk e installarlo poi nello smartphone. La cosa richiede parecchi click. (in "Progettazione" Tendina "Compila" salva su computer, copiarlo nello smartphone installarlo nello smartphone.)

Un'altra, più utile quando si fanno i programmi pezzo per pezzo, è installare una apposita app nello smartphone con la quale lo smartphone è in collegamento perenne con il PC via rete wifi di casa propria e mostra quel che fa il programma in sviluppo senza doverlo installare tutte le volte che si fa una modifica. (solo alla fine, quando la app è pronta, si installerà l'apk definitivo una volta per tutte).

Va installata da qui:

https://play.google.com/store/apps/details?id=com.thunkable.appinventor.aicompanion3&hl=it 

e poi, aperta nello smartphone, le vanno date le autorizzazioni per accedere alle risorse necessarie alle app. 

Chiederà poi un codice (che identifica la app che si sta sviluppando): il codice da scrivere nello smartphone  è quello che appare con la tendina nel PC:

attendere che lo smartphone si connetta, in modo che appaia l'interfaccia appena progettata:

schermata iniziale

Ora occorre toccare il pulsante e vedere se i dati arrivano nell'Etichetta:

se tutto va bene , nello smartphone deve apparire questo

Una volta che il "passaggio" è aperto , non resta che sbizzarrirsi con la fantasia e la potenza dei blocchi di thunkable.

Prossimamente:
1. come modificare le richieste ("query") al sito SOCRATA della Regione
2. come analizzare i file json e estrarre le parti che interessano
3. come mostrare i dati con un po' di immagini

Estrazione dati direttamente via URL

link ai dati in formato json : https://www.dati.lombardia.it/resource/jy22-j8r3.json (se clicchi li scarichi: okkio!)

link ai dati in formato csv: https://www.dati.lombardia.it/resource/jy22-j8r3.csv (se clicchi li scarichi: okkio!)

E1. Selezione delle righe che hanno spesa a preventivo 2018 maggiore di zero:

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$where=preventivo_2018>0

E2. Selezione di tutte le righe che contengono nel campo descrizione la parola POLIZIA

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$where=descrizione%20like%20%27%25POLIZIA%25%27

E3. Scaricare un csv con tutte le righe che contengono nel campo descrizione la parola POLIZIA

https://www.dati.lombardia.it/resource/jy22-j8r3.csv?$where=descrizione%20like%20%27%25POLIZIA%25%27

E4. scaricare i campi descrizione e preventivo_2019 se preventivo_2019 è maggiore di zero

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$query=SELECT%20descrizione,preventivo_2019%20WHERE%20preventivo_2019%20>%200

Nota:

The $query parameter allows you to combine multiple SoQL clauses together into a single parameter, for convenience. Similar to SQL, clauses must be specified in a specific order:

• SELECT
• WHERE
• ORDER BY
• GROUP BY
• LIMIT
• OFFSET

Note that unlike SQL, there is no FROM clause.

E5. Come E4 , ma limitando a sole 5 righe

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$query=SELECT%20descrizione,preventivo_2019%20WHERE%20preventivo_2019%20>%200%20LIMIT%205

E6. Come E5, ma partendo dalla centesima riga:

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$query=SELECT%20descrizione,preventivo_2019%20WHERE%20preventivo_2019%20>%200%20LIMIT%205%20OFFSET%20100

ma va bene anche usando gli spazi invece di %20:

www.dati.lombardia.it/resource/jy22-j8r3.json?$query=SELECT descrizione,preventivo_2019 WHERE preventivo_2019>0 LIMIT 5 OFFSET 100

E7. Ordinare secondo la colonna descrizione in ordine alfabetico , sempre limitando, ad esempio, a 50 righe

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$query=SELECT%20descrizione,preventivo_2019%20WHERE%20preventivo_2019>0%20ORDER%20BY%20descrizione%20LIMIT%2050

E8. Estrarre le righe in cui il campo descrizione inizia con POLIZIA

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$where=starts_with(descrizione,%27POLIZIA%27)

E9. Estrarre le righe in cui il campo descrizione inizia con POLIZIA E il cui preventivo 2019 non è nullo

https://www.dati.lombardia.it/resource/jy22-j8r3.json?$where=starts_with(descrizione,%27POLIZIA%27)%20AND%20preventivo_2019>0