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 )|
We have successfully captured the Excel data to a Pandas DataFrame and are all set to analyze the Excel data on Pandas!
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.
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:
We can also pass a list to sheet_name. That would allow us to pull data from multiple sheets.
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.
You need to mention the filepath if the import file isn’t located in the same folder as the code:
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).
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
To read Excel files or CSV files in Python you need to first install the PANDAS module.
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.
When we use read_excel to import excel data into pandas, the data gets stored as a DataFrame