[python] Combine Date and Time columns using python pandas

I have a pandas dataframe with the following columns;

Date              Time
01-06-2013      23:00:00
02-06-2013      01:00:00
02-06-2013      21:00:00
02-06-2013      22:00:00
02-06-2013      23:00:00
03-06-2013      01:00:00
03-06-2013      21:00:00
03-06-2013      22:00:00
03-06-2013      23:00:00
04-06-2013      01:00:00

How do I combine data['Date'] & data['Time'] to get the following? Is there a way of doing it using pd.to_datetime?

Date
01-06-2013 23:00:00
02-06-2013 01:00:00
02-06-2013 21:00:00
02-06-2013 22:00:00
02-06-2013 23:00:00
03-06-2013 01:00:00
03-06-2013 21:00:00
03-06-2013 22:00:00
03-06-2013 23:00:00
04-06-2013 01:00:00

You can cast the columns if the types are different (datetime and timestamp or str) and use to_datetime :

df.loc[:,'Date'] = pd.to_datetime(df.Date.astype(str)+' '+df.Time.astype(str))

Result :

0   2013-01-06 23:00:00
1   2013-02-06 01:00:00
2   2013-02-06 21:00:00
3   2013-02-06 22:00:00
4   2013-02-06 23:00:00
5   2013-03-06 01:00:00
6   2013-03-06 21:00:00
7   2013-03-06 22:00:00
8   2013-03-06 23:00:00
9   2013-04-06 01:00:00

Best,

Use the combine function:

datetime.datetime.combine(date, time)

I don't have enough reputation to comment on jka.ne so:

I had to amend jka.ne's line for it to work:

df.apply(lambda r : pd.datetime.combine(r['date_column_name'],r['time_column_name']).time(),1)

This might help others.

Also, I have tested a different approach, using replace instead of combine:

def combine_date_time(df, datecol, timecol):
    return df.apply(lambda row: row[datecol].replace(
                                hour=row[timecol].hour,
                                minute=row[timecol].minute),
                    axis=1)

which in the OP's case would be:

combine_date_time(df, 'Date', 'Time')

I have timed both approaches for a relatively large dataset (>500.000 rows), and they both have similar runtimes, but using combine is faster (59s for replace vs 50s for combine).

You can use this to merge date and time into the same column of dataframe.

import pandas as pd    
data_file = 'data.csv' #path of your file

Reading .csv file with merged columns Date_Time:

data = pd.read_csv(data_file, parse_dates=[['Date', 'Time']]) 

You can use this line to keep both other columns also.

data.set_index(['Date', 'Time'], drop=False)

The answer really depends on what your column types are. In my case, I had datetime and timedelta.

> df[['Date','Time']].dtypes
Date     datetime64[ns]
Time    timedelta64[ns]

If this is your case, then you just need to add the columns:

> df['Date'] + df['Time']

My dataset had 1second resolution data for a few days and parsing by the suggested methods here was very slow. Instead I used:

dates = pandas.to_datetime(df.Date, cache=True)
times = pandas.to_timedelta(df.Time)
datetimes  = dates + times

Note the use of cache=True makes parsing the dates very efficient since there are only a couple unique dates in my files, which is not true for a combined date and time column.

The accepted answer works for columns that are of datatype string. For completeness: I come across this question when searching how to do this when the columns are of datatypes: date and time.

df.apply(lambda r : pd.datetime.combine(r['date_column_name'],r['time_column_name']),1)

First make sure to have the right data types:

df["Date"] = pd.to_datetime(df["Date"])
df["Time"] = pd.to_timedelta(df["Time"])

Then you easily combine them:

df["DateTime"] = df["Date"] + df["Time"]

Here is a one liner, to do it. You simply concatenate the two string in each of the column with a " " space in between.

Say df is your dataframe and columns are 'Time' and 'Date'. And your new column is DateAndTime.

df['DateAndTime'] = df['Date'].str.cat(df['Time'],sep=" ")

And if you also wanna handle entries like datetime objects, you can do this. You can tweak the formatting as per your needs.

df['DateAndTime'] = pd.to_datetime(df['DateAndTime'], format="%m/%d/%Y %I:%M:%S %p")

Cheers!! Happy Data Crunching.

DATA:

<TICKER>,<PER>,<DATE>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL> SPFB.RTS,1,20190103,100100,106580.0000000,107260.0000000,106570.0000000,107230.0000000,3726

CODE:

data.columns = ['ticker', 'per', 'date', 'time', 'open', 'high', 'low', 'close', 'vol']    
data.datetime = pd.to_datetime(data.date.astype(str) + ' ' + data.time.astype(str), format='%Y%m%d %H%M%S')

You can also convert to datetime without string concatenation, by combining datetime and timedelta objects. Combined with pd.DataFrame.pop, you can remove the source series simultaneously:

df['DateTime'] = pd.to_datetime(df.pop('Date')) + pd.to_timedelta(df.pop('Time'))

print(df)

             DateTime
0 2013-01-06 23:00:00
1 2013-02-06 01:00:00
2 2013-02-06 21:00:00
3 2013-02-06 22:00:00
4 2013-02-06 23:00:00
5 2013-03-06 01:00:00
6 2013-03-06 21:00:00
7 2013-03-06 22:00:00
8 2013-03-06 23:00:00
9 2013-04-06 01:00:00

print(df.dtypes)

DateTime    datetime64[ns]
dtype: object