Extracting dates from string and into a dataframe into a new column python

Question

I am trying to extract dates from a set format of MONDAY, JUNE 14, 2021 from a sea of text in a data frame and create a new column for the extracted dates.

For example, the data frame looks like:

x	title	ii
xx	aldkla MONDAY, JUNE 14, 2021 slkfdklkdj	lkskjdfkdlskld
yy	alksflksd TUESDAY, November, 23, 2020 alfjsldjf	sfdadsf

And I am trying to create the end product to be:

x	title	ii	Date
xx	aldkla MONDAY, JUNE 14, 2021 slkfdklkdj	lkskjdfkdlskld	2021-06-14
yy	alksflksd TUESDAY, November, 23, 2020 alfjsldjf	sfdadsf	2020-11-23

Currently, I have been using the library datefinder because I found it to be able to convert the str to date the best. However, once I start using it with a dataframe it does work. This is what I put.

df = dataframe #the example above 


Date = datefinder.find_dates(df.title)
for match in Date: 
  df["time"] = match

Is there a better method? Or am I going about this all wrong?

FObersteiner · Accepted Answer · 2021-06-30 08:06:02Z

2

dateutil's parser does a good job with kwarg fussy=True:

import pandas as pd
from dateutil import parser

s = pd.Series(["aldkla MONDAY, JUNE 14, 2021 slkfdklkdj", "alksflksd TUESDAY, November, 23, 2020 alfjsldjf"])

s.apply(parser.parse, fuzzy=True)

>>>
0   2021-06-14
1   2020-11-23
dtype: datetime64[ns]

answered Jun 30, 2021 at 8:06

FObersteiner

26.1k9 gold badges60 silver badges94 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2021-06-30 07:47:46Z

1

Using str.replace along with to_datetime:

df["Date"] = pd.to_datetime(df["title"].str.replace(r'\b([^\s,]+),? (\d+), (\d{4})\b', r'\1\2\3'), format='%B%d%Y', errors='ignore')

One slight potential problem with the above might occur if the days in your text dates are not zero left padded. In that case, more work would be needed to get those dates to parse properly.

answered Jun 30, 2021 at 7:47

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Ferris · Accepted Answer · 2021-06-30 08:15:56Z

0

# method 1
obj = df['title']
Date = pd.to_datetime(obj.str.replace(r'^.*?(\w+)[\s,]+(\d+)[\s,]+(\d{4}).*?$', r'\1-\2-\3'), 
               format='%B-%d-%Y', 
               errors='ignore')

# method 2
import datefinder
Date = df['title'].map(datefinder.find_dates).map(list).str[0]

edited Jun 30, 2021 at 8:15

answered Jun 30, 2021 at 8:08

Ferris

5,6611 gold badge18 silver badges27 bronze badges

Collectives™ on Stack Overflow

Extracting dates from string and into a dataframe into a new column python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related