使用Python将格式混乱的Excel数据转换为标准表格格式

当前格式 #

原始表格以日期行开头，随后是场地名称（Venue1-Venue5）和对应数量（QTY）的成对列，每日期下包含多行场地数据，示例数据如下：

0    1       2    3       4    5       6    7       8    9
0   01/01/2023  NaN     NaN  NaN     NaN  NaN     NaN  NaN     NaN  NaN
1       Venue1  QTY  Venue2  QTY  Venue3  QTY  Venue4  QTY  Venue5  QTY
2            A    0       A    0       A    1       A    0       A    0
3            B   17       B    3       B   11       B    3       B    0
4            C    0       C    0       C    1       C    0       C    0
5            D    0       D    0       D   29       D    0       D    0
6            E    0       E    0       E    0       E    0       E    0
7            F    0       F    0       F    0       F    0       F    0
8            G    0       G    0       G    0       G    0       G    0
9            H    0       H    0       H    0       H    0       H    0
10  02/01/2023  NaN     NaN  NaN     NaN  NaN     NaN  NaN     NaN  NaN
11      Venue1  QTY  Venue2  QTY  Venue3  QTY  Venue4  QTY  Venue5  QTY
12           A    0       A    0       A    1       A    0       A    0
13           B   11       B    3       B    0       B    6       B    2
14           C    0       C    0       C    0       C    0       C    0
15           D   20       D    0       D   28       D    0       D   24
16           E    0       E    0       E    0       E    0       E    0
17           F    0       F    0       F    0       F    0       F    0
18           G    0       G    0       G    0       G    0       G    0
19           H    0       H    0       H    0       H    0       H    0

截图说明：表格按日期分组，每组第一行显示日期，第二行是"Venue+QTY"的列标题，后续行是各场地的数量数据。

期望格式 #

重构为三列标准表格：日期、Venues（场地）、QTY（数量），每行对应单个日期+单个场地的数量记录。

截图说明：表格包含三列，依次为日期、完整场地名称、对应数量，所有日期的场地数据平铺展示。

解决方案（Pandas示例代码） #

以下是可直接套用的Pandas处理步骤：

1. 读取数据
   先读取目标Excel文件：

import pandas as pd

# 替换为你的文件路径
df = pd.read_excel("your_records.xlsx", header=None)

2. 标记并填充日期
   识别日期行，将日期向下填充到对应分组的所有行：

# 标记日期行：第一列是日期，其余列全为NaN
date_mask = df.iloc[:, 1:].isna().all(axis=1)
df['日期'] = df.loc[date_mask, 0]
# 向下填充，让每组数据绑定对应日期
df['日期'] = df['日期'].ffill()

3. 过滤有效数据行
   剔除日期行和Venue/QTY标题行，只保留实际场地数据：

# 移除日期行
df = df[~date_mask]
# 移除标题行（第一列以Venue开头）
title_mask = df[0].str.startswith('Venue', na=False)
df = df[~title_mask]

4. 重塑表格为长格式
   将宽格式转换为期望的三列结构：

# 定义Venue和QTY的列对索引：(0,1),(2,3)...(8,9)
col_pairs = [(i, i+1) for i in range(0, len(df.columns)-1, 2)]
result_dfs = []

for venue_col, qty_col in col_pairs:
    # 提取当前列对数据
    temp_df = df[['日期', venue_col, qty_col]].copy()
    # 重命名列
    temp_df.columns = ['日期', 'Venues', 'QTY']
    # 获取对应Venue名称（从标题行提取）
    venue_name = df.loc[title_mask, venue_col].iloc[0]
    # 拼接完整场地名称（如Venue1-A）
    temp_df['Venues'] = f"{venue_name}-{temp_df['Venues']}"
    result_dfs.append(temp_df)

# 合并所有结果
final_df = pd.concat(result_dfs, ignore_index=True)
# 转换QTY为数值类型
final_df['QTY'] = pd.to_numeric(final_df['QTY'], errors='coerce')

5. 保存处理结果
   将格式化后的表格导出为新Excel文件：

final_df.to_excel("formatted_records.xlsx", index=False)

补充说明 #

• 若场地命名规则不同，可调整temp_df['Venues']的拼接逻辑；
• 若日期识别不准确，可改用正则匹配：date_mask = df[0].str.contains(r'\d{2}/\d{2}/\d{4}')；
• 处理后可通过final_df.head()检查结构是否符合预期。

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