Pandas read_excel () with multiple sheets and separate columns

Question

I am trying to use pandas.read_excel() to import multiple sheets from a spreadsheet. If I do not specify columns with the parse_cols keyword, I can get all the data from the sheets, but I cannot 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 sheets from excel, but also specify specific columns based on that sheet?

Thanks.

Answer 1

When you pass a list of sheet names to read_excel , it returns a dictionary. You can achieve the same result 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)