Combine Date and Time columns using python pandas

Question

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    amp  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

User · Answer

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)

User · Answer

DATA   lt TICKER gt   lt PER gt   lt DATE gt   lt TIME gt   lt OPEN gt   lt HIGH gt   lt LOW gt   lt CLOSE gt   lt VOL gt  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

User · Answer

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

User · Answer

Use the  combine function   datetime datetime combine date  time

User · Answer

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 apply to datetime   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    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

User · Answer

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

User · Answer

The answer really depends on what your column types are  In my case  I had datetime and timedelta    gt  df   Date   Time    dtypes Date     datetime64 ns  Time    timedelta64 ns    If this is your case  then you just need to add the columns    gt  df  Date     df  Time

User · Answer

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

User · Answer

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

User · Answer

Here is a one liner  to do it  You simply concatenate the two string in each of the column with a  quot   quot  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  quot   quot    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  quot  m  d  Y  I  M  S  p quot    Cheers   Happy Data Crunching

User · Answer

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

User · Answer

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

[python] Combine Date and Time columns using python pandas

Examples related to python

Examples related to pandas

Examples related to datetime

Examples related to series