如何在Pandas中按customer_id和var_name去重并保留最后时间戳

Hey there! Let's tackle this problem where you need to remove duplicate records, retaining only the entry with the latest timestamp for each unique combination of customer_id and var_name.

Your Requirements & Example Data #

Goal: Delete duplicates, keep the last (latest timestamp) record for each customer_id + var_name pair.

Sample Input Data:

customer_id value var_name timestamp
1 1 apple 2018-03-22 00:00:00.000
2 3 apple 2018-03-23 08:00:00.000
2 4 apple 2018-03-24 08:00:00.000
1 1 orange 2018-03-22 08:00:00.000
2 3 orange 2018-03-24 08:00:00.000
2 5 orange 2018-03-23 08:00:00.000

Expected Output:

customer_id value var_name timestamp
1 1 apple 2018-03-22 00:00:00.000
2 4 apple 2018-03-24 08:00:00.000
1 1 orange 2018-03-22 08:00:00.000
2 3 orange 2018-03-24 08:00:00.000

Solution: Using SQL Window Functions #

The cleanest way to do this is with the ROW_NUMBER() window function, which lets us rank records in each group and filter for the top-ranked (latest) entry. Here's the query:

WITH ranked_records AS (
    SELECT 
        *,
        -- Rank records in each customer/var group by latest timestamp first
        ROW_NUMBER() OVER (
            PARTITION BY customer_id, var_name 
            ORDER BY timestamp DESC
        ) AS record_rank
    FROM your_table_name
)
-- Keep only the top-ranked (latest) record for each group
SELECT customer_id, value, var_name, timestamp
FROM ranked_records
WHERE record_rank = 1;

How This Works: #

• PARTITION BY customer_id, var_name: Splits your data into groups where every record in a group has the same customer_id and var_name (these are the fields we use to define duplicates).
• ORDER BY timestamp DESC: Sorts each group from the newest timestamp to the oldest.
• ROW_NUMBER(): Assigns a number to each record in the group—1 for the latest entry, 2 for the next, etc.
• The final SELECT filters out all records where record_rank != 1, leaving only the latest entry per group.

Alternative for Older Databases (No CTE Support) #

If your database doesn't allow Common Table Expressions (CTEs), use a subquery instead:

SELECT customer_id, value, var_name, timestamp
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id, var_name 
            ORDER BY timestamp DESC
        ) AS record_rank
    FROM your_table_name
) ranked_subquery
WHERE record_rank = 1;

Quick Notes: #

• Replace your_table_name with the actual name of your table.
• If multiple records have the exact same timestamp for a customer_id + var_name pair, ROW_NUMBER() will pick one arbitrarily. If you want to keep all tied records, use RANK() instead of ROW_NUMBER().

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