.. currentmodule:: pandas
.. ipython:: python
:suppress:
import os
import csv
from pandas.compat import StringIO, BytesIO
import pandas as pd
ExcelWriter = pd.ExcelWriter
import sys
reload(sys) # Reload does the trick!
sys.setdefaultencoding('UTF8')
import numpy as np
np.random.seed(123456)
randn = np.random.randn
np.set_printoptions(precision=4, suppress=True)
import matplotlib.pyplot as plt
plt.close('all')
import pandas.util.testing as tm
pd.options.display.max_rows=15
clipdf = pd.DataFrame({'A':[1,2,3],'B':[4,5,6],'C':['p','q','r']},
index=['x','y','z'])
.. _io.hdf5:
HDF5 (PyTables)
---------------
``HDFStore`` is a dict-like object which reads and writes pandas using
the high performance HDF5 format using the excellent `PyTables
`__ library. See the :ref:`cookbook `
for some advanced strategies
.. warning::
As of version 0.15.0, pandas requires ``PyTables`` >= 3.0.0. Stores written with prior versions of pandas / ``PyTables`` >= 2.3 are fully compatible (this was the previous minimum ``PyTables`` required version).
.. warning::
There is a ``PyTables`` indexing bug which may appear when querying stores using an index. If you see a subset of results being returned, upgrade to ``PyTables`` >= 3.2. Stores created previously will need to be rewritten using the updated version.
.. warning::
As of version 0.17.0, ``HDFStore`` will not drop rows that have all missing values by default. Previously, if all values (except the index) were missing, ``HDFStore`` would not write those rows to disk.
.. ipython:: python
:suppress:
:okexcept:
os.remove('store.h5')
.. ipython:: python
store = pd.HDFStore('store.h5')
print(store)
Objects can be written to the file just like adding key-value pairs to a
dict:
.. ipython:: python
np.random.seed(1234)
index = pd.date_range('1/1/2000', periods=8)
s = pd.Series(randn(5), index=['a', 'b', 'c', 'd', 'e'])
df = pd.DataFrame(randn(8, 3), index=index,
columns=['A', 'B', 'C'])
wp = pd.Panel(randn(2, 5, 4), items=['Item1', 'Item2'],
major_axis=pd.date_range('1/1/2000', periods=5),
minor_axis=['A', 'B', 'C', 'D'])
# store.put('s', s) is an equivalent method
store['s'] = s
store['df'] = df
store['wp'] = wp
# the type of stored data
store.root.wp._v_attrs.pandas_type
store
In a current or later Python session, you can retrieve stored objects:
.. ipython:: python
# store.get('df') is an equivalent method
store['df']
# dotted (attribute) access provides get as well
store.df
Deletion of the object specified by the key
.. ipython:: python
# store.remove('wp') is an equivalent method
del store['wp']
store
Closing a Store, Context Manager
.. ipython:: python
store.close()
store
store.is_open
# Working with, and automatically closing the store with the context
# manager
with pd.HDFStore('store.h5') as store:
store.keys()
.. ipython:: python
:suppress:
store.close()
import os
os.remove('store.h5')
Read/Write API
''''''''''''''
``HDFStore`` supports an top-level API using ``read_hdf`` for reading and ``to_hdf`` for writing,
similar to how ``read_csv`` and ``to_csv`` work. (new in 0.11.0)
.. ipython:: python
df_tl = pd.DataFrame(dict(A=list(range(5)), B=list(range(5))))
df_tl.to_hdf('store_tl.h5','table',append=True)
pd.read_hdf('store_tl.h5', 'table', where = ['index>2'])
.. ipython:: python
:suppress:
:okexcept:
os.remove('store_tl.h5')
As of version 0.17.0, HDFStore will no longer drop rows that are all missing by default. This behavior can be enabled by setting ``dropna=True``.
.. ipython:: python
:suppress:
import os
.. ipython:: python
df_with_missing = pd.DataFrame({'col1':[0, np.nan, 2],
'col2':[1, np.nan, np.nan]})
df_with_missing
df_with_missing.to_hdf('file.h5', 'df_with_missing',
format = 'table', mode='w')
pd.read_hdf('file.h5', 'df_with_missing')
df_with_missing.to_hdf('file.h5', 'df_with_missing',
format = 'table', mode='w', dropna=True)
pd.read_hdf('file.h5', 'df_with_missing')
.. ipython:: python
:suppress:
os.remove('file.h5')
This is also true for the major axis of a ``Panel``:
.. ipython:: python
matrix = [[[np.nan, np.nan, np.nan],[1,np.nan,np.nan]],
[[np.nan, np.nan, np.nan], [np.nan,5,6]],
[[np.nan, np.nan, np.nan],[np.nan,3,np.nan]]]
panel_with_major_axis_all_missing = pd.Panel(matrix,
items=['Item1', 'Item2','Item3'],
major_axis=[1,2],
minor_axis=['A', 'B', 'C'])
panel_with_major_axis_all_missing
panel_with_major_axis_all_missing.to_hdf('file.h5', 'panel',
dropna = True,
format='table',
mode='w')
reloaded = pd.read_hdf('file.h5', 'panel')
reloaded
.. ipython:: python
:suppress:
os.remove('file.h5')
.. _io.hdf5-fixed:
Fixed Format
''''''''''''
.. note::
This was prior to 0.13.0 the ``Storer`` format.
The examples above show storing using ``put``, which write the HDF5 to ``PyTables`` in a fixed array format, called
the ``fixed`` format. These types of stores are are **not** appendable once written (though you can simply
remove them and rewrite). Nor are they **queryable**; they must be
retrieved in their entirety. They also do not support dataframes with non-unique column names.
The ``fixed`` format stores offer very fast writing and slightly faster reading than ``table`` stores.
This format is specified by default when using ``put`` or ``to_hdf`` or by ``format='fixed'`` or ``format='f'``
.. warning::
A ``fixed`` format will raise a ``TypeError`` if you try to retrieve using a ``where`` .
.. code-block:: python
pd.DataFrame(randn(10,2)).to_hdf('test_fixed.h5','df')
pd.read_hdf('test_fixed.h5','df',where='index>5')
TypeError: cannot pass a where specification when reading a fixed format.
this store must be selected in its entirety
.. _io.hdf5-table:
Table Format
''''''''''''
``HDFStore`` supports another ``PyTables`` format on disk, the ``table``
format. Conceptually a ``table`` is shaped very much like a DataFrame,
with rows and columns. A ``table`` may be appended to in the same or
other sessions. In addition, delete & query type operations are
supported. This format is specified by ``format='table'`` or ``format='t'``
to ``append`` or ``put`` or ``to_hdf``
.. versionadded:: 0.13
This format can be set as an option as well ``pd.set_option('io.hdf.default_format','table')`` to
enable ``put/append/to_hdf`` to by default store in the ``table`` format.
.. ipython:: python
:suppress:
:okexcept:
os.remove('store.h5')
.. ipython:: python
store = pd.HDFStore('store.h5')
df1 = df[0:4]
df2 = df[4:]
# append data (creates a table automatically)
store.append('df', df1)
store.append('df', df2)
store
# select the entire object
store.select('df')
# the type of stored data
store.root.df._v_attrs.pandas_type
.. note::
You can also create a ``table`` by passing ``format='table'`` or ``format='t'`` to a ``put`` operation.
.. _io.hdf5-keys:
Hierarchical Keys
'''''''''''''''''
Keys to a store can be specified as a string. These can be in a
hierarchical path-name like format (e.g. ``foo/bar/bah``), which will
generate a hierarchy of sub-stores (or ``Groups`` in PyTables
parlance). Keys can be specified with out the leading '/' and are ALWAYS
absolute (e.g. 'foo' refers to '/foo'). Removal operations can remove
everything in the sub-store and BELOW, so be *careful*.
.. ipython:: python
store.put('foo/bar/bah', df)
store.append('food/orange', df)
store.append('food/apple', df)
store
# a list of keys are returned
store.keys()
# remove all nodes under this level
store.remove('food')
store
.. warning::
Hierarchical keys cannot be retrieved as dotted (attribute) access as described above for items stored under the root node.
.. code-block:: python
In [8]: store.foo.bar.bah
AttributeError: 'HDFStore' object has no attribute 'foo'
# you can directly access the actual PyTables node but using the root node
In [9]: store.root.foo.bar.bah
Out[9]:
/foo/bar/bah (Group) ''
children := ['block0_items' (Array), 'block0_values' (Array), 'axis0' (Array), 'axis1' (Array)]
Instead, use explicit string based keys
.. ipython:: python
store['foo/bar/bah']
.. _io.hdf5-types:
Storing Types
'''''''''''''
Storing Mixed Types in a Table
++++++++++++++++++++++++++++++
Storing mixed-dtype data is supported. Strings are stored as a
fixed-width using the maximum size of the appended column. Subsequent attempts
at appending longer strings will raise a ``ValueError``.
Passing ``min_itemsize={`values`: size}`` as a parameter to append
will set a larger minimum for the string columns. Storing ``floats,
strings, ints, bools, datetime64`` are currently supported. For string
columns, passing ``nan_rep = 'nan'`` to append will change the default
nan representation on disk (which converts to/from `np.nan`), this
defaults to `nan`.
.. ipython:: python
df_mixed = pd.DataFrame({ 'A' : randn(8),
'B' : randn(8),
'C' : np.array(randn(8),dtype='float32'),
'string' :'string',
'int' : 1,
'bool' : True,
'datetime64' : pd.Timestamp('20010102')},
index=list(range(8)))
df_mixed.ix[3:5,['A', 'B', 'string', 'datetime64']] = np.nan
store.append('df_mixed', df_mixed, min_itemsize = {'values': 50})
df_mixed1 = store.select('df_mixed')
df_mixed1
df_mixed1.get_dtype_counts()
# we have provided a minimum string column size
store.root.df_mixed.table
Storing Multi-Index DataFrames
++++++++++++++++++++++++++++++
Storing multi-index dataframes as tables is very similar to
storing/selecting from homogeneous index DataFrames.
.. ipython:: python
index = pd.MultiIndex(levels=[['foo', 'bar', 'baz', 'qux'],
['one', 'two', 'three']],
labels=[[0, 0, 0, 1, 1, 2, 2, 3, 3, 3],
[0, 1, 2, 0, 1, 1, 2, 0, 1, 2]],
names=['foo', 'bar'])
df_mi = pd.DataFrame(np.random.randn(10, 3), index=index,
columns=['A', 'B', 'C'])
df_mi
store.append('df_mi',df_mi)
store.select('df_mi')
# the levels are automatically included as data columns
store.select('df_mi', 'foo=bar')
.. _io.hdf5-query:
Querying
''''''''
Querying a Table
++++++++++++++++
.. warning::
This query capabilities have changed substantially starting in ``0.13.0``.
Queries from prior version are accepted (with a ``DeprecationWarning``) printed
if its not string-like.
``select`` and ``delete`` operations have an optional criterion that can
be specified to select/delete only a subset of the data. This allows one
to have a very large on-disk table and retrieve only a portion of the
data.
A query is specified using the ``Term`` class under the hood, as a boolean expression.
- ``index`` and ``columns`` are supported indexers of a DataFrame
- ``major_axis``, ``minor_axis``, and ``items`` are supported indexers of
the Panel
- if ``data_columns`` are specified, these can be used as additional indexers
Valid comparison operators are:
``=, ==, !=, >, >=, <, <=``
Valid boolean expressions are combined with:
- ``|`` : or
- ``&`` : and
- ``(`` and ``)`` : for grouping
These rules are similar to how boolean expressions are used in pandas for indexing.
.. note::
- ``=`` will be automatically expanded to the comparison operator ``==``
- ``~`` is the not operator, but can only be used in very limited
circumstances
- If a list/tuple of expressions is passed they will be combined via ``&``
The following are valid expressions:
- ``'index>=date'``
- ``"columns=['A', 'D']"``
- ``"columns in ['A', 'D']"``
- ``'columns=A'``
- ``'columns==A'``
- ``"~(columns=['A','B'])"``
- ``'index>df.index[3] & string="bar"'``
- ``'(index>df.index[3] & index<=df.index[6]) | string="bar"'``
- ``"ts>=Timestamp('2012-02-01')"``
- ``"major_axis>=20130101"``
The ``indexers`` are on the left-hand side of the sub-expression:
``columns``, ``major_axis``, ``ts``
The right-hand side of the sub-expression (after a comparison operator) can be:
- functions that will be evaluated, e.g. ``Timestamp('2012-02-01')``
- strings, e.g. ``"bar"``
- date-like, e.g. ``20130101``, or ``"20130101"``
- lists, e.g. ``"['A','B']"``
- variables that are defined in the local names space, e.g. ``date``
.. note::
Passing a string to a query by interpolating it into the query
expression is not recommended. Simply assign the string of interest to a
variable and use that variable in an expression. For example, do this
.. code-block:: python
string = "HolyMoly'"
store.select('df', 'index == string')
instead of this
.. code-block:: python
string = "HolyMoly'"
store.select('df', 'index == %s' % string)
The latter will **not** work and will raise a ``SyntaxError``.Note that
there's a single quote followed by a double quote in the ``string``
variable.
If you *must* interpolate, use the ``'%r'`` format specifier
.. code-block:: python
store.select('df', 'index == %r' % string)
which will quote ``string``.
Here are some examples:
.. ipython:: python
dfq = pd.DataFrame(randn(10,4),columns=list('ABCD'),index=pd.date_range('20130101',periods=10))
store.append('dfq',dfq,format='table',data_columns=True)
Use boolean expressions, with in-line function evaluation.
.. ipython:: python
store.select('dfq',"index>pd.Timestamp('20130104') & columns=['A', 'B']")
Use and inline column reference
.. ipython:: python
store.select('dfq',where="A>0 or C>0")
Works with a Panel as well.
.. ipython:: python
store.append('wp',wp)
store
store.select('wp', "major_axis>pd.Timestamp('20000102') & minor_axis=['A', 'B']")
The ``columns`` keyword can be supplied to select a list of columns to be
returned, this is equivalent to passing a
``'columns=list_of_columns_to_filter'``:
.. ipython:: python
store.select('df', "columns=['A', 'B']")
``start`` and ``stop`` parameters can be specified to limit the total search
space. These are in terms of the total number of rows in a table.
.. ipython:: python
# this is effectively what the storage of a Panel looks like
wp.to_frame()
# limiting the search
store.select('wp',"major_axis>20000102 & minor_axis=['A','B']",
start=0, stop=10)
.. note::
``select`` will raise a ``ValueError`` if the query expression has an unknown
variable reference. Usually this means that you are trying to select on a column
that is **not** a data_column.
``select`` will raise a ``SyntaxError`` if the query expression is not valid.
.. _io.hdf5-timedelta:
Using timedelta64[ns]
+++++++++++++++++++++
.. versionadded:: 0.13
Beginning in 0.13.0, you can store and query using the ``timedelta64[ns]`` type. Terms can be
specified in the format: ``()``, where float may be signed (and fractional), and unit can be
``D,s,ms,us,ns`` for the timedelta. Here's an example:
.. ipython:: python
from datetime import timedelta
dftd = pd.DataFrame(dict(A = pd.Timestamp('20130101'), B = [ pd.Timestamp('20130101') + timedelta(days=i,seconds=10) for i in range(10) ]))
dftd['C'] = dftd['A']-dftd['B']
dftd
store.append('dftd',dftd,data_columns=True)
store.select('dftd',"C<'-3.5D'")
Indexing
++++++++
You can create/modify an index for a table with ``create_table_index``
after data is already in the table (after and ``append/put``
operation). Creating a table index is **highly** encouraged. This will
speed your queries a great deal when you use a ``select`` with the
indexed dimension as the ``where``.
.. note::
Indexes are automagically created (starting ``0.10.1``) on the indexables
and any data columns you specify. This behavior can be turned off by passing
``index=False`` to ``append``.
.. ipython:: python
# we have automagically already created an index (in the first section)
i = store.root.df.table.cols.index.index
i.optlevel, i.kind
# change an index by passing new parameters
store.create_table_index('df', optlevel=9, kind='full')
i = store.root.df.table.cols.index.index
i.optlevel, i.kind
Oftentimes when appending large amounts of data to a store, it is useful to turn off index creation for each append, then recreate at the end.
.. ipython:: python
df_1 = pd.DataFrame(randn(10,2),columns=list('AB'))
df_2 = pd.DataFrame(randn(10,2),columns=list('AB'))
st = pd.HDFStore('appends.h5',mode='w')
st.append('df', df_1, data_columns=['B'], index=False)
st.append('df', df_2, data_columns=['B'], index=False)
st.get_storer('df').table
Then create the index when finished appending.
.. ipython:: python
st.create_table_index('df', columns=['B'], optlevel=9, kind='full')
st.get_storer('df').table
st.close()
.. ipython:: python
:suppress:
:okexcept:
os.remove('appends.h5')
See `here `__ for how to create a completely-sorted-index (CSI) on an existing store.
Query via Data Columns
++++++++++++++++++++++
You can designate (and index) certain columns that you want to be able
to perform queries (other than the `indexable` columns, which you can
always query). For instance say you want to perform this common
operation, on-disk, and return just the frame that matches this
query. You can specify ``data_columns = True`` to force all columns to
be data_columns
.. ipython:: python
df_dc = df.copy()
df_dc['string'] = 'foo'
df_dc.ix[4:6,'string'] = np.nan
df_dc.ix[7:9,'string'] = 'bar'
df_dc['string2'] = 'cool'
df_dc.ix[1:3,['B','C']] = 1.0
df_dc
# on-disk operations
store.append('df_dc', df_dc, data_columns = ['B', 'C', 'string', 'string2'])
store.select('df_dc', [ pd.Term('B>0') ])
# getting creative
store.select('df_dc', 'B > 0 & C > 0 & string == foo')
# this is in-memory version of this type of selection
df_dc[(df_dc.B > 0) & (df_dc.C > 0) & (df_dc.string == 'foo')]
# we have automagically created this index and the B/C/string/string2
# columns are stored separately as ``PyTables`` columns
store.root.df_dc.table
There is some performance degradation by making lots of columns into
`data columns`, so it is up to the user to designate these. In addition,
you cannot change data columns (nor indexables) after the first
append/put operation (Of course you can simply read in the data and
create a new table!)
Iterator
++++++++
Starting in ``0.11.0``, you can pass, ``iterator=True`` or ``chunksize=number_in_a_chunk``
to ``select`` and ``select_as_multiple`` to return an iterator on the results.
The default is 50,000 rows returned in a chunk.
.. ipython:: python
for df in store.select('df', chunksize=3):
print(df)
.. note::
.. versionadded:: 0.12.0
You can also use the iterator with ``read_hdf`` which will open, then
automatically close the store when finished iterating.
.. code-block:: python
for df in pd.read_hdf('store.h5','df', chunksize=3):
print(df)
Note, that the chunksize keyword applies to the **source** rows. So if you
are doing a query, then the chunksize will subdivide the total rows in the table
and the query applied, returning an iterator on potentially unequal sized chunks.
Here is a recipe for generating a query and using it to create equal sized return
chunks.
.. ipython:: python
dfeq = pd.DataFrame({'number': np.arange(1,11)})
dfeq
store.append('dfeq', dfeq, data_columns=['number'])
def chunks(l, n):
return [l[i:i+n] for i in range(0, len(l), n)]
evens = [2,4,6,8,10]
coordinates = store.select_as_coordinates('dfeq','number=evens')
for c in chunks(coordinates, 2):
print store.select('dfeq',where=c)
Advanced Queries
++++++++++++++++
Select a Single Column
^^^^^^^^^^^^^^^^^^^^^^
To retrieve a single indexable or data column, use the
method ``select_column``. This will, for example, enable you to get the index
very quickly. These return a ``Series`` of the result, indexed by the row number.
These do not currently accept the ``where`` selector.
.. ipython:: python
store.select_column('df_dc', 'index')
store.select_column('df_dc', 'string')
.. _io.hdf5-selecting_coordinates:
Selecting coordinates
^^^^^^^^^^^^^^^^^^^^^
Sometimes you want to get the coordinates (a.k.a the index locations) of your query. This returns an
``Int64Index`` of the resulting locations. These coordinates can also be passed to subsequent
``where`` operations.
.. ipython:: python
df_coord = pd.DataFrame(np.random.randn(1000,2),index=pd.date_range('20000101',periods=1000))
store.append('df_coord',df_coord)
c = store.select_as_coordinates('df_coord','index>20020101')
c.summary()
store.select('df_coord',where=c)
.. _io.hdf5-where_mask:
Selecting using a where mask
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Sometime your query can involve creating a list of rows to select. Usually this ``mask`` would
be a resulting ``index`` from an indexing operation. This example selects the months of
a datetimeindex which are 5.
.. ipython:: python
df_mask = pd.DataFrame(np.random.randn(1000,2),index=pd.date_range('20000101',periods=1000))
store.append('df_mask',df_mask)
c = store.select_column('df_mask','index')
where = c[pd.DatetimeIndex(c).month==5].index
store.select('df_mask',where=where)
Storer Object
^^^^^^^^^^^^^
If you want to inspect the stored object, retrieve via
``get_storer``. You could use this programmatically to say get the number
of rows in an object.
.. ipython:: python
store.get_storer('df_dc').nrows
Multiple Table Queries
++++++++++++++++++++++
New in 0.10.1 are the methods ``append_to_multiple`` and
``select_as_multiple``, that can perform appending/selecting from
multiple tables at once. The idea is to have one table (call it the
selector table) that you index most/all of the columns, and perform your
queries. The other table(s) are data tables with an index matching the
selector table's index. You can then perform a very fast query
on the selector table, yet get lots of data back. This method is similar to
having a very wide table, but enables more efficient queries.
The ``append_to_multiple`` method splits a given single DataFrame
into multiple tables according to ``d``, a dictionary that maps the
table names to a list of 'columns' you want in that table. If `None`
is used in place of a list, that table will have the remaining
unspecified columns of the given DataFrame. The argument ``selector``
defines which table is the selector table (which you can make queries from).
The argument ``dropna`` will drop rows from the input DataFrame to ensure
tables are synchronized. This means that if a row for one of the tables
being written to is entirely ``np.NaN``, that row will be dropped from all tables.
If ``dropna`` is False, **THE USER IS RESPONSIBLE FOR SYNCHRONIZING THE TABLES**.
Remember that entirely ``np.Nan`` rows are not written to the HDFStore, so if
you choose to call ``dropna=False``, some tables may have more rows than others,
and therefore ``select_as_multiple`` may not work or it may return unexpected
results.
.. ipython:: python
df_mt = pd.DataFrame(randn(8, 6), index=pd.date_range('1/1/2000', periods=8),
columns=['A', 'B', 'C', 'D', 'E', 'F'])
df_mt['foo'] = 'bar'
df_mt.ix[1, ('A', 'B')] = np.nan
# you can also create the tables individually
store.append_to_multiple({'df1_mt': ['A', 'B'], 'df2_mt': None },
df_mt, selector='df1_mt')
store
# individual tables were created
store.select('df1_mt')
store.select('df2_mt')
# as a multiple
store.select_as_multiple(['df1_mt', 'df2_mt'], where=['A>0', 'B>0'],
selector = 'df1_mt')
Delete from a Table
'''''''''''''''''''
You can delete from a table selectively by specifying a ``where``. In
deleting rows, it is important to understand the ``PyTables`` deletes
rows by erasing the rows, then **moving** the following data. Thus
deleting can potentially be a very expensive operation depending on the
orientation of your data. This is especially true in higher dimensional
objects (``Panel`` and ``Panel4D``). To get optimal performance, it's
worthwhile to have the dimension you are deleting be the first of the
``indexables``.
Data is ordered (on the disk) in terms of the ``indexables``. Here's a
simple use case. You store panel-type data, with dates in the
``major_axis`` and ids in the ``minor_axis``. The data is then
interleaved like this:
- date_1
- id_1
- id_2
- .
- id_n
- date_2
- id_1
- .
- id_n
It should be clear that a delete operation on the ``major_axis`` will be
fairly quick, as one chunk is removed, then the following data moved. On
the other hand a delete operation on the ``minor_axis`` will be very
expensive. In this case it would almost certainly be faster to rewrite
the table using a ``where`` that selects all but the missing data.
.. ipython:: python
# returns the number of rows deleted
store.remove('wp', 'major_axis>20000102' )
store.select('wp')
.. warning::
Please note that HDF5 **DOES NOT RECLAIM SPACE** in the h5 files
automatically. Thus, repeatedly deleting (or removing nodes) and adding
again, **WILL TEND TO INCREASE THE FILE SIZE**.
To *repack and clean* the file, use :ref:`ptrepack `
.. _io.hdf5-notes:
Notes & Caveats
'''''''''''''''
Compression
+++++++++++
``PyTables`` allows the stored data to be compressed. This applies to
all kinds of stores, not just tables.
- Pass ``complevel=int`` for a compression level (1-9, with 0 being no
compression, and the default)
- Pass ``complib=lib`` where lib is any of ``zlib, bzip2, lzo, blosc`` for
whichever compression library you prefer.
``HDFStore`` will use the file based compression scheme if no overriding
``complib`` or ``complevel`` options are provided. ``blosc`` offers very
fast compression, and is my most used. Note that ``lzo`` and ``bzip2``
may not be installed (by Python) by default.
Compression for all objects within the file
.. code-block:: python
store_compressed = pd.HDFStore('store_compressed.h5', complevel=9, complib='blosc')
Or on-the-fly compression (this only applies to tables). You can turn
off file compression for a specific table by passing ``complevel=0``
.. code-block:: python
store.append('df', df, complib='zlib', complevel=5)
.. _io.hdf5-ptrepack:
ptrepack
++++++++
``PyTables`` offers better write performance when tables are compressed after
they are written, as opposed to turning on compression at the very
beginning. You can use the supplied ``PyTables`` utility
``ptrepack``. In addition, ``ptrepack`` can change compression levels
after the fact.
.. code-block:: console
ptrepack --chunkshape=auto --propindexes --complevel=9 --complib=blosc in.h5 out.h5
Furthermore ``ptrepack in.h5 out.h5`` will *repack* the file to allow
you to reuse previously deleted space. Alternatively, one can simply
remove the file and write again, or use the ``copy`` method.
.. _io.hdf5-caveats:
Caveats
+++++++
.. warning::
``HDFStore`` is **not-threadsafe for writing**. The underlying
``PyTables`` only supports concurrent reads (via threading or
processes). If you need reading and writing *at the same time*, you
need to serialize these operations in a single thread in a single
process. You will corrupt your data otherwise. See the (:issue:`2397`) for more information.
- If you use locks to manage write access between multiple processes, you
may want to use :py:func:`~os.fsync` before releasing write locks. For
convenience you can use ``store.flush(fsync=True)`` to do this for you.
- Once a ``table`` is created its items (Panel) / columns (DataFrame)
are fixed; only exactly the same columns can be appended
- Be aware that timezones (e.g., ``pytz.timezone('US/Eastern')``)
are not necessarily equal across timezone versions. So if data is
localized to a specific timezone in the HDFStore using one version
of a timezone library and that data is updated with another version, the data
will be converted to UTC since these timezones are not considered
equal. Either use the same version of timezone library or use ``tz_convert`` with
the updated timezone definition.
.. warning::
``PyTables`` will show a ``NaturalNameWarning`` if a column name
cannot be used as an attribute selector.
*Natural* identifiers contain only letters, numbers, and underscores,
and may not begin with a number.
Other identifiers cannot be used in a ``where`` clause
and are generally a bad idea.
.. _io.hdf5-data_types:
DataTypes
'''''''''
``HDFStore`` will map an object dtype to the ``PyTables`` underlying
dtype. This means the following types are known to work:
====================================================== =========================
Type Represents missing values
====================================================== =========================
floating : ``float64, float32, float16`` ``np.nan``
integer : ``int64, int32, int8, uint64,uint32, uint8``
boolean
``datetime64[ns]`` ``NaT``
``timedelta64[ns]`` ``NaT``
categorical : see the section below
object : ``strings`` ``np.nan``
====================================================== =========================
``unicode`` columns are not supported, and **WILL FAIL**.
.. _io.hdf5-categorical:
Categorical Data
++++++++++++++++
.. versionadded:: 0.15.2
Writing data to a ``HDFStore`` that contains a ``category`` dtype was implemented
in 0.15.2. Queries work the same as if it was an object array. However, the ``category`` dtyped data is
stored in a more efficient manner.
.. ipython:: python
dfcat = pd.DataFrame({ 'A' : pd.Series(list('aabbcdba')).astype('category'),
'B' : np.random.randn(8) })
dfcat
dfcat.dtypes
cstore = pd.HDFStore('cats.h5', mode='w')
cstore.append('dfcat', dfcat, format='table', data_columns=['A'])
result = cstore.select('dfcat', where="A in ['b','c']")
result
result.dtypes
.. warning::
The format of the ``Categorical`` is readable by prior versions of pandas (< 0.15.2), but will retrieve
the data as an integer based column (e.g. the ``codes``). However, the ``categories`` *can* be retrieved
but require the user to select them manually using the explicit meta path.
The data is stored like so:
.. ipython:: python
cstore
# to get the categories
cstore.select('dfcat/meta/A/meta')
.. ipython:: python
:suppress:
:okexcept:
cstore.close()
import os
os.remove('cats.h5')
String Columns
++++++++++++++
**min_itemsize**
The underlying implementation of ``HDFStore`` uses a fixed column width (itemsize) for string columns.
A string column itemsize is calculated as the maximum of the
length of data (for that column) that is passed to the ``HDFStore``, **in the first append**. Subsequent appends,
may introduce a string for a column **larger** than the column can hold, an Exception will be raised (otherwise you
could have a silent truncation of these columns, leading to loss of information). In the future we may relax this and
allow a user-specified truncation to occur.
Pass ``min_itemsize`` on the first table creation to a-priori specify the minimum length of a particular string column.
``min_itemsize`` can be an integer, or a dict mapping a column name to an integer. You can pass ``values`` as a key to
allow all *indexables* or *data_columns* to have this min_itemsize.
Starting in 0.11.0, passing a ``min_itemsize`` dict will cause all passed columns to be created as *data_columns* automatically.
.. note::
If you are not passing any ``data_columns``, then the ``min_itemsize`` will be the maximum of the length of any string passed
.. ipython:: python
dfs = pd.DataFrame(dict(A = 'foo', B = 'bar'),index=list(range(5)))
dfs
# A and B have a size of 30
store.append('dfs', dfs, min_itemsize = 30)
store.get_storer('dfs').table
# A is created as a data_column with a size of 30
# B is size is calculated
store.append('dfs2', dfs, min_itemsize = { 'A' : 30 })
store.get_storer('dfs2').table
**nan_rep**
String columns will serialize a ``np.nan`` (a missing value) with the ``nan_rep`` string representation. This defaults to the string value ``nan``.
You could inadvertently turn an actual ``nan`` value into a missing value.
.. ipython:: python
dfss = pd.DataFrame(dict(A = ['foo','bar','nan']))
dfss
store.append('dfss', dfss)
store.select('dfss')
# here you need to specify a different nan rep
store.append('dfss2', dfss, nan_rep='_nan_')
store.select('dfss2')
.. _io.external_compatibility:
External Compatibility
''''''''''''''''''''''
``HDFStore`` writes ``table`` format objects in specific formats suitable for
producing loss-less round trips to pandas objects. For external
compatibility, ``HDFStore`` can read native ``PyTables`` format
tables.
It is possible to write an ``HDFStore`` object that can easily be imported into ``R`` using the
``rhdf5`` library (`Package website`_). Create a table format store like this:
.. _package website: http://www.bioconductor.org/packages/release/bioc/html/rhdf5.html
.. ipython:: python
np.random.seed(1)
df_for_r = pd.DataFrame({"first": np.random.rand(100),
"second": np.random.rand(100),
"class": np.random.randint(0, 2, (100,))},
index=range(100))
df_for_r.head()
store_export = pd.HDFStore('export.h5')
store_export.append('df_for_r', df_for_r, data_columns=df_dc.columns)
store_export
.. ipython:: python
:suppress:
store_export.close()
import os
os.remove('export.h5')
In R this file can be read into a ``data.frame`` object using the ``rhdf5``
library. The following example function reads the corresponding column names
and data values from the values and assembles them into a ``data.frame``:
.. code-block:: R
# Load values and column names for all datasets from corresponding nodes and
# insert them into one data.frame object.
library(rhdf5)
loadhdf5data <- function(h5File) {
listing <- h5ls(h5File)
# Find all data nodes, values are stored in *_values and corresponding column
# titles in *_items
data_nodes <- grep("_values", listing$name)
name_nodes <- grep("_items", listing$name)
data_paths = paste(listing$group[data_nodes], listing$name[data_nodes], sep = "/")
name_paths = paste(listing$group[name_nodes], listing$name[name_nodes], sep = "/")
columns = list()
for (idx in seq(data_paths)) {
# NOTE: matrices returned by h5read have to be transposed to to obtain
# required Fortran order!
data <- data.frame(t(h5read(h5File, data_paths[idx])))
names <- t(h5read(h5File, name_paths[idx]))
entry <- data.frame(data)
colnames(entry) <- names
columns <- append(columns, entry)
}
data <- data.frame(columns)
return(data)
}
Now you can import the ``DataFrame`` into R:
.. code-block:: R
> data = loadhdf5data("transfer.hdf5")
> head(data)
first second class
1 0.4170220047 0.3266449 0
2 0.7203244934 0.5270581 0
3 0.0001143748 0.8859421 1
4 0.3023325726 0.3572698 1
5 0.1467558908 0.9085352 1
6 0.0923385948 0.6233601 1
.. note::
The R function lists the entire HDF5 file's contents and assembles the
``data.frame`` object from all matching nodes, so use this only as a
starting point if you have stored multiple ``DataFrame`` objects to a
single HDF5 file.
Backwards Compatibility
'''''''''''''''''''''''
0.10.1 of ``HDFStore`` can read tables created in a prior version of pandas,
however query terms using the
prior (undocumented) methodology are unsupported. ``HDFStore`` will
issue a warning if you try to use a legacy-format file. You must
read in the entire file and write it out using the new format, using the
method ``copy`` to take advantage of the updates. The group attribute
``pandas_version`` contains the version information. ``copy`` takes a
number of options, please see the docstring.
.. ipython:: python
import os
legacy_file_path = os.path.abspath('source/_static/legacy_0.10.h5')
.. ipython:: python
:okwarning:
# a legacy store
legacy_store = pd.HDFStore(legacy_file_path,'r')
legacy_store
# copy (and return the new handle)
new_store = legacy_store.copy('store_new.h5')
new_store
new_store.close()
.. ipython:: python
:suppress:
legacy_store.close()
import os
os.remove('store_new.h5')
Performance
'''''''''''
- ``tables`` format come with a writing performance penalty as compared to
``fixed`` stores. The benefit is the ability to append/delete and
query (potentially very large amounts of data). Write times are
generally longer as compared with regular stores. Query times can
be quite fast, especially on an indexed axis.
- You can pass ``chunksize=`` to ``append``, specifying the
write chunksize (default is 50000). This will significantly lower
your memory usage on writing.
- You can pass ``expectedrows=`` to the first ``append``,
to set the TOTAL number of expected rows that ``PyTables`` will
expected. This will optimize read/write performance.
- Duplicate rows can be written to tables, but are filtered out in
selection (with the last items being selected; thus a table is
unique on major, minor pairs)
- A ``PerformanceWarning`` will be raised if you are attempting to
store types that will be pickled by PyTables (rather than stored as
endemic types). See
`Here `__
for more information and some solutions.
Experimental
''''''''''''
HDFStore supports ``Panel4D`` storage.
.. ipython:: python
:okwarning:
p4d = pd.Panel4D({ 'l1' : wp })
p4d
store.append('p4d', p4d)
store
These, by default, index the three axes ``items, major_axis,
minor_axis``. On an ``AppendableTable`` it is possible to setup with the
first append a different indexing scheme, depending on how you want to
store your data. Pass the ``axes`` keyword with a list of dimensions
(currently must by exactly 1 less than the total dimensions of the
object). This cannot be changed after table creation.
.. ipython:: python
:okwarning:
store.append('p4d2', p4d, axes=['labels', 'major_axis', 'minor_axis'])
store
store.select('p4d2', [ pd.Term('labels=l1'), pd.Term('items=Item1'), pd.Term('minor_axis=A_big_strings') ])
.. ipython:: python
:suppress:
store.close()
import os
os.remove('store.h5')