Convert time and date columns to timestamp

user_7814125 0
Viewed 357

I have two columns - date and time.

date                  | time    
2012-10-16 00:00:00   | 09:38:08-05:00

I wish to combine these two and form a timestamp. I don't know how to approach the problem. Should I strip the date, add time, and create a DateTime and then convert to timestamp.

pandas dataframe timestamp datetime python
1 Answers

Basically, this is a variation of Combine Date and Time columns using python pandas but you have a UTC offset, so let's parse this correctly as well. Ex:

df = pd.DataFrame({'date': ['2012-10-16 00:00:00'],
                   'time': ['09:38:08-05:00']})

# make sure date column is date as string dtype
df['date'] = pd.to_datetime(df['date']).dt.date.astype(str)

# make sure time column is of string dtype
df['time'] = df['time'].astype(str)

# combine date and time column (now both string dtype), then parse to datetime
df['datetime'] = pd.to_datetime(df['date'] + ' ' + df['time'])

# df['datetime']
# 0   2012-10-16 09:38:08-05:00
# Name: datetime, dtype: datetime64[ns, pytz.FixedOffset(-300)]

optional: specify a time zone. UTC-5 is a fixed offset, which is sometimes not desired. If you know the tz of your source, you can simply add that info:

df['datetime'] = df['datetime'].dt.tz_convert('US/Central')

# df['datetime']
# 0   2012-10-16 09:38:08-05:00
# Name: datetime, dtype: datetime64[ns, US/Central]

optional: parse to UTC directly. In most cases, it is best to work with UTC. You can parse to UTC directly:

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

# df['datetime']
# 0   2012-10-16 14:38:08+00:00
# Name: datetime, dtype: datetime64[ns, UTC]
user_101974180
Related