How To Convert A Worksheet To A Data Frame In Pandas?

I am trying to read different worksheets from an Excel workbook in Python with Pandas. When I read the entire workbook and then I want to apply a .merge() then the first worksheet

Solution 1:

You can get all worksheets from a workbook into a dictionary by using the sheetname=None argument with the read_excel method. Key/value pairs will be ws name/dataframe.

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

Note the sheetname argument will change to sheet_name in future pandas versions...

Solution 2:

I found out a solution that did the trick.

#Method 1: Add the sheetname once you have read the entire workbook #Read the entire workbook 
wb_data = pd.ExcelFile("C:\\Users\\Dev\\Testing\\Daily_Data\\NSN-Daily Data 
Report.xlsx")
#Select your sheetname to read 
data_frame_excel = pd.read_excel(wb_data,index_col=None,na_values=
['NA'],parse_cols="J" sheetname="Sheet-2")

#apply merge
merged_df   = 
data_frame_sql.merge(data_frame_excel,how="inner",on="sectorname")

Solution 3:

To read .xlsx files in Pandas, for a document with multiple sheets, specify the sheet name and use a different engine.

Step 1 (install the openpyxl package):

! pip install openpyxl

Step 2 (use the openpyxl engine):

data_df = pd.read_excel(<ARCHIVE_PATH>, sheetname= <sheet_name>, engine='openpyxl')

Here is the official documentation.

Another solution using openpyxl directly:

wb = load_workbook(ARCHIVE_PATH)
ws = wb[<sheet-name>]
data_df = pd.DataFrame(ws.values)