如何用Python Pandas从非常规Excel表格提取指定任务行？

问题分析 #

1. 类型不匹配：原始JSON中的任务编号是字符串格式（如"130101"），但你定义的task_number列表用的是整数类型，isin()因类型不一致无法匹配到目标行。
2. 表头位置错误：表格的实际表头在索引10的行（Unnamed:0值为Task的行），直接读取会把前面的标题行、空行混入数据，导致列名和数据对应混乱。

修正代码 #

import pandas as pd

# 原始JSON数据
excel_sheet_json = '''{"Unnamed: 0.1":0,"Unnamed: 0":"Some sentence titling the page","Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":1,"Unnamed: 0":null,"Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":2,"Unnamed: 0":null,"Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":3,"Unnamed: 0":"a thing:","Unnamed: 1":"sdftrhg","Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":9,"Unnamed: 0":null,"Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":10,"Unnamed: 0":"Task","Unnamed: 1":"Description","Unnamed: 2":"A","Unnamed: 3":"B","Unnamed: 4":"C","Unnamed: 5":null},{"Unnamed: 0.1":11,"Unnamed: 0":null,"Unnamed: 1":null,"Unnamed: 2":1,"Unnamed: 3":1,"Unnamed: 4":1,"Unnamed: 5":2.0},{"Unnamed: 0.1":12,"Unnamed: 0":"130","Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":13,"Unnamed: 0":"130101","Unnamed: 1":"something","Unnamed: 2":1,"Unnamed: 3":2,"Unnamed: 4":3,"Unnamed: 5":4.0},{"Unnamed: 0.1":14,"Unnamed: 0":null,"Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":15,"Unnamed: 0":null,"Unnamed: 1":"bam","Unnamed: 2":8,"Unnamed: 3":0,"Unnamed: 4":0,"Unnamed: 5":null},{"Unnamed: 0.1":16,"Unnamed: 0":null,"Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":17,"Unnamed: 0":"131","Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":18,"Unnamed: 0":"131269","Unnamed: 1":"something","Unnamed: 2":4,"Unnamed: 3":5,"Unnamed: 4":6,"Unnamed: 5":7.0},{"Unnamed: 0.1":43,"Unnamed: 0":null,"Unnamed: 1":null,"Unnamed: 2":null,"Unnamed: 3":null,"Unnamed: 4":null,"Unnamed: 5":null},{"Unnamed: 0.1":44,"Unnamed: 0":null,"Unnamed: 1":"bam","Unnamed: 2":8,"Unnamed: 3":0,"Unnamed: 4":0,"Unnamed: 5":null}'''

# 1. 直接将JSON转为DataFrame，无需存Excel再读取
df = pd.read_json(excel_sheet_json, lines=True)

# 2. 定位表头行（索引为10的行，Unnamed:0值为"Task"）
header_row_idx = 10
new_header = df.iloc[header_row_idx].values
# 从表头下一行开始加载有效数据
df_clean = df.iloc[header_row_idx+1:].reset_index(drop=True)
# 设置新表头
df_clean.columns = new_header

# 3. 处理Task列：将字符串转为整数（无法转换的空值转为NaN）
df_clean['Task'] = pd.to_numeric(df_clean['Task'], errors='coerce')

# 4. 定义要筛选的任务编号
task_number = [130101, 131269]

# 5. 筛选符合条件的行，排除Task为空的无效行
filtered_df = df_clean[df_clean['Task'].isin(task_number)].dropna(subset=['Task'])

# 查看筛选结果
print(filtered_df)

# 可选：保存为新Excel文件
filtered_df.to_excel("filtered_tasks.xlsx", index=False)

关键说明 #

• 跳过无效行：直接从表头行之后读取数据，避免前面的标题、空行干扰数据结构。
• 类型统一：通过pd.to_numeric将Task列转为数值类型，与task_number的整数格式匹配，确保筛选生效。
• 空值处理：errors='coerce'将无法转换的空值转为NaN，后续用dropna排除这些无效行，保证结果纯净。

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