How to combine one Pandas dataframe column with a date and another with a time?

user_4256436 0
Viewed 172

I'm working with a CSV that has two columns, one with a date in the month/day/year format (for example 01/15/2019) and another with a time in the format with a 12-hour time an "a" for AM and "p" for PM. Examples: 10/9/2017,9:50a and 10/9/2017,3:50p. I'd like to combine these two columns into one column of DateTime objects, for example "2017-10-09 09:50:00" and "2017-10-09 15:50:00".

These happen to be in columns 2 and 3 of the CSV. I've tried the following options with pd.read_csv:

  • Passing parse_dates = True: Nothing appears to happen. The resulting date and time columns are just "objects" according to dtypes, not DateTime objects.
  • Passing parse_dates = [[2,3]]: Combines the columns but doesn't result in a DateTime object.
  • Passing parse_dates = [2,3]: Converts the individual columns to DateTime objects but doesn't combine; the date column is correctly converted, but the time column ends up with today's date attached, e.g. 2019-01-15 15:50:00.

I've also tried date_parser but I am not sure what parsing function I need to give it -- and it just seems like pandas should be able to handle these date/time formats without coercing.

Any thoughts? Thanks in advance.

pandas datetime python
2 Answers

You can convert to datetime after you have read your data into a dataframe. Since your date and time components are split across 2 series, trying to parse directly with pd.read_csv may be difficult.

from io import StringIO

x = """date,time
10/9/2017,9:50a
10/9/2017,3:50p"""

# replace StringIO(x) with 'file.csv'
df = pd.read_csv(StringIO(x))

df['datetime'] = pd.to_datetime(df['date'] + ' ' + df['time'])

print(df)
#         date   time            datetime
# 0  10/9/2017  9:50a 2017-10-09 09:50:00
# 1  10/9/2017  3:50p 2017-10-09 15:50:00
user_92095460

I figured it out, just after @jpp posted their solution and used something like this: 

df = pd.read_csv("rawdata.csv", parse_dates={"Combined": [2,3]})
df['Combined'] = pd.to_datetime(df['Combined'], errors="coerce")

The last argument is because I had NaNs in my data. Basically: Like the other answer said, combine columns first and then use to_datetime to change it afterwards. Thanks again.

user_42564360
Related