Tarjetas Black¶

La base de datos Tarjetas Black tiene el siguiente modelo de entidad relación:

png

Las tablas continenem la siguiente información:

  • La tabla consejero contiene detalles de la persona que ha relizado el gasto
  • La tabla movimiento almacena los detalles del cargo realizado con la tarjeta de crédito
  • La tabla comercio almacena datos del comercio donde se ha realizado el cargo
  • La tabla actividad contiene una clasificación de las distintas actividades a las que se puede dedicar un comercio
1
!gdown --id 15p6iQuRTY0AZJY1ZEjruC9KcQPZ642SZ 

/usr/local/lib/python3.8/dist-packages/gdown/cli.py:127: FutureWarning: Option `--id` was deprecated in version 4.3.1 and will be removed in 5.0. You don't need to pass it anymore to use a file ID.
  warnings.warn(
Downloading...
From: https://drive.google.com/uc?id=15p6iQuRTY0AZJY1ZEjruC9KcQPZ642SZ
To: /content/tajetasblack.db
100% 4.76M/4.76M [00:00<00:00, 125MB/s]

1
%load_ext sql

1
%sql sqlite:///tajetasblack.db

'Connected: @tajetasblack.db'
1
2
3
4
5
6
7
8
9
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('sqlite:///tajetasblack.db', echo=False)

df_actividad = pd.read_sql_table("actividad", engine)
df_consejero = pd.read_sql_table("consejero", engine)
df_comercio = pd.read_sql_table("comercio", engine)
df_movimiento = pd.read_sql_table("movimiento", engine)

Ejercicio 1¶

Obtener todas las actividades

1
2
3
4
%%sql 

SELECT *
FROM actividad;

Como ya tenemos guardado la tabla actividad en un dataframe y nos piden mostrar todo. Mostramos df_actividad.

1
df_actividad
id_actividad actividad
0 1 ROPA
1 2 HOTEL
2 3 RESTAURANTE
3 4 COCHE
4 5 COMPRA BIENES
5 6 TELECOM
6 7 MISC
7 8 AVION
8 9 REGALOS
9 10 SOCIAL
10 11 DIRECT MARKETING
11 12 LIBRERIA
12 13 SALIDAS
13 14 TREN
14 15 SUPERMERCADO
15 16 DEPORTE
16 17 VIAJE
17 18 HOGAR
18 19 BUSINESS
19 20 DUTYFREE
20 21 BANCO
21 22 IMAGEN
22 23 COMPRA BIENES LUJO
23 24 ESTETICA
24 25 SALUD
25 26 ESCUELA
26 27 MUEBLES
27 28 CASINO
28 29 BEBIDAS
29 30 BARCO
30 31 TABACO
31 32 CA$H
32 33 ARTE
33 34 SEGUROS
34 35 AGRICULTURA
35 36 FUNERARIAS

Ejercicio 2¶

Obtener 5 actividades.
Puede ser cualquier actividad

1
2
3
4
5
%%sql 

SELECT *
FROM actividad
LIMIT 5;

 * sqlite:///tajetasblack.db
Done.
id_actividad actividad
1 ROPA
2 HOTEL
3 RESTAURANTE
4 COCHE
5 COMPRA BIENES

El comando limit de sql es el metodo head(n) donde n es el número de filas que se imprimen.

1
df_actividad.head(5)
id_actividad actividad
0 1 ROPA
1 2 HOTEL
2 3 RESTAURANTE
3 4 COCHE
4 5 COMPRA BIENES

Ejercicio 3¶

Obtener 5 actividades ordenadas por el nombre de la actividad (ascendente)

1
2
3
4
5
6
%%sql 

SELECT *
FROM actividad
ORDER BY actividad 
LIMIT 5;

 * sqlite:///tajetasblack.db
Done.
id_actividad actividad
35 AGRICULTURA
33 ARTE
8 AVION
21 BANCO
30 BARCO

El metodo ORDER BY de sql en Pandas es mediante el metodo sort_values(col), por defecto se ordena de forma ascendente.

1
2
3
4
5
(
    df_actividad
                .sort_values("actividad")
                .head(5)
)
id_actividad actividad
34 35 AGRICULTURA
32 33 ARTE
7 8 AVION
20 21 BANCO
29 30 BARCO

Ejercicio 4¶

Obtener 5 comercios ordenados por su id (descendente)

1
2
3
4
5
6
%%sql 

SELECT comercio, actividad_completa
FROM comercio
ORDER BY id_comercio DESC
LIMIT 5;

 * sqlite:///tajetasblack.db
Done.
comercio actividad_completa
LAZCANO SUPERMERCADOS,ULTRAMARINOS, ECONOMATOS
RESTAURANTE OR DAGO RESTAURANTES RESTO
PORTO ALEGRE II RESTAURANTES RESTO
PC CITY S.SEBASTIAN REYES ELECTRODOMESTICOS,EQUIPOS ELECTRICOS
SIDRERIA RESTAURANTE GAZTELUPE RESTAURANTES RESTO

Refiriendome al anterior ejercicio para poder ordenar de manera ascendente hay que añadir un atributo en sort_values(column, ascending=False). Para seleccionar las columnas utilizamos filter(column). Es importante que primero se ordene el dataframe ya que si se filtra primero no tendremos la columna id_comercio.

1
2
3
4
5
6
7
(
    df_comercio
                .sort_values("id_comercio",ascending=False)
                .filter(["comercio","actividad_completa"])
                .head(5)
                
)
comercio actividad_completa
13807 LAZCANO SUPERMERCADOS,ULTRAMARINOS, ECONOMATOS
13806 RESTAURANTE OR DAGO RESTAURANTES RESTO
13805 PORTO ALEGRE II RESTAURANTES RESTO
13804 PC CITY S.SEBASTIAN REYES ELECTRODOMESTICOS,EQUIPOS ELECTRICOS
13803 SIDRERIA RESTAURANTE GAZTELUPE RESTAURANTES RESTO

Ejercicio 5¶

  • Sobre la tabla de movimientos, crea una nueva columna con el Iva (Importe * 21%)
  • 2 Decimales
  • Ordenado por fecha (descendente), hora (ascendente) y minuto (ascendente)
  • Sólo 5 registros
1
2
3
4
5
6
7
8
%%sql 

SELECT id_movimiento, 
       importe,
       round(importe * 0.21, 2) as "Iva"
FROM movimiento
ORDER BY fecha ASC, id_movimiento ASC
LIMIT 5;

 * sqlite:///tajetasblack.db
Done.
id_movimiento importe Iva
6293 134.49 28.24
11314 60.97 12.8
15693 22.7 4.77
15694 48.08 10.1
15695 2.84 0.6

Para añadir una columna utilizamos el metodo assign(nombre_column = function).

1
2
3
4
5
6
7
(
    df_movimiento
                  .sort_values(["fecha","id_movimiento"])
                  .filter(["id_movimiento","importe"])
                  .assign(Iva = lambda ds: round( ds.importe * 0.21, 2 ))
                  .head(5)
)
id_movimiento importe Iva
6292 6293 134.49 28.24
11313 11314 60.97 12.80
15692 15693 22.70 4.77
15693 15694 48.08 10.10
15694 15695 2.84 0.60

Ejercicio 6¶

  • Muestra el nombre del consejero junto con su cargo
  • Ordenado el resultado por la primera columna (descendente)
  • Sólo 5 registros
1
2
3
4
5
6
7
%%sql 

SELECT id_consejero, 
       nombre || ' es ' || funcion as "Nombre_Cargo"
FROM consejero
ORDER BY 1 DESC
LIMIT 5;

 * sqlite:///tajetasblack.db
Done.
id_consejero Nombre_Cargo
83 Domingo Navalmoral Sánchez es directivo
82 José Carlos Contreras Gómez es directivo
81 José María García Alonso es directivo
80 Rodrigo de Rato Figaredo es directivo
79 María Elena Gil García es directivo

Prácticamente como el ejercicio anterior, hacemos el filter despues de la creación de Nombre_Cargo dado a que necesitamos columnas que no mostramos. Dentro del lambda es python por lo que solo con hacer una concatenación de Strings es suficiente para la función.

1
2
3
4
5
6
7
(
    df_consejero
                .sort_values("id_consejero", ascending=False)
                .assign(Nombre_Cargo = lambda ds: ds.nombre + " es " + ds.funcion)
                .filter(["id_consejero","Nombre_Cargo"])
                .head(5)
)
id_consejero Nombre_Cargo
82 83 Domingo Navalmoral Sánchez es directivo
81 82 José Carlos Contreras Gómez es directivo
80 81 José María García Alonso es directivo
79 80 Rodrigo de Rato Figaredo es directivo
78 79 María Elena Gil García es directivo

Ejercicio 7¶

  • Muestra los conceales del Partido Popular
  • Ordena por el nombre del concejal (ascendente)
  • Solo 5 registros
1
2
3
4
5
6
7
8
%%sql 

SELECT id_consejero, nombre
FROM consejero
WHERE funcion = 'concejal'
    AND organizacion = 'Partido Popular'
ORDER BY nombre ASC
LIMIT 5;

 * sqlite:///tajetasblack.db
Done.
id_consejero nombre
1 Alberto Recarte García Andrade
5 Antonio Cámara Eguinoa
9 Beltrán Gutiérrez Moliner
10 Cándido Cerón Escudero
13 Estanislao Rodríguez-Ponga Salamanca

Para filtrar filas empleamos query(), no es necesario ascending=True ya que es el por defecto.

1
2
3
4
5
6
7
(
    df_consejero
                .query("funcion == 'concejal' and organizacion == 'Partido Popular'", engine="python")
                .filter(["id_consejero","nombre"])
                .sort_values("nombre")
                .head(5)
)
id_consejero nombre
0 1 Alberto Recarte García Andrade
4 5 Antonio Cámara Eguinoa
8 9 Beltrán Gutiérrez Moliner
9 10 Cándido Cerón Escudero
12 13 Estanislao Rodríguez-Ponga Salamanca

Ejercicio 8¶

  • Mostrar los movimientos cuyo importe es mayor a 1023 euros y menor a 1400
  • Ordenado por importe
  • Sólo 5 registros
1
2
3
4
5
6
7
%%sql 

SELECT *
FROM movimiento
WHERE importe between 1023 and 1400
ORDER BY importe 
LIMIT 5;

 * sqlite:///tajetasblack.db
Done.
id_movimiento fecha hora minuto importe id_consejero id_comercio
33910 2011-07-10 10 1 1024.16 38 7147
62166 2008-12-07 13 39 1024.65 65 12074
24810 2008-06-04 19 29 1025 27 5593
66222 2007-11-11 17 41 1028.05 69 1076
32979 2006-09-16 17 19 1030.59 38 736

El comando Between de SQL no esta implementado dentro de Pandas pero se puede realizar con una query normal.

1
2
3
4
5
6
(
    df_movimiento
                  .query("1023 <= importe <= 1400", engine="python")
                  .sort_values("importe")
                  .head(5)
)
id_movimiento fecha hora minuto importe id_consejero id_comercio
33909 33910 2011-07-10 10 1 1024.16 38 7147
62165 62166 2008-12-07 13 39 1024.65 65 12074
24809 24810 2008-06-04 19 29 1025.00 27 5593
66221 66222 2007-11-11 17 41 1028.05 69 1076
32978 32979 2006-09-16 17 19 1030.59 38 736

Ejercicio 9¶

  • Mostrar los consejeros cuyo id corresponde a los número 1, 2, 3, 5, 7 y 10
  • Ordena por Id (descendente)
1
2
3
4
5
6
%%sql 

SELECT *
FROM consejero
WHERE id_consejero in (1, 2, 3, 5, 7, 10)
order by id_consejero desc

 * sqlite:///tajetasblack.db
Done.
id_consejero nombre funcion organizacion
10 Cándido Cerón Escudero concejal Partido Popular
7 Antonio Romero Lázaro concejal PSOE
5 Antonio Cámara Eguinoa concejal Partido Popular
3 Ángel Eugenio Gómez del Pulgar Perales concejal PSOE
2 Alejandro Couceiro Ojeda concejal CEIM
1 Alberto Recarte García Andrade concejal Partido Popular 
1
2
3
4
5
(
    df_consejero
                .query("id_consejero in (1,2,3,5,7,10)", engine="python")
                .sort_values("id_consejero", ascending=False)
)
id_consejero nombre funcion organizacion
9 10 Cándido Cerón Escudero concejal Partido Popular
6 7 Antonio Romero Lázaro concejal PSOE
4 5 Antonio Cámara Eguinoa concejal Partido Popular
2 3 Ángel Eugenio Gómez del Pulgar Perales concejal PSOE
1 2 Alejandro Couceiro Ojeda concejal CEIM
0 1 Alberto Recarte García Andrade concejal Partido Popular

Ejercicio 10¶

Muestra los comercios con el identificador de actividad vacio

1
2
3
4
5
%%sql 

SELECT *
FROM comercio
WHERE id_actividad IS NULL

 * sqlite:///tajetasblack.db
Done.
id_comercio comercio actividad_completa id_actividad
4472 None None None
9412 BLANCO-SUMMER-CC.ZIELO BLANCO None
12725 SALA RETIRO CAJA DE MADRID None None
13277 MOVISTAR None None

No se puede hacer mediante comandos sql por lo que recurrimos a la busqueda por serie booleana.

1
df_comercio.query("id_actividad.isnull()",engine="python")
id_comercio comercio actividad_completa id_actividad
4471 4472 None None NaN
9411 9412 BLANCO-SUMMER-CC.ZIELO BLANCO NaN
12724 12725 SALA RETIRO CAJA DE MADRID None NaN
13276 13277 MOVISTAR None NaN

Ejercicio 11¶

  • Obtener comercios ordenados por su nombre en forma descendente
  • Que no tengan el nombre a nulo
  • Que no tengan un ID cuyo valor sea 650 o 6788
  • Sólo 5 registros

Pista: La ordenación distingue entre mayúsculas y minúsculas, por lo que a la hora de ordenar hazlo sobre una columna auxiliar donde estén todos los valores en mayúscula

1
2
3
4
5
6
7
8
%%sql 

SELECT id_comercio, comercio, actividad_completa
FROM comercio
WHERE comercio is not null
    and id_comercio not in (650, 6788)
ORDER BY comercio  COLLATE NOCASE DESC
LIMIT 5

 * sqlite:///tajetasblack.db
Done.
id_comercio comercio actividad_completa
12863 ZUBEROA JATEXEA RESTAURANTES DE 4 Y 5 TENEDORES
1352 ZORZAL RESTAURANTES RESTO
7322 ZOO AQUARIUM MADRID - TPV PC ESPECTACULOS Y DEPORTES
3567 ZOO AQUARIUM ESPECTACULOS Y DEPORTES
7066 ZONULA S.L. FOTOGRAFIA,EQUIPOS CINE,OPTICA,INST.MEDI

Al pedirnos todos los datos no nulso podemos utilizar el metodo de la serie notnull(). Y para ordenar hay que crear una columna de comercio de mayúsculas para poder ordenar bien. Y ascending=False.

1
2
3
4
5
6
7
8
(
    df_comercio
                .query("comercio.notnull() and id_comercio not in (650, 6788)", engine="python")
                .assign(comercio_Mayus = lambda ds: ds.comercio.str.upper())
                .sort_values("comercio_Mayus",ascending=False)
                .filter(["id_comercio","comercio","actividad_completa"])
                .head(5)
)
id_comercio comercio actividad_completa
12862 12863 ZUBEROA JATEXEA RESTAURANTES DE 4 Y 5 TENEDORES
1351 1352 ZORZAL RESTAURANTES RESTO
7321 7322 ZOO AQUARIUM MADRID - TPV PC ESPECTACULOS Y DEPORTES
3566 3567 ZOO AQUARIUM ESPECTACULOS Y DEPORTES
7065 7066 ZONULA S.L. FOTOGRAFIA,EQUIPOS CINE,OPTICA,INST.MEDI

Ejercicio 12¶

  • Mostrar los consejeros cuyo nombre empieza por A y su apellido por R
  • Ordenado por nombre (ascendente)
1
2
3
4
5
6
%%sql 

SELECT *
FROM consejero
WHERE nombre LIKE 'A% R%'
ORDER BY nombre

 * sqlite:///tajetasblack.db
Done.
id_consejero nombre funcion organizacion
1 Alberto Recarte García Andrade concejal Partido Popular
4 Angel Rizaldos González concejal Izquierda Unida
6 Antonio Rey de Viñas Sánchez-Majestad concejal CC OO
7 Antonio Romero Lázaro concejal PSOE

No funciona LIKE en el query pero podemos utilizar los metodos de str dentro del query y utilizar expresiones regulares para obtener los consejeros que empiezan por A y su apellido por R.

1
2
3
4
5
(
    df_consejero
                .query("nombre.str.contains(pat='^A+.*R')", engine="python") 
                .sort_values("nombre")
)
id_consejero nombre funcion organizacion
0 1 Alberto Recarte García Andrade concejal Partido Popular
3 4 Angel Rizaldos González concejal Izquierda Unida
5 6 Antonio Rey de Viñas Sánchez-Majestad concejal CC OO
6 7 Antonio Romero Lázaro concejal PSOE

Ejercicio 13¶

  • Muestra los consejeros cuyo ID sea menor o igual a 5
  • El consejero cuyo ID es 3 debe de salir 2 veces
  • Ordenado por ID (ascendente)

Pista: Duplica la tabla consejero para obtener el dato repetido

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
%%sql 

SELECT *
FROM consejero
WHERE id_consejero BETWEEN 1 AND 5
UNION ALL
SELECT *
FROM consejero
WHERE id_consejero = 3
ORDER BY id_consejero

 * sqlite:///tajetasblack.db
Done.
id_consejero nombre funcion organizacion
1 Alberto Recarte García Andrade concejal Partido Popular
2 Alejandro Couceiro Ojeda concejal CEIM
3 Ángel Eugenio Gómez del Pulgar Perales concejal PSOE
3 Ángel Eugenio Gómez del Pulgar Perales concejal PSOE
4 Angel Rizaldos González concejal Izquierda Unida
5 Antonio Cámara Eguinoa concejal Partido Popular

Creamos un duplicado con el query restrictivo y lo añadimos el dataframe entero y hacemos el otro query para obtener la tabla deseada.

1
2
3
4
5
6
7
df_clone = df_consejero.query("id_consejero == 3", engine="python")
df_clone = pd.concat([df_consejero,df_clone])
(
    df_clone
            .query("1 <= id_consejero <= 5", engine="python")
            .sort_values("id_consejero")
 )
id_consejero nombre funcion organizacion
0 1 Alberto Recarte García Andrade concejal Partido Popular
1 2 Alejandro Couceiro Ojeda concejal CEIM
2 3 Ángel Eugenio Gómez del Pulgar Perales concejal PSOE
2 3 Ángel Eugenio Gómez del Pulgar Perales concejal PSOE
3 4 Angel Rizaldos González concejal Izquierda Unida
4 5 Antonio Cámara Eguinoa concejal Partido Popular

Ejercicio 14¶

Muestra las distintas funciones que tienen los consejeros

1
2
3
4
%%sql 

SELECT DISTINCT funcion
FROM consejero

 * sqlite:///tajetasblack.db
Done.
funcion
concejal
directivo

Para quitar duplicados usamos drop_duplicates()

1
2
3
4
5
6
(
    df_consejero
                .filter(["funcion"])
                .drop_duplicates()
                
)
funcion
0 concejal
64 directivo

Ejercicio 15¶

Muestra el total de movimientos, el importe mínimo, máximo y medio (2 decimales), para todos los importes positivos

Resutado con Pandas:

No description has been provided for this image 
1
2
3
4
5
6
7
8
%%sql 

SELECT count(*) as "Total_Movimientos", 
    min(importe) as "Importe_Minimo",
    max(importe) as "Importe_Maximo",
    avg(importe) as "Importe_Medio"
FROM movimiento
WHERE importe > 0

 * sqlite:///tajetasblack.db
Done.
Total_Movimientos Importe_Minimo Importe_Maximo Importe_Medio
75567 0.05 16921.75 156.45903238185838

Filtramos los importes positivos, filtramos las columnas que queremos y agregamos las operaciones.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
(
    df_movimiento
                  .query("importe > 0", engine="python")
                  .filter(["id_movimiento","importe"])
                  .agg(
                       Total_Movimientos = ("id_movimiento", "count"),
                       Importe_Minimo = ("importe", "min"),
                       Importe_Maximo = ("importe", "max"),
                       Importe_Medio = ("importe", "mean")
                  )
                  .round({"importe": 2}) # Para solo redondear la columna importe
)
id_movimiento importe
Total_Movimientos 75567.0 NaN
Importe_Minimo NaN 0.05
Importe_Maximo NaN 16921.75
Importe_Medio NaN 156.46

Ejercicio 16¶

  • Obten los siguientes datos agrupados de la tabla de movimientos:
    • Fecha del primer movimiento
    • Fecha del último movimiento
    • Número de días que han pasado desde el último al primer movimento
    • Suma de Importes

Pista: Para obtener el número de días entre 2 fechas se tienen que restar las fechas

Resutado con Pandas:

No description has been provided for this image 
1
2
3
4
5
6
7
%%sql 

select min(fecha) as "Inicio",
    max(fecha) as "Fin",
    (JULIANDAY(max(fecha)) - JULIANDAY(min(fecha))) as "Num_Dias",
    sum(importe) as "Sum_Importe"
FROM movimiento

 * sqlite:///tajetasblack.db
Done.
Inicio Fin Num_Dias Sum_Importe
2002-12-31 2012-08-28 3528.0 11659204.609999878

Filtramos por fecha e importe y agregamos Inicio que el la fecha más pequeña, Fin que es la fecha más grande, Num_dias es la diferencia y Sum_importe es la suma de importe.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
(
    df_movimiento
                  .filter(["fecha", "importe"])
                  .agg(
                      Inicio = ("fecha", "min"),
                      Fin = ("fecha", "max"),
                      Num_dias = ("fecha", lambda s: s.max() - s.min()),
                      Sum_importe = ("importe", "sum")
                  )
)
fecha importe
Inicio 2002-12-31 00:00:00 NaN
Fin 2012-08-28 00:00:00 NaN
Num_dias 3528 days 00:00:00 NaN
Sum_importe NaN 11659204.61

Ejercicio 17¶

Muestra las 5 horas de día donde se gasta más dinero

1
2
3
4
5
6
7
%%sql 

select hora, round(sum(importe), 2) as "Importe"
from movimiento
group by hora
order by 2 desc
limit 5

 * sqlite:///tajetasblack.db
Done.
hora Importe
15 2256615.99
16 1951272.07
17 902056.68
14 897004.71
19 636204.99

Para hacer la consulta agrupamos por hora y sumamos todo el importe. ordenamos descendiente y se cogen los 5 primeros.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
(
    df_movimiento
                  .groupby("hora")
                  .agg(
                      Importe = ("importe", "sum")
                  )
                  .sort_values("Importe", ascending=False)
                  .head(5)
                  
)
Importe
hora
15 2256615.99
16 1951272.07
17 902056.68
14 897004.71
19 636204.99

Ejercicio 18.¶

  • Muestra las 5 organizaciones con mas miembros
  • No tengas en cuenta valoles nulos
1
2
3
4
5
6
7
8
%%sql 

select organizacion, count(*) as "Numero_miembros"
from consejero
where organizacion is not null
group by organizacion
order by 2 desc
limit 5

 * sqlite:///tajetasblack.db
Done.
organizacion Numero_miembros
Partido Popular 27
PSOE 15
CC OO 6
Izquierda Unida 5
UGT 4

Primero obtenemos los valores no nulos de organizacion, los agrupamos por organización y añadimos Numero_miembros que cuenta el Numero de filas con la misma organizacion. Se ordena y se muestra

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
(
    df_consejero
                .query("organizacion.notnull()",engine="python")
                .groupby("organizacion")
                .agg(
                    Numero_miembros = ("organizacion","size")
                )
                .sort_values("Numero_miembros",ascending=False)
                .head(5)
)
Numero_miembros
organizacion
Partido Popular 27
PSOE 15
CC OO 6
Izquierda Unida 5
UGT 4

Ejercicio 19¶

Muestra los comercios con más de 1 registro por actividad, ordenando el resultado por e número de actividades (descendente) y el nombre del comercio (ascendente)

1
2
3
4
5
6
7
8
9
%%sql

select comercio, id_actividad, count(*) as "Numero_actividades"
from comercio
where comercio IS NOT NULL
group by comercio, id_actividad
having count(*) > 1
order by 3 desc, 1 ASC
limit 5

 * sqlite:///tajetasblack.db
Done.
comercio id_actividad Numero_actividades
DATAFONOS PARKING P4 4 4
EMBASSY 3 4
LA VACA ARGENTINA 3 4
TICKETMASTER VENTA ENTRAD 13 4
APARCAMIENTO CLINICA LA CONCEP 4 3

para poder contar cuantas actividades tiene cada comercio hay que sumar cuantos filas del mismo comercio y para ordenar es el sort_values en modo de lista.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
(
    df_comercio
              .query("comercio.notnull()",engine="python")
              .groupby(["comercio","id_actividad"])
              .agg(
                  Numero_actividades = ("comercio", "size")
              )
              .sort_values(["Numero_actividades","comercio"],ascending =[False,True])
              .head(5)
)
Numero_actividades
comercio id_actividad
DATAFONOS PARKING P4 4.0 4
EMBASSY 3.0 4
LA VACA ARGENTINA 3.0 4
TICKETMASTER VENTA ENTRAD 13.0 4
APARCAMIENTO CLINICA LA CONCEP 4.0 3

Ejercicio 20¶

Muestra los 5 movimientos que tengan mayor importe

1
2
3
4
5
6
7
8
%%sql 

select c.nombre, m.fecha, m.importe
from movimiento as m
    INNER JOIN consejero as c
    ON (m.id_consejero = c.id_consejero)
order by m.importe DESC
limit 5

 * sqlite:///tajetasblack.db
Done.
nombre fecha importe
Ildefonso José Sánchez Barcoj 2009-12-30 16921.75
Matías Amat Roca 2006-12-26 15000
Ramón Ferraz Ricarte 2007-12-19 13549
Miguel Blesa de la Parra 2006-07-19 13148.29
Miguel Blesa de la Parra 2006-04-04 12597.26

Empleamos merge(df, how, on) para hacer los join luego es mostrar por pantalla lo pedido.

1
2
3
4
5
6
7
(
    df_movimiento
                .merge(df_consejero,how= "inner", on="id_consejero")
                .filter(["nombre","fecha","importe"])
                .sort_values("importe",ascending=False)
                .head(5)         
)
nombre fecha importe
62488 Ildefonso José Sánchez Barcoj 2009-12-30 16921.75
66104 Matías Amat Roca 2006-12-26 15000.00
65386 Ramón Ferraz Ricarte 2007-12-19 13549.00
64137 Miguel Blesa de la Parra 2006-07-19 13148.29
64056 Miguel Blesa de la Parra 2006-04-04 12597.26

Ejercicio 21¶

Muestra los comercios que no tienen asociada una actividad

1
2
3
4
5
6
7
8
%%sql 

select c.*, a.actividad
from comercio as c
    LEFT JOIN actividad as a
    ON (c.id_actividad = a.id_actividad)
where a.id_actividad IS NULL
order by id_comercio ASC

 * sqlite:///tajetasblack.db
Done.
id_comercio comercio actividad_completa id_actividad actividad
4472 None None None None
9412 BLANCO-SUMMER-CC.ZIELO BLANCO None None
12725 SALA RETIRO CAJA DE MADRID None None None
13277 MOVISTAR None None None

Como nos piden los comercios que no tengan asociadas ninguna actividad tendremos que hacer un join del comercio entero para obtener valores nulos luego solo debemos mostrar los nulos por pantalla.

1
2
3
4
5
6
(
    df_comercio
              .merge(df_actividad, how="left", on="id_actividad")
              .query("id_actividad.isnull()",engine="python")
              .sort_values("id_comercio")
)
id_comercio comercio actividad_completa id_actividad actividad
4471 4472 None None NaN NaN
9411 9412 BLANCO-SUMMER-CC.ZIELO BLANCO NaN NaN
12724 12725 SALA RETIRO CAJA DE MADRID None NaN NaN
13276 13277 MOVISTAR None NaN NaN

Ejercicio 22¶

Muestra el importe agregado por consejero y actividad

1
2
3
4
5
6
7
8
9
%%sql

select c.nombre, a.actividad, sum(importe) as Sum_Importe
from movimiento as m INNER JOIN consejero as c ON (m.id_consejero = c.id_consejero)
     INNER JOIN comercio as comer ON (m.id_comercio = comer.id_comercio)
     LEFT JOIN actividad as a ON (comer.id_actividad = a.id_actividad)
group by 1, 2
order by 3 DESC
limit 5

 * sqlite:///tajetasblack.db
Done.
nombre actividad Sum_Importe
Ricardo Morado Iglesias BANCO 398902
José Antonio Moral Santín CA$H 367809.82
Ildefonso José Sánchez Barcoj BANCO 219123.41999999998
Ramón Ferraz Ricarte CA$H 172817.0400000001
Ildefonso José Sánchez Barcoj COMPRA BIENES 154770.60000000024

Podemos hacerlo mediante innerjoins todos menos actividad que se debe hacer mediante un left. Agrupamos por nombre y actividad y creamos nueva columna que es la suma de los importes. Por último se ordenan por Sum_Importe de manera descendente.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
(
    df_movimiento 
                  .merge(df_consejero,how="inner",on="id_consejero")
                  .merge(df_comercio, how="inner", on="id_comercio")
                  .merge(df_actividad, how="left", on="id_actividad")
                  .groupby(["nombre","actividad"]).agg(
                      Sum_Importe = ("importe", "sum")
                  )
                  .sort_values("Sum_Importe", ascending=False)
                  .head(5)
)
Sum_Importe
nombre actividad
Ricardo Morado Iglesias BANCO 398902.00
José Antonio Moral Santín CA$H 367809.82
Ildefonso José Sánchez Barcoj BANCO 219123.42
Ramón Ferraz Ricarte CA$H 172817.04
Ildefonso José Sánchez Barcoj COMPRA BIENES 154770.60

Ejercicio 23¶

Muestra la media de los importes medios mensuales

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
%%sql

with subquery as (
    select strftime('%m', fecha) as mes,  
           avg(importe) as importe
    from movimiento
    group by 1
)
select round(avg(importe), 2) as "Media importes mensuales"
from subquery

 * sqlite:///tajetasblack.db
Done.
Media importes mensuales
152.51

Primero tenemos que utilizar DatetimeIndex para obtener el mes para agruparlo y hacer la media de cada mes por último hacemos la media de esa columna y la redondeamos a 2.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
(
    df_movimiento
                .assign(
                    mes = lambda df: pd.DatetimeIndex(df["fecha"]).month
                )
                .groupby("mes").agg(
                    avg_importe = ("importe","mean"),
                   
                )
                .avg_importe.mean().round(2)
)

152.51

Ejercicio 24¶

Muestra los 5 importes mas altos que están debajo de la media ordenados por importe (descendente) y fecha

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
%%sql 

select c.nombre, m.fecha, m.importe
from movimiento as m
    INNER JOIN consejero as c ON (m.id_consejero = c.id_consejero)
where importe < (
    select avg(importe)
    from movimiento)
order by m.importe DESC, m.fecha
limit 5

 * sqlite:///tajetasblack.db
Done.
nombre fecha importe
Rubén Cruz Orive 2004-11-04 152.91
Carmen Contreras Gómez 2006-03-31 152.91
Rubén Cruz Orive 2008-05-01 152.91
Carmen Contreras Gómez 2003-04-08 152.89
Carmen Contreras Gómez 2003-07-13 152.89

Para poder hacer el query es necesario conocer el valor de la media del importe por lo que creamos una nueva columna con ese valor y procedemos ha hacer las indicaciones del ejercicio empleando todo lo utilizado anteriormente.

1
2
3
4
5
6
7
8
9
(
    df_movimiento
                .merge(df_consejero, how="inner", on="id_consejero")
                .assign(avg_importe = lambda ds: ds.importe.mean())
                .query("importe < avg_importe",engine="python")
                .sort_values(["importe","fecha"],ascending=[False,True])
                .filter(["nombre","fecha","importe"])
                .head(5)
)
nombre fecha importe
54999 Rubén Cruz Orive 2004-11-04 152.91
69737 Carmen Contreras Gómez 2006-03-31 152.91
56264 Rubén Cruz Orive 2008-05-01 152.91
69310 Carmen Contreras Gómez 2003-04-08 152.89
69386 Carmen Contreras Gómez 2003-07-13 152.89

Ejercicio 25¶

  • Muestra los 5 primeros consejeros con un mayor importe medio
1
2
3
4
5
6
7
8
9
%%sql 

select nombre as "Consejero", 
    (select avg(importe)
     from movimiento as m
     where m.id_consejero = c.id_consejero) as "Importe_Medio"
from consejero as c
order by 2 desc
limit 5

 * sqlite:///tajetasblack.db
Done.
Consejero Importe_Medio
Ricardo Morado Iglesias 1675.4673962264155
Gerardo Díaz Ferrán 1357.8145161290322
Enrique de la Torre Martínez 820.7129347826086
Ramón Ferraz Ricarte 477.60496909765124
Matías Amat Roca 423.5705408163269

Para realizar este ejercicio es requerido hacer un inner join de movimientos y consejero agrupar por nombre, hallar la media del importe y ordenar de manera descendente

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
(
    df_consejero
                .merge(df_movimiento, how="inner", on="id_consejero")
                .groupby("nombre").agg(
                    Importe_Medio = ("importe", "mean")
                )
                .rename({"nombre" : "Consejero"})
                .filter(["Consejero","Importe_Medio"])
                .sort_values("Importe_Medio",ascending=False)
                .head(5)
)
Importe_Medio
nombre
Ricardo Morado Iglesias 1675.467396
Gerardo Díaz Ferrán 1357.814516
Enrique de la Torre Martínez 820.712935
Ramón Ferraz Ricarte 477.604969
Matías Amat Roca 423.570541 
1