Calculate Pandas DataFrame Time Difference Between Two Columns in Hours and Minutes

Question

I have two columns  fromdate and todate  in a dataframe  import pandas as pd  data     todate    pd Timestamp  2014-01-24 13 03 12 050000    pd Timestamp  2014-01-27 11 57 18 240000    pd Timestamp  2014-01-23 10 07 47 660000              fromdate    pd Timestamp  2014-01-26 23 41 21 870000    pd Timestamp  2014-01-27 15 38 22 540000    pd Timestamp  2014-01-23 18 50 41 420000      df   pd DataFrame data   I add a new column  diff  to find the difference between the two dates using df  diff     df  fromdate   - df  todate    I get the diff column  but it contains days  when there s more than 24 hours                     todate                fromdate                   diff 0 2014-01-24 13 03 12 050 2014-01-26 23 41 21 870 2 days 10 38 09 820000 1 2014-01-27 11 57 18 240 2014-01-27 15 38 22 540 0 days 03 41 04 300000 2 2014-01-23 10 07 47 660 2014-01-23 18 50 41 420 0 days 08 42 53 760000  How do I convert my results to only hours and minutes  i e  days are converted to hours

User · Accepted Answer

Pandas timestamp differences returns a datetime.timedelta object. This can easily be converted into hours by using the *as_type* method, like so

import pandas
df = pandas.DataFrame(columns=['to','fr','ans'])
df.to = [pandas.Timestamp('2014-01-24 13:03:12.050000'), pandas.Timestamp('2014-01-27 11:57:18.240000'), pandas.Timestamp('2014-01-23 10:07:47.660000')]
df.fr = [pandas.Timestamp('2014-01-26 23:41:21.870000'), pandas.Timestamp('2014-01-27 15:38:22.540000'), pandas.Timestamp('2014-01-23 18:50:41.420000')]
(df.fr-df.to).astype('timedelta64[h]')

to yield,

0    58
1     3
2     8
dtype: float64

User · Answer

How do I convert my results to only hours and minutes  The accepted answer only returns days   hours  Minutes are not included    To provide a column that has hours and minutes  as hh mm or x hours y minutes  would require additional calculations and string formatting  This answer shows how to get either total hours or total minutes as a float  using timedelta math  and is faster than using  astype  timedelta64 h    Pandas Time Deltas User Guide Pandas Time series   date functionality User Guide python timedelta objects  See supported operations  The following sample data is already a datetime64 ns  dtype  It is required that all relevant columns are converted using pandas to datetime     import pandas as pd    test data from OP  with values already in a datetime format data     to date    pd Timestamp  2014-01-24 13 03 12 050000    pd Timestamp  2014-01-27 11 57 18 240000    pd Timestamp  2014-01-23 10 07 47 660000              from date    pd Timestamp  2014-01-26 23 41 21 870000    pd Timestamp  2014-01-27 15 38 22 540000    pd Timestamp  2014-01-23 18 50 41 420000        test dataframe  the columns must be in a datetime format  use pandas to datetime if needed df   pd DataFrame data     add a timedelta column if wanted  It s added here for information only   df  time delta with sub     df from date sub df to date     also works df  time delta      df from date - df to date     create a column with timedelta as total hours  as a float type df  tot hour diff      df from date - df to date    pd Timedelta hours 1     create a colume with timedelta as total minutes  as a float type df  tot mins diff      df from date - df to date    pd Timedelta minutes 1     display df                    to date               from date             time delta  tot hour diff  tot mins diff 0 2014-01-24 13 03 12 050 2014-01-26 23 41 21 870 2 days 10 38 09 820000      58 636061    3518 163667 1 2014-01-27 11 57 18 240 2014-01-27 15 38 22 540 0 days 03 41 04 300000       3 684528     221 071667 2 2014-01-23 10 07 47 660 2014-01-23 18 50 41 420 0 days 08 42 53 760000       8 714933     522 896000  Other methods  An item of note from the podcast in Other Resources   total seconds   was added and merged when the core developer was on vacation  and would not have been approved   This is also why there aren t other  total xx methods       convert the entire timedelta to seconds   this is the same as td   timedelta seconds 1   df from date - df to date  dt total seconds    out   0    211089 82 1     13264 30 2     31373 76 dtype  float64    get the number of days  df from date - df to date  dt days  out   0    2 1    0 2    0 dtype  int64    get the seconds for hours   minutes   seconds  but not days   note the difference from total seconds  df from date - df to date  dt seconds  out   0    38289 1    13264 2    31373 dtype  int64  Other Resources  Talk Python to Me  Episode  271  Unlock the mysteries of time  Python s datetime that is   Timedelta begins at 31 minutes As per Python core developer Paul Ganssle and python dateutil maintainer   Use  df from date - df to date    pd Timedelta hours 1  Don t use  df from date - df to date  dt total seconds     3600  pandas Series dt total seconds  dt accessor       Real Python  Using Python datetime to Work With Dates and Times The dateutil module provides powerful extensions to the standard datetime module     timeit test import pandas as pd    dataframe with 2M rows data     to date    pd Timestamp  2014-01-24 13 03 12 050000    pd Timestamp  2014-01-27 11 57 18 240000      from date    pd Timestamp  2014-01-26 23 41 21 870000    pd Timestamp  2014-01-27 15 38 22 540000     df   pd DataFrame data  df   pd concat  df    1000000  reset index drop True     timeit  df from date - df to date    pd Timedelta hours 1   out   43 1 ms    1 05 ms per loop  mean    std  dev  of 7 runs  10 loops each     timeit  df from date - df to date  astype  timedelta64 h     out   59 8 ms    1 29 ms per loop  mean    std  dev  of 7 runs  10 loops each

User · Answer

This was driving me bonkers as the  astype   solution above didn t work for me   But I found another way   Haven t timed it or anything  but might work for others out there   t1   pd to datetime  1 1 2015 01 00   t2   pd to datetime  1 1 2015 03 30    print pd Timedelta t2 - t1  seconds   3600 0      if you want hours   Or   print pd Timedelta t2 - t1  seconds   60 0      if you want minutes

[python] Calculate Pandas DataFrame Time Difference Between Two Columns in Hours and Minutes

Examples related to python

Examples related to pandas

Examples related to datetime

Examples related to python-datetime