> ## Content Index > Fetch the complete content index at: https://datascientyst.com/llms.txt > Use this file to discover other available public pages before exploring further. # Read Excel XLS with Python Pandas - URL: https://datascientyst.com/read-excel-xls-with-python-pandas/ - Published: 2021-07-18T09:47:56.000Z - Updated: 2021-07-18T09:47:56.000Z - Author: John D K - Tags: read_excel() In this post you can learn **how to read Excel files (ext xls, xlsx etc) with Python and Pandas**. We will import one or several sheets from an Excel file to a Pandas DataFrame. The list of the supported file extensions: - `xls` - `xlsx` - `xlsm` - `xlsb` - `odf` - `ods` - `odt` Note for `ods`, `ods` and `odt` please check: [Read Excel(OpenDocument ODS) with Python Pandas](https://datascientyst.com/read-excel-opendocument-ods-python-pandas) ## Step 1: Install Pandas and odfpy Python offers many different modules for reading and manipulating Excel files. In this guide we are going to use `pandas` and `odfpy`: ```python pip install pandas pip install odfpy ``` ## Step 2: Read the one sheet of Excel(XLS) file Pandas offers a powerful method for **reading any type of Excel files `read_excel()`**. It's pretty easy to be used and requires only the file path: ```python import pandas as pd pd.read_excel('animals.xls') ``` It will read and return all non empty cells from the Excel file: | | Rank | Animal | Maximum speed | Class | Notes | | - | ---- | ------------------------------- | ------------------------------------------------- | ------------- | ------------------------------------------------- | | 0 | 1 | Peregrine falcon | 389 km/h (242 mph)108 m/s (354 ft/s)\[2\]\[6\] | Flight-diving | The peregrine falcon is the fastest aerial ani... | | 1 | 2 | Golden eagle | 240–320 km/h (150–200 mph)67–89 m/s (220–293 f... | Flight-diving | Assuming the maximum size at 1.02 m, its relat... | | 2 | 3 | White-throated needletail swift | 169 km/h (105 mph)\[8\]\[9\]\[10\] | Flight | NaN | | 3 | 4 | Eurasian hobby | 160 km/h (100 mph)\[11\] | Flight | Can sometimes outfly the swift | | 4 | 5 | Mexican free-tailed bat | 160 km/h (100 mph)\[12\] | Flight | It has been claimed to have the fastest horizo... | | 5 | 6 | Frigatebird | 153 km/h (95 mph) | Flight | The frigatebird's high speed is helped by its ... | | 6 | 7 | Rock dove (pigeon) | 148.9 km/h (92.5 mph)\[13\] | Flight | Pigeons have been clocked flying 92.5 mph (148... | | 7 | 8 | Spur-winged goose | 142 km/h (88 mph)\[14\] | Flight | NaN | | 8 | 9 | Gyrfalcon | 128 km/h (80 mph)\[citation needed\] | Flight | NaN | ![pandas-read excel-xls-xlsx-python](https://datascientyst.com/content/images/2021/07/pandas-read-excel-xls-xlsx-python.png) ## Step 3: Read the second sheet of Excel file by name If you like to **read data from a specific sheet** \- for example `Sheet 2` then you can specify the name as a parameter - `sheet_name`: ```python pd.read_excel('animals.xlsx', sheet_name="Sheet2") ``` Which will result in: | | Blackbuck | Unnamed: 1 | | - | ------------------------------------------------------- | ------------------------------------------------- | | 0 | NaN | NaN | | 1 | Male blackbuck | Male blackbuck | | 2 | NaN | NaN | | 3 | Female with young at the National Zoological Park Delhi | Female with young at the National Zoological P... | | 4 | Conservation status | Conservation status | | 5 | Least Concern (IUCN 3.1)\[1\] | Least Concern (IUCN 3.1)\[1\] | | 6 | Scientific classification | Scientific classification | ## Step 4: Python read excel file - specify columns and rows If you like to read a range of data and not the whole sheet - `read_excel` offers several very useful parameters. ### Python read excel file select rows Next code example will show you **how to read 3 rows skipping the first two rows**. In this way Pandas will read only some rows from the whole sheet: ```python pd.read_excel('animals.xlsx', skiprows=2, nrows=3) ``` which will result in: | | 2 | Golden eagle | 240–320 km/h (150–200 mph)67–89 m/s (220–293 f... | Flight-diving | Assuming the maximum size at 1.02 m, its relat... | | - | - | ------------------------------- | ------------------------------------------------- | ------------- | ------------------------------------------------- | | 0 | 3 | White-throated needletail swift | 169 km/h (105 mph)\[8\]\[9\]\[10\] | Flight | NaN | | 1 | 4 | Eurasian hobby | 160 km/h (100 mph)\[11\] | Flight | Can sometimes outfly the swift | | 2 | 5 | Mexican free-tailed bat | 160 km/h (100 mph)\[12\] | Flight | It has been claimed to have the fastest horizo... | ### Python read excel file select columns If you like to\*\* work with few columns\*\* and not the whole sheet - then parameter `use_cols` can be used as shown: ```python pd.read_excel('animals.xlsx', usecols='C:D') ``` ### Python read excel file specify columns and rows Finally if you like to **select a range from specific columns and rows** than you can use: ```python ``` Which will result into: | | 240–320 km/h (150–200 mph)67–89 m/s (220–293 f... | Flight-diving | | - | ------------------------------------------------- | ------------- | | 0 | 169 km/h (105 mph)\[8\]\[9\]\[10\] | Flight | | 1 | 160 km/h (100 mph)\[11\] | Flight | | 2 | 160 km/h (100 mph)\[12\] | Flight | ## Step 5\. Read multiple sheets from Excel file What if you like to r**ead with Pandas multiple sheets from Excel.** It's possible with `pd.read_excel` by providing a list of all sheets to be read as follows: ```python pd.read_excel('animals.xlsx', sheet_name=["Sheet1", "Sheet2"]) ``` Note that a dictionary of - keys - sheet names - values - resulted DataFrames will be returned. In order to access data you can access it by a sheet name as: ```python pd.read_excel('animals.xlsx', sheet_name=["Sheet1", "Sheet2"]).get('Sheet1') ``` which will return the data for `Sheet1` as a DataFrame. ### Read All Sheets For loading all sheets from Excel file use `sheet_name=None`: ```python pd.read_excel('animals.xlsx', sheet_name=None) ``` ## Step 6\. Pandas read excel data with conversion, NA values and parsing Finally let's check what we can do if we need to convert data, drop or fill missing values, parse dates and numbers. Pandas offers several parameters for this purpose: - **converters** \- dict of functions for converting values in certain columns - **keep\_default\_na** \- whether or not to include the default NaN values - **parse\_dates** - **ate\_parser** \- converting a sequence of string columns to an array of datetime instances. - **thousands** - **convert\_float** You can check the Notebook in the resources for more examples of the above. ## Resources - [Python Pandas Reading Excel files](https://pandas.pydata.org/pandas-docs/stable/user%5Fguide/io.html?ref=datascientyst.com#excelfile-class) - [pandas.read\_excel](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read%5Fexcel.html?ref=datascientyst.com#pandas.read%5Fexcel) - [Notebook - Read Excel ODS with Python Pandas](https://github.com/softhints/datascientyst/blob/master/excel/1.read-excel-xls-xlsx-python-pandas.ipynb?ref=datascientyst.com)