CODEWITHSUNDEEP
python-3.xpandasdataframedatetime
Rahul rajan
Rahul rajan

Reputation: 1266

Logical comparison with date column not working in Pandas dataframe

I have a dataframe like this:

Milestone   Initial_Date    Next_Date   Buffer  Buffer1
-------------------------------------------------------
M0          11/1/2020       13/1/2020   6       1
M1          13/1/2020       15/1/2020   3       1
M0          24/12/2019      25/12/2019  4       2
M1          16/12/2019      21/12/2019  9       2
M0          8/1/2020        14/1/2020   10      1
M2          6/1/2020        9/1/2020    5       2
M3         18/1/2020       21/1/2020    3       4

and I am applying below logic to the dataframe

CASE
   WHEN milestone = 'M0' THEN Intial_date + Buffer
   WHEN milestone = 'M1' THEN Next_datee + Buffer
   WHEN milestone >= 'M2' THEN Intial_date + Buffer1
   ELSE NULL
END AS Result

Expected output:

Result
------------
17/1/2020
18/1/2020
28/12/2019
30/12/2019
18/1/2020
8/1/2020
22/1/2020

My code:

#datatypes of Date fields are datetime64[ns] and Buffer is float64

   data['Milestone'] = pd.Categorical(data['Milestone'],categories=['00','M0','M1','M2','M3','M4','M5','M6','M7'],ordered=True)
    buffer = pd.to_timedelta(final_result['Buffer'], unit='d')
    buffer1 = pd.to_timedelta(final_result['Buffer1'], unit='d')
    data['Result'] =np.select([data['Milestone']=='M0',data['Milestone']=='M1',
                                            data[MILESTONE']>='M2']
                                ,[data['Initial_Date']+Buffer,data['Next_Date']+Buffer,
                                  data['Initial_Date']+Buffer1)

I am getting an error

TypeError: invalid type promotion

from the above code. Can you help me fix this?

Upvotes: 1

Views: 102

Answers (1)

jezrael
jezrael

Reputation: 863351

First is necessary add default parameter to None or NaT and then convert output to datetimes:

data['Result'] =pd.to_datetime(np.select([data['Milestone']=='M0',
                               data['Milestone']=='M1',
                               data['Milestone']>='M2'],
                              [data['Initial_Date']+buffer,
                               data['Next_Date']+buffer,
                               data['Initial_Date']+buffer1],
                               default=None))

print (data)
  Milestone Initial_Date  Next_Date  Buffer  Buffer1     Result
0        M0   2020-01-11 2020-01-13       6        1 2020-01-17
1        M1   2020-01-13 2020-01-15       3        1 2020-01-18
2        M0   2019-12-24 2019-12-25       4        2 2019-12-28
3        M1   2019-12-16 2019-12-21       9        2 2019-12-30
4        M0   2020-01-08 2020-01-14      10        1 2020-01-18
5        M2   2020-01-06 2020-01-09       5        2 2020-01-08
6        M3   2020-01-18 2020-01-21       3        4 2020-01-22

Upvotes: 2

Related Questions