How to Read an Excel File using polars

Last Updated : 20 Sep, 2024

The Polars is a fast, efficient DataFrame library in Python, designed for processing large datasets with low memory usage and high performance. While Polars is more commonly used with CSV, Parquet, and JSON files, we can also work with Excel files, though this requires an additional setup as Polars does not have native support for Excel files.

In this brief tutorial, we are going to use an Excel file named "On the Rise Bakery Business Challenge.xlsx" to explore how we can use powerful tools like Polars. By the end of this tutorial, we will be able to read the petabytes of data from an Excel file directly to our terminal.

Installing Polars:

pip install polars

Method 1 - Using polars.read_excel to Read Excel Files Directly

One of the most important use-case of polars.read_excel over other manipulation libraries like Pandas is that they are performance reducers, meaning they degrade the performance as the system or dataset scales up. This method of importing Excel files to Polars is very efficient as they directly just reads the contents of an Excel file directly to the Polar Dataframe, bypassing the need to be dependent on another library like "Openxyl".

Python

import polars as pl  # Read Excel file directly into a Polars DataFrame df = pl.read_excel("On the Rise Bakery Business Challenge.xlsx")  # Display the DataFrame print(df)

Output:

Method 2 - Using Openpyxl with Polars to Read Excel Files

Step 1 - Load Excel Files Using Openpyxl

We can't use Polars to read the excel files. Instead we can use the Openpyxl library to load the Excel file and extract the data like no of tables, grids size, formulas etc., which will later be converted into Dataframe of the Polars

Python

import openpyxl  # Load the Excel file wb = openpyxl.load_workbook("On the Rise Bakery Business Challenge.xlsx")  # Select the active sheet ws = wb.active  # Extract data from the Excel sheet data = [] for row in ws.iter_rows(values_only=True):     data.append(list(row))

In the above Snippet:

We used openpyxl.load_workbook() to load the Excel file.
ws.iter_rows(values_only=True) helps to iterate over each rows and able to retrieve all the values embedded, excluding formulas and cell metadata.

Step 2 - Convert the Extracted Data into a Polars DataFrame

Once the data is extracted, for fast and efficient manipulations we use Polars to convert the list of lists into a DataFrame.

Python

# ... # Convert to Polars DataFrame df = pl.DataFrame(data[1:], schema=data[0]) print(df)

Explanation:

To convert Excel data into a polars DataFrame we use pl.DataFrame(data[1:], schema=data[0]). The first row have the column headers, which are used as the schema design for the DataFrame.

Complete Code:

Python

import openpyxl import polars as pl  # Load the Excel file using openpyxl with data_only=True to get the values instead of formulas wb = openpyxl.load_workbook("On the Rise Bakery Business Challenge.xlsx", data_only=True)  # Select the active sheet ws = wb.active  # Extract data from the Excel sheet data = [] for row in ws.iter_rows(values_only=True):     data.append(list(row))  # Convert to Polars DataFrame using schema for column names df = pl.DataFrame(data[1:], schema=data[0])  # Use 'schema' for column names print(df)

Output:

Advantages of polars.read_excel over openpyxl with polars

1) Simplicity and Efficiency:

Method 2 is much more simple to undersand and and efficient to use as it only use one function to read excel files directly into a polars DataFrame in one step. This eliminates the one read operation of Excel files as done in openpyxl (Method 1) and then converting it to polars DataFrame.

2) Performance:

Method 2 is higlhy optimized for performance as it don't require any negative overhead of reading the excel files before implementing it to polar DataFrame. which makes it performance proof function. while on other hand openpyxl first reads and then put the data into the Polars DataFrame.

3) Faster Execution with Large Datasets

Method 2(polars.read_excel) is specifically built for handling large amount datasets. Polars is made a peformance ready function while read_excel uses multiple threads and memory efficiency, enabling much faster and smooth processing when dealing with massive files.

Conclusion

In this Tutorial we learned how to read the Excel files using Polars, Openpyxl and polars.read_excel method to efficiently convert the data into DataFrame for fast and efficient data processing.

While both methods are meant for reading Excel files and converting them to DataFrame into polars DataFrames, Using any of them depends on our own use cases if our task to handle big and robust architecture we must go with polars.read_excel else go with openpyxl and polars execution with method 1.

How to import an excel file into Python using Pandas?

gautam_rana

Improve

Article Tags :

Practice Tags :

python

How to Read an Excel File using polars

Method 1 - Using polars.read_excel to Read Excel Files Directly

Method 2 - Using Openpyxl with Polars to Read Excel Files

Step 1 - Load Excel Files Using Openpyxl

Step 2 - Convert the Extracted Data into a Polars DataFrame

Complete Code:

Advantages of polars.read_excel over openpyxl with polars

1) Simplicity and Efficiency:

2) Performance:

3) Faster Execution with Large Datasets

Conclusion

Similar Reads