Autor: Luis Fernando Apáez
La limpieza de datos es importante pues ésta prepara los datos para su correcto análisis. De hecho, los analistas y científicos de datos dedican la mayor parte de su tiempo a la limpieza, versus el tiempo dedicado propiamente al análisis.
Los datos que estaremos ocupando se referentes a los vuelos mensuales de aerolíneas en aeropuertos de EU entre el 2014 y 2015.
Consideremos la tabla dbo.flight_statistics de la base de datos prueba, veamos los primeros registros:
SELECT TOP 5 * FROM prueba.dbo.flight_statistics;
| registration_code | airport_code | carrier_code | canceled | on_time | delayed | diverted | statistician_name | statistician_surname | registration_date |
|---|---|---|---|---|---|---|---|---|---|
| 000000119 | JFK | AA | 74 | 819 | 233 | 13 | Miriam | Smith | 2014-01-31 |
| 120 | JFK | B6 | 438 | 1865 | 1010 | 29 | Myriam | Smith | 2014-01-31 |
| 000000121 | JFK | HA | NULL | NULL | NULL | NULL | Mirian | Smyht | 2014-01-31 |
| 122 | JFK | MQ | NULL | NULL | NULL | NULL | Miriam | Smyth | 2014-01-31 |
| 123 | JFK | EV | NULL | NULL | NULL | NULL | Astrid | Harper | 2014-01-31 |
Notemos que en la columna registration_code tenemos valores con diferentes formatos, por ejemplo vemos el código 120 o el código 000000121. Supongamos en realidad que la forma correcta de tener el código es en el formato 000000121 por lo que nos interesa alterar los códigos como 120 para tener todos los valores en el mismo formato.
Para ello nos auxiliaremos de la función
REPLICATE(string, integer)
la cual repite una cadena un número especifico de veces. Así, podremos repetir los ceros que nos hacen faltan en los códigos como 120 Así, escribimos pues:
REPLICATE('0', 9)
para repetir 9 veces el número 0. No obstante, no es nuestro interés agregar 9 cero a los códigos de tres cifras, como 120, de hecho nos interesa agregar 9 menos el número de dígitos de dicho número. Esto es, si el código tiene 3 cifras, entonces debemos agregar 6 ceros, o lo que es lo mismo, debemos agregar 9 - 3 ceros. De tal manera, en realidad debemos de escribir
REPLICATE('0', 9 - len(registration_code))
Con lo anterior habremos generado el número de ceros necesarios para tener los códigos de registration_code en el formato que queremos, para lo cual concatenaremos dichos ceros con el registro correspondiente de registration_code:
SELECT TOP 5
REPLICATE('0', 9 - LEN(registration_code)) + registration_code AS registration_code
FROM prueba.dbo.flight_statistics;
| registration_code |
|---|
| 000000119 |
| 000000120 |
| 000000121 |
| 000000122 |
| 000000123 |
Con lo cual hemos conseguido lo que buscábamos. Notemos que la concatenación la hemos realizado con el símbolo de suma +. Tenemos otra alternativa para conseguir el mismo resultado, en vez de utilizar la concatenación con el símbolo de suma, podemos invocar propiamente a la función CONCAT():
SELECT TOP 5
-- Empleamos la funcion concat()
CONCAT(REPLICATE('0', 9 - LEN(registration_code)), registration_code) AS registration_code
FROM prueba.dbo.flight_statistics;
| registration_code |
|---|
| 000000119 |
| 000000120 |
| 000000121 |
| 000000122 |
| 000000123 |
FORMAT() ¶Podemos utilizar la función FORMAT() para obtener un resultado totalmente análogo al anterior. La sintaxis básica para esta función es
FORMAT(value, format [, culture])
donde culture es opcional y representa diferentes códigos de idioma; value es numeric, date o time.
De tal forma, podemos utilizar esta función sobre los registros de la columna registration_code como sigue:
SELECT TOP 5
FORMAT(CAST(registration_code AS INT), '000000000') AS registration_code
FROM prueba.dbo.flight_statistics;
| registration_code |
|---|
| 000000119 |
| 000000120 |
| 000000121 |
| 000000122 |
| 000000123 |
donde convertimos primero los registros de la columna registration_code a tipo entero para después darle el formato en 9 ceros. Es decir, considerando ya el código como un entero, si dicho entero es de tres cifras (120), entonces con la función FORMAT() damos el formato de nueve cifras a dicho número, es decir, agregamos 6 ceros al inicio del número en cuestión (conseguimos por ejemplo el número 000000120).
Observación: Cabe resaltar que el tipo de dato de la columna registration_code es de tipo VARCHAR, pero necesitamos que sea de tipo numérico por lo que realizamos la conversión a tipo entero mediante la función CAST().
Deseamos obtener cada registro de registration_code que tenga asociado más de 100 retrasos (delayed) de la tabla flight_statistics. En una única columna concatenaremos los campos carrier_code, registration_code y airport_code, buscando tener los registros de esta única columna en el formato
AA-000000119-JFKpor ejemplo. Para ello:
SELECT
CONCAT(
carrier_code,
' - ',
REPLICATE('0', 9 - LEN(registration_code)),
registration_code,
' - ',
airport_code)
AS registration_code
FROM prueba.dbo.flight_statistics
WHERE delayed > 100;
| registration_code |
|---|
| AA - 000000119 - JFK |
| B6 - 000000120 - JFK |
| AA - 000000127 - JFK |
| B6 - 000000128 - JFK |
| DL - 000000134 - MSP |
| DL - 000000136 - MSP |
| EV - 000000137 - MSP |
| OO - 000000141 - MSP |
| OO - 000000142 - MSP |
| OO - 000000143 - MSP |
O alternativamente podemos utilizar la función FORMAT()
SELECT
CONCAT(
carrier_code,
' - ',
FORMAT(CAST(registration_code AS INT), '000000000'),
' - ',
airport_code)
AS registration_code
FROM prueba.dbo.flight_statistics
WHERE delayed > 100;
| registration_code |
|---|
| AA - 000000119 - JFK |
| B6 - 000000120 - JFK |
| AA - 000000127 - JFK |
| B6 - 000000128 - JFK |
| DL - 000000134 - MSP |
| DL - 000000136 - MSP |
| EV - 000000137 - MSP |
| OO - 000000141 - MSP |
| OO - 000000142 - MSP |
| OO - 000000143 - MSP |