whitespaces in column name handled differently than input to eval/query

[EinarJohnsen]

Code Sample

>>> import pandas as pd
>>> d = pd.DataFrame(([1, 2], [3, 4]), columns=["Col 1", "Col 2"])
>>> d.query("`Col 1` < `Col 2`") # This works as expected.
   Col 1  Col 2
0      1      2
1      3      4
>>>  d.query("`Col  1` < `Col 2`") # 2 whitespaces between "Col" and "1". Also works, but not expected?
   Col 1  Col 2
0      1      2
1      3      4

>>> d = pd.DataFrame(([1, 2], [3, 4]), columns=["Col  1", "Col 2"]) # 2 whitespaces between "Col" and "1" in the column name. 
>>> d.query("`Col  1` < `Col 2`") # 2 whitespaces between "Col" and "1" in the query.  
pandas.core.computation.ops.UndefinedVariableError: name 'Col_1_BACKTICK_QUOTED_STRING' is not defined

>>> d.query("`Col          1` < `Col 2`") # 10 whitespaces between "Col" and "1".
pandas.core.computation.ops.UndefinedVariableError: name 'Col_1_BACKTICK_QUOTED_STRING' is not defined

>>> d.eval("`Col  1` < `Col 2`") # 2 whitespaces between "Col" and "1".
pandas.core.computation.ops.UndefinedVariableError: name 'Col_1_BACKTICK_QUOTED_STRING' is not defined

Problem description

When using backticks in the input to DataFrame.query or DataFrame.eval, it seems that the number of whitespaces between Col and 1, is mapped into: Col_1_BACKTICK_QUOTED_STRING, regardless of how many whitespaces there are between Col and 1.

Having 2 whitespaces when defining the column name in the DataFrame, it seems that the column name is defined as: Col__1_BACKTICK_QUOTED_STRING.
With 10 whitespaces: Col__________1_BACKTICK_QUOTED_STRING, and so forth.

This makes the number of whitespaces in the column name important, as column name will be based on the amount of them. However, the number of whitespaces in the input to DataFrame.query or DataFrame.eval does not seem to have any affect, and will always have one underscore, and not match the column name if it has more than one whitespace.

Expected Output

The expected output would be to have the column name and the input to query or eval transformed with the same function, having a one to one mapping between whitespaces and underscores when definding the variable. i.e.
2 whitespaces: Col__1_BACKTICK_QUOTED_STRING.
10 whitespaces: Col__________1_BACKTICK_QUOTED_STRING.

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit : None python : 3.6.8.final.0 python-bits : 64 OS : Linux OS-release : 3.10.0-957.21.3.el7.x86_64 machine : x86_64 processor : x86_64 byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : en_US.UTF-8

pandas : 0.25.1
numpy : 1.17.2
pytz : 2019.3
dateutil : 2.8.0
pip : 19.2.3
setuptools : 41.4.0
Cython : None
pytest : 5.2.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.10.1
IPython : 7.8.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : 2.7.0
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 0.15.0
pytables : None
s3fs : None
scipy : 1.3.1
sqlalchemy : None
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None

[dvir1]

I think you're right, notice that in line
d.query("`Col 1` < `Col 2`") # 2 whitespaces between "Col" and "1" in the query.
you have 3 whitespaces instead of 2

[aaditya-panik]

I had an initial look at this. The expression parser seems to assume that column names would not be differentiated based on the number of whitespaces in them according to how it's written here:

pandas/pandas/core/computation/expr.py

Lines 60 to 64 in d8f9be7

	tokval = " ".join(
	it.takewhile(
	lambda tokval: tokval != "`",
	map(operator.itemgetter(1), token_generator),
	)

The tokenize module generates tokens and excludes the whitespaces.

I would've further looked into this, but this is literally my first go at debugging issues in open-source projects so just here to observe and learn first 😅. I just left this as a reference for someone to have a look into this. Moreover

[hwalinga]

Hello, if the maintainers are going to accept my PR this is going to be fixed in #28215.

[EinarJohnsen]

Fixed in #28215