Wsrtjtyk

I'm having a nightmare converting a series of strings to dates. This is a sample of my data:

net_due_date                from_date   clearing_date

0   2018-10-25 00:00:00.000 2017-06-06  2018-10-13 00:00:00.000

1   2018-09-27 00:00:00.000 2017-06-06  2018-09-30 00:00:00.000

2   2018-05-31 00:00:00.000 2017-06-06  2018-05-18 00:00:00.000

3   2017-12-22 00:00:00.000 2017-06-06  2017-12-08 00:00:00.000

4   2018-01-30 00:00:00.000 2017-06-06  2018-01-16 00:00:00.000

5   2018-07-31 00:00:00.000 2017-06-06  2018-07-31 00:00:00.000

6   2018-05-29 00:00:00.000 2017-06-06  2018-05-17 00:00:00.000

7   2017-12-14 00:00:00.000 2017-06-06  2017-12-08 00:00:00.000

8   2017-11-24 00:00:00.000 2017-06-06  2017-12-08 00:00:00.000

9   2018-09-27 00:00:00.000 2017-06-06  2018-09-13 00:00:00.000

10  2018-01-25 00:00:00.000 2017-06-06  2018-01-16 00:00:00.000

11  2017-11-24 00:00:00.000 2017-06-06  2017-11-30 00:00:00.000

12  2018-10-24 00:00:00.000 2018-01-09  2018-10-11 00:00:00.000

13  2018-01-22 00:00:00.000 2018-01-09  2018-10-10 00:00:00.000

14  2018-09-06 00:00:00.000 2018-01-09  2018-10-10 00:00:00.000

15  2018-10-24 00:00:00.000 2018-01-09  2018-10-10 00:00:00.000

16  2018-06-15 00:00:00.000 2018-01-09  2018-10-10 00:00:00.000

17  2018-04-10 00:00:00.000 2018-01-09  2018-10-10 00:00:00.000

18  2018-01-12 00:00:00.000 2018-01-09  2018-10-10 00:00:00.000

19  2018-01-24 00:00:00.000 2018-01-09  2018-10-10 00:00:00.000

These are the dtypes:

net_due_date     object

from_date        object

clearing_date    object

dtype: object

I'm trying to convert all of this data to the same format so I can run functions on it to calculate variance between dates.

The following code works fine on net_due_date:

df['net_due_date'] = pd.to_datetime(df['net_due_date'], format='%Y-%m-%d')

And a similar code works fine on from_date:

df['from_date'] = pd.to_datetime(df['from_date'], format='%Y-%m-%d')

However, clearing_date has some values in there that are 9999-12-31 and when I run the same code, I get the following error:

OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 9999-12-31 00:00:00

I have literally spent hours trying to fix this, the closest I have got being this link:
Time Series, OOB Timestamps

But then I'm confronted with the following error:

TypeError: unsupported operand type(s) for //: 'str' and 'int'

To get around this, I have tried converting the column to an int first using to_numeric, but get another series of errors. I'm hoping someone has been in this problem before and can help because I cannot find anything online to fix this!

You can use the errors argument of the same method:

errors : {‘ignore’, ‘raise’, ‘coerce’}, default ‘raise’

• If ‘raise’, then invalid parsing will raise an exception




• If ‘coerce’, then invalid parsing will be set as NaT




• If ‘ignore’, then invalid parsing will return the input

This one line should do the trick for you:

df = df.apply(lambda x: pd.to_datetime(x, errors='coerce'))



# results:

#    net_due_date  from_date  clearing_date

# ...

# 10   2018-01-25 2017-06-06     2018-01-16

# 11   2017-11-24 2017-06-06            NaT

# 12   2018-10-24 2018-01-09     2018-10-11

# ...

If necessary you can massage the DataFrame afterwards for the NaT values and transform to something meaningful to you, e.g.:

df.fillna(pd.datetime.now().date(), inplace=True)



#    net_due_date  from_date  clearing_date    

# 10   2018-01-25 2017-06-06     2018-01-16

# 11   2017-11-24 2017-06-06     2018-11-23 # <-- changed to today

# 12   2018-10-24 2018-01-09     2018-10-11

And just to confirm the dtypes:

net_due_date      datetime64[ns]

 from_date        datetime64[ns]

 clearing_date    datetime64[ns]

dtype: object

I assume that in your case all columns are of String type and contain dates
+ sometime time part. So the simplest choice is:

df = df.applymap(pd.to_datetime)

Run the below script converting a part of your source data:

import pandas as pd



dd = { 'net_due_date': [ '2018-10-25 00:00:00.000', '2018-09-27 00:00:00.000',

        '2018-05-31 00:00:00.000', '2017-12-22 00:00:00.000',

        '2018-01-30 00:00:00.000' ],

    'from_date': [ '2017-06-06', '2017-06-06', '2017-06-06', '2017-06-06', '2017-06-06' ],

    'clearing_date': [ '2018-10-13 00:00:00.000', '2018-09-30 00:00:00.000',

        '2018-05-18 00:00:00.000', '2017-12-08 00:00:00.000', '2018-01-16 00:00:00.000' ] }

df = pd.DataFrame(data=dd)

df = df.applymap(pd.to_datetime)

When you execute df.info(), you will get (a part of the printout):

Data columns (total 3 columns):

net_due_date     5 non-null datetime64[ns]

from_date        5 non-null datetime64[ns]

clearing_date    5 non-null datetime64[ns]

For demonstration purpose, you may add print(df) both before and after
the conversion.

As far as "very big" years are concerned, Pandas converts dates with
year in the range between 1677 and 2262. So maybe as the first step
you should change such out of range dates to e.g. 2250.