Conteggi aggregati di campi con multivalore

Un amico mi passa la seguente tabella:

tabella di partenza

La quarta colonna (prov_tocca) di questa tabella (sicilia.csv ) contiene i nomi delle province che toccano la provincia corrente presente nella seconda colonna (DEN_PCM), cioè Caltanissetta è confinante (toccata spazialmente) con le province di: Palermo, Agrigento, Enna, Catania e Ragusa.

L’amico curioso mi chiede: quante volte è presente il nome Palermo nel quarto campo (prov_tocca)?? In questo caso specifico la risposta è banale, ma il quesito generale è: Conteggi aggregati di campi con multivalore: in generale i valori presenti nel secondo campo sono diversi da quelli presenti nel quarto campo.

La mia risposta: usando SQLite o un Layer Virtuale in QGIS, la query che risolve il quesito è:

WITH recursive splitvalues(fid, val, more) AS (
  SELECT fid, '', replace(prov_tocca, ', ', ',') || ','
  FROM "Sicilia"
  UNION ALL
  SELECT fid, substr(more, 0, instr(more, ',')), substr(more, 1+instr(more, ','))
  FROM splitvalues
  WHERE more <> ''
)
SELECT trim(val, ', ') AS prov, count(*) AS n
FROM splitvalues
WHERE val <> ''
GROUP BY val
ORDER BY val;

risultato:

output query

cioè la provincia di Messina è presente tre volte nel campo ‘prov_tocca‘ della prima tabella; Caltanissetta è presente 5 volte e cosi via per le altre province.

Faccio un piccola verifica usando VISIDATA:

  1. apro file (vd sicilia.csv);
  2. Shift+! sul campo fid per renderla chiave primaria;
  3. navigo fino all’ultimo campo prov_tocca e sposto il cursore nella riga con più elementi e digito Shift+: (per splittare colonna) [oppure: sulla colonna prov_tocca digito = prov_tocca(‘,’) e invio e poi ordino dopo aver digitato #];
  4. alla richiesta split regex: digito , (virgola e invio);
  5. nascondo le colonne che non mi servono digitando – : prov_tocca, SIGLA e DEN_PCM;
  6. navigo fino a fid e Shift+m (per il melted);
  7. navigo fino alla colonna Value e digito +
  8. alla richiesta digito count (per contare le occorrenze) e invio;
  9. digito Shift+f sempre sul campo Value;
  10. Fatto!!!
gif

Ulteriore verifica usando Miller:

mlr --c2p --barred nest --explode --values --across-fields --nested-fs "," -f prov_tocca then reshape -r "tocca" -o item,value then count-distinct -f value then sort -nr count sicilia.csv
Output Miller
Province

Riferimenti:

Dati:

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...

Questo sito utilizza Akismet per ridurre lo spam. Scopri come vengono elaborati i dati derivati dai commenti.