19

Want to import only certain range of data from an excel spreadsheet (.xlsm format as it has macros) into a pandas dataframe. Was doing it this way:

data    = pd.read_excel(filepath, header=0,  skiprows=4, nrows= 20, parse_cols = "A:D")

But it seems that nrows works only with read_csv() ? What would be the equivalent for read_excel()?

Gabriel
  • 3,737
  • 11
  • 30
  • 48
  • It doesn't look like there is such an option. – Ami Tavory Mar 02 '16 at 12:53
  • 1
    Any suggestion how to just import up until certain row? – Gabriel Mar 02 '16 at 12:57
  • Something like `pd.read_excel(...).head(50)` will get you the first 50 rows, but of course it reads and discards, so I'm afraid it's not very helpful. Sorry. – Ami Tavory Mar 02 '16 at 13:00
  • `skip_footer`, as in the answer, ought to work (although, assumes you already know n). Alternatively, unless the excel file is large (which usually they aren't else wouldn't be in a spreadsheet), @AmiTavory's suggestion ought to be fine. Finally, `read_excel` is just a wrapper for py/excel libraries (I think `xlrd` by default). If you really want fine control you'll need to use one of those libraries. Here is a good starting point: http://www.python-excel.org/ – JohnE Mar 02 '16 at 13:31
  • @JohnE Very good points - useful to know. – Ami Tavory Mar 02 '16 at 13:33
  • @JohnE thanks for the info – Gabriel Mar 02 '16 at 13:38

4 Answers4

18

As noted in the documentation, as of pandas version 0.23, this is now a built-in option, and functions almost exactly as the OP stated.

The code

data = pd.read_excel(filepath, header=0, skiprows=4, nrows= 20, use_cols = "A:D")

will now read the excel file, take data from the first sheet (default), skip 4 rows of data, then take the first line (i.e., the fifth line of the sheet) as the header, read the next 20 rows of data into the dataframe (lines 6-25), and only use the columns A:D. Note that use_cols is now the final option, as parse_cols is deprecated.

NathanielDavidChu
  • 181
  • 1
  • 2
14

If you know the number of rows in your Excel sheet, you can use the skip_footer parameter to read the first n - skip_footer rows of your file, where n is the total number of rows.

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html

Usage: 

data = pd.read_excel(filepath, header=0, parse_cols = "A:D", skip_footer=80)

Assuming your excel sheet has 100 rows, this line would parse the first 20 rows.

Erol
  • 6,478
  • 5
  • 41
  • 55
8

I'd like to make (extend) @Erol's answer bit more flexible.

Assuming that we DON'T know the total number of rows in the excel sheet:

xl = pd.ExcelFile(filepath)

# parsing first (index: 0) sheet
total_rows = xl.book.sheet_by_index(0).nrows

skiprows = 4
nrows = 20

# calc number of footer rows
# (-1) - for the header row
skipfooter = total_rows - nrows - skiprows - 1

df = xl.parse(0, skiprows=skiprows, skipfooter=skipfooter, parse_cols="A:D") \
       .dropna(axis=1, how='all')

.dropna(axis=1, how='all') will drop all columns containing only NaN's

MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419
0

My Dear... Take it easy... Make it simple

data = pd.read_excel(filepath, header=0, skiprows=4, parse_cols = "A:D")

pd = pd[:20]

I hope this is the answer to your question. Enjoy...

Jahangir Khan
  • 1
  • 2
  • "My Dear... Take it easy... Make it simple" --> Be more respectful, no jugement, ex: "Here is a simple one line solution:" – Martin Aug 08 '23 at 08:54