python - Pandas read_excel() with multiple sheets and specific columns

Question

I'm trying to use pandas.read_excel() to import multiple worksheets from a spreadsheet. If I do not specify the columns with the parse_cols keyword I'm able to get all the data from the sheets, but I can't seem to figure out how to specify specific columns for each sheet.

import pandas as pd

workSheets = ['sheet1', 'sheet2', 'sheet3','sheet4']
cols = ['A,E','A,E','A,C','A,E']

df = pd.read_excel(excelFile, sheetname=workSheets, parse_cols='A:E')  #This works fine
df = pd.read_excel(excelFile, sheetname=workSheets, parse_cols=cols) #This returns empty dataFrames

Does anyone know if there is a way, using read_excel(), to import multiple worksheets from excel, but also specify specific columns based on which worksheet?

Thanks.

Answer 1

When you pass a list of sheet names to read_excel, it returns a dictionary. You can achieve the same thing with a loop:

workSheets = ['sheet1', 'sheet2', 'sheet3', 'sheet4']
cols = ['A,E', 'A,E', 'A,C', 'A,E']
df = {}
for ws, c in zip(workSheets, cols):
    df[ws] = pd.read_excel(excelFile, sheetname=ws, parse_cols=c)

Below is update for Python 3.6.5 & Pandas 0.23.4:

pd.read_excel(excelFile, sheet_name=ws, usecols=c)