Pandas time conversion and elapsed time

user_15302651 0
Viewed 240

I have a pandas dataframe that has columns:

['A'] of departure time (listed as integer ex: 700 or 403 which is 7:00 and 4:03);

['B'] of elapsed time (listed as integer ex: 70 or 656 which is 70 mins and 656 mins);

['C'] of arrival time (listed as integer: 1810 and 355 which is 18:10 and 03:55).

I need to find a way to develop a new column ['D'] with a boolean value that returns True if the arrival is on the following day and False if arrival is on the same day.

I thought of accessing the -2 index of column A to convert hour to minute and then add the remainder minutes to normalize the values but not sure how to do that, or if there's a simpler way to find this. The idea behind this would be to get total minutes elapsed from moment the day started and if exceeds total minutes in a day, then I'd have my answer but unsure if this would work.

jupyter pandas numpy time python
1 Answers

Similar to the method you outlined, you can accomplish the task by converting the integers in column A to a 24-hour datetime (starting from 1900-01-01), adding the integer number of minutes from column B as a timedelta and then check to see if the result is still in day 1 of the month. As a sanity check, I made sure the last row should return True.

You can probably combine these steps without creating a new column, but I think the code is more readable this way.

import numpy as np
import pandas as pd
import datetime as dt

df = pd.DataFrame({
    'A':[700,403,2359],
    'B':[70,656,2],
    'C':[810,1059,1]
})

# convert to string, add leading zeros, then convert column A to datetime
df['arrival'] = pd.to_datetime(df['A'].astype(str).str.zfill(4), format='%H%M') + pd.to_timedelta(df['B'],'m')

# check if you are on day 1 of the month still
df['D'] = np.where(df.arrival.dt.day > 1, True, False)

Output:

      A    B     C             arrival      D
0   700   70   810 1900-01-01 08:10:00  False
1   403  656  1059 1900-01-01 14:59:00  False
2  2359    2     1 1900-01-02 00:01:00   True
user_53270680
Related