Python excel

Python can read Excel files easily. The pandas module can be used to read the Excel file into Python. This article shows several examples of this.

Preparation

(1): Prepare the pandas library for Python or Anaconda, install:

pip install pandas

(2): pandas depends on the xlrd module for processing Excel, install command:

pip install xlrd

(3): open the code editor jupyter, ipython, pycharm, whatever you use.

Prepare the excel data table

Create an excel file with your data. Make sure to remember the sheet name, thats shown on the bottom of the screen.

Related course: Data Analysis with Python Pandas

Read Excel file

use Pandas to read excel data

After loading the pandas module, you can load the excel file with the function call below.

df = pd.read_excel('path',sheet_name='which table in excel, table name/location')

If you don't use sheet_name='***', the first table will be read by default

import pandas as pd
import xlrd

df=pd.read_excel('e:\pandas_excel.xlsx',sheet_name='table1') 
print(df.head())
print(df)

You can use any sheet:

df1 = pd.read_excel('e:\pandas_excel.xlsx',sheet_name='table2')
print(df1.head(2))
print(df1)

Read the specified (limited) row of excel data

You can access the Excel files rows and columns using the dataframe structure.

import pandas as pd
import xlrd
df = pd.read_excel('e:\pandas_excel.xlsx',sheet_name='table1')
print(df.head(10))
print(df.ix[[0,2,3]].values)  
print(df.ix[[0,2,3]]) 
print(df.index[0:5:2]) 
print(df.ix[df.index[0:5:2]].values)
print(df.ix[df.index[0:5:2]]) 

You can use the row names and column indices:

df = pd.read_excel('e:\pandas_excel.xlsx',sheet_name='table1')
print(df['name'].values) 
print(df.ix[:,["name","address"]]) 
print(list(df.columns[0:3:1]))  
print(df.ix[:,list(df.columns[1:3:1])].values) 
print(df.ix[:,list(df.columns[1:3:1])]) 

Python pandas Excel data into a dictionary

You can convert Excel data into a Python dictionary. The code below shows an example of that:

df = pd.read_excel('e:\pandas_excel.xlsx',sheet_name='table1')
d = []
for i in df.index[::]:
    d_row = df.ix[i,list(df.columns[0:3:1])].to_dict()
    d.append(d_row)
print(d)