Assigning each excel sheet to a variable while looping (using openpyxl) and create dataframe of each sheets

Question

I have an excel document with multiple sheets containing different data sets. For instance, first sheet has 2 column data where as the second sheet (sheet 2) has 3 columns. I need to iterate through the sheetnames using openpyxl or pandas and create dataframe of each sheet with its name assigned to each data frame.

How can I do this.

Can someone put your expertise here?

Thank you

Answer 1

I think you could achieve this with a simple dictionary: Take this as a hypothetical example:

import openpyxl
d = {}
wb = openpyxl.load_workbook("file.xlsx")

sheets = ['Sheet1', 'Sheet2', 'Sheet4', 'Sheet5']

for sheet in sheets:
    d[sheet] = something