The Canteen Project Where the Merge Returned Nothing
Three students build an Informatics Practices project on the school canteen. They export two files for one term: orders.csv with 1,200 rows and columns order_id, item_code, qty, order_date; and menu.csv with 45 rows and columns item_code, item_name, price. Most item codes are numeric, such as 101 and 102, but a few are alphanumeric, such as S12 and C07. When they run pd.read_csv on both files and then pd.merge(orders, menu, on='item_code'), the merged frame is empty. After casting both columns with astype(str) they get 1,180 rows and cannot explain the missing 20. They also find 12 duplicated order_id values caused by re-entry, three blank prices, dates stored as plain text, and a qty column where the mean is 2.4 but the median is 2 because one order of 250 pieces was placed for a function. Their teacher insists the report must state how many orders were excluded and why, and must not describe a typical order using a number that no canteen day ever shows.
1. Diagnose the empty merge and suggest the most reliable fix, including where it should be applied.
Read the worked answer
read_csv infers each column separately. Because every value in orders.item_code is digits, it becomes int64. In menu.item_code, the presence of S12 and C07 forces the whole column to object (text). Merging an int64 key against an object key compares 101 with '101', which are different values; modern pandas raises a ValueError about merging on int64 and object columns, and older versions simply match nothing - hence the empty result.
Casting with astype(str) afterwards works here but is fragile: any stray space, a code read as 101.0, or a leading zero would still break the match. The reliable fix is at read time, before any comparison:
orders = pd.read_csv('orders.csv', dtype={'item_code': str}) and the same for menu.csv, plus skipinitialspace=True if the export is untidy. Choose string for the key because converting menu to numbers is impossible while S12 exists, and because item codes are identifiers, not quantities - arithmetic on them is meaningless. Fix the dtype at the source, verify with orders['item_code'].dtype and a sample of menu, and only then merge.
2. The students now get 1,180 rows instead of 1,200. Which join should they use, and how should the discrepancy be reported?
Read the worked answer
The default merge is an inner join: it keeps only keys found in both frames. The 20 missing orders refer to items that are no longer on menu.csv (discontinued or renamed items), so they have no name or price and are dropped.
The choice depends on the question. For revenue on current menu items, an inner join is defensible but it silently understates term revenue; for a complete order log it is wrong. The sound approach is pd.merge(orders, menu, on='item_code', how='left', indicator=True). A left join keeps all 1,200 orders, adds NaN for unmatched menu fields, and the _merge column labels each row 'both' or 'left_only'. Filtering merged['_merge'] == 'left_only' gives exactly the 20 problem orders, which can then be checked against the canteen's old price list.
Reporting matters as much as computing. The report should state: 1,200 orders downloaded, 20 (1.7%) had item codes absent from the current menu, therefore the price-based analysis covers 1,180 orders and total revenue is a lower bound. Dropping rows without saying so is the error an examiner will look for.
3. Which measure should the students quote for a typical order size, and what should they do about the three blank prices?
Read the worked answer
They should quote the median, 2 pieces. The mean of 2.4 is pulled upwards by the single 250-piece function order, which is an outlier, not a typical event; the mean describes no real canteen day. The median is resistant to that one extreme value. The correct presentation names both and explains the gap: 'median 2, mean 2.4, the mean inflated by one bulk order of 250'. They should also plot a histogram or box plot to show the skew rather than trust a single number, and confirm that 250 is a genuine order and not a data-entry error.
The three blank prices need a decision, not a default. Filling them with the median price of other items would invent revenue and quietly bias any total. Dropping those rows is acceptable only if the students report how many orders were affected and what share of revenue they represent. The best route is to trace the three item codes to the canteen's rate list and correct them at source; if that is impossible, exclude them and state in the limitations section that revenue excludes three items of unknown price. Any of these is defensible when disclosed; a silent fillna(0) is not.