python - How to convert a worksheet to a Data frame in Pandas? -

i trying read different worksheets excel workbook in python pandas. when read entire workbook , want apply .merge() first worksheet read others not considered. tried read each worksheet of workbook guess not converted data frames because when apply .merge() end following error: valueerror: invalid file path or buffer object type: <class 'pandas.core.frame.dataframe'>

this have done far:

this code works converting entire workbook data frame data of first worksheet processed

import pandas pd import pypyodbc  #sql extractor start_date = date.today() retrieve_values = "[dev].[cs].[qt_kpiexport] @start_date='{start_date:%y-%m-%d}'".format(     start_date=start_date) connection = pypyodbc.connect(driver="{sql server}", server="xxx.xxx.xxx.xxx", uid="x",pwd="xxx", trusted_connection="no") data_frame_sql = pd.read_sql(retrieve_values, connection)  #read entire workbook  wb_data = pd.excelfile("c:\\users\\dev\\testing\\daily_data\\nsn-daily data report.xlsx") #convert dataframe entire workbook data_frame_excel = pd.read_excel(wb_data,index_col=none,na_values=['na'],parse_cols="j")  #apply merge merged_df   = data_frame_sql.merge(data_frame_excel,how="inner",on="sectorname") 

this code tries read different worksheets , convert them data frames no success...yet! (check answer below)

data_frame_sql = pd.read_sql(retrieve_values, connection)  #method 1: tried parse worksheet 2 #read entire workbook , select specific worksheet wb_data = pd.excelfile("c:\\users\\dev\\testing\\daily_data\\nsn-daily data report.xlsx", sheetname="sheet-2") data_frame_excel = pd.read_excel(wb_data,index_col=none,na_values=['na'],parse_cols="j")  #apply merge merged_df   = data_frame_sql.merge(data_frame_excel,how="inner",on="sectorname") #no success... data of first sheet read  #method 2: tried parse worksheet 2 #read entire workbook wb_data = pd.excelfile("c:\\users\\dev\\testing\\daily_data\\nsn-daily data report.xlsx") data_frame_excel = pd.read_excel(wb_data,index_col=none,na_values=['na'],parse_cols="j")  #select 1 specific sheet ws_sheet_2 = wb_data.parse("sheet-2")  #apply merge merged_df   = data_frame_sql.merge(ws_sheet_2,how="inner",on="sectorname") # no success.... valueerror: invalid file path or buffer object type: <class 'pandas.core.frame.dataframe'> 

any or advice appreciated.

you can worksheets workbook dictionary using sheetname=none argument read_excel method. key/value pairs ws name/dataframe.

ws_dict = pd.read_excel('excel_file.xlsx', sheetname=none) 

note sheetname argument change sheet_name in future pandas versions...