top of page

PANDASQL em PYTHON para Ciência de Dados.

O PANDASQL é um pacote que permite manipular objetos data.frame de PYTHON através de argumentos SQL simples. Argumentos simples são uma instrução SQL, em que nomes de tabelas são nomes de objetos DataFrame (PANDAS). Este pacote permite, da mesma forma que PostgreSQL, Sqlite, MySQL, manipular um conjunto de dados, de forma simples e prática.




Antes de começar nosso tutorial devemos fazer download de nossa base de dados de música disponível no seguinte link: https://github.com/lerocha/chinook-database. Esta base de dados está composta por onze tabelas com suas respectivas relações. Para maiores informações consultei o seguinte link: https://archive.codeplex.com/?p=chinookdatabase.


A seguir nosso primeiro passo neste tutorial é instalar as livrarias necessarias neste tutorial: PANDASQL, PANDAS, PSYCOPG2, fazendo uso do comando pip install + livraria no interpretador de comandos de Windows CMD: pip install pandasql, pip install pandas, pip install psycopg2. Uma vez instalado as livrerias digitamos em nossa área de trabalho o comando (IDE - Python de preferência):


import psycopg2

import pandas as pd

from pandasql import sqldf


A primeira linha permite conectar com nossa base de dados Chinook alocada no programa de SQL - PostgreSQL. A segunda livraria será usada na transformação de dados tipo lista em DataFrame de Python. E a terceira linha nós permite manipular nossas tabelas através de comandos SQL em Python, de forma simples e prática.


Para o desenvolvimento do tutorial usaremos as tabelas artist e album, as quais estão relacionadas através do campo artistid. Antes de qualquer manipulação de dados devemos conhecer nossa base de dados, como ela é estruturada e organizada, de modo que podamos realizar sem problemas nossas devidas consultas (Query) para qualquer análise de interesse. Neste sentido, usaremos a palavra, frequentemente, usada de SQL a qual nós permite lê os dados: sendo esta palavra SELECT.


Para começar nosso tutorial devemos extrair as tabelas artist e album do banco de dados PostgreSQL. Isto, poderá ser realizado de forma simples por medio das linhas de comandos:


con = psycopg2.connect(user = "postgres", password = "postgres",

host = "localhost",

port = "5432",

database = "chinook")


Uma vez, realizada a conexão via comandos com o programa PostgreSQL de seu computador, criamos as chamadas SQL de nossas tabelas, como sigue:


cur = con.cursor()

sql = "SELECT * FROM artist;"

cur.execute(sql)

artist = pd.DataFrame(cur.fetchall(), columns = ['artistid', 'name'])


sql = "SELECT * FROM album;"

cur.execute(sql)

album = pd.DataFrame(cur.fetchall(), columns = ['albumid', 'title', 'artistid'])


Estas linhas de comandos nos permite criar um DataFrame em Python, com seus respetivos nomes (Figura 1).



Figura 1. Tabelas Artist e Album da Base de Dados Chinook.

A seguir palavra-chave é combinada com a cláusula LIMIT. Esta cláusula permite limitar o número de visualizações de nossos dados, no console de trabalho (IDE) PYTHON. Para começar digitalizamos:


print(sqldf("SELECT * FROM album LIMIT 10;", locals()))

print(sqldf("SELECT * FROM artist LIMIT 10;", locals()))

A saída destas duas linhas de comandos, permitem visualizar as primeiras 10 linhas da nossa tabela album e artist. Antes de continuar com o seguinte exemplo, veremos como a partir da função COUNT(*) podemos saber a quantidade exata de dados em cada tabela. Isto é alcançado digitalizando os seguintes comandos:


print(sqldf("SELECT COUNT(*) FROM album;", locals()))

print(sqldf("SELECT COUNT(*) FROM artist;", locals()))


Uma vez familiarizado com algumas cláusulas e consultas, realizadas de nossas tabelas, procederemos com aplicação da cláusula WHERE. Está cláusula é uma ferramenta importante que permite escolher quais linhas serão selecionadas numa declaração SELECT. Está cláusula é a mais simples e mais usada numa consulta, devido a que admite exatamente um único valor. Assim, consideremos o seguinte exemplo: Desejamos descobrir o artistid do artista com o nome de “Iron Maiden” usando a tabela artist, e uma vez você conheça o valor de artistid, este deverá ser usado para retornar o title e albumid, correspondente da tabela album. Isto é alcançado mediante o uso das seguintes linhas SQL:


print(sqldf("SELECT artistid FROM artist WHERE name = 'Iron Maiden' ", locals()))

print(sqldf("SELECT albumid, title FROM album WHERE artistid = 90; ", locals()))


Na primeira consulta (name = ‘Iron Maiden’) a função SQLDF retorna todas as linhas que combinem com o critério de busca, mas, neste caso, apenas uma única linha e todas suas colunas são retornadas. O resultado para o artista Iron Maiden podemos observar que seu artistid é igual a 90. Na segunda consulta (artistid = 90) na tabela album a função SQLDF retorna 21 linhas as quais combinem com o critério de busca. Além disso, nesta consulta são retornadas unicamente duas das três colunas da tabela: albumid e title.


O exemplo anterior pode ser realizado através de uma subconsulta mediante a palavra-chave SELECT. Uma subconsulta é uma consulta dentro de outra consulta, o qual é comumente realizado quando nosso banco de dados é relacionado. Desta forma, para o artista com o nome de “Iron Maiden” temos:


print( sqldf("SELECT albumid, title FROM album WHERE artistid IN (SELECT artistid FROM artist WHERE name = 'Iron Maiden') ", locals()) )



Figura 2. Albumid e Title para o artista Iron Maiden.

Como dito anteriormente nosso banco de dados é relacional, ou seja, podemos trabalhar com os diferentes relacionamentos entre as tabelas. Nosso caso, as duas tabelas (album e artist) são relacionadas através do campo chamado artistid. Assim, podemos realizar consultas combinadas. Para alcançar nosso objetivo neste tutorial usaremos unicamente a sintaxe INNER JOIN. Considere o seguinte exemplo:


print( sqldf("SELECT albumid, name, title FROM album INNER JOIN artist ON album.artistid = artist.artistid LIMIT 15;", locals()) )



Figura 3. INNER JOIN para ambas as tabelas (Album e Artist).

A saída mostra os registros selecionados que tem valores iguais em ambas as tabelas. No casso, as colunas retornadas são o albumid, o nome do artista e o título do álbum. O comado ON dentro da função SQLDF é usado com o alvo de instruir, mediante a estrutura SQL, ao Python qual é a coluna que possui o relacionamento entre as tabelas.


Para finalizar nosso tutorial suponha que você deseja conhecer para cada um dos artistas da tabela artist, o número total de álbuns para cada um. Para isto é necessário conversar um pouco sobre as cláusulas HAVING, ORDER BY e GROUP BY.


  1. A clausula HAVING: Esta cláusula permite adicionar um controle extra as agregações de linhas em uma operação de GROUP BY.

  2. A clausula GROUP BY: Esta declaração classifica nossos dados em grupos com o objetivo principal de agregar. Esta declaração é executada antes de cláusulas como WHERE, ORDER BY e funções.

  3. A clausula GROUP BY: Esta declaração classifica nossos dados em grupos com o objetivo principal de agregar. Esta declaração é executada antes de cláusulas como WHERE, ORDER BY e funções.


Assim, a partir da explicação anterior e mediante a digitação da seguinte linha de comando no console de trabalho de Python podemos obter para cada artista o número total de álbuns:


print( sqldf("SELECT name, COUNT(1) AS albuns FROM (SELECT name, title FROM album, artist WHERE album.artistid = artist.artistid) AS ntable GROUP BY name HAVING COUNT(1)>=1 ORDER BY COUNT(1) DESC LIMIT 15;", locals()



Figura 4. Número total de albuns por artista.

De esta forma terminamos nosso tutorial.

Comments


SGeoStat

© 2018 Por SGeoStat.

Orgulhosamente criado com Wix.com

bottom of page