查询Pandas中的HDF5

Question

我在hdf5文件中将以下数据（18619211行）存储为pandas数据帧对象：

             date    id2         w
id                              
100010 1980-03-31   10401  0.000839
100010 1980-03-31   10604  0.020140
100010 1980-03-31   12490  0.026149
100010 1980-03-31   13047  0.033560
100010 1980-03-31   13303  0.001657

其中id是索引，其他是列。date是np.datetime64。我需要执行这样的查询（代码当然不起作用）：

db=pd.HDFStore('database.h5')
data=db.select('df', where='id==id_i & date>bgdt & date<endt')

注id_i, bgdt, endt都是变量，不是实际值，需要在循环中传递。例如：

dates是Pandas周期索引或时间戳索引，无论哪种方式，我都可以相互转换。

dates=['1990-01', 1990-04','1990-09',......]  

id_list是一个id列表

id_list=[100010, 100011,1000012,.......]

循环看起来是这样的（我做循环的原因是因为数据量很大，我同时查询了其他数据集，然后执行了一些操作）

db=pd.HDFStore('database.h5')
for id_i in id_list:
    for date in dates:
        bgdt=date-1 (move to previous month)
        endt=date-60 (previous 60 month)
        data=db.select('df', where='index==id_i & date>bgdt & date<endt')
        ......

这个问题有两个部分：

• 我不知道如何同时查询索引和列。pandas中的doc演示了如何基于索引条件或列条件进行查询，但没有同时基于索引条件或列条件进行查询的示例。
  • （顺便说一下，这在熊猫文献中很常见。doc通常显示非常简单的事情，比如如何做“A”，或者如何做“B”，但是不显示如何同时做“A”和“B”。一个很好的例子是在多索引pandas数据帧上使用query。文档基于level=0或level=1显示，但没有关于如何同时执行这两个操作的示例。）
• 我不知道如何将三个变量id_i, bgdt, endt传递给查询。我只知道如何通过使用%s来传递，但不是全部。
  • 我对datetime数据类型也有点困惑。似乎有很多日期时间：datetime.datetime，numpy.datetime64，pandas.Period。我主要处理每月的数据，所以pandas.Period是最有用的。但是我不能很容易地转换时间戳的列（不是索引）（从原始数据解析时熊猫的默认日期类型）。有没有任何数据类型是简单的“日期”，不是时间戳，不是期间，而是只有年、月和日的简单日期？

很多麻烦，但我真的很喜欢python和pandas（我正试图将我的工作流从SAS移到python）。任何帮助都将不胜感激！

Answer 1

here是用于查询非索引列的文档。

创建测试数据。不清楚原始框架是如何构造的，例如它的唯一数据和范围，所以我创建了一个示例，其中有10行，还有一个带有id列的多级日期范围。

In [60]: np.random.seed(1234)

In [62]: pd.set_option('display.max_rows',20)

In [63]: index = pd.MultiIndex.from_product([np.arange(10000,11000),pd.date_range('19800101',periods=10000)],names=['id','date'])

In [67]: df = DataFrame(dict(id2=np.random.randint(0,1000,size=len(index)),w=np.random.randn(len(index))),index=index).reset_index().set_index(['id','date'])

In [68]: df
Out[68]: 
                  id2         w
id    date                     
10000 1980-01-01  712  0.371372
      1980-01-02  718 -1.255708
      1980-01-03  581 -1.182727
      1980-01-04  202 -0.947432
      1980-01-05  493 -0.125346
      1980-01-06  752  0.380210
      1980-01-07  435 -0.444139
      1980-01-08  128 -1.885230
      1980-01-09  425  1.603619
      1980-01-10  449  0.103737
...               ...       ...
10999 2007-05-09    8  0.624532
      2007-05-10  669  0.268340
      2007-05-11  918  0.134816
      2007-05-12  979 -0.769406
      2007-05-13  969 -0.242123
      2007-05-14  950 -0.347884
      2007-05-15   49 -1.284825
      2007-05-16  922 -1.313928
      2007-05-17  347 -0.521352
      2007-05-18  353  0.189717

[10000000 rows x 2 columns]

将数据写入磁盘，说明如何创建数据列（注意，索引是自动可查询的，这也允许id2可查询）。这实际上等同于做。这需要打开和关闭商店（您可以通过打开、追加和关闭商店来完成相同的任务）。

要查询列，它必须是数据列或帧的索引。

In [70]: df.to_hdf('test.h5','df',mode='w',data_columns=['id2'],format='table')

In [71]: !ls -ltr test.h5
-rw-rw-r-- 1 jreback users 430540284 May 26 17:16 test.h5

查询

In [80]: ids=[10101,10898]

In [81]: start_date='20010101'

In [82]: end_date='20010301'

可以将日期指定为字符串（内联或变量；还可以指定类似时间戳的对象）

In [83]: pd.read_hdf('test.h5','df',where='date>start_date & date<end_date')
Out[83]: 
                  id2         w
id    date                     
10000 2001-01-02  972 -0.146107
      2001-01-03  954  1.420412
      2001-01-04  567  1.077633
      2001-01-05   87 -0.042838
      2001-01-06   79 -1.791228
      2001-01-07  744  1.110478
      2001-01-08  237 -0.846086
      2001-01-09  998 -0.696369
      2001-01-10  266 -0.595555
      2001-01-11  206 -0.294633
...               ...       ...
10999 2001-02-19  616 -0.745068
      2001-02-20  577 -1.474748
      2001-02-21  990 -1.276891
      2001-02-22  939 -1.369558
      2001-02-23  621 -0.214365
      2001-02-24  396 -0.142100
      2001-02-25  492 -0.204930
      2001-02-26  478  1.839291
      2001-02-27  688  0.291504
      2001-02-28  356 -1.987554

[58000 rows x 2 columns]

您可以使用在线列表

In [84]: pd.read_hdf('test.h5','df',where='date>start_date & date<end_date & id=ids')
Out[84]: 
                  id2         w
id    date                     
10101 2001-01-02  722  1.620553
      2001-01-03  849 -0.603468
      2001-01-04  635 -1.419072
      2001-01-05  331  0.521634
      2001-01-06  730  0.008830
      2001-01-07  706 -1.006412
      2001-01-08   59  1.380005
      2001-01-09  689  0.017830
      2001-01-10  788 -3.090800
      2001-01-11  704 -1.491824
...               ...       ...
10898 2001-02-19  530 -1.031167
      2001-02-20  652 -0.019266
      2001-02-21  472  0.638266
      2001-02-22  540 -1.827251
      2001-02-23  654 -1.020140
      2001-02-24  328 -0.477425
      2001-02-25  871 -0.892684
      2001-02-26  166  0.894118
      2001-02-27  806  0.648240
      2001-02-28  824 -1.051539

[116 rows x 2 columns]

也可以指定布尔表达式

In [85]: pd.read_hdf('test.h5','df',where='date>start_date & date<end_date & id=ids & id2>500 & id2<600')
Out[85]: 
                  id2         w
id    date                     
10101 2001-01-12  534 -0.220692
      2001-01-14  596 -2.225393
      2001-01-16  596  0.956239
      2001-01-30  513 -2.528996
      2001-02-01  572 -1.877398
      2001-02-13  569 -0.940748
      2001-02-14  541  1.035619
      2001-02-21  571 -0.116547
10898 2001-01-16  591  0.082564
      2001-02-06  586  0.470872
      2001-02-10  531 -0.536194
      2001-02-16  586  0.949947
      2001-02-19  530 -1.031167
      2001-02-22  540 -1.827251

为了回答你的实际问题，我会这样做（他们的信息确实不够，但我会提出一些合理的期望）：

• 不要循环查询，除非有非常少量的绝对查询
• 把最大的一块读入内存。通常，这是通过选择您需要的最大范围的数据来完成的，即使您选择的数据比实际需要的多。
• 然后使用内存中的表达式进行子选择，这通常会加快数量级。
• 列表元素总共被限制为大约30个元素（这是PyTables方面当前的一个实现限制）。如果您指定更多，它将起作用，但会发生的情况是，您将读取大量数据，然后它将被重新索引（在内存中）。所以用户需要意识到这一点。

例如，假设您有1000个惟一的id，每个id有10000个日期，如我的示例所示。您要选择其中200个，日期范围为1000。

因此，在本例中，我只需选择日期，然后进行内存比较，如下所示：

df = pd.read_hdf('test.h5','df',where='date=>global_start_date & date<=global_end_date')
df[df.isin(list_of_ids)]

您可能还拥有每个ID更改的日期。所以把它们分块，这次使用一个id列表。

像这样的：

output = []
for i in len(list_of_ids) % 30:
    ids = list_of_ids[i:(i+30)]
    start_date = get_start_date_for_these_ids (global)
    end_date = get_end_date_for_these_ids (global)
    where = 'id=ids & start_date>=start_date & end_date<=end_date'
    df = pd.read_hdf('test.h5','df',where=where)
    output.append(df)

 final_result = concat(output)

然后，基本思想是使用所需的条件选择数据的超集，然后进行子选择以使其适合内存，但是您限制了查询的数量（例如，假设您最终选择了查询的一行，如果您必须查询这个18M次，那就太糟糕了）。