Apr-13-2017, 08:29 AM
You can check for lines that have same CHQNO as following line and deposit is same as following withdrawal (first_one) and for lines that has same CHQNO as previous line and withdrawal is same as previous deposit (second_one).
This works only for deposit "followed" by withdrawal, otherwise you would need to either sort it or use more conditions of same type. Or it could be done by "SQL-joining" dataframe with itself on chqno and withdrawal==deposit followed by some cleaning/processing.
This works only for deposit "followed" by withdrawal, otherwise you would need to either sort it or use more conditions of same type. Or it could be done by "SQL-joining" dataframe with itself on chqno and withdrawal==deposit followed by some cleaning/processing.
Output:In [4]: first_one = (df.CHQNO == df.CHQNO.shift(-1)) & (df.Deposit == df.Withdrawal.shift(-1))
In [5]: second_one = (df.CHQNO == df.CHQNO.shift(1)) & (df.Deposit.shift(1) == df.Withdrawal)
In [6]: df['rtn'] = np.where(first_one | second_one, "RTN", "")
In [7]: df
Out[7]:
DATE CHQNO Deposit Withdrawal rtn
0 2016-04-19 533247.0 1975000.00 NaN RTN
1 2016-04-19 533247.0 NaN 1975000.00 RTN
2 2016-05-05 NaN 1947293.42 NaN
3 2016-05-05 NaN NaN 1947293.42
4 2016-06-03 535199.0 1930000.00 NaN RTN
5 2016-06-04 535199.0 NaN 1930000.00 RTN
6 2016-08-04 NaN 195000.00 NaN
7 2016-08-05 628490.0 5000.00 NaN RTN
8 2016-08-05 628490.0 NaN 5000.00 RTN
9 2016-12-30 750200.0 15000.00 NaN
10 2016-12-31 326500.0 NaN 15000.00 