Excel透视表加载异常：Pandas读取数据失败求助

核心问题分析 #

1. 数据读取错位：透视表的合并单元格结构导致skiprows=10后，表头与数据未正确对齐，出现大量空值；同时df_tmv.iloc[:,1]查找"Grand Total"的逻辑失效，因为透视表合并单元格后目标值可能不在第二列，或读取后该列无有效内容。
2. 索引逻辑混淆：代码中混用了Excel行号（1基）与DataFrame索引（0基），比如totals_start_row=11是Excel行号，但DataFrame读取后索引从0开始，导致后续循环范围完全错误。
3. 循环范围错误：range(totals_start_row, totals_start_row + totals_end_row, 8)的终止条件逻辑混乱，未基于DataFrame的实际索引范围计算。
4. 写入格式冲突：直接加载原文件的透视表格式后写入新sheet，易引发格式兼容问题，导致输出数据异常。

修正步骤与代码 #

1. 先确认数据结构 #

读取文件后先打印数据头部信息，明确表头、合并单元格填充情况、"Grand Total"及"Bicycles on Road"的位置：

# 读取后执行以下代码查看结构
print(df_tmv.head(30))
print(df_tmv.info())

2. 修正后的完整代码 #

import os
import pandas as pd
from openpyxl import load_workbook

input_directory = "./"
completed_files = []

for filename in os.listdir(input_directory):
    file_path = os.path.join(input_directory, filename)
    # 过滤支持的文件类型
    if not filename.endswith((".xlsx", ".xls")):
        print(f"不支持的文件类型: {filename}")
        continue

    # 根据文件类型选择引擎
    engine = 'openpyxl' if filename.endswith(".xlsx") else None
    df_tmv = pd.read_excel(file_path, sheet_name='TMV Table', engine=engine)
    
    # 填充透视表合并单元格的空值，确保行标签可识别
    df_tmv.iloc[:, 0] = df_tmv.iloc[:, 0].ffill()
    df_tmv.iloc[:, 1] = df_tmv.iloc[:, 1].ffill()
    
    # 定位Grand Total行（处理可能的字符串匹配问题）
    grand_total_mask = df_tmv.apply(lambda row: 'Grand Total' in str(row.values), axis=1)
    if not grand_total_mask.any():
        print(f"{filename}中未找到Grand Total行")
        continue
    grand_total_row = df_tmv[grand_total_mask].index[0]
    
    # 只处理Grand Total之前的数据
    df_process = df_tmv.iloc[:grand_total_row].copy()
    
    # 假设每个时间戳组为8行，最后一行是Bicycles on Road（0基索引为start_idx+6）
    group_size = 8
    for start_idx in range(0, len(df_process), group_size):
        end_idx = start_idx + group_size
        if end_idx > len(df_process):
            break
        # 获取Bicycles on Road行的数值列数据
        bike_values = df_process.iloc[start_idx + 6, 2:].astype(float)
        # 对组内所有行的数值列做减法
        df_process.iloc[start_idx:end_idx, 2:] = df_process.iloc[start_idx:end_idx, 2:].astype(float) - bike_values
    
    # 写入新sheet，覆盖已存在的同名sheet
    with pd.ExcelWriter(file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
        df_process.to_excel(writer, sheet_name='TMV Table without Bikes', index=False)
    
    completed_files.append(filename)

print("处理完成的文件：")
for file in completed_files:
    print(file)

额外调试建议 #

• 若数值列存在非数值内容，先执行清洗：df_process.iloc[:,2:] = pd.to_numeric(df_process.iloc[:,2:], errors='coerce')，避免类型转换报错。
• 若每个时间戳组的行数不是固定8行，可通过行标签（如"Total"）定位每组起始行，再找到对应的"Bicycles on Road"行。

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