Este capítulo te mostrará cómo trabajar con hojas de cálculo, por ejemplo archivos de Microsoft Excel, en Python. Ya vimos cómo importar archivos csv (y tsv) en Importación de datos. En este capítulo te presentaremos herramientas para trabajar con datos en hojas de cálculo de Excel y Google Sheets.
Si tú o tus colaboradores usan hojas de cálculo para organizar datos que luego procesará una herramienta analítica como Python, te recomendamos leer el artículo “Data Organization in Spreadsheets” de Karl Broman y Kara Woo {cite}broman2018data. Las buenas prácticas que presenta este artículo te ahorrarán muchos dolores de cabeza más adelante, cuando importes los datos de una hoja de cálculo a Python para analizarlos y visualizarlos. (Para hojas de cálculo pensadas para que las lean personas, recomendamos el paquete good practice tables.)
Para este capítulo necesitarás instalar el paquete pandas. También tendrás que instalar el paquete openpyxl ejecutando uv add openpyxl en la terminal.
pandas puede leer archivos xls, xlsx, xlsm, xlsb, odf, ods y odt desde tu sistema de archivos local o desde una URL. También ofrece la opción de leer una sola hoja o una lista de hojas.
Para mostrar cómo funciona, trabajaremos con una hoja de cálculo de ejemplo llamada “students.xlsx”. La figura siguiente muestra el aspecto de la hoja de cálculo.
El primer argumento de pd.read_excel() es la ruta del archivo que se va a leer. Si has descargado el archivo en tu computadora y lo has puesto en una subcarpeta llamada “data”, deberías usar la ruta “data/students.xlsx”, pero también podemos cargarlo directamente desde la URL.
import pandas as pd
students = pd.read_excel(
"https://github.com/aeturrell/python4DS/raw/main/data/students.xlsx"
)
students| Student ID | Full Name | favourite.food | mealPlan | AGE | |
|---|---|---|---|---|---|
| 0 | 1 | Sunil Huffmann | Strawberry yoghurt | Lunch only | 4 |
| 1 | 2 | Barclay Lynn | French fries | Lunch only | 5 |
| 2 | 3 | Jayendra Lyne | NaN | Breakfast and lunch | 7 |
| 3 | 4 | Leon Rossini | Anchovies | Lunch only | NaN |
| 4 | 5 | Chidiegwu Dunkel | Pizza | Breakfast and lunch | five |
| 5 | 6 | Güvenç Attila | Ice cream | Lunch only | 6 |
Tenemos seis estudiantes en los datos y cinco variables para cada uno. Sin embargo, hay algunas cosas que quizá queramos corregir en este dataset:
snake_case, usando el argumento names.pd.read_excel(
"https://github.com/aeturrell/python4DS/raw/main/data/students.xlsx",
names=["student_id", "full_name", "favourite_food", "meal_plan", "age"],
)| student_id | full_name | favourite_food | meal_plan | age | |
|---|---|---|---|---|---|
| 0 | 1 | Sunil Huffmann | Strawberry yoghurt | Lunch only | 4 |
| 1 | 2 | Barclay Lynn | French fries | Lunch only | 5 |
| 2 | 3 | Jayendra Lyne | NaN | Breakfast and lunch | 7 |
| 3 | 4 | Leon Rossini | Anchovies | Lunch only | NaN |
| 4 | 5 | Chidiegwu Dunkel | Pizza | Breakfast and lunch | five |
| 5 | 6 | Güvenç Attila | Ice cream | Lunch only | 6 |
age se lee como una columna de objetos, pero en realidad debería ser numérica. Igual que con read_csv(), puedes pasar un argumento dtype a read_excel() y especificar los tipos de datos de las columnas que lees. Tus opciones incluyen "boolean", "int", "float", "datetime", "string" y más. Pero enseguida vemos que esto no va a funcionar con la columna “age”, ya que mezcla números y texto: así que primero necesitamos convertir su texto en números.students = pd.read_excel(
"data/students.xlsx",
names=["student_id", "full_name", "favourite_food", "meal_plan", "age"],
)
students["age"] = students["age"].replace("five", 5)
students/var/folders/8n/n8x6tb9n5tz72hhnwdf7l04w0000gn/T/ipykernel_75836/530746669.py:5: FutureWarning: Downcasting behavior in `replace` is deprecated and will be removed in a future version. To retain the old behavior, explicitly call `result.infer_objects(copy=False)`. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`
students["age"] = students["age"].replace("five", 5)| student_id | full_name | favourite_food | meal_plan | age | |
|---|---|---|---|---|---|
| 0 | 1 | Sunil Huffmann | Strawberry yoghurt | Lunch only | 4.0 |
| 1 | 2 | Barclay Lynn | French fries | Lunch only | 5.0 |
| 2 | 3 | Jayendra Lyne | NaN | Breakfast and lunch | 7.0 |
| 3 | 4 | Leon Rossini | Anchovies | Lunch only | NaN |
| 4 | 5 | Chidiegwu Dunkel | Pizza | Breakfast and lunch | 5.0 |
| 5 | 6 | Güvenç Attila | Ice cream | Lunch only | 6.0 |
Bien, ahora podemos aplicar los tipos de datos.
students = students.astype(
{
"student_id": "Int64",
"full_name": "string",
"favourite_food": "string",
"meal_plan": "category",
"age": "Int64",
}
)
students.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 6 entries, 0 to 5
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 student_id 6 non-null Int64
1 full_name 6 non-null string
2 favourite_food 5 non-null string
3 meal_plan 6 non-null category
4 age 5 non-null Int64
dtypes: Int64(2), category(1), string(2)
memory usage: 466.0 bytesHicieron falta varios pasos y algo de ensayo y error para cargar los datos exactamente en el formato que queremos, y esto no es inesperado. La ciencia de datos es un proceso iterativo. No hay forma de saber exactamente cómo serán los datos hasta que los cargas y les echas un vistazo. El patrón general que usamos es: cargar los datos, echarles un vistazo, ajustar el código, volver a cargarlos y repetir hasta que estés satisfecho con el resultado.
Una característica importante que distingue las hojas de cálculo de los archivos planos es la noción de múltiples hojas. La figura siguiente muestra una hoja de cálculo de Excel con varias hojas. Los datos provienen del dataset palmerpenguins {cite}horst2020palmerpenguins. Cada hoja contiene información sobre pingüinos de una isla distinta donde se recogieron los datos.
Puedes leer una sola hoja con el siguiente comando (para no mostrar el archivo completo, usaremos .head() para mostrar solo las primeras 5 filas):
pd.read_excel(
"https://github.com/aeturrell/python4DS/raw/main/data/penguins.xlsx",
sheet_name="Torgersen Island",
).head()| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | year | |
|---|---|---|---|---|---|---|---|---|
| 0 | Adelie | Torgersen | 39.1 | 18.7 | 181.0 | 3750.0 | male | 2007 |
| 1 | Adelie | Torgersen | 39.5 | 17.4 | 186.0 | 3800.0 | female | 2007 |
| 2 | Adelie | Torgersen | 40.3 | 18.0 | 195.0 | 3250.0 | female | 2007 |
| 3 | Adelie | Torgersen | NaN | NaN | NaN | NaN | NaN | 2007 |
| 4 | Adelie | Torgersen | 36.7 | 19.3 | 193.0 | 3450.0 | female | 2007 |
Ahora bien, esto depende de que conozcamos de antemano los nombres de las hojas. Habrá situaciones en las que quieras leer datos sin mirar antes la hoja de cálculo de Excel. Para leer todas las hojas, usa sheet_name=None. El objeto que se crea es un diccionario con pares clave-valor que son, respectivamente, los nombres de las hojas y los dataframes. Veamos el segundo par clave-valor (ten en cuenta que debemos convertir los objetos keys() y values() en listas para luego obtener el segundo elemento de cada uno mediante un índice, es decir, list(dictionary.keys())[<element number>]).
Para que veas cómo funciona, primero imprimamos todas las claves obtenidas:
penguins_dict = pd.read_excel(
"https://github.com/aeturrell/python4DS/raw/main/data/penguins.xlsx",
sheet_name=None,
)
print([x for x in penguins_dict.keys()])['Torgersen Island', 'Biscoe Island', 'Dream Island']Ahora mostremos el dataframe de la segunda entrada
print(list(penguins_dict.keys())[1])
list(penguins_dict.values())[1].head()Biscoe Island| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | year | |
|---|---|---|---|---|---|---|---|---|
| 0 | Adelie | Biscoe | 37.8 | 18.3 | 174.0 | 3400.0 | female | 2007 |
| 1 | Adelie | Biscoe | 37.7 | 18.7 | 180.0 | 3600.0 | male | 2007 |
| 2 | Adelie | Biscoe | 35.9 | 19.2 | 189.0 | 3800.0 | female | 2007 |
| 3 | Adelie | Biscoe | 38.2 | 18.1 | 185.0 | 3950.0 | male | 2007 |
| 4 | Adelie | Biscoe | 38.8 | 17.2 | 180.0 | 3800.0 | male | 2007 |
Lo que realmente queremos es que estos tres datasets coherentes estén en un mismo dataframe. Para ello, podemos usar la función pd.concat(), que concatena cualquier iterable de dataframes.
penguins = pd.concat(penguins_dict.values(), axis=0)
penguins| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | year | |
|---|---|---|---|---|---|---|---|---|
| 0 | Adelie | Torgersen | 39.1 | 18.7 | 181.0 | 3750.0 | male | 2007 |
| 1 | Adelie | Torgersen | 39.5 | 17.4 | 186.0 | 3800.0 | female | 2007 |
| 2 | Adelie | Torgersen | 40.3 | 18.0 | 195.0 | 3250.0 | female | 2007 |
| 3 | Adelie | Torgersen | NaN | NaN | NaN | NaN | NaN | 2007 |
| 4 | Adelie | Torgersen | 36.7 | 19.3 | 193.0 | 3450.0 | female | 2007 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 119 | Chinstrap | Dream | 55.8 | 19.8 | 207.0 | 4000.0 | male | 2009 |
| 120 | Chinstrap | Dream | 43.5 | 18.1 | 202.0 | 3400.0 | female | 2009 |
| 121 | Chinstrap | Dream | 49.6 | 18.2 | 193.0 | 3775.0 | male | 2009 |
| 122 | Chinstrap | Dream | 50.8 | 19.0 | 210.0 | 4100.0 | male | 2009 |
| 123 | Chinstrap | Dream | 50.2 | 18.7 | 198.0 | 3775.0 | female | 2009 |
344 rows × 8 columns
Como muchas personas usan las hojas de cálculo de Excel tanto para presentar como para almacenar datos, es bastante común encontrar en una hoja de cálculo celdas que no forman parte de los datos que quieres leer.
La figura siguiente muestra una hoja de cálculo así: en el centro de la hoja hay lo que parece un dataframe, pero hay texto ajeno en celdas por encima y por debajo de los datos.
Puedes descargar esta hoja de cálculo desde aquí o cargarla directamente desde una URL. Si quieres cargarla desde el disco de tu computadora, primero tendrás que guardarla en una subcarpeta llamada “data”.
Las tres filas superiores y las cuatro inferiores no forman parte del dataframe. Podríamos omitir las tres filas superiores con skiprows. Fíjate en que usamos skiprows=4, ya que la cuarta fila contiene los nombres de las columnas, no los datos.
pd.read_excel("data/deaths.xlsx", skiprows=4)| Name | Profession | Age | Has kids | Date of birth | Date of death | |
|---|---|---|---|---|---|---|
| 0 | David Bowie | musician | 69 | True | 1947-01-08 | 2016-01-10 00:00:00 |
| 1 | Carrie Fisher | actor | 60 | True | 1956-10-21 | 2016-12-27 00:00:00 |
| 2 | Chuck Berry | musician | 90 | True | 1926-10-18 | 2017-03-18 00:00:00 |
| 3 | Bill Paxton | actor | 61 | True | 1955-05-17 | 2017-02-25 00:00:00 |
| 4 | Prince | musician | 57 | True | 1958-06-07 | 2016-04-21 00:00:00 |
| 5 | Alan Rickman | actor | 69 | False | 1946-02-21 | 2016-01-14 00:00:00 |
| 6 | Florence Henderson | actor | 82 | True | 1934-02-14 | 2016-11-24 00:00:00 |
| 7 | Harper Lee | author | 89 | False | 1926-04-28 | 2016-02-19 00:00:00 |
| 8 | Zsa Zsa Gábor | actor | 99 | True | 1917-02-06 | 2016-12-18 00:00:00 |
| 9 | George Michael | musician | 53 | False | 1963-06-25 | 2016-12-25 00:00:00 |
| 10 | Some | NaN | NaN | NaN | NaT | NaN |
| 11 | NaN | also like to write stuff | NaN | NaN | NaT | NaN |
| 12 | NaN | NaN | at the | bottom, | NaT | NaN |
| 13 | NaN | NaN | NaN | NaN | NaT | too! |
También podríamos usar nrows para omitir las filas sobrantes de la parte inferior (otra opción sería omitir un número determinado de filas al final usando skipfooter).
pd.read_excel("data/deaths.xlsx", skiprows=4, nrows=10)| Name | Profession | Age | Has kids | Date of birth | Date of death | |
|---|---|---|---|---|---|---|
| 0 | David Bowie | musician | 69 | True | 1947-01-08 | 2016-01-10 |
| 1 | Carrie Fisher | actor | 60 | True | 1956-10-21 | 2016-12-27 |
| 2 | Chuck Berry | musician | 90 | True | 1926-10-18 | 2017-03-18 |
| 3 | Bill Paxton | actor | 61 | True | 1955-05-17 | 2017-02-25 |
| 4 | Prince | musician | 57 | True | 1958-06-07 | 2016-04-21 |
| 5 | Alan Rickman | actor | 69 | False | 1946-02-21 | 2016-01-14 |
| 6 | Florence Henderson | actor | 82 | True | 1934-02-14 | 2016-11-24 |
| 7 | Harper Lee | author | 89 | False | 1926-04-28 | 2016-02-19 |
| 8 | Zsa Zsa Gábor | actor | 99 | True | 1917-02-06 | 2016-12-18 |
| 9 | George Michael | musician | 53 | False | 1963-06-25 | 2016-12-25 |
En los archivos CSV, todos los valores son strings. Esto no es especialmente fiel a los datos, pero es sencillo: todo es un string.
Los datos subyacentes en las hojas de cálculo de Excel son más complejos. Una celda puede ser una de estas cinco cosas:
Un valor lógico, como TRUE / FALSE
Un número, como “10” o “10.5”
Una fecha, que también puede incluir la hora, como “11/1/21” o “11/1/21 3:00 PM”
Un string, como “ten”
Una moneda, que admite valores numéricos en un rango limitado y cuatro dígitos decimales de precisión fija
Al trabajar con datos de hojas de cálculo, es importante tener en cuenta que la forma en que se almacenan los datos subyacentes puede ser muy distinta de lo que ves en la celda. Por ejemplo, Excel no tiene la noción de número entero. Todos los números se almacenan como de punto flotante (números reales), pero puedes elegir mostrar los datos con un número personalizable de decimales. Del mismo modo, las fechas en realidad se almacenan como números, concretamente el número de segundos transcurridos desde el 1 de enero de 1970. Puedes personalizar cómo se muestra la fecha aplicando formato en Excel. Para mayor confusión, también es posible tener algo que parece un número pero que en realidad es un string (por ejemplo, escribe '10 en una celda de Excel).
Estas diferencias entre cómo se almacenan los datos subyacentes y cómo se muestran pueden causar sorpresas cuando los datos se cargan en herramientas analíticas como pandas. Por defecto, pandas intentará adivinar el tipo de datos de cada columna. Un flujo de trabajo recomendable es dejar que pandas adivine inicialmente los tipos de las columnas, revisarlos y luego cambiar los tipos de datos que quieras.
Creemos un pequeño dataframe que luego podamos exportar. Fíjate en que item es una categoría y quantity es un entero.
bake_sale = pd.DataFrame(
{"item": pd.Categorical(["brownie", "cupcake", "cookie"]), "quantity": [10, 5, 8]}
)
bake_sale| item | quantity | |
|---|---|---|
| 0 | brownie | 10 |
| 1 | cupcake | 5 |
| 2 | cookie | 8 |
Puedes volver a escribir los datos en disco como un archivo de Excel usando la función <dataframe>.to_excel(). El argumento de palabra clave index=False hace que solo se escriban las dos columnas, sin el índice que se añadió automáticamente en el paso anterior.
bake_sale.to_excel("data/bake_sale.xlsx", index=False)La figura siguiente muestra el aspecto de los datos en Excel.
Al igual que al leer un CSV, la información sobre el tipo de dato se pierde cuando volvemos a leer los datos; puedes comprobarlo si vuelves a leer los datos y consultas info para ver los tipos de dato. Aunque conservamos int64 porque pandas reconoce que la segunda columna era de tipo entero, perdimos el tipo de dato categórico de “item”. Esta pérdida de tipos de dato hace que los archivos de Excel no sean fiables para guardar en caché resultados intermedios.
pd.read_excel("data/bake_sale.xlsx").info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 2 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 item 3 non-null object
1 quantity 3 non-null int64
dtypes: int64(1), object(1)
memory usage: 180.0+ bytesSi necesitas más opciones de formato y más control sobre cómo escribes hojas de cálculo, consulta la documentación de openpyxl, que puede hacer prácticamente todo lo que te imagines. En general, publicar datos en hojas de cálculo no es la mejor opción; pero si de verdad quieres publicar datos en hojas de cálculo siguiendo las buenas prácticas, consulta gptables.