如何对含两类索引的列进行Pandas透视并重构DataFrame？

初始数据 #

你的初始DataFrame如下：

import pandas as pd

df = pd.DataFrame({
    "Row tags": ["4", "100101 - Hospital A.xls", "100195 - Hospital B.xls", "100105 - Hospital C.xls", "5", "100101 - Hospital A.xls", "100195 - Hospital B.xls"],
    "Values": [100, 30, 30, 40, 50, 25, 25]
})

已完成的步骤 #

你已经通过正则识别出两类行，生成了带Regex pattern列的中间表：

# 生成Regex pattern列（识别带.xls的行）
df["Regex pattern"] = df["Row tags"].str.contains(r"\.xls$")

中间表结果：

Row tags                  Values  Regex pattern
0  4                         100           False
1  100101 - Hospital A.xls   30            True
2  100195 - Hospital B.xls   30            True
3  100105 - Hospital C.xls   40            True
4  5                         50           False
5  100101 - Hospital A.xls   25            True
6  100195 - Hospital B.xls   25            True

后续解决方案 #

1. 生成分组Code列 #

利用ffill()向前填充，让子行继承最近的纯数字分组标签：

# 仅在纯数字行填充Code列，其余行设为NaN
df["Code"] = df["Row tags"].where(df["Regex pattern"] == False)
# 向前填充NaN，给所有子行分配对应分组的Code
df["Code"] = df["Code"].ffill()

2. 过滤掉总和行 #

只保留带.xls的子行（Regex pattern为True的行）：

result = df[df["Regex pattern"] == True].copy()

3. 调整列格式 #

重命名列并调整到目标输出的顺序：

# 重命名Row tags为Hospital
result = result.rename(columns={"Row tags": "Hospital"})
# 选择并排序所需列，重置索引
result = result[["Code", "Hospital", "Values"]].reset_index(drop=True)

最终结果 #

运行上述代码后，result即为目标输出：

print(result)

输出：

Code                  Hospital  Values
0    4  100101 - Hospital A.xls      30
1    4  100195 - Hospital B.xls      30
2    4  100105 - Hospital C.xls      40
3    5  100101 - Hospital A.xls      25
4    5  100195 - Hospital B.xls      25

内容的提问来源于stack exchange，提问作者Adrian Guerra