trips came out of three regional systems and needs cleaning. Its columns are trip_id, city, fare and status. City names arrive in mixed case with stray spaces, fares arrive as text ("£12.50", "9", "£1,050.00", or empty), and some trips were exported twice with the same trip_id. Keep one row per trip_id, keep only completed trips (the status in any case, with stray spaces), and treat a missing fare as 0. Return result with the columns city (lower case, trimmed) and total_fare (rounded to 2), one row per city, from the highest total down.
The data
trips12 rows
| trip_idint64 | cityobject | fareobject | statusobject |
|---|
| 1001 | london | £12.50 | completed |
| 1002 | LONDON | 9 | Completed |
| 1003 | Manchester | £31.20 | completed |
| 1003 | Manchester | £31.20 | completed |
| 1004 | manchester | | completed |
| 1005 | London | £1,050.00 | completed |
and 6 more rows
Target output
| city | total_fare |
|---|
| london | 1071.5 |
| manchester | 31.2 |
| leeds | 29.5 |
| bristol | 11 |
What this question tests
Tests data cleaning as it happens on the job: text that should be numbers, names that should match, rows that were exported twice. Interviewers use it to see whether you inspect the data before aggregating it, because every one of these problems changes the totals without raising an error.
More Python interview questions