How to convert pandas dataframe date column in format of 'dd/mm/yyyy %H:%M' to 'yyyy/mm/dd %H:%M'

user_7655447 0
Viewed 2497

I have dataframe in the format of 'dd/mm/yyyy %H:%M'.

Date                Price
29/10/2018 19:30    163.09
29/10/2018 20:00    211.95
29/10/2018 20:30    205.86
29/10/2018 21:00    201.39
29/10/2018 21:30    126.68
29/10/2018 22:00    112.36
29/10/2018 22:30    120.94

I want this dataframe in the format of 'yyyy/mm/dd %H:%M' as following.

Date                Price
2018/29/10 19:30    163.09
2018/29/10 20:00    211.95
2018/29/10 20:30    205.86
2018/29/10 21:00    201.39
2018/29/10 21:30    126.68
2018/29/10 22:00    112.36
2018/29/10 22:30    120.94

I tried df['Date'] = pd.to_datetime(df['Date]) but it gives result as following which is not something I am looking for

Date                   Price
2018-29-10 19:30:00    163.09
2018-29-10 20:00:00    211.95
2018-29-10 20:30:00    205.86
2018-29-10 21:00:00    201.39
pandas python-3.x datetime python
2 Answers

Use strftime for convert datetimes to string format:

df['Date'] = pd.to_datetime(df['Date']).dt.strftime('%Y/%m/%d %H:%M')

print (df)
               Date   Price
0  2018/10/29 19:30  163.09
1  2018/10/29 20:00  211.95
2  2018/10/29 20:30  205.86
3  2018/10/29 21:00  201.39
4  2018/10/29 21:30  126.68
5  2018/10/29 22:00  112.36
6  2018/10/29 22:30  120.94

print (type(df.loc[0, 'Date']))
<class 'str'>

print (df['Date'].dtype)
object

So if want working with datetimeslike function, use only to_datetime, format is YYYY-MM-DD HH:MM:SS:

df['Date'] = pd.to_datetime(df['Date'])
print (df)
                 Date   Price
0 2018-10-29 19:30:00  163.09
1 2018-10-29 20:00:00  211.95
2 2018-10-29 20:30:00  205.86
3 2018-10-29 21:00:00  201.39
4 2018-10-29 21:30:00  126.68
5 2018-10-29 22:00:00  112.36
6 2018-10-29 22:30:00  120.94

print (type(df.loc[0, 'Date']))
<class 'pandas._libs.tslibs.timestamps.Timestamp'>

print (df['Date'].dtype)
datetime64[ns]
user_29010020

Pandas stores datetime as integers

When you say it gives result as following, you are only seeing a string representation of these underlying integers. You should not misconstrue this as how Pandas stores your data or, indeed, how the data will be represented when you export it to another format.

Convert to object dtype

You can use pd.Series.dt.strftime to convert your series to a series of strings. This will have object dtype, which represents a sequence of pointers:

df['Date'] = pd.to_datetime(df['Date']).dt.strftime('%Y/%m/%d %H:%M')

You will lose all vectorisation benefits, so you should aim to perform this operation only if necessary and as late as possible.

user_92095460
Related