Turn one messy CSV into an analysis someone can rerun

The situation

A chain of pharmacies in Lahore exports its monthly sales as a single messy CSV: inconsistent branch names, prices stored as text with 'Rs', a few blank rows, and dates in two formats. The manager wants three numbers she can trust each month, and an analysis her junior can rerun next month without you.

Clean it with pandas, answer her questions, and make the notebook reproducible.

What you'll be able to show

• Know when to reach for NumPy and when for pandas
• Load a file and understand it with the standard first checks
• Filter, add columns and change values without breaking the row count
• Group and merge correctly, and make the analysis rerun the same way twice

What you need

• Python with pandas and numpy, in Jupyter, Colab or any notebook
• A messy sales CSV you build to the description below

Tasks

1. 1Build the mess on purpose

   Create a CSV of at least 200 sales rows: columns branch, date, product, quantity, price. Make it realistically dirty: three spellings of one branch, prices like 'Rs 450' as text, five blank rows, and dates in both 2026-03-01 and 01/03/2026 styles.
   A correct result: The CSV and a note listing the four kinds of mess you put in.

2. 2Look before you touch

   Load it and run the standard checks: shape, head, dtypes, describe, and isna().sum(). Write one sentence about the file based only on what they showed.
   A correct result: The output of the five checks and your one-sentence summary.

3. 3Clean it

   Drop the blank rows, standardise the branch names, parse both date formats into real dates, and convert price to a number. Keep the raw frame and clean into a copy. Check the row count after each step.
   A correct result: A clean dataframe, the row count at each step, and a short list of the fixes.

4. 4Vectorise one calculation

   Add a line_total column (quantity times price) with a vectorised operation, not a loop. Then show the same thing done with a Python loop and compare the time on the full file.
   A correct result: The vectorised column, the loop version, and the two timings.

5. 5Answer the three questions

   Group to answer: revenue by branch this month, the top five products by revenue, and the average basket value per branch. Merge in a branch-region lookup and confirm the row count did not change.
   A correct result: Three result tables and the before-and-after row count of the merge.

6. 6Make it rerun

   Add at least three assertions (for example no nulls in price, row count within an expected range, revenue positive). Restart the kernel and run everything top to bottom. Confirm the three numbers are identical.
   A correct result: The assertions, and a note confirming a clean top-to-bottom run gives the same numbers.

What to hand in

The notebook (with the raw CSV), showing the checks, the cleaning, the timed comparison, the three answers and the assertions, plus the three numbers written out for the manager.

How it is marked