You can easily import an Excel file into Python using pandas. In order to accomplish this goal, you’ll need to use read_excel.
In this short post, I’ll show you the steps to import an Excel file into Python using a simple example.
But before we begin, here is the general structure that you may apply in Python to import your Excel file:
import pandas as pd from pandas import DataFrame ReadExcel = pd.read_excel (r'Path where the Excel file is stored\File name.xlsx') #for an earlier version of Excel, you may need to use the file extension of 'xls' df = DataFrame(ReadExcel,columns=['Column name 1','Column name 2','Column name 3']) #add as many columns as you need print (df)
And if you have a specific Excel sheet that you’d like to import, you may then apply this logic:
ReadExcel = pd.read_excel (r'Path where the Excel file is stored\File name.xlsx', sheet_name='Type here the name of your Excel sheet')
Let’s now review an example that includes the data to be imported into Python.
The data to be imported into Python
Suppose that you have the following table stored in an Excel file (where the Excel file name is ‘Product List’):
How would you then import the above data into Python?
You may follow the steps below to import an Excel file into Python.
Steps to Import an Excel File into Python using pandas
Step 1: Capture the file path
First, you’ll need to capture the full path where your Excel file is stored on your computer.
In my case, I stored the Excel File on my Desktop:
C:\Users\Doron E\Desktop\Product List.xlsx
In the Python code, to be provided below, you’ll need to modify the path name to the location where your Excel file is stored on your computer.
Do not forget to include the file name (in our example, it’s ‘Product list‘ as highlighted in blue). You will also need to include the Excel file extension (in our case, it’s ‘.xlsx‘ as highlighted in green).
Step 2: Assign the column names
Next, assign the column names using the DataFrame. In our scenario, we are dealing with only two columns: ‘Product’ and ‘Price’
Make sure that the number of columns and names assigned in the Python code, match with the number of columns and names within the Excel file.
Step 3: Apply the Python code
And here is the Python code tailored to our example. I included some notes within the code to clarify some of the components used.
import pandas as pd from pandas import DataFrame ProductList = pd.read_excel (r'C:\Users\Doron E\Desktop\Product List.xlsx') #(use "r" before the path string to address special character, such as '\'). Don't forget to put the file name at the end of the path + '.xlsx' df = DataFrame(ProductList,columns=['Product','Price']) # assign column names print (df)
Step 4: Run the Python code
Press F5 to run the Python code. Here is what you’ll get:
Notice that we got the same results as those that were stored in the Excel file.
We just saw how to import an Excel file into Python using pandas.
At times, you may need to import a CSV file into Python. If that’s the case, you may want to check the following tutorial that explains how to import a CSV file into Python using pandas.
You can also check the pandas documentation to find out more about the different options that you may apply in regards to read_excel.