Como usar SQL em pandas usando consultas pandasql

Tutoriais

Python SQL

SQL, ou Linguagem de Consulta Estruturada, é uma linguagem de programação usada para acessar, extrair, manipular e explorar dados armazenados em bancos de dados relacionais. pandas é uma biblioteca de código aberto em Python projetada especificamente para manipulação e análise de dados.

Neste tutorial, vamos discutir quando e como podemos (e quando não podemos) usar a funcionalidade SQL no contexto do pandas. Além disso, vamos olhar para vários exemplos de implementação desta abordagem e comparar os resultados com o código equivalente em pandas puro.

Por que Usar SQL no pandas?

Dadas as definições na introdução, por que alguém deveria querer usar SQL combinado com pandas quando o último é um pacote abrangente para análise de dados?

A resposta é que em algumas ocasiões, especialmente para programas complexos, as consultas SQL parecem muito mais diretas e fáceis de ler do que o código correspondente em pandas. Isto é particularmente verdadeiro para as pessoas que inicialmente usaram SQL para trabalhar com dados e depois aprenderam pandas.

Se você precisa de mais treinamento em pandas, você pode conferir nosso curso de Manipulação de Dados com pandas e o Tutorial Pandas: DataFrames em Python.

Para ver a leitura de SQL em ação, suponha que temos uma tabela (um dataframe) chamada penguins contendo informações diversas sobre pinguins (e trabalharemos com tabela tão simples no tutorial posterior). Para extrair todas as espécies únicas de pinguins que são machos e que têm asas maiores que 210 mm, precisaríamos do seguinte código em pandas:

penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique()

Ao invés disso, para obter a mesma informação usando SQL, executaríamos o seguinte código:

SELECT DISTINCT species FROM penguins WHERE sex = 'Male' AND flipper_length_mm > 210

O segundo pedaço de código, escrito em SQL, parece quase uma frase natural em Inglês e portanto é muito mais intuitivo. Podemos aumentar a sua leitura ainda mais, espalhando-o por várias linhas:

SELECT DISTINCT species
  FROM penguins 
 WHERE sex = 'Male' 
   AND flipper_length_mm > 210

Agora que identificamos as vantagens de usar SQL para pandas, vamos ver como podemos técnicamente combiná-los ambos.

Como Usar pandasql

A biblioteca Python pandasql permite consultar dataframes pandas executando comandos SQL sem ter que se conectar a qualquer servidor SQL. Por trás dos panos, ela usa o sintaxe SQLite, detecção automática de qualquer dataframe pandas, e o trata como uma tabela SQL regular.

Configurando seu ambiente

Primeiro, precisamos instalar pandasql:

pip install pandasql

Em seguida, importamos as pacotes necessárias:

from pandasql import sqldf
import pandas as pd

Você está acima importando diretamente a função sqldf da biblioteca pandasql, que é virtualmente a única função significativa da biblioteca. Como o nome sugere, é aplicada para consultar dataframes usando a sintaxe SQL. Além desta função, o pandasql vem com dois datasets internos simples que podem ser carregados usando as funções autoexplicativas load_births() e load_meat().

Sintaxe do pandasql

A sintaxe da função sqldf é muito simples:

sqldf(query, env=None)

Aqui, query é um parâmetro obrigatório que recebe uma consulta SQL como uma string, e env—um parâmetro opcional (e raramente útil) que pode ser locals() ou globals() e permite que sqldf acesse o conjunto correspondente de variáveis em seu ambiente Python.

A função sqldf retorna o resultado de uma consulta como um dataframe do pandas.

Quando podemos usar o pandasql

A biblioteca pandasql permite trabalhar com dados usando a Linguagem de Consulta de Dados (DQL), que é um dos subconjuntos de SQL. Em outras palavras, com o pandasql, podemos executar consultas nos dados armazenados em um banco de dados para recuperar as informações necessárias. Em particular, podemos acessar, extrair, filtrar, ordenar, agrupar, unir, agregar os dados e realizar operações matemáticas ou lógicas neles.

Quando não podemos usar o pandasql

O pandasql não permite o uso de qualquer outro subconjunto de SQL, exceto DQL. Isso significa que não podemos aplicar o pandasql para modificar (atualizar, truncar, inserir, etc.) tabelas ou alterar (atualizar, excluir ou inserir) os dados em uma tabela.

Adicionalmente, já que esta biblioteca é baseada em sintaxe SQL, devíamos ter cuidado com os bugides conhecidos no SQLite.

Exemplos de uso de pandasql

Agora, vamos dar uma olhada mais granular em como executar consultas SQL em dataframes de pandas usando a função sqldf do pandasql. Para ter algum dado para praticar, vamos carregar um dos conjuntos de dados embutidos da biblioteca seaborn — penguins:

import seaborn as sns
penguins = sns.load_dataset('penguins')
print(penguins.head())

Output:

   species island     bill_length_mm  bill_depth_mm  flipper_length_mm  \
0  Adelie  Torgersen            39.1           18.7              181.0   
1  Adelie  Torgersen            39.5           17.4              186.0   
2  Adelie  Torgersen            40.3           18.0              195.0   
3  Adelie  Torgersen             NaN            NaN                NaN   
4  Adelie  Torgersen            36.7           19.3              193.0   

   body_mass_g     sex  
0       3750.0    Male  
1       3800.0  Female  
2       3250.0  Female  
3          NaN     NaN  
4       3450.0  Female

Se você precisar atualizar suas habilidades em SQL, nossa skill track de Fundamentos de SQL é um bom ponto de referência.

Extraindo dados com pandasql

print(sqldf('''SELECT species, island 
FROM penguins 
LIMIT 5'''))

Output:

  species  island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

Acima, nós extraímos informações sobre as espécies e a geografia das primeiras cinco pinguins do dataframe penguins. Note que a execução da função sqldf retorna um dataframe de pandas:

print(type(sqldf('''SELECT species, island 
                      FROM penguins 
                     LIMIT 5''')))

Output:

<class 'pandas.core.frame.DataFrame'>

Em pandas puro, seria:

print(penguins[['species', 'island']].head())

Output:

   species island
0  Adelie  Torgersen
1  Adelie  Torgersen
2  Adelie  Torgersen
3  Adelie  Torgersen
4  Adelie  Torgersen

Outro exemplo é a extração de valores únicos de uma coluna:

print(sqldf('''SELECT DISTINCT species 
                 FROM penguins'''))

Output:

     species
0     Adelie
1  Chinstrap
2     Gentoo

Em pandas, seria:

print(penguins['species'].unique())

Output:

['Adelie' 'Chinstrap' 'Gentoo']

Ordenando dados com pandasql

print(sqldf('''SELECT body_mass_g 
                 FROM penguins 
                ORDER BY body_mass_g DESC 
                LIMIT 5'''))

Output:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0

Acima, nós ordenamos nossos pinguins pelo massa corporal em ordem decrescente e mostramos os cinco valores de massa corporal mais altos.

No pandas, seria:

print(penguins['body_mass_g'].sort_values(ascending=False, 
ignore_index=True).head())

Resultado:

0    6300.0
1    6050.0
2    6000.0
3    6000.0
4    5950.0
Name: body_mass_g, dtype: float64

Filtrando dados com pandasql

Vamos tentar o mesmo exemplo que mencionamos no capítulo “Por que usar SQL em pandas”: extraindo as espécies únicas de pinguins que são machos e que possuem asas maiores que 210 mm:

print(sqldf('''SELECT DISTINCT species
                 FROM penguins 
                WHERE sex = 'Male' 
                  AND flipper_length_mm > 210'''))

Resultado:

     species
0  Chinstrap
1     Gentoo

A cima, filtramos os dados com base em duas condições: sexo = 'Macho' e flipper_length_mm > 210.

O mesmo código em pandas pareceria um pouco mais assustador:

print(penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique())

Resultado:

['Chinstrap' 'Gentoo']

Agrupando e agregando dados com pandasql

Agora, vamos aplicar agrupamento e agregação de dados para encontrar a cauda mais longa para cada espécie no dataframe:

print(sqldf('''SELECT species, MAX(bill_length_mm)
                 FROM penguins 
                GROUP BY species'''))

Resultado:

     species  MAX(bill_length_mm)
0     Adelie                 46.0
1  Chinstrap                 58.0
2     Gentoo                 59.6

O mesmo código em pandas:

print(penguins[['species', 'bill_length_mm']].groupby('species', as_index=False).max())

Resultado:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6

Executando operações matemáticas com pandasql

Com pandasql, podemos facilmente executar operações matemáticas ou lógicas nos dados. Imaginem que queremos calcular a relação de comprimento de cauda para cada pinguim e mostrar as cinco maiores medições desta proporção:

print(sqldf('''SELECT bill_length_mm / bill_depth_mm AS length_to_depth
                 FROM penguins
                ORDER BY length_to_depth DESC
                LIMIT 5'''))

Resultado:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599

Note que desta vez, usamos o alias length_to_depth para a coluna com os valores de proporção. Caso contrário, obteríamos uma coluna com um nome monstruoso bill_length_mm / bill_depth_mm.

Em pandas, precisaríamos primeiro criar uma nova coluna com os valores de proporção:

penguins['length_to_depth'] = penguins['bill_length_mm'] / penguins['bill_depth_mm']
print(penguins['length_to_depth'].sort_values(ascending=False, ignore_index=True).head())

Resultado:

0    3.612676
1    3.510490
2    3.505882
3    3.492424
4    3.458599
Name: length_to_depth, dtype: float64

Conclusão

Para concluir, neste tutorial, exploramos porque e quando podemos combinar a funcionalidade do SQL para pandas para escrever código melhor e mais eficiente. Discussamos como configurar e usar a biblioteca pandasql para esse fim e quais limitações este pacote tem. Finalmente, consideramos numerosos exemplos populares de aplicação prática de pandasql e, em cada caso, comparamos o código com seu contrapartida em pandas.

Agora você tem tudo o que precisa para aplicar o SQL para pandas em projetos do mundo real. Um ótimo local para sua prática é o DataLab, o bloco de notas de dados com suporte a AI do DataCamp.

Source:
https://www.datacamp.com/tutorial/how-to-use-sql-in-pandas-using-pandasql-queries