Repository: pandas-dev/pandas
Pull request: ENH: Optimize nrows in read_excel
- [ ] closes #32727
- [ ] tests added / passed
- [ ] passes `black pandas`
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry

based on #33281

----

output of asv benchmarks:

```
(pandas-dev) marco@marco-Predator-PH315-52:~/pandas-dev/asv_bench$ asv continuous -f 1.1 upstream/master optimise-nrows-excel -b excel.ReadExcel
· Creating environments..................................................................................................................................
· Discovering benchmarks
·· Uninstalling from conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
·· Building d0a8a687 <optimise-nrows-excel> for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt....................................
·· Installing d0a8a687 <optimise-nrows-excel> into conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
· Running 4 total benchmarks (2 commits * 1 environments * 2 benchmarks)
[  0.00%] · For pandas commit c413df6d <master> (round 1/2):
[  0.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt....................................
[  0.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 12.50%] ··· Setting up io.excel:62                                                                                                                                                                               ok
[ 12.50%] ··· Running (io.excel.ReadExcel.time_read_excel--)..
[ 25.00%] · For pandas commit d0a8a687 <optimise-nrows-excel> (round 1/2):
[ 25.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
[ 25.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 37.50%] ··· Setting up io.excel:62                                                                                                                                                                               ok
[ 37.50%] ··· Running (io.excel.ReadExcel.time_read_excel--)..
[ 50.00%] · For pandas commit d0a8a687 <optimise-nrows-excel> (round 2/2):
[ 50.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 62.50%] ··· Setting up io.excel:62                                                                                                                                                                               ok
[ 62.50%] ··· io.excel.ReadExcel.time_read_excel                                                                                                                                                                   ok
[ 62.50%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      953±6ms   
               openpyxl   1.66±0.03s 
                 odf      6.02±0.02s 
              ========== ============

[ 75.00%] ··· io.excel.ReadExcel.time_read_excel_nrows                                                                                                                                                             ok
[ 75.00%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      878±20ms  
               openpyxl   1.67±0.02s 
                 odf      4.58±0.04s 
              ========== ============

[ 75.00%] · For pandas commit c413df6d <master> (round 2/2):
[ 75.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
[ 75.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 87.50%] ··· Setting up io.excel:62                                                                                                                                                                               ok
[ 87.50%] ··· io.excel.ReadExcel.time_read_excel                                                                                                                                                                   ok
[ 87.50%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      941±5ms   
               openpyxl   1.69±0.02s 
                 odf      6.15±0.04s 
              ========== ============

[100.00%] ··· io.excel.ReadExcel.time_read_excel_nrows                                                                                                                                                             ok
[100.00%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      971±20ms  
               openpyxl   1.69±0.01s 
                 odf      6.07±0.03s 
              ========== ============

       before           after         ratio
     [c413df6d]       [d0a8a687]
     <master>         <optimise-nrows-excel>
-        971±20ms         878±20ms     0.90  io.excel.ReadExcel.time_read_excel_nrows('xlrd')

SOME BENCHMARKS HAVE CHANGED SIGNIFICANTLY.
PERFORMANCE INCREASED.
```

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

Commit message: :pencil:

Patch to review:
diff --git a/asv_bench/benchmarks/io/excel.py b/asv_bench/benchmarks/io/excel.py
index 80af2cff41769..1eaccb9f2d897 100644
--- a/asv_bench/benchmarks/io/excel.py
+++ b/asv_bench/benchmarks/io/excel.py
@@ -11,7 +11,7 @@
 
 
 def _generate_dataframe():
-    N = 2000
+    N = 20000
     C = 5
     df = DataFrame(
         np.random.randn(N, C),
@@ -69,5 +69,9 @@ def time_read_excel(self, engine):
         fname = self.fname_odf if engine == "odf" else self.fname_excel
         read_excel(fname, engine=engine)
 
+    def time_read_excel_nrows(self, engine):
+        fname = self.fname_odf if engine == "odf" else self.fname_excel
+        read_excel(fname, engine=engine, nrows=1)
+
 
 from ..pandas_vb_common import setup  # noqa: F401 isort:skip
diff --git a/doc/source/whatsnew/v1.1.0.rst b/doc/source/whatsnew/v1.1.0.rst
index a49b29d691692..f8f482e402c5f 100644
--- a/doc/source/whatsnew/v1.1.0.rst
+++ b/doc/source/whatsnew/v1.1.0.rst
@@ -869,6 +869,7 @@ Performance improvements
 - Performance improvement in arithmetic operations (``sub``, ``add``, ``mul``, ``div``) for :class:`MultiIndex` (:issue:`34297`)
 - Performance improvement in ``DataFrame[bool_indexer]`` when ``bool_indexer`` is a ``list`` (:issue:`33924`)
 - Significant performance improvement of :meth:`io.formats.style.Styler.render` with styles added with various ways such as :meth:`io.formats.style.Styler.apply`, :meth:`io.formats.style.Styler.applymap` or :meth:`io.formats.style.Styler.bar` (:issue:`19917`)
+- Performance improvement in `read_excel` for integer ``header``, ``skiprows``, and ``nrows`` (:issue:`33281`).
 
 .. ---------------------------------------------------------------------------
 
diff --git a/pandas/io/excel/_base.py b/pandas/io/excel/_base.py
index 3cd0d721bbdc6..4fb42ca2a0497 100644
--- a/pandas/io/excel/_base.py
+++ b/pandas/io/excel/_base.py
@@ -3,12 +3,12 @@
 from io import BufferedIOBase, BytesIO, RawIOBase
 import os
 from textwrap import fill
-from typing import Any, Mapping, Union
+from typing import Any, List, Mapping, Optional, Sequence, Union
 
 from pandas._config import config
 
 from pandas._libs.parsers import STR_NA_VALUES
-from pandas._typing import StorageOptions
+from pandas._typing import Scalar, StorageOptions
 from pandas.errors import EmptyDataError
 from pandas.util._decorators import Appender, deprecate_nonkeyword_arguments
 
@@ -397,9 +397,47 @@ def get_sheet_by_index(self, index):
         pass
 
     @abc.abstractmethod
-    def get_sheet_data(self, sheet, convert_float):
+    def get_sheet_data(
+        self,
+        sheet,
+        convert_float: bool,
+        header: Optional[Union[int, Sequence[int]]],
+        skiprows: Optional[Union[int, Sequence[int]]],
+        nrows: Optional[int],
+    ) -> List[List[Scalar]]:
         pass
 
+    def should_skip_row(
+        self,
+        index: int,
+        header: Optional[Union[int, Sequence[int]]],
+        skiprows: Optional[Union[int, Sequence[int]]],
+        nrows: Optional[int],
+    ) -> bool:
+        """
+        Determines whether row should be skipped.
+
+        Parameters
+        ----------
+        index : int
+            Index of row.
+        header : int, list of int
+            Rows used as column labels.
+        skiprows : int, list of int
+            Rows to skip at the begining.
+        nrows : int
+            Number of rows to parse.
+
+        Returns
+        -------
+        bool
+            Determines if row should be skipped.
+        """
+        if nrows is not None and isinstance(header, int) and isinstance(skiprows, int):
+            if index < header + skiprows - 1:
+                return True
+        return False
+
     def parse(
         self,
         sheet_name=0,
@@ -453,7 +491,20 @@ 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)
+            get_sheet_data_header = 0 if header is None else header
+            get_sheet_data_skiprows = 0 if skiprows is None else skiprows
+            get_sheet_data_nrows = nrows if isinstance(nrows, int) else None
+            if isinstance(get_sheet_data_header, list) or isinstance(
+                get_sheet_data_skiprows, list
+            ):
+                get_sheet_data_nrows = None
+            data = self.get_sheet_data(
+                sheet,
+                convert_float,
+                get_sheet_data_header,
+                get_sheet_data_skiprows,
+                get_sheet_data_nrows,
+            )
             usecols = _maybe_convert_usecols(usecols)
 
             if not data:
diff --git a/pandas/io/excel/_odfreader.py b/pandas/io/excel/_odfreader.py
index 6cbca59aed97e..791580109f1ed 100644
--- a/pandas/io/excel/_odfreader.py
+++ b/pandas/io/excel/_odfreader.py
@@ -1,8 +1,8 @@
-from typing import List, cast
+from typing import List, Optional, Sequence, cast
 
 import numpy as np
 
-from pandas._typing import FilePathOrBuffer, Scalar, StorageOptions
+from pandas._typing import FilePathOrBuffer, Scalar, StorageOptions, Union
 from pandas.compat._optional import import_optional_dependency
 
 import pandas as pd
@@ -71,7 +71,14 @@ def get_sheet_by_name(self, name: str):
 
         raise ValueError(f"sheet {name} not found")
 
-    def get_sheet_data(self, sheet, convert_float: bool) -> List[List[Scalar]]:
+    def get_sheet_data(
+        self,
+        sheet,
+        convert_float: bool,
+        header: Optional[Union[int, Sequence[int]]],
+        skiprows: Optional[Union[int, Sequence[int]]],
+        nrows: Optional[int],
+    ) -> List[List[Scalar]]:
         """
         Parse an ODF Table into a list of lists
         """
@@ -87,7 +94,12 @@ def get_sheet_data(self, sheet, convert_float: bool) -> List[List[Scalar]]:
 
         table: List[List[Scalar]] = []
 
+        if nrows is not None and isinstance(header, int) and isinstance(skiprows, int):
+            sheet_rows = sheet_rows[: header + skiprows + nrows + 1]
         for i, sheet_row in enumerate(sheet_rows):
+            if self.should_skip_row(i, header, skiprows, nrows):
+                table.append([])
+                continue
             sheet_cells = [x for x in sheet_row.childNodes if x.qname in cell_names]
             empty_cells = 0
             table_row: List[Scalar] = []
diff --git a/pandas/io/excel/_openpyxl.py b/pandas/io/excel/_openpyxl.py
index c2730536af8a3..0224a5885a470 100644
--- a/pandas/io/excel/_openpyxl.py
+++ b/pandas/io/excel/_openpyxl.py
@@ -1,8 +1,8 @@
-from typing import List
+from typing import List, Optional, Sequence
 
 import numpy as np
 
-from pandas._typing import FilePathOrBuffer, Scalar, StorageOptions
+from pandas._typing import FilePathOrBuffer, Scalar, StorageOptions, Union
 from pandas.compat._optional import import_optional_dependency
 
 from pandas.io.excel._base import ExcelWriter, _BaseExcelReader
@@ -535,7 +535,14 @@ 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,
+        header: Optional[Union[int, Sequence[int]]],
+        skiprows: Optional[Union[int, Sequence[int]]],
+        nrows: Optional[int],
+    ) -> List[List[Scalar]]:
         data: List[List[Scalar]] = []
         for row in sheet.rows:
             data.append([self._convert_cell(cell, convert_float) for cell in row])
diff --git a/pandas/io/excel/_pyxlsb.py b/pandas/io/excel/_pyxlsb.py
index c15a52abe4d53..b4e5553817c81 100644
--- a/pandas/io/excel/_pyxlsb.py
+++ b/pandas/io/excel/_pyxlsb.py
@@ -1,6 +1,6 @@
-from typing import List
+from typing import List, Optional, Sequence
 
-from pandas._typing import FilePathOrBuffer, Scalar, StorageOptions
+from pandas._typing import FilePathOrBuffer, Scalar, StorageOptions, Union
 from pandas.compat._optional import import_optional_dependency
 
 from pandas.io.excel._base import _BaseExcelReader
@@ -68,7 +68,14 @@ 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,
+        header: Optional[Union[int, Sequence[int]]],
+        skiprows: Optional[Union[int, Sequence[int]]],
+        nrows: Optional[int],
+    ) -> List[List[Scalar]]:
         return [
             [self._convert_cell(c, convert_float) for c in r]
             for r in sheet.rows(sparse=False)
diff --git a/pandas/io/excel/_xlrd.py b/pandas/io/excel/_xlrd.py
index a7fb519af61c6..dd9f2682930ec 100644
--- a/pandas/io/excel/_xlrd.py
+++ b/pandas/io/excel/_xlrd.py
@@ -1,8 +1,9 @@
 from datetime import time
+from typing import List, Optional, Sequence
 
 import numpy as np
 
-from pandas._typing import StorageOptions
+from pandas._typing import Scalar, StorageOptions, Union
 from pandas.compat._optional import import_optional_dependency
 
 from pandas.io.excel._base import _BaseExcelReader
@@ -49,7 +50,14 @@ def get_sheet_by_name(self, name):
     def get_sheet_by_index(self, 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,
+        header: Optional[Union[int, Sequence[int]]],
+        skiprows: Optional[Union[int, Sequence[int]]],
+        nrows: Optional[int],
+    ) -> List[List[Scalar]]:
         from xlrd import (
             XL_CELL_BOOLEAN,
             XL_CELL_DATE,
@@ -98,9 +106,16 @@ def _parse_cell(cell_contents, cell_typ):
                     cell_contents = val
             return cell_contents
 
-        data = []
+        data: List[List[Scalar]] = []
 
-        for i in range(sheet.nrows):
+        sheet_nrows = sheet.nrows
+        if nrows is not None and isinstance(header, int) and isinstance(skiprows, int):
+            sheet_nrows = min(header + skiprows + nrows + 1, sheet_nrows)
+
+        for i in range(sheet_nrows):
+            if self.should_skip_row(i, header, skiprows, nrows):
+                data.append([])
+                continue
             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 431a50477fccc..3b697fa5b5f2b 100644
--- a/pandas/tests/io/excel/test_readers.py
+++ b/pandas/tests/io/excel/test_readers.py
@@ -1153,5 +1153,19 @@ def test_read_datetime_multiindex(self, engine, read_ext):
             ],
         )
         expected = pd.DataFrame([], columns=expected_column_index)
+        tm.assert_frame_equal(expected, actual)
 
+    @pytest.mark.parametrize("header, skiprows", [(1, 2), (0, 3)])
+    @td.check_file_leaks
+    def test_header_skiprows_nrows(self, engine, read_ext, header, skiprows):
+        # GH 32727
+        data = pd.read_excel("test1" + read_ext, engine=engine)
+        expected = (
+            DataFrame(data.iloc[3:6])
+            .reset_index(drop=True)
+            .rename(columns=data.iloc[2].rename(None))
+        )
+        actual = pd.read_excel(
+            "test1" + read_ext, engine=engine, header=header, skiprows=skiprows, nrows=3
+        )
         tm.assert_frame_equal(expected, actual)


Code review comments:
diff_hunk: @@ -453,7 +491,20 @@ 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)
+            get_sheet_data_header = 0 if header is None else header
+            get_sheet_data_skiprows = 0 if skiprows is None else skiprows
+            get_sheet_data_nrows = nrows if isinstance(nrows, int) else None
+            if isinstance(get_sheet_data_header, list) or isinstance(
path: pandas/io/excel/_base.py
text: @author:
should be possible to just take the maximum in that list, right?

diff_hunk: @@ -98,9 +106,16 @@ def _parse_cell(cell_contents, cell_typ):
                     cell_contents = val
             return cell_contents
 
-        data = []
+        data: List[List[Scalar]] = []
 
-        for i in range(sheet.nrows):
+        sheet_nrows = sheet.nrows
+        if nrows is not None and isinstance(header, int) and isinstance(skiprows, int):
+            sheet_nrows = min(header + skiprows + nrows + 1, sheet_nrows)
+
+        for i in range(sheet_nrows):
+            if self.should_skip_row(i, header, skiprows, nrows):
+                data.append([])
path: pandas/io/excel/_xlrd.py
text: @user1:
Do we need to append the empty list here? Would be preferable to just continue

@author:
I removed `should_skip_row` entirely, I think the benefit from the original PR came from not reading all the rows into memory, rather than from optimising skipping rows

diff_hunk: @@ -453,7 +491,20 @@ 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)
+            get_sheet_data_header = 0 if header is None else header
path: pandas/io/excel/_base.py
text: @user1:
Is this required? Looks like there are already checks within the subsequent functions for int values, no?