How To Filter A Pandas Dataframe By Timestamp Functon Using .query()

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 %>% fi

Solution 1:

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

Solution 2:

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

Solution 3:

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