Conditional Statement to update columns based on range

Question

I have a dataframe called clients that has 5000+ rows with the following:

ID   Ordered   Days
1     101565    131
2     202546    122
3     459863    78
4     328453    327
5     458975    -27

I'm trying to create a loop that looks at the numbers of days and replace them with a new columns if it does meet the days critetaria based on the following:

Days           NEW_COLUMNS
0-119          0-3 Months
120-209        4-6 Months
210-299        7-9 Months
300+           10+ Months
-280 to -196   Reach out clients
-195 to -104   Send promotion
-103 to -1     Close case
< -280         Plan

I have the following code but hasn't worked so far:

if(days <-280)(NEW_COLUMNS ="plan")
else if (days>-280 && days <-196)(NEW_COLUMNS=" Reach out clients";)
else if (days>-195 && days<-104)(NEW_COLUMNS =" Send promotion";)
else if (days>-103 && days <-1)(NEW_COLUMNS="Close case";)
else if(days> 0 && days <119)(NEW_COLUMNS="0-3 Months";)
else if(days > 120 && days <209)(NEW_COLUMNS="4-6 Mos";)
else if(days > 210 && day s<299)(NEW_COLUMNS="7-9 Mos";)
else if(days > 300)(NEW_COLUMNS="10+ Mos";)

Eventually I want a table that looks like this:

ID   Ordered   Days    New_Columns
1     101565    131     4-6 Months 
2     202546    122     4-6 Months
3     459863    78      0-3 Months 
4     328453    327     10+ Months
5     458975    -27     Close case

Answer 1

Check out the below code:

from pandas import DataFrame
Cars = {'ID': [1, 2, 3, 4, 5],
    'Ordered': [101565,202546,459863,328453,458975],
    'Days': [131, 122, 78, 327, -27]
    }

df = DataFrame(Cars, columns=['ID', 'Ordered', 'Days'])

if "New_Columns" not in df:
    df["New_Columns"] = ""

for index, row in df.iterrows():
    days = row['Days']
    val = ''
    if days < -280:
        val = 'Plan'
    elif -280 < days < -196:
        val = 'Reach out clients'
    elif -195 < days < -104:
        val = 'Send promotion'
    elif -103 < days < -1:
        val = 'Close case'
    elif 0 < days < 119:
        val = '0-3 Months'
    elif 120 < days < 209:
        val = '4-6 Months'
    elif 210 < days < 299:
        val = '7-9 Months'
    elif days > 300:
        val = '10+ Months'

    df.at[index, 'New_Columns'] = val

print(df)

Output :

    ID  Ordered  Days New_Columns
0   1   101565   131  4-6 Months
1   2   202546   122  4-6 Months
2   3   459863    78  0-3 Months
3   4   328453   327  10+ Months
4   5   458975   -27  Close case

Answer 2

One elegant way to solve this is by using numpy.select. This function takes a list of conditions and a list of choices and then pick the choice where the first condition is true.

An advantage is that since the conditions are checked in order, only one side of the condition for the day value needs to be checked.

Assuming the input dataframe is called df:

import numpy as np
import pandas as pd

conditions = [
    df['Days'] < -280,
    df['Days'] < -195,
    df['Days'] < -103,
    df['Days'] < 0,
    df['Days'] < 120,
    df['Days'] < 210,
    df['Days'] < 300,
    True
]

outputs = [
    "Plan", "Reach out clients", "Send promotion", "Close case",
    "0-3 Months", "4-6 Months", "7-9 Months", "10+ Months"
]

df['New_Columns'] = np.select(conditions, outputs)

Result:

    ID  Ordered  Days New_Columns
0   1   101565   131  4-6 Months
1   2   202546   122  4-6 Months
2   3   459863    78  0-3 Months
3   4   328453   327  10+ Months
4   5   458975   -27  Close case