Using Pandas query() to filter dataframe on a timestamp column

Question

I'm trying to filter a Pandas dataframe using a string and function query() on a timestamp column:

df.query('Timestamp < "2020-02-01"')

However, I get the following error:

Traceback (most recent call last):   
File "C:\ENERCON\Python 3.7.2\lib\site-packages\IPython\core\interactiveshell.py", line 3326, in run_code
     exec(code_obj, self.user_global_ns, self.user_ns)   
File "<ipython-input-3-7bb40e9c631a>", line 1, in <module>
     df.query('Timestamp < "2020-02-01"')   
File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\frame.py", line 3199, in query
     res = self.eval(expr, **kwargs)   
File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\frame.py", line 3315, in eval
     return _eval(expr, inplace=inplace, **kwargs)   
File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\eval.py", line 327, in eval
     ret = eng_inst.evaluate()   
File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\engines.py", line 142, in evaluate
     return self.expr()   
File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 837, in __call__
     return self.terms(self.env)   
File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\ops.py", line 380, in __call__
     return self.func(left, right) 
TypeError: '<' not supported between instances of 'type' and 'str'

Also tried to convert the string to datetime, but the error is similar.

df.query('Timestamp < @pd.to_datetime("2020-02-01")')
Traceback (most recent call last):
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\IPython\core\interactiveshell.py", line 3326, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-5-23540526aad9>", line 1, in <module>
    df.query('Timestamp < @pd.to_datetime("2020-02-01")')
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\frame.py", line 3199, in query
    res = self.eval(expr, **kwargs)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\frame.py", line 3315, in eval
    return _eval(expr, inplace=inplace, **kwargs)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\eval.py", line 322, in eval
    parsed_expr = Expr(expr, engine=engine, parser=parser, env=env, truediv=truediv)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 830, in __init__
    self.terms = self.parse()
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 847, in parse
    return self._visitor.visit(self.expr)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 441, in visit
    return visitor(node, **kwargs)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 447, in visit_Module
    return self.visit(expr, **kwargs)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 441, in visit
    return visitor(node, **kwargs)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 450, in visit_Expr
    return self.visit(node.value, **kwargs)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 441, in visit
    return visitor(node, **kwargs)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 747, in visit_Compare
    return self.visit(binop)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 441, in visit
    return visitor(node, **kwargs)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 565, in visit_BinOp
    return self._maybe_evaluate_binop(op, op_class, left, right)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 547, in _maybe_evaluate_binop
    return self._maybe_eval(res, self.binary_ops)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\expr.py", line 519, in _maybe_eval
    self.env, self.engine, self.parser, self.term_type, eval_in_python
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\ops.py", line 399, in evaluate
    res = self(env)
  File "C:\ENERCON\Python 3.7.2\lib\site-packages\pandas\core\computation\ops.py", line 380, in __call__
    return self.func(left, right)
TypeError: '<' not supported between instances of 'type' and 'Timestamp'

If I run the equivalent function using .loc I have the desired results (but I can't use a user input string).

df.loc[df['Timestamp'] < "2020-02-01"]
Out[4]:                 
     Timestamp  Error  ...  ToD  Day_Night
0    2020-01-17 00:00:00      0  ...    0      Night  
1    2020-01-17 00:10:00      0  ...    0      Night
2    2020-01-17 00:20:00      0  ...    0      Night
3    2020-01-17 00:30:00      0  ...    0      Night 
4    2020-01-17 00:40:00      0  ...    0      Night 
2154 2020-01-31 23:10:00      0  ...   23      Night  
2155 2020-01-31 23:20:00      0  ...   23      Night 
2156 2020-01-31 23:30:00      0  ...   23      Night
2157 2020-01-31 23:40:00      0  ...   23      Night 
2158 2020-01-31 23:50:00      0  ...   23      Night
[2159 rows x 37 columns]

Does anyone knows how to use query() with datetime columns?

i think the error message gives a clue - Timestamp is a type and cant be compared to str or datetime. run a test and change the Timestamp name to something else and see if the code works. the df['Timestamp'] is allowed by pandas, that's why it works, cos it is not seen as a type but as a column. Read the warnings box for more info : pandas.pydata.org/pandas-docs/stable/user_guide/… — sammywemmy
– sammywemmy, Commented Feb 18, 2020 at 9:12
Thanks, that is the problem. After renaming the column it works. — bnando
– bnando, Commented Feb 18, 2020 at 10:47

Giorgos Myrianthous · Accepted Answer · 2020-02-18 11:50:14Z

3

The Timestamp column name shadows the built-in type timestamp. As a first step, you can rename the column to something else, using rename():

df.rename(columns={"Timestamp": "MyTimestamp"})

Then the following should do the trick for the datetime:

df.query('MyTimestamp < 20200201')

Alternatively, if you want to query the dataframe using a timestamp:

df.query('MyTimestamp < @ts("20200201T071320")'

edited Feb 18, 2020 at 11:50

answered Feb 18, 2020 at 9:14

Giorgos Myrianthous

40.4k21 gold badges156 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frank Over a year ago

Thanks for this- found the \@ts syntax doesn't work in newer versions, but \@Timetamp does.

Collectives™ on Stack Overflow

Using Pandas query() to filter dataframe on a timestamp column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related