Is there a way to iterate through the rows using the lambda function to add values from the a row to the following row?

user_19393711 0
Viewed 50

I have the following dataframe that shows the duration of a job taken by an employee as shown:

Date ID number Hour Job duration
14/07/2022 1123 12 240
14/07/2022 1123 13 0
14/07/2022 1123 14 0
14/07/2022 1123 15 0
14/07/2022 1123 16 70
14/07/2022 1123 17 0

I've iterated through the dataframe to "spread" the minutes along the hour using the following code:

for i in df.index:
    if df["Job duration"][i] > 60:
        x = df["Job duration"][i] - 60
        df["Job duration"][i] = 60
        df["Job duration"][i+1] = df["Job duration"][i+1] + x

This code works in a small dataset as shown below. In a large dataset however, this doesn't work and will take a long time computationally.

Date ID number Hour Job duration
14/07/2022 1123 12 60
14/07/2022 1123 13 60
14/07/2022 1123 14 60
14/07/2022 1123 15 60
14/07/2022 1123 16 60
14/07/2022 1123 17 10

Is there a method of using the lambda function in python to iterate through the rows of the "Job duration" column to speed the process? Thanks in advance.

data-science pandas lambda python
1 Answers

I'm not sure how fast this solution is, but you may just give it a shot:

import pandas as pd

def update_series(series):
    copied = series.copy()
    shifted = series.sub(60).shift(1).fillna(0).clip(lower=0)
    while max(shifted) > 0:
        series.update(shifted[shifted > 0])
        series.update(copied[copied > 60])
        shifted = shifted.shift(1).sub(60).fillna(0).clip(lower=0)
    series[series > 60] = 60
    return series

df = pd.DataFrame({"ID": [1123, 1123, 1123, 1123, 1123, 1123, 1123, 1123, 1123],
                   "Job duration": [0,210,0,0,0,0,70,0,0]})
df["Job duration"] = update_series(df["Job duration"])
user_196332540
Related