Skip to main content
 首页 » 编程设计

python之具有按索引值选择多索引的 Pandas HDFStore 找不到列

2025年02月15日13lori

我有一个存储在 HDF5 文件中的大型 Pandas Dataframe。 Dataframe 是具有 Datetime 和 PatientID 的多索引。

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

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

请您参考如下方法:

在使用 data_columns=True 参数保存 HDF 文件时,我可能会重现此错误。

演示:

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

生成所有列的列表,包括 MultiIndex 列:

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}