Como usar SQL no pandas Usando consultas pandasql

SQL, ou Structured Query Language (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 do Python projetada especificamente para manipulação e análise de dados.

Neste tutorial, discutiremos quando e como podemos (e quando não podemos) usar a funcionalidade SQL na estrutura do pandas. Além disso, daremos uma olhada em vários exemplos de implementação dessa abordagem e compararemos os resultados com o código equivalente em pandas puro.

Por que usar SQL no pandas?

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

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

Se precisar de mais treinamento sobre pandas, confira nosso curso Manipulação de dados com pandas e o tutorial Pandas: DataFrames em Python.

Para ver a legibilidade do SQL em ação, vamos supor que temos uma tabela (um dataframe) chamada penguins que contém várias informações sobre pinguins (e trabalharemos com essa tabela mais adiante neste tutorial). Para extrair todas as espécies exclusivas de pinguins que são machos e que têm nadadeiras maiores que 210 mm, precisaríamos do seguinte código no pandas:

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

Em vez disso, para obter as mesmas informações usando SQL, executaríamos o seguinte código:

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

O segundo trecho de código, escrito em SQL, parece quase uma frase natural em inglês e, portanto, é muito mais intuitivo. Podemos aumentar ainda mais sua legibilidade, distribuindo-a em 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 combinar tecnicamente os dois.

Como usar o pandasql

A biblioteca pandasql Python permite consultar os quadros de dados do pandas executando comandos SQL sem precisar se conectar a nenhum servidor SQL. Por trás disso, ele usa a sintaxe do SQLite, detecta automaticamente qualquer dataframe do pandas e o trata como uma tabela SQL comum.

Configuração de seu ambiente

Primeiro, precisamos instalar o pandasql:

pip install pandasql

Em seguida, importamos os pacotes necessários:

from pandasql import sqldf
import pandas as pd

Acima, importamos diretamente a função sqldf do pandasql, que é praticamente a única função significativa da biblioteca. Como o próprio nome sugere, ele é aplicado para consultar quadros de dados usando a sintaxe SQL. Além dessa função, o pandasql vem com dois conjuntos de dados internos simples que podem ser carregados usando as funções autoexplicativas load_births() e load_meat().

pandasql Syntax

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 cadeia de caracteres, 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 DQL (Data Query Language, linguagem de consulta de dados), que é um dos subconjuntos do 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, classificar, agrupar, juntar, agregar os dados e realizar operações matemáticas ou lógicas sobre eles.

Quando não podemos usar o pandasql

O pandasql não permite o uso de nenhum outro subconjunto de SQL além do 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.

Além disso, como essa biblioteca é baseada na sintaxe SQL, devemos tomar cuidado com as peculiaridades conhecidas do SQLite.

Exemplos de uso do pandasql

Agora, daremos uma olhada mais detalhada em como executar consultas SQL nos quadros de dados do pandas usando a função sqldf de pandasql. Para praticar com alguns dados, vamos carregar um dos conjuntos de dados integrados da biblioteca seaborn:penguins:

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

Saída:

   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 trilha de habilidades SQL Fundamentals é um bom ponto de referência.

Extração de dados com o pandasql

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

Saída:

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

Acima, extraímos informações sobre as espécies e a geografia dos primeiros cinco pingüins do quadro de dados penguins. Observe que a execução da função sqldf retorna um dataframe do pandas:

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

Saída:

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

Em pandas puros, seria:

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

Saída:

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

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

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

Saída:

     species
0     Adelie
1  Chinstrap
2     Gentoo

Em pandas, seria:

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

Saída:

['Adelie' 'Chinstrap' 'Gentoo']

Classificação de dados com o pandasql

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

Saída:

   body_mass_g
0       6300.0
1       6050.0
2       6000.0
3       6000.0
4       5950.0

Acima, classificamos nossos pinguins por massa corporal em ordem decrescente e exibimos os cinco principais valores de massa corporal.

Em pandas, seria:

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

Saída:

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

Filtragem de dados com o pandasql

Vamos tentar o mesmo exemplo que mencionamos no capítulo Por que usar SQL em pandas: extrair as espécies únicas de pinguins que são machos e que têm nadadeiras maiores que 210 mm:

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

Saída:

     species
0  Chinstrap
1     Gentoo

Acima, filtramos os dados com base em duas condições: sex = 'Male' e flipper_length_mm > 210.

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

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

Saída:

['Chinstrap' 'Gentoo']

Agrupamento e agregação de dados com o pandasql

Agora, vamos aplicar o agrupamento e a agregação de dados para encontrar o bico mais longo de cada espécie no quadro de dados:

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

Saída:

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

O mesmo código no pandas:

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

Saída:

     species  bill_length_mm
0     Adelie            46.0
1  Chinstrap            58.0
2     Gentoo            59.6

Realização de operações matemáticas com o pandasql

Com o pandasql, podemos realizar facilmente operações matemáticas ou lógicas nos dados. Vamos imaginar que queremos calcular a relação entre o comprimento do bico e a profundidade de cada pinguim e exibir os cinco principais valores dessa medida:

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

Saída:

   length_to_depth
0         3.612676
1         3.510490
2         3.505882
3         3.492424
4         3.458599

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

No pandas, precisaríamos primeiro criar uma nova coluna com os valores da 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())

Saída:

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 por que e quando podemos combinar a funcionalidade do SQL para pandas para escrever um código melhor e mais eficiente. Discutimos como configurar e usar a biblioteca pandasql para essa finalidade e quais são as limitações desse pacote. Por fim, consideramos vários exemplos populares da aplicação prática do pandasql e, em cada caso, comparamos o código com sua contraparte do pandas.

Agora você tem tudo o que precisa para aplicar o SQL para pandas em projetos reais. Um ótimo lugar para sua prática é o Datacamp Workspace, que é um ambiente flexível e poderoso para realizar análises de dados e compartilhar insights com seus colaboradores.