Loading first 100 rows of excel

Admin 1
Viewed 6942

I have a very large excel file and I'd like to only load the first 100 rows. It seems that pandas doesn't do this well, as it takes about 10 seconds to load in the following command:

pd.read_excel('excel/BigFile.xlsx', nrows=100)

It seems to take the same amount of time as not even passing in the nrows param at all. Is there a way to "quickly" read the first 100 rows of an excel file? If not in pandas, are there other tools that can do this better?

pandas excel python
2 Answers

Cause

pandas uses the xlrd package under the hood for reading out excel files. The default behaviour of xlrd seems to be to load the entire excel workbook into memory, regardless of what data is read out in the end. This would explain why you're noticing no reduction in loading time when you're using the nrows parameter of pd.read_excel().

xlrd does offer the possibility to load worksheets on demand instead, but that won't be of much help unfortunately if all your data is in one single very large excel worksheet (plus it seems that this option doesn't support .xlsx files).

Solution

The excel parsing package openpyxl does offer the possibility to load individual excel rows on demand (i.e. only the needed excel rows are loaded into memory). With a little bit of custom code, openpyxl can be harnessed to retrieve your excel data as a pandas dataframe:

import openpyxl
import pandas as pd


def read_excel(filename, nrows):
    """Read out a subset of rows from the first worksheet of an excel workbook.

    This function will not load more excel rows than necessary into memory, and is 
    therefore well suited for very large excel files.

    Parameters
    ----------
    filename : str or file-like object
        Path to excel file.
    nrows : int
        Number of rows to parse (starting at the top).

    Returns
    -------
    pd.DataFrame
        Column labels are constructed from the first row of the excel worksheet.

    """
    # Parameter `read_only=True` leads to excel rows only being loaded as-needed
    book = openpyxl.load_workbook(filename=filename, read_only=True, data_only=True)
    first_sheet = book.worksheets[0]
    rows_generator = first_sheet.values

    header_row = next(rows_generator)
    data_rows = [row for (_, row) in zip(range(nrows - 1), rows_generator)]
    return pd.DataFrame(data_rows, columns=header_row)


# USAGE EXAMPLE
dframe = read_excel('very_large_workbook.xlsx', nrows=100)

Using this code to load the first 100 rows of a >100MB single-sheet excel workbook takes just <1sec on my machine, whereas doing the same with pd.read_excel(nrows=100) takes >2min.

user_73069990

The sxl module was created expressly for this purpose. To get the first 100 rows of a worksheet:

import sxl

wb = sxl.Workbook('myfile.xlsx')
ws = wb.sheets[1]  # this gets the first sheet
data = ws.head(100)
user_958520
Related