如何按行名分组（groupby）计算行间时间差并提取最新记录？

Hey there! Let's walk through exactly how to solve this problem with pandas. I'll break it down step by step with example code so you can follow along easily.

First, let's start with sample data that matches your scenario (multiple rows per id, with different change dates). We'll also convert the date column to a datetime type—this is critical for calculating date differences correctly.

import pandas as pd

# Sample data matching your use case
data = {
    'id': ['A', 'A', 'B', 'B', 'B'],
    'change_date': ['2023-01-01', '2023-01-10', '2023-02-05', '2023-02-15', '2023-02-20'],
    'value': [10, 20, 5, 15, 25]
}
df = pd.DataFrame(data)

# Convert the date column to datetime format
df['change_date'] = pd.to_datetime(df['change_date'])

To calculate the days between consecutive rows within each group, we first need to sort the data by id and change_date (oldest to newest). This ensures the diff() function (used next) compares each row to the immediately preceding row in the same group.

# Sort by id, then by change_date (ascending = oldest first)
df_sorted = df.sort_values(by=['id', 'change_date'])

Now we'll use groupby() to group rows by id, then apply diff() to the change_date column to get the time difference between each row and the previous one. We'll convert this difference to days and store it in a new column.

# Calculate days between current row and the last row in the same group
df_sorted['days_since_last_change'] = df_sorted.groupby('id')['change_date'].diff().dt.days

Finally, we'll group again by id and use last() to keep only the most recent row (since we sorted earlier, the last row in each group is the newest date). We'll also reset the index to get a clean DataFrame.

# Keep only the latest row for each id
result = df_sorted.groupby('id').last().reset_index()

What the Result Looks Like #

If you print result, you'll get exactly what you asked for—only the newest record per group, with the days since the last change as a new column:

Optional: Handle Groups with Only One Row #

If some id groups have only one row, the days_since_last_change column will show NaN (since there's no previous row to compare). You can fill these with a default value like 0 if needed:

result['days_since_last_change'] = result['days_since_last_change'].fillna(0)

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