python - AWS Lambda、Python3 和大型 XLSX 文件到 CSV 文件
问题描述
我有一批大小从 10Mb 到 400Mb 的 XLSX 文件。它们内部始终具有相同的表格和结构,但有些包含的数据比其他的多。
我正在尝试使用 AWS Lambda 处理这些;它是提交过程的一部分,因此 S3 中的文件删除是 Lambda 的事件。
我很快了解到 XLSX 是一种可怕的格式,但我无法改变它。目前,我的主要 Lambda 正在使用我在网上找到并稍作改动的此类工作。内存使用率和速度比 Pandas read_excel 有所提高,但仍然不够。对于 400Mb 的文件,Lambda 只会超时或耗尽其内存分配(即使是最大)。
对脚本进行一些内存分析,我可以看到在枢轴操作期间大小减小,但我不能真正跳过它。
关于如何改进它以提高内存效率的任何提示?
每个工作表都需要保存到自己的 CSV,但如果有帮助,可以将其拆分为多个 CSV 文件,比如可能是块大小样式的迭代器?
import io
import zipfile
from lxml import etree
from pandas import read_csv, to_numeric
class ExcelParse:
sheet_xslt = etree.XML('''
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sp="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
>
<xsl:output method="text"/>
<xsl:template match="sp:row">
<xsl:for-each select="sp:c">
<xsl:value-of select="parent::*/@r"/> <!-- ROW -->
<xsl:text>,</xsl:text>
<xsl:value-of select="@r"/> <!--REMOVEME-->
<xsl:text>,</xsl:text>
<xsl:value-of select="@t"/> <!-- TYPE -->
<xsl:text>,</xsl:text>
<xsl:value-of select="sp:v/text()"/> <!-- VALUE -->
<xsl:text>\n</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
''')
def __init__(self, file):
self.fh = zipfile.ZipFile(file)
self.ns = {
'ns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
}
self.shared = self.load_shared()
self.workbook = self.load_workbook()
def load_workbook(self):
# Load workbook
name = 'xl/workbook.xml'
root = etree.parse(self.fh.open(name))
res = {}
for el in etree.XPath("//ns:sheet", namespaces=self.ns)(root):
res[el.attrib['name']] = str(
int(el.attrib['sheetId']) -
1) # Sheet ID in the XML starts at 2 for some reason?
return res
def load_shared(self):
# Load shared strings
name = 'xl/sharedStrings.xml'
root = etree.parse(self.fh.open(name))
res = etree.XPath("/ns:sst/ns:si/ns:t", namespaces=self.ns)(root)
return {str(pos): el.text for pos, el in enumerate(res)}
def _parse_sheet(self, root):
transform = etree.XSLT(self.sheet_xslt)
result = transform(root)
df = read_csv(io.StringIO(str(result)),
header=None,
names=['row', 'cell', 'type', 'value'])
return df
def read(self, sheet_name):
sheet_id = self.workbook[sheet_name]
sheet_path = f'xl/worksheets/sheet{sheet_id}.xml'
root = etree.parse(self.fh.open(sheet_path))
df = self._parse_sheet(root)
# First row numbers are filled with nan
df['row'] = to_numeric(df['row'].fillna(0))
# Translate string contents
cond = (df.type == 's') & (~df.value.isnull())
df.loc[cond, 'value'] = df[cond]['value'].map(self.shared)
# Add column number and sort rows
df['col'] = df.cell.str.replace(r'[0-9]+', '', regex=True)
# Pivot everything
df = df.pivot(
index='row', columns='col',
values='value').reset_index(drop=True).reset_index(drop=True)
df.columns.name = None # pivot adds a name to the "columns" array
# Sort columns (pivot will put AA before B)
cols = sorted(df.columns, key=lambda x: (len(x), x))
df = df[cols]
df = df.dropna(how='all') # Ignore empty lines
df = df.dropna(how='all', axis=1) # Ignore empty cols
new_header = df.iloc[0] # Grab the first row for the header
df = df[1:] # Take the data less the header row
df.columns = new_header # Set the header row as the df header
return df
def new_method():
xlsx = ExcelParse(
'BigFile.xlsx'
)
print(xlsx.workbook)
df = xlsx.read('Task')
# for sheet_name, sheet_id in xlsx.workbook.items():
# df = xlsx.read(sheet_name)
# do stuff
new_method()
解决方案
为了加快这个过程,你可以尝试这些事情并检查
- 尝试使用xlsx2csv将工作簿中的每个工作表转换为 csv,然后执行 pd.read_csv()。CSV 读取速度比 excel 快。
- 如您所说,数据是固定结构,不会改变尝试在 read_csv 中使用“dtype”选项。这将有助于 pandas 避免自动识别每列的数据类型,我猜这会节省一些时间。
对于内存问题,
我无法理解完全使用 10GB RAM 的想法,但无论如何,至少据我所知,要大幅度改进可能很困难。使用 'dtype' 选项可能会有所帮助,因为某些数据类型比其他数据类型占用更多内存,您可以将所有列设置为 str 并检查。read_csv 也有 chunksize 选项。你可以探索一下。
您还可以在 read_csv 中探索 low_memory 和 memory_map 选项。我还没有尝试过,所以不能真正告诉你有效性。
万一我弄错了,如果你在谈论存储内存,你可以在 read_csv 中使用 storage_options,如果你不这样做,它将直接从 S3 读/写到 S3。
如果您需要更多本地存储内存,请查看EFS for Lambdas。
另一方面,您可以制作一个 Fargate 图像并从您的 lambda 中调用它。有了这个,如果这不是约束,你可以获得很长的执行时间。查看更多。