How to read an excel file (with extension xlsx) with pandas in python ?

Examples of how to read an excel file (with extension xlsx) with pandas in python

Read a excel file with only one sheet

Let's consider the following excel file:

To read a excel file with pandas, a solution is to use the the pandas function read_excel()

import pandas as pd

df = pd.read_excel ('../read_excel_file_with_python.xlsx')

print (df)

gives

  First Name Last Name  Age  Height
0       John       Doe   24     190
1     Elijah     Baley   31     169
2       Paul    Edison   22     185
3     Martin      Cage   41     176
4     Robert     Lemon   32     195

Note: If the following message error appears

 Missing optional dependency 'xlrd'. Install xlrd >= 1.0.0 for Excel support Use pip or conda to install xlrd.

it is necessary to install first the module xlrd. For example with conda:

conda install xlrd

Read a excel file with several sheets

Let's onsider for example a excel file with two sheets:

To read the excel file, use first the function ExcelFile():

xls = pd.ExcelFile('../read_excel_file_with_python.xlsx')

and get the sheet names:

print(xls.sheet_names)

returns

['Personal Data', 'Public Data']

Then get data from the sheet 1

df1 = pd.read_excel(xls, 'Personal Data')

print(df1)

returns

  First Name Last Name  Age  Height
0       John       Doe   24     190
1     Elijah     Baley   31     169
2       Paul    Edison   22     185
3     Martin      Cage   41     176
4     Robert     Lemon   32     195

and sheet 2

df2 = pd.read_excel(xls, 'Public Data')

print(df2)

returns

   id   pseudo
0    1    Dodo
1    2   Space
2    3     Edi
3    4  Azerty
4    5     Bob

References

• How to Import an Excel File into Python using pandas
• Your Guide to Reading Excel (xlsx) Files in Python
• Reading Excel files
• Using Pandas to pd.read_excel() for multiple worksheets of the same workbook
• Pandas: Looking up the list of sheets in an excel file