How do I convert dates into ISO-8601 DateTime format in a Pandas dataframe

Question

I have the dataframe below (using python/pandas) and wish to convert the

q_string          q_visits  q_date
red               1790  02/10/2012 00:00
blue              364   02/10/2012 00:00
current           280   02/10/2012 00:00
molecular         259   02/10/2012 00:00
cell              201   02/10/2012 00:00

How can I convert the 'q_date' field into SO-8601 DateTime format (yyyy-MM- ddTHH:mm:ssZ)?

Thanks in advance.

Petras

Answer 1

Use the pandas datetools parser to parse the date and then format it using the standard python strftime function.

>>> df['q_date'].apply(
        lambda x: pd.datetools.parse(x).strftime('%Y-%m-%dT%H:%M:%SZ'))
0    20120210T00:0000Z
1    20120210T00:0000Z
2    20120210T00:0000Z
3    20120210T00:0000Z
4    20120210T00:0000Z
Name: q_date, dtype: object

Blackleg

viktor this leaves it as object dtype so not that useful, better to use to_datetime – Birthright

@Birthright I agree, it's just that the OP asked for a string, and the method I wrote was slightly faster than pd.to_datetime(df['q_date']).apply(lambda x: x.strftime(...)) so I chose that one. – Blackleg

I think the % symbol after M should not be there. For me, using pandas v 1.2.4, the formatting string that worked was '%Y-%m-%dT%H:%M:%SZ' – Interrogative

Answer 2

I would use pd.to_datetime and the .dt accessor

pd.to_datetime(df['q_date']).dt.strftime('%Y-%m-%dT%H:%M:%SZ')

See also strftime() and strptime() Format Codes

Verbose

Answer 3

use to_datetime for pandas datetime format and use strftime to convert to required format.

currentDate = pd.to_datetime(df['q_date'])
convertDate = currentDate.strftime('%Y-%m-%dT%H:%M:%S.%fZ')

or use isoformat to convert to ISO format.

convertDate = currentDate.isoformat()

Lactam

Answer 4

First convert your q_date column into a datetime64[ns] Series, then map over the column with a custom format string

In [178]: df = df.convert_objects(convert_dates='coerce')

In [179]: df
Out[179]:
    q_string  q_visits              q_date
0        red      1790 2012-02-10 00:00:00
1       blue       364 2012-02-10 00:00:00
2    current       280 2012-02-10 00:00:00
3  molecular       259 2012-02-10 00:00:00
4       cell       201 2012-02-10 00:00:00

In [180]: df['iso_q_date'] = df.q_date.map(lambda x: datetime.datetime.strftime(x, '%y%m%dT%H:%M%SZ'))

In [181]: df
Out[181]:
    q_string  q_visits              q_date       iso_q_date
0        red      1790 2012-02-10 00:00:00  120210T00:0000Z
1       blue       364 2012-02-10 00:00:00  120210T00:0000Z
2    current       280 2012-02-10 00:00:00  120210T00:0000Z
3  molecular       259 2012-02-10 00:00:00  120210T00:0000Z
4       cell       201 2012-02-10 00:00:00  120210T00:0000Z

Baugh

Answer 5

To retain the time as well rather than just the date, use pd.Timestamp.isoformat:

df.q_date = df.q_date.pipe(pd.to_datetime).apply(pd.Timestamp.isoformat) + 'Z'

FYI, the timespec argument allows to specify additional terms of the time to include.

Extravert