求助：基于多分组条件的Groupby+Transform时间差计算

Got it, let's break this down—GroupBy + Transform is such a powerful combo for grouped conditional logic, but I totally get how frustrating it can be to find exactly the right example that matches your specific use case. Let's walk through this step by step to solve your problem: calculating the time difference between Assigned and Approval statuses only when both exist in a group (grouped by Casenumber and Site), and adding that as a new column.

Step 1: Prepare Your Data (and Ensure Datetime Type) #

First, let's start with a sample dataset that mirrors your structure. The key here is making sure your timestamp column is converted to a datetime type—you can't calculate time differences on string values!

import pandas as pd

# Sample dataset matching your use case
data = {
    'Casenumber': ['CASE001', 'CASE001', 'CASE001', 'CASE002', 'CASE002', 'CASE003'],
    'Site': ['NYC', 'NYC', 'NYC', 'LA', 'LA', 'CHI'],
    'Status': ['Assigned', 'In Progress', 'Approval', 'Assigned', 'In Progress', 'Approval'],
    'Timestamp': ['2024-01-01 10:00:00', '2024-01-01 11:00:00', '2024-01-01 14:00:00',
                  '2024-01-02 09:00:00', '2024-01-02 10:00:00', '2024-01-03 15:00:00']
}

df = pd.DataFrame(data)
# Convert timestamp column to datetime (critical for time calculations)
df['Timestamp'] = pd.to_datetime(df['Timestamp'])

Step 2: Define a Group Processing Function #

We'll create a function that checks if a group has both Assigned and Approval statuses, calculates the time difference if they exist, and returns a Series that aligns with the original group's rows. This works seamlessly with transform because transform expects a result that matches the group's length (so it can broadcast the value back to every row in the group).

def calculate_assigned_to_approval_diff(group):
    # Check if both statuses exist in the group
    has_assigned = 'Assigned' in group['Status'].unique()
    has_approval = 'Approval' in group['Status'].unique()
    
    if has_assigned and has_approval:
        # Grab the first occurrence of each status (adjust this if you need earliest/latest)
        assigned_time = group[group['Status'] == 'Assigned']['Timestamp'].iloc[0]
        approval_time = group[group['Status'] == 'Approval']['Timestamp'].iloc[0]
        
        # Calculate time difference (here we use hours—swap to .days or .total_seconds() as needed)
        time_diff_hours = (approval_time - assigned_time).total_seconds() / 3600
        
        # Return the time difference for every row in the group
        return pd.Series([time_diff_hours] * len(group), index=group.index)
    else:
        # Return NaN for groups missing either status
        return pd.Series([pd.NA] * len(group), index=group.index)

Step 3: Apply with GroupBy + Transform #

Now we'll apply this function to our grouped DataFrame. transform will handle aligning the results back to the original DataFrame rows automatically.

# Add the new time difference column
df['Assigned_to_Approval_Hours'] = df.groupby(['Casenumber', 'Site']).transform(calculate_assigned_to_approval_diff)

Step 4: Check the Result #

Running the code above will give you this output:

Casenumber Site     Status           Timestamp  Assigned_to_Approval_Hours
0    CASE001  NYC   Assigned 2024-01-01 10:00:00                         4.0
1    CASE001  NYC  In Progress 2024-01-01 11:00:00                         4.0
2    CASE001  NYC   Approval 2024-01-01 14:00:00                         4.0
3    CASE002   LA   Assigned 2024-01-02 09:00:00                          NaN
4    CASE002   LA  In Progress 2024-01-02 10:00:00                          NaN
5    CASE003  CHI   Approval 2024-01-03 15:00:00                          NaN

Customization Tips #

• Multiple Status Occurrences: If a group has multiple Assigned or Approval entries, replace .iloc[0] with .min() or .max() to get the earliest/latest timestamp instead of the first one.
• Time Difference Units: Adjust the calculation to use .days for full days, .total_seconds() for seconds, or any other datetime delta attribute you need.
• Targeted Rows Only: If you only want the time difference to appear on rows with Assigned or Approval (not In Progress), modify the return logic in the function to check the row's status:

  return group['Status'].apply(lambda x: time_diff_hours if x in ['Assigned', 'Approval'] else pd.NA)
  

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