csv.writer是否远快于pandas的to_csv?如何让二者输出结果一致?
问题解答
现存核心问题
- pandas to_csv 慢的根本原因是逻辑错误,不是方法本身性能差
你在循环内每次迭代都将之前所有采集到的df重新concat生成全量的finaldf,还每次都把全量数据写入CSV,总写入行数是 1+2+3+...+9000=4050万行,时间复杂度达到O(n²),才会出现导出9千行要数小时的问题,和to_csv本身性能无关。 - 自定义writerows函数存在多处错误
- 传入的rows是pandas DataFrame对象,不是csv.writer要求的可迭代行列表,直接把
[rows]传给writerows相当于把整个DataFrame当成单个元素写入,输出格式必然不符合预期 - 每次调用writerows都会写入一次表头,最终输出会重复出现N次表头
- 用追加模式写入全量数据,会导致数据重复写入
- 额外性能损耗
每个html页面你重复调用了两次pd.read_html读取同一张表(比如match='Name'的表每次读两次),完全没必要,额外拖慢了采集效率。
修正方案
方案1:修正to_csv用法即可满足性能要求(最简单,输出和你之前的预期完全一致)
不需要自己封装csv.writer,只要改下循环逻辑,性能就足够,9千行最多几秒就能写完:
import csv import datetime import numpy as np import pandas as pd from bs4 import BeautifulSoup as bs start = datetime.datetime.now() count = 0 allin = [] for html in results: count+=1 soup =bs(html,'html.parser') try : all = soup.find('td',class_='FootNote').text except : all = np.nan # 去掉重复的read_html调用 name_df = pd.read_html(html, match='Name')[0] name = name_df.set_index([0, name_df.groupby(0).cumcount()])[1].unstack(0) comname_df = pd.read_html(html, match='Company Name')[0] comname = comname_df.set_index([0, comname_df.groupby(0).cumcount()])[1].unstack(0) try : adm = pd.read_html(html, match='Admission Sponsor', index_col=0)[0].T except : adm = pd.DataFrame({'Admission Sponsor':np.nan,'Sponsor':np.nan},index=[0]) df = name.join(comname).join(adm) df['remark']= all allin.append(df) print(count,'|', end=' ') # 循环结束后一次性合并+写入,只写一次 finaldf = pd.concat(allin, ignore_index=True) finaldf.to_csv('testing01.csv', index=0) finish = datetime.datetime.now() - start print("Time Taken:",finish)
方案2:如果你一定要用csv.writer,修正后的写法如下
import csv import datetime import numpy as np import pandas as pd from bs4 import BeautifulSoup as bs def init_csv(filename, header): """初始化CSV文件,只写一次表头""" with open(filename,'w',encoding='UTF8',newline='') as f: writer = csv.writer(f) writer.writerow(header) def append_rows(rows, filename): """追加增量行数据,不写表头""" with open(filename,'a',encoding='UTF8',newline='') as f: writer = csv.writer(f) writer.writerows(rows) start = datetime.datetime.now() count = 0 # 先初始化文件,表头只写一次 first_run = True csv_name = 'testing.csv' for html in results: count+=1 soup =bs(html,'html.parser') try : all = soup.find('td',class_='FootNote').text except : all = np.nan name_df = pd.read_html(html, match='Name')[0] name = name_df.set_index([0, name_df.groupby(0).cumcount()])[1].unstack(0) comname_df = pd.read_html(html, match='Company Name')[0] comname = comname_df.set_index([0, comname_df.groupby(0).cumcount()])[1].unstack(0) try : adm = pd.read_html(html, match='Admission Sponsor', index_col=0)[0].T except : adm = pd.DataFrame({'Admission Sponsor':np.nan,'Sponsor':np.nan},index=[0]) df = name.join(comname).join(adm) df['remark']= all # 第一次运行先写表头 if first_run: init_csv(csv_name, df.columns.tolist()) first_run = False # 把当前df转成列表,追加写入,只写当前批次的增量数据 append_rows(df.values.tolist(), csv_name) print(count,'|', end=' ') finish = datetime.datetime.now() - start print("Time Taken:",finish)
两种方案的导出结果都和你之前正确的to_csv输出完全一致,性能提升至少上千倍,9千行数据最多几秒就能完成导出。
内容的提问来源于stack exchange,提问作者Yazid Yaakub
相关产品推荐
相关产品推荐

