Repository: pandas-dev/pandas
Pull request: PERF: Optimize read_excel nrows
- [x] closes #32727
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [x] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.

I timed read_excel on a file with 10 columns and 1000 rows, and report the best of 10 repeats (ms) below. This change can make a modest improvement on xls and ods files, and a significant improvement on xlsx and xlsb files. When `nrows=None` this has no measurable impact on the run time.

| ext  | nrows | time (main) | time (this branch) |
|------|-------|------------|------------|
| xls  | None  |    22.4    |    22.1    |
| xls  | 10    |    21.4    |    17.0    |
| xlsx | None  |    99.1    |    99.7    |
| xlsx | 10    |    98.0    |     8.8    |
| xlsb | None  |    81.0    |    80.2    |
| xlsb | 10    |    80.2    |     4.8    |
| ods  | None  |   571      |   569      |
| ods  | 10    |   566      |   517      |

Here are the results of ```asv run -e -E existing --bench ReadExcel``` showing similar results (the benchmark spreadsheet is different than the one above).

```
[75.00%] ··· io.excel.ReadExcel.time_read_excel                              ok
[75.00%] ··· ========== ============
               engine               
             ---------- ------------
                xlrd     36.5±0.3ms 
              openpyxl   162±0.1ms  
                odf       688±5ms   
             ========== ============

[100.00%] ··· io.excel.ReadExcelNRows.time_read_excel                         ok
[100.00%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd     24.5±0.1ms 
               openpyxl   29.0±0.1ms 
                 odf       508±3ms   
              ========== ============
```

Problem statement:
read_excel opimize nrows
#### Code Sample

```python
pd.read_excel(fname, nrows=10)
```
#### Problem description

Pandas has option toread only several rows of excel files.
But now it always read all rows and after pandas cut some part. 
For example, file have 100 columns and 50k rows, but for test need only first 10 rows.
Now pandas will read to list all 50k rows which use memory and take too many time to read.

les this should explain **why** the current behaviour is a problem and why the expected output is a better solution.]

#### Expected Output

Better solution should be read only rows which need for operation.

as I understand there shoul be some changes

pandas/io/excel/_base.py
```python
    @abc.abstractmethod
    def get_sheet_data(self, sheet, convert_float, header, skiprows, nrows):
        pass
```
pandas/io/excel/_base.py
```python
   data = self.get_sheet_data(sheet, convert_float, header, skiprows, nrows)
```

and in files _openpyxl.py, _odfreader.py, _xlrd.py
there should be something like
```python

    def get_sheet_data(self, sheet, convert_float: bool, header: int, skiprows: int, nrows: int) -> List[List[Scalar]]:
        data = []  # type: List[List[Scalar]]
        skiprows = 0 if skiprows is None else skiprows
        header = 0 if header is None else header

        for row in sheet.rows:
            if nrows is not None:
                if header > 0:
                    #print("skip lines before header")
                    header -= 1
                    data.append(["", ""])
                    continue
    
                if skiprows > 0:
                    #print("skip skiprows after header")
                    skiprows -= 1
                    data.append(["", ""])
                    continue
    
                if nrows >= 0:
                    #print("read nrows")
                    nrows -= 1
                else:
                    break
            data.append([self._convert_cell(cell, convert_float) for cell in row])

        return data
```

With this changes read_excel with engine='openpyxl' takes only 5 seconds instead of 50 seconds of current version. And if file will contain 1kk rows, it will take always around 5 seconds, but current version will take tens of minutes.

Hints from issue discussion:
take
reverted
take
take

Commit message: Add asv test with nrows=10

Patch to review:
diff --git a/asv_bench/benchmarks/io/excel.py b/asv_bench/benchmarks/io/excel.py
index 3363b43f29b78..a2d989e787e0f 100644
--- a/asv_bench/benchmarks/io/excel.py
+++ b/asv_bench/benchmarks/io/excel.py
@@ -86,4 +86,15 @@ def time_read_excel(self, engine):
         read_excel(fname, engine=engine)
 
 
+class ReadExcelNRows(ReadExcel):
+    def time_read_excel(self, engine):
+        if engine == "xlrd":
+            fname = self.fname_excel_xls
+        elif engine == "odf":
+            fname = self.fname_odf
+        else:
+            fname = self.fname_excel
+        read_excel(fname, engine=engine, nrows=10)
+
+
 from ..pandas_vb_common import setup  # noqa: F401 isort:skip
diff --git a/doc/source/whatsnew/v1.5.0.rst b/doc/source/whatsnew/v1.5.0.rst
index 4920622a15f3f..9700b4f841066 100644
--- a/doc/source/whatsnew/v1.5.0.rst
+++ b/doc/source/whatsnew/v1.5.0.rst
@@ -451,6 +451,7 @@ Performance improvements
 - Performance improvement when setting values in a pyarrow backed string array (:issue:`46400`)
 - Performance improvement in :func:`factorize` (:issue:`46109`)
 - Performance improvement in :class:`DataFrame` and :class:`Series` constructors for extension dtype scalars (:issue:`45854`)
+- Performance improvement in :func:`read_excel` when ``nrows`` argument provided (:issue:`32727`)
 
 .. ---------------------------------------------------------------------------
 .. _whatsnew_150.bug_fixes:
diff --git a/pandas/io/excel/_base.py b/pandas/io/excel/_base.py
index 030ae9fefda98..44d140a9e88c1 100644
--- a/pandas/io/excel/_base.py
+++ b/pandas/io/excel/_base.py
@@ -563,7 +563,7 @@ def get_sheet_by_index(self, index: int):
         pass
 
     @abc.abstractmethod
-    def get_sheet_data(self, sheet, convert_float: bool):
+    def get_sheet_data(self, sheet, convert_float: bool, rows: int | None = None):
         pass
 
     def raise_if_bad_sheet_by_index(self, index: int) -> None:
@@ -577,6 +577,51 @@ def raise_if_bad_sheet_by_name(self, name: str) -> None:
         if name not in self.sheet_names:
             raise ValueError(f"Worksheet named '{name}' not found")
 
+    def _check_skiprows_func(self, skiprows, rows_to_use):
+        """
+        See how many file rows are required when `skiprows` is callable
+        """
+        i = 0
+        rows_used_so_far = 0
+        while rows_used_so_far < rows_to_use:
+            if not skiprows(i):
+                rows_used_so_far += 1
+            i += 1
+        return i
+
+    def _calc_rows(self, header, index_col, skiprows, nrows):
+        """
+        If nrows specified, find the number of rows needed from the file
+        """
+        if nrows is None:
+            return
+        if not isinstance(nrows, int) or nrows < 0:
+            raise ValueError("'nrows' must be an integer >=0")
+        if header is None:
+            header_rows = 1
+        elif isinstance(header, int):
+            header_rows = 1 + header
+        else:
+            header_rows = 1 + header[-1]
+        # If there is a MultiIndex header and an index then there is also
+        # a row containing just the index name(s)
+        if is_list_like(header) and len(header) > 1 and index_col is not None:
+            header_rows += 1
+        if skiprows is None:
+            return header_rows + nrows
+        if isinstance(skiprows, int):
+            return header_rows + nrows + skiprows
+        if is_list_like(skiprows):
+            return self._check_skiprows_func(
+                lambda x: x in skiprows,
+                header_rows + nrows,
+            )
+        if callable(skiprows):
+            return self._check_skiprows_func(
+                skiprows,
+                header_rows + nrows,
+            )
+
     def parse(
         self,
         sheet_name: str | int | list[int] | list[str] | None = 0,
@@ -643,7 +688,8 @@ def parse(
             else:  # assume an integer if not a string
                 sheet = self.get_sheet_by_index(asheetname)
 
-            data = self.get_sheet_data(sheet, convert_float)
+            file_rows_needed = self._calc_rows(header, index_col, skiprows, nrows)
+            data = self.get_sheet_data(sheet, convert_float, file_rows_needed)
             if hasattr(sheet, "close"):
                 # pyxlsb opens two TemporaryFiles
                 sheet.close()
diff --git a/pandas/io/excel/_odfreader.py b/pandas/io/excel/_odfreader.py
index 5a7e5b0d8d325..075590f3535fe 100644
--- a/pandas/io/excel/_odfreader.py
+++ b/pandas/io/excel/_odfreader.py
@@ -90,7 +90,7 @@ def get_sheet_by_name(self, name: str):
         raise ValueError(f"sheet {name} not found")
 
     def get_sheet_data(
-        self, sheet, convert_float: bool
+        self, sheet, convert_float: bool, file_rows_needed: int | None = None
     ) -> list[list[Scalar | NaTType]]:
         """
         Parse an ODF Table into a list of lists
@@ -148,6 +148,8 @@ def get_sheet_data(
                 empty_rows = 0
                 for _ in range(row_repeat):
                     table.append(table_row)
+            if file_rows_needed is not None and len(table) >= file_rows_needed:
+                break
 
         # Make our table square
         for row in table:
diff --git a/pandas/io/excel/_openpyxl.py b/pandas/io/excel/_openpyxl.py
index 6d70b3f319f37..8f4201d0befff 100644
--- a/pandas/io/excel/_openpyxl.py
+++ b/pandas/io/excel/_openpyxl.py
@@ -588,7 +588,9 @@ def _convert_cell(self, cell, convert_float: bool) -> Scalar:
 
         return cell.value
 
-    def get_sheet_data(self, sheet, convert_float: bool) -> list[list[Scalar]]:
+    def get_sheet_data(
+        self, sheet, convert_float: bool, file_rows_needed: int | None = None
+    ) -> list[list[Scalar]]:
 
         if self.book.read_only:
             sheet.reset_dimensions()
@@ -603,6 +605,8 @@ def get_sheet_data(self, sheet, convert_float: bool) -> list[list[Scalar]]:
             if converted_row:
                 last_row_with_data = row_number
             data.append(converted_row)
+            if file_rows_needed is not None and len(data) >= file_rows_needed:
+                break
 
         # Trim trailing empty rows
         data = data[: last_row_with_data + 1]
diff --git a/pandas/io/excel/_pyxlsb.py b/pandas/io/excel/_pyxlsb.py
index 36e2645560078..5d40ccdf2f8f3 100644
--- a/pandas/io/excel/_pyxlsb.py
+++ b/pandas/io/excel/_pyxlsb.py
@@ -79,7 +79,12 @@ def _convert_cell(self, cell, convert_float: bool) -> Scalar:
 
         return cell.v
 
-    def get_sheet_data(self, sheet, convert_float: bool) -> list[list[Scalar]]:
+    def get_sheet_data(
+        self,
+        sheet,
+        convert_float: bool,
+        file_rows_needed: int | None = None,
+    ) -> list[list[Scalar]]:
         data: list[list[Scalar]] = []
         prevous_row_number = -1
         # When sparse=True the rows can have different lengths and empty rows are
@@ -94,6 +99,8 @@ def get_sheet_data(self, sheet, convert_float: bool) -> list[list[Scalar]]:
                 data.extend([[]] * (row_number - prevous_row_number - 1))
                 data.append(converted_row)
                 prevous_row_number = row_number
+            if file_rows_needed is not None and len(data) >= file_rows_needed:
+                break
         if data:
             # extend rows to max_width
             max_width = max(len(data_row) for data_row in data)
diff --git a/pandas/io/excel/_xlrd.py b/pandas/io/excel/_xlrd.py
index f38a05e7a4e64..0bf3ac6134cf6 100644
--- a/pandas/io/excel/_xlrd.py
+++ b/pandas/io/excel/_xlrd.py
@@ -1,8 +1,13 @@
+from __future__ import annotations
+
 from datetime import time
 
 import numpy as np
 
-from pandas._typing import StorageOptions
+from pandas._typing import (
+    Scalar,
+    StorageOptions,
+)
 from pandas.compat._optional import import_optional_dependency
 from pandas.util._decorators import doc
 
@@ -56,7 +61,9 @@ def get_sheet_by_index(self, index):
         self.raise_if_bad_sheet_by_index(index)
         return self.book.sheet_by_index(index)
 
-    def get_sheet_data(self, sheet, convert_float):
+    def get_sheet_data(
+        self, sheet, convert_float: bool, file_rows_needed: int | None = None
+    ) -> list[list[Scalar]]:
         from xlrd import (
             XL_CELL_BOOLEAN,
             XL_CELL_DATE,
@@ -107,7 +114,10 @@ def _parse_cell(cell_contents, cell_typ):
 
         data = []
 
-        for i in range(sheet.nrows):
+        nrows = sheet.nrows
+        if file_rows_needed is not None:
+            nrows = min(nrows, file_rows_needed)
+        for i in range(nrows):
             row = [
                 _parse_cell(value, typ)
                 for value, typ in zip(sheet.row_values(i), sheet.row_types(i))
diff --git a/pandas/tests/io/excel/test_readers.py b/pandas/tests/io/excel/test_readers.py
index 1e0f74ea41453..23d24b0428499 100644
--- a/pandas/tests/io/excel/test_readers.py
+++ b/pandas/tests/io/excel/test_readers.py
@@ -1219,6 +1219,97 @@ def test_read_excel_nrows_non_integer_parameter(self, read_ext):
         with pytest.raises(ValueError, match=msg):
             pd.read_excel("test1" + read_ext, nrows="5")
 
+    def test_read_excel_nrows_mi_column(self, read_ext):
+        expected = pd.read_excel(
+            "testmultiindex" + read_ext,
+            sheet_name="mi_column",
+            header=[0, 1],
+            index_col=0,
+        ).iloc[:3]
+        actual = pd.read_excel(
+            "testmultiindex" + read_ext,
+            sheet_name="mi_column",
+            header=[0, 1],
+            index_col=0,
+            nrows=3,
+        )
+        tm.assert_frame_equal(actual, expected)
+
+    def test_read_excel_nrows_mi_index(self, read_ext):
+        expected = pd.read_excel(
+            "testmultiindex" + read_ext,
+            sheet_name="mi_index",
+            index_col=[0, 1],
+        ).iloc[:3]
+        actual = pd.read_excel(
+            "testmultiindex" + read_ext,
+            sheet_name="mi_index",
+            index_col=[0, 1],
+            nrows=3,
+        )
+        tm.assert_frame_equal(actual, expected)
+
+    def test_read_excel_nrows_mi_both(self, read_ext):
+        expected = pd.read_excel(
+            "testmultiindex" + read_ext,
+            sheet_name="both",
+            header=[0, 1],
+            index_col=[0, 1],
+        ).iloc[:3]
+        actual = pd.read_excel(
+            "testmultiindex" + read_ext,
+            sheet_name="both",
+            header=[0, 1],
+            index_col=[0, 1],
+            nrows=3,
+        )
+        tm.assert_frame_equal(actual, expected)
+
+    def test_read_excel_nrows_mi_column_name(self, read_ext):
+        expected = pd.read_excel(
+            "testmultiindex" + read_ext,
+            sheet_name="mi_column_name",
+            header=[0, 1],
+            index_col=0,
+        ).iloc[:3]
+        actual = pd.read_excel(
+            "testmultiindex" + read_ext,
+            sheet_name="mi_column_name",
+            header=[0, 1],
+            index_col=0,
+            nrows=3,
+        )
+        tm.assert_frame_equal(actual, expected)
+
+    def test_read_excel_nrows_skiprows_list(self, read_ext):
+        expected = pd.read_excel(
+            "testskiprows" + read_ext,
+            sheet_name="skiprows_list",
+            skiprows=[0, 2],
+        ).iloc[:3]
+        actual = pd.read_excel(
+            "testskiprows" + read_ext,
+            sheet_name="skiprows_list",
+            skiprows=[0, 2],
+            nrows=3,
+        )
+        tm.assert_frame_equal(actual, expected)
+
+    def test_read_excel_nrows_skiprows_func(self, read_ext):
+        func = lambda x: x == 0 or x == 2
+        expected = pd.read_excel(
+            "testskiprows" + read_ext,
+            sheet_name="skiprows_list",
+            skiprows=func,
+        ).iloc[:3]
+        actual = pd.read_excel(
+            "testskiprows" + read_ext,
+            sheet_name="skiprows_list",
+            skiprows=func,
+            nrows=3,
+        )
+        tm.assert_frame_equal(actual, expected)
+
     def test_read_excel_squeeze(self, read_ext):
         # GH 12157
         f = "test_squeeze" + read_ext


Code review comments:
diff_hunk: @@ -577,6 +577,51 @@ def raise_if_bad_sheet_by_name(self, name: str) -> None:
         if name not in self.sheet_names:
             raise ValueError(f"Worksheet named '{name}' not found")
 
+    def _check_skiprows_func(self, skiprows, rows_to_use):
+        """
+        See how many file rows are required when `skiprows` is callable
+        """
+        i = 0
+        rows_used_so_far = 0
+        while rows_used_so_far < rows_to_use:
+            if not skiprows(i):
+                rows_used_so_far += 1
+            i += 1
+        return i
+
+    def _calc_rows(self, header, index_col, skiprows, nrows):
+        """
+        If nrows specified, find the number of rows needed from the file
+        """
+        if nrows is None:
+            return
+        if not isinstance(nrows, int) or nrows < 0:
+            raise ValueError("'nrows' must be an integer >=0")
+        if header is None:
+            header_rows = 1
+        elif isinstance(header, int):
+            header_rows = 1 + header
+        else:
+            header_rows = 1 + header[-1]
+        # If there is a MultiIndex header and an index then there is also
+        # a row containing just the index name(s)
+        if is_list_like(header) and len(header) > 1 and index_col is not None:
+            header_rows += 1
+        if skiprows is None:
+            return header_rows + nrows
+        if isinstance(skiprows, int):
+            return header_rows + nrows + skiprows
+        if is_list_like(skiprows):
+            return self._check_skiprows_func(
+                lambda x: x in skiprows,
+                header_rows + nrows,
+            )
+        if callable(skiprows):
+            return self._check_skiprows_func(
+                skiprows,
+                header_rows + nrows,
+            )
path: pandas/io/excel/_base.py
text: @user1:
Is skiprows validated? If it isn't caught by one of these if statements then you'll be returning None and I'd guess that would raise an odd error message to the user.

@author:
If it returns None, no nrows optimization is attempted. I added a comment for clarity.

diff_hunk: @@ -577,6 +577,51 @@ def raise_if_bad_sheet_by_name(self, name: str) -> None:
         if name not in self.sheet_names:
             raise ValueError(f"Worksheet named '{name}' not found")
 
+    def _check_skiprows_func(self, skiprows, rows_to_use):
+        """
+        See how many file rows are required when `skiprows` is callable
+        """
+        i = 0
+        rows_used_so_far = 0
+        while rows_used_so_far < rows_to_use:
+            if not skiprows(i):
+                rows_used_so_far += 1
+            i += 1
+        return i
+
+    def _calc_rows(self, header, index_col, skiprows, nrows):
+        """
+        If nrows specified, find the number of rows needed from the file
+        """
+        if nrows is None:
+            return
+        if not isinstance(nrows, int) or nrows < 0:
path: pandas/io/excel/_base.py
text: @user1:
we have a validator for this (i think in the parser somewhere)

@author:
Thanks! Good call.

diff_hunk: @@ -577,6 +577,51 @@ def raise_if_bad_sheet_by_name(self, name: str) -> None:
         if name not in self.sheet_names:
             raise ValueError(f"Worksheet named '{name}' not found")
 
+    def _check_skiprows_func(self, skiprows, rows_to_use):
+        """
+        See how many file rows are required when `skiprows` is callable
+        """
+        i = 0
+        rows_used_so_far = 0
+        while rows_used_so_far < rows_to_use:
+            if not skiprows(i):
+                rows_used_so_far += 1
+            i += 1
+        return i
+
+    def _calc_rows(self, header, index_col, skiprows, nrows):
+        """
+        If nrows specified, find the number of rows needed from the file
+        """
+        if nrows is None:
+            return
+        if not isinstance(nrows, int) or nrows < 0:
+            raise ValueError("'nrows' must be an integer >=0")
path: pandas/io/excel/_base.py
text: @user1:
I think the error message should specify that nrows is also allowed to be None. Is it better to have this validation in `pd.read_excel` itself?

@author:
I'll use validate_integer

diff_hunk: @@ -1219,6 +1219,97 @@ def test_read_excel_nrows_non_integer_parameter(self, read_ext):
         with pytest.raises(ValueError, match=msg):
             pd.read_excel("test1" + read_ext, nrows="5")
 
+    def test_read_excel_nrows_mi_column(self, read_ext):
path: pandas/tests/io/excel/test_readers.py
text: @user1:
Can you parametrize these tests using kwargs? I _think_ that's the only thing that's changing between them?

@user1:
Also, add PR # as a comment.

@author:
yes, that's much better

diff_hunk: @@ -577,6 +577,51 @@ def raise_if_bad_sheet_by_name(self, name: str) -> None:
         if name not in self.sheet_names:
             raise ValueError(f"Worksheet named '{name}' not found")
 
+    def _check_skiprows_func(self, skiprows, rows_to_use):
+        """
+        See how many file rows are required when `skiprows` is callable
+        """
+        i = 0
+        rows_used_so_far = 0
+        while rows_used_so_far < rows_to_use:
+            if not skiprows(i):
+                rows_used_so_far += 1
+            i += 1
+        return i
+
+    def _calc_rows(self, header, index_col, skiprows, nrows):
+        """
+        If nrows specified, find the number of rows needed from the file
+        """
+        if nrows is None:
+            return
+        if not isinstance(nrows, int) or nrows < 0:
+            raise ValueError("'nrows' must be an integer >=0")
+        if header is None:
+            header_rows = 1
+        elif isinstance(header, int):
+            header_rows = 1 + header
+        else:
+            header_rows = 1 + header[-1]
+        # If there is a MultiIndex header and an index then there is also
+        # a row containing just the index name(s)
+        if is_list_like(header) and len(header) > 1 and index_col is not None:
+            header_rows += 1
+        if skiprows is None:
+            return header_rows + nrows
+        if isinstance(skiprows, int):
path: pandas/io/excel/_base.py
text: @user1:
is_integer again

diff_hunk: @@ -577,6 +577,51 @@ def raise_if_bad_sheet_by_name(self, name: str) -> None:
         if name not in self.sheet_names:
             raise ValueError(f"Worksheet named '{name}' not found")
 
+    def _check_skiprows_func(self, skiprows, rows_to_use):
+        """
+        See how many file rows are required when `skiprows` is callable
+        """
+        i = 0
+        rows_used_so_far = 0
+        while rows_used_so_far < rows_to_use:
+            if not skiprows(i):
+                rows_used_so_far += 1
+            i += 1
+        return i
+
+    def _calc_rows(self, header, index_col, skiprows, nrows):
+        """
+        If nrows specified, find the number of rows needed from the file
+        """
+        if nrows is None:
+            return
+        if not isinstance(nrows, int) or nrows < 0:
+            raise ValueError("'nrows' must be an integer >=0")
+        if header is None:
+            header_rows = 1
+        elif isinstance(header, int):
path: pandas/io/excel/_base.py
text: @user1:
Use pandas.core.dtypes.common.is_integer here to handle np.int64 et al