Python Pandas read excel sheet different range with table headers

Question

I am working to read tables in the excel sheet, and want to use the keywords like 'TAB_1', 'TAB_2' to get the values of each table named as 'TAB_1' and 'TAB_2'. Is there a quick syntax in pandas to work on it?

1. what the excel sheet like:

2. what I expect to get using the keyword from ['TAB_1', 'TAB_2]
   df_tab_1 =
   test_a test_b
   1 2
   3 4
   df_tab_2 =
   test_c test_d
   5 6
   7 8
   as two pandas dataframes

Answer 1

It's not possible to do that with Pandas. As workaround, you can name your Tables in Excel then use openpyxl to load them:

import pandas as pd
import openpyxl

wb = openpyxl.load_workbook('data.xlsx')
ws = wb.active

data1 = []
for row in ws[ws.tables['TAB_1'].ref]:
    data1.append([cell.value for cell in row])
df1 = pd.DataFrame(data1[1:], columns=data1[0])

# Same for df2

Output:

>>> df1
   test_a  test_b
0       1       2
1       3       4

>>> df2
   test_c  test_d
0       5       6
1       7       8