Python高效读取大型Excel数据：pandas与openpyxl按行读取实战

1. 项目缘起为什么数学建模要按行读取Excel做数学建模的朋友尤其是参加国赛、美赛这类竞赛的应该都经历过这个场景拿到一个几十兆甚至上百兆的Excel数据文件里面可能包含几十万行数据但你的模型可能只需要分析其中特定条件下的几百行或者需要按某种顺序分批处理。这时候如果你一股脑用pandas.read_excel把整个文件读进内存轻则程序卡顿内存飙升重则直接报MemoryError比赛时间宝贵这种低级错误足以让人心态爆炸。我最初也是这么干的直到在一次处理人口普查数据时吃了大亏。一个200MB的xlsx文件用pd.read_excel读取后内存占用直接冲到2GB后续计算根本进行不下去。后来才明白对于大规模数据尤其是Excel这种格式“按需读取”不是一种优化技巧而是必备的生存技能。它背后的核心思想是只把当前计算需要的数据加载到内存中用完后及时释放像流水一样处理数据而不是试图把整个水库搬进家里。Python生态里pandas是数据处理的事实标准但很多人只熟悉read_csv和read_excel这种“全量读取”模式。实际上结合pandas、openpyxl用于.xlsx或xlrd用于旧版.xls我们可以实现非常灵活的行级读取。这不仅适用于数学建模任何涉及大型Excel文件分析的场景如金融数据分析、运营报表处理、科研数据清洗都能从中受益。2. 核心工具链pandas与底层引擎的协作要实现按行读取首先要理解pandas读取Excel的底层机制。pandas本身并不直接解析Excel文件它更像一个指挥官调用不同的“引擎”来干活。openpyxl这是处理.xlsx格式Excel 2007及以上的主流引擎。它支持完整的Excel功能如公式、图表、样式并且能够以只读模式高效地流式读取数据这是我们实现按行读取的关键。xlrd曾经是读取.xls格式Excel 97-2003的引擎但新版已停止支持.xlsx。对于旧文件有时仍会用到。odf用于处理OpenDocument格式.ods。当我们调用pd.read_excel(‘data.xlsx’)时pandas默认会使用openpyxl引擎将整个工作表加载到一个类似网格的内存对象中然后再转换成DataFrame。要实现按行读取我们就需要绕过pandas的默认全量加载直接与openpyxl的“只读、优化模式”对话或者利用pandas本身提供的分块参数。这里有一个关键选择你的“按需”是“按顺序分批”还是“按条件跳跃式读取”按顺序分批比如每次读1000行进行处理适合数据清洗、逐行计算等场景。pandas的read_excel函数有一个鲜为人知的参数chunksize在读取CSV时常用但在Excel中需要引擎支持openpyxl的只读模式可以配合迭代。按条件跳跃式读取比如只要“城市‘北京’”且“年份2020”的数据。这通常无法在读取时直接过滤除非数据已排序且条件简单更常见的策略是先快速读取关键列如索引列、条件列到内存确定所需行的位置再精准读取这些行的其他列数据。3. 实战方法一使用openpyxl引擎进行流式行迭代这是最接近“按行读取”概念的方法尤其适合处理超大型文件且不需要pandas高级功能如自动类型推断的初步数据探查或简单处理。核心原理openpyxl提供了load_workbook(..., read_onlyTrue)模式。在此模式下它不会将整个工作表加载到内存而是像扫描文档一样一行一行地读取。我们可以遍历这个生成器每次获取一行原始单元格数据。操作步骤与代码详解from openpyxl import load_workbook # 1. 以只读模式打开工作簿 # read_onlyTrue 是关键它启用流式读取。 # data_onlyTrue 会计算并返回单元格的值而不是公式本身。 wb load_workbook(filename大型数据集.xlsx, read_onlyTrue, data_onlyTrue) # 2. 选择活动工作表或指定名称的工作表 ws wb.active # 或 ws wb[Sheet1] # 3. 迭代工作表的行 # ws.iter_rows() 返回一个生成器每次迭代产生一行一个由Cell对象组成的元组。 # values_onlyTrue 直接返回单元格的值而不是Cell对象更高效。 # min_row和max_row可以指定读取的范围。 data_rows [] for row in ws.iter_rows(min_row2, values_onlyTrue): # 假设第一行是标题 # row 是一个元组例如 (1, ‘北京‘, 2023, 1500.5) # 这里可以进行条件判断实现“按需” if row[1] ‘北京‘: # 假设第二列是城市 # 注意此时row只是普通元组不是pandas DataFrame。 # 对于复杂处理可以先收集再批量转为DataFrame。 data_rows.append(row) # 如果满足条件的数据量足够可以在这里进行分批处理然后清空data_rows以释放内存 if len(data_rows) 10000: process_batch(data_rows) # 你的处理函数 data_rows [] # 处理最后一批数据 if data_rows: process_batch(data_rows) # 4. 关闭工作簿重要 wb.close()为什么这样做内存友好read_only模式确保内存中只保留当前正在处理的行峰值内存使用量极低。控制粒度细你可以精确控制从哪一行开始(min_row)到哪一行结束(max_row)以及处理哪些行。注意事项与踩坑点注意openpyxl的read_only模式是只读的你不能修改单元格或样式。它对于公式的处理是data_only参数决定True取计算结果False取公式字符串。如果文件中有公式且需要最新结果确保在Excel中保存并计算过一次。 另一个坑是iter_rows返回的是Cell对象或值的元组不是pandas的Series。如果你后续分析严重依赖pandas的向量化运算频繁在列表和DataFrame间转换可能会有开销。此时方法二分块读取可能更合适。4. 实战方法二pandas的read_excel配合分块与条件过滤如果你的数据处理逻辑重度依赖pandas且数据量还没大到必须用openpyxl原始迭代那么可以尝试在pandas的框架内实现“按需”。思路我们分两步走。第一步利用usecols参数快速读取判断条件所需的列通常数据量很小。第二步根据筛选出的行索引利用skiprows和nrows参数精准读取目标行。场景模拟假设我们有一个销售数据sales.xlsx包含订单ID、城市、销售额、日期等列。我们需要分析“上海”地区在“2023-01-01”之后的销售情况。import pandas as pd # 第一步快速读取关键列定位目标行 # 只读取‘城市‘和‘日期‘两列大幅减少I/O和内存占用 df_cond pd.read_excel(‘sales.xlsx‘, usecols[‘城市‘, ‘日期‘]) # 应用过滤条件得到布尔序列 condition (df_cond[‘城市‘] ‘上海‘) (df_cond[‘日期‘] ‘2023-01-01‘) # 获取满足条件的行索引注意这是文件中的行索引从0开始包含标题行 # pd.read_excel默认认为第一行是标题所以数据从0开始。但skiprows参数指的是文件行号从0开始。 # 这里需要仔细处理索引偏移。更稳妥的方法是获取行号iloc索引2因为0是标题1是数据第一行。 target_row_indices df_cond[condition].index.tolist() # 这是DataFrame的索引从0开始 # 转换为文件中的行号假设标题行占一行 target_file_row_numbers [i 2 for i in target_row_indices] # 1 for 0-based to 1-based, 1 for header # 第二步精准读取目标行 # 但pd.read_excel没有直接按行号列表读取的功能。我们需要换一种思路。 # 方案A如果目标行是连续的可以用skiprows和nrows。 # 方案B使用openpyxl迭代并过滤如方法一但最后用pd.DataFrame构建。 # 方案C推荐如果条件过滤后的数据量已经可以装入内存直接读取全部然后过滤。 # 这里展示方案C的变种结合read_excel的skiprows进行“粗略”分块在块内过滤。 def read_excel_in_chunks(file_path, chunk_size10000): 一个模拟分块读取Excel的函数注意pandas的read_excel没有原生chunksize参数 total_rows pd.read_excel(file_path, nrows0).shape[0] # 先读标题获取总行数不准确仅示例 for start_row in range(1, total_rows 1, chunk_size): # 从第1行数据开始跳过标题后 # 注意skiprows跳过的是文件行号。这里我们跳过前面的行读取chunk_size行。 df_chunk pd.read_excel(file_path, skiprowsrange(1, start_row), nrowschunk_size) if df_chunk.empty: break # 在块内应用你的条件过滤 df_filtered_chunk df_chunk[(df_chunk[‘城市‘] ‘上海‘) (df_chunk[‘日期‘] ‘2023-01-01‘)] yield df_filtered_chunk # 使用生成器逐块处理 all_filtered_data [] for chunk in read_excel_in_chunks(‘sales.xlsx‘, chunk_size5000): # 对每个块进行你的建模分析 # process_chunk(chunk) all_filtered_data.append(chunk) # 最后合并所有过滤后的块如果内存允许 final_df pd.concat(all_filtered_data, ignore_indexTrue)为什么这样设计利用pandas优势在分块内部我们可以充分利用pandas强大的向量化运算和条件过滤代码更简洁。平衡I/O与内存通过控制chunk_size我们可以平衡单次I/O读取的数据量和内存占用。如果单个块过滤后数据量仍然很大可以在yield之前就进行聚合计算如求和、求平均只保留结果进一步节省内存。重要踩坑点最大的坑在于skiprows和行号计算。pd.read_excel的skiprows参数接受一个列表列表中的数字是文件行号从0开始计数。这意味着标题行是第0行。如果你要跳过前100行数据应该写skiprowsrange(1, 101)跳过第1到第100行数据行因为第0行是标题。在写循环时务必厘清这个索引关系否则会出现数据错位。我建议在关键步骤打印df_chunk.head()来验证读取的起始位置是否正确。5. 性能对比与选型策略两种方法没有绝对的好坏只有适合的场景。特性openpyxl流式迭代 (方法一)pandas分块过滤 (方法二)内存占用极低仅当前行在内存较低取决于chunk_size大小I/O次数一次顺序读取多次读取次数总行数/块大小功能灵活性较低获得原始值或元组高直接获得DataFrame可使用全部pandas功能开发便利性需要手动解析行、处理类型转换便利pandas自动处理类型、标题适用场景1. 文件极大内存严格受限2. 只需简单提取或计数3. 数据格式不规则需自定义解析1. 需要进行复杂条件过滤、计算2. 数据量中等可接受分块3. 希望代码与pandas生态无缝集成选型建议如果你的数据是“宽表”列很多但只关心其中几列优先使用pd.read_excel(usecols[...])读取指定列这是最简单有效的“按需”。如果你的数据是“长表”行很多且过滤条件复杂可以尝试组合技先用usecols快速读取条件列确定目标行号范围。如果目标行连续再用skiprows和nrows读取如果不连续且目标行数不多可以考虑用openpyxl迭代并收集这些行最后用pd.DataFrame构造。对于超大规模、仅需一次性扫描的任务如统计行数、查找特定字符串openpyxl的read_only模式是唯一选择。6. 高级技巧与边界情况处理在实际数学建模中数据往往没那么“干净”。下面分享几个处理棘手情况的技巧。1. 处理多个工作表如果数据分布在多个sheet中且需要统一处理可以在迭代时加入循环。wb load_workbook(‘data.xlsx‘, read_onlyTrue) for sheet_name in wb.sheetnames: ws wb[sheet_name] for row in ws.iter_rows(values_onlyTrue): # 处理每一行可以加上sheet_name作为标识 process_row(row, sheet_name)2. 动态判断数据类型openpyxl读取的值都是Python基础类型。如果后续需要正确的数据类型如日期需要手动转换。from datetime import datetime def convert_cell_value(value): if isinstance(value, datetime): return value.date() # 或保持datetime # 处理其他类型... return value # 在迭代中使用 converted_row [convert_cell_value(cell) for cell in row]3. 内存监控与优化对于长时间运行的任务监控内存是好事。import psutil import os def get_memory_usage(): process psutil.Process(os.getpid()) return process.memory_info().rss / 1024 ** 2 # 返回MB # 在读取循环中定期打印 if row_count % 10000 0: print(f“已处理 {row_count} 行当前内存占用{get_memory_usage():.2f} MB“)4. 应对“打开文件”错误如果文件被其他程序如Excel软件锁定读取会失败。最好加入异常处理和重试机制。import time def safe_read_excel(file_path, max_retries3): for i in range(max_retries): try: df pd.read_excel(file_path) return df except PermissionError: if i max_retries - 1: print(f“文件被占用第{i1}次重试...“) time.sleep(2) else: raise return None7. 一个完整的数学建模数据读取示例假设我们为“城市交通流量预测”建模数据文件traffic.xlsx包含时间戳、监测点ID、车流量三列文件非常大。我们只需要分析“监测点ID1001”在早高峰7:00-9:00的数据。import pandas as pd from openpyxl import load_workbook from datetime import time def stream_process_large_excel(file_path, target_site_id, time_start, time_end): 流式处理大型Excel文件提取特定监测点早晚高峰数据。 wb load_workbook(filenamefile_path, read_onlyTrue, data_onlyTrue) ws wb.active # 假设第一行是标题时间戳, 监测点ID, 车流量 data_records [] row_count 0 for row in ws.iter_rows(min_row2, values_onlyTrue): # 从第2行开始 timestamp, site_id, volume row # 解包 row_count 1 # 类型安全处理 try: # 确保timestamp是datetime.time对象这里假设Excel中已是时间格式 if not isinstance(timestamp, time): # 如果是datetime提取time部分 if hasattr(timestamp, ‘time‘): t timestamp.time() else: continue # 跳过格式错误行 else: t timestamp # 条件过滤 if site_id target_site_id and time_start t time_end: data_records.append((timestamp, site_id, volume)) # 每处理50000行批量处理一次并清空缓存防止列表过大 if len(data_records) 50000: # 这里可以调用建模的预处理函数例如计算当前批次的一些统计量 # process_batch_for_model(data_records) # 为了示例我们简单打印 print(f“已筛选到 {len(data_records)} 条目标数据处理中...“) # 模拟处理清空列表实际中可能是聚合或写入临时文件 data_records.clear() except Exception as e: print(f“第{row_count}行数据解析错误: {e} 数据行: {row}“) continue # 处理最后一批数据 if data_records: print(f“处理最后一批共 {len(data_records)} 条数据“) # final_processing(data_records) wb.close() print(f“流式读取完成。总共扫描 {row_count} 行。“) # 通常data_records在处理过程中已被清空或转移这里不返回。 # 实际应用中你可能将数据分批送入模型或聚合后返回统计结果。 # 调用函数提取监测点1001在早7点到9点的数据 stream_process_large_excel(‘traffic.xlsx‘, target_site_id1001, time_starttime(7,0), time_endtime(9,0))这个示例展示了在一个完整的数据处理流程中如何嵌入按行读取、条件过滤、异常处理和批量操作的逻辑。它没有一次性返回所有数据而是模拟了“边读边处理”的流式思维这对于内存受限的建模环境至关重要。最后记住一点在数学建模中数据读取不是目的而是第一步。选择哪种方法取决于你的数据规模、硬件条件以及后续模型的复杂程度。在比赛开始前用一个小样本测试一下你的读取方案的速度和内存占用花这点时间绝对值得它能帮你避开很多中途卡死的绝望时刻。