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

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

Task ---|Duration(min)|
Task1---| 30--------|
Task2---| 60--------|
Task3---| 45--------|
Task4---|-----------|
Task5---| 30--------|

Desired df

Task ---|Duration(min)|--- |Due Time
Task1---| 30--------|-------| 5pm (now)
Task2---| 60--------|-------| 5:30pm
Task3---| 45--------|-------| 6:30pm
Task4---|-----------|-------| 7:15pm
Task5---| 30--------|-------| 7:45pm

Thanks so much Stackoverflow community!

CodePudding user response：

what's your main task or purpose for this post? let me have a guess :) write a program to calculate each task's start time(Due Time)?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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