在Pandas中按Session分组取Datetime列最后值并计算预期结束时间

Hey there, let's tackle this Pandas grouping and calculation problem step by step.

First, let's lay out your original DataFrame clearly:

import pandas as pd

# 原始输入数据
data = {
    'Doctor': ['A']*12,
    'Start': ['2020-01-18 12:00:00', '2020-01-18 12:30:00', '2020-01-18 13:00:00', '2020-01-18 13:00:00',
              '2020-01-18 13:30:00', '2020-01-18 14:00:00', '2020-01-18 14:00:00', '2020-01-18 14:30:00',
              '2020-01-18 14:30:00', '2020-01-19 12:00:00', '2020-01-19 12:30:00', '2020-01-19 14:00:00'],
    'B_ID': [1,2,3,4,5,6,7,8,9,12,13,14],
    'Session': ['S1']*5 + ['S3']*4 + ['S2']*3,
    'Finish': ['2020-01-18 12:33:00', '2020-01-18 12:52:00', '2020-01-18 13:23:00', '2020-01-18 13:37:00',
               '2020-01-18 13:56:00', '2020-01-18 14:15:00', '2020-01-18 14:28:00', '2020-01-18 14:40:00',
               '2020-01-18 15:01:00', '2020-01-19 12:20:00', '2020-01-19 12:40:00', '2020-01-19 14:20:00']
}

df = pd.DataFrame(data)

Your goal is to group by the Session column, extract the last Start and Finish times for each group, then add an expected_finish column equal to the group's last_start plus 30 minutes. Here's how to do it:

Step 1: Convert time columns to datetime type #

First, we need to convert the string-formatted time columns to proper datetime objects—otherwise we can't perform time-based calculations:

df['Start'] = pd.to_datetime(df['Start'])
df['Finish'] = pd.to_datetime(df['Finish'])

Step 2: Group by Session and aggregate last values #

Use groupby() and agg() to pull the final Start and Finish entries for each Session:

grouped_df = df.groupby('Session').agg(
    last_start=('Start', 'last'),
    last_finish=('Finish', 'last')
).reset_index()

Step 3: Calculate the expected_finish column #

Add 30 minutes to each last_start using pd.Timedelta:

grouped_df['expected_finish'] = grouped_df['last_start'] + pd.Timedelta(minutes=30)

Final Output #

If you print grouped_df, you'll get exactly the result you wanted:

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