How to query dates in pandas using pd.DataFrame.query()-CodePudding

When querying rows in a dataframe based on a datcolumn value, it works when comparing just the year, but not for a date.

fil1.query('Date>2019')

This works fine. However when giving complete date, it fails.

fil1.query('Date>01-01-2019')
#fil1.query('Date>1-1-2019')   # fails as well

TypeError: Invalid comparison between dtype=datetime64[ns] and int

What is the correct way to use dates on query function ? The docs doesnt seem to help.

CodePudding user response：

There are two errors in your code. Date default format is yyyy-mm-dd, and you should use "" for values.

fil1.query('Date>"2019-01-01"')

CodePudding user response：

Query on dates works for me

df = pd.DataFrame({'Dates': ['2022-01-01', '2022-01-02','2022-01-03','2022-01-04']})
df
Out[101]: 
        Dates
0  2022-01-01
1  2022-01-02
2  2022-01-03
3  2022-01-04
df.query('Dates>"2022-01-02"')
Out[102]: 
        Dates
2  2022-01-03
3  2022-01-04

CodePudding user response：

If you filter only the year, this is probably an integer value, so this works:

fil1.query('year > 2019')

A full date-string must be filtered with quotation marks, e.g.

fil1.query('date > "2019-01-01"')

It's a bit like in SQL, there you also cannot filter like WHERE date > 1-1-2019, you need to do WHERE date > '2019-01-01'.