SQL vs Pandas - Agrupamento e frequências

Neste artigo vamos ver como calcular a frequência absoluta e relativa de um conjunto de registros comparando como isso pode serfeito no SQL e no Pandas. Vamos ver uma tabela de vendas com dados de um determinado periodo e faremos o seu agrupamento por período para poder fazer estes cálculos.

Primeiro vamos criar as tabelas e popular os dados. Vamos começar pelo SQL

MySQL

CREATE TABLE vendas (

id int NOT NULL AUTO_INCREMENT,

mes int,

produto int,

quantidade int,

PRIMARY KEY (id)

);

SQL SERVER

CREATE TABLE vendas (

id int IDENTITY(1,1) PRIMARY KEY,

mes int,

produto int,

quantidade int

);

Depois de criar as tabelas, vamos inserir os dados que serão utilizados

INSERT INTO vendas (mes, produto, quantidade) VALUES (1,1,10);

INSERT INTO vendas (mes, produto, quantidade) VALUES (1,2,20);

INSERT INTO vendas (mes, produto, quantidade) VALUES (1,2,30);

INSERT INTO vendas (mes, produto, quantidade) VALUES (2,1,12);

INSERT INTO vendas (mes, produto, quantidade) VALUES (2,2,26);

INSERT INTO vendas (mes, produto, quantidade) VALUES (2,2,34);

INSERT INTO vendas (mes, produto, quantidade) VALUES (3,1,13);

INSERT INTO vendas (mes, produto, quantidade) VALUES (3,2,21);

INSERT INTO vendas (mes, produto, quantidade) VALUES (3,2,36);

INSERT INTO vendas (mes, produto, quantidade) VALUES (4,1,14);

INSERT INTO vendas (mes, produto, quantidade) VALUES (4,2,27);

INSERT INTO vendas (mes, produto, quantidade) VALUES (4,2,32);

INSERT INTO vendas (mes, produto, quantidade) VALUES (5,1,12);

INSERT INTO vendas (mes, produto, quantidade) VALUES (5,2,23);

INSERT INTO vendas (mes, produto, quantidade) VALUES (5,2,35);

INSERT INTO vendas (mes, produto, quantidade) VALUES (6,1,14);

INSERT INTO vendas (mes, produto, quantidade) VALUES (6,2,26);

INSERT INTO vendas (mes, produto, quantidade) VALUES (6,2,38);

INSERT INTO vendas (mes, produto, quantidade) VALUES (7,1,12);

INSERT INTO vendas (mes, produto, quantidade) VALUES (7,2,27);

INSERT INTO vendas (mes, produto, quantidade) VALUES (7,2,35);

INSERT INTO vendas (mes, produto, quantidade) VALUES (8,1,14);

INSERT INTO vendas (mes, produto, quantidade) VALUES (8,2,28);

INSERT INTO vendas (mes, produto, quantidade) VALUES (8,2,32);

INSERT INTO vendas (mes, produto, quantidade) VALUES (9,1,11);

INSERT INTO vendas (mes, produto, quantidade) VALUES (9,2,23);

INSERT INTO vendas (mes, produto, quantidade) VALUES (9,2,35);

INSERT INTO vendas (mes, produto, quantidade) VALUES (10,1,11);

INSERT INTO vendas (mes, produto, quantidade) VALUES (10,2,21);

INSERT INTO vendas (mes, produto, quantidade) VALUES (10,2,31);

INSERT INTO vendas (mes, produto, quantidade) VALUES (11,1,9);

INSERT INTO vendas (mes, produto, quantidade) VALUES (11,2,17);

INSERT INTO vendas (mes, produto, quantidade) VALUES (11,2,25);

INSERT INTO vendas (mes, produto, quantidade) VALUES (12,1,8);

INSERT INTO vendas (mes, produto, quantidade) VALUES (12,2,16);

INSERT INTO vendas (mes, produto, quantidade) VALUES (12,2,27);

Faremos a mesma operação no Pandas

import numpy as np

import pandas as pd

dados = np.array([[1,1,10], [1,2,20], [1,2,30], [2,1,12], [2,2,26], [2,2,34], [3,1,13], [3,2,21], [3,2,36], [4,1,14], [4,2,27], [4,2,32], [5,1,12], [5,2,23], [5,2,35], [6,1,14], [6,2,26], [6,2,38], [7,1,12], [7,2,27], [7,2,35], [8,1,14], [8,2,28], [8,2,32], [9,1,11], [9,2,23], [9,2,35], [10,1,11], [10,2,21], [10,2,31], [11,1,9], [11,2,17], [11,2,25], [12,1,8], [12,2,16], [12,2,27]])

colunas = ["mes", "produto", "quantidade"]

dataframe = pd.DataFrame(data=dados, columns=colunas)

dataframe.sort_values(by=["mes"], ascending=True)

print(dataframe)

Nossa primeira tarefa vai ser agrupar as quantidades por mês. No SQL isso é posível agrupando os dados pela coluna mes e utilizando uma função de agrupamento para o campo quantidade que vai fazer a soma das quantidades do período. A soma do período foi obtida utilizando a função SUM().

SELECT mes, sum(quantidade) as total FROM vendas GROUP BY mes ORDER BY mes

No Pandas faremos exatamente a mesma coisa, um agrupamento por mês e depois a soma da coluna quantidade. Faremos uma cópia do dataframe original para fazer o agrupamento.

dataframe2 = dataframe.groupby(["mes"],as_index=False)["quantidade"].sum()

dataframe2.columns = ["mes", "total"]

print(dataframe2)

A seguir faremos o cálculo da frequência relativa em porcentagem, vamos calcular quando vale cada período em percentual em relação ao todo. No SQL isso pode ser obtido dividindo a soma do periodo pela soma de todos os periodos, a soma de todos os periodos pode ser obtida através de um subselect

MySQL

SELECT mes, sum(quantidade) as total, (SUM(quantidade) / (SELECT SUM(quantidade) FROM vendas))*100 as percentual FROM vendas GROUP BY mes ORDER BY mes

SQL Server

SELECT mes, sum(quantidade) as total, (SELECT SUM(quantidade) FROM vendas) as x,

(CAST(SUM(quantidade) as float) / CAST((SELECT SUM(quantidade) FROM vendas) as float))*100 as percentual FROM vendas GROUP BY mes ORDER BY mes

Perceba que no caso do SQL Server foi feito um passo a mais em relação a consulta feita no MySQL. Como estamos fazendo a divisão de inteiros, tivemos que fazer a conversão de cada campo usado na divisão para float, para isso utilizamos a função CAST.

No Pandas a operação e um pouco mais simples. Podemos criar uma coluna nova no dataframe onde fizemos o agrupamento e dividir o total calculado pela soma de todas as quantidades, para isso podemos usar o método sum() no campo da quantidade

dataframe2["percentual"] = (dataframe2["total"] / dataframe2["total"].sum())*100

print(dataframe2)

Para encerrar, vamos fazer a soma acumulada em cada período, cada mes vai ser a soma dele mesmo com os valores dos meses anteriores. No SQL isso pode ser obtido mais uma vez utilizando um subselect onde somamos todas as quantidades iguais ou menores que o mes atual.

MySQL

SELECT V.mes, sum(V.quantidade) as total, (SUM(V.quantidade) / (SELECT SUM(quantidade) FROM vendas))*100 as percentual, (SELECT SUM(quantidade) FROM vendas WHERE mes <= V.mes) as acumulado FROM vendas as V GROUP BY V.mes ORDER BY V.mes

SQL SERVER

SELECT V.mes, sum(V.quantidade) as total,

(CAST(SUM(V.quantidade) as float) / CAST((SELECT SUM(quantidade) FROM vendas) as float))*100 as percentual,(SELECT SUM(quantidade) FROM vendas WHERE mes <= V.mes) as acumulado FROM vendas as V GROUP BY V.mes ORDER BY V.mes

No Pandas essa operação pode ser feita utilizado o método cumsum() no campo da quantidade. Para isso basta criar uma nova coluna no dataframe usado para o agrupamento e aplicar o método cumsum() na coluna total.

dataframe2["acumulado"] = dataframe2["total"].cumsum()