Dynamic Filtering a Dataframe based date in Pandas with condition

Question

I am trying to filter a dataframe based on date field.

 Date    Value
201810   100
201811   150
201812   95
201901   125
201902   150
201903   200
201904   225

The filtering is to be done dynamically. E.g. the first date and end date should not be 'hard coded'. So my approach is as follows:

month = pd.DataFrame(set(df['Date']),columns=['Date'])
df['Date'] = pd.to_datetime(df['Date'],format='%Y%m)

From here, I have to filter based on date and create a new dataframe. In this the last date, say 201903.

dt_first = month['Date'].head(1)   <---first date is being dynamically created
dt_last = month.iloc[-2]           <-- last date, dynamically created. 
df_filter = df[(df.Date.ge(dt_first))&(df.Date.le(dt_last))]

But the last line is generating a blank dataframe. The resultant dataframe should look like

 Date    Value
 201810   100
 201811   150
 201812   95
 201901   125
 201902   150
 201903   200

I know I am missing out something.

Can anybody please suggest how to effectively filter the above dataframe based on condition?

Answer 1

Here is necessary compare by scalar, so is extracted first and last value of column by Series.iat:

Btw, if compare by ge for greater or equal in sorted values with first val it match all data, so this condition always return Trues, so should be removed with same output:

df['Date'] = pd.to_datetime(df['Date'],format='%Y%m')

df_filter = df[(df.Date.ge(df['Date'].iat[0]))&(df.Date.lt(df['Date'].iat[-1]))]

Same like:

df_filter = df[(df.Date.lt(df['Date'].iat[-1]))]

print (df)
        Date  Value
0 2018-10-01    100
1 2018-11-01    150
2 2018-12-01     95
3 2019-01-01    125
4 2019-02-01    150
5 2019-03-01    200
6 2019-04-01    225