How to only keep the fastest ride in pandas

user_13921816 0
Viewed 74

I have a dataframe like this :

 df = pd.DataFrame({'origin': ['town a', 'town a', 'town a','town a', 'town c', 'town c'],\
'destination': ['town b', 'town b', 'town b','town b','town b','town b'], \
'departure_hour': ['09:30', '09:45','10:00', '10:30','14:30', '15:30'],\
'arrival_hour': ['11:30', '10:50','12:00', '11:45','16:30', '19:30'],\
'date': ['29-09-2020', '29-09-2020','29-09-2020','29-09-2020','29-09-2020','29-09-2020']})

   origin destination departure_hour arrival_hour        date
0  town a      town b          09:30        11:30  29-09-2020
1  town a      town b          09:45        10:50  29-09-2020
2  town a      town b          10:00        12:00  29-09-2020
3  town a      town b          10:30        11:45  29-09-2020
4  town c      town b          14:30        16:30  29-09-2020
5  town c      town b          15:30        19:30  29-09-2020

We have ride between different cities with departure and arrival hour. I want to delete each row (trip) where we can take another trip later and arrival sooner.

So I want to have this result:

   origin destination departure_hour arrival_hour        date
1  town a      town b          09:45        10:50  29-09-2020
3  town a      town b          10:30        11:45  29-09-2020
4  town c      town b          14:30        16:30  29-09-2020
5  town c      town b          15:30        19:30  29-09-2020

I can do this with this method:

df['count_utility']=df.apply(lambda x : sum((df['departure_hour']>x.departure_hour)&(df['arrival_hour']<x.arrival_hour)&(df['origin']==x.origin)&(df['destination']==x.destination)&(df['date']==x.date)),axis=1)

and then apply a filter : df['count_utility']==0

But this method is too slow for my Dataframe which has 1 Million rows.

I think it could be faster using a group by on origin, destination and date but I don't know how to do that.

pandas dataframe python-3.x python performance
1 Answers

One idea with numpy broadcasting in custom function per groups in GroupBy.apply:

def f(x):
    a = x['departure_hour'].to_numpy()
    b = x['arrival_hour'].to_numpy()
    m = (a > a[:, None]) & (b < b[:, None])
    x['count_utility']  = m.sum(axis=1)
    return x

df = df.groupby(['origin','destination','date']).apply(f)
print (df)
   origin destination departure_hour arrival_hour        date  count_utility
0  town a      town b          09:30        11:30  29-09-2020              1
1  town a      town b          09:45        10:50  29-09-2020              0
2  town a      town b          10:00        12:00  29-09-2020              1
3  town a      town b          10:30        11:45  29-09-2020              0
4  town c      town b          14:30        16:30  29-09-2020              0
5  town c      town b          15:30        19:30  29-09-2020              0
user_29010020
Related