PyTables enhancements for selection

[jreback]

now

changes to pandas.io.pytables to support more natural selection (from tables):

1. rename column -> major, index -> minor ( to be more consistent with panel nomenclature)
2. provide parsable string selection methodology - pretty easy to do - and can be backwards compatible

store.select('mypanel', where = [ 'major>=20120103', 'major<=20120401', dict(minor = ['A','B','C' ]))

rather than existing

store.select('mypanel', where = [ 
dict(field = 'column', op = '>=', value = datetime.datetime(2012,1,3)), 
dict(field = 'column', op = '<=', value = datetime.datetime(2012,4,1)), 
dict(field = 'index', value = ['A','B','C'])  ])

future

not sure that pandas should get really fancy just yet with operations - (e.g. 'or' operations, and actual value selection)

where = [ ( 'major>20120901' & dict(minor = ['A','B','C']) | (minor = ['D']) ]
where = [ item['foo']>2.0 ]

but probably necessary once pandas support 'chunking' type operations on pytables

need to build a full-fledged selection parser to translate to the numexpr type operations (maybe with a patsy backend????)
BUT this may actually be useful to support generic operations in this way on in-memory panels/frames

not sure of use cases here though - I usually just read in 'about' what data I need and sub-select from there
unless you have hundreds of millions of rows I don't know if its necessary to optimize more (in which case it is!)

[hayd]

I wonder if this would be a useful feature to extend this notation to regular DataFrames... has it been discussed before? (I think it may have been.)

Someone was trying to roll theIr own DSL for this on SO...

[jreback]

absolutely, hopefuly in #3202, #3393 going to implement df.eval() which will allow things like:

df['df>0 & df<5'] (which .eval will actually execute), also see the expressions module, some beginning of support for this as well

The theory is to accept a numpy-like DSL (but with frames/series/constants) that potentially need alignment and then pass the numpified to numexpr for evaluation

Which is also similar to the expressions in HDFStore queries (via Terms)

a bit non-trivial as to have to take the string expression, compile/parse it, walk the ast tree to find the aligning sections, then repackage to numexpr

@hayd up for it????

[hayd]

Do you think going via Terms is a good solution:

df.select(where=[Term(...), Term(..)])  # just like HDFStore

and then the eval'd string would be parsed into that.

That way, we could first get select working with Terms (which shouldn't be too bad), and then write the parser for the DSL (we have to come to a consensus on the grammar...). ?

[jreback]

That is definitely a good start on it. The thoughts I had were:

1. move Term to the expressions module and generalized it
2. a list of Termss is an and, so need an or, maybe something like:

import expressions as e
_or = e.OR
_and = e.AND
_not = e.NOT
_t = e.Term

expr = e.Expression(_or(_and(_t('foo','=',2), _t('bar','=',1)), _t('foobar','>',5)))
mask = expr.eval(df)

Roughty equivalent to:

mask = ((df['foo'] == 2) & (df['bar'] == 1)) | (df['foobar']>5)

which is an easy way to compose (not that user friendly though),
essentially anything can be built up toExpressions

This then could replace the syntax in HDFStore for queries, and be used (until we do parsing)
in pretty much any expression

want to give it a try?

[jreback]

@hayd so we don't lose this thread....why don't we continue on #3393

[hayd]

Happy to give this a try. Will thrash this out to expressions later in the week, and ping back on the other thread. :)

[jreback]

great!

FYI the numexpr package does basically this (unfortunately we can't use it directly because we have to preprocess / align and such, you might get some inspiration from that (we in fact call this with expressions.evaluate)