Compare two dates in pandas dataframe with current date and create new column?

Question

I have dataframe(df) as shown below:

from datetime import date
Today = str(date.today())
df['Today_Date'] = Today

Status_Date  Today_Date  Planned_Date   
 25-11-2020  27-11-2020  25-11-2020      
 28-11-2020  27-11-2020  29-11-2020      
 26-11-2020  27-11-2020  29-11-2020      
 27-11-2020  27-11-2020  27-11-2020      
 30-11-2020  27-11-2020  29-11-2020      
 23-11-2020  27-11-2020  24-11-2020

I need compare Status_date and Planned_Date with Today_date which ideally current date and create new column as shown below:

Expected Output

Status_Date  Today_Date  Planned_Date   Status_Color
 25-11-2020  27-11-2020  25-11-2020      Red
 28-11-2020  27-11-2020  29-11-2020      Green
 26-11-2020  27-11-2020  29-11-2020      Amber
 27-11-2020  27-11-2020  27-11-2020      Amber
 30-11-2020  27-11-2020  29-11-2020      Green
 23-11-2020  27-11-2020  24-11-2020      Red

How can this be done in python?

@MayankPorwal, 1.If Status Date and Planned_Date is less than` Today_Date` then Red .2.If Status Date and Planned_Date is greater than` Today_Date` then Green. 3.Else Amber — LDF_VARUM_ELLAM_SHERIAAVUM
– LDF_VARUM_ELLAM_SHERIAAVUM, Commented Nov 27, 2020 at 4:38

Mayank Porwal · Accepted Answer · 2020-11-27 05:05:58Z

First convert all date columns to pandas datetime using pd.to_datetime, then Use numpy.select to create your new column:

In [3957]: df.Status_Date = pd.to_datetime(df.Status_Date)    
In [3958]: df.Today_Date = pd.to_datetime(df.Today_Date)    
In [3959]: df.Planned_Date = pd.to_datetime(df.Planned_Date)

In [3961]: conds = [((df.Today_Date > df.Status_Date) & (df.Today_Date > df.Planned_Date)), ((df.Today_Date < df.Status_Date) & (df.Today_Date < df.Planned_Date))]    
In [3962]: choices = ['Red', 'Green']

In [3965]: df['Status_Color'] = np.select(conds, choices, default='Amber')

In [3966]: df
Out[3966]: 
  Status_Date Today_Date Planned_Date Status_Color
0  2020-11-25 2020-11-27   2020-11-25          Red
1  2020-11-28 2020-11-27   2020-11-29        Green
2  2020-11-26 2020-11-27   2020-11-29        Amber
3  2020-11-27 2020-11-27   2020-11-27        Amber
4  2020-11-30 2020-11-27   2020-11-29        Green
5  2020-11-23 2020-11-27   2020-11-24          Red

Collectives™ on Stack Overflow

Compare two dates in pandas dataframe with current date and create new column?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related