Keep the latest row per key

Pandas data engineering interview problem. Difficulty: beginner. Pattern: Deduplication. About 10 minutes. Free to practice.

Keep only the most recent record of each customer. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

A table sometimes holds several versions of the same record. Here each customer can appear many times with different updated_at dates, and you want only the newest version. df has customer_id, name, email and updated_at (a date as text in YYYY-MM-DD format). Return 1 row for each customer_id: the row with the latest updated_at. Keep all 4 columns, sort by customer_id, and give the result a clean index (0, 1, 2, ...). The editor builds df and ends with result = df. Change the last line to build your answer. Remember that drop_duplicates keeps rows by their current order, so sort first.

Requirements

• Keep exactly 1 row for each customer_id.
• The kept row has the latest updated_at.
• Keep customer_id, name, email, updated_at, sorted by customer_id.
• Use reset_index(drop=True) and store the DataFrame in result.

Constraints

• updated_at is text in ISO format, which sorts correctly as text.
• Sort by customer_id and updated_at before you drop the duplicates.
• Use drop_duplicates("customer_id", keep="last") after sorting.

Examples

Input: df customer_id | name | email | updated_at 1 | Ann | [email protected] | 2024-03-01 2 | Bo | [email protected] | 2024-03-10 1 | Ann | [email protected] | 2024-01-01 3 | Cy | [email protected] | 2024-01-15 1 | Ann | [email protected] | 2024-02-01 2 | Bo | [email protected] | 2024-02-01 Output: customer_id | name | email | updated_at 1 | Ann | [email protected] | 2024-03-01 2 | Bo | [email protected] | 2024-03-10 3 | Cy | [email protected] | 2024-01-15 Customer 1's newest version is the March one even though it arrives first; customer 2's newest is 10 March; customer 3 has only 1 row.

Topics: lakebench, pandas, drop_duplicates, latest, sort.

More interview problems · All interview problems · Learn data engineering