Filtering Pandas DataFrame on last n dates

Question

I have a Pandas DF that looks like this:

df

I want to filter the DF using a locally defined int parameter, 'days'. Such as when days = 10, my filtered DF only has the data for the last available 10 dates.

Until now, I have tried the following:

days=10    
cutoff_date = df["SeriesDate"][-1:] - datetime.timedelta(days=days)

However, then trying to output the filtered DF using:

df[df['SeriesDate'] > cutoff_date] 

I get the follwing error:

ValueError: Can only compare identically-labeled Series objects

I am still learning Python so will appreciate any help that I can get with this.

Answer 1

I think you need select last value of column SeriesDate by iloc:

start = pd.to_datetime('2015-02-24')
rng = pd.date_range(start, periods=15, freq='20H')
df = pd.DataFrame({'SeriesDate': rng, 'Value_1': np.random.random(15)})  
print (df)
            SeriesDate   Value_1
0  2015-02-24 00:00:00  0.849160
1  2015-02-24 20:00:00  0.332487
2  2015-02-25 16:00:00  0.687638
3  2015-02-26 12:00:00  0.310326
4  2015-02-27 08:00:00  0.660795
5  2015-02-28 04:00:00  0.354475
6  2015-03-01 00:00:00  0.061312
7  2015-03-01 20:00:00  0.443908
8  2015-03-02 16:00:00  0.708326
9  2015-03-03 12:00:00  0.257419
10 2015-03-04 08:00:00  0.618363
11 2015-03-05 04:00:00  0.121625
12 2015-03-06 00:00:00  0.637324
13 2015-03-06 20:00:00  0.058292
14 2015-03-07 16:00:00  0.047624

days=10    
cutoff_date = df["SeriesDate"].iloc[-1] - pd.Timedelta(days=days)
print (cutoff_date)
2015-02-25 16:00:00

df1 = df[df['SeriesDate'] > cutoff_date] 
print (df1)
            SeriesDate   Value_1
3  2015-02-26 12:00:00  0.310326
4  2015-02-27 08:00:00  0.660795
5  2015-02-28 04:00:00  0.354475
6  2015-03-01 00:00:00  0.061312
7  2015-03-01 20:00:00  0.443908
8  2015-03-02 16:00:00  0.708326
9  2015-03-03 12:00:00  0.257419
10 2015-03-04 08:00:00  0.618363
11 2015-03-05 04:00:00  0.121625
12 2015-03-06 00:00:00  0.637324
13 2015-03-06 20:00:00  0.058292
14 2015-03-07 16:00:00  0.047624

Another alternative is use max, thanks Pocin:

cutoff_date = df["SeriesDate"].max() - pd.Timedelta(days=days)
print (cutoff_date)
2015-02-25 16:00:00

And if you want filter by dates only:

days=10    
cutoff_date = df["SeriesDate"].dt.date.iloc[-1] - pd.Timedelta(days=days)
print (cutoff_date)
2015-02-25

EDIT:

You can filter out dates where is weekend with dayofweek and then use isin

start = pd.to_datetime('2015-02-24')
rng = pd.date_range(start, periods=15)
df = pd.DataFrame({'SeriesDate': rng, 'Value_1': np.random.random(15)})  
print (df)
   SeriesDate   Value_1
0  2015-02-24  0.498387
1  2015-02-25  0.435767
2  2015-02-26  0.299233
3  2015-02-27  0.489286
4  2015-02-28  0.892167
5  2015-03-01  0.507436
6  2015-03-02  0.360427
7  2015-03-03  0.903886
8  2015-03-04  0.718148
9  2015-03-05  0.645489
10 2015-03-06  0.251285
11 2015-03-07  0.139275
12 2015-03-08  0.756845
13 2015-03-09  0.565863
14 2015-03-10  0.148077

days=10    
last_day = df["SeriesDate"].dt.date.iloc[-1]
cutoff_date = last_day - pd.Timedelta(days=days)
rng = pd.date_range(cutoff_date, last_day)

rng = rng[(rng.dayofweek != 0) & (rng.dayofweek != 6)]
print (rng)
DatetimeIndex(['2015-02-28', '2015-03-03', '2015-03-04', '2015-03-05',
               '2015-03-06', '2015-03-07', '2015-03-10'],
              dtype='datetime64[ns]', freq=None)

df1 = df[df['SeriesDate'].isin(rng)]
print (df1)
   SeriesDate   Value_1
4  2015-02-28  0.892167
7  2015-03-03  0.903886
8  2015-03-04  0.718148
9  2015-03-05  0.645489
10 2015-03-06  0.251285
11 2015-03-07  0.139275
14 2015-03-10  0.148077