更快的读取Excel文件到pandas数据框的方法

Question

我有一个包含五个工作表的14MB Excel文件，我正在将其读入Pandas数据帧中，虽然下面的代码有效，但需要9分钟！

有人有加速建议吗？

import pandas as pd

def OTT_read(xl,site_name):
    df = pd.read_excel(xl.io,site_name,skiprows=2,parse_dates=0,index_col=0,
                       usecols=[0,1,2],header=None,
                       names=['date_time','%s_depth'%site_name,'%s_temp'%site_name])
    return df

def make_OTT_df(FILEDIR,OTT_FILE):
    xl = pd.ExcelFile(FILEDIR + OTT_FILE)
    site_names = xl.sheet_names
    df_list = [OTT_read(xl,site_name) for site_name in site_names]
    return site_names,df_list

FILEDIR='c:/downloads/'
OTT_FILE='OTT_Data_All_stations.xlsx'
site_names_OTT,df_list_OTT = make_OTT_df(FILEDIR,OTT_FILE)

- jsignell

---

请尝试将文件保存为 CSV 格式并加载，可能 Excel 读取速度不如 CSV 快。 - EdChum

它有多个工作表，那不会行吗？ - jsignell

你应该仍然能够保存每个工作表，但不幸的是，这里痛苦的是你必须单独地保存每个工作表。14MB 不是一个很大的大小，CSV 读取器将会非常快速地处理它。另一个观点也许是尝试 ExcelFile.parse。 - EdChum

Answer 1

正如其他人所建议的那样，CSV读取速度更快。因此，如果您使用Windows并且有Excel，则可以调用VBScript将Excel转换为CSV，然后读取CSV。我尝试了下面的脚本，大约需要30秒。

# create a list with sheet numbers you want to process
sheets = map(str,range(1,6))

# convert each sheet to csv and then read it using read_csv
df={}
from subprocess import call
excel='C:\\Users\\rsignell\\OTT_Data_All_stations.xlsx'
for sheet in sheets:
    csv = 'C:\\Users\\rsignell\\test' + sheet + '.csv' 
    call(['cscript.exe', 'C:\\Users\\rsignell\\ExcelToCsv.vbs', excel, csv, sheet])
    df[sheet]=pd.read_csv(csv)

这里是一小段Python代码，用于创建ExcelToCsv.vbs脚本：

#write vbscript to file
vbscript="""if WScript.Arguments.Count < 3 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file> <worksheet number (starts at 1)>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))
worksheet_number = CInt(WScript.Arguments.Item(2))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
oBook.Worksheets(worksheet_number).Activate

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
""";

f = open('ExcelToCsv.vbs','w')
f.write(vbscript.encode('utf-8'))
f.close()

此答案受益于在命令行上将XLS转换为CSV以及csv和xlsx文件导入到pandas数据框架：速度问题

- Rich Signell

---

如果在Linux上，解决方案是什么？ - Sidhartha

之前出现了“TypeError: write() argument must be str, not bytes”的错误，所以我将其更改为f = open('ExcelToCsv.vbs','wb')。谢谢。 - Anuj Sharma

转换文件为CSV时要小心。Excel的CSV转换器会对数据进行处理。例如，它会从文本中删除前导零。它可能会将波士顿的邮政编码从02108转换为2108。它无法很好地处理包含逗号和引号混合的值。它会干扰你的数字格式，可能会丢失数据。 - undefined

Answer 2

我使用xlsx2csv将Excel文件在内存中虚拟转换为CSV格式，这有助于将读取时间缩短约一半。

from xlsx2csv import Xlsx2csv
from io import StringIO
import pandas as pd


def read_excel(path: str, sheet_name: str) -> pd.DataFrame:
    buffer = StringIO()
    Xlsx2csv(path, outputencoding="utf-8", sheet_name=sheet_name).convert(buffer)
    buffer.seek(0)
    df = pd.read_csv(buffer)
    return df

- Jing Xue

---

我尝试使用这段代码，但我认为它可能已经过时了。对于任何想要使用它的人，请尝试以下代码：from io import StringIO from openpyxl import load_workbook import pandas as pd def read_excel(path: str, sheet_index: int) -> pd.DataFrame: buffer = StringIO() Xlsx2csv(path, outputencoding="utf-8").convert(buffer,sheetid=sheet_index) buffer.seek(0) df = pd.read_csv(buffer, low_memory=False) return df``` - toubi

在我的测试中，pd.read_excel（excel_file，sheet_name = None）和Xlsx2csv版本之间的性能差异很小，有时候Xlsx2csv会慢一些。我的文件有5个工作表，每个工作表有两列和约9k行。 - Vlad Iliescu

在我的实践中，“Xlsx2csv” 大约节省了 40% 的时间。 - Alpha

Answer 3

如果每个工作表中的行数少于65536行，您可以尝试使用xls（而不是xlsx）。根据我的经验，xls比xlsx更快。很难与csv进行比较，因为这取决于工作表的数量。

虽然这不是一个理想的解决方案（xls是一种二进制旧专有格式），但我发现在处理许多工作表、带有经常更新的值的内部公式或出于任何您真正需要保留Excel多表功能（而不是CSV分隔文件）的原因时，这非常有用。

- toto_tico

Answer 4

我鼓励你自己进行比较，看哪种方法适合你的情况。

例如，如果你要处理大量的XLSX文件，且每个文件只需要读取一次，那么你可能不需要考虑CSV转换。但是，如果你将反复阅读CSV，则强烈建议将工作簿中的每个工作表保存为csv文件，然后使用pd.read_csv()重复读取它们。

下面是一个简单的脚本，可以让您比较直接导入XLSX、在内存中转换XLSX为CSV和导入CSV。它基于Jing Xue的答案。

剧透：如果你将多次读取文件，将XLSX转换为CSV会更快。

我对一些正在处理的文件进行了测试，以下是我的结果：

5,874 KB xlsx file (29,415 rows, 58 columns)
Elapsed time for [Import XLSX with Pandas]:    0:00:31.75
Elapsed time for [Convert XLSX to CSV in mem]: 0:00:22.19
Elapsed time for [Import CSV file]:            0:00:00.21

********************
202,782 KB xlsx file (990,832 rows, 58 columns)
Elapsed time for [Import XLSX with Pandas]:    0:17:04.31
Elapsed time for [Convert XLSX to CSV in mem]: 0:12:11.74
Elapsed time for [Import CSV file]:            0:00:07.11

是的！相比于XLSX格式，这个202MB的文件只需要7秒就能处理完毕，而XLSX格式则需要17分钟！！！

如果你准备好自己进行测试，只需在Excel中打开XLSX文件并将任意一个工作表保存为CSV即可。对于最终解决方案，显然需要循环遍历每个工作表以进行处理。

你还需要执行pip install rich pandas xlsx2csv命令来安装所需的库。

from rich import print
import pandas as pd
from datetime import datetime
from xlsx2csv import Xlsx2csv
from io import StringIO

def timer(name, startTime = None):
    if startTime:
        print(f"Timer: Elapsed time for [{name}]: {datetime.now() - startTime}")
    else:
        startTime = datetime.now()
        print(f"Timer: Starting [{name}] at {startTime}")
        return startTime


def read_excel(path: str, sheet_name: str) -> pd.DataFrame:
    buffer = StringIO()
    Xlsx2csv(path, outputencoding="utf-8", sheet_name=sheet_name).convert(buffer)
    buffer.seek(0)
    df = pd.read_csv(buffer)
    return df


xlsxFileName = "MyBig.xlsx"
sheetName = "Sheet1"
csvFileName = "MyBig.csv"

startTime = timer(name="Import XLSX with Pandas")
df = pd.read_excel(xlsxFileName, sheet_name=sheetName)
timer("Import XLSX with Pandas", startTime)

startTime = timer(name="Convert XLSX to CSV first")
df = read_excel(path=xlsxFileName, sheet_name=sheetName)
timer("Convert XLSX to CSV first", startTime)

startTime = timer(name="Import CSV")
df = pd.read_csv(csvFileName)
timer("Import CSV", startTime)

- Chris Harris

Answer 5

根据我的经验，Pandas的read_excel()函数可以很好地处理包含多个工作表的Excel文件。正如使用Pandas读取多个工作表中建议的那样，如果将sheet_name分配为None，它将自动将每个工作表放入Dataframe中，并输出带有工作表名称键的Dataframes字典。

但是，代码中需要解析文本的位置是导致耗时的原因。5张工作表的14MB的Excel并不算太大。我有一个20.1MB的Excel文件，其中有46张工作表，每个工作表都有超过6000行和17列，使用read_excel的时间大约如下：

t0 = time.time()

def parse(datestr):
    y,m,d = datestr.split("/")
    return dt.date(int(y),int(m),int(d))

data = pd.read_excel("DATA (1).xlsx", sheet_name=None, encoding="utf-8", skiprows=1, header=0, parse_dates=[1], date_parser=parse)

t1 = time.time()

print(t1 - t0)
## result: 37.54169297218323 seconds

在上面的代码中，data 是一个由 46 个数据框组成的字典。

正如其他人建议的那样，使用 read_csv() 可以帮助提速，因为读取 .csv 文件更快。但请注意，由于 .xlsx 文件使用压缩，所以 .csv 文件可能会更大，因此读取速度较慢。但如果你想使用 Python 将文件转换为逗号分隔格式（VBCode 由 Rich Signel 提供），可以使用：将 xlsx 转换为 csv。

- Ehsan Tabatabaei

Answer 6

我知道这个问题已经很老了，但如果有人正在寻找不涉及VB的答案，那么Pandas的read_csv()确实更快，但您不需要VB脚本来获取csv文件。

打开Excel文件并另存为*.csv（逗号分隔值）格式。

在工具下，您可以选择Web选项，在编码选项卡下，您可以更改编码以适应您的数据。我最终使用了Windows西欧语言编码，因为Windows UTF编码是“特殊”的，但有很多方法可以实现相同的效果。然后使用pd.read_csv()中的编码参数指定您的编码。

编码选项列在此处

- kirby

Answer 7

最近遇到了类似的问题，通过使用xlwings库解决了它。

import xlwings as xw
import pandas as pd


def xw_excel_to_df(file_path, sheet_name=None, sheet_range=None):
    app = xw.App()
    book = xw.Book(file_path, read_only=True)

    if sheet_name is None:
        sheet_name = book.sheets[0].name

    if sheet_range is None:
        data = book.sheets[sheet_name]["A1"].expand().value
    else:
        data = book.sheets[sheet_name][sheet_range].value

    df = pd.DataFrame(data=data[1:], columns=data[0])

    book.close()
    app.quit()

    return df

基本上，它在本地打开一个Excel实例，读取数据，将其加载到数据框中，然后关闭Excel。

您必须在您的电脑上安装Excel，然后可能需要解决一些依赖关系才能在远程使用此资源。

顺便说一下，我在使用jupyter notebook进行一些本地分析时，它对我来说完美无缺！ :)

- Caio

Answer 8

如果你愿意处理一次缓慢的转换，没有理由打开 Excel。

1. 使用 pd.read_excel() 将数据读入数据框架中
2. 立即使用 pd.to_csv() 将其转储到 CSV 中

避免使用 Excel 和 Windows 特定的调用。在我的情况下，这个一次性的时间消耗值得麻烦。我喝了一杯 ☕。

- Dan Steingart