Pandas - take N last values ​​from a group

Question

I have a dataframe that looks something like this (date in format:) dd/mm/yyyy:

      Param1 Param2    date       value
1       a      b    30/10/2007      5
2       a      b    31/10/2007      8
3       a      b    01/11/2007      9
4       a      b    01/12/2007      3
5       a      b    02/12/2007      2
6       a      b    01/03/2008      11
7       b      c    05/10/2008      7
8       b      c    06/10/2008      13
9       b      c    07/10/2008      19
10      b      c    08/11/2008      22
11      b      c    09/11/2008      35
12      b      c    08/12/2008      5

what I need to do is group by Param1and Param2create N (in my case, 3) additional columns for the 3 last previous values ​​that are at least 30 days from the current row. The result should look something like this:

      Param1 Param2    date       value  prev_1 prev_2 prev_3
1       a      b    30/10/2007      5     None   None   None
2       a      b    31/10/2007      8     None   None   None
3       a      b    01/11/2007      9     None   None   None
4       a      b    01/12/2007      3      9      8      5
5       a      b    02/12/2007      2      9      8      5
6       a      b    01/03/2008      11     2      3      9
7       b      c    05/10/2008      7     None   None   None
8       b      c    06/10/2008      13    None   None   None
9       b      c    07/10/2008      19    None   None   None
10      b      c    08/11/2008      22      19    13     7
11      b      c    09/11/2008      35      19    13     7 
12      b      c    08/12/2008      5       22    19    13 

I tried to use set_index, stackand related functions, but I just could not understand it (not ugly for).

Any help would be appreciated!

EDIT: while it looks like this question: question

This is not exactly the same, because you cannot do simple shiftbecause you need to check the status of the interval for at least 30 days.

Answer 1

:

data.date = pd.to_datetime(data.date, dayfirst=True)
data['ind'] = data.index

def func(a):
    aa = data[(data.ind<a.ind)\
        &(data.Param1==a.Param1)&(data.Param2==a.Param2)&(data.date<=(a.date-np.timedelta64(30, 'D')))]
    aaa = [np.nan]*3+list(aa.value.values)
    aaaa = pd.Series(aaa[::-1][:3], index=['prev_1', 'prev_2', 'prev_3'])
    return pd.concat([a, aaaa])

data.apply(func, 1).drop('ind',1)