CODEWITHSUNDEEP
pythonpandasdataframedatetime
LDF_VARUM_ELLAM_SHERIAAVUM
LDF_VARUM_ELLAM_SHERIAAVUM

Reputation: 103

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

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?

Upvotes: 0

Views: 2106

Answers (1)

Mayank Porwal
Mayank Porwal

Reputation: 34086

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

Upvotes: 3

Related Questions