PeterMkc PeterMkc - 6 days ago 6
Python Question

How to convert "dd.mm.yyyy" into "yyyy-mm-dd" with Pandas in Python?

I am reading a CSV file with intraday stock market data into a Pandas data frame.

import pandas as pd

df = pd.read_csf('file.csv')
df.columns = ['Day', 'Time', 'Open', 'High', 'Low', 'Volume']
df.head()

Day Time Open High Low Close Volume
0 23.11.2009 17:15 1104.00 1104.00 1104.00 1104.00 2
1 23.11.2009 17:20 1103.00 1103.00 1103.00 1103.00 11
2 23.11.2009 17:25 1103.75 1103.75 1103.75 1103.75 1
3 23.11.2009 17:30 1103.25 1103.25 1102.50 1102.50 3
4 23.11.2009 17:35 1103.25 1103.50 1103.00 1103.00 13


Unfortunately the date format in the column "Day" is in European date format: "dd.mm.yyyy". For example "28.11.2016".

How can I easily convert the "Day" column into something like "yyyy-mm-dd" or "2016-11-28"?

Answer

to_datetime just works on your date strings:

In [3]:
pd.to_datetime(df['Day'])

Out[3]:
0   2009-11-23
1   2009-11-23
2   2009-11-23
3   2009-11-23
4   2009-11-23
Name: Day, dtype: datetime64[ns]

This returns a datetime64 column

Note that if you passed parse_dates=['Day'] to read_csv then it would have converted on reading in your csv:

In [6]:
df = pd.read_csv(io.StringIO(t), parse_dates=['Day'], index_col=0)
df

Out[6]:
         Day   Time     Open     High      Low    Close  Volume
0 2009-11-23  17:15  1104.00  1104.00  1104.00  1104.00       2
1 2009-11-23  17:20  1103.00  1103.00  1103.00  1103.00      11
2 2009-11-23  17:25  1103.75  1103.75  1103.75  1103.75       1
3 2009-11-23  17:30  1103.25  1103.25  1102.50  1102.50       3
4 2009-11-23  17:35  1103.25  1103.50  1103.00  1103.00      13

Note that pandas generally returns a copy of the data, some methods have an inplace arg but to_datetime does not so you need to self-assign to overwrite:

df['Day'] = pd.to_datetime(df['Day'])
Comments