How to use read_excel() in pandas Python

Overview

The read_excel() method from the pandassoftware library written in python library reads excel files, that is, files in the .xls format. It takes the file name or directory as the first argument with the sheet name as the second argument value. As a matter of course, it takes an excel file as input and returns it as a DataFrame.

Syntax


pd.read_excel(io:path_to_file,
sheet_name="Sheet1",
header=0,
names=None,
index_col=None,
na_values=None)

Parameters

It takes the following argument values:

  • path_to_file: This is the excel spreadsheet name with an extension or path to the file.
  • sheet_name: Its default value is None. It shows the sheet name because a single excel spreadsheet may contain multiple sheets.
  • header: Its default value is 0, and the header value is 0 as well. This means the first row will be used as the header of the returned DataFrame. Use None if the file does not have header or column labels.
  • names: This is a list of column names or header values. Its default value is None.
  • index_col: This helps us use columns as row labels. Its default value is None.
  • na_values: This helps us assign some value to NA or empty fields in the excel file. Its default value is None.

Return value

It returns a DataFrame.

Examples

Now, let’s discuss this method in detail with code examples.

This data.xlsx file has two workbooks (Employee and Payment). The "Employee" sheet contains the EID, name, and salary, while the "Payment" sheet contains EID, payment, and the hiring date.

Code

import pandas as pd
import numpy as np
# invoking read_excel() with
# io= data.xlsx,
# sheet_name= "Payment"
# header=0
df = pd.read_excel("data.xlsx", index_col=0)
print(df)

Explanation

  • Line 7: We invoke the read_excel() method to extract excel data into a DataFrame.
  • Line 8: We print DataFrame on the console.

Code

import pandas as pd
import numpy as np
# loading data.xlsx excel file in program
xlsx = pd.ExcelFile("data.xlsx")
# reading excel file to convert into dataframe
df = pd.read_excel(xlsx)
print(df)

Explanation

  • Line 4: We call ExcelFile() from pandas to load an excel file in the xlsx variable.
  • Line 6: We invoke the read_excel() method to convert excel data into a DataFrame. It helps manipulate the dataset easily after loading it into the program.