如何使用Pandas / Python查询HDF存储

To manage the amount of RAM I consume in doing an analysis, I have a large dataset stored in hdf5 (.h5) and I need to query this dataset efficiently using Pandas.

为了管理我在进行分析时消耗的RAM量,我有一个大型数据集存储在hdf5(.h5)中,我需要使用Pandas有效地查询这个数据集。

The data set contains user performance data for a suite of apps. I only want to pull a few fields out of the 40 possible, and then filter the resulting dataframe to only those users who are using a one of a few apps that interest me.

该数据集包含一套应用程序的用户性能数据。我只想从40个字段中提取几个字段,然后将结果数据帧过滤为仅使用我感兴趣的几个应用程序之一的用户。

# list of apps I want to analyze
apps = ['a','d','f']

# Users.h5 contains only one field_table called 'df'
store = pd.HDFStore('Users.h5')

# the following query works fine
df = store.select('df',columns=['account','metric1','metric2'],where=['Month==10','IsMessager==1'])

# the following pseudo-query fails
df = store.select('df',columns=['account','metric1','metric2'],where=['Month==10','IsMessager==1', 'app in apps'])

I realize that the string 'app in apps' is not what I want. This is simply a SQL-like representation of what I hope to achieve. I cant seem to pass a list of strings in any way that I try, but there must be a way.

我意识到字符串'app in app'并不是我想要的。这只是我希望实现的类似SQL的表示。我似乎无法以任何方式传递一系列字符串,但我必须有办法。

For now I am simply running the query without this parameter and then I filter out the apps I don't want in a subsequent step thusly

现在我只是在没有这个参数的情况下运行查询,然后我在后续步骤中过滤掉了我不想要的应用程序

df = df[df['app'].isin(apps)]

But this is much less efficient since ALL of the apps need to first be loaded into memory before I can remove them. In some cases, this is big problem because I don't have enough memory to support the whole unfiltered df.

但这样效率要低得多,因为在我删除之前,所有应用程序都需要首先加载到内存中。在某些情况下,这是一个很大的问题,因为我没有足够的内存来支持整个未经过滤的df。

1 个解决方案

#1

You are pretty close.

你很近。

In [1]: df = DataFrame({'A' : ['foo','foo','bar','bar','baz'],
                        'B' : [1,2,1,2,1], 
                        'C' : np.random.randn(5) })

In [2]: df
Out[2]: 
     A  B         C
0  foo  1 -0.909708
1  foo  2  1.321838
2  bar  1  0.368994
3  bar  2 -0.058657
4  baz  1 -1.159151

[5 rows x 3 columns]

Write the store as a table (note that in 0.12 you will use table=True, rather than format='table'). Remember to specify the data_columns that you want to query when creating the table (or you can do data_columns=True)

将商店写为表格(请注意,在0.12中,您将使用table = True,而不是format ='table')。请记住在创建表时指定要查询的data_columns(或者可以执行data_columns = True)

In [3]: df.to_hdf('test.h5','df',mode='w',format='table',data_columns=['A','B'])

In [4]: pd.read_hdf('test.h5','df')
Out[4]: 
     A  B         C
0  foo  1 -0.909708
1  foo  2  1.321838
2  bar  1  0.368994
3  bar  2 -0.058657
4  baz  1 -1.159151

[5 rows x 3 columns]

Syntax in master/0.13, isin is accomplished via query_column=list_of_values. This is presented as a string to where.

master / 0.13中的语法,isin是通过query_column = list_of_values完成的。这是作为字符串显示在哪里。

In [8]: pd.read_hdf('test.h5','df',where='A=["foo","bar"] & B=1')
Out[8]: 
     A  B         C
0  foo  1 -0.909708
2  bar  1  0.368994

[2 rows x 3 columns]

Syntax in 0.12, this must be a list (which ands the conditions).

在0.12中的语法,这必须是一个列表(其中包括条件)。

In [11]: pd.read_hdf('test.h5','df',where=[pd.Term('A','=',["foo","bar"]),'B=1'])
Out[11]: 
     A  B         C
0  foo  1 -0.909708
2  bar  1  0.368994

[2 rows x 3 columns]

#1