Identify amount of due date between subsequent rows

Question

I have a table that group by ID and sorted transaction date as shown below.

id     transactions_date    membership_expire_date
1      2016-11-16           2016-12-16
1      2016-12-15           2017-01-14
1      2017-01-15           2017-02-14
1      2017-02-15           2017-03-17
2      2015-01-31           2015-03-03
2      2015-02-28           2015-03-31
2      2015-04-05           2015-05-01

I want calculate if the users were late on the due date. For example, on userid 1, on the second row's transactions_date, user performed payment before the membership_expire_date stated on 1st row(within, equal to or 1 day after membership_expire_date are considered as punctual), therefore the amount of due = 0. However, for userid 2 last row, the user paid on 2015-04-05. Therefore, 2015-04-05 - 2015-03-31 - 1 days(one day after membership_expire_date is fine) = 4 days due.

How should I compute them? I am stuck after sorted them this way.

transactions_train = transactions_train.sort_values(by=['id','transaction_date', 'membership_expire_date'], ascending=True)

The expected result is something like below.

id     transactions_date    membership_expire_date     late_count
1      2016-11-16           2016-12-16                     0
1      2016-12-15           2017-01-14                     0
1      2017-01-15           2017-02-14                     0
1      2017-02-16           2017-03-17                     1
2      2015-01-31           2015-03-03                     0
2      2015-02-28           2015-03-31                     0
2      2015-04-05           2015-05-01                     4

look into `shift` function as explained [here](https://stackoverflow.com/questions/23664877/pandas-equivalent-of-oracle-lead-lag-function) — shanmuga, Nov 23 '17 at 10:27

score 0 · Accepted Answer · answered Nov 23 '17 at 10:45

You need to look at shift, indeed.

def days_due(group):
    print('-', group)
    day = pd.Timedelta('1d')
    days_late = ((group['transactions_date'] - group['membership_expire_date'].shift()) / day - 1)
    days_late = days_late.where(days_late > 0)
    return days_late.fillna(0).astype(int)

df['late_count'] = pd.concat(days_due(group) for idx, group in df.groupby('id'))

  id  transactions_date   membership_expire_date  late_count
0     1   2016-11-16  2016-12-16  0
1     1   2016-12-15  2017-01-14  0
2     1   2017-01-15  2017-02-14  0
3     1   2017-02-16  2017-03-17  1
4     2   2015-01-31  2015-03-03  0
5     2   2015-02-28  2015-03-31  0
6     2   2015-04-05  2015-05-01  4

Identify amount of due date between subsequent rows

1 Answers1