使用Python从Excel提取数据并规整分块表格结构

从Excel提取数据后得到的DataFrame结构如下：

需要将其转换为如下规整格式：

现有初始代码，需完善实现上述转换：

import pandas as pd
import numpy as np

# Step 1: Read the Excel file
# Replace 'your_file_path.xlsx' with the actual path to your Excel file
file_path = 'your_file_path.xlsx'

# Load the Excel file
xls = pd.ExcelFile(file_path)

# Assuming 'Datei2' is the name of the sheet we want to read
df = pd.read_excel(xls, sheet_name='Datei2')
df['Column1 - Copy'] = df['Column1']
split_columns = df['Column1 - Copy'].str.split(' ', expand=True)
split_columns.columns = [f'Column1 - Copy.{i+1}' for i in range(split_columns.shape[1])]
df = pd.concat([df, split_columns], axis=1)

# Step 6: Fill Down Values
df['Column1 - Copy.16'] = df['Column1 - Copy.16'].fillna(method='ffill')

import pandas as pd
import numpy as np

# 读取Excel文件
file_path = 'your_file_path.xlsx'
xls = pd.ExcelFile(file_path)
df = pd.read_excel(xls, sheet_name='Datei2')

# 识别数据块标识行（匹配"data "开头的行）
df['data_block'] = np.where(df.iloc[:, 0].str.startswith('data ', na=False), df.iloc[:, 0], np.nan)

# 向下填充数据块标识，让每个数据行对应所属分组
df['data_block'] = df['data_block'].ffill()

# 过滤掉表头行和分组标识行
filtered_df = df[~df.iloc[:, 0].isin(['id', 'data 1', 'data 2'])]

# 提取data编号并转为整数，重命名列
filtered_df['data'] = filtered_df['data_block'].str.extract(r'data (\d+)').astype(int)
filtered_df = filtered_df.rename(columns={filtered_df.columns[0]: 'id', filtered_df.columns[1]: 'datum'})

# 保留目标列并重置索引
final_df = filtered_df[['id', 'datum', 'data']].reset_index(drop=True)

print(final_df)

关键步骤说明 #

1. 标记数据块：通过第一列内容判断，标记出每个数据分组的起始行。
2. 填充分组标识：用ffill()将分组标识填充到该组所有行，确保每行明确所属分组。
3. 过滤无效行：移除无用的表头行和分组标题行，仅保留有效数据行。
4. 提取分组编号：从分组标识中提取数字作为data列值，转为整数类型。
5. 规整结构：重命名列名，保留需要的字段并重置索引，得到目标格式。

对初始代码的优化 #

初始代码的列拆分操作并非必要，直接通过分组标识识别+填充的方式更简洁高效。如果原始Excel列位置有变化，只需调整iloc[:, 0]和iloc[:, 1]的索引即可适配。

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