How to read data from Excel or CSV to Pandas in Python

Reading data from Excel or CSV to Pandas is an important step in solving data analytics problems using Pandas in Python. Thankfully, Pandas module comes with a few great functions that let’s you get this done easily.

Read Data from Excel to Pandas

You can import data from an Excel file to Pandas using the read_excel function. Read Excel stores the excel data as a Pandas DataFrame.

Consider a simple excel storing names of students and their scores in Maths & English. Let’s name the file : scores.xlsx

Sample Excel for explaining read_excel function
Sample Excel for explaining read_excel function

To import this data to Pandas, we need to write the following code:

import pandas
df = pandas.read_excel("scores.xlsx")
df

The code give us the following output:

Name Score (Maths) Score (English )
0A3396
1B1290
2C6396
3D4964
4E6446
5F6956
6G6092
7H2665
8I7493
9J3288

We have successfully captured the Excel data to a Pandas DataFrame and are all set to analyze the Excel data on Pandas!

For example, you could drop columns and rows in pandas DataFrames by names , index values or conditions and then export the resultant Pandas DataFrame to excel.

Reading Data from Excel file stored in a particular location

In the above example, we just passed the name of the excel file to the read_excel function. It works only if the excel is stored in the same folder as the code. If the excel is in a different folder, we need to specify the path of the file.

df=pandas.read_excel("C:\\folder\\sub_folder\\scores.xlsx")
df

Reading Data from a particular sheet in an Excel file

An excel file can have multiple sheets. We can mention which sheet we want to pull the data from by using the optional sheet_name attribute:

df=pandas.read_excel("C:\\folder\\sub_folder\\scores.xlsx",sheet_name="Sheet1")
df

We can also pass a list to sheet_name. That would allow us to pull data from multiple sheets.

df=pandas.read_excel("C:\\folder\\sub_folder\\scores.xlsx",sheet_name=["Sheet1","Sheet2"])
df

Read Data from CSV to Pandas

Just like excel, it’s easy to read data from a CSV file too. The function in this case is read_csv.

df=pandas.read_csv("scores.csv")
df

You need to mention the filepath if the import file isn’t located in the same folder as the code:

df=pandas.read_csv("C:\\folder\\sub_folder\\scores.csv")
df

Do be careful about the file-type from which you are importing data. If you try to read an excel file using read_csv you will see an error (and vice versa).

Further Reading

To learn about exporting a DataFrame to Excel or CSV, do read my article on How to export data from Pandas to Excel or CSV.

If you found the above tutorial useful, you may want to check out some of my other articles on Python.

FAQ : Pandas Read Excel

Can Python read Excel files?

To read Excel files or CSV files in Python you need to first install the PANDAS module.

How do I view Excel files in pandas?

We need to import the data from the Excel into pandas. To do that use the pandas read_excel method to read in data from the Excel file.

How do I convert an Excel file to a pandas DataFrame?

When we use read_excel to import excel data into pandas, the data gets stored as a DataFrame

1 thought on “How to read data from Excel or CSV to Pandas in Python”

Leave a Reply

Your email address will not be published. Required fields are marked *