Input Data:
JavaScript
x
8
1
Period Invoice_No Amount Date2
Mar-19 123 5000.00 12-02-20193
Mar-19 124 5000.00 13-02-20194
Mar-19 125 7000.00 13-02-20195
Mar-19 126 5000.00 19-02-20196
Mar-19 127 5000.00 25-02-20197
Mar-19 128 5000.00 27-02-20198
So basically want to check if first row amount (5000.00) is equal to second row amount; then perform a date difference function (13-02-2019 “-” 12-02-2019) and if difference is less than “5 days” then the following is the output. If difference is more than 5 days exclude from the output (Line number 4)
JavaScript
1
6
1
Period Invoice_No Amount Date2
Mar-19 123 5000.00 12-02-20193
Mar-19 124 5000.00 13-02-20194
Mar-19 127 5000.00 25-02-20195
Mar-19 128 5000.00 27-02-20196
Advertisement
Answer
You can shift the date to calculate the time delta between the rows. After I filter for the conditions you defined.
JavaScript
1
12
12
1
df['Date_diff'] = df['Date'].shift(-1)2
df['Date_diff'] = df.apply(lambda x: pd.Timedelta(x['Date_diff'] - x['Date']),axis=1)3
4
mask = (df['Amount'] == df['Amount'].shift(-1)) & (df['Date_diff'] < pd.Timedelta('5 days'))5
mask2 = (df['Amount'] == df['Amount'].shift(1)) & (df['Date_diff'].shift(1) < pd.Timedelta('5 days'))6
7
print(df[mask | mask2].drop('Date_diff', axis=1))8
9
Period Invoice_No Amount Date10
0 Feb-19 123 5000.0 2019-02-1211
1 Feb-19 123 5000.0 2019-02-1312