Daily cancellation rate excluding banned clients and drivers
trips(id, client_id, driver_id, city_id, status, request_at) with status in completed, cancelled_by_driver, cancelled_by_client; users(users_id, banned, role). For each day from 2013-10-01 to 2013-10-03, return the cancellation rate among trips where both the client and the driver are unbanned: cancelled such trips / total such trips that day, rounded to 2 decimals.
import pandas as pd
def cancellation_rate(trips: pd.DataFrame, users: pd.DataFrame) -> pd.DataFrame:
# your code hereWrite the implementation.
Filter to trips where both parties are unbanned, then take the daily mean of a cancelled flag. Merge users twice — once on client_id, once on driver_id — to attach each party's ban status, keep rows where neither is banned, restrict to the date range, and group by day.
- ✗Checking only the client's ban status, not the driver's
- ✗Skipping the ban filter entirely
- ✗Counting only one cancellation type as cancelled
- →Why are two separate merges with users needed here?
- →How does str.startswith handle both cancellation types at once?
Attach both parties' ban status via two merges, keep trips where neither is banned, then take the daily mean of the cancelled flag.
import pandas as pd
def cancellation_rate(trips: pd.DataFrame, users: pd.DataFrame) -> pd.DataFrame:
ban = users.set_index('users_id')['banned']
t = trips[
(trips['client_id'].map(ban) == 'No') &
(trips['driver_id'].map(ban) == 'No') &
(trips['request_at'].between('2013-10-01', '2013-10-03'))
].copy()
t['cancelled'] = t['status'].str.startswith('cancelled')
out = (t.groupby('request_at')['cancelled'].mean().round(2)
.reset_index(name='cancellation_rate'))
return outMapping client_id and driver_id through the ban lookup enforces that both parties are unbanned. status.str.startswith('cancelled') flags both cancelled_by_driver and cancelled_by_client, so the per-day mean is the cancellation rate. For 2013-10-01..03 the rates come out 0.33, 0.00, 0.50.
