Finding the rows where a column value drops to zero in Python DataFrame

user_11641908 0
Viewed 74

I have the following sample data-set where I need to find the rows where overdue_amount drops to zero while loan_balance column increases by the same amount per loan_id. For instance, the rows 2-> 3, 7 -> 8, 11 -> 12

report_date  customer_id    loan_id   Overdue_Amount   Loan_Balance  Flag_1   

01/01/20                1    12        125000           0              0
02/01/20                1    12        125000           0              1
03/01/20                1    12         0               125000         1
04/01/20                1    13         0               125000         0
05/01/20                1    13         0               125000         0
01/01/20                2    111        0               0              0
02/01/20                2    111        6000            0              1
03/01/20                2    111        0               6000           1
04/01/20                2    112        0               6000           0
01/01/20                3    131       165878           0              0
02/01/20                3    131       165878           0              1     
03/01/20                3    131       0               165878          1
04/01/20                3    132       9000            10000           0
05/01/20                3    132       9000            10000           0
06/01/20                3    132       9000            10000           0
07/01/20                3    132       9000            10000           0
pandas dataframe python-3.x python
1 Answers

You can solve this by calculating the differential of the columns Overdue_Amount and Loan_Balance and then selecting the rows where the difference in one column equals the negative of the difference in the other. Then you extract the row indices.

Assuming your DataFrame is called df:

diff = df[['Overdue_Amount', 'Loan_Balance']].diff()
row_idx = diff.index[(diff['Overdue_Amount'] != 0) & (diff['Overdue_Amount'] == -diff['Loan_Balance'])].values
row_idx = np.sort(np.append(row_idx, row_idx + 1))

Output:

array([ 2,  3,  7,  8, 11, 12], dtype=int64)
user_105070360
Related