CODEWITHSUNDEEP
pythonpandasdatetime
James C
James C

Reputation: 55

Add time from a previous row and a duration from another row with Python

Using Python, I would like to calculate a column for a series of tasks called 'Due Time' that is based on the previous task time and duration.
Task 1 starts now (5pm in my example).
Task 2 starts 30 minutes after Task 1 because the duration of Task 1 was 30 minutes. Task 3 starts 60 minutes after Task 2 because the duration of Task 2 was 60 minutes. If there is no duration, I would like it to default to a duration of 30 minutes.

It won't let me embed pictures yet so here's an attempt at a chart:

Current df

Desired df

Thanks so much Stackoverflow community!

Upvotes: 1

Views: 269

Answers (3)

keramat
keramat

Reputation: 4543

Use:

df['Duration(min)'][df['Duration(min)']=='']=30
temp = pd.to_timedelta(df['Duration(min)'].astype(int).shift(fill_value=0).cumsum(), 'm')+pd.Timestamp.now()
df['due'] =temp.dt.strftime('%H:%M %p')

Output:

Task    Duration(min)   due
0   Task1   30  08:13 AM
1   Task2   60  08:43 AM
2   Task3   45  09:43 AM
3   Task4   30  10:28 AM
4   Task5   30  10:58 AM

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71689

Shift and fill the NaN values in Duration(min) with 30min, then calculate a cumulative sum and convert the column to timedelta, now add this with timestamp 17:00:00 to get the result:

s = df['Duration(min)'].fillna(30).shift(fill_value=0)
df['Due time'] = pd.to_datetime('17:00:00') + pd.to_timedelta(s.cumsum(), unit='m')

    Task  Duration(min)            Due time
0  Task1           30.0 2022-05-23 17:00:00
1  Task2           60.0 2022-05-23 17:30:00
2  Task3           45.0 2022-05-23 18:30:00
3  Task4            NaN 2022-05-23 19:15:00
4  Task5           30.0 2022-05-23 19:45:00

Upvotes: 1

user
user

Reputation: 234

I think this is what you're looking for. If not, let me know!

import datetime
import pandas as pd

def addTime(tm, minutes):
    fulldate = datetime.datetime(100, 1, 1, tm.hour, tm.minute)
    fulldate = fulldate + datetime.timedelta(minutes=minutes)
    return fulldate.time()

LEN_DATA = 10
duration = [30]*LEN_DATA

startTime =  datetime.datetime(100, 1, 1, 17, 0)
nextTime = datetime.datetime(100, 1, 1, 17, 0)
dueTime = [startTime.strftime('%H:%M')]*LEN_DATA
for i in range(1,LEN_DATA):
  nextTime = addTime(nextTime, duration[i-1])
  dueTime[i] = nextTime.strftime('%H:%M')

task = [f"Task {i}" for i in range(1,LEN_DATA+1)]
data = {"Task":task,
        "Duration (min)" : duration,
        "Due Time" : dueTime}
df = pd.DataFrame(data)

Task    Duration (min)  Due Time
Task 1  30  17:00
Task 2  30  17:30
Task 3  30  18:00
Task 4  30  18:30
Task 5  30  19:00
Task 6  30  19:30
Task 7  30  20:00
Task 8  30  20:30
Task 9  30  21:00
Task 10 30  21:30

Upvotes: 0

Related Questions