How to import an excel file into Python using Pandas?
Last Updated : 17 Aug, 2020
It is not always possible to get the dataset in CSV format. So, Pandas provides us the functions to convert datasets in other formats to the Data frame. An excel file has a '.xlsx' format.
Before we get started, we need to install a few libraries.
pip install pandas pip install xlrd
For importing an Excel file into Python using Pandas we have to use pandas.read_excel() function.
Syntax: pandas.read_excel(io, sheet_name=0, header=0, names=None,....)
Return: DataFrame or dict of DataFrames.
Let’s suppose the Excel file looks like this:
Now, we can dive into the code.
Example 1: Read an Excel file.
Python3 import pandas as pd df = pd.read_excel("sample.xlsx") print(df) Output:
Example 2: To select a particular column, we can pass a parameter "index_col".
Python3 import pandas as pd # Here 0th column will be extracted df = pd.read_excel("sample.xlsx", index_col = 0) print(df) Output:
Example 3: In case you don't prefer the initial heading of the columns, you can change it to indexes using the parameter "header".
Python3 import pandas as pd df = pd.read_excel('sample.xlsx', header = None) print(df) Output:
Example 4: If you want to change the data type of a particular column you can do it using the parameter "dtype".
Python3 import pandas as pd df = pd.read_excel('sample.xlsx', dtype = {"Products": str, "Price":float}) print(df) Output:
Example 5: In case you have unknown values, then you can handle it using the parameter "na_values". It will convert the mentioned unknown values into "NaN"
Python3 import pandas as pd df = pd.read_excel('sample.xlsx', na_values =['item1', 'item2']) print(df) Output:
Similar Reads
How to Read an Excel File using polars The Polars is a fast, efficient DataFrame library in Python, designed for processing large datasets with low memory usage and high performance. While Polars is more commonly used with CSV, Parquet, and JSON files, we can also work with Excel files, though this requires an additional setup as Polars
4 min read
How to import excel file and find a specific column using Pandas? To read specific columns from an Excel file in Pandas, you have the flexibility to use either column indices or letters. This is achieved by setting the usecols argument, which can take a comma-separated string or a list containing column identifying letters or indices. In this article, we will lear
5 min read
Joining Excel Data from Multiple files using Python Pandas Let us see how to join the data of two excel files and save the merged data as a new Excel file. We have 2 files, registration details.xlsx and exam results.xlsx. registration details.xlsx We are having 7 columns in this file with 14 unique students details. Column names are as follows : Admission D
2 min read
Read Html File In Python Using Pandas We are given an HTML file that contains one or more tables, and our task is to extract these tables as DataFrames using Python. For example, if we have an HTML file with a table like this:<table> <tr><th>Code</th><th>Language</th><th>Difficulty</th>
4 min read
Convert CSV to Excel using Pandas in Python Pandas can read, filter, and re-arrange small and large datasets and output them in a range of formats including Excel. In this article, we will be dealing with the conversion of .csv file into excel (.xlsx). Pandas provide the ExcelWriter class for writing data frame objects to excel sheets. Syntax
1 min read