ENH: Quoting column names containing spaces with backticks to use them in query and eval.
State: closed

- [x] closes #6508
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: API Design

Comments:
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=h1) Report
> Merging [#24955](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/95f8dca47723192ddeed5a2f198d0521c687c9aa?src=pr&el=desc) will **increase** coverage by `<.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/24955/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #24955      +/-   ##
==========================================
+ Coverage   92.38%   92.38%   +<.01%     
==========================================
  Files         166      166              
  Lines       52398    52406       +8     
==========================================
+ Hits        48406    48414       +8     
  Misses       3992     3992
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.8% <100%> (ø)` | :arrow_up: |
| #single | `42.89% <40%> (-0.01%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/24955/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `96.92% <100%> (ø)` | :arrow_up: |
| [pandas/core/common.py](https://codecov.io/gh/pandas-dev/pandas/pull/24955/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29tbW9uLnB5) | `98.44% <100%> (+0.03%)` | :arrow_up: |
| [pandas/core/computation/expr.py](https://codecov.io/gh/pandas-dev/pandas/pull/24955/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29tcHV0YXRpb24vZXhwci5weQ==) | `88.77% <100%> (+0.08%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=footer). Last update [95f8dca...114b2c2](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=h1) Report
> Merging [#24955](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/46639512c06300a9844ea27f90167d5648c9b93a?src=pr&el=desc) will **increase** coverage by `<.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/24955/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #24955      +/-   ##
==========================================
+ Coverage   91.26%   91.27%   +<.01%     
==========================================
  Files         173      173              
  Lines       52982    53003      +21     
==========================================
+ Hits        48356    48376      +20     
- Misses       4626     4627       +1
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `89.83% <100%> (ø)` | :arrow_up: |
| #single | `41.76% <60%> (+0.01%)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/computation/common.py](https://codecov.io/gh/pandas-dev/pandas/pull/24955/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29tcHV0YXRpb24vY29tbW9uLnB5) | `89.47% <100%> (+3.75%)` | :arrow_up: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/24955/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `96.79% <100%> (ø)` | :arrow_up: |
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/24955/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `93.52% <100%> (ø)` | :arrow_up: |
| [pandas/core/computation/expr.py](https://codecov.io/gh/pandas-dev/pandas/pull/24955/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29tcHV0YXRpb24vZXhwci5weQ==) | `88.52% <100%> (+0.35%)` | :arrow_up: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/pull/24955/diff?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `89.3% <0%> (-0.11%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=footer). Last update [4663951...192c093](https://codecov.io/gh/pandas-dev/pandas/pull/24955?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

Okay, apparently syntax is really important for the release notes. (Sorry, first time I am doing such a thing.) So, before I make another attempt. Is this correct?

```
 - You can now refer to a column name with spaces by quoting it in backticks for the functions :func:`DataFrame.query` and :func:`DataFrame.eval`. (:issue:`6508`)
```

One line, no linebreaks. 
@TomAugspurger 

Yes, that will clash. But is it really that problematic that it clashes? Maybe it can be used as an advantage to the user, to use that name instead, if he/she doesn't like backtick quoting. 

Anyway, there are a few ways we can do something about it. 

 * Make clash less likely 

So, if we use a suffix like: `_SPECIAL_BACKTICK_QUOTED_SUFFIX`, I think it is unlikely this will cause any problems in the real world. It is also most easy and elegant to implement. I only change this in the common.py function. 

---

The problem with really solving is, that is complete ambiguity, is that when we are in the parser, we don't have access to the column names anymore. The column names are somewhere in a DeepChainMap in a Scope object and I don't know how and if we can extract only the column names from this DeepChainMap, since a lot more has been added to it. I can try to reverse engineer this process, but if the process is altered, it will break. Not worth it. So we can do:

 * Bring the parser logic to the api level
Only when we are at the eval function of the DataFrame we have correct access to the collumn names and can perform a non-ambiguous replacement of the back quoted string. 

 * Bring a class with the column names all into the parser 
We can bring a class containing the column names all the way to the parser. 
I think it isn't that big of a problem to catch backtick quoting with a simple regex. Backticks are not likely to occur normally in those queries. 

But if we want it to be based on tokenization of a quoted expression, it is a bit more tricky. Tokenization is done by python's own tokenization function and that will split the backtick quoted string into multiple tokens. 

There are two ways to still make this happen. 

 * Write some logic around Python's tokenization that catches a backtick and appends all following tokens to it. Still a bit a hack. 

 * Fork Python's tokenization method and add support for backtick quoted strings. Shouldn't be too difficult to adjust since it already can detect normally quoted strings, but you pull in a lot of code and all changes to that method in the Python core than has to be manually applied to the Pandas tokenization method. Also, I don't know how simple that is to make Python2 compatible, but maybe I don't have to?

---

I can change the test to use the fixtures and add some more mixed cases, but what does IOW mean?
@hwalinga ``.query`` is just a user convenience function, it is not meant to fully replicate the api at all. you can *always* and unambiguously do ``df[df['columns with spaces']>1]`` to get complete api functionaility. I am not averse to using a uuid in the column name (this is how you do this to guarantee that you never have clashes), but still prefer to tokenize properly (and of course I don't mean adding our *own* custom tokenizer). 
@jreback  If it is just a convenience function, why not make it even more convenient? :)

But let's focus on what has to be done first. One by one. 

---

I think I can write some kind of wrapper around Python's tokenizer to catch backtick quoted strings. 

---

I don't think a uuid will help. Eventually numexpr needs to receive valid Python identifiers, so I think a more complex suffix will be enough to prevent clashes. This would than be the same as how @ is replaced by local variables. 

---

Since changing the approach to the tokenizer instead is a different approach, is it okay that I make a different pull request for that?
@jreback I changed the tokenize function so that backtick quoted strings are now a single token. 

PS. The failed test on macOS is an off by one error in a datetime index test. Seems unrelated to my changes.
@jreback Can you see if this is now ready to be included?
@hwalinga looks like you have a merge conflict in the whatsnew that needs to be resolved
can you merge master
@jreback Merge conflicts resolved. 
thanks @hwalinga nice patch!