Como detectar un dato no numérico en una lista de números una vez que se ha modificado la alineación de las celdas

En el post anterior veíamos como detectar números introducidos incorrectamente en una hoja de cálculo diferenciándolos por la alineación por defecto.

Este método era útil si no se había alterado la alineación.

Pero ¿como hacerlo si se ha unificado la alineación de toda la columna, y ya no es posible deshacer esa acción?

Supongamos que vemos eso tan feo, tan desalineado que, antes de guardar, alineamos todos nuestro números a la derecha, como es normal en una columna numérica, luego cerramos el Excel, o el Libreoffice.

Al volver a abrirlo, nos encontraremos algo así, y sin posibilidad de deshacer la acción del alineado.

Vamos a recuperar los colores del post anterior para diferenciar las celdas que no contienen datos numéricos.

Una opción poco conocida por muchos usuarios de Microsoft Excel es la posibilidad de personalización de la barra de estado.

Se trata de la barra que se encuentra en la parte inferior de la ventana del programa.

En ella se pueden mostrar datos de las celdas seleccionadas.

Los datos a mostrar se configuran haciendo clic con el botón derecho encima de la barra.

Entre los datos a mostrar, dos son de especial interés, estos son "Recuento" y "Recuento numérico"

El primero de ellos "Recuento" indica el número de celdas seleccionadas que contienen datos. Esto es celdas no vacías.

El segundo "Recuento numérico" indica el número de celdas seleccionadas que contienen datos numéricos.

Ante una columna que debería incluir solo datos numéricos la información mostrada por ambas cifras debería ser idéntica.

En nuestro caso, "Recuento" indica 13 celdas no vacías, mientras "Recuento numérico" indica 8 celdas con datos numéricos, por lo tanto quedan celdas no vacías pero con datos no numéricos.

NOTA:

Es habitual incluir una fila de títulos, si se hace esto, se deberá esperar que exista una unidad de diferencia entre el dato mostrado por ambas opciones.

A continuación se añade una fila de título y se corrigen os datos numéricos, vemos que ahora tenemos 14 filas no vacías de las cuales una es la de título y las otras 13 son numéricas.

Como detectar un dato no numérico en una lista de números si no se ha modificado el formato.

En ocasiones se puede dar el caso de que una Hoja de Cálculo arroje resultados que calculando manualmente no coinciden.

Cuando eso pasa, lo mas probable es que no se esté detectando e dato como número. Usualmente eso se debe a la inclusión de algún valor no correcto, por ejemplo espacios, comas en lugar de puntos, letras o en lugar de números cero, etc.

Si no se ha modificado el formato, más en concreto la alineación por defecto de las celdas, será relativamente sencillo encontrar los datos erróneos.

Teniendo en cuanta que, por defecto, las hojas de cálculo como Microsoft Excel, alinean los datos numéricos a la derecha y los textos a la izquierda, es fácil detectar los datos erróneos.

En la imagen siguiente, en las celdas amarillas, la colocación de los separadores de miles y los separadores decimales no son coherentes, por lo tanto se tratan como texto.

En la celda verde, se ha incluido un espacio para indicar la separación de los miles (un método usado en documentos de texto habitualmente, pero que provoca que las hojas de cálculo no reconozcan el dato correctamente)

En la celda azul se ha escrito por error una letra o en lugar de un cero.

Como se aprecia, todas las celdas erróneas aparecen alineadas a la izquierda como si fuesen un texto, mientras que las numéricas aparecen a la derecha.


 Este método es válido para pocas líneas y antes de haber alterado la alineación de las celdas.

Como convertir valores numéricos almacenados como texto a datos numéricos (método 2 - Mediante una fórmula)

En el post anterior veíamos que en ocasiones Microsoft Excel no reconoce correctamente los datos numéricos tratándolos como textos.

Esto provoca que no sean válidos para hacer operaciones matemáticas, entre otras cosas.

En este caso vamos a ver como convertir eses datos mediante una sencilla fórmula de la hoja de cálculo.

Aunque el ejemplo se hará en Microsoft Excel, este ejemplo es válido para otras hojas de cálculo como Libreoffice Calc o Google Spreadsheets.

La fórmula a usar es valor, la cual recibe un único parámetro que es un número almacenado como texto.

Si todo va bien el resultado será una columna con los valores numéricos correspondientes.

Como ventaja adicional, con respecto al método anterior, la fórmula devolverá un error "#¡VALOR!" en caso de que alguno de los datos a convertir no pueda ser tratado como número.

En el siguiente ejemplo, en amarillo un texto "hola" y en verde un texto compuesto de caracteres alfanuméricos "125abc456"

Este método es preferible a la conversión seleccionando las celdas cuando los datos a convertir sean numerosos, pues es más sencillo hacer una fórmula y aplicarla a toda la columna que seleccionar la columna con el ratón.

Como convertir valores numéricos almacenados como texto a datos numéricos (método 1 - Seleccionando con el ratón)

En ocasiones Microsoft Excel reconoce datos numéricos como textos.

En estos casos se muestra un pequeño triángulo verde en la parte superior izquierda de la celda.

Convertir el  contenido de estas celdas en números reconocibles por Excel es sencillo.

Es preciso seleccionar con el ratón (no vale con el teclado, ya que no se muestra el menú emergente) todo el rango afectado.

Una vez finalizada la selección se mostrará un menú emergente en el que se indica que se han encontrado números almacenados como texto, permitiendo convertirlos a número.

Seleccionaremos la opción "Convertir en número", y automáticamente desaparecerá el triángulo verde, y por defecto cambiará la alineación hacia la derecha, quedando los datos como se muestra a continuación.

Como convertir un número entre formato americano y español en un fichero CSV (antes de importar)

Teniendo en cuenta que Microsoft Excel intentará reconocer el formato de los números, es posible que una vez importado un dato como texto, aunque se sustituyan puntos por comas para convertir el texto en número (como se explicaba en el post anterior Como convertir un número entre formato americano y español en un fichero CSV.), será necesario convertir luego los textos en números.

Este inconveniente se puede solucionar realizando la sustitución antes de la importación.

Para ello, dependiendo del tamaño del fichero, usaremos un editor nuestro editor de texto preferido.

En este caso veremos como hacerlo con el Notepad (Bloc de notas)

Sin embargo, en el ejemplo mostrado, no es posible modificar los puntos por comas, sin más, ya que el separador utilizado son las comas. Esto nos obligará a hacer dos sustituciones. En primer lugar sustituir las comas por puntos y coma, y luego sustituir los puntos por comas.

Paso 1 - Abrir el fichero con el Bloc de notas.

Paso 2 - Reemplazar comas por puntos y coma.

Paso 3 - Reemplazar puntos por comas.

Paso 4 - Abrir con la hoja de cálculo Microsoft Excel.

Como convertir un número entre formato americano y español en un fichero CSV.

Un fichero CSV (character-separated values) solo es un fichero de texto, por lo que la diferencia entre el formato numérico anglosajón y el español implica un intercambio entre puntos y comas.

En España el separador decimal es la coma, mientras que el separador de miles, ahora en desuso, es el punto.

En el mundo anglosajón el caso es el inverso, esto es separador decimal el punto y separador de miles la coma.

En esta entrada se tratará el caso de tener un listado de números decimales sin separador de miles, como puede ser un listado de precios, por ejemplo.

Algo así:

codigoArticulo;Precio
1234567890,123.23
9876543214,547.15
8574965265,21.48
1236523658,2.45

Os muestro una imagen de como quedaría abierto en el Bloc de Notas:

Una vez abierto este fichero con una hoja de cálculo como Microsoft Excel en castellano no abre como era de esperar, ya que no reconoce la coma como un separador de campos válidos. (Microsoft Excel usa como separador por defecto el indicado en la Configuración Regional del sistema operativo)

Se podría separar por la coma usando la opción Datos -> Texto en columnas.

Quedando como muestro:

Como vemos, si que se han separado los datos, pero la columna de precios se trata como un texto.

Podemos solucionar esto desde la propia hoja de cálculo, o mediante un un paso anterior modificando los datos de origen.

En próximas entradas veremos los dos métodos para solucionar este problema.

Añadir elementos a una lista en Python

Creamos un programa que nos solicita por teclado un número entero y lo añade a una lista en python.

La lista solo admitirá números enteros, para lo cual se convertirá el dato devuelto por input, que es un string en un int.

Esta conversión puede no ser posible, como en el caso de introducir un texto en lugar de un número, lo que genera una excepción.

Como ejemplo, si ejecutamos esta línea de cógido, e introducimos el texto "hola" en la consola, se genera una excepción de tipo ValueError: invalid literal for int() with base 10: 'hola'

dato = int(input("dame un numero: "))

La salida en la consola será:

dame un numero: hola

Traceback (most recent call last):

  File "C:/Users/Ubuntu/PycharmProjects/002/007.py", line 1, in <module>

    dato = int(input("dame un numero: "))

ValueError: invalid literal for int() with base 10: 'hola'



Process finished with exit code 1

Por lo tanto tendremos que controlar la excepción para ignorar esas entradas y continuar con el programa.

Para ello usaremos la instrucción try / except

En caso de que no se pueda realizar la conversión a número entero entonces se ejecutará el código dentro de except, en este caso la instrucción continue.

Esta instrucción hace que se pase a la siguiente iteración del bucle while.

En caso de que no se genere ninguna excepción se ejecuta el código dentro de else.

De esta forma solo se añadirá el dato a la lista si se realiza correctamente la conversión.

Además como la condición de finalización del bucle es que se introduzca un valor negativo se comprueba si el valor es mayor que cero, en caso contrario no se añade, y al ser evaluado en el while se finaliza el bucle.

miLista = []

dato = 999
while dato > 0:
    try:
        dato = int(input("dame un numero: "))
    except:
        continue    else:
        if dato > 0: miLista.append(dato)

print (miLista)

Python listas y conjuntos

Por ahora nos centraremos en el uso de listas en Python.

Si bien es cierto que se pueden crear arrays de un único tipo de datos mediante la librería array, estas estructuras optimizan mejor la memoria, pero como contrapartida son mas lentas que las listas.

Por otra parte que todos los datos contenidos son del mismo tipo, pero eso se puede resolver mediante programación.

Por ahora el único uso que haremos de otros tipos de datos, será el de los conjuntos, por la utilidad que tiene a la hora de eliminar duplicados de una lista.

A continuación muestro un ejemplo de como se usaría un set / conjunto para eliminar los duplicados de una lista en python.

listaConDuplicados = [1,2,3,4,3,2,5,1,2,99,4,5,6,4]
print (listaConDuplicados)
listaSinDuplicados=list(set(listaConDuplicados))
print (listaSinDuplicados)

La salida en consola sería la siguiente:

[1, 2, 3, 4, 3, 2, 5, 1, 2, 99, 4, 5, 6, 4]
[1, 2, 3, 4, 5, 99, 6]

Como se puede comprobar, se obtiene una nueva lista desordenada en la que se han mantenido solo las primeras apariciones de cada elemento.

En este ejemplo se ha usado dos listas diferentes, pero se podría haber asignado el set a la lista original, perdiendo los datos originales y obteniendo en su lugar la lista sin repeticiones.

listaConDatos = [1, 2, 3, 4, 3, 2, 5, 1, 2, 99, 4, 5, 6, 4]
print (listaConDatos)
listaConDatos=list(set(listaConDatos))
print (listaConDatos)

Esta segunda opción permite un aprovechamiento mayor de la memoria por parte del script.

El limite de Excel

¿Qué hacer cuando se llega al límite de lo que puedes hacer con Microsoft Excel?

En mi caso me ha pasado.

Por un lado la necesidad de realizar cruces de listados, o funciones "sumar si" que tardan más de 15 minutos.

Por otro lado me he encontrado con tener que importar parte de un fichero de 30 Millones de líneas.

Para el primero la solución ha sido usar una base de datos, en este caso he tenido que probar entre Micrsoft Access y Mysql (en concreto una distribución xamp)

Aunque la primera es más cómoda, la potencia de la segunda es muy superior.

Para el segundo caso, he tenido que buscar algún método para extraer parte de las líneas a un nuevo fichero.

En este caso, y dado que ya tenía alguna experiencia previa, me he decantado por la creación de scripts en Python, dada la facilidad para el tratamiento de ficheros y textos, así como su posibilidad de automatizar mediante el programador de tareas.

Abriendo csv en Libreoffice Calc

Una buena solución al problema de que Microsoft Excel separe automáticamente los archivos csv por el separador de listas (en español el punto y coma) es usar otro programa.

Para ficheros no demasiado grandes se puede usar Libreoffice Calc.

La hoja de calculo Libreoffice Calc, siempre abre un menú al importar un csv, permitiendo seleccionar tanto el separador como el conjunto de caracteres del fichero (lo cual afecta sobre todo, a los caracteres acentuados, eñes y diéresis).

La forma habitual para abrir el csv será pulsar con el botón derecho del ratón, seleccionar Abrir con..., y luego LibreOffice.

Una vez seleccionado, se abre, y se muestra la ventana de importación.

Por defecto usa el mismo separador que Microsoft Excel. Como se ve en la imagen, usa por defecto dos separadores simultáneamente, el tabulador, y el punto y coma.

Si se continúa la importación, aparecerá el mismo problema que con Excel.

Cambiando la configuracion de los separadores por el pipe, que es el que usa nuestro fichero de ejemplo, se consigue una importación correcta.

Este es el resultado obtenido:

A partir de este punto, se puede continuar trabajando con Libreoffice Calc o guardar el fichero en el formato que deseemos y abrilo luego con otro programa de hoja de cálculo.


Existe un truco que nos permitirá abrir el fichero csv con Excel directamente, pero para ello es preciso realizar una modificación en el fichero con anterioridad a la importación.

Como abrir un csv correctamente con excel

Si no podemos asegurar que en nuestro fichero csv los únicos puntos y comas son los separadores, entonces, tendremos que abrir primero el Microsoft Excel y luego importar el fichero.

De esta forma  Microsoft Excel nos consultará acerca de como importar el fichero.

Para ello habrá que acceder a la pestaña Datos, Obtener datos externos, Desde archivo de texto.

A partir de este punto se nos muestra el asistente que nos guiará en los pasos de importación.

Pero en este caso, a diferencia de si lo abrimos directamente con Microsoft Excel no se ha separado previamente por los puntos y coma.

En este caso seleccionaremos como separador el caracter pipe (barra vertical de la tecla del número 1) y se importará correctamente.

Este es el resultado de la importación.

Se aprecia que en esta ocasión, a diferencia de si abrimos el csv directamente en Microsoft Excel (ver post titulado "Por qué no abrir un csv directamente con excel", los nomres de los autores del libro de la fila número 3, no se han separado en dos filas, sino que se mantienen unidos como era deseable.

Por qué no abrir un csv directamente con excel

Cuando se usa un csv separado por caracteres, se debe tener especial  cuidado a la hora de abrirlo con excel para evitar la pérdida de datos.

Esto se produce en el  caso de que en los campos se use el separador de listas por defecto de windows, el cual para castellano es el punto y coma.

Si se abre un csv que incluya algún punto y coma, excel entenderá que queremos separarlo en columnas y lo hará sin consultarnos.

A la hora de proceder a separar los datos por el separador que indiquemos, vamos a provocar la sobreescritura de los datos ya separados por Excel en el momento de la apertura del fichero.

Para el ejemplo vamos a usar este fichero de texto, separado por pipes, destacar que el libro de la fila 3 tiene dos autores, lo que será el origen de nuestros problemas:

titulo|autores|precio
El Quijote|Miguel de Cervantes|22,99
Mi libro|Yo mismo; Mi amigo|5.99
Otro libro|Otro autor|24.99

Abrimos nuestro csv con el botón derecho seleccionando "Abrir con" y luego Excel

Al abrir, a priori no se nota nada rato.

Pero si ampliamos el ancho de las columnas, comprobaremos que sin habernos solicitado permiso, excel ha localizado un punto y coma en la línea 3 y ya ha separado los datos.

Siguiendo el proceso habitual y si no nos hemos dado cuenta de esta separación, procedemos a separar los datos por pipes.

En el momento de indicar el separador, puede que notemos como que en el archivo original faltasen datos (en realidad, lo que pasa es que solo está aplicando la opción "Texto en columnas" a la columna A, con lo que el resto de los datos no los tiene en cuenta.

Luego viene un mensaje de advertencia, indicando que "Aquí hay datos" que es el indicativo de que en las columnas en las que se van a separar los datos, ya hay algo escrito.

Pero como es habitual pasaremos por alto.

Esto conducirá al resultado que muestro en la siguiente captura, y que se suele achacar a falta de datos en los ficheros originales.

Como se puede comprobar la casilla C3 se encuentra vacía, a pesar de que en el fichero original si que había datos.

El proceso completo en vídeo.

En un próximo post veremos como abrir correctamente estes ficheros tanto con Microsoft Excel como con Libreoffice Calc