Extractall for the dates ending with 'st','nd', 'rd','th' while swapping days with months using RegEx

Question

I have got these dates within text in a pandas dataframe column.

import pandas as pd
sr = pd.Series(['text Mar 20, 2009 text', 'text March 20, 2009 text', 'text 20 Mar. 2009 text', 'text Sep 2010 text','text Mar 20th, 2009 text ','text Mar 21st, 2009 text'])

when I use regex, I get this.

a=sr.str.extractall(r'((?P<day>(?:\d{2} )?(?P<month>(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[a-z,.]*)) (?:\d{2}[-/th|st|nd|rd\s]*[,.]* )?(?P<year>\d{4}))')


       all              day     month  year
match               
0   0   Mar 20, 2009    Mar      Mar    2009
1   0   March 20, 2009  March    March  2009
2   0   20 Mar. 2009    20 Mar.  Mar.   2009
3   0   Sep 2010        Sep      Sep    2010
4   0   Mar 20th, 2009  Mar      Mar    2009
5   0   Mar 21st, 2009  Mar      Mar    2009

How can I get the dates (20,20th,21st...) into the day column?

Answer 1

One solution with pandas (why reinvent the wheel?):

    import pandas as pd
    df = sr.to_frame(name='all')
    df['all'] = pd.to_datetime(df['all'])
    df['day'] = df['all'].dt.day
    df['month'] = df['all'].dt.strftime('%b')
    df['year'] = df['all'].dt.year

Output:

         all  day month  year
0 2009-03-20   20   Mar  2009
1 2009-03-20   20   Mar  2009
2 2009-03-20   20   Mar  2009
3 2010-09-01    1   Sep  2010
4 2009-03-20   20   Mar  2009
5 2009-03-21   21   Mar  2009