Splitting strings with dates of similar cells

user_10325234 0
Viewed 20

I have a dataframe which contains a date column. But the date column is written as second quarter of 2017. I want to change this to 4-1-2017. The problem is the string of the date isn't structured the same for each cell. so what i use now is:

data = [{'date' : '2e kw 2017'},{'date' : '3ekw 2017'},{'date' : '4e kw 2017'} ]

df = pd.DataFrame(data, columns =['date']) 

df[['kwartaal','jaar']] = df.date.str.split(" kw ",expand=True) 

df['kwartaal'] = df['kwartaal'].replace(['2e'],'4-1-')
df['kwartaal'] = df['kwartaal'].replace(['3e'],'7-1-')
df['kwartaal'] = df['kwartaal'].replace(['4e'],'10-1-')

df['date'] = pd.to_datetime(df['kwartaal'] + df['jaar'])

which gives me the desired result for the second and fourth quarter, but not for the third. What is the best way to split this?

        date   kwartaal  jaar
0 2017-04-01       4-1-  2017
1        NaT  3ekw 2017  None
2 2017-10-01      10-1-  2017
pandas dataframe date python
1 Answers

Let's try replaceing the whole ekw groups with Q and let Pandas handle the conversion:

df['date'] = pd.to_datetime(df['date'].str.replace('e\s*kw\s*','Q'))

Output:

        date
0 2017-04-01
1 2017-07-01
2 2017-10-01
user_42384080
Related