How to combine multiple columns in a DataFrame to Pandas datetime

Question

I have a pandas data frame with values as below

ProcessID1 UserID Date Month Year Time 248 Tony 29 4 2017 23:30:56 436 Jeff 28 4 2017 20:02:19 500 Greg 4 5 2017 11:48:29 I would like to know is there any way I can combine columns of Date,Month&Year & time to a pd.datetimeformat?

Possible duplicate of [Combine Date and Time columns using python pandas](https://stackoverflow.com/questions/17978092/combine-date-and-time-columns-using-python-pandas) — jpp, Apr 08 '18 at 19:18
Possible duplicate of [Pandas: create timestamp from 3 columns: Month, Day, Hour](https://stackoverflow.com/questions/26137946/pandas-create-timestamp-from-3-columns-month-day-hour) — jpp, Apr 08 '18 at 19:19

jezrael · Answer 1 · 2020-02-24T06:22:06.513

13

Use to_datetime with automatic convert column Day,Month,Year with add times converted to_timedelta:

df['Datetime'] = pd.to_datetime(df.rename(columns={'Date':'Day'})[['Day','Month','Year']]) + \
                 pd.to_timedelta(df['Time'])

Another solutions are join all column converted to strings first:

df['Datetime'] = pd.to_datetime(df[['Date','Month','Year', 'Time']]
                   .astype(str).apply(' '.join, 1), format='%d %m %Y %H:%M:%S')

df['Datetime']  = (pd.to_datetime(df['Year'].astype(str) + '-' +
                                  df['Month'].astype(str) + '-' +
                                  df['Date'].astype(str) + ' ' +
                                  df['Time']))

print (df)
   ProcessID1 UserID  Date  Month  Year      Time            Datetime
0         248   Tony    29      4  2017  23:30:56 2017-04-29 23:30:56
1         436   Jeff    28      4  2017  20:02:19 2017-04-28 20:02:19
2         500   Greg     4      5  2017  11:48:29 2017-05-04 11:48:29

Last if need remove these columns:

df = df.drop(['Date','Month','Year', 'Time'], axis=1)
print (df)
   ProcessID1 UserID            Datetime
0         248   Tony 2017-04-29 23:30:56
1         436   Jeff 2017-04-28 20:02:19
2         500   Greg 2017-05-04 11:48:29

edited Feb 24 '20 at 06:22

answered Apr 08 '18 at 14:24

jezrael

822,522
95
1,334
1,252

I tried with the above code. it is giving me KeyError: ('Day', 'Month', 'Year') – Jithesh Erancheri Apr 08 '18 at 14:43
@JitheshErancheri - What is `print (df.columns.tolist())` ? Error means there is no some columns. Or maybe use some old pandas version? [first solution is implemented in pandas 0.18.1](http://pandas.pydata.org/pandas-docs/stable/timeseries.html#assembling-datetime-from-multiple-dataframe-columns) – jezrael Apr 08 '18 at 14:46
it gives 'UserID', 'Date', 'Month', 'Year', 'Time' – Jithesh Erancheri Apr 08 '18 at 14:52
It seems you forget for `rename` - `df.rename(columns={'Date':'Day'})` in first solution. – jezrael Apr 08 '18 at 14:55
I renamed the columns . now print (df.columns.tolist()) outputs 'UserID', 'Day', 'Month', 'Year', 'Time'. But pd.to_datetime(df["Day","Month","Year"])+ pd.to_timedelta(df['Time']) still gives the earlier mentioned KeyError. I am using Pandas 0.22. version. – Jithesh Erancheri Apr 08 '18 at 15:22
@JitheshErancheri - Are dat a confidental? If not, can you send me csv to my email in my profile? – jezrael Apr 08 '18 at 15:23
1

I can share with you – Jithesh Erancheri Apr 08 '18 at 15:26
@jezrael is there a missing square bracket in this line? df['Datetime'] = pd.to_datetime(df.rename(columns={'Date':'Day'})['Day','Month','Year']]) should be: df['Datetime'] = pd.to_datetime(df.rename(columns={'Date':'Day'})[['Day','Month','Year']]) ? – griffinleow Feb 24 '20 at 03:53

score 1 · Answer 2 · answered Sep 09 '21 at 11:16

Concatenate the columns together to a string format and use pd.to_datetime to convert to datetime.

import pandas as pd
import io

txt = """
ProcessID1  UserID   Date   Month    Year     Time 
        248    Tony     29       4   2017  23:30:56
        436    Jeff     28       4   2017  20:02:19
        500    Greg      4       5   2017  11:48:29
"""

df = pd.read_csv(io.StringIO(txt), sep="[\t ,]+")

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

Anurag Dabas · Answer 3 · 2021-04-10T18:52:18.787

import pandas as pd

You can also do this by using apply() method:-

df['Datetime']=df[['Year','Month','Date']].astype(str).apply('-'.join,1)+' '+df['Time']

Finally convert 'Datetime' to datetime dtype by using pandas to_datetime() method:-

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

Output of df:

    ProcessID1  UserID   Date   Month   Year    Time        Datetime
0   248          Tony     29    4       2017    23:30:56    2017-04-29 23:30:56
1   436          Jeff     28    4       2017    20:02:19    2017-04-28 20:02:19
2   500          Greg      4    5       2017    11:48:29    2017-05-04 11:48:29

Now if you want to remove 'Date','Month','Year' and 'Time' column then use:-

df=df.drop(columns=['Date','Month','Year', 'Time'])

How to combine multiple columns in a DataFrame to Pandas datetime

3 Answers3

Linked

Related