Retrieve past n records of panda dataframe based on column value

Question

I have a panda dataframe df below;

         pay        pay2        date
0  209.070007  208.000000   2018-08-06
1  207.110001  209.320007   2018-08-07
2  207.250000  206.050003   2018-08-08
3  208.880005  207.279999   2018-08-09

Given a last_date, I want to retrieve the past n rows from last_date onwards, including the row of last_date itself.

For instance, given last_date = '2018-08-08' and n=3. The result dataframe should look like this;

         pay        pay2        date
0  209.070007  208.000000   2018-08-06
1  207.110001  209.320007   2018-08-07
2  207.250000  206.050003   2018-08-08

just filter it with last date and sort in ascending order on date column — Sociopath, Aug 10 '18 at 08:12

score 2 · Accepted Answer · answered Aug 10 '18 at 08:14

Use

In [285]: s = df.date.eq(last_date)

In [286]: loc = s.index[s][-1]    # or s.idxmax()

In [287]: df.loc[loc-3:loc]
Out[287]:
          pay        pay2        date
0  209.070007  208.000000  2018-08-06
1  207.110001  209.320007  2018-08-07
2  207.250000  206.050003  2018-08-08

Details

In [288]: s
Out[288]:
0    False
1    False
2     True
3    False
Name: date, dtype: bool

In [289]: loc
Out[289]: 2

score 2 · Answer 2 · answered Aug 10 '18 at 08:38

I would do this shorter. First convert string to datetime.

last_date = '2018-08-08'
last_date = pd.to_datetime(last_date)

n = 2
df.loc[(df['date'] <= last_date)].loc[:n-1]

#           pay        pay2       date
# 0  209.070007  208.000000 2018-08-06
# 1  207.110001  209.320007 2018-08-07

score 1 · Answer 3 · answered Aug 10 '18 at 08:23

You Need:

df = pd.DataFrame({'pay':['209.07','207.110001','207.250000','208.880005'],
                   'pay2':['208','209.320007','206.050003','207.279999'],
                    'date':['2018-08-06','2018-08-07','2018-08-08','2018-08-09']})

last_date = pd.to_datetime('2018-08-08')
n= 3

df['date'] =pd.to_datetime(df['date'])
df_new = df[df['date']<=last_date].sort_values("date", ascending=False)

df_new = df_new[:n].sort_values("date", ascending=True)

print(df_new)

Output:

          pay        pay2       date
0      209.07         208 2018-08-06
1  207.110001  209.320007 2018-08-07
2  207.250000  206.050003 2018-08-08

Retrieve past n records of panda dataframe based on column value

3 Answers3