CODEWITHSUNDEEP
pythonpython-3.xpandasdatetime
Nikhil Mangire
Nikhil Mangire

Reputation: 407

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

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

Upvotes: 4

Views: 2678

Answers (2)

jezrael
jezrael

Reputation: 862681

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]

Upvotes: 4

jpp
jpp

Reputation: 164673

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.

Upvotes: 2

Related Questions