如何用Pandas提取Excel中分散的rel-xxx格式数据区块并规整

我有一个数据分散排布的Excel文件，希望不编辑原文件，直接用Pandas处理，规则如下：

• 以匹配rel-xxx模式的内容作为列名
• 该内容所在行的后续两行为对应列的有效取值
• 同一行或列中可能存在多个有效数据块
• 注意：原始数据中casexx命名不统一，最终仅保留case1和case2作为有效行名称

示例输入代码 #

import pandas as pd

data1 = [
    ["case1","rel-01","y2","rel-02","y4"],
    ["cas2",0,2,3,3],
    ["case3",1,3,5,7],
    ["case4","rel-03","y4","y5","y6"],
    ["case5",2,4,5,0],
    ["case6",0,1,2,3],
    ["case7","y1","y2","rel-04","y4"],
    ["case8",0,2,4,3],
    ["case9",2,3,4,5],
    ]

df1 = pd.DataFrame(data1,columns=['C1','C2','C3','C4','C5'])
print(df1)

期望输出 #

C1      C2  C3      C4  C5
0  case1  rel-01  y2  rel-02  y4
1   cas2       0   2       3   3
2  case3       1   3       5   7
3  case4  rel-03  y4      y5  y6
4  case5       2   4       5   0
5  case6       0   1       2   3
6  case7      y1  y2  rel-04  y4
7  case8       0   2       4   3
8  case9       2   3       4   5

    C1     rel-01 rel-02 rel-03 rel-04   
0  case1  0       3     2      4
1  case2  1       5     0      4

解决方案代码 #

import pandas as pd
import re

# 实际场景替换为 pd.read_excel("你的Excel文件路径.xlsx")
data1 = [
    ["case1","rel-01","y2","rel-02","y4"],
    ["cas2",0,2,3,3],
    ["case3",1,3,5,7],
    ["case4","rel-03","y4","y5","y6"],
    ["case5",2,4,5,0],
    ["case6",0,1,2,3],
    ["case7","y1","y2","rel-04","y4"],
    ["case8",0,2,4,3],
    ["case9",2,3,4,5],
]
df1 = pd.DataFrame(data1, columns=['C1','C2','C3','C4','C5'])

# 初始化结果表，指定行名为case1、case2
result = pd.DataFrame(index=['case1', 'case2'])
# 匹配rel-xxx格式的正则
rel_pattern = re.compile(r'rel-\d+')

# 遍历每一行，定位rel列并提取后续数据
for idx, row in df1.iterrows():
    # 找出当前行中所有符合rel-xxx格式的列
    rel_cols = [col for col in df1.columns if rel_pattern.match(str(row[col]))]
    if not rel_cols:
        continue
    # 检查后续是否有两行数据，避免索引越界
    if idx + 2 >= len(df1):
        continue
    # 取后续两行作为case1和case2的取值
    case1_vals = df1.iloc[idx+1]
    case2_vals = df1.iloc[idx+2]
    # 将对应列的数据写入结果表
    for col in rel_cols:
        col_name = row[col]
        result[col_name] = [case1_vals[col], case2_vals[col]]

# 重置索引并添加C1列
result = result.reset_index().rename(columns={'index': 'C1'})
print(result)

代码说明 #

1. 用正则表达式rel-\d+精准匹配所有符合rel-xxx格式的列名，避免误识别
2. 遍历原始数据的每一行，找到目标列后，直接提取其下两行的数据作为case1和case2的取值
3. 最终将所有提取的数据整合到统一的结果表中，确保行名称符合要求

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