convert string date to datetime format in pandas

Question

I have a Pandas dataFrame, with a column of Date:

    ID  Amount   raw-Date   ZIP transaction-ID  Date    flag
749     145552  $100.00 1/15/2018   27614-7901  1342-P0192-F43  1/15/2018   1.0
1307    145552  $100.00 3/15/2018   27614-7901  1342-P0192-F43  3/15/2018   1.0
1672    145552  $100.00 2/15/2018   27614-7901  1342-P0192-F43  2/15/2018   1.0
3508    145552  $100.00 4/15/2018   27614-7901  1342-P0192-F43  4/15/2018   1.0
4144    145552  $250.00 4/24/2018   27614-7901  1234-O8910-B32  4/24/2018   1.0
4145    145552  $100.00 4/24/2018   27614-7901  1234-O8910-B32  4/24/2018   1.0
4787    145552  $100.00 5/15/2018   27614-7901  1342-P0192-F43  5/15/2018   1.0
8350    145552  $212.44 12/21/2018  27614-7901  1342-P0192-F43  12/21/2018  1.0

When I sort them by the Date column, i.e., using data.sort_values('Date'), I get:

ID  Amount   raw-Date   ZIP transaction-ID  Date    flag
749     145552  $100.00 1/15/2018   27614-7901  1342-P0192-F43  1/15/2018   1.0
8350    145552  $212.44 12/21/2018  27614-7901  1342-P0192-F43  12/21/2018  1.0
1672    145552  $100.00 2/15/2018   27614-7901  1342-P0192-F43  2/15/2018   1.0
1307    145552  $100.00 3/15/2018   27614-7901  1342-P0192-F43  3/15/2018   1.0
3508    145552  $100.00 4/15/2018   27614-7901  1342-P0192-F43  4/15/2018   1.0
4144    145552  $250.00 4/24/2018   27614-7901  1234-O8910-B32  4/24/2018   1.0
4145    145552  $100.00 4/24/2018   27614-7901  1234-O8910-B32  4/24/2018   1.0
4787    145552  $100.00 5/15/2018   27614-7901  1342-P0192-F43  5/15/2018   1.0

in which apparently it sorts dates as a string. I tried pd.to_datetime(data['Date']) and again got same sorted result:

    ID  Amount  raw-Date    ZIP Appeal ID   Date    flag 
749     145552  $100.00 1/15/2018   27614-7901  1342-P0192-F43  2018-01-15  1.0
8350    145552  $212.44 12/21/2018  27614-7901  1342-P0192-F43  2018-12-21  1.0
1672    145552  $100.00 2/15/2018   27614-7901  1342-P0192-F43  2018-02-15  1.0
1307    145552  $100.00 3/15/2018   27614-7901  1342-P0192-F43  2018-03-15  1.0
3508    145552  $100.00 4/15/2018   27614-7901  1342-P0192-F43  2018-04-15  1.0
4144    145552  $250.00 4/24/2018   27614-7901  1234-O8910-B32  2018-04-24  1.0
4145    145552  $100.00 4/24/2018   27614-7901  1234-O8910-B32  2018-04-24  1.0
4787    145552  $100.00 5/15/2018   27614-7901  1342-P0192-F43  2018-05-15  1.0

I appreciate any help.

data['Date']=pd.to_datetime(data['Date']) didn't work? You have to reassign back to data['Date']. Check data.info() to see that the dtype of Date column is after pd.to_datetime. — Scott Boston
– Scott Boston, Commented Jun 17, 2020 at 20:40
@QuangHoang, well, for posting the question I edited the column name to keep the confidentiality of data, and it resulted in two columns of same name. In what I have they have same name. I edited the question accordingly. — Afshin Oroojlooy
– Afshin Oroojlooy, Commented Jun 17, 2020 at 20:54
Are you asking about sorting or convert to datetime? Did you reassign the sorted data: df = df.sort_values('Date')? — Quang Hoang
– Quang Hoang, Commented Jun 17, 2020 at 21:05
@QuangHoang I asking about sorting date, which are sorted as string, not as datetime. — Afshin Oroojlooy
– Afshin Oroojlooy, Commented Jun 18, 2020 at 0:36

Quang Hoang · Accepted Answer · 2020-06-17 20:46:13Z

2

Your data has duplicated column name Date, which is discouraged. In this case: df['Date'] would give a dataframe with two columns, and pd.to_datetime(df['Date']) would fail.

That said, you can do an apply:

df['Date'] = df['Date'].apply(pd.to_datetime)

After that, df.Date.dtypes would give:

Date    datetime64[ns]
Date    datetime64[ns]
dtype: object

answered Jun 17, 2020 at 20:46

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Afshin Oroojlooy Over a year ago

data.Date.dtypes returns dtype('<M8[ns]'). To get this columns I have data['Date'] = pd.to_datetime(data['raw-Date'])

Quang Hoang Over a year ago

'<M8[ns]' is an alias for datetime64[ns].

Quang Hoang Over a year ago

Also, pass errors='coerce' to pd.to_datetime would help.

Collectives™ on Stack Overflow

convert string date to datetime format in pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related