Read Excel with Pandas


Excel files can be read using the Python module Pandas. In this article we will read excel files using Pandas.

Related course:
Data Analysis in Python with Pandas

Read Excel column names
We import the pandas module, including ExcelFile. The method read_excel() reads the data into a Pandas Data Frame, where the first parameter is the filename and the second parameter is the sheet.

The list of columns will be called df.columns.

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
 
df = pd.read_excel('File.xlsx', sheetname='Sheet1')
 
print("Column headings:")
print(df.columns)

Using the data frame, we can get all the rows below an entire column as a list. To get such a list, simply use the column header

print(df['Sepal width'])

Read Excel data
We start with a simple Excel file, a subset of the Iris dataset.

excel - python pandas

To iterate over the list we can use a loop:

for i in df.index:
    print(df['Sepal width'][i])

We can save an entire column into a list:

listSepalWidth = df['Sepal width']
print(listSepalWidth[0])

We can simply take entire columns from an excel sheet:

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
 
df = pd.read_excel('File.xlsx', sheetname='Sheet1')
 
sepalWidth = df['Sepal width']
sepalLength = df['Sepal length']
petalLength = df['Petal length']