How to filter a Pandas dataframe by timestamp functon using .query()

Question

I am trying to filter a Pandas df by dates (today and yesterday). For automation purposes I wish to filter using a timestamp function. This is pretty seamless in R:

df %>% 
  filter(date >= today() - 1)

However, my attempts to replicate in Pandas are not reaching any success so far: Yesterday comes out fine, but .query() doesnt recognise it?

yesterday = (date.today() - timedelta(days=6)).strftime('%Y-%m-%d')
df.\
   query('date >= yesterday')

Ideally I am seeking something all encompassing like:

df.\
   query('date >= (date.today() - timedelta(days=6)).strftime('%Y-%m-%d')')

Why are you using a timedelta(days=6) (six days ago) in Python and comparing against grabbing yesterday (one day ago) in R? — boot-scootin
– boot-scootin, Commented Apr 1, 2020 at 22:17
Sorry, that's confusing and a typo. So for test purposes the data set I built meant the date variable ended 6 days ago. Sorry it seems a bit confusing. — Robert Chestnutt
– Robert Chestnutt, Commented Apr 2, 2020 at 8:49

Ugurite · Accepted Answer · 2020-04-01 22:37:48Z

4

Try: df.query('date >= @yesterday'). You need @ so pandas recognizes it's a variable.

edited Apr 1, 2020 at 22:37

answered Apr 1, 2020 at 22:26

Ugurite

5531 gold badge5 silver badges12 bronze badges

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

Comments

Umar.H · Accepted Answer · 2020-04-01 22:27:22Z

3

IIUC, you want to create an outside varible to use inside your query?

from the docs

You can refer to variables in the environment by prefixing them with an ‘@’ character like @a + b.

using pandas only

import pandas as pd

df = pd.DataFrame({'date' : pd.date_range('01-02-2020','01-03-2021',freq='D')})
df = df.set_index('date')

delta = (pd.Timestamp('today') - pd.DateOffset(days=1)).strftime('%d-%m-%y')

df.query(f"date <= @delta")

  date
  2020-01-02
  2020-01-03
  2020-01-04
  2020-01-05
  2020-01-06

answered Apr 1, 2020 at 22:27

Umar.H

23.1k8 gold badges50 silver badges94 bronze badges

Comments

Ben.T · Accepted Answer · 2020-04-01 22:27:10Z

2

you can do it with string formatting:

df.query(f'date>= "{pd.Timestamp.today() - pd.Timedelta(days=6)}"')

Note: I tried with pd.Timestamp and pd.Timedelta but I'm sure it will work with date and timedelta as you used

answered Apr 1, 2020 at 22:27

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

Collectives™ on Stack Overflow

How to filter a Pandas dataframe by timestamp functon using .query()

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related