Flag the month of default - python pandas

user_15293079 0
Viewed 146

I have a pandas dataframe like this:

pd.DataFrame({
'customer_id': ['100', '200', '300', '400', '500', '600'],
'Month1': [1, 1, 1, 1, 1, 1],
'Month2': [1, 0, 1, 1, 1, 1],
'Month3': [0, 0, 0, 0, 1, 1],
'Month4': [0, 0, 0, 0, 0, 1]})

This is showing a boolean value for when a customer defaults on a loan. The first month with 0 means the customer defaulted that month. I want an output that displays the month number the customer defaulted on the loan. Output:

pd.DataFrame({
'customer_id': ['100', '200', '300', '400', '500', '600'],
'Month1': [1, 1, 1, 1, 1, 1],
'Month2': [1, 0, 1, 1, 1, 1],
'Month3': [0, 0, 0, 0, 1, 1],
'Month4': [0, 0, 0, 0, 0, 1],
'default_month': [3, 2, 3, 3, 4, np.nan]})
pandas python-3.x
2 Answers

You can check whether all the 'Month' columns in a row are not 0, using all(axis=1) and ne(0) and return np.nan which means that the person has not yet defaulted (i.e. your row 5).

Then using eq(0)and idxmax you can check which is the first value of a row that equals to 0 and grab that column name.

import numpy as np

m = df.filter(like='Month')
df['default_month'] = np.where((m.ne(0)).all(1),np.nan,
                               m.eq(0).idxmax(1))

df
 
  customer_id  Month1  Month2  Month3  Month4 default_month
0         100       1       1       0       0        Month3
1         200       1       0       0       0        Month2
2         300       1       1       0       0        Month3
3         400       1       1       0       0        Month3
4         500       1       1       1       0        Month4
5         600       1       1       1       1           NaN
user_91673820

Here's some code to get your result:

first we need a list of months in reverse order. From your data, I just pulled them directly from the index.

months = list(df.columns[1:5])
months.reverse()

months is now ['Month4', 'Month3', 'Month2', 'Month1'].

We iterate backwards so when we find an earlier month of default, it overwrites

for (i,m) in enumerate(months):
    mask = df[m] == 0 # Check for a default

    df.loc[mask,'default_month'] = len(months) - i

This returns the output you are looking for.

user_22580130
Related