求助：基于Ref_id筛选Pandas DataFrame最新时间行的方法

Hey there! Let's break down how to get the exact result you need. The core idea is to prioritize rows with the latest Date_upload for each Ref_id, and if there's a tie, pick the one with the latest Date_download.

Step 1: Prepare Your Data & Convert Date Columns #

First, make sure your date columns are in datetime format—string sorting won't work correctly for timestamps. Let's start by building the DataFrame (I've adjusted the column order to match your sample data properly):

import pandas as pd

# Raw data matching your sample
data = {
    'value1': ['03-492-9', '01-782-8', '01-782-8', '73-80-12', '73-80-12'],
    'Date_upload': ['2018-04-07 17:17:00', '2018-05-07 14:21:00', '2018-04-20 19:38:00', '2018-04-03 18:54:00', '2018-04-03 18:54:00'],
    'Date_download': ['2018-05-10 19:06:03', '2018-05-10 19:06:10', '2018-05-11 20:06:18', '2018-05-10 19:06:23', '2018-05-11 19:06:28'],
    'Ref_id': [59844, 59000, 36741, 127500, 174000],
    'value2': [6950.0, 6500.0, 65000.0, 12500.0, 6199.0]
}

df = pd.DataFrame(data)

# Convert date columns to datetime type
df['Date_upload'] = pd.to_datetime(df['Date_upload'])
df['Date_download'] = pd.to_datetime(df['Date_download'])

Step 2: Sort & Keep Only the Rows You Need #

We have two straightforward ways to achieve your goal:

Method 1: Sort + Drop Duplicates

This is efficient and easy to read. We sort the DataFrame so the rows we want are first for each Ref_id, then keep only the first entry per Ref_id:

# Sort by Ref_id, then latest Date_upload, then latest Date_download
sorted_df = df.sort_values(
    by=['Ref_id', 'Date_upload', 'Date_download'],
    ascending=[True, False, False]
)

# Keep only the first row for each Ref_id
result_df = sorted_df.drop_duplicates(subset='Ref_id', keep='first')

Method 2: GroupBy After Sorting

If you prefer using groupby, you can sort the entire DataFrame by the date columns first, then grab the top row for each Ref_id:

# Sort by latest Date_upload first, then latest Date_download
sorted_df = df.sort_values(
    by=['Date_upload', 'Date_download'],
    ascending=[False, False]
)

# Group by Ref_id and keep the first row of each group
result_df = sorted_df.groupby('Ref_id', as_index=False).first()

Step 3: Check the Result #

If you print result_df, you'll get exactly the output you're expecting:

value1       Date_upload     Date_download  Ref_id  value2
0  03-492-9 2018-04-07 17:17:00 2018-05-10 19:06:03   59844   6950.0
1  01-782-8 2018-05-07 14:21:00 2018-05-10 19:06:10   59000   6500.0
4  73-80-12 2018-04-03 18:54:00 2018-05-11 19:06:28  174000   6199.0

Key Notes #

• Datetime Conversion: Never skip converting date strings to datetime—this ensures sorting works correctly for timestamps (e.g., "2018-05-07" will come after "2018-04-20" as expected).
• Sort Order: Using ascending=False for date columns puts the newest entries at the top, so drop_duplicates or groupby.first() grabs the right row.

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