Three Exports, One Grocery Store, and a Chart That Lies
A student team is helping a neighbourhood grocery store that sells at its till and on two delivery apps. The three CSV exports hold 2,140, 860 and 515 rows. Dates appear as 12/03/2024, 2024-03-12 and 12-Mar-24; product names differ in case and spelling ('Amul Butter 500g' vs 'amul butter 500 gm'). The unit_price column has 214 blanks, 38 negative values used for refunds, and a few 99,999 placeholders. One day's till export is duplicated entirely after a failed sync. Daily revenue normally runs between ₹4,000 and ₹61,000, but one festival Sunday shows ₹1,80,000. The team loads all rows into pandas, plots revenue in file order as a line chart and titles it 'Monthly sales trend'; the line jumps up and down dozens of times and the owner concludes that sales are unstable. They also call .mean() on the row-level revenue column and report an average daily revenue of ₹12,400.
1. Give an ordered cleaning plan for these three exports, stating for each problem whether you would delete, repair or keep the rows, and why.
Read the worked answer
Order matters: fix identity and time before touching money.
- Preserve raw copies and log every step. Write the cleaned table to a new file; never overwrite the exports.
- Remove the duplicated day. Do not run a blanket drop_duplicates - genuine identical small orders can exist. Match on an order id where present, otherwise on the full row plus the export timestamp window, then confirm that the row count for that date returns to a plausible value.
- Parse dates with an explicit format per source using pd.to_datetime(..., errors='coerce'), then count the resulting NaT. The 12/03/2024 style is ambiguous - verify day-first against one date you know independently (a festival or a known holiday) rather than guessing.
- Normalise product names. Lower-case, strip whitespace, unify units ('500g' and '500 gm' to one form), then map to a product master table with a lookup. Use fuzzy matching only to propose candidates for human review; fuzzy matching on money columns silently merges different products.
- Handle unit_price carefully. The 99,999 placeholders become NaN (documented sentinel). The 214 blanks: recover from the product master price in force on that date where possible, otherwise mark missing - never fill with 0 (destroys revenue) or the column mean (distorts every total). The 38 negatives are refunds: keep them, flag them as a transaction type, and let them reduce revenue. Taking absolute values would inflate sales.
- Validate: recompute quantity × price, check the day totals against the store's own end-of-day figure, and only then move to analysis.
2. Explain why the line chart is misleading and describe the chart or charts you would produce instead.
Read the worked answer
The x-axis is the row index, not time. Three exports have been concatenated in file order, so consecutive points can be from three different channels and from unrelated days; the line therefore measures the order in which the CSVs were loaded, not how sales changed. The duplicated day adds a spurious spike, and the festival Sunday appears as one isolated point with nothing to compare it against. Sharp up-and-down movement is guaranteed even if the business is perfectly stable.
Replace it with:
- Aggregate first. groupby date, sum revenue per day, then reindex over the full date range so days with zero sales appear as zero rather than vanishing.
- One time-series line of daily revenue with the festival Sunday annotated with a text label rather than silently included.
- Small multiples per channel (till, App A, App B) on the same time axis, so a drop in one channel is not hidden by growth in another.
- A 7-day rolling mean overlaid on the raw daily line to show trend versus day-to-day noise - the owner's question ('are sales unstable?') is about trend and weekly rhythm, which the raw series cannot answer.
Add a short note on the chart stating the cleaning performed, so the graph cannot be read as raw data.
3. The team reports an average daily revenue of ₹12,400. Is that number defensible, and how should typical daily revenue be summarised?
Read the worked answer
It is not defensible, for two separate reasons.
First, the wrong mean. They averaged the row-level revenue column across all 3,515 rows, so the answer depends on how many transactions happened to be exported, not on how many days there were. A day with 200 small orders counts 200 times. Daily revenue must be computed by aggregating per date first, then averaging over days.
Second, outlier sensitivity. Even after correct aggregation, the festival Sunday at ₹1,80,000 sits far above the ₹4,000–₹61,000 normal band. The mean is dragged upward by it; the median is not. If the median daily revenue is, say, ₹9,800 while the mean is ₹12,400, the store's ordinary day is much closer to ₹9,800 than to the headline figure.
Report the median and interquartile range as the primary summary, with the number of days covered. Do not delete the festival day - it is real and commercially the most interesting day of the year. Instead show the mean both with and without it, label it explicitly, and state the business conclusion ('a typical day earns around ₹9,800; festival days can be fifteen times that'). If the owner wants a planning number, the median is the right input, not the mean.