The pandas documentation for query() states that
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.query.html
Column names which are Python keywords (like “list”, “for”, “import”, etc) cannot be used.
This is why, in the toy example below, I cannot use pandas.query() to filter the columns called 'min' nor 'sum'
My question is: what is an efficient and easy to read way to filter on multiple columns if I cannot use query() ? E.g. to run the pandas equivalent of a SQL statement like:
select m.* from df m where (m.[min] > 0.5 and m.[min] < 0.9) or m.[sum] > 0.7
Using pandas syntax, this comes to mind:
out5 = df.loc[ ( (df['min'] > 0.5 ) & df['min'] < 0.9 ) | df['sum'] > 0.7 ]
which, well, works, but I find very hard to read, and much more obscure than in most other languages, from SQL to R.
I also don't understand why the first condition [ min > 0.5 ] must be in brackets or else it won't work.
I had thought of using the pandasql package, which loads dataframes into an in-memory SqlLite database, runs a query there, then exports back to pandas, but development stopped about 5 years ago, it's never reached version 1, and I am afraid that data types could be emssed up (eg SqlLite doesn't explicitly support dates or datetimes).
The toy example is this:
import pandas as pd
import numpy as np
df = pd.DataFrame(columns=['a','min','sum','col with space'] , data = np.random.rand(100,4))
# this works
out1 = df.query("a > 0.5" )
# this doesn't: TypeError: '>' not supported between instances of 'function' and 'float'
out2 = df.query(" min > 0.5 ")
# doesn't work:
# NumExprClobberingError: Variables in expression "(sum) > (0.5)" overlap with builtins: ('sum')
out3 = df.query(" sum > 0.5 ")
# this works
out4 = df.query(" `col with space` > 0.5 ")