Efficient calculation of timedeltas between two series where different cases can occur

user_9069554 0
Viewed 53

I am working with a dataset of about 1 million rows containing arrival and departure data, transforming raw data sourced from an HTML table and I've run into a problem with calculating time differences between scheduled and actual departures. There appear to be four different cases which are shown in the table below for time differences and I need a quick way to calculate timedeltas, converted to minutes, that can take into account all of them at once. The one I am using currently can handle 3 of the 4 cases correctly .

The data has two date/time columns in the format shown in the below table, and the third column included is the result of calculating timedeltas with the current technique (code later in this post).

|          Sch Dp          | Act Dp  |  Diff  |
|--------------------------|---------|--------|
| 02/24/2014 10:22 PM (Mo) |         | NaN    |
| 02/25/2014 10:22 PM (Tu) | 10:24PM | 2.0    |
| 02/26/2014 10:22 PM (We) | 12:53AM | 151.0  |
| 11/02/2010 4:36 AM (Tu)  | 4:13AM  | 1417.0 |

The four main cases illustrated above:

Row 1: (Missing Data Case) actual departure column is missing data due to a cancellation (indicated elsewhere in df)

Row 2: (Normal Case) actual departure occurs on the same day as scheduled departure, on time or later than scheduled

Row 3: (Depart Next Day Case) actual departure occurs later but the date of departure changes without formal indication

Row 4: (Depart Before Scheduled Case) actual departure occurs a few minutes before the scheduled time

The problem I am running into is that since there is no date given in the actual departure column, it is more complicated to determine the time differences for cases 3 and 4. I currently have the below code operating on the raw data loaded into a dataframe, and it works for cases 1-3 but not 4.

sch_time =  pd.to_datetime(df['Sch Dp'], format='%I:%M %p', exact=False, errors='coerce')
act_time = pd.to_datetime(df['Act Dp'], format='%I:%M%p', exact=False, errors='coerce')
    
time_diff = pd.to_timedelta(act_time - sch_time,  errors='coerce') 
time_diff = time_diff - pd.to_timedelta(time_diff.dt.days, unit='d')
new_df['Diff'] =(60 * (time_diff.dt.days * 24 + time_diff.dt.seconds // 3600) + (time_diff.dt.seconds % 3600) // 60)

Is there a way to handle something like this in a relatively simple and computationally efficient way? I could probably write a function to do this and use pd.series.apply(), but from what I have read and experienced while trying to resolve this, .apply() is very slow and is included out of convenience but shouldn't be the go-to solution. Since my dataframe has almost 1 million rows, I don't foresee .apply() being optimal or even remotely quick. My guess is that there must be a way to do this more efficiently.

(Wondering if there is something possible with strategic math calculations, perhaps modulus or absolute values, but experimentation has been yielding wrong results.)

UPDATE: Since I haven't received a response yet, I wrote this (functional but not graceful) function but I'm having trouble figuring out how to use it with .apply(). It takes into account the individual columns (I split "Sch Dp" into a full date+time (no day of week), date only, time only, and also convert all the datatypes to proper formats.

Could anyone advise?

def calc_diff(full_sched, sched_date, sched_time, act_time):
    if pd.isnull(act_time):
        return np.nan
    else:
        if sched_time > pd.to_datetime('12:00:00').time():
            act_datetime = pd.Timestamp.combine(sched_date, act_time)
            if act_datetime < full_sched:
                act_datetime = pd.to_datetime(act_datetime) + pd.Timedelta(1, unit='day')
        else: 
            act_datetime = pd.Timestamp.combine(sched_date, act_time) 
        time_diff = pd.to_timedelta(act_datetime - full_sched) 
        time_diff = time_diff.total_seconds() // 60
    return time_diff
pandas timedelta dataframe datetime python
1 Answers

If I understand you problem correctly, you'll need to set a range of timedelta within which you expect the delay (negative / too early departure as well as positive / late departure). You can use that to determine if a day should be added to the "actual departure" column (as in your example, row 3) or not (as in your example, row 4).

# departure, slice of the day name and to datetime...
df['dep'] = pd.to_datetime(df['Sch Dp'].str[:-4])

# use date of scheduled departure, and time from actual departure.
# set specific format and errors=coerce so that the empty string gives NaT.
df['adep'] = pd.to_datetime(df['dep'].dt.date.astype(str)+ " "+df['Act D'], 
                            format='%Y-%m-%d %I:%M%p', errors='coerce')

# set the expected delay, derive a boolean mask from that.
max_expected_delay = pd.Timedelta(hours=4)
delta = df['adep']-df['dep']
m_late = (delta < max_expected_delay) & (max_expected_delay*-1 > delta)
m_early = (delta*-1 < max_expected_delay) & (max_expected_delay*-1 > delta*-1)

# add (or remove) a day if actual departure falls within expected range
df.loc[m_late, 'adep'] += pd.Timedelta(days=1)
df.loc[m_early, 'adep'] -= pd.Timedelta(days=1)

df['diff[min]'] = (df['adep']-df['dep']).dt.total_seconds()/60

#                      Sch Dp    Act D  ...                adep diff[min]
# 0  02/24/2014 10:22 PM (Mo)           ...                 NaT       NaN
# 1  02/25/2014 10:22 PM (Tu)  10:24PM  ... 2014-02-25 22:24:00       2.0
# 2  02/26/2014 10:22 PM (We)  12:53AM  ... 2014-02-27 00:53:00     151.0
# 3   11/02/2010 4:36 AM (Tu)   4:13AM  ... 2010-11-02 04:13:00     -23.0
# 4  11/02/2010 12:13 AM (Tu)  11:56PM  ... 2010-11-01 23:56:00     -17.0
user_101974180
Related