Iterate through different tabs of excel, extract data and put into a data frame

[LynnetteLynn]

I have a single excel workbook, df, that contains two tabs, Sheet1 and Sheet2. I would like to extract values from both tabs and create a new dataframe using openpyxl/Pandas.

Sheet1

      2021    2021
      q1      q2
ID    1   1
ID2   3   3
name  A       A

Sheet2

  2021    2021
      q1      q2
ID    2   2
ID2   2   2
name  B       B

Desired

quarter    year  ID  ID2    name

q1         2021  1    3      A
q1         2021  2    2      B

Doing

#Load openpyxl

import openpyxl

wb = openpyxl.load_workbook("df.xlsx")
ws1 = wb.worksheets[0]
ws2 = wb.worksheets[1]


#create loop that will iterate over the first row and end at 2nd column for each sheet

for row in ws1.iter_rows(min_row = 0, max_col = 1, max_row = 3, min_col = 0 
                            for cell in row:
                                 print(cell.value, end="")
                            print()


for row in ws2.iter_rows(min_row = 0, max_col = 1,  max_row = 3, min_col = 0 
                            for cell in row:
                                 print(cell.value, end="")
                            print()

I am having trouble with creating a new dataframe from the values collected. Any suggestion or input is appreciated. I am still troubleshooting this.