使用Python和Pandas动态捕获Excel指定数据并生成目标DataFrame的技术咨询

需求说明 #

• 从大型Excel表格读取特定行、列、单元格数据，转换为指定DataFrame格式
• 需适配表格结构变化（新增行/列），持续捕获目标数据
• 核心逻辑：展示指定季度（如q1.22）和ID对应的列名计数，生成包含ID、date、TYPE的输出

示例数据 #

原始表格结构 #

q1.22           
ID      type1   OFFICE  nontype1    Customer
NY      1       3       1           2
CA      1       33      1           0
TOTALS  2       36      2           1

对应字典数据 #

data = {
    '0': ['id', 'NY', 'CA', 'TOTALS'],
    'q1.22': ['type1', '1', '1', '2'],
    '0_2': ['OFFICE', '3', '33', '36'],
    '0_3': ['nontype1', '1', '1', '2'],
    '0_4': ['Customer', '2', '0', '1']
}

期望输出 #

ID  date    TYPE
NY  q1.22   type1
NY  q1.22   nontype1
NY  q1.22   Customer
NY  q1.22   Customer
CA  q1.22   type1
CA  q1.22   nontype1

当前代码问题 #

当前实现依赖固定行列索引，输出不符合预期且无法适配表格结构变化，错误输出示例：

ID  date    TYPE
0   id  id  Unnamed: 0
1   id  id  q1.22
2   NY  id  Unnamed: 0
3   NY  id  q1.22
...

改进方案代码 #

核心思路 #

1. 重构原始表格，修复表头与数据行的对应关系，摆脱固定索引依赖
2. 动态识别目标季度、ID列及需要统计的TYPE列（排除非目标列与总计行）
3. 根据单元格数值循环生成对应次数的记录

import pandas as pd

# 加载数据（实际场景替换为pd.read_excel读取Excel文件）
df = pd.DataFrame(data)

# 步骤1：重构表格结构，修复表头和数据行
real_columns = ['ID'] + df.iloc[0, 1:].tolist()
# 提取有效数据行：跳过表头行，排除总计行
data_rows = df.iloc[1:-1].copy()
data_rows.columns = real_columns

# 步骤2：定义目标配置，可根据需求调整
target_quarter = 'q1.22'
exclude_cols = ['OFFICE']  # 不需要统计的列
type_cols = [col for col in real_columns if col not in ['ID'] + exclude_cols]

# 步骤3：动态生成目标数据
captured_data = []
for _, row in data_rows.iterrows():
    current_id = row['ID']
    for col in type_cols:
        count = int(row[col])
        # 按计数添加对应次数的记录
        for _ in range(count):
            captured_data.append({
                'ID': current_id,
                'date': target_quarter,
                'TYPE': col
            })

# 转换为目标DataFrame
output_df = pd.DataFrame(captured_data)
print(output_df)

代码优势 #

• 不依赖固定行列索引，表格新增行/列时，只要保持ID列、季度列的命名规则即可自动适配
• 可灵活配置需要排除的非目标列，仅统计指定TYPE列
• 严格按照单元格数值生成对应次数的记录，完全匹配期望输出逻辑

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