在Pandas中按条件计算自上次有效休息后的时长

To calculate the hours since the last valid break (over 20 minutes) for each worker's shift, we'll track valid breaks and group consecutive shifts that don't have a valid break between them. Here's the step-by-step implementation:

Step 1: Convert datetime columns to proper datetime type #

First, parse string datetime values into Pandas datetime objects for time calculations:

import pandas as pd

df = pd.DataFrame({'Worker' : ['Alice','Alice','Alice', 'Bob','Bob','Bob'],
                          'Shift_start' : ['2022-01-01 10:00:00', '2022-01-01 13:10:00', '2022-01-01 15:45:00', '2022-01-01 11:30:00', '2022-01-01 13:40:00', '2022-01-01 15:20:00'],
                          'Shift_end' : ['2022-01-01 12:30:00', '2022-01-01 15:30:00', '2022-01-01 17:30:00', '2022-01-01 13:30:00', '2022-01-01 15:10:00', '2022-01-01 18:10:00']})

# Convert string dates to datetime objects
df['Shift_start'] = pd.to_datetime(df['Shift_start'])
df['Shift_end'] = pd.to_datetime(df['Shift_end'])

Step 2: Calculate break duration and flag valid breaks #

Compute the time between consecutive shifts and mark breaks longer than 20 minutes as valid. The first shift of each worker is treated as starting after a valid break (start of their workday):

# Calculate time between current shift start and previous shift end for each worker
df['break_duration'] = df.groupby('Worker')['Shift_start'].diff()

# Flag breaks over 20 minutes as valid; fill NaN (first shift) with True
df['valid_break'] = df['break_duration'] > pd.Timedelta(minutes=20)
df['valid_break'] = df.groupby('Worker')['valid_break'].fillna(True)

Step 3: Group shifts by valid breaks #

Use cumulative sum on the valid break flag to create groups of shifts that fall between valid breaks:

df['break_group'] = df.groupby('Worker')['valid_break'].cumsum()

Step 4: Compute hours since last valid break #

For each group, use the first shift's start time as the reference point. Calculate the time difference between the current shift start and this reference, convert to hours, and round to two decimals:

# Get the first shift start time for each worker's break group
df['reference_time'] = df.groupby(['Worker', 'break_group'])['Shift_start'].transform('first')

# Calculate hours since last valid break
df['Hours_since_break'] = (df['Shift_start'] - df['reference_time']).dt.total_seconds() / 3600
df['Hours_since_break'] = df['Hours_since_break'].round(2)

Step 5: Clean up (optional) #

Drop intermediate columns if you don't need them:

df = df.drop(['break_duration', 'valid_break', 'break_group', 'reference_time'], axis=1)

Final Result #

The resulting DataFrame matches your expected output:

Explanation #

• Valid Break Detection: Breaks over 20 minutes are marked as valid, including the start of each worker's workday.
• Grouping Shifts: The cumulative sum of valid break flags creates groups where each group starts immediately after a valid break.
• Reference Time: For each group, the first shift's start time is the reference point (when the worker resumed after a valid break).
• Hours Calculation: The time difference between the current shift start and the reference time gives the hours since the last valid break, rounded to two decimals.

内容的提问来源于stack exchange，提问作者datadatadata