我有一个大的Pandas数据帧(~15GB,83m行),我有兴趣保存为h5
(或feather
)文件。一列包含长ID的数字字符串,它们应该具有字符串/对象类型。但即使我确保熊猫将所有列解析为对象
:
df = pd.read_csv('data.csv', dtype=object)
print(df.dtypes) # sanity check
df.to_hdf('df.h5', 'df')
> client_id object
event_id object
account_id object
session_id object
event_timestamp object
# etc...
我得到这个错误:
File "foo.py", line 14, in <module>
df.to_hdf('df.h5', 'df')
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/core/generic.py", line 1996, in to_hdf
return pytables.to_hdf(path_or_buf, key, self, **kwargs)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/io/pytables.py", line 279, in to_hdf
f(store)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/io/pytables.py", line 273, in <lambda>
f = lambda store: store.put(key, value, **kwargs)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/io/pytables.py", line 890, in put
self._write_to_group(key, value, append=append, **kwargs)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/io/pytables.py", line 1367, in _write_to_group
s.write(obj=value, append=append, complib=complib, **kwargs)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/io/pytables.py", line 2963, in write
self.write_array('block%d_values' % i, blk.values, items=blk_items)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/io/pytables.py", line 2730, in write_array
vlarr.append(value)
File "/shared_directory/projects/env/lib/python3.6/site-packages/tables/vlarray.py", line 547, in append
self._append(nparr, nobjects)
File "tables/hdf5extension.pyx", line 2032, in tables.hdf5extension.VLArray._append
OverflowError: value too large to convert to int
显然它无论如何都试图将其转换为int,但失败了。
运行df.to_feather()
时,我也遇到了类似的问题:
df.to_feather('df.feather')
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/core/frame.py", line 1892, in to_feather
to_feather(self, fname)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pandas/io/feather_format.py", line 83, in to_feather
feather.write_dataframe(df, path)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pyarrow/feather.py", line 182, in write_feather
writer.write(df)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pyarrow/feather.py", line 93, in write
table = Table.from_pandas(df, preserve_index=False)
File "pyarrow/table.pxi", line 1174, in pyarrow.lib.Table.from_pandas
File "/shared_directory/projects/env/lib/python3.6/site-packages/pyarrow/pandas_compat.py", line 501, in dataframe_to_arrays
convert_fields))
File "/usr/lib/python3.6/concurrent/futures/_base.py", line 586, in result_iterator
yield fs.pop().result()
File "/usr/lib/python3.6/concurrent/futures/_base.py", line 425, in result
return self.__get_result()
File "/usr/lib/python3.6/concurrent/futures/_base.py", line 384, in __get_result
raise self._exception
File "/usr/lib/python3.6/concurrent/futures/thread.py", line 56, in run
result = self.fn(*self.args, **self.kwargs)
File "/shared_directory/projects/env/lib/python3.6/site-packages/pyarrow/pandas_compat.py", line 487, in convert_column
raise e
File "/shared_directory/projects/env/lib/python3.6/site-packages/pyarrow/pandas_compat.py", line 481, in convert_column
result = pa.array(col, type=type_, from_pandas=True, safe=safe)
File "pyarrow/array.pxi", line 191, in pyarrow.lib.array
File "pyarrow/array.pxi", line 78, in pyarrow.lib._ndarray_to_array
File "pyarrow/error.pxi", line 85, in pyarrow.lib.check_status
pyarrow.lib.ArrowInvalid: ('Could not convert 1542852887489 with type str: tried to convert to double', 'Conversion failed for column session_id with type object')
所以:
在对这个主题进行了一些阅读之后,问题似乎是处理string
类型列。我的string
列包含全数字字符串和带字符的字符串的混合。Pandas可以灵活地选择将字符串保留为对象
,没有声明的类型,但是当序列化为hdf5
或feather
时,列的内容被转换为类型(例如str
或double
)并且不能混合。当遇到足够大的混合类型库时,这两个库都会失败。
将我的混合列强制转换为字符串允许我将其保存在feather中,但在HDF5中,文件膨胀并且当我耗尽磁盘空间时该过程结束。
以下是一个类似案例的答案,其中一位评论者指出(2年前)“这个问题非常标准,但解决方案很少”。
Pandas中的字符串类型称为object
,但这掩盖了它们可能是纯字符串或混合dtype(numpy有内置字符串类型,但Pandas从不将它们用于文本)。因此,在这种情况下,首先要做的是将所有字符串强制执行为字符串类型(使用df[coll]. astype(str)
)。但即便如此,在足够大的文件(16GB,带有长字符串)中,这仍然失败了。为什么?
我遇到这个错误的原因是我有很长的高熵(许多不同的唯一值)字符串的数据。(对于低熵数据,切换到分类
dtype可能是值得的。)在我的例子中,我意识到我只需要这些字符串来识别行——所以我可以用唯一的整数替换它们!
df[col] = df[col].map(dict(zip(df[col].unique(), range(df[col].nunique()))))
对于文本数据,除了hdf5
/feather
之外,还有其他推荐的解决方案,包括:
json
msgpack
(注意在Pandas 0.25中read_msgpack
已被弃用)ickle
(已知存在安全问题,请小心-但对于数据帧的内部存储/传输应该可以)parquet
,Apache Arrow生态系统的一部分。这是Matthew Rocklin(dask
开发人员之一)比较msgpack
和泡菜
的答案。他在博客上写了一个更广泛的比较。
HDF5不是适合这个用例的解决方案。如果你想在单个结构中存储许多数据帧,hdf5是一个更好的解决方案。打开文件时它有更多的开销,然后它允许你有效地加载每个数据帧,也可以轻松地加载它们的切片。它应该被认为是一个存储数据帧的文件系统。
在时间序列事件的单个数据帧的情况下,推荐的格式将是Apache Arrow项目格式之一,即feather
或parquet
。人们应该将它们视为基于列的(压缩的)csv文件。这两者之间的特殊权衡在什么是羽毛和镶木地板之间的区别?下进行了很好的阐述。
需要考虑的一个特殊问题是数据类型。由于feather
不是为通过压缩来优化磁盘空间而设计的,因此它可以为更多种数据类型提供支持。虽然parquet
试图提供非常有效的压缩,但它只能支持有限的子集,从而可以更好地处理数据压缩。