Uso de Pandas para leer archivos de Excel grandes en Python

En este tutorial aprenderás a trabajar con archivos de Excel grandes en Pandas, enfocándote en leer y analizar un archivo xls y luego trabajar con un subconjunto de los datos originales.

Bono gratis: Haga clic aquí para descargar un proyecto de Python de ejemplo con código fuente que le muestra cómo leer archivos de Excel grandes.

Este tutorial utiliza Python (probado con versiones de 64 bits de v2.7.9 y v3.4.3), Pandas (v0.16.1) y XlsxWriter (v0.7.3). Recomendamos usar la distribución de Anaconda para comenzar rápidamente, ya que viene preinstalado con todas las bibliotecas necesarias.

Contenido

Leer el archivo

El primer archivo con el que trabajaremos es una recopilación de todos los accidentes automovilísticos en Inglaterra de 1979 a 2004, para extraer todos los accidentes que ocurrieron en Londres en el año 2000.

Excel

Comience descargando el archivo ZIP de origen de data.gov.uk y extraiga el contenido. A continuación, intente abrir Accidents7904.csv en Excel. Ten cuidado. Si usted no tiene suficiente memoria, esto podría muy bien bloquear su ordenador.

¿Qué pasa?

Debería ver un error «Archivo no cargado por completo», ya que Excel solo puede manejar un millón de filas a la vez.

También probamos esto en LibreOffice y recibimos un error similar: «Los datos no se pudieron cargar por completo porque se excedió el número máximo de filas por hoja».

Para solucionarlo, podemos abrir el archivo en Pandas. Antes de empezar, el código fuente está en Github.

Pandas

Dentro de un nuevo directorio de proyecto, active un virtualenv y, a continuación, instale Pandas:

$ pip install pandas==0.16.1

Ahora vamos a construir el guión. Cree un archivo llamado pandasaccidents.py y añada el siguiente código:

import pandas as pd

# Read the file
data = pd.read_csv("Accidents7904.csv", low_memory=False)

# Output the number of rows
print("Total rows: {0}".format(len(data)))

# See which headers are available
print(list(data))

Aquí, importamos Pandas, leímos en el archivo, lo que podría llevar algún tiempo, dependiendo de la cantidad de memoria que tenga su sistema, y sacamos el número total de filas que tiene el archivo, así como los encabezados disponibles (por ejemplo, títulos de columna).

Cuando corras, deberías ver:

Total rows: 6224198
['xefxbbxbfAccident_Index', 'Location_Easting_OSGR', 'Location_Northing_OSGR',
 'Longitude', 'Latitude', 'Police_Force', 'Accident_Severity', 'Number_of_Vehicles',
 'Number_of_Casualties', 'Date', 'Day_of_Week', 'Time', 'Local_Authority_(District)',
 'Local_Authority_(Highway)', '1st_Road_Class', '1st_Road_Number', 'Road_Type',
 'Speed_limit', 'Junction_Detail', 'Junction_Control', '2nd_Road_Class',
 '2nd_Road_Number', 'Pedestrian_Crossing-Human_Control',
 'Pedestrian_Crossing-Physical_Facilities', 'Light_Conditions', 'Weather_Conditions',
 'Road_Surface_Conditions', 'Special_Conditions_at_Site', 'Carriageway_Hazards',
 'Urban_or_Rural_Area', 'Did_Police_Officer_Attend_Scene_of_Accident',
 'LSOA_of_Accident_Location']

Entonces, ¡hay más de seis millones de filas! No es de extrañar que Excel se ahogara. Dirija su atención a la lista de encabezados, el primero en particular:

'xefxbbxbfAccident_Index',

Esto debería decirAccident_Index. ¿Qué pasa con el extraxefxbbxbf¿Al principio? Bueno, elxrealmente significa que el valor es hexadecimal, que es una marca de orden de bytes, lo que indica que el texto es Unicode.

¿Por qué nos importa?

No puedes asumir que los archivos que lees están limpios. Pueden contener símbolos adicionales como este que pueden deshacer tus scripts.

Este archivo es bueno, ya que de lo contrario está limpio, pero muchos archivos tienen datos faltantes, datos en formato inconsistente interno, etc. Así que cada vez que tengas un archivo para analizar, lo primero que debes hacer es limpiarlo. ¿Cuánta limpieza? Suficiente para permitirte hacer un análisis. Siga el principio de Kiss.

¿Qué tipo de limpieza podrías necesitar?

Fijar fecha/hora. El mismo archivo puede tener fechas en diferentes formatos, como los formatos estadounidense (mm-dd-yy) o europeo (dd-mm-yy). Estos deben ser llevados a un formato común.
Elimine los valores vacíos. El archivo puede tener columnas y / o filas en blanco, y esto aparecerá como NaN (No es un número) en Pandas. Pandas proporciona una forma sencilla de eliminar estos: ladropna()función. Vimos un ejemplo de esto en el último post del blog.
Elimine los valores de basura que se han abierto paso en los datos. Estos son valores que no tienen sentido (como la marca de orden de bytes que vimos anteriormente). A veces, puede ser posible trabajar a su alrededor. Por ejemplo, podría haber un conjunto de datos donde la edad se ingresó como un número de coma flotante (por error). Losint()Entonces la función podría usarse para asegurarse de que todas las edades están en formato entero.

Análisis

Para aquellos de ustedes que conocen SQL, puede usar las sentencias SELECT, WHERE y / O con diferentes palabras clave para refinar su búsqueda. Podemos hacer lo mismo en Pandas, y de una manera que sea más amigable para el programador.

Para empezar, vamos a encontrar todos los accidentes que sucedieron en un domingo. Mirando los encabezados de arriba, hay unDay_of_Weekscampo, que utilizaremos.

En el archivo ZIP que descargó, hay un archivo llamado Road-Accident-Safety-Data-Guide-1979-2004.xls, que contiene información adicional sobre los códigos utilizados. Si lo abres, verás que el domingo tiene el código1.

print("nAccidents")
print("-----------")

# Accidents which happened on a Sunday
accidents_sunday = data[data.Day_of_Week == 1]
print("Accidents which happened on a Sunday: {0}".format(
    len(accidents_sunday)))

Así de simple es.

Aquí, apuntamos elDay_of_Weekscampo y devolvió un DataFrame con la condición que comprobamos para –day of week == 1.

Cuando corras deberías ver:

Accidents
-----------
Accidents which happened on a Sunday: 693847

Como puede ver, hubo 693.847 accidentes que ocurrieron un domingo.

Vamos a hacer nuestra pregunta más complicada: Averige todos los accidentes que sucedieron en un domingo y que involucraron a más de veinte automóviles:

# Accidents which happened on a Sunday, > 20 cars
accidents_sunday_twenty_cars = data[
    (data.Day_of_Week == 1) & (data.Number_of_Vehicles > 20)]
print("Accidents which happened on a Sunday involving > 20 cars: {0}".format(
    len(accidents_sunday_twenty_cars)))

Ejecuta el guion. Ahora tenemos 10 accidentes:

Accidents
-----------
Accidents which happened on a Sunday: 693847
Accidents which happened on a Sunday involving > 20 cars: 10

Añadamos otra condición: el clima.

Abra la guía Road-Accident-Safety-Data-Guide-1979-2004.xls, y vaya a la hoja de tiempo. Verás que el código2significa, «Lluvia sin vientos fuertes».

Añádalo a nuestra consulta:

# Accidents which happened on a Sunday, > 20 cars, in the rain
accidents_sunday_twenty_cars_rain = data[
    (data.Day_of_Week == 1) & (data.Number_of_Vehicles > 20) &
    (data.Weather_Conditions == 2)]
print("Accidents which happened on a Sunday involving > 20 cars in the rain: {0}".format(
    len(accidents_sunday_twenty_cars_rain)))

Así que hubo cuatro accidentes que ocurrieron un domingo, involucrando más de veinte autos, mientras estaba lloviendo:

Accidents
-----------
Accidents which happened on a Sunday: 693847
Accidents which happened on a Sunday involving > 20 cars: 10
Accidents which happened on a Sunday involving > 20 cars in the rain: 4

Podríamos seguir haciendo esto cada vez más complicado, según sea necesario. Por ahora, nos detendremos ya que nuestro principal interés es mirar los accidentes en Londres.

Si nos fijamos en Road-Accident-Safety-Data-Guide-1979-2004.xls de nuevo, hay una hoja llamada Policía. El código para1dice: “Policía Metropolitana”. Esto es lo que más comúnmente se conoce como Scotland Yard, y es la fuerza policial responsable de la mayor parte (aunque no todo) de Londres. Para nuestro caso, esto es lo suficientemente bueno, y podemos extraer esta información como así:

# Accidents in London on a Sunday
london_data = data[data['Police_Force'] == 1 & (data.Day_of_Week == 1)]
print("nAccidents in London from 1979-2004 on a Sunday: {0}".format(
    len(london_data)))

Ejecuta el guion. Esto creó un nuevo DataFrame con los accidentes manejados por la “Policía Metropolitana” de 1979 a 2004 un domingo:

Accidents
-----------
Accidents which happened on a Sunday: 693847
Accidents which happened on a Sunday involving > 20 cars: 10
Accidents which happened on a Sunday involving > 20 cars in the rain: 4

Accidents in London from 1979-2004 on a Sunday: 114624

¿Qué pasaría si quisieras crear un nuevo DataFrame que solo contenga accidentes en el año 2000?

Lo primero que tenemos que hacer es convertir el formato de fecha a uno que Python pueda entender usando el formato de fecha.pd.to_datetime()función. Esto toma una fecha en cualquier formato y la convierte a un formato que podamos entender (aaaa-mm-dd). Entonces podemos crear otro DataFrame que solo contenga accidentes para 2000:

# Convert date to Pandas date/time
london_data_2000 = london_data[
    (pd.to_datetime(london_data['Date'], coerce=True) >
        pd.to_datetime('2000-01-01', coerce=True)) &
    (pd.to_datetime(london_data['Date'], coerce=True) <
        pd.to_datetime('2000-12-31', coerce=True))
]
print("Accidents in London in the year 2000 on a Sunday: {0}".format(
    len(london_data_2000)))

Cuando corras, deberías ver:

Accidents which happened on a Sunday: 693847
Accidents which happened on a Sunday involving > 20 cars: 10
Accidents which happened on a Sunday involving > 20 cars in the rain: 4

Accidents in London from 1979-2004 on a Sunday: 114624
Accidents in London in the year 2000 on a Sunday: 3889

Por lo tanto, esto es un poco confuso al principio. Normalmente, para filtrar un array solo usarías unforun bucle con un condicional:

for data in array:
    if data > X and data < X:
        # Do something

Sin embargo, realmente no debería definir su propio bucle, ya que muchas bibliotecas de alto rendimiento, como Pandas, tienen funciones de ayuda en su lugar. En este caso, el código anterior realiza un bucle sobre todos los elementos y filtra los datos fuera de las fechas establecidas y, a continuación, devuelve los puntos de datos que se encuentran dentro de las fechas.

¡Bien!

Conversión

Lo más probable es que, mientras usa Pandas, todos los demás en su organización estén atrapados con Excel. ¿Quieres compartir el DataFrame con los que usan Excel?

En primer lugar, tenemos que hacer una limpieza. ¿Recuerdas la marca de orden de bytes que vimos antes? Eso causa problemas al escribir estos datos en un archivo de Excel - Pandas lanza un UnicodeDecodeError. ¿Por qué? Porque el resto del texto se decodifica como ASCII, pero los valores hexadecimales no se pueden representar en ASCII.

Podríamos escribir todo como Unicode, pero recuerde que esta marca de orden de bytes es un extra innecesario (para nosotros) que no queremos ni necesitamos. Así que vamos a deshacernos de él cambiando el nombre del encabezado de la columna:

london_data_2000.rename(
    columns={'xefxbbxbfAccident_Index': 'Accident_Index'}, 
    inplace=True)

Esta es la forma de cambiar el nombre de una columna en Pandas; un poco complicado, para ser honesto.inplace = Truees necesario porque queremos modificar la estructura existente, y no crear una copia, que es lo que hace Pandas por defecto.

Ahora podemos guardar los datos en Excel:

# Save to Excel
writer = pd.ExcelWriter(
    'London_Sundays_2000.xlsx', engine='xlsxwriter')
london_data_2000.to_excel(writer, 'Sheet1')
writer.save()

Asegúrese de instalar XlsxWriter antes de ejecutar:

$ pip install XlsxWriter==0.7.3

Si todo salió bien, esto debería haber creado un archivo llamado LondonSundays2000.xlsx, y luego guardar nuestros datos en Sheet1. Abra este archivo en Excel o LibreOffice, y confirme que los datos son correctos.

Conclusión

Entonces, ¿qué logramos? Bueno, tomamos un archivo muy grande que Excel no pudo abrir y utilizamos Pandas para...

Abre el archivo.
Realice consultas de tipo SQL contra los datos.
Cree un nuevo archivo XLSX con un subconjunto de los datos originales.

Tenga en cuenta que a pesar de que este archivo es de casi 800 MB, en la era del big data, sigue siendo bastante pequeño. ¿Qué pasa si quieres abrir un archivo 4GB? Incluso si tiene 8 GB o más de RAM, eso podría no ser posible ya que gran parte de su RAM está reservada para el sistema operativo y otros procesos del sistema. De hecho, mi computadora portátil se congeló varias veces al leer por primera vez el archivo de 800 MB. Si abriera un archivo de 4GB, tendría un ataque al corazón.

Bono gratis: Haga clic aquí para descargar un proyecto de Python de ejemplo con código fuente que le muestra cómo leer archivos de Excel grandes.

Entonces, ¿cómo procedemos?

El truco es no abrir todo el archivo de una sola vez. Eso es lo que veremos en la próxima publicación del blog. Hasta entonces, analiza tus propios datos. Deja tus preguntas o comentarios a continuación. Puedes tomar el código de este tutorial desde el repositorio.

Uso de Pandas para leer archivos de Excel grandes en Python

Leer el archivo

Excel

Pandas

Análisis

Conversión

Conclusión

Enlaces Externos

Deja una respuesta Cancelar la respuesta

Uso de Pandas para leer archivos de Excel grandes en Python

Leer el archivo

Excel

Pandas

Análisis

Conversión

Conclusión

Enlaces Externos

Entradas relacionadas

Configuración de Python para el aprendizaje automático en Windows

Fundamentos de estadísticas de Python: cómo describir sus datos

Deja una respuesta Cancelar la respuesta