Pandas HDF query syntax is more restrictive than PyTables

[BrenBarn]

With PyTables, you can do queries like someTable.where("X**2 + Y**2 < 1") (as described here). It seems, though, that pandas imposes its own more restrictive query syntax which allows only a very limited set of query operations. This is confusing to users who expect to be able to query a Pandas HDFStore in the same way that they would query the underlying PyTables table.

Comments by @jreback on this Stackoverflow question suggest the pandas query handling is needed to handle complex queries and/or queries involving datetimes. However, it's rather extreme to block all queries from using complex expressions, even when the same query would work fine on the Pytable itself. (The SO question gives a simple example of that.) I suggest the following:

1. The documentation should make it very clear that the examples provided there exhaustively describe the types of possible queries. (If they don't, then we need to come up with a comprehensive explanation of what is allowed.) They should also document that this query syntax is a subset of what is allowed with Pytables.
2. It would be nice to provide some sort of override flag (or perhaps a separate function) saying "Just pass this query through to Pytables", to stop pandas from messing with the query. There is still work to be done in terms of wrapping the query result in a DataFrame, but this is on the output side and doesn't require modifying the query on the way in. This might also need to accept the condvars argument to pass ad-hoc variables to PyTables for use in queries.

Any thoughts on this? I'm curious what kinds of queries motivated the creation of this pandas-specific query syntax initially.

[jreback]

There are quite a few example in the tests, but the following are quite common things to do and yet the syntax that PyTables forces is quite specialized and error prone. The key here is that type inference and conversion can be done on the associated selectors, basically you can say hey this column is a datetime so let's do appropraite conversion on the input arguments, which is orthogonal to the actual representation inside the data store.

Further we can all more natural syntax (the 'and' and the 'in' clauses), function evaluation, and automatic variable interpretation.

I have printed the generated query underneath for convenience.

In [4]: df = DataFrame({'A' : pd.date_range('20130101',periods=3), 'B' : list('abc')})

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

In [6]: pd.read_hdf('test.h5','df',where='B in ["a","c"]')
((B == "a") | (B == "c"))
Out[6]: 
           A  B
0 2013-01-01  a
2 2013-01-03  c

In [7]: pd.read_hdf('test.h5','df',where='A>"20130101" and A<=pd.Timestamp("20130103")')
((A > 1356998400000000000) & (A <= 1357171200000000000))
Out[7]: 
           A  B
1 2013-01-02  b
2 2013-01-03  c

So these are really for user convience. Surely you could write the actual query, but would you actually want to?

So you have your title backwards. pandas query syntax is MUCH more expressive that PyTables would ever hope to be (or should be IMHO, it does a great job for the limited dtype support)

[BrenBarn]

I agree that is convenient. What I'm saying is that it is inconvenient not to be able to do other equally reasonable and very simple things like where="A<B+2" or where="A+B<2".

Ideally the pandas query syntax could eventually be expanded to allow arbitrary expressions. But in the absence of that I think it makes sense to allow a bypass for the large number of potential cases which use more complicated expression syntax and thus are currently (as I understand it) not available at all via the pandas syntax.

[jreback]

well, I don't think it very hard to add what you are talking about, just a matter of no-one ever requested and I didn't have as a test case.

I would certainly agree that pandas syntax should be a super-set. I don't think it very hard to get there, your other issue #11283 would likely solve the problem.

[BrenBarn]

The other issue won't solve the problem; that just fixes the case where you're doing a comparison against a single value that happens to be a numpy value.

Is there an existing issue for adding various sorts of expressions to the query syntax?

[jreback]

oh, that seemed to cover what I think you were looking. I can reopen this if you'd like to address what you think are missing.