按A分组筛选A、B日期绝对时间差最小的行

Let's walk through how to solve this using pandas—it's perfect for this kind of targeted data manipulation.

Original Dataset #

First, here's your input data formatted for clarity:

Step-by-Step Code Implementation #

Here's the code to get your desired output, with explanations along the way:

import pandas as pd

# Create the original DataFrame from your data
data = {
    'A': ['2002-01-16', '2002-01-16', '2002-01-16', '2002-01-16',
          '2002-04-27', '2002-04-27', '2002-04-27', '2002-04-27'],
    'B': ['2002-02-28', '2002-01-30', '2002-02-28', '2002-01-30',
          '2002-04-30', '2002-05-25', '2002-04-30', '2002-05-25'],
    'C': ['Jack', 'Helen', 'Peter', 'Jud', 'Nick', 'Wendy', 'Bryan', 'Sarah']
}
df = pd.DataFrame(data)

# Convert date columns from strings to datetime objects (critical for time calculations)
df['A'] = pd.to_datetime(df['A'])
df['B'] = pd.to_datetime(df['B'])

# Calculate absolute day difference between columns A and B
df['time_diff'] = (df['B'] - df['A']).dt.days.abs()

# Group by column A, find the minimum time difference per group, then filter matching rows
min_diff_per_group = df.groupby('A')['time_diff'].transform('min')
result_df = df[df['time_diff'] == min_diff_per_group].drop(columns='time_diff')

# Reset index to match your expected output format (optional but clean)
result_df = result_df.reset_index(drop=True)

print(result_df)

Final Output #

Running this code will produce exactly the result you're looking for:

Quick Breakdown #

• Datetime Conversion: We turn columns A and B into datetime objects so we can calculate valid time differences (strings won't work for this!).
• Time Difference Calculation: We compute the absolute number of days between A and B to get a numerical value we can compare.
• Group & Filter: Using transform('min') lets us fetch the smallest time difference for each group of A values. We then filter the original DataFrame to keep only rows where the time difference matches that group's minimum.

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