0
$\begingroup$

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
$\endgroup$
0
$\begingroup$

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
$\endgroup$
  • $\begingroup$ Thanks @Arun Augustine. The iteration works perfectly. $\endgroup$ – Learning Aug 27 at 14:09
  • 1
    $\begingroup$ you are welcome, also check the below np.select method its seems perfect. $\endgroup$ – Arun Augustine Aug 28 at 1:42
1
$\begingroup$

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
$\endgroup$
  • $\begingroup$ Thanks for the feedback @Shaido. simple and easy to understand. $\endgroup$ – Learning Aug 28 at 20:26
  • $\begingroup$ @Learning: No problems, hope it helped. :) $\endgroup$ – Shaido - Reinstate Monica Aug 29 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.