Convert column to date with mixed types. "to_datetime" not working

user_14580806 0
Viewed 154

Here is my data frame:

   INV_NUM        LOCATION        CREATED_DATE     DATE_OPENED
      1             North           10/10/2020      10/11/2020
      2             North           10/11/2020      10/11/2020
      3             South           10/12/2020      10/01/2020
      4             West            10/13/2020      WIP

I want to create an output that makes a new column called "STATUS". If the "CREATED_DATE" is before "DATE_OPENED" return "Closed", if it is on or after "DATE_OPENED" return "Open", if "DATE_OPENED" is 'WIP' then return "Closed".

Here is the code I wrote to do that:

Define a function:

def date_test(row):
    if row['DATE OPENED'] == 'WIP':
        return 'Closed'
    if row['CREATED_DATE'] < row['DATE OPENED']:
        return 'Closed'
    if row['CREATED_DATE'] >= row['DATE OPENED']:
        return 'Open'
    else:
        return 0000

Change the type to datetime:

df['DATE OPENED'] = pd.to_datetime(df['DATE OPENED'], errors='ignore')
df['CREATED_DATE'] = pd.to_datetime(df['CREATED_DATE'], errors='raise')

Test the function:

df['STATUS'] = df.apply(lambda row: date_test(row), axis=1)

When I run that code here is the error I get:

TypeError: '<' not supported between instances of 'Timestamp' and 'str'

I also found that the dtype for column "DATE_OPENED" remained as 'non-null object' after running to_datetime.

I think its the 'WIP' throwing it off but it should ignore that one and continue changing the others and the function looks for the 'WIP' string first.

pandas datetime python
2 Answers

I believe the issue is in this line:

df['DATE OPENED'] = pd.to_datetime(df['DATE OPENED'], errors='ignore')

errors='ignore' appears to return the whole series in its original form and thus none of the strings in that column are converted to datetime objects. I recommend using errors='coerce' that is going to change your 'WIP' stings into NaT objects and you'll have to update your function to handle that, but something like this should work:

import pandas as pd


df = pd.DataFrame(
    dict(
        INV_NUM = [1,2,3,4],
        LOCATION = "North North South West".split(),
        CREATED_DATE = "10/10/2020 10/11/2020 10/12/2020 10/13/2020".split(),
        DATE_OPENED = "10/11/2020 10/11/2020 10/01/2020 WIP".split(),
))

def date_test(row):
    if row['DATE_OPENED'] is pd.NaT:
        return 'Closed'
    if row['CREATED_DATE'] < row['DATE_OPENED']:
        return 'Closed'
    if row['CREATED_DATE'] >= row['DATE_OPENED']:
        return 'Open'
    else:
        return 0000

df['DATE_OPENED'] = pd.to_datetime(df['DATE_OPENED'], errors='coerce')
df['CREATED_DATE'] = pd.to_datetime(df['CREATED_DATE'], errors='raise')

df['STATUS'] = df.apply(lambda row: date_test(row), axis=1)

step through the code in python tutor

Edit: If you want to keep errors='ignore' and those 'WIP' strings then this should work:

df['DATE_OPENED'] = df['DATE_OPENED'].apply(
    lambda x: pd.to_datetime(x, errors='ignore')
)

python tutor link 2

user_112285410

You can simplify the logic. Given the df, convert both columns to_datetime, leaving NaT for invalid dates ('WIP')

df['DATE_OPENED'] = pd.to_datetime(df['DATE_OPENED'], errors='coerce')
df['CREATED_DATE'] = pd.to_datetime(df['CREATED_DATE'], errors='coerce')

Now create a column STATUS with 'Closed' as default:

df['STATUS'] = 'Closed'

Since any comparison to NaT will give False, you can set 'Open' as

df.loc[df['CREATED_DATE'] >= df['DATE_OPENED'], 'STATUS'] = 'Open'

giving you

df
   INV_NUM LOCATION CREATED_DATE DATE_OPENED  STATUS
0        1    North   2020-10-10  2020-10-11  Closed
1        2    North   2020-10-11  2020-10-11  Open
2        3    South   2020-10-12  2020-10-01  Open
3        4     West   2020-10-13         NaT  Closed
user_101974180
Related