Operaciones De Bases De Datos en Terminal

Es un hecho interesante y no muy conocido que podemos realizar las operaciones básicas de bases de datos como selección de columnas o uniones con comandos en la terminal. Esto es importante porque muchas veces recibimos dumps de datos enormes y no muy bien estructurados que no podemos alimentar directamente en nuestras bases de datos y que pueden sentar a pandas y a nuestras computadoras por una falta de memoria.

Algunas de las herramientas para manejar este tipo de casos son:

Hay otras herramientas mas poderosas como sed y awk pero, esas requieren libros enteros para aprender a manejarlas correctamente.

Vamos a hacer un pequeño ejemplo de tratamiento de archivos con cut sort uniq, join y tal vez un poco de grep para filtrar o extraer datos.

cat

El comando cat lo podemos utilizar para unir dos archivos que tengan las mismas columnas y que resulte en un archivo mas largo. Si tenemos archivos con headers lo único que debemos cuidar es en eliminar los headers.

file1.txt
col1,col2,col3,col4
1,44,0.213,95613
2,27,0.472,54320
3,26,0.581,65823
4,44,0.937,65389
5,27,0.678,99858
6,44,0.654,65277
7,67,0.278,87442
8,44,0.894,18597

file2.txt
7,32,0.326,95003
7,28,0.762,54335
3,27,0.551,65823
3,44,0.837,12389
5,27,0.678,92348
5,65,0.674,65345
6,67,0.228,78442
9,44,0.824,18347

$ cat file1.txt file2.txt > file3.txt

file3.txt
col1,col2,col3,col4
1,44,0.213,95613
2,27,0.472,54320
3,26,0.581,65823
4,44,0.937,65389
5,27,0.678,99858
6,44,0.654,65277
7,67,0.278,87442
8,44,0.894,18597
col1,col2,col3,col4
7,32,0.326,95003
7,28,0.762,54335
3,27,0.551,65823
3,44,0.837,12389
5,27,0.678,92348
5,65,0.674,65345
6,67,0.228,78442
9,44,0.824,18347

Omitiendo la primera línea del segundo archivo

$ cat file1.txt <(tail -n+2 file2.txt) > file3a.txt

el archivo file3a.txt se ve así

file3.txt
col1,col2,col3,col4
1,44,0.213,95613
2,27,0.472,54320
3,26,0.581,65823
4,44,0.937,65389
5,27,0.678,99858
6,44,0.654,65277
7,67,0.278,87442
8,44,0.894,18597
7,32,0.326,95003
7,28,0.762,54335
3,27,0.551,65823
3,44,0.837,12389
5,27,0.678,92348
5,65,0.674,65345
6,67,0.228,78442
9,44,0.824,18347

join

El comando join nos permite unir los archivos a lo ancho dependiendo de alguna columna que tengan en común los archivos. Es el equivalente a los joins de SQL, pero idealmente las columnas llave deben estar ordenadas.

Ejemplo de join con una sola columna

$ join -t,  -o 1.1,1.2,1.3,1.4,2.3,2.4 file1.txt file5.txt 
col1,col2,col3,col4,col5,col6
1,44,0.213,95613,Pedro,Verde
2,27,0.472,54320,Maria,Azul
3,26,0.581,65823,Juan,Rojo
4,44,0.937,65389,Omar,Gris
5,27,0.678,99858,Ilce,Rojo
6,44,0.654,65277,Laura,Naranja
7,67,0.278,87442,Miguel,Blanco
8,44,0.894,18597,Rosa,Negro

Se pueden usar diferentes columnas en los archivos Si la columna llave en el archivo file1 fuera la 1 en file5 foera la 3

join -1 1 -2 3 -t,  -o 1.1,1.2,1.3,1.4,2.2,2.4 file1.txt file5.txt

cut

El comando cut nos permite seleccionar conjuntos de columnas, ya sea una o por rangos o en una lista y nos permite reordenar la salida, lo cual es muy conveniente para los comandos cat o join.

Ejemplo de extracción de una sola columna, aqui vamos a extraer la primera columna de file1.txt

$ cut -f1 -d',' file1.txt
col1
1
2
3
4
5
6
7
8

Ejemplo de extracción de una lista de columnas

Si queremos extraer un par de columnas de file1.txt podemos tomar las columnas 1 y 3 por ejemplo

$ cut -f1,3 -d',' file1.txt
col1,col3
1,0.213
2,0.472
3,0.581
4,0.937
5,0.678
6,0.654
7,0.278
8,0.894

Ejemplo de extracción de un rango de columnas

$ cut -f1-3 -d',' file1.txt 
col1,col2,col3
1,44,0.213
2,27,0.472
3,26,0.581
4,44,0.937
5,27,0.678
6,44,0.654
7,67,0.278
8,44,0.894

sort, uniq

Estos comandos permiten hacer el filtrado y ordenación de las columnas y se utilizan en general para ordenar y hacer conteos de llaves únicas, de registros diferentes, y como paso previo a la unión con join.

Extracción de llaves únicas de una columna, para este ejemplo usaremos la columna 1 del archivo file2.txt

$ cut -f1 -d',' <(tail -n+2 file2.txt) | sort | uniq
3
5
6
7
9

Conteo de llaves únicas

$ cut -f1 -d',' <(tail -n+2 file2.txt) | sort | uniq | wc -l
	5

Obtener uno archivo ordenado por el primer campo

$ sort <(tail -n+2 file2.txt | sort -k 1n) <(tail -n+2 file1.txt | sort -k 1n)

1,44,0.213,95613
2,27,0.472,54320
3,26,0.581,65823
3,27,0.551,65823
3,44,0.837,12389
4,44,0.937,65389
5,27,0.678,92348
5,27,0.678,99858
5,65,0.674,65345
6,44,0.654,65277
6,67,0.228,78442
7,28,0.762,54335
7,32,0.326,95003
7,67,0.278,87442
8,44,0.894,18597
9,44,0.824,18347

Conclusión

La terminal es una de las herramientas mas poderosas en el arsenal del científico de datos, del analista y del programador. Permite hacer análisis y conteos rápidos sin necesidad de utilizar herramientas complicadas y por su diseño, estas herramientas son robustas y de uso sencillo, además de que se pueden combinar o encadenar para generar comportamientos complejos o para generar scripts.