Time difference within group by objects in Python Pandas

Question

I have a dataframe that looks like this:

from    to   datetime           other

-------------------------------------------------

11      1 2016-11-06 22:00:00          -

11      1 2016-11-06 20:00:00          -

11      1 2016-11-06 15:45:00          -

11      12 2016-11-06 15:00:00          -

11      1 2016-11-06 12:00:00          -

11      18 2016-11-05 10:00:00          -

11      12 2016-11-05 10:00:00          -

12      1 2016-10-05 10:00:59          -

12      3 2016-09-06 10:00:34          -

I want to groupby "from" and then "to" columns and then sort the "datetime" in descending order and then finally want to calculate the time difference within these grouped by objects between the current time and the next time. For eg, in this case, I would like to have a dataframe like the following:

from    to time diff in minutes                                       

11      1     120

11      1     255

11      1     225

11      1     0

11      12     300

11      12     0

11      18     0

12      1     25

12      3     0

I can't get my head around figuring this out!! Is there a way out for this? Any help will be much much appreciated!! Thank you so much in advance!

 

Answer 1

You can use the following functions in the Pandas library to solve this problem:

DataFrame.assign

It assigns new columns to a DataFrame, returning a new object with all the original columns in addition to the new ones. 

DataFrame.sort_values

Sort by the values along column or row.

For example:

df.assign(

    timediff=df.sort_values(

        'datetime', ascending=False

    ).groupby(['from', 'to']).datetime.diff(-1).dt.seconds.div(60).fillna(0))

Output:

Hope this answer helps.

If you want to be build successful data science career then enroll for best data science certification.