python pandas: split string into date and time in datetime format

Question

I have a field in my dataframe df, labeled date, but after testing it a lot, I figured out that it is a string and not a date format.

Transaction_id | Date
ABC              4/1/2016 9:13:58 PM
CDE              10/3/2015 10:12:25 AM
EFG              12/12/2017 3:02:45 PM

I need to split the Date column up into Date & time and I want them to be in datetime format. I don't know how to do this with regex, since the lengths are different.

Output:

Transaction_id | Date                    | Date2       | Time            
ABC              4/1/2016 9:13:58 PM       04/01/2016    21:13:58
CDE              10/3/2015 10:12:25 AM     10/03/2016    10:12:25
EFG              12/12/2017 3:02:45 PM     12/12/2017    15:02:45

FYI, your dates are of not consistent format. First row is 12-hr format, others are 24-hr. — Zero
– Zero, Commented Sep 19, 2017 at 20:00
sorry - typo; should have been 3 instead of 15; fixed, thank you — jeangelj
– jeangelj, Commented Sep 19, 2017 at 20:03

piRSquared · Accepted Answer · 2017-09-19 20:11:31Z

4

Note that I placed a errors='coerce' to handle non-sensical date data.

date2 = pd.to_datetime(df.Date, errors='coerce')

df.assign(Date2=date2.dt.date, Time=date2.dt.time)

  Transaction_id                   Date       Date2      Time
0            ABC    4/1/2016 9:13:58 PM  2016-04-01  21:13:58
1            CDE  10/3/2015 10:12:25 AM  2015-10-03  10:12:25
2            EFG  12/12/2017 3:02:45 PM  2017-12-12  15:02:45

edited Sep 19, 2017 at 20:11

answered Sep 19, 2017 at 19:58

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

6 Comments

Zero Over a year ago

Date parsing issue, with 9:13:58 PM should be 17:13:58?

piRSquared Over a year ago

Thanks... will fix

Zero Over a year ago

Data is inconsistent with 12hr and 24hr timestamps.

jeangelj Over a year ago

Great! Thank you so much!

piRSquared Over a year ago

@jeangelj not yet. This is broken. the %p directive isn't being respected. I'm looking for a fix.

|

Collectives™ on Stack Overflow

python pandas: split string into date and time in datetime format

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related