Second Most Recent Service per Vehicle

PandasHardSenior level~10 min

Problem

One DataFrame logs service jobs done on vehicles.

`servicevisit` (`servicevisit.csv`): `plate`, `job`, `opened_on`, `closed_on`. Each row is a service job on a vehicle, with the day it opened and closed. `(plate, opened_on)` is unique, and each vehicle's `opened_on` dates are distinct.

For each vehicle (`plate`), return its **second most recent** service visit, judged by `opened_on` (a later `opened_on` is more recent). If a vehicle has **only one** visit, return that single visit instead.

Return the columns `plate`, `job`, `opened_on`, `closed_on`. Rows may be in any order.

**Example**

A vehicle with visits opened on 2023-01-10, 2023-03-01 and 2023-05-04 returns the 2023-03-01 visit (its second-newest). A vehicle with a single visit returns that one row.

Input data

Example rows — the live problem includes the full dataset.

servicevisit

plate	job	opened_on	closed_on
AB-12	oil	2023-01-10	2023-01-10
AB-12	brakes	2023-03-01	2023-03-02
AB-12	tires	2023-05-04	2023-05-04
ZX-99	wash	2023-02-01	2023-02-01

Expected output

Your answer should return 2 rows with the columns plate, job, opened_on, closed_on.

Starter code (Pandas (Python))

import pandas as pd

def second_most_recent_service(servicevisit: pd.DataFrame) -> pd.DataFrame:
    # Your code here
    return servicevisit

Solve this Pandas question free

Write Pandas (Python) and run it instantly in your browser — even on your phone. No signup needed to try.

Solve it now → Create free account

Solution & explanation

Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.