Question

Pandas DataFrame in Python :

I have csv file which has date column with object data type which ranges from 1908 to 2018:

(Original) Date                 (My result) Date                  (I Need) Date                      

17-Sep-08                                  2008-09-17                 1908-09-17

7-Sep-09                                    2009-09-07                  1909-09-07

.    (more years)                         .   (more years)               .    

.                                                 .                                      .

8-Nov-07                                       2007-11-07                 2007-11-07

23-Sep-08                                     2008-09-23                 2008-09-23

29-Dec-18                                     2018-12-29                 2018-12-29

When I am converting it to datetime64[ns] or/and adding column as year after extracting just year values from date column, all the year values starts at 2008 and ends at 2018 instead of starting at 1908 and ending at 2018.

Can anyone answer the question

Answer 1

This is a case of ambiguity that arises when representation of years is done in two digits.

In [16]: Out[16]: datetime.date (2068, 7, 31) In [17]: out[17]: datetime.date(1969, 7, 31) datetine . datetine. strptime('31-Jul-68 ','%d-%b-Xy').date() : datetine . datetine . strptime('31-Jul-69", '%d-XbXy'),date() :

The pivot year is 1969, i.e anything with the %y year below 69 is mapped to 2000 and anything equal to or above 69 maps to 1900. This is because we are considering two centuries simultaneously by representing them with only two digits. (00-99)

It has been illustrated in the python documentation for time library: Year 2000 (Y2K) issues: Python depends on the platform’s C library, which generally doesn’t have year 2000 issues, since all dates and times are represented internally as seconds since the epoch. Function strptime() can parse 2-digit years when given %y format code. When 2-digit years are parsed, they are converted according to the POSIX and ISO C standards: values 69–99 are mapped to 1969–1999, and values 0–68 are mapped to 2000–2068.

Manual processing is preferred. The years from 1908 to 1968 can be separately processed to remove this ambiguity.

In [43]: pivotyear = 1969 ...: century int(str(pivotyear)[:2]) 100 : def convert(year) ...converted year- century year if converted year > pivotyear: return converted vear else: return converted year 100 : year 20 #input the year : converted-year 1900 + year #if year required is < 1969 #comment these two lines if year>1969 ...: print(converted year) #convert (year) #if year required is > 1969): : 1920 In [44]: convert (year) Out [44]: 2020 #if year required IS > 1969)