CODEWITHSUNDEEP
pythonpandasdatedatetimeposix
CaptainPlanet
CaptainPlanet

Reputation: 43

How to convert these 2 date/time columns into 1?

I've spent a few hours reading and trying things from the Python and Pandas docs and I'm not getting what I need...

I have 2 columns-- one is called DATE_GMT and one is called TIME_GMT. The date column is self-explanatory. The TIME column shows "0" through "24" as to which hour it is...

enter image description here

How do I convert the date and time columns, and then merge them so they are POSIX time supportive?

Upvotes: 1

Views: 78

Answers (3)

Venkata Gogu
Venkata Gogu

Reputation: 1051

You can directly takes these two columns as two strings and append them together. Then use to_datetime from pandas and give the format of the string to update this as datetime value.

Code

d = pd.DataFrame({'DATE_GMT':['20-JAN-16','20-JAN-16','20-JAN-16','20-JAN-16','20-JAN-16'],
                  'HOUR_GMT':[23,23,23,23,23]})
d['combined_date'] = pd.to_datetime(d['DATE_GMT'].astype(str)+' '+d['HOUR_GMT'].astype(str),format='%d-%b-%y %H') 

    DATE_GMT   HOUR_GMT combined_date
0   20-JAN-16      23   2016-01-20 23:00:00
1   20-JAN-16      23   2016-01-20 23:00:00
2   20-JAN-16      23   2016-01-20 23:00:00
3   20-JAN-16      23   2016-01-20 23:00:00
4   20-JAN-16      23   2016-01-20 23:00:00

Upvotes: 3

piRSquared
piRSquared

Reputation: 294238

Use a combination of pd.to_datetime and pd.to_timedelta

pd.to_datetime(df.date) + pd.to_timedelta(df.hour, unit='h')

0   2016-01-20 20:00:00
1   2016-01-21 21:00:00
2   2016-01-21 22:00:00
3   2016-01-21 23:00:00
dtype: datetime64[ns]

Upvotes: 2

YOLO
YOLO

Reputation: 21709

To do this, you can use to_datetime function by passing it a dataframe of date time values.

## sample data 
df = pd.DataFrame({'date':['20-JAN-2016','21-JAN-2016','21-JAN-2016','21-JAN-2016'],
                   'hour':[20,21,22,23]})

# convert to datetime
df['date'] = pd.to_datetime(df['date'])

# extract date components
df['year'] = df.date.dt.year
df['month'] = df.date.dt.month
df['day'] = df.date.dt.day

# remove date
df.drop('date', axis=1, inplace=True)

df['full_date'] = pd.to_datetime(df)

print(df)

   hour  year  month  day           full_date
0    20  2016      1   20 2016-01-20 20:00:00
1    21  2016      1   21 2016-01-21 21:00:00
2    22  2016      1   21 2016-01-21 22:00:00
3    23  2016      1   21 2016-01-21 23:00:00

Upvotes: 2

Related Questions