使用Python读取特定结构Excel并转换数据格式的技术求助

需求概述 #

• 读取Excel文件，左侧包含15个固定列，右侧是延伸5年的结构化数据（红色标记区域）
• 需要将右侧数据转换为目标层级结构后，与左侧固定列合并

当前处理步骤 #

1. 读取固定列区域（起止位置可确定）
2. 读取右侧红色标记区域的数据
3. 合并两部分数据

右侧数据示例DataFrame #

df = pd.DataFrame({'col1':  {0: 'Year'  , 1: 'Option'   , 2: 'Category' , 3: 'Type' , 4: 'Country'  , 5: 'Australia', 6: 'New Zealand'},
                   'col2':  {0: '2024'  , 1: 'S'        , 2: 'FTE'      , 3: 'A'    , 4: ''         , 5: '-1,0'     , 6: '-2,0'},
                   'col3':  {0: ''      , 1: ''         , 2: 'Budget'   , 3: 'B'    , 4: 'EUR'      , 5: '-100,5'   , 6: '-200,5'},
                   'col4':  {0: ''      , 1: ''         , 2: ''         , 3: 'C'    , 4: 'EUR'      , 5: '-1000'    , 6: '-2000'},
                   'col5':  {0: ''      , 1: 'T'        , 2: 'FTE'      , 3: 'A'    , 4: ''         , 5: '1,0'      , 6: '2,0'},
                   'col6':  {0: ''      , 1: ''         , 2: 'Budget'   , 3: 'B'    , 4: 'EUR'      , 5: '100,5'    , 6: '200,5'},
                   'col7':  {0: ''      , 1: ''         , 2: ''         , 3: 'C'    , 4: 'EUR'      , 5: '1000'     , 6: '2000'},
                   'col8':  {0: '2025'  , 1: 'S'        , 2: 'FTE'      , 3: 'A'    , 4: ''         , 5: '-3,0'     , 6: '-4,0'},
                   'col9':  {0: ''      , 1: ''         , 2: 'Budget'   , 3: 'B'    , 4: 'EUR'      , 5: '-300,5'   , 6: '-400,5'},
                   'col10': {0: ''      , 1: ''         , 2: ''         , 3: 'C'    , 4: 'EUR'      , 5: '3000'     , 6: '-4000'},
                   'col11': {0: ''      , 1: 'T'        , 2: 'FTE'      , 3: 'A'    , 4: ''         , 5: '3,0'      , 6: '4,0'},
                   'col12': {0: ''      , 1: ''         , 2: 'Budget'   , 3: 'B'    , 4: 'EUR'      , 5: '300,5'    , 6: '400,5'},
                   'col13': {0: ''      , 1: ''         , 2: ''         , 3: 'C'    , 4: 'EUR'     , 5: '3000'     , 6: '4000'},
                   })

当前瓶颈 #

通过df = pd.read_excel(...usecols='T:Z', header=None...)读取数据和表头，再用df.columns = pd.MultiIndex.from_arrays(...)设置多级列索引后，得到的2024年数据结构如下：

尝试用.stack()和.melt()转换为目标结构，但未成功，需要解决方案。

解决方案 #

步骤1：分离表头与数据 #

先从示例DataFrame中提取表头行和实际数据行：

# 提取前4行作为表头（Year、Option、Category、Type层级）
header_rows = df.iloc[:4].T
# 提取第5-6行作为国家数据，设置Country为索引
data_rows = df.iloc[4:].set_index('col1')

步骤2：填充表头缺失值并设置多级列索引 #

表头存在空值，需要向前填充补全各层级信息：

# 填充年份层级（第一列空值用前值补全）
header_rows[0] = header_rows[0].ffill()
# 填充Option层级（第二列空值用前值补全）
header_rows[1] = header_rows[1].ffill()
# 填充Category层级（第三列空值用前值补全）
header_rows[2] = header_rows[2].ffill()

# 将表头转为多级列索引
data_rows.columns = pd.MultiIndex.from_frame(
    header_rows[[0,1,2,3]],
    names=['Year', 'Option', 'Category', 'Type']
)

步骤3：重塑为长格式目标结构 #

使用stack()将列层级转为行维度，得到扁平化结构：

# 堆叠所有列层级，保留Country作为索引
final_data = data_rows.stack(['Year', 'Option', 'Category', 'Type']).reset_index()
# 重命名列名
final_data.columns = ['Country', 'Year', 'Option', 'Category', 'Type', 'Value']
# 处理数值格式（替换逗号为小数点，转为float类型）
final_data['Value'] = final_data['Value'].str.replace(',', '.').astype(float)

步骤4：合并左侧固定列数据 #

假设左侧固定列数据存储在fixed_cols_df中，直接通过Country字段合并：

merged_df = pd.merge(fixed_cols_df, final_data, on='Country', how='left')

处理后的数据将以国家-年份-Option-类别-类型为单一行记录，完全适配目标结构需求。

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