在Pandas中查询HDF5
我有一份数据（18,619,211行），存储在一个hdf5文件中的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的文档展示了如何根据索引条件或列条件进行查询，但没有示例说明如何同时根据这两者进行查询。
- （顺便说一下，这在Pandas文档中很常见。文档通常只展示如何做'A'，或者如何做'B'，但不展示如何同时做'A'和'B'。一个好的例子是如何在MultiIndex的pandas数据框上使用
query。文档展示了如何基于level=0或level=1进行查询，但没有示例说明如何同时做这两者。）
- （顺便说一下，这在Pandas文档中很常见。文档通常只展示如何做'A'，或者如何做'B'，但不展示如何同时做'A'和'B'。一个好的例子是如何在MultiIndex的pandas数据框上使用
- 我不知道如何将三个变量
id_i, bgdt, endt传递给查询。我知道如何使用%s传递一个，但不知道如何传递所有三个。- 我对日期时间的数据类型也有点困惑。似乎有很多种日期时间类型：
datetime.datetime、numpy.datetime64、pandas.Period。我主要处理的是按月的数据，所以pandas.Period是最有用的。但是我不能轻易地将一个时间戳的列（从原始数据解析时Pandas的默认日期类型）转换成这种格式。有没有一种数据类型仅仅是“日期”，不是时间戳，也不是周期，而只是一个简单的日期，只有年、月和日？
- 我对日期时间的数据类型也有点困惑。似乎有很多种日期时间类型：
虽然遇到了很多麻烦，但我真的很喜欢Python和pandas（我正在尝试将我的工作流程从SAS转移到Python）。任何帮助都将不胜感激！
1 个回答
10
这里是关于如何查询非索引列的文档。
首先，创建一些测试数据。原始数据框是怎么构建的并不清楚，比如它的数据是否唯一，范围是什么，所以我创建了一个样本，包含1000万行数据，并且有一个多层次的日期范围和一个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)
基本的思路是，使用你想要的标准选择一个数据的超集，然后进行子选择以适应内存，但要限制你的查询次数（例如，想象一下，如果你的查询最终只选择一行数据，但你要查询1800万次，那就很糟糕）。