熊猫 HDFStore 与多索引按索引值选择未找到列

Question

我有一个大的熊猫数据帧存储在HDF5文件中。数据帧是具有日期时间和患者 ID 的多索引。

store = pd.HDFStore('prepro_mon.h5',mode='r')
print(store.get_storer('raw_import').levels)
Out: ['Datetime', 'PatientID']

但是，按患者 ID 过滤 Select 语句不起作用。

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

我收到此错误

Traceback (most recent call last):
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.py", line 4660, in generate
    return Expr(where, queryables=q, encoding=self.table.encoding)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationpytables.py", line 524, in __init__
    self.terms = self.parse()
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 742, in parse
    return self._visitor.visit(self.expr)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 318, in visit_Module
    return self.visit(expr, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 321, in visit_Expr
    return self.visit(node.value, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 643, in visit_Compare
    return self.visit(binop)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 415, in visit_BinOp
    op, op_class, left, right = self._possibly_transform_eq_ne(node)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 353, in _possibly_transform_eq_ne
    left = self.visit(node.left, side='left')
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 312, in visit
    return visitor(node, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationexpr.py", line 429, in visit_Name
    return self.term_type(node.id, self.env, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationpytables.py", line 41, in __init__
    super(Term, self).__init__(name, env, side=side, encoding=encoding)
  File "C:Program FilesAnaconda3libsite-packagespandascomputationops.py", line 58, in __init__
    self._value = self._resolve_name()
  File "C:Program FilesAnaconda3libsite-packagespandascomputationpytables.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:OxygenationIndexdataloaderOIFeatures.py", line 51, in <module>
    store.select('raw_import', where='PatientID = 88000')
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.py", line 724, in select
    return it.get_result()
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.py", line 1423, in get_result
    results = self.func(self.start, self.stop, where)
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.py", line 717, in func
    columns=columns, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.py", line 4266, in read
    df = super(AppendableMultiFrameTable, self).read(**kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.py", line 4086, in read
    if not self.read_axes(where=where, **kwargs):
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.py", line 3287, in read_axes
    self.selection = Selection(self, where=where, **kwargs)
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.py", line 4647, in __init__
    self.terms = self.generate(where)
  File "C:Program FilesAnaconda3libsite-packagespandasiopytables.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

任何帮助都非常感谢。我^不确定这是否可能是一个错误，因为我一直在阅读应该可以按索引查询。

Answer 1

使用参数保存 HDF 文件时，我可以重现此错误data_columns=True。

演示：

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

收益 率：

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

现在让我们将其保存到HDF文件 - 一次不指定data_columns（默认：None），一次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)

两者具有相同的索引级别：

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

让我们尝试选择使用 where 子句：

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

溶液：

如果要为列编制索引，可以使用以下技巧：

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

生成包括多索引列在内的所有列的列表：

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

测试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

店铺信息：

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])

HDF 存储中的索引列：

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}