如何基于Excel工作表名和单元格位置生成对应DataFrame？

我有一个Excel表格（sample.xlsx），包含3个工作表（'Sheet1'、'Sheet2'、'Sheet3'）。我已读取所有工作表并合并为一个DataFrame，代码如下：

import pandas as pd
data_df = pd.concat(pd.read_excel("sample.xlsx", header=None, index_col=None, sheet_name=None))

data_df的结构如下：

0      1      2
Sheet1 0   val1   val2   val3
       1  val11  val21  val31
Sheet2 0   val1   val2   val3
       1  val11  val21  val31
Sheet3 0   val1   val2   val3
       1  val11  val21  val31

我希望创建一个与data_df形状相同，但每个单元格值为对应单元格位置信息的新DataFrame。我尝试获取多级索引：

multi_index = data_df.index.levels[:]

得到结果：

[['Sheet1', 'Sheet2', 'Sheet3'], [0, 1]]

但不知道如何利用这些数据生成如下格式的DataFrame：

0            1            2
0  Sheet1 - A1  Sheet1 - B1  Sheet1 - C1
1  Sheet1 - A2  Sheet1 - B2  Sheet1 - C2
2  Sheet2 - A1  Sheet2 - B1  Sheet2 - C1
3  Sheet2 - A2  Sheet2 - B2  Sheet2 - C2
4  Sheet3 - A1  Sheet3 - B1  Sheet3 - C1
5  Sheet3 - A2  Sheet3 - B2  Sheet3 - C2

• 先重置原DataFrame的多级索引，把工作表名和行号转为普通列：

  temp_df = data_df.reset_index(names=['sheet', 'row'])
  

• 生成对应Excel列的字母标识（A、B、C...）：

  import string
  # 根据原DataFrame的列数截取对应数量的大写字母
  col_letters = list(string.ascii_uppercase[:data_df.shape[1]])
  

• 拼接位置信息并生成目标列：
  遍历每一列，结合工作表名、行号（注意Excel行号从1开始，所以要给原row值+1）和列字母，生成指定格式的字符串：

  for idx, col in enumerate(col_letters):
      temp_df[idx] = temp_df.apply(lambda x: f"{x['sheet']} - {col}{x['row']+1}", axis=1)
  

• 移除临时列，得到最终结果：

  result_df = temp_df.drop(['sheet', 'row'], axis=1)
  

执行后result_df的结构完全符合需求：

0            1            2
0  Sheet1 - A1  Sheet1 - B1  Sheet1 - C1
1  Sheet1 - A2  Sheet1 - B2  Sheet1 - C2
2  Sheet2 - A1  Sheet2 - B1  Sheet2 - C1
3  Sheet2 - A2  Sheet2 - B2  Sheet2 - C2
4  Sheet3 - A1  Sheet3 - B1  Sheet3 - C1
5  Sheet3 - A2  Sheet3 - B2  Sheet3 - C2

如果处理大数据量，推荐用更高效的向量化实现（避免apply循环）：

import pandas as pd
import string

data_df = pd.concat(pd.read_excel("sample.xlsx", header=None, index_col=None, sheet_name=None))

df_reset = data_df.reset_index(names=['sheet', 'row'])
col_letters = list(string.ascii_uppercase[:data_df.shape[1]])
# 把行号转为字符串格式，方便拼接
row_str = (df_reset['row'] + 1).astype(str)

for idx, col in enumerate(col_letters):
    df_reset[idx] = df_reset['sheet'] + ' - ' + col + row_str

result_df = df_reset.drop(['sheet', 'row'], axis=1)

内容的提问来源于stack exchange，提问作者martin li