Combining different date and time columns to form a datetime value

Question

I'm trying to create a YYYY-MM-DD HH:MM:SS AM/PM format (for e.g. 2017-01-01 12:00:00 AM) from a. A date column that is of the DDMMMYYYY format (for e.g. 01JAN2017); and b. A time column that is of the HH:MM:SS AM/PM (for e.g. 12:00:00 AM) format.

The AM/PM in (b) appears to be the biggest problem.

I've tried a few approaches from stack overflow a. read.csv(parse_dates = [['date','time]]), and then re-arranging the date-time column using pd.to_datetime(df, format='%d%b%Y %H:%M:%S'). b. Converting 'date' using the datetime function and 'time' function to timedeltas using pd.time_delta before trying to concatenate both of them. c. Looping(Combine date and time columns using datetime) d. Writing a parser, and then including the parser into the pd.read_csv command together with parse_dates = [['date','time']]. (Convert string date time to pandas datetime)

a. df = pd.read_csv('a.csv',parse_dates=[['date','time']]) df['datetime'] = pd.to_datetime(df['datetime'], format = format='%d%b%Y %H:%M:%S)

b. df = pd.read_csv('a.csv') df["Date"] = pd.to_datetime(df["Date"]) df["Time"] = pd.to_timedelta(df["Time"]) df["DateTime"] = df["Date"] + df["Time"]

c. Same as the code in the link

d. Same as the code in the link

I received plenty of error messages on formats because the time column has the AM or PM portion after the HH:MM:SS portion.

jezrael · Accepted Answer · 2019-06-14 11:57:32Z

1

Use to_datetime with %I for parse hour in 12H format with %p for parse AM/PM. Last if need in output AM/PM is necessary convert to strings by Series.dt.strftime:

df = pd.read_csv('a.csv')

print (df)
        Date         Time
0  01JAN2017  12:00:00 AM

df["DateTime"] = pd.to_datetime(df["Date"] + ' ' +df["Time"],  format='%d%b%Y %I:%M:%S %p')
df["DateTime_str"] = df["DateTime"].dt.strftime('%Y-%m-%d %I:%M:%S %p')
print (df)
        Date         Time   DateTime            DateTime_str
0  01JAN2017  12:00:00 AM 2017-01-01  2017-01-01 12:00:00 AM

edited Jun 14, 2019 at 11:57

answered Jun 14, 2019 at 11:18

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

tlhy Over a year ago

Sorry I have a follow-up question. How about if my date is first converted df['date_actual'] = pd.to_datetime(df['date_actual'],format='%d%b%Y') - this is so that I can merge different dataframes together by date first, which yields 2017-01-01. How do I merge this with the time column containing values such as 12:00:00 AM? I tried pd.to_datetime again but I don't think it works

Collectives™ on Stack Overflow

Combining different date and time columns to form a datetime value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related