从同一份电子表格提取多表并合并为含年份日期列的连续DataFrame

核心思路 #

处理这类按年份分散的表格，核心是定位各年份表格的起始位置、提取年份信息，逐个处理后合并数据。主要用到的Pandas操作包括：pd.read_excel（分块读取指定范围）、pd.concat（合并数据框）、pd.melt（宽表转长表）、日期格式转换、字符串匹配。

具体步骤与代码 #

1. 先确认Excel结构（可选但关键） #

先读取全量无表头数据，定位各年份表格的标题行、数据行范围：

import pandas as pd

# 读取无表头的全量数据，查看结构
full_df = pd.read_excel('Worldwide Rig Count Nov 2022.xlsx', sheet_name='Worldwide_Rigcount', header=None)
print(full_df.head(30))  # 打印前30行，识别年份标题行（比如第一列为4位数字的行）和数据范围

2. 提取所有年份表格并合并为连续长表 #

假设通过查看发现：年份标题行第一列为4位数字，下一行是月份列（Jan、Feb等），数据从月份行下一行开始，到下一个年份行的前两行结束（因为表格间间隔一行）。代码如下：

import pandas as pd

# 读取全量无表头数据
full_df = pd.read_excel('Worldwide Rig Count Nov 2022.xlsx', sheet_name='Worldwide_Rigcount', header=None)

# 筛选所有年份标题行的索引（匹配4位数字）
year_rows = full_df[full_df[0].astype(str).str.match(r'^\d{4}$')].index.tolist()

all_year_data = []

for year_idx in year_rows:
    # 提取当前年份
    current_year = str(full_df.iloc[year_idx, 0])
    # 确定当前表格的结束行：下一个年份行的上一行，若无下一个年份则取表格末尾
    next_year_idx = year_rows[year_rows.index(year_idx)+1] if year_rows.index(year_idx)+1 < len(year_rows) else len(full_df)
    # 读取当前年份的表格：表头为年份行下一行，跳过末尾不需要的行
    temp_df = pd.read_excel('Worldwide Rig Count Nov 2022.xlsx',
                           sheet_name='Worldwide_Rigcount',
                           header=year_idx+1,
                           skipfooter=len(full_df) - (next_year_idx - 1))
    # 添加年份列
    temp_df['Year'] = current_year
    # 将月份列转成行，生成长表
    temp_df = temp_df.melt(id_vars=['Year', 'Unnamed: 0'],  # 'Unnamed: 0'为地区列，可根据实际重命名
                          var_name='Month',
                          value_name='Rig Count')
    # 生成完整日期列
    temp_df['Date'] = pd.to_datetime(temp_df['Month'] + ' ' + temp_df['Year'], format='%b %Y')
    # 整理列顺序
    temp_df = temp_df[['Date', 'Unnamed: 0', 'Rig Count']].rename(columns={'Unnamed: 0': 'Region'})
    all_year_data.append(temp_df)

# 合并所有数据，按日期排序（最新数据在末尾）
final_long_df = pd.concat(all_year_data).sort_values('Date').reset_index(drop=True)
print(final_long_df)

3. 可选：转置为年月作为列的宽表 #

如果需要将每个年月作为列、地区作为行，可基于上面的长表转置：

# 基于长表生成宽表
final_wide_df = final_long_df.pivot(index='Region', columns='Date', values='Rig Count')
print(final_wide_df)

关键Pandas操作说明 #

• pd.read_excel：通过header指定表头行、skipfooter跳过冗余行，实现分块读取单个年份表格
• pd.melt：将宽表（月份为列）转成长表，便于整合年份与日期
• pd.to_datetime：将"月份+年份"字符串转换为标准日期格式，支持后续同比分析
• pd.concat：合并多个年份的数据框
• pd.pivot：将长表转成宽表，满足按年月列展示的需求

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