0 votes
1 view
in Data Science by (16k points)

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!


 

1 Answer

0 votes
by (33.2k points)

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:

image

Hope this answer helps.

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

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...