Finding columns which contain dates in Pandas

Question

I'm trying to identify columns which contain dates as strings in order to then convert them to a better type (DateTime or something numeric like UTC). The date format used is 27/11/2012 09:17 which I can search for using a regex of \d{2}/\d{2}/\d{4} \d{2}:\d{2}.

My current code is:

date_cols = []
df = cleaned_data
date_pattern = re.compile('\d{2}/\d{2}/\d{4} \d{2}:\d{2}')
for column in df:
    if date_pattern.search(str(item)):
        date_cols += [column]
return date_cols

I'm sure this is not taking advantage of the capabilities of pandas. Is there a better way, either to identify the columns, or to convert them to DateTime or UTC timestamps directly?

Answer 1

If you are looking to convert entire columns, you can use convert_objects:

df.convert_objects(convert_dates=True)

To extract dates contained in columns/Series you could use findall:

In [11]: s = pd.Series(['1', '10/11/2011 11:11'])

In [12]: s.str.findall('\d{2}/\d{2}/\d{4} \d{2}:\d{2}')
Out[12]:
0                    []
1    [10/11/2011 11:11]
dtype: object

In [13]: s.str.findall('\d{2}/\d{2}/\d{4} \d{2}:\d{2}').apply(pd.Series)
Out[13]:
                  0
0               NaN
1  10/11/2011 11:11

*and then convert to Timestamps using convert_objects...*

Answer 2

Depending on how overzealous you want to be, to_datetime will coerce anything it thinks is a datetime into a datetime, including ints → datetimes (defaults to ns since UNIX epoch).

to_datetime gives you a lot of control over how to interpret the datetimes it finds too.

pandas.to_datetime(arg, errors='ignore', dayfirst=False, utc=None,
                                 box=True, format=None, coerce=False, unit='ns')