Formatear datos recuperados¶

Empleamos cláusulas de SQL para formatear y editar el resultado de las consultas. También exportamos los resultados de las consultas formateadas a un archivo de texto para que pueda analizarlos en otros paquetes de software como Tableau o Excel. Usamos AS, DISTINCT y ORDER BY.

%load_ext sql

%sql mysql://studentuser:studentpw@localhost/dognitiondb

Se emplea AS para cambiar los títulos de las columnas en una salida, permite asignar un alias (un nombre temporal) a una tabla o una columna de una tabla y aumentar la legibilidad de las consultas. Necesitamos cambiar el nombre del campo de marca de tiempo de la tabla completed_tests de "created_at" a "time_stamp":

%%sql
SELECT dog_guid, created_at AS time_stamp
FROM complete_tests AS tests
LIMIT 4;

Necesitamos cambiar el título del campo "start_time" en la tabla exam_answers a "exam start time" en el resultado de la consulta. Tener en cuenta que un alias que incluye un espacio, debe estar entre comillas y MySQL admite comillas simples y dobles:

%%sql
SELECT start_time AS "exam start time"
FROM exam_answers
LIMIT 4;

Se usa la cláusula DISTINCT para eliminar filas duplicadas, dado que esta base de datos no se declararon claves principales en en las tablas, y a veces se pueden ingresar filas duplicadas completas por error. Supongamos que nos interesa saber cuáles son los diferentes valores posibles en la columna, necesitamos la lista de todas las razas de perros de la base de datos:

Cabe destacar que si se usa la cláusula DISTINCT en una columna que tiene valores NULL, MySQL incluirá un sólo valor NULL en la salida DISTINCT de esa columna.

%%sql 
SELECT DISTINCT breed
FROM dogs;

Cuando la cláusula DISTINCT se usa con varias columnas en una declaración SELECT, la combinación de todas las columnas juntas se usa para determinar la unicidad de una fila en un conjunto de resultados. Por ejemplo, si quisieramos conocer todas las combinaciones posibles de estados y ciudades en la tabla de usuarios. Observamos que, obtenemos muchas filas con California (CA) en la columna del estado y tres filas con Gainesville en la columna de la ciudad (Georgia, Florida y Virginia tienen ciudades llamadas Gainesville en nuestra tabla de usuarios), pero no hay dos filas que tengan la misma combinación de estado y ciudad.

%%sql
SELECT DISTINCT state, city
FROM users;

%%sql
SELECT DISTINCT state, city
FROM users
WHERE state = "CA" AND city = "Gainesville";

%%sql
SELECT DISTINCT state, city
FROM users
WHERE state = "CA";

%%sql
SELECT DISTINCT state, city
FROM users
WHERE city = "Gainesville";

Observación: Cuando usa la cláusula DISTINCT con la cláusula LIMIT, MySQL deja de buscar cuando encuentra el número de filas únicas especificadas en la cláusula LIMIT, no cuando pasa por el número de filas en la cláusula LIMIT. Por ejemplo, recuperamos los estados de la base y limitamos la salida a las 8 primeras filas´usando SELECT y LIMIT. Podemos ver que sólo se repite un estado, es decir, tenemos 7 valores únicos en las 8 primeras filas. Después, agregamos la cláusula DISTINCT y mantemos limitada la salida en 8. Vemos que la búsqueda de valores distintos no se detiene en las primeras 8 filas sino cuando encuentra los primeros 8 valores únicos.

%%sql
SELECT state
FROM users
LIMIT 8;

%%sql
SELECT DISTINCT state
FROM users
LIMIT 8;

El uso de la poderosa cláusula ORDER BY para permitirle ordenar la salida de acuerdo con nuestras propias especificaciones y de esta manera, clasificar los resultados de las salidas. Por ejemplo, deseamos que las razas de perros estén ordenadas alfabéticamente. Por defecto, el orden es un forma ascendente, pero podemos indicarle de qué forma necesitamos:

%%sql
SELECT DISTINCT breed
FROM dogs 
ORDER BY breed;

%%sql
SELECT DISTINCT breed
FROM dogs
ORDER BY breed DESC;

 * mysql://studentuser:***@localhost/dognitiondb
2006 rows affected.

Combinar ORDER BY con LIMIT nos permite de una manera fácil de seleccionar los "10 primeros" y los "últimos 10" en una lista o columna. Por ejemplo: quereemos seleccionar los ID de 3 ID de usuarios que pasaron la mayor cantidad de tiempo en sus pruebas.

%%sql
SELECT DISTINCT user_guid, median_ITI_minutes
FROM dogs 
ORDER BY median_ITI_minutes DESC
LIMIT 3;

 * mysql://studentuser:***@localhost/dognitiondb
3 rows affected.

También puede ordenar su salida en función de un campo derivado. Si quisiera que su intervalo entre pruebas se expresara en segundos en lugar de minutos, podría incorporar una columna derivada y un alias en su última consulta para obtener los 3 pares de perros y clientes que pasaron la mayor cantidad de tiempo promedio entre sus pruebas Dognition en segundos:

%%sql
SELECT DISTINCT user_guid, (median_ITI_minutes*60) AS median_ITI_sec
FROM dogs 
ORDER BY median_ITI_sec DESC
LIMIT 3;

 * mysql://studentuser:***@localhost/dognitiondb
3 rows affected.

Si desea seleccionar todos los ID de usuario distintos de los clientes en los Estados Unidos (abreviado "US") y ordenarlos según los estados en los que viven en orden alfabético primero y el tipo de membresía en segundo lugar, podría consultar. Como hay valores nulos en las columnas estado y tipo de membresía, escribimos la consulta para excluirlos del resultado:

%%sql
SELECT DISTINCT user_guid, state, membership_type
FROM users
WHERE country="US" AND state IS NOT NULL and membership_type IS NOT NULL
ORDER BY state ASC, membership_type ASC
LIMIT 6;

 * mysql://studentuser:***@localhost/dognitiondb
6 rows affected.

Hay dos formas de exportar los resultados de su consulta utilizando nuestra interfaz Jupyter. Con un copy and paste, aunque esta estrategia es muy simple, solo funciona si su salida tiene un tamaño muy limitado. También es posible, pedirle a MySQL, colocar los resultados de una consulta en una variable como un lugar de almacenamiento temporal y luego usar el código de Python para formatear los datos en la variable como un archivo CSV que se puede descargar. De esta forma, todos los resultados de una consulta se guardarán en la variable, no solo las primeras 1000 filas como se muestra en Jupyter, incluso si hemos configurado Jupyter para mostrar solo 1000 filas de la salida. El enlace para descargar, se ve en la línea de salida, también, puede acceder al archivo desde la página de inicio de Jupyter, donde puede realizar cualquier acción como un notebook más. Por ejemplo, queremos exportar una lista con las razas registradas en la base de datos, ordenadas de forma descendente.

breed_list = %sql SELECT DISTINCT breed FROM dogs ORDER BY breed;

breed_list.csv('breed_list.csv')

Como se puede observar en el archivo, los datos de la columna raza necesitan una limpieza. Usamos la función REPLACE para eliminar los guiones de razas, pero también se eliminarán los guiones intermedios como el de French Bulldog-Boston. De manera que si queremos mantener estos tipos de guíones, podemos usar la función TRIM.

SELECT DISTINCT breed,
REPLACE(breed,'-','') AS breed_fixed
FROM dogs
ORDER BY breed_fixed;

%%sql
SELECT DISTINCT breed, TRIM(LEADING '-' FROM breed) AS breed_fixed
FROM dogs
ORDER BY breed_fixed;

user_guid	median_ITI_minutes
ce7a74c8-7144-11e5-ba71-058fbc01cf0b	9964.75
ce7b0686-7144-11e5-ba71-058fbc01cf0b	9934.6000001
ce3c4a40-7144-11e5-ba71-058fbc01cf0b	9912.3499999

user_guid	median_ITI_sec
ce33102e-7144-11e5-ba71-058fbc01cf0b	56242621.0002
ce71fdde-7144-11e5-ba71-058fbc01cf0b	28501473.0
ce2462fe-7144-11e5-ba71-058fbc01cf0b	23877894.0

user_guid	state	membership_type
ce138312-7144-11e5-ba71-058fbc01cf0b	AE	1
ce7587ba-7144-11e5-ba71-058fbc01cf0b	AE	1
ce76f528-7144-11e5-ba71-058fbc01cf0b	AE	1
ce221dbe-7144-11e5-ba71-058fbc01cf0b	AE	2
ce70836e-7144-11e5-ba71-058fbc01cf0b	AE	2
ce969298-7144-11e5-ba71-058fbc01cf0b	AE	3

breed
Yorkshire Terrier-Soft Coated Wheaten Terrier Mix
Yorkshire Terrier-Silky Terrier Mix
Yorkshire Terrier-Shih Tzu Mix
Yorkshire Terrier-Rat Terrier Mix
Yorkshire Terrier-Poodle Mix
Yorkshire Terrier-Pomeranian Mix
Yorkshire Terrier-Pekingese Mix
Yorkshire Terrier-Miniature Schnauzer Mix
Yorkshire Terrier-Miniature Pinscher Mix
Yorkshire Terrier-Maltese Mix
Yorkshire Terrier-Lhasa Apso Mix
Yorkshire Terrier-Havanese Mix
Yorkshire Terrier-German Wirehaired Pointer Mix
Yorkshire Terrier-German Spitz Mix
Yorkshire Terrier-Dogue de Bordeaux Mix
Yorkshire Terrier-Chihuahua Mix
Yorkshire Terrier-Cairn Terrier Mix
Yorkshire Terrier-Bichon Frise Mix
Yorkshire Terrier-Belgian Malinois Mix
Yorkshire Terrier-Australian Terrier Mix
Yorkshire Terrier- Mix
Yorkshire Terrier
Xoloitzcuintli
Wirehaired Vizsla
Wirehaired Pointing Griffon
Wire Fox Terrier-Parson Russell Terrier Mix
Wire Fox Terrier-Lhasa Apso Mix
Wire Fox Terrier-Italian Greyhound Mix
Wire Fox Terrier-Dachshund Mix
Wire Fox Terrier-Chihuahua Mix
Wire Fox Terrier-Bulldog Mix
Wire Fox Terrier-Australian Cattle Dog Mix
Wire Fox Terrier
Whippet-Tibetan Terrier Mix
Whippet-Scottish Deerhound Mix
Whippet-Saluki Mix
Whippet-Russell Terrier Mix
Whippet-Rhodesian Ridgeback Mix
Whippet-Rat Terrier Mix
Whippet-Poodle Mix