Combine Date and Time columns using pandas

Question

I have a pandas dataframe with the following columns:

data = {'Date': ['01-06-2013', '02-06-2013', '02-06-2013', '02-06-2013', '02-06-2013', '03-06-2013', '03-06-2013', '03-06-2013', '03-06-2013', '04-06-2013'],
        'Time': ['23:00:00', '01:00:00', '21:00:00', '22:00:00', '23:00:00', '01:00:00', '21:00:00', '22:00:00', '23:00:00', '01:00:00']}
df = pd.DataFrame(data)

         Date      Time
0  01-06-2013  23:00:00
1  02-06-2013  01:00:00
2  02-06-2013  21:00:00
3  02-06-2013  22:00:00
4  02-06-2013  23:00:00
5  03-06-2013  01:00:00
6  03-06-2013  21:00:00
7  03-06-2013  22:00:00
8  03-06-2013  23:00:00
9  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

score 287 · Accepted Answer · edited Dec 05 '21 at 19:59

It's worth mentioning that you may have been able to read this in directly e.g. if you were using read_csv using parse_dates=[['Date', 'Time']].

Assuming these are just strings you could simply add them together (with a space), allowing you to use to_datetime, which works without specifying the format= parameter

In [11]: df['Date'] + ' ' + df['Time']
Out[11]:
0    01-06-2013 23:00:00
1    02-06-2013 01:00:00
2    02-06-2013 21:00:00
3    02-06-2013 22:00:00
4    02-06-2013 23:00:00
5    03-06-2013 01:00:00
6    03-06-2013 21:00:00
7    03-06-2013 22:00:00
8    03-06-2013 23:00:00
9    04-06-2013 01:00:00
dtype: object

In [12]: pd.to_datetime(df['Date'] + ' ' + df['Time'])
Out[12]:
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
dtype: datetime64[ns]

Alternatively, without the + ' ', but the format= parameter must be used. Additionally, pandas is good at inferring the format to be converted to a datetime, however, specifying the exact format is faster.

pd.to_datetime(df['Date'] + df['Time'], format='%m-%d-%Y%H:%M:%S')

Note: surprisingly (for me), this works fine with NaNs being converted to NaT, but it is worth worrying that the conversion (perhaps using the raise argument).

`%%timeit`

# sample dataframe with 10000000 rows using df from the OP
df = pd.concat([df for _ in range(1000000)]).reset_index(drop=True)

%%timeit
pd.to_datetime(df['Date'] + ' ' + df['Time'])
[result]:
1.73 s ± 10.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
pd.to_datetime(df['Date'] + df['Time'], format='%m-%d-%Y%H:%M:%S')
[result]:
1.33 s ± 9.88 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

score 61 · Answer 2 · answered Sep 13 '16 at 16:24

61

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)

answered Sep 13 '16 at 16:24

jka.ne

1,584
1
15
19

2

I cannot find anything regarding the `time` dtype, in pandas. I have rather a `timedelta` (and a `datetime`) in which case you just need to add them, [see my answer](https://stackoverflow.com/a/50352414/1060349) – toto_tico May 15 '18 at 14:31
When I 'pd.read_excel' an Excel column that Excel identifies as "Time," pandas also reads it as "Time" automatically without any parsing argument required. Thanks for this solution. +1 – Saeed Oct 14 '18 at 03:10
6

Just a note, as of pandas 1.0.0 `pd.datetime` [has been deprecated](https://pandas.pydata.org/pandas-docs/version/1.0.0/whatsnew/v1.0.0.html#deprecations) and it's suggested to just explicitly import the `datetime` module instead. – CopOnTheRun Mar 22 '20 at 15:22
This method is too slow for big datasets. It's faster to convert both columns to strings, join them and use `to_datetime`. – Mykola Zotko Oct 12 '21 at 09:52

score 38 · Answer 3 · edited Dec 05 '21 at 17:56

38

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,

edited Dec 05 '21 at 17:56

Trenton McKinney

56,955
33
144
158

answered Apr 05 '18 at 09:22

Chris PERE

722
7
13

This was the only option I could get to work with Python `datetime` module types. But now I have this one report converted to `datetime64[ns]`, and this leaves me with mixed types elsewhere, which is never a good thing. – xtian May 23 '21 at 00:27
1

This option is inefficient compared to the [accepted answer](https://stackoverflow.com/a/17978188/7758804) because the two columns are already strings. Therefore, using `.astype(str)` is unnecessary (given the OP). Also, `df['Date'] = ` will suffice; no need for `.loc` (e.g. `df.loc[:,'Date'] =`) – Trenton McKinney Dec 05 '21 at 17:53

score 22 · Answer 4 · edited Jul 05 '17 at 08:47

22

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)

edited Jul 05 '17 at 08:47

Eric Aya

69,473
35
181
253

answered Jul 05 '17 at 08:16

Manish Rana

315
2
5

1

You can also use custom `date_parser`, for example `parser = lambda date: pd.datetime.strptime(date, '%d-%b-%y %H:%M:%S')` – Serendipity Oct 25 '17 at 06:44

score 14 · Answer 5 · edited Dec 05 '21 at 18:06

14

You can also convert to datetime without string concatenation, by combining to_datetime and to_timedelta, which create datetime and timedeltea objects, respectively. 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

edited Dec 05 '21 at 18:06

Trenton McKinney

56,955
33
144
158

answered Aug 02 '18 at 10:43

jpp

159,742
34
281
339

3

Great general solution! I had type datetime date and type str time and this worked. – sparrow May 13 '20 at 18:05
2

Actually this is much faster than the accepted answer, especially if the date column is already of type `pd.Timestamp`. – L. Francis Cong Dec 18 '21 at 17:45

score 12 · Answer 6 · edited May 23 '17 at 11:54

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).

score 11 · Answer 7 · answered Aug 06 '18 at 16:11

11

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"]

answered Aug 06 '18 at 16:11

queise

2,286
21
28

toto_tico · Answer 8 · 2019-01-24T08:53:54.377

6

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']

edited Jan 24 '19 at 08:53

answered May 15 '18 at 14:10

toto_tico

17,977
9
97
116

The accepted answer assumes strings: *"Assuming these are just strings you could simply add them together (with a space)"*. My answers are for `datetime` and `timedelta`. The main answer somehow figure out that the columns were strings, or maybe it was just the answer that worked for the one that posted the question. – toto_tico Jan 24 '19 at 09:02

score 4 · Answer 9 · answered Aug 28 '18 at 08:11

4

Use the combine function:

datetime.datetime.combine(date, time)

answered Aug 28 '18 at 08:11

Stephen

169
4
10

score 4 · Answer 10 · answered Feb 08 '21 at 04:53

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.

score 2 · Answer 11 · answered Jul 16 '19 at 15:38

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.

score 2 · Answer 12 · answered Aug 02 '20 at 01:42

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')

score -1 · Answer 13 · answered Jul 13 '21 at 09:59

I think the best solution is to parse dates within read_csv (or other read_ functions) directly. It is not obvious how to manage two columns in date_parser but here it is:

date_parser = lambda x,y: datetime.strptime(f"{x}T{y}", "%d-%m-%YT%H:%M:%S")
date = pd.read_csv('data.csv', parse_dates={'date': ['Date', 'Time']}, date_parser=date_parser)

Combine Date and Time columns using pandas

13 Answers13

`%%timeit`

Linked

Related