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

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 )
0	A	33	96
1	B	12	90
2	C	63	96
3	D	49	64
4	E	64	46
5	F	69	56
6	G	60	92
7	H	26	65
8	I	74	93
9	J	32	88

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