How to make a conditional slice of a numeric string and replace it with another numeric string value?

Question

My date field CRT_DT has dates coded as follows 1190314 which represents the following date March 14th 2019, also 03/14/2019 990201 which represents the the following date February 1st 1999, also 02/01/1999

I would like to make a field that normalizes this date field to regular dates, so for the above it would have 03/14/2019 and 02/01/1999. The rule is that if it starts with a "1" then replace it with a "20" and convert it to date type, if it begins with "9" then add a "19".

df['CRT_DT_Fix'] = 
np.where(df['CRT_DT'].str.slice(stop=1)='1','20'+df['CRT_DT'].str.slice(start=2),'19'+df['CRT_DT'].str.slice(start=2))

can you provide a mvp? need to see some example data and sample output. — Umar.H
– Umar.H, Commented Oct 3, 2019 at 18:52
@Datanovice What Code Different wrote is a good example in terms of teh dataframe and the desired output, the data set has many more dates though, not just the two given in the example. — Shmelky
– Shmelky, Commented Oct 3, 2019 at 19:27
It should work on any number of days. If it doesn't, please include the inputs here so I can take a look — Code Different
– Code Different, Commented Oct 3, 2019 at 19:48

Code Different · Accepted Answer · 2019-10-03 19:20:54Z

1

Use regex for the replacement:

df = pd.DataFrame({
    'CRT_DT': ['1190314', '9990201']
})
s = df['CRT_DT'].str.replace('^1', '20') \
        .str.replace('^9', '19')
df['Date'] = pd.to_datetime(s, format='%Y%m%d')

Result:

    CRT_DT       Date
0  1190314 2019-03-14
1  9990201 1999-02-01

answered Oct 3, 2019 at 19:20

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nev1111 · Accepted Answer · 2020-01-18 20:00:03Z

0

If you're still looking for a solution, this also works:

import datetime
from datetime import datetime
string='990201'
day=string[-2:]
month=string[-4:][:2]
year=int(string[:-4])+1900

new_date_str=month+day+str(year)
new_date=datetime.strptime(new_date_str, '%m%d%Y')

answered Jan 18, 2020 at 20:00

Nev1111

1,0499 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to make a conditional slice of a numeric string and replace it with another numeric string value?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related