data-cleanup-and-analysis

A messy export becomes a clean table, a written record of every cleaning decision, a short report and one chart. The bar: every number in the report traces back to rows in the raw file, and every raw row is accounted for — in the clean table, or in the rejects file with its reason.

Know your reach. code.run is JavaScript in a small sandbox: no packages, no network, and it cannot write files. It reads with await tool("fs.read", { path }) (large files in pages, with offset and limit), stops after 30 seconds or 50 reads, and returns about 32,000 bytes at most. So compute inside it, return compact results, and save files yourself with fs.write and fs.append. A saved file tops out near 1 MB: split a bigger clean table into numbered parts and say so in the report.

Method

1. Find the file and leave it alone. Locate it with fs.list and read the first page with fs.read. The raw file is never edited; everything you make goes in a new folder beside it, such as cleaned/<file-name>/. For an .xlsx, fs.read returns the workbook as text: if that text is not a usable table (several sheets run together, merged header cells, garbled values), say so and ask the Commander for a CSV export of the one sheet. Do not assume Python exists; only if the WORKBENCH is placed, check with shell.exec (python --version) before relying on it.
2. Parse it properly. Write a character-by-character CSV reader in code.run: quoted fields may hold commas, line breaks and doubled quotes (""). Detect the delimiter (comma, semicolon, tab) from the header line, drop a leading byte-order mark, accept both line endings. Never split lines on commas. Make the first run return only the shape of what fs.read gave you and the first five parsed rows, then build on that.
3. Inspect before touching anything. One pass that returns a profile, not data: row count, column names, and per column the likely type, blanks, distinct values, smallest and largest, and five samples. Also count exact duplicate rows, rows with the wrong number of fields, and odd values: two date formats in one column, numbers with currency signs or thousands separators, text in a number column, stray spaces, the many spellings of "missing" (N/A, -, null), and impossible values (negative quantities, dates in the future).
4. Write the decisions down first. Save decisions.md: one numbered line per problem with how many rows it touches, the rule you will apply, and why. Where the data cannot settle it (is 03/04 March or April, is a blank zero or unknown, which duplicate wins), ask the Commander. If you cannot ask, take the cautious option — leave the value as it is or reject the row — and label it an assumption.
5. Clean by those rules only. Each raw row ends up in exactly one place: clean.csv, or rejects.csv with its original line number, the reason, and the row unchanged. Trim spaces, write dates as YYYY-MM-DD, numbers as plain decimals, and every "missing" marker as an empty cell. Return the output in chunks under the size limit; save the first with fs.write and the rest with fs.append. Quote any output field that holds a comma, quote or line break.
6. Reconcile against the raw file. Raw data rows = clean rows + rejected rows. For each key number column, the raw total = clean total + rejected total, or the gap is explained line by line. Do this with a fresh code.run that reads the saved files, not from what you remember writing.
7. Compute only what the data supports. Counts, sums, medians, ranges, group totals, change over time. Give the number of rows behind every figure. Show the median beside an average when values are lopsided. No average over a handful of rows, no trend from two points, no percentage without its base, and no causes: two columns moving together is all you can say.
8. Draw one chart for one question. Save a self-contained chart.html with inline SVG (or a plain .svg): no outside scripts, fonts or links. Bars for categories, a line for time, bars starting at zero, units on the axes, and a title that states the finding. Take the values from the saved clean.csv.
9. Report and hand over. Save report.md: what the file was, what was cleaned (each decision with its count), three to six findings in plain English with their numbers, what the data cannot tell, and open questions. Re-read each saved file, then name the report with deliverable_note.