Pandas HDFStore with Multiindex Selecting by Index Value not finding column

Question

I have a large Pandas Dataframe stored in a HDF5 file. The Dataframe is multi-index with Datetime and PatientID.

store = pd.HDFStore('prepro_mon.h5',mode='r')
print(store.get_storer('raw_import').levels)

Out: ['Datetime', 'PatientID']

But filtering a Select statement by Patient ID doesn't work.

store.select('raw_import', where='PatientID = 88000') 

I get this error

Traceback (most recent call last):
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 4660, in generate
    return Expr(where, queryables=q, encoding=self.table.encoding)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\pytables.py", line 524, in __init__
    self.terms = self.parse()
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 742, in parse
    return self._visitor.visit(self.expr)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 318, in visit_Module
    return self.visit(expr, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 321, in visit_Expr
    return self.visit(node.value, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 643, in visit_Compare
    return self.visit(binop)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 415, in visit_BinOp
    op, op_class, left, right = self._possibly_transform_eq_ne(node)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 353, in _possibly_transform_eq_ne
    left = self.visit(node.left, side='left')
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\expr.py", line 429, in visit_Name
    return self.term_type(node.id, self.env, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\pytables.py", line 41, in __init__
    super(Term, self).__init__(name, env, side=side, encoding=encoding)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\ops.py", line 58, in __init__
    self._value = self._resolve_name()
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\computation\pytables.py", line 47, in _resolve_name
    raise NameError('name {0!r} is not defined'.format(self.name))
NameError: name 'PatientID' is not defined

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "H:\OxygenationIndex\dataloaderOIFeatures.py", line 51, in <module>
    store.select('raw_import', where='PatientID = 88000')
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 724, in select
    return it.get_result()
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 1423, in get_result
    results = self.func(self.start, self.stop, where)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 717, in func
    columns=columns, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 4266, in read
    df = super(AppendableMultiFrameTable, self).read(**kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 4086, in read
    if not self.read_axes(where=where, **kwargs):
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 3287, in read_axes
    self.selection = Selection(self, where=where, **kwargs)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 4647, in __init__
    self.terms = self.generate(where)
  File "C:\Program Files\Anaconda3\lib\site-packages\pandas\io\pytables.py", line 4672, in generate
    .format(where, ','.join(q.keys()))
ValueError: The passed where expression: PatientID = 88000
            contains an invalid variable reference
            all of the variable refrences must be a reference to
            an axis (e.g. 'index' or 'columns'), or a data_column
            The currently defined references are: SvO2,AWPMean,peep_s,SpO2,fiO2_m,TempCentral,tv_m,fiO2_s,HF,RR_s,TempPeri,etCO2,tv_s,peep_m,peakP_m,RR_m,columns,StayMinutes,VentMode,plateauP,index

Any help is much appreciated. I^m not sure if it might be a bug, because I keep reading that it should be possible to query by index.

Answer 1

I could reproduce this error when saving HDF file using data_columns=True parameter.

Demo:

index = pd.MultiIndex.from_product([pd.date_range('20170101',periods=4),
                                    np.arange(1,5)],
                                   names=['Datetime','PatientID'])
df = pd.DataFrame(dict(id2=np.random.randint(0, 1000, size=len(index)),
                       w=np.random.randn(len(index))),
                  index=index).reset_index().set_index(['Datetime','PatientID'])

df

yields:

In [54]: df
Out[54]:
                      id2         w
Datetime   PatientID
2017-01-01 1          735  0.223941
           2          262  1.057944
           3          521  1.075384
           4          500  0.932443
2017-01-02 1          372 -0.780629
           2          489 -1.137088
           3          836  1.862521
           4          490 -1.150150
2017-01-03 1          366 -1.411923
           2          763 -1.509591
           3          779 -0.537362
           4          672  0.312881
2017-01-04 1          549  1.670399
           2           90 -2.265253
           3          856 -0.780087
           4          571 -0.498892

now let's save it to HDF file - once without specifying data_columns (default: None) and once with data_columns=True:

store = pd.HDFStore('d:/temp/41482523.h5', complib='blosc', complevel=9)
store.append('no_dc', df)
store.append('dc_true', df, data_columns=True)

both have the same index levels:

In [61]: store.get_storer('no_dc').levels
Out[61]: ['Datetime', 'PatientID']

In [62]: store.get_storer('dc_true').levels
Out[62]: ['Datetime', 'PatientID']

let's try to select using where clause:

In [63]: store.select('no_dc', where="PatientID==4")
Out[63]:
                      id2         w
Datetime   PatientID
2017-01-01 4          500  0.932443
2017-01-02 4          490 -1.150150
2017-01-03 4          672  0.312881
2017-01-04 4          571 -0.498892

In [64]: store.select('dc_true', where="PatientID==4")
...
skipped
...
NameError: name 'PatientID' is not defined
...
skipped
...
ValueError: The passed where expression: PatientID==4
            contains an invalid variable reference
            all of the variable refrences must be a reference to
            an axis (e.g. 'index' or 'columns'), or a data_column
            The currently defined references are: columns,index,id2,w

SOLUTION:

If you want to index columns you can use the following trick:

store.append('dc_all_cols', df, data_columns=df.iloc[[0]].reset_index().columns.tolist())

which produces list of all columns including MultiIndex columns:

In [70]: df.iloc[[0]].reset_index().columns.tolist()
Out[70]: ['Datetime', 'PatientID', 'id2', 'w']

Test select(where="..."):

In [66]: store.select('dc_all_cols', where="PatientID==4")
Out[66]:
                      id2         w
Datetime   PatientID
2017-01-01 4          500  0.932443
2017-01-02 4          490 -1.150150
2017-01-03 4          672  0.312881
2017-01-04 4          571 -0.498892

Store info:

In [65]: store
Out[65]:
<class 'pandas.io.pytables.HDFStore'>
File path: d:/temp/41482523.h5
/dc_all_cols            frame_table  (typ->appendable_multi,nrows->16,ncols->4,indexers->[index],dc->[Datetime,PatientID,id2,w])
/dc_true                frame_table  (typ->appendable_multi,nrows->16,ncols->4,indexers->[index],dc->[id2,w])
/no_dc                  frame_table  (typ->appendable_multi,nrows->16,ncols->4,indexers->[index],dc->[PatientID,Datetime])

Indexed columns in the HDF Store:

In [71]: store.get_storer('no_dc').table.colindexes
Out[71]:
{
    "Datetime": Index(6, medium, shuffle, zlib(1)).is_csi=False,
    "index": Index(6, medium, shuffle, zlib(1)).is_csi=False,
    "PatientID": Index(6, medium, shuffle, zlib(1)).is_csi=False}

In [72]: store.get_storer('dc_true').table.colindexes
Out[72]:
{
    "index": Index(6, medium, shuffle, zlib(1)).is_csi=False,
    "id2": Index(6, medium, shuffle, zlib(1)).is_csi=False,
    "w": Index(6, medium, shuffle, zlib(1)).is_csi=False}

In [73]: store.get_storer('dc_all_cols').table.colindexes
Out[73]:
{
    "w": Index(6, medium, shuffle, zlib(1)).is_csi=False,
    "index": Index(6, medium, shuffle, zlib(1)).is_csi=False,
    "PatientID": Index(6, medium, shuffle, zlib(1)).is_csi=False,
    "id2": Index(6, medium, shuffle, zlib(1)).is_csi=False,
    "Datetime": Index(6, medium, shuffle, zlib(1)).is_csi=False}