Come riscrivere le query SQL in Pandas ed altro ancora

traduzione dell'articolo "How to rewrite your SQL queries in Pandas, and more" di Irina Truong

Quindici anni fa, c’erano solo pochi skill che uno sviluppatore software doveva conoscere bene. Con quelle conoscenze si aveva la possibilità di trovare una posizione lavorativa nel 95% dei casi. Quelli skill erano:

programmazione orientata agli oggetti
linguaggi di scripting
javascript, e ...
SQL

SQL è uno strumento utile ogni volta che è necessario dare un’occhiata veloce ad alcuni dati e trarre conclusioni preliminari che potrebbero, alla fine, portare da una relazione o un’applicazione in fase di scrittura. Questa è chiamata analisi esplorativa.

Ora però i dati arrivano in varie forme e formati e non sono più sinonimo di “database relazionale”. Ci si può imbattere in file CSV, testo normale, Parquet, HDF5 e chissà cos’altro ancora. Ed è qui che brilla la libreria Pandas.

Cos'è Pandas?

Python Data Analysis Library, acronimo per Pandas, è una libreria Python creata per l’analisi e la manipolazione dei dati. È un prodotto open source ed supportata da Anaconda. Si adatta particolarmente a dati strutturati (in formato tabellare). Maggiori informazioni si hanno al sito http://pandas.pydata.org/pandas-docs/stable/index.html

Cosa ci posso fare?

Tutte le query che prima facevi ai dati in SQL, e molto altro ancora!

Grande! Da dove comincio?

Questa è la parte che può spaventare chi è abituato a interrogare i dati via SQL.

SQL è un linguaggio di programmazione dichiarativa: https://it.wikipedia.org/wiki/Programmazione_dichiarativa.

Con SQL, si dichiara ciò che si vuole in una frase che sembra quasi inglese.

La sintassi di Pandas è molto diversa da SQL. In Pandas, si applicano le operazioni sui dataset le si incatenano, al fine di trasformare e rimodellare i dati come si desidera

Abbiamo bisogno di un frasario!

L'anatomia di una query SQL

Una query SQL è composta da alcune parole chiave importanti. Attraverso quelle parole chiave si aggiungo le specifiche esatte di quali dati si vuole conoscere. Questo è uno scheletro senza tali specifiche:

SELECT… FROM… WHERE…

GROUP BY… HAVING…

ORDER BY…

LIMIT… OFFSET…

Ci sono altri termini. Questi sono i più importanti. Come si traducono questi termini in Pandas?

Prima di tutto è necessario caricare alcuni dati in Pandas in quanto non presenti nel database. Ecco come:

import pandas as pd
airports = pd.read_csv('data/airports.csv')
airport_freq = pd.read_csv('data/airport-frequencies.csv')
runways = pd.read_csv('data/runways.csv')

source file-load_data_pd-py

Ho preso questi dati da http://ourairports.com/data/

SELECT, WHERE, DISTINCT, LIMIT

Ecco alcune istruzioni SELECT. I risultati vengono troncati con LIMIT e filtrati con WHERE. DISTINCT viene usato per rimuovere i risultati duplicati.

SQL	Pandas
select * from airports	airports
select * from airports limit 3	airports.head(3)
select id from airports where ident = 'KLAX'	airports[airports.ident == 'KLAX'].id
select distinct type from airport	airports.type.unique()

SELECT con condizioni multiple

Uniamo condizioni multiple con una &. Se invece vogliamo solo un sottoinsieme di colonne dalla tabella, allora quel sottoinsieme lo si ottiene dichiarando un’altra coppia fra parentesi quadre.

SQL	Pandas
select * from airports where iso_region = 'US-CA' and type = 'seaplane_base'	airports[(airports.iso_region == 'US-CA') & (airports.type == 'seaplane_base')]
select ident, name, municipality from airports where iso_region = 'US-CA' and type = 'large_airport'	airports[(airports.iso_region == 'US-CA') & (airports.type == 'large_airport')][['ident', 'name', 'municipality']]

SQL

Pandas

select * from airports where iso_region = 'US-CA' and type = 'seaplane_base'

airports[(airports.iso_region == 'US-CA') & (airports.type == 'seaplane_base')]

select ident, name, municipality from airports where iso_region = 'US-CA' and type = 'large_airport'

airports[(airports.iso_region == 'US-CA') & (airports.type == 'large_airport')][['ident', 'name', 'municipality']]

ORDER BY

Da impostazioni predefinite, Pandas ordina in dati in modalità crescente. Per ottenere l’ordinamento inverso, si deve usare l’opzione ascending == False

SQL	Pandas
select * from airport_freq where airport_ident = 'KLAX' order by type	airport_freq[airport_freq.airport_ident == 'KLAX'].sort_values('type')
select * from airport_freq where airport_ident = 'KLAX' order by type desc	airport_freq[airport_freq.airport_ident == 'KLAX'].sort_values('type', ascending=False)

SQL

Pandas

select * from airport_freq where airport_ident = 'KLAX' order by type

airport_freq[airport_freq.airport_ident == 'KLAX'].sort_values('type')

select * from airport_freq where airport_ident = 'KLAX' order by type desc

airport_freq[airport_freq.airport_ident == 'KLAX'].sort_values('type', ascending=False)

IN… NOT IN

Ora sappiamo come filtrare su un valore, ma quando riguarda un elenco di valore presenti in una condizione IN ? In Pandas, questa operazione, avviene attraverso il metodo .isin(). Per avere invece una qualsiasi condizione al rovescio (la negazione) va usato il simbolo ~.

SQL	Pandas
select * from airports where type in ('heliport', 'balloonport')	airports[airports.type.isin(['heliport', 'balloonport'])]
select * from airports where type not in ('heliport', 'balloonport')	airports[~airports.type.isin(['heliport', 'balloonport'])]

SQL

Pandas

select * from airports where type in ('heliport', 'balloonport')

airports[airports.type.isin(['heliport', 'balloonport'])]

select * from airports where type not in ('heliport', 'balloonport')

airports[~airports.type.isin(['heliport', 'balloonport'])]

GROUP BY, COUNT, ORDER BY

Il raggruppamento è semplice: lo si ottine con l’operatore .groupby(). C’è una sottile differenza semantica fra il COUNT in SQL e quello in Pandas. In Pandas, .count() restituisce il numero di valori univoci. Per ottenere lo stesso risultato di SQL COUNT, invece va utilizzato .size().

SQL	Pandas
select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, type	airports.groupby(['iso_country', 'type']).size()
select iso_country, type, count() from airports group by iso_country, type order by iso_country, count() desc	airports.groupby(['iso_country', 'type']).size().to_frame('size').reset_index().sort_values(['iso_country', 'size'], ascending=[True, False])

SQL

Pandas

select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, type

airports.groupby(['iso_country', 'type']).size()

select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, count(*) desc

airports.groupby(['iso_country', 'type']).size().to_frame('size').reset_index().sort_values(['iso_country', 'size'], ascending=[True, False])

Qui sotto ecco come si raggruppa su più di un campo. Pandas, come impostazione predefinita, ordina i dati seguendo l’elenco dei campi, pertanto non è necessario usare il .sort_values() come nel primo esempio. Se si vogliono invece utilizzare diversi campi per l’ordinamento, o con DESC invece di ASC, come nel secondo esempio, allora si deve essere più espliciti:

SQL	Pandas
select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, type	airports.groupby(['iso_country', 'type']).size()
select iso_country, type, count() from airports group by iso_country, type order by iso_country, count() desc	airports.groupby(['iso_country', 'type']).size().to_frame('size').reset_index().sort_values(['iso_country', 'size'], ascending=[True, False])

SQL

Pandas

select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, type

airports.groupby(['iso_country', 'type']).size()

select iso_country, type, count(*) from airports group by iso_country, type order by iso_country, count(*) desc

airports.groupby(['iso_country', 'type']).size().to_frame('size').reset_index().sort_values(['iso_country', 'size'], ascending=[True, False])

A cosa serve il trucco con .to_frame() e .reset_index()? Dovendo ordinare per il nuovo campo calcolato (size), è necessario trasformalo in un DataFrame. Dopo aver applicato l’operazione di raggruppamento in Pandas, si ottiene un nuovo tipo dal nome GroupByObject. Pertanto è necessario riconvertirlo in un DataFrame. Attraverso .reset_index(), si riapplica la numerazione delle righe per il dataframe.

HAVING

In SQL, è possibile filtrare i dati raggruppati usando la condizione HAVING. In Pandas, si può utilizzare .filter() e fornire una funzione Python (o una lambda) che restituirà True qualora il gruppo debba essere incluso nel risultato.

SQL	Pandas
select type, count() from airports where iso_country = 'US' group by type having count() > 1000 order by count(*) desc	airports[airports.iso_country == 'US'].groupby('type').filter(lambda g: len(g) > 1000).groupby('type').size().sort_values(ascending=False)

SQL

Pandas

select type, count(*) from airports where iso_country = 'US' group by type having count(*) > 1000 order by count(*) desc

airports[airports.iso_country == 'US'].groupby('type').filter(lambda g: len(g) > 1000).groupby('type').size().sort_values(ascending=False)

I primi N record

Si assuma di avere fatto alcune query preliminari e di avere ora un dataframe dal nome by_country, che contiene il numero di aeroporti per paese:

L’esempio successivo ordina l’elenco per airport_count e seleziona solo i primi 10 paesi con il conteggio maggiore. Il secondo esempio è quello con il caso più complicato, in cui si vogliono ordinare “i prossimi 10 dopo i primi 10”:

SQL Pandas

SQL	Pandas
select iso_country from by_country order by size desc limit 10	by_country.nlargest(10, columns='airport_count')
select iso_country from by_country order by size desc limit 10 offset 10	`by_country.nlargest(20, columns='airport_count').tail(10)`

select iso_country from by_country order by size desc limit 10

by_country.nlargest(10, columns='airport_count')

select iso_country from by_country order by size desc limit 10 offset 10

by_country.nlargest(20, columns='airport_count').tail(10)

funzioni di aggregazione (MIN, MAX, MEAN)

ora, a partire da questo dataframe (runways)

la lunghezza minima, massima, media e mediana del campo runways è calcolata in questo modo:

SQL	Pandas
select max(length_ft), min(length_ft), mean(length_ft), median(length_ft) from runways	runways.agg({'length_ft': ['min', 'max', 'mean', 'median']})

Si può notare che mentre in SQL, ogni statistica è rappresentata in una colonna, in Pandas invece sono rappresentati su ogni riga.

Non c’è nulla cui preoccuparsi: si può facilmente trasporre il dataframe con .T per ottenere il risultato in colonne:

JOIN

Attraverso .merge() si uniscono i dataframe Pandas. Per farlo è necessario indicare quale è la colonna da unire (left_on e right_on), e il tipo di unione: inner (predefinito), left (che corrisponde a LEFT OUTER in SQL), right (RIGHT OUTER), o outer (FULL OUTER).

SQL	Pandas
select airport_ident, type, description, frequency_mhz from airport_freq join airports on airport_freq.airport_ref = airports.id where airports.ident = 'KLAX'	airport_freq.merge(airports[airports.ident == 'KLAX'][['id']], left_on='airport_ref', right_on='id', how='inner')[['airport_ident', 'type', 'description', 'frequency_mhz']]

SQL

Pandas

select airport_ident, type, description, frequency_mhz from airport_freq join airports on airport_freq.airport_ref = airports.id where airports.ident = 'KLAX'

airport_freq.merge(airports[airports.ident == 'KLAX'][['id']], left_on='airport_ref', right_on='id', how='inner')[['airport_ident', 'type', 'description', 'frequency_mhz']]

UNION ALL e UNION

si utilizza pd.concat() per ottenere la funzione UNION ALL fra due dataframe:

SQL	Pandas
select name, municipality from airports where ident = 'KLAX' union all select name, municipality from airports where ident = 'KLGB'	pd.concat([airports[airports.ident == 'KLAX'][['name', 'municipality']], airports[airports.ident == 'KLGB'][['name', 'municipality']]])

SQL

Pandas

select name, municipality from airports where ident = 'KLAX' union all select name, municipality from airports where ident = 'KLGB'

pd.concat([airports[airports.ident == 'KLAX'][['name', 'municipality']], airports[airports.ident == 'KLGB'][['name', 'municipality']]])

Per deduplicare i risultati (l’equivalente di <strong<UNION), va aggiunto anche .drop_duplicates().

INSERT

Fino a qui è stato mostrato come interrogare i dati, ma, nel processo delle analisi esplorative, si può avere anche la necessità di modificarli. Cosa si deve fare per aggiungere record mancanti?

In Pandas non esiste un qualcosa come INSERT. Pertanto è necessario creare un nuovo dataframe contenente i nuovi record e quindi concatenarlo con l’altro:

SQL	Pandas
create table heroes (id integer, name text);	df1 = pd.DataFrame({'id': [1, 2], 'name': ['Harry Potter', 'Ron Weasley']})
insert into heroes values (1, 'Harry Potter');	df2 = pd.DataFrame({'id': [3], 'name': ['Hermione Granger']})
insert into heroes values (2, 'Ron Weasley');
insert into heroes values (3, 'Hermione Granger');	pd.concat([df1, df2]).reset_index(drop=True)

UPDATE

ed ora come correggere alcuni dati errati nel dataframe originale:

SQL	Pandas
update airports set home_link = 'http://www.lawa.org/welcomelax.aspx' where ident == 'KLAX'	airports.loc[airports['ident'] == 'KLAX', 'home_link'] = 'http://www.lawa.org/welcomelax.aspx'

DELETE

Il modo più semplice (e il più leggibile) per “cancellare” dati da un dataframe di Pandas è di suddividere il dataframe nelle righe che da mantenere. In alternativa, si può ottenere gli indici delle righe da eliminare e da utilizzare con .drop():

SQL	Pandas
delete from lax_freq where type = 'MISC'	lax_freq = lax_freq[lax_freq.type != 'MISC']
	lax_freq.drop(lax_freq[lax_freq.type == 'MISC'].index)

Immutabilità

Va detta una cosa importante: immutabilità. Per impostazione predefinita, la maggior parte degli operatori applicati a un dataframe Pandas restituiscono un nuovo oggetto. Alcuni operatori accettano un parametro inplace = True, quindi si può lavorare con il dataframe originale. Ad esempio, ecco come ripristinare un indice al suo posto:

df = df.reset_index(drop=True, inplace=True)

Tuttavia, l’operatore .loc mostrato nel precedente esempio per l’UPDATE individua gli indici dei record per gli aggiornamenti e i valori vengono modificati sul posto. Inoltre, se si ha aggiornato tutti i valori in una colonna:

df['url'] = 'http://google.com'

o aggiungere una nuova colonna calcolata:

df['total_cost'] = df['price'] * df['quantity']

tutto questo accade sul posto.

Ed altro La cosa più bella di Pandas è che è più di un semplice motore di interrogazione dati. Si possono fare molte altre cose con i dati, come:

 
df.to_csv(...) # file csv
df.to_hdf(...) # file HDF5
df.to_pickle(...) # oggetti serializzati
df.to_sql(...) # su un database SQL
df.to_excel(...) # su foglio Excel
df.to_json(...) # in una stringa JSON
df.to_html(...) # rappresentati in un tabella HTML
df.to_feather(...) # binario feather-format
df.to_latex(...) # tabella d'ambiente tabulare
df.to_stata(...) # file binari in Stata
df.to_msgpack(...) # oggetto (serializzato) msgpack
df.to_gbq(...) # in una tabella Google BigQuery
df.to_string(...) # in un output tabella console-friendly tabular
df.to_clipboard(...) # appunti che possono essere copiati in Excel

Disegnati

top_10.plot(
    x='iso_country',
    y='airport_count',
    kind='barh',
    figsize=(10, 7),
    title='Top 10 countries with most airports')

in modo d'avere grafici davvero carini!

Condividere Il miglior mezzo modo per condividere risultati di query di Pandas, grafici e cose come questo sono i notebook Jupyter (http://jupyter.org/). Tant'è che, alcune persone (come il soprendente Jake Vanderplas), pubblicano interi libri come notebook Jupyter: https://github.com/jakevdp/PythonDataScienceHandbook.È così facile creare un nuovo notebook:
```
$ pip install jupyter
$ jupyter notebook
```
Successivamente:
- aprire l'indirizzo web localhost:8888
- premere su "Nuovo" e dare un nome al notebook
- interrogare e visualizzare i dati
- creare un repository GitHub e aggiungere il notebook (il file con estensione .ipynb)
GitHub ha un fantastico visualizzatore integrato per i notebook Jupyter con formattazione Markdown.

Ed ora che abbia inizio il viaggio con Pandas!

Spero che ora di aver convinto che la libreria Pandas può essere utile a te e al tuo vecchio amico SQL ai fini dell’analisi esplorativa dei dati - e in alcuni casi, anche meglio. È arrivato il momento di mettere le mani su alcuni dati da interrogare!

Articolo di Irina Truong