Load a Results File in pandas and Compute Overall and Per-Group Pass/Fail Rates

In a data scientist technical round, after a project discussion, you get a short pandas exercise: you are given a file, you read it into Python, and you do some simple analysis on it, such as computing pass and fail rates. The report does not describe the file. This version assumes a CSV of test-run results, `test_results.csv`, in which each row is meant to be one run of one test: | Column | Meaning | | --- | --- | | `run_id` | Identifier of the run; intended to be unique | | `test_name` | Name of the test that was run | | `suite` | Group the test belongs to | | `run_date` | Date of the run, `YYYY-MM-DD` | | `status` | Outcome of the run, such as `PASS`, `FAIL` or `SKIPPED` | A sample of the file: ```text run_id,test_name,suite,run_date,status 101,test_login,auth,2025-03-03,PASS 102,test_logout,auth,2025-03-03,FAIL 103,test_search,search,2025-03-03,pass 104,test_filters,search,2025-03-03, FAIL 105,test_export,reports,2025-03-04,PASS 106,test_login,auth,2025-03-04,SKIPPED 107,test_search,search,2025-03-04,PASS 108,test_export,reports,2025-03-04, 109,test_logout,auth,2025-03-04,PASS 109,test_logout,auth,2025-03-04,PASS ``` Your code should work on the full file, not only on this sample. ### Clarifying Questions - Do skipped runs and runs with a missing status count in the denominator of the pass and fail rates? - Should status labels be compared ignoring case and surrounding spaces? - Is `run_id` supposed to be unique, and should an exact duplicate row be treated as a logging artifact? - Should rates be computed per run, or per test using only each test's latest run? ### Part 1 — Load and check the file Read the file into a DataFrame. Check the column types, missing values, duplicate rows and the distinct status labels, and clean what needs cleaning. Report what you found and what you changed. ```hint Look before computing Print the distinct status values exactly as stored, including missing ones, before deciding what counts as a pass. ``` #### What This Part Should Cover - Loading with appropriate types, including parsing the date column - Finding and handling exact duplicate rows and conflicting duplicate identifiers - Normalizing status labels and deciding what to do with missing or unexpected values ### Part 2 — Pass and fail rates Compute the overall pass rate and fail rate. Then compute the pass rate for each suite, with the number of runs behind it, sorted from the lowest pass rate. State which runs are included in each denominator. ```hint Say what the denominator is A rate means little until you decide which rows are in its denominator; keep the counts of excluded rows next to the rates. ``` #### What This Part Should Cover - A clearly defined denominator and the counts of runs excluded from it - A grouped computation that keeps run counts alongside each rate - Sorting with a deterministic tie-break, and a caution about rates computed from very few runs ### What a Strong Answer Covers - Idiomatic pandas: vectorized string methods, boolean masks, and groupby with named aggregations - Data checks done before any rate is computed, with findings reported rather than silently fixed - Explicit decisions about skipped and missing outcomes, stated as assumptions - Correct numbers on the sample, and code that generalizes to the full file ### Follow-up Questions - How would you find flaky tests, those that both pass and fail across runs? - How would you show whether the pass rate is changing over time? - How would you compute the same suite-level rates in SQL? - The full file does not fit in memory. What would you change?