import polars as pl
students = pl.read_excel(
"data/students.xlsx",
)
studentsSpreadsheets
Introduction
This chapter will show you how to work with spreadsheets, for example Microsoft Excel files, in Python. We already saw how to import csv (and tsv) files in Data Import. In this chapter we will introduce you to tools for working with data in Excel spreadsheets and Google Sheets.
If you or your collaborators are using spreadsheets for organising data that will be ingested by an analytical tool like Python, we recommend reading the paper “Data Organization in Spreadsheets” by Karl Broman and Kara Woo {cite}broman2018data. The best practices presented in this paper will save you much headache down the line when you import the data from a spreadsheet into Python to analyse and visualise. (For spreadsheets that are meant to be read by humans, we recommend the good practice tables package.)
Prerequisites
You will need the polars package for this chapter. Install fastexcel so read_excel() can use the default (fast) engine, xlsxwriter for write_excel(), and openpyxl if you want to use the openpyxl engine explicitly (uv add fastexcel xlsxwriter openpyxl).
Reading Excel (and Similar) Files
polars can read in xls, xlsx, xlsm, xlsb, odf, ods, and odt files from your local filesystem or from a URL. It also supports an option to read a single sheet or a list of sheets. The default reader uses fastexcel (Rust-backed, via the fastexcel Python package); you can also select other engines such as openpyxl when you need engine-specific options.
To show how this works, we’ll work with an example spreadsheet called “students.xlsx”. The figure below shows what the spreadsheet looks like.
The first argument to pl.read_excel() is the path to the file to read. If you have downloaded the file onto your computer and put it in a subfolder called “data” then you would want to use the path “data/students.xlsx” but we can also load it directly from the URL.
We have six students in the data and five variables on each student. However there are a few things we might want to address in this dataset:
- The column names are all over the place. You can rename them to follow a consistent format; we recommend
snake_case. If you want to replace every column name in order, assigning tostudents.columnsis clear and short. If you only rename some columns, use.rename({"Old Name": "new_name", ...})with the exact strings from the sheet.
students.columns = [
"student_id",
"full_name",
"favourite_food",
"meal_plan",
"age",
]
studentsagemay be inferred as strings (for example Utf8) when the column mixes numeric values and text, but we want it numeric. Just like withread_csv(), you can supply aschema_overridesargument toread_excel()and specify Polars data types for the columns you read in (for examplepl.Int64,pl.Utf8,pl.Boolean,pl.Datetime, and more). That still will not fix a value like"five"until we map it to a number first.
students = pl.read_excel("data/students.xlsx")
students.columns = [
"student_id",
"full_name",
"favourite_food",
"meal_plan",
"age",
]
students = students.with_columns(pl.col("age").replace({"five": 5}))
studentsOkay, now we can apply the data types.
students = students.with_columns(
[
pl.col("student_id").cast(pl.Int64),
pl.col("full_name").cast(pl.Utf8),
pl.col("favourite_food").cast(pl.Utf8),
pl.col("meal_plan").cast(pl.Categorical),
pl.col("age").cast(pl.Int64),
]
)
students.schemaIt took multiple steps and trial-and-error to load the data in exactly the format we want, and this is not unexpected. Data science is an iterative process. There is no way to know exactly what the data will look like until you load it and take a look at it. The general pattern we used is load the data, take a peek, make adjustments to your code, load it again, and repeat until you’re happy with the result.
Reading Individual Sheets
An important feature that distinguishes spreadsheets from flat files is the notion of multiple sheets. The figure below shows an Excel spreadsheet with multiple sheets. The data come from the palmerpenguins dataset {cite}horst2020palmerpenguins. Each sheet contains information on penguins from a different island where data were collected.
You can read a single sheet using the following command (so as not to show the whole file, we’ll use .head() to just show the first 5 rows):
pl.read_excel(
"data/penguins.xlsx",
sheet_name="Torgersen Island",
).head()Now this relies on us knowing the names of the sheets in advance. There will be situations where you want to read in data without peeking into the Excel spreadsheet. To read all sheets in Polars, use sheet_id=0 (or sheet_name=None, which also works in recent versions of Polars). The object that’s created is a dictionary where the keys are the sheet names and the values are Polars DataFrames. To access a specific sheet, you can convert the keys() or values() to a list and then index into it, ie list(dictionary.keys())[<element number>] .
To give a sense of how this works, let’s first print all of the retrieved keys:
penguins_dict = pl.read_excel(
"data/penguins.xlsx",
sheet_id=0,
)
print([x for x in penguins_dict.keys()])Now let’s show the second entry data frame
print(list(penguins_dict.keys())[1])
list(penguins_dict.values())[1].head()What we really want is these three consistent datasets to be in the same single data frame. For this, we can use the pl.concat() function. This concatenates any given iterable of data frames.
penguins = pl.concat(penguins_dict.values())
penguinsReading Part of a Sheet
Since many use Excel spreadsheets for presentation as well as for data storage, it’s quite common to find cell entries in a spreadsheet that are not part of the data you want to read in.
The figure below shows such a spreadsheet: in the middle of the sheet is what looks like a data frame but there is extraneous text in cells above and below the data.
This spreadsheet can be downloaded from here or you can load it directly from a URL. If you want to load it from your own computer’s disk, you’ll need to save it in a sub-folder called “data” first.
The top three rows and the bottom four rows are not part of the data frame. We could skip the top three rows by passing read_options to read_excel(). Note that we set skip_rows=4 since the fourth row contains column names, not the data.
pl.read_excel(
"data/deaths.xlsx",
read_options={"skip_rows": 4},
)We could also set n_rows inside read_options to omit the extraneous rows at the bottom (another option would be to skip a set number of rows at the end using skip_footer in read_options, depending on the engine).
pl.read_excel(
"data/deaths.xlsx",
read_options={"skip_rows": 4, "n_rows": 10},
)Data Types
In CSV files, all values are strings. This is not particularly true to the data, but it is simple: everything is a string.
The underlying data in Excel spreadsheets is more complex. A cell can be one of five things:
A logical, like TRUE / FALSE
A number, like “10” or “10.5”
A date, which can also include time like “11/1/21” or “11/1/21 3:00 PM”
A string, like “ten”
A currency, which allows numeric values in a limited range and four decimal digits of fixed precision
When working with spreadsheet data, it’s important to keep in mind that how the underlying data is stored can be very different than what you see in the cell. For example, Excel has no notion of an integer. All numbers are stored as floating points (real number), but you can choose to display the data with a customizable number of decimal points. Similarly, dates are actually stored as numbers, specifically the number of seconds since January 1, 1970. You can customize how you display the date by applying formatting in Excel. Confusingly, it’s also possible to have something that looks like a number but is actually a string (e.g. type '10 into a cell in Excel).
These differences between how the underlying data are stored vs. how they’re displayed can cause surprises when the data are loaded into analytical tools such as polars. By default, polars will guess the data type in a given column. A recommended workflow is to let polars guess the column types initially, inspect them, and then change any data types that you want to.
Writing to Excel
Let’s create a small data frame that we can then write out. Note that item is a category and quantity is an integer.
bake_sale = pl.DataFrame(
{
"item": pl.Series(["brownie", "cupcake", "cookie"], dtype=pl.Categorical),
"quantity": [10, 5, 8],
}
)
bake_saleYou can write data back to disk as an Excel file using the <dataframe>.write_excel() method. Polars does not use a row index like pandas, so only the columns in the DataFrame are written by default.
bake_sale.write_excel("data/bake_sale.xlsx")The figure below shows what the data looks like in Excel.
Just like reading from a CSV, information on data type is lost when we read the data back in—you can see this if you read the data back in and check the schema for the data types. Although we kept Int64 because polars recognised that the second column was of integer type, we lost the categorical data type for “item”. This data type loss makes Excel files unreliable for caching interim results.
pl.read_excel("data/bake_sale.xlsx").schemaFormatted Output
If you need more formatting options and more control over how you write spreadsheets, check out the documentation for openpyxl which can do pretty much everything you imagine. Generally, releasing data in spreadsheets is not the best option: but if you do want to release data in spreadsheets according to best practice, then check out gptables.