Назовите все строки именем индекса

Предположим, у нас есть такая книга Excel:

Мы можем использовать pd.read_excel с sheet_name=None, чтобы прочитать это в словарь с именами листов в качестве ключей:

import pandas as pd

file_name = 'myfile.xlsx'

dict_df = pd.read_excel(file_name, sheet_name=None)

dict_df

{'Sheet1':   Col1  Col2
 0    A     1
 1    B     2
 2    C     3,
 'Sheet2':   Col1  Col2
 0    D     4
 1    E     5
 2    F     6}

Теперь мы можем использовать pd.concat , чтобы добавить ключи словаря к значениям индекса. Начиная с pd 2.0.0 вы можете использовать df.add_prefix с axis=0:

# pd >= 2.0.0
df = pd.concat([v.add_prefix(f"{k}_", axis=0) for k, v in dict_df.items()])

df

         Col1  Col2
Sheet1_0    A     1
Sheet1_1    B     2
Sheet1_2    C     3
Sheet2_0    D     4
Sheet2_1    E     5
Sheet2_2    F     6

Для более ранних версий вы можете перезаписать индекс, используя df.set_index:

# pd < 2.0.0
df = pd.concat([v.set_index(f"{k}_" + v.index.astype(str)) 
                for k, v in dict_df.items()])

# same result

Но, возможно, было бы лучше добавить имена листов на отдельном уровне, используя параметры keys и names:

df = pd.concat([v for v in dict_df.values()], 
               keys=dict_df.keys(), 
               names=['Sheets', 'Rows'])

df

            Col1  Col2
Sheets Rows           
Sheet1 0       A     1
       1       B     2
       2       C     3
Sheet2 0       D     4
       1       E     5
       2       F     6

Или просто:

df = pd.concat(dict_df, names=['Sheets', 'Rows'])
# same result

Например, таким образом вы можете легко выбрать конкретный лист:

df.loc['Sheet1']

     Col1  Col2
Rows           
0       A     1
1       B     2
2       C     3

Если вы читаете несколько книг Excel и хотите включить имена файлов, вы можете сделать что-то вроде этого:

workbooks = ['myfile.xlsx', 'myfile2.xlsx'] # `myfile2` here a copy of `myfile`

nested_dict = {(wb,k): v for wb in workbooks 
               for k, v in pd.read_excel(wb, sheet_name=None).items()}

df = pd.concat([v for v in nested_dict.values()], 
               keys=nested_dict.keys(), 
               names=['Filename', 'Sheets', 'Rows'])

df

                         Col1  Col2
Filename     Sheets Rows           
myfile.xlsx  Sheet1 0       A     1
                    1       B     2
                    2       C     3
             Sheet2 0       D     4
                    1       E     5
                    2       F     6
myfile2.xlsx Sheet1 0       A     1
                    1       B     2
                    2       C     3
             Sheet2 0       D     4
                    1       E     5
                    2       F     6

Или еще раз:

df = pd.concat(nested_dict, names=['Filename', 'Sheets', 'Rows'])
# same result

Выбор Sheet1 из первой книги:

df.loc[('myfile.xlsx', 'Sheet1')]

     Col1  Col2
Rows           
0       A     1
1       B     2
2       C     3