Proyecto Limpieza de Datos con SQL 📊

Este proyecto tiene como objetivo destacar la importancia de la limpieza de datos en el mundo del análisis de datos y cómo esta fase crítica sienta las bases para la toma de decisiones informadas y resultados precisos en cualquier iniciativa de análisis. A lo largo del proyecto se usarán las mejores practicas y técnicas con el fin de identificar y transformar una fuente de datos con errores e inconsistencias en un dataset final apto para el analisis y con los requerimientos del cliente.

Herramientas 🛠️

Para realizar esta tarea se usó el gestor de datos MySQL

Contexto y Dataset del Proyecto.

Se toma el dataset de una empresa la cual organiza sus empleados por distintas areas, además especificando datos como el salario, fecha de cumpleaños y fecha de inicio con la compañia.

El Dataset del proyecto es tomado de la web Kaggle, donde se suministra un archivo .csv con datos de una compañia ficticia.

Requerimiento de Limpieza de Datos a Resolver 🧹

Estandarizar el contenido de la tabla al idioma inglés, tanto en los encabezados de columna como en los registros existentes.

Manejar los registros nulos, duplicados y vacios.

Dar el correcto formato a los tipos de datos según la columna.

Establecer formatos de fecha y hora adecuados.

Remover espacios en blanco en los registros

Registrar el tipo de contrato del empleado según corresponda.

Remover Columnas no requeridas.

Adicionar columnas requeridas para el posterior Analisis de Datos.

Ejecución Limpieza de Datos con MySQL

-- # Proyecto Limpieza de Datos # -- -- # Creación de la base de datos # -- create database if not exists limpieza; -- # Se importa el archivo .csv # -- -- # Se Selecciona la base de datos a trabajar #-- use limpieza; -- # Se genera una muestra de los datos # -- SELECT * FROM limpieza LIMIT 10; -- # Creación de procedimiento almacenado # -- DELIMITER // CREATE PROCEDURE limp() BEGIN SELECT * FROM limpieza; -- # Sentencia Select para evitar escribir toda la consulta # -- END // DELIMITER; -- # Se ejecuta el procedimiento almacenado # -- CALL limp(); -- # Se inicia la limpieza de datos # -- -- # Cambiar el nombre a las columnas # -- ALTER TABLE limpieza CHANGE COLUMN `ï»¿Id?empleado` id_employee varchar(20) null; -- # Entre comillas debido a los caracteres especiales # -- ALTER TABLE limpieza CHANGE COLUMN `gÃ©nero` gender varchar(20) null; -- # Entre comillas debido a los caracteres especiales # -- ALTER TABLE limpieza CHANGE COLUMN Name name varchar(40) null ALTER TABLE limpieza CHANGE COLUMN apellido last_name varchar(40) null; ALTER TABLE limpieza CHANGE COLUMN star_date start_date varchar(20) null; -- SELECT COUNT(*) AS total_duplicados FROM (SELECT id_employee, COUNT(*) AS duplicados FROM limpieza GROUP BY id_employee HAVING COUNT(*) > 1) AS duplicados # Crear una tabla temporal con valores unicos y luego hacerla "Original" (permanentemente) # -- -- # Cambiar el nombre de la tabla # -- RENAME TABLE limpieza to conduplicados; -- # Crear una tabla temporal # -- CREATE TEMPORARY TABLE temp_limpieza AS SELECT DISTINCT * FROM conduplicados; -- # Verificar la cantidad de registros # -- SELECT COUNT(*) AS original FROM conduplicados; SELECT COUNT(*) AS uniques FROM temp_limpieza; -- # Convertir la tabla temporal a permanente # -- CREATE TABLE limpieza AS SELECT * FROM temp_limpieza SELECT COUNT(*) AS total_duplicados FROM (SELECT id_employee, COUNT(*) AS duplicados FROM limpieza GROUP BY id_employee HAVING COUNT(*) > 1) AS duplicados -- # Se elimina la tabla que contiene los duplicados # -- DROP TABLE conduplicados; -- # Activar / Desactivar modo seguro (0 = desactivar) (1 = activar) # -- SET sql_safe_updates = 0; -- # Se analizan los metadatos de la tabla, identificando el tipo de datos # -- DESCRIBE limpieza; -- SELECT name, last_name FROM limpieza WHERE LENGTH(name) - LENGTH(TRIM(name)) > 0 OR LENGTH(last_name) - LENGTH(TRIM(last_name)) > 0# Ensayo de eliminar los espacios en blanco en la columna name # -- SELECT name, TRIM(name) AS name FROM limpieza WHERE LENGTH(name) - LENGTH(TRIM(name)) > 0; -- UPDATE limpieza SET name = TRIM(name) WHERE LENGTH(name) - LENGTH(TRIM(name)) > 0 -- Al igual que en la columna name se actualizan los registros de la columna last_name # -- UPDATE limpieza SET last_name = TRIM(last_name) WHERE LENGTH(last_name) - LENGTH(TRIM(last_name)) > 0# Se introducen a modo de prueba espacios en blanco adicionales en la columna area # -- UPDATE limpieza SET area = REPLACE(area, ' ', ' '); -- # Se muestran los registros en la columna area donde hay más de 1 espacio en blanco # -- SELECT area FROM limpieza WHERE area REGEXP '\s{2,}'; -- # Ensayo para visualizar el recorte de espacios en blanco # -- SELECT area, TRIM(REGEXP_REPLACE(area, '\s{2,}', ' ')) AS ensayo FROM limpieza; -- # Se actualiza la tabla con la función TRIM para remover espacios en blanco # -- UPDATE limpieza SET area = TRIM(REGEXP_REPLACE(area, '\s{2,}', ' ')); -- # Ensayo traducción de los registros en la columna gender # -- SELECT gender, CASE WHEN gender = 'hombre' THEN 'male' WHEN gender = 'mujer' THEN 'female' ELSE 'other' END AS gender1 FROM limpieza; -- UPDATE limpieza SET gender = CASE WHEN gender = 'hombre' THEN 'male' WHEN gender = 'mujer' THEN 'female' ELSE 'other' END -- # Modificar tipo de dato de la columna type # -- ALTER TABLE limpieza MODIFY COLUMN type TEXT; -- # Ensayo para cambiar registros columna type según corresponda # -- SELECT type, CASE WHEN type = 0 THEN 'hybrid' WHEN type = 1 THEN 'remote' ELSE 'other' END AS type1 FROM limpieza; -- # Actualización columna type usando la función CASE anterior # -- UPDATE limpieza SET type = CASE WHEN type = 0 THEN 'hybrid' WHEN type = 1 THEN 'remote' ELSE 'other' END; -- # Se usa la función almacenada para verificar las actualizaciones hechas # -- CALL limp(); -- # Ensayo para modificar la columna salary # -- SELECT salary, CAST(TRIM(REPLACE(REPLACE(salary, '$', ''), ',', '')) AS DECIMAL (15 , 2 )) AS salary1 FROM limpieza; -- # Actualizar la columna salary usando las funciones del ensayo anterior # -- UPDATE limpieza SET salary = CAST(TRIM(REPLACE(REPLACE(salary, '$', ''), ',', '')) AS DECIMAL (15 , 2 )); -- # Cambiar tipo de dato a la columna salary # -- ALTER TABLE limpieza MODIFY COLUMN salary int null; -- # Volvemos a verificar los metadatos # -- DESCRIBE limpieza; -- # Removemos de la tabla la columna promotion_date # -- ALTER TABLE limpieza DROP COLUMN promotion_date; -- # Ensayo para dar el formato adecuado a la columna birht_date # -- SELECT birth_date, STR_TO_DATE(birth_date, '%m/%d/%Y') AS date FROM limpieza; -- # Actualizamos la columna birth_date con la función anterior # -- UPDATE limpieza SET birth_date = STR_TO_DATE(birth_date, '%m/%d/%Y'); -- # Damos el tipo de dato correcto a la columna birth_date # -- ALTER TABLE limpieza MODIFY COLUMN birth_date DATE; -- # Realizamos el mismo procedimiento a la columna start_date # -- SELECT start_date, STR_TO_DATE(start_date, '%m/%d/%Y') AS date FROM limpieza; UPDATE limpieza SET start_date = STR_TO_DATE(start_date, '%m/%d/%Y'); ALTER TABLE limpieza MODIFY COLUMN start_date DATE; -- # Ensayo para manipular la columna finish_date # -- SELECT finish_date, STR_TO_DATE(finish_date, '%Y-%m-%d %H:%i:%s') AS DATE1 FROM limpieza; -- # Se crea una columna backup y se llena con los datos de la columna finish_date # -- ALTER TABLE limpieza ADD COLUMN date_backup text; UPDATE limpieza SET date_backup = finish_date; -- # Se actualiza la columna finish_date con el ensayo anterior # -- UPDATE limpieza SET finish_date = STR_TO_DATE(finish_date, '%Y-%m-%d %H:%i:%s UTC') WHERE finish_date <> ''; -- # Se adicionan dos columnas a la tabla : date y hour # -- ALTER TABLE limpieza ADD COLUMN date date, ADD COLUMN hour time; -- # Se llenan los registros de las dos columnas previamente creadas con datos de la columna finish_date # -- UPDATE limpieza SET date = DATE(finish_date), hour = TIME(finish_date) WHERE finish_date IS NOT NULL AND finish_date <> ''; -- # Se agrega valores NULL a la columna finish_date donde hayan espacios en blanco # -- UPDATE limpieza SET finish_date = NULL WHERE finish_date = ''; -- # Se cambia el tipo de dato de la columna finish_date # -- ALTER TABLE limpieza MODIFY COLUMN finish_date datetime; -- # De nuevo se verifican los metadatos # -- DESCRIBE limpieza; -- # Verificamos de nuevo los datos con el procedimiento almacenado # -- CALL limp(); -- # Removemos la columna date_backup # -- ALTER TABLE limpieza DROP COLUMN date_backup; -- # Adionamos la columna age # -- ALTER TABLE limpieza ADD COLUMN age int; -- # Se realiza un ensayo para calcular el valor de age para cada registro # -- SELECT birth_date, start_date, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM limpieza; -- # Se actualiza la columna age con la función del ensayo anterior # -- UPDATE limpieza SET age = timestampdiff(year,birth_date,curdate()); -- # Se adiciona la columna email # -- ALTER TABLE limpieza ADD COLUMN email varchar(50); -- # Se realiza un ensayo para concatenar los registros para la columna email # -- SELECT lower(concat(substring_index(name,' ', 1), '_', substring(last_name,1,2), '.', substring(type,1,1), '@cleaning.com')) as email FROM limpieza; -- # Se actualiza la columna email con el ensayo anterior # -- UPDATE limpieza SET email = lower(concat(substring_index(name,' ', 1), '_', substring(last_name,1,2), '.', substring(type,1,1), '@cleaning.com')); -- # Se llama al procediiento almacenado para verificar el estado final de la tabla # -- CALL limp(); -- # Fin Proyecto de limpieza # --

Resultados ✅

Con el fin de mostrar el cumplimiento de los requerimientos establecidos, a continuacion se muestran unas imagenes de la ejecución realizada en MySQL

1. Estado de la Tabla Inicial.

2. Cantidad de registros antes de la limpieza, cantidad de registros duplicados y cantidad de registros posterior a la limpieza.

3. Visualización espacios en blanco en las columnas y posterior eliminación de estos.

4. Traducción Columna gender.

5. Asignación tipo de contrato.

6. Correcto formato columna salary, start_date y finish_date.

7. Adición Columnas requeridas.

8. Tabla final, limpia y lista para el Analisis.

-----FIN LIMPIEZA DE DATOS----

Bonus 💡

Una vez realizado la limpieza de datos se puede proceder a realizar un analisis de los mismos con el fin de responder preguntas empresariales que permitan tomar decisiones basadas en hechos. Así, a continuación se presentan las siguientes 5 consultas de ejemplo, las cuales pueden tener relevancia para la compañia.

¿Cuántos reegistros hay por cada valor distinto de la columna area?

¿Cuál es la proporción de género dependiendo el tipo de contrato?

¿Cuántos empleados se encuentran por encima de la edad promedio?

¿Cuáles son los dos empleados con el mayor salario en cada una de las areas?

¿Cuáles son la cantidad de cumpleaños que ocurren en cada mes?

-- # Bonus # -- -- # Consultas para generar un analisis de la tabla creada luego de realizar la limpieza de datos # -- -- # 1.¿Cuantos reegistros hay por cada valor distinto de la columna area? # -- SELECT area, COUNT(*) AS total_registrer FROM limpieza GROUP BY area ORDER BY total_registros DESC; -- # 2. ¿Cual es la proporción de género dependiendo el tipo de contrato? # -- SELECT type, gender, FORMAT(COUNT(gender) / (SELECT COUNT(*) FROM limpieza) * 100, 2) AS percent_ratio FROM limpieza GROUP BY type , gender ORDER BY type; -- # 3. ¿Cuántos empleados se encuentran por encima de la edad promedio? # -- SELECT COUNT(*) AS total_employee FROM limpieza WHERE age > (SELECT AVG(age) AS aveg_age FROM limpieza); -- # 4. ¿Cuales son los dos empleados con el mayor salario en cada una de las areas? #-- SELECT CONCAT(name, ' ', last_name) as full_name, area, salary FROM ( SELECT name, last_name, area, salary, row_number() over (partition by area order by salary DESC) as ranking FROM limpieza ORDER BY area, ranking) as rankings WHERE ranking IN (1,2) ORDER BY area, salary DESC; -- 5. ¿Cualés son la cantidad de cumpleaños que ocurren en cada mes? SELECT DATE_FORMAT(birth_date, '%M') AS month_name, COUNT(*) AS total_birthdays FROM limpieza GROUP BY month_name , DATE_FORMAT(birth_date, '%m') ORDER BY DATE_FORMAT(birth_date, '%m');

Resultados consultas ✅

¿Cuantos registros hay por cada valor distinto de la columna area?

¿Cuál es la proporción de género dependiendo el tipo de contrato?

¿Cuántos empleados se encuentran por encima de la edad promedio?

¿Cuáles son los dos empleados con el mayor salario en cada una de las areas?

¿Cualés son la cantidad de cumpleaños que ocurren en cada mes?

Conclusiones 📌

La limpieza de datos emerge como un pilar inquebrantable en el mundo del análisis de datos. A lo largo de este proyecto, hemos explorado su importancia crítica en la transformación de datos crudos en información valiosa. Este proceso minucioso no solo garantiza la confiabilidad de los resultados, sino que también habilita a los interesados a tomar decisiones más informadas y estratégicas. La detección y corrección de errores, valores atípicos y datos inconsistentes son pasos esenciales que allanan el camino para un análisis preciso y robusto. Los analistas de datos, al dominar esta fase inicial, pueden proporcionar insights confiables que respalden la toma de decisiones empresariales, promoviendo así un enfoque basado en datos en todas las facetas de la organización. En última instancia, la limpieza de datos no es solo una tarea, sino una inversión crucial para el éxito continuo en el emocionante mundo del análisis de datos.