WIP: use eval expression parsing as replacement for Term in HDFStore
State: closed

extension of @cpcloud #4037

allows natural syntax for queries, with in-line variables allowed

some examples
`"ts>=Timestamp('2012-02-01') & users=['a','b','c']"`

`['A > 0']`

```
dates=pd.date_range('20130101',periods=5)
'index>dates'
```

Todos:
- [ ] update docs / need new examples
- [ ] API changes (disallow dict, and separated expressions)
- [ ] tests for `|` and `~` operators, and invalid filter expressions
- [ ] code clean up, maybe create new `Expr` base class to opt in/out of allowed operations
    (e.g. `Numexpr` doesn't want to allow certain operations, but `PyTables` needs them,
     so maybe define in the base class for simplicity, and just not allow them (checked in `visit`),
     which right now doesn't allow a `generic_visitor`)
- [ ] can `ops.Value` be equiv of `ops.Constant`?


Labels:

Comments:
looks nice!

`visit` calls `generic_visitor` if there's no `visit_SomeASTNode` method

not sure if `Value` could be replaced by `Constant` since if look up fails and `name` is not a string then the value is used, but if `name` is a string then it will raise, since you're allowing strings that's a problem. you could do is map string to themselves

did u take out `USub`?

https://s3.amazonaws.com/archive.travis-ci.org/jobs/8829617/log.txt

no but I think I need to handle the class vars differently
I think I am overwriting the base class (I need to copy)

class vars ok.....just looking at the 3.2 fails now....

turn out `[-1]` parses using `USub` in py3.....weird

shouldn't that parse as `List(elts=[USub(op=Neg, value=Num(n=1))])` or something like that (not sure I got all of the fields correct...regardless of version?

nope i'm wrong, py2 does it without the `USub`

py3: `'Module(body=[Expr(value=List(elts=[UnaryOp(op=USub(), operand=Num(n=1))], ctx=Load()))])` which is what i would have expected all along, looks like py2 special cases constants

py2: `'Module(body=[Expr(value=List(elts=[Num(n=-1)], ctx=Load()))])`

py2 is different (and actually this was a subscript op but that's ok)

ok...fixed some silly py3 issues...everything should pass now

so i guess assignment expressions are out of the question, for all time? Going to be difficult to disambiguate the parse if we wanted to do that later down the line, unless there's an arbitrary rule: something like `a = b` means assignment but e.g., in the context of boolean operations `a = b | b = c` `=` means `==`

no I think u might be able to handle assignment for the ne engine
for pytables I am always going to do the translation (as by its nature a query expression)
so prob just need to refactor out the assignment conversion
so u can ignore it and I will use it

I think I mentioned above that ExprVisitor should be vey general (eg having pretty much all ops defined)
then in sub classes u can override as needed ( and have explicit enabling of ops)

oh i see. yes tweak the parser in a subclass for the engine but keep reasonable parsing defaults

watch out `blaze` here we come!

hahha

want to be careful about syntax though 
don't want to go crazy

sure lest we all start declaring brainf--k executable pseudocode

Hi, are you planning to also enable this for in-memory DF's? 

This would be a huge improvement. At the moment df[df.a==7] does not only imply more typing (most df's having longer names and logical conditions more terms) but more importantly, it makes impossible to apply the same query to same-schema df's or to df after merging with another data frame. I.e. if I have cond = 'a=b & c<d' applicable in df, it is very advantageous to be able to reuse this expression in df3 = df.merge(df2), which is currently not the case. 

But probably you have thought of this already! 

Here is a sample perf test

@meteore not exactly sure what you are asking

can you give a more concrete example?

```
In [54]: df = DataFrame(randn(1000000, 10))
df2
In [55]: df2 = DataFrame(randn(*df.shape))
df
In [56]: df3 = DataFrame(randn(*df.shape))

In [57]: ex1 = 'df + df2 * 2 + df3 ** 2 * df * df + df2 ** 4'

In [58]: ex2 = 'df + df2 + df3'

In [59]: timeit pd.eval(ex1)
10 loops, best of 3: 58.5 ms per loop

In [60]: timeit pd.eval(ex2)
10 loops, best of 3: 53.5 ms per loop

In [61]: timeit df + df2 * 2 + df3 ** 2 * df * df + df2 ** 4
1 loops, best of 3: 558 ms per loop

In [62]: timeit df + df2 + df3
10 loops, best of 3: 77.2 ms per loop
```

sounds like @meteore might be asking if we're going to include method calls and/or attribute access. the answer to both for the `numexpr` engine is: maybe

#### ideas about how to do it

one way to do this is to call the method (attribute access is a method too) on the pandas object in python and then reassign to a temporary variable (doing this recursively down the chain of methods if any). ostensibly this would work for any function too

i think @meteore is asking if we're going to allow something like

`df.eval('a < b & c == d')` where `a`, `b`, `c` and `d` are columns in `df`

which is probably not a big deal to add (no `pytables` engine here)

and definitely much better than

`df[(df.a < df.b) & (df.c == df.d)]`

probably just adding the scope, a la

``` python
def eval(self, expr, enforce_scope=True, **kwargs):
    columns = self.columns
    env = kwargs.setdefault('env', Scope())
    if set(env.locals) & set(env.globals) & set(columns) and enforce_scope:
        raise NameError("a useful error message")
    else:
        env.locals.update(self.to_dict())
    return pd.eval(expr, **kwargs)
```

would work

i think supporting

`df["df['a']>df['b']"]` is pretty easy, just need to add the new methods i defined in visit for: attribute, subscript,index, slice,call,list, etc....

the assignment thing I would wait on...I mean you could do

`pd.eval("df['a'] = df['a'] + df['b']")`

but not sure that's _useful_

`df['a'] = E("df['a'] + df['b']")` I think will be the way to specify this

(`E` is of course shortcut for `pd.eval`)

and of course

```
foo = ['A','B']
df["df>0 & df['a'] == foo"] = ......
```

is quite doable with currrent impl

as an FYI on this, some optimization may actually make perf worse....eg. `isin` is quite fast (and ne/nummexpr)
prob don't handle this anyhow so the `df['a'] == foo` should be ddone in pandas space rather than ne....

some of these you can prob  deal with some maybe be hard to even figure out....

this cann get infiitly complex

no i wasn't suggesting assignment, i that was a typo sorry, don't think that's useful either.

slicing inside the string seems a bit redundant i would highly discourage users from using that syntax and just name the columns, problem there is a name clash between outer scope vars and column names, because python objects are really just namespaces

@jreback do u call the method before putting into the op? or do u call it when needed?

as for the previous, we could have an `enforce_scope=True` keyword argument that makes `eval` raise when column names overlap if `True` and favors column names if `False`, defaulting to `True` it would only be for frame `eval`

see above ... i'll add that if you ok it. i think not having to type all of the `df.x1 op1 ... opN df.xN` would be nice, but is another thing to remember. but hey if you're using `eval` you might be ok with taxing your working memory a bit more ;)

I effectively delay all op calling to the very end

my algo is a bit different then yours
- parse the terms (which will for example turn `df.index[foo]` into the selected index (e.g. say foo is a slice or a sub-set or whatever)
- I create 2 different pruned trees
  - condition, which is the syntax I pass to `ne`, I have to prune to remove any `filters`, e.g. a selector which is not in the table (e.g. if you store a frame, then you can use `ne` to select on the `index`, but the `columns` becomes a filter
  - filter, which is the inverse of condition. I actually only allow a single `andable` filter here for simplicity, e.g.
    I don't allow right now (`columns = A | columns = B`), in theory I could but didn't on this iteration

then I pass the condition syntax to `ne` and use the `filter` to post-process

scope is easier than you think....because you have the expression and you can grab scope right there (its a tiny bit tricky because if the user wraps the expression in a `Term` then it grabs scope, then later I 'rememberr' this scope and update when I assemble the final expression

so the following are equivalent

`store.select('df',[Term('....'),Term('....')])`

and

`store.select('df',['.... & ....'])` 

IOW I implicity and  passed list/tuple (see my version of `Expr`)

Just to clarify: what would be very useful is the following (just made up this example; I have this all the time in neurophysiology data analysis)

**Now**

``` python
    closed_high = issues_df[(issues_df.severity>=2) & (issues_df.status== 'done')]
    # I need to specify the condition again for another filter
    closed_critical = closed_high[closed_high.severity>=4] 
```

**Proposal**

``` python
    is_done = 'status=done' # or use == if better-defined
    is_high = 'severity >=2'
    is_critical = 'severity >=4'

    # can use everywhere!
    closed_high = issues_df[and(is_done, is_high)] 
    closed_critical = closed_high[is_critical] # one can reuse the query string if instead applying directly to issues
```

Little details:
- using = or == for equality, whatever is most convenient.
- if `[ ]` too overloaded, could use `.query`
- `is_interesting = is_done + ' & ' + is_high` should also be possible.  

scoping issues I use the `update` method of `Scope`, essentially taking a stack_level and updating the namespace from top-level to bottom (e.g. in reverse stack order)....seems to work (with nearer shadow variables overwriting top-level)...in practie I don't think this is a big deal.....biggest issues is to make sure that you capture the calling state

Another little detail
- prefixing a column name with a string signals that it is nonlocal to the current data frame.
- and somewhat unrelated but also  very useful for relational work: if a column access results in KeyError, search amongst the named indexes before giving up.