Pandas assign value to nearest IF block in else clause

Question

I have a pandas dataframe that looks like below

customer_id   recency frequency H_cnt  Years_with_us 
   1           0         143      3       0.32    
   2           14        190      8       1.7

I would like to do the below

a) If any of my row is not matched using a IF clause, I want that specific row to be assign to a nearby if clause based on H_cnt

For ex: Nearby if clause is found using H_cnt. If you look at row 1, we can find that H_cnt will not fall into any of the if clauses that I have written.

So, now in else block I would like to write a condition that can assign it to the nearest if block. In this case, nearest if block will be short tenure - promising Because H_cnt = 3 is closer/nearby to H_cnt = 4 (instead of H_cnt >= 9) as shown in 1st elif statement in code below

Currently my code looks like below

cust = 'customer_id'
for row in df.iterrows():
    rec = row[1] 
    r = rec['recency'] 
    f = rec['frequency'] 
    y = rec['years_with_us'] 
    h = rec['H_cnt']
    if ((r <= 11) and (f >=131) and (y >= 0.6) and (h >= 9)):
        classes_append({rec[cust]:'Champions'})
    elif ((r <= 11) and (f >=19) and (y < 0.6) and (h >= 4)):
        classes_append({rec[cust]:'Short Tenure - Promising'})
    elif ((r <= 62) and (f >=19) and (y >= 1.5)):
        classes_append({rec[cust]:'Loyal Customers'})
    elif ((r <= 62) and (f >=19) and (y >= 0.6) and (y < 1.5)):
        classes_append({rec[cust]:'Potential Loyalist'})
    elif ((r <= 62) and (f <=18) and (y >= 0.6)):
    classes_append({rec[cust]:'New Customers'})
    else:
        print("hi")
        print(row[1])
        classes_append({0:[row[1]['recency'],row[1]['frequency'],row[1]['H_cnt'],row[1]['years_with_IFX']]})
    accs = [list(i.keys())[0] for i in classes]
    segments = [list(i.values())[0] for i in classes]
    df['new_segment'] = df[cust].map(dict(zip(accs,segments)))

I expect my output to be like as below

customer_id   recency frequency H_cnt  Years_with_us  new_segment
   1           0         143      3       0.32        Short Tenure - Promising
   2           14        190      8       1.7         Champions

Answer 1

You can do it this way:

import pandas as pd

data = {'customer_id': [1, 2],
        'recency': [0, 14],
        'frequency': [143, 190],
        'H_cnt': [3, 8],
        'Years_with_us': [0.32, 1.7]}

df = pd.DataFrame(data)

print(df)


cust = 'customer_id'

classes = []

for row in df.iterrows():
    rec = row[1] 
    r = rec['recency'] 
    f = rec['frequency'] 
    y = rec['Years_with_us'] 
    h = rec['H_cnt']
    if ((r <= 11) and (f >= 131) and (y >= 0.6) and (h >= 9)):
        classes.append({rec[cust]:'Champions'})
    elif ((r <= 11) and (f >= 19) and (y < 0.6) and (h >= 4)):
        classes.append({rec[cust]:'Short Tenure - Promising'})
    elif ((r <= 62) and (f >= 19) and (y >= 1.5)):
        classes.append({rec[cust]:'Loyal Customers'})
    elif ((r <= 62) and (f >= 19) and (y >= 0.6) and (y < 1.5)):
        classes.append({rec[cust]:'Potential Loyalist'})
    elif ((r <= 62) and (f <= 18) and (y >= 0.6)):
        classes.append({rec[cust]:'New Customers'})
    else:
        if h < 4:
            classes.append({rec[cust]: 'Short Tenure - Promising'})
        elif h < 9:
            classes.append({rec[cust]: 'Potential Loyalist'})
        else:
            classes.append({rec[cust]: 'Champions'})

accs = [list(i.keys())[0] for i in classes]
segments = [list(i.values())[0] for i in classes]
df['new_segment'] = df[cust].map(dict(zip(accs,segments)))

print(df)

which gives you

   customer_id  recency  frequency  H_cnt  Years_with_us  \
0            1        0        143      3           0.32   
1            2       14        190      8           1.70   

                new_segment  
0  Short Tenure - Promising  
1           Loyal Customers