Lo dividimos en 2 partes para facilitar la implementacion del codigo, ya que el ipynb anterior se estaba haciendo demasiado grande
import pandas as pd
import sqlite3
# Conectar a database de SQLite
con = sqlite3.connect('atida.db')
# Numero de categorias presentees en cada pedido
cat = pd.DataFrame()
query ='''
SELECT "Categorias Distintas", COUNT(*) Repeticiones
FROM
(
SELECT num_order, COUNT(DISTINCT Categoria) "Categorias Distintas"
FROM
(SELECT num_order, analytic_category Categoria
FROM items
INNER JOIN products ON products.product_id = items.product_id
WHERE Categoria IS NOT NULL)
GROUP BY num_order
)
GROUP BY "Categorias Distintas"
'''
cat = pd.read_sql_query(query, con)
# Mostrar el porcentaje de pedidos con producots de 1 sola categoria
print(100 * cat.iloc[0][1] / cat["Repeticiones"].sum())
cat.head(10)
# Veces que en un mismo pedido hay productos de varias categorias
# Por ejemplo, dada la categoria Higiene, cada vez que se compra un producto de Higiene,
# un x% de las veces se incluye al menos otro porducto de higiene, un y% de las veces un producto de
# Cosmetica y Belleza, un z% uno de Herbolario...
# Parte 1: Obtener listado de las categorias de cada pedido
categorias_pedido = pd.DataFrame()
query = '''
SELECT group_concat(Categoria) "Lista Categorias"
FROM
(SELECT num_order, analytic_category Categoria
FROM items
INNER JOIN products ON products.product_id = items.product_id
WHERE Categoria IS NOT NULL)
GROUP BY num_order
'''
categorias_pedido = pd.read_sql_query(query, con)
# Filtrar para eliminar los pedidos de un solo producto. Porque en ese caso no hay paquetizacion.
def longitud_lista(lista):
return len(lista.split(","))
# Nueva columna auxiliar
categorias_pedido["bool"] = categorias_pedido["Lista Categorias"].map(longitud_lista)
# Elimina las filas de un solo producto
categorias_pedido = categorias_pedido[categorias_pedido["bool"] != 1]
# Elimina la columna auxiliar
del categorias_pedido["bool"]
# Obtener, cada vez que un producto de estas categorias esta incluido, con que otras categorias se compra
# Matriz para guardar los resultados
resultados = pd.DataFrame({"Categoria Buscada": ["Cosmética y Belleza", "Higiene", "Infantil", "Herbolario", "Nutrición"],
"Cosmética y Belleza": [0, 0, 0, 0, 0],
"Higiene": [0, 0, 0, 0, 0],
"Infantil": [0, 0, 0, 0, 0],
"Herbolario": [0, 0, 0, 0, 0],
"Nutrición": [0, 0, 0, 0, 0],})
# Lista de categorias
lista_categorias = ["Cosmética y Belleza", "Higiene", "Infantil", "Herbolario", "Nutrición"]
# Loop sobre las categorias
for categoria in resultados["Categoria Buscada"]:
print(categoria)
# Loop sobre la lista de categorias de los pedidos
for listado in categorias_pedido["Lista Categorias"]:
# Hacer split para convertirlo a lista
listado = listado.split(",")
# Si ese pedido incluye la categoria que buscamos:
if categoria in listado:
# Eliminar la categoria una vez para que no se cuente a si misma si solo aparece una vez
listado.remove(categoria)
# Eliminar categorias repetidas, ya que solo contamos si estan o no, y no cuantas veces estan
listado = list(dict.fromkeys(listado))
# Contar cuantas otras categorias incluye y anotarlo en resultados
for cat in listado:
# Ignorar categorias que no sean de las 5 elegidas
if cat in lista_categorias:
resultados.loc[resultados["Categoria Buscada"] == categoria, cat] = resultados.loc[resultados["Categoria Buscada"] == categoria, cat] + 1
resultados.to_csv("paquetizacion_categorias.csv", sep="|")
# Numero total de clientes
query = '''
SELECT COUNT(DISTINCT customer_id)
FROM items
'''
cursor = con.cursor()
cursor.execute(query)
print("Numero total de clientes")
print(cursor.fetchall())
# Numero total de marcas
query = '''
SELECT COUNT(DISTINCT marca_value)
FROM items
INNER JOIN products ON items.product_id = products.product_id
'''
cursor = con.cursor()
cursor.execute(query)
print("Numero total de marcas")
print(cursor.fetchall())
# Numero total de productos
query = '''
SELECT COUNT(DISTINCT product_id)
FROM items
'''
cursor = con.cursor()
cursor.execute(query)
print("Numero total de productos")
print(cursor.fetchall())
# Entradas de datos disponibles por dia
# Crear dataframe para guardar datos
entradas_por_dia = pd.DataFrame()
query = '''
SELECT
strftime('%Y-%m-%d', created_at) Dia,
COUNT(*)
FROM items
GROUP BY Dia
ORDER BY Dia
'''
entradas_por_dia = pd.read_sql_query(query,con)
entradas_por_dia.to_csv("entradas_por_dia.csv", sep="|")
# N. de productos por categoria
# Crear dataframe para guardar datos
productos_por_categoria = pd.DataFrame()
query = '''
SELECT analytic_category, COUNT(DISTINCT items.product_id)
FROM items
INNER JOIN products ON items.product_id = products.product_id
WHERE analytic_category IS NOT NULL
GROUP BY analytic_category
'''
productos_por_categoria = pd.read_sql_query(query,con)
productos_por_categoria.to_csv("productos_por_categoria.csv", sep="|")
# Ingresos por mes
# Crear dataframe para guardar datos
ingresos_mes = pd.DataFrame()
query = '''
SELECT
strftime('%Y-%m', created_at) Mes,
SUM(price * qty_ordered * (1-discount)) Ingresos
FROM items
GROUP BY Mes
ORDER BY Mes
'''
ingresos_mes = pd.read_sql_query(query,con)
ingresos_mes.to_csv("insights_ingresos_mes.csv", sep="|", index=False)
# Numero total de clientes
query = '''
SELECT COUNT(DISTINCT customer_id)
FROM items
'''
cursor = con.cursor()
cursor.execute(query)
print("Numero total de clientes")
print(cursor.fetchall())
# Numero total de productos vendidos
query = '''
SELECT SUM(qty_ordered)
FROM items
'''
cursor = con.cursor()
cursor.execute(query)
print("Numero total de productos vendidos")
print(cursor.fetchall())
# Numero total de clientes 2017
query = '''
SELECT COUNT(DISTINCT customer_id)
FROM items
WHERE created_at < "2018"
'''
cursor = con.cursor()
cursor.execute(query)
print("Numero total de clientes")
print(cursor.fetchall())
# Pedidos que repiten marca
repiten_marca = pd.DataFrame()
query ='''
SELECT "Numero de marcas", COUNT(*)
FROM
(
SELECT num_order, COUNT(DISTINCT marca_value) "Numero de marcas"
FROM
(
SELECT num_order, marca_value
FROM
items INNER JOIN products ON items.product_id = products.product_id
WHERE marca_value IS NOT NULL
)
GROUP BY num_order
)
GROUP BY "Numero de marcas"
'''
repiten_marca = pd.read_sql_query(query, con)
repiten_marca.head()
repiten_marca.to_clipboard()
# Clientes que solo compraron una vez
repiten_marca = pd.DataFrame()
query ='''
SELECT Pedidos, COUNT(*) Clientes
FROM
(
SELECT customer_id Cliente, COUNT(DISTINCT num_order) Pedidos
FROM
(
SELECT customer_id, num_order
FROM items
)
GROUP BY Cliente
)
GROUP BY Pedidos
'''
repiten_marca = pd.read_sql_query(query, con)
repiten_marca.head()
# Ventas por cliente
ventas_por_cliente = pd.DataFrame()
query ='''
SELECT customer_id Cliente, SUM(Ingresos) Ingresos
FROM
(
SELECT customer_id, (price * qty_ordered * (1-discount)) Ingresos
FROM items
)
GROUP BY Cliente
ORDER BY Ingresos DESC
'''
ventas_por_cliente = pd.read_sql_query(query, con)
ventas_por_cliente.to_clipboard()
ventas_por_cliente.to_clipboard()
ventas_por_cliente.hist(column='Ingresos')