{"course":{"slug":"ai-for-small-business","title":"AI for a Small Business","url":"https://addaly.com/learn/ai-for-small-business","level":"beginner","track":"work"},"lesson":{"slug":"cleaning-before-counting","title":"Why cleaning is most of the job","url":"https://addaly.com/learn/ai-for-small-business/cleaning-before-counting","position":69,"of":88,"minutes":9,"answer":"Counts are only as meaningful as the categories underneath them, and a category spelled four ways is four categories; so the work of making numbers comparable is the analysis, not preparation for it.","keyIdea":"Counts are only as meaningful as the categories underneath them, and a category spelled four ways is four categories; so the work of making numbers comparable is the analysis, not preparation for it."},"body":{"format":"markdown","text":"## The unglamorous majority\n\nPeople who do this for a living put the share of time spent preparing data somewhere between 60 and 80%. That is not a failing of their tools. It is what the job is, and it is worth knowing before you start, so that the afternoon you spend fixing dates does not feel like a detour.\n\n## The five faults you will actually meet\n\n**Duplicates.** The same order exported twice, the same customer under two records. Check by counting rows and counting distinct identifiers; if they differ, find out why before doing anything else.\n\n**Dates as text.** Your spreadsheet sees `03/04/2026` and applies a regional guess. Half the world reads that as March, half as April. Where the day is above 12 the guess is forced and correct; where it is below 12 it is silent and might be wrong. The result is a dataset where some dates are right and some are six months out, and nothing looks broken. Convert dates to `YYYY-MM-DD` as the first operation on any file.\n\n**Numbers as text.** A currency symbol, a comma, a trailing space, and the column will not sum. Worse, it sums the subset that parsed, which produces a plausible total that is wrong by exactly the rows you did not notice.\n\n**Categories that multiply.** `Repair`, `repair`, `Repairs`, `Repair `, `REPAIR`. Five categories to a computer, one to you. This is the fault that quietly ruins more small-business analyses than any other, because every count and every chart splits along lines you cannot see.\n\n**Missing values.** An empty cell, a zero and the word `N/A` are three different things, and treating a blank as a zero turns \"we did not record this\" into \"this was nothing\", which will drag every average you calculate towards a number that never happened.\n\n> Figure: The five faults in every real export. Layers, top to bottom: Duplicates (The same order exported twice, one customer under two records. Count rows, count distinct identifiers, and find out why they differ before anything else.); Dates as text (03/04/2026 is March to half the world and April to the other half. Above the twelfth the guess is forced and correct; below it the guess is silent and may be six months out.); Numbers as text (A currency symbol, a comma, a trailing space, and the column sums only the rows that parsed. The total is plausible and wrong by exactly the rows you did not notice.); Categories that multiply (Repair, repair, Repairs, REPAIR. Five categories to a computer and one to you, splitting every count and every chart along lines you cannot see.); Missing values (A blank, a zero and the word N/A are three different things. Treating a blank as a zero turns we did not record this into this was nothing.). Sixty to eighty per cent of the work is here, and it is the analysis rather than preparation for it. Never clean in place: keep the raw export, write the cleaning down as steps you can rerun, and put the result in a new file.\n\n## Where a model helps, and where it must not\n\nIt helps with categories. Give it the 340 distinct values in your product column and ask which are the same thing, and it will group them well: that is meaning-matching, which is what these models are good at. It also writes the cleaning code faster than you will.\n\nTwo hard limits, both from module six.\n\nIt must not do the arithmetic. Totals, counts and averages come from the spreadsheet or the code, never from the model reading the data and reporting a figure.\n\nAnd it must not decide what a blank means. Only you know whether an empty delivery-charge cell means free delivery, collection, or a till operator who skipped the field. Guessing here invents a business fact, and every number downstream inherits it.\n\n## The tools, all free\n\n**LibreOffice Calc** does find-and-replace, text-to-columns and deduplication, and opens the CSV your platform exports without a licence.\n\n**OpenRefine** is free, open source, and built exactly for the categories problem. Its clustering feature finds variant spellings automatically and is the fastest route from 340 product names to 90 real ones.\n\n**Python with pandas** is free and handles files a spreadsheet will not open. Roughly fifteen lines does the whole job, and a model will write those fifteen lines correctly if you describe your columns.\n\nPaid tools exist and do the same work with nicer screens. For the volumes in this course they add nothing a free tool does not already do.\n\n## Keep the original\n\nNever clean in place. Keep the raw export untouched, do the cleaning as a script or a documented sequence of steps, and write the result to a new file.\n\nTwo reasons, and the second is the one that gets people. You will need to rerun this next month, and a remembered sequence of manual edits cannot be rerun. And when a figure looks wrong, the only way to find out whether the fault is in the data or the cleaning is to go back to what came out of the till.\n\n## The test that catches most of it\n\nBefore analysing, check four things and write the answers down.\n\nHow many rows, and how many distinct customers, orders or jobs. What the earliest and latest dates are: this catches the 1970 and 2087 rows that every export contains. What the smallest and largest amounts are, which catches the negative refunds and the misplaced decimal point. And how many distinct values each category column has, which catches the multiplying-categories problem immediately.\n\nFour questions, ten minutes, and they find something in most real datasets. A figure produced without them is not wrong, exactly. It is unexamined, which is a different problem and usually a longer-lived one."},"check":{"question":"A workshop's job export has a `type` column with 61 distinct values, which the owner knows should be about 12. She uses a model to group them and it returns 12 clean categories. What must she check before trusting any counts?","options":[{"text":"That the model has not invented a category name that did not appear in the original data, since generated labels can be plausible but absent from the source.","correct":false,"tempting":"Worth a glance, and a renamed category still counts the same rows, so it does not change any total."},{"text":"That every one of the 61 original values was assigned somewhere, since a value silently dropped removes its rows from every count.","correct":true},{"text":"That the 12 categories match the ones used in her accounting software, so the analysis can be reconciled against figures she already reports elsewhere.","correct":false,"tempting":"Useful for reconciliation later, and irrelevant to whether the grouping preserved the data."},{"text":"That the grouping did not merge two genuinely different job types that happen to be described in similar words, which would hide a difference in margin between them.","correct":false,"tempting":"A real risk that a review of the 12 groups would catch, but it is a judgement about the grouping rather than the completeness check that comes first."}]},"prev":{"slug":"the-data-you-already-have","title":"What you are already sitting on, and what of it is usable","url":"https://addaly.com/learn/ai-for-small-business/the-data-you-already-have"},"next":{"slug":"the-analysis-that-shows-its-working","title":"Make it compute, do not let it estimate","url":"https://addaly.com/learn/ai-for-small-business/the-analysis-that-shows-its-working"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Why cleaning is most of the job', in AI for a Small Business. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/ai-for-small-business/cleaning-before-counting (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Why cleaning is most of the job","courseTitle":"AI for a Small Business","module":{"index":8,"slug":"your-own-numbers","title":"Learning from your own numbers"},"answer":null,"keyIdea":"Counts are only as meaningful as the categories underneath them, and a category spelled four ways is four categories; so the work of making numbers comparable is the analysis, not preparation for it.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"stack","title":"The five faults in every real export","layers":[{"label":"Duplicates","detail":"The same order exported twice, one customer under two records. Count rows, count distinct identifiers, and find out why they differ before anything else."},{"label":"Dates as text","detail":"03/04/2026 is March to half the world and April to the other half. Above the twelfth the guess is forced and correct; below it the guess is silent and may be six months out."},{"label":"Numbers as text","detail":"A currency symbol, a comma, a trailing space, and the column sums only the rows that parsed. The total is plausible and wrong by exactly the rows you did not notice."},{"label":"Categories that multiply","detail":"Repair, repair, Repairs, REPAIR. Five categories to a computer and one to you, splitting every count and every chart along lines you cannot see."},{"label":"Missing values","detail":"A blank, a zero and the word N/A are three different things. Treating a blank as a zero turns we did not record this into this was nothing."}],"caption":"Sixty to eighty per cent of the work is here, and it is the analysis rather than preparation for it. Never clean in place: keep the raw export, write the cleaning down as steps you can rerun, and put the result in a new file."},"alt":"Figure: The five faults in every real export. Layers, top to bottom: Duplicates (The same order exported twice, one customer under two records. Count rows, count distinct identifiers, and find out why they differ before anything else.); Dates as text (03/04/2026 is March to half the world and April to the other half. Above the twelfth the guess is forced and correct; below it the guess is silent and may be six months out.); Numbers as text (A currency symbol, a comma, a trailing space, and the column sums only the rows that parsed. The total is plausible and wrong by exactly the rows you did not notice.); Categories that multiply (Repair, repair, Repairs, REPAIR. Five categories to a computer and one to you, splitting every count and every chart along lines you cannot see.); Missing values (A blank, a zero and the word N/A are three different things. Treating a blank as a zero turns we did not record this into this was nothing.). Sixty to eighty per cent of the work is here, and it is the analysis rather than preparation for it. Never clean in place: keep the raw export, write the cleaning down as steps you can rerun, and put the result in a new file."}},"blocks":[{"t":"h2","id":"the-unglamorous-majority","runs":[{"text":"The unglamorous majority"}]},{"t":"p","runs":[{"text":"People who do this for a living put the share of time spent preparing data somewhere between 60 and 80%. That is not a failing of their tools. It is what the job is, and it is worth knowing before you start, so that the afternoon you spend fixing dates does not feel like a detour."}]},{"t":"h2","id":"the-five-faults-you-will-actually-meet","runs":[{"text":"The five faults you will actually meet"}]},{"t":"p","runs":[{"text":"Duplicates.","b":true},{"text":" The same order exported twice, the same customer under two records. Check by counting rows and counting distinct identifiers; if they differ, find out why before doing anything else."}]},{"t":"p","runs":[{"text":"Dates as text.","b":true},{"text":" Your spreadsheet sees "},{"text":"03/04/2026","code":true},{"text":" and applies a regional guess. Half the world reads that as March, half as April. Where the day is above 12 the guess is forced and correct; where it is below 12 it is silent and might be wrong. The result is a dataset where some dates are right and some are six months out, and nothing looks broken. Convert dates to "},{"text":"YYYY-MM-DD","code":true},{"text":" as the first operation on any file."}]},{"t":"p","runs":[{"text":"Numbers as text.","b":true},{"text":" A currency symbol, a comma, a trailing space, and the column will not sum. Worse, it sums the subset that parsed, which produces a plausible total that is wrong by exactly the rows you did not notice."}]},{"t":"p","runs":[{"text":"Categories that multiply.","b":true},{"text":" "},{"text":"Repair","code":true},{"text":", "},{"text":"repair","code":true},{"text":", "},{"text":"Repairs","code":true},{"text":", "},{"text":"Repair ","code":true},{"text":", "},{"text":"REPAIR","code":true},{"text":". Five categories to a computer, one to you. This is the fault that quietly ruins more small-business analyses than any other, because every count and every chart splits along lines you cannot see."}]},{"t":"p","runs":[{"text":"Missing values.","b":true},{"text":" An empty cell, a zero and the word "},{"text":"N/A","code":true},{"text":" are three different things, and treating a blank as a zero turns “we did not record this” into “this was nothing”, which will drag every average you calculate towards a number that never happened."}]},{"t":"h2","id":"where-a-model-helps-and-where-it-must-not","runs":[{"text":"Where a model helps, and where it must not"}]},{"t":"p","runs":[{"text":"It helps with categories. Give it the 340 distinct values in your product column and ask which are the same thing, and it will group them well: that is meaning-matching, which is what these models are good at. It also writes the cleaning code faster than you will."}]},{"t":"p","runs":[{"text":"Two hard limits, both from module six."}]},{"t":"p","runs":[{"text":"It must not do the arithmetic. Totals, counts and averages come from the spreadsheet or the code, never from the model reading the data and reporting a figure."}]},{"t":"p","runs":[{"text":"And it must not decide what a blank means. Only you know whether an empty delivery-charge cell means free delivery, collection, or a till operator who skipped the field. Guessing here invents a business fact, and every number downstream inherits it."}]},{"t":"h2","id":"the-tools-all-free","runs":[{"text":"The tools, all free"}]},{"t":"p","runs":[{"text":"LibreOffice Calc","b":true},{"text":" does find-and-replace, text-to-columns and deduplication, and opens the CSV your platform exports without a licence."}]},{"t":"p","runs":[{"text":"OpenRefine","b":true},{"text":" is free, open source, and built exactly for the categories problem. Its clustering feature finds variant spellings automatically and is the fastest route from 340 product names to 90 real ones."}]},{"t":"p","runs":[{"text":"Python with pandas","b":true},{"text":" is free and handles files a spreadsheet will not open. Roughly fifteen lines does the whole job, and a model will write those fifteen lines correctly if you describe your columns."}]},{"t":"p","runs":[{"text":"Paid tools exist and do the same work with nicer screens. For the volumes in this course they add nothing a free tool does not already do."}]},{"t":"h2","id":"keep-the-original","runs":[{"text":"Keep the original"}]},{"t":"p","runs":[{"text":"Never clean in place. Keep the raw export untouched, do the cleaning as a script or a documented sequence of steps, and write the result to a new file."}]},{"t":"p","runs":[{"text":"Two reasons, and the second is the one that gets people. You will need to rerun this next month, and a remembered sequence of manual edits cannot be rerun. And when a figure looks wrong, the only way to find out whether the fault is in the data or the cleaning is to go back to what came out of the till."}]},{"t":"h2","id":"the-test-that-catches-most-of-it","runs":[{"text":"The test that catches most of it"}]},{"t":"p","runs":[{"text":"Before analysing, check four things and write the answers down."}]},{"t":"p","runs":[{"text":"How many rows, and how many distinct customers, orders or jobs. What the earliest and latest dates are: this catches the 1970 and 2087 rows that every export contains. What the smallest and largest amounts are, which catches the negative refunds and the misplaced decimal point. And how many distinct values each category column has, which catches the multiplying-categories problem immediately."}]},{"t":"p","runs":[{"text":"Four questions, ten minutes, and they find something in most real datasets. A figure produced without them is not wrong, exactly. It is unexamined, which is a different problem and usually a longer-lived one."}]}],"check":{"question":[{"text":"A workshop’s job export has a "},{"text":"type","code":true},{"text":" column with 61 distinct values, which the owner knows should be about 12. She uses a model to group them and it returns 12 clean categories. What must she check before trusting any counts?"}],"options":[{"text":[{"text":"That the model has not invented a category name that did not appear in the original data, since generated labels can be plausible but absent from the source."}],"correct":false,"tempting":[{"text":"Worth a glance, and a renamed category still counts the same rows, so it does not change any total."}]},{"text":[{"text":"That every one of the 61 original values was assigned somewhere, since a value silently dropped removes its rows from every count."}],"correct":true,"tempting":null},{"text":[{"text":"That the 12 categories match the ones used in her accounting software, so the analysis can be reconciled against figures she already reports elsewhere."}],"correct":false,"tempting":[{"text":"Useful for reconciliation later, and irrelevant to whether the grouping preserved the data."}]},{"text":[{"text":"That the grouping did not merge two genuinely different job types that happen to be described in similar words, which would hide a difference in margin between them."}],"correct":false,"tempting":[{"text":"A real risk that a review of the 12 groups would catch, but it is a judgement about the grouping rather than the completeness check that comes first."}]}],"order":[2,0,1,3],"why":null},"prev":{"slug":"the-data-you-already-have","title":"What you are already sitting on, and what of it is usable","minutes":9},"next":{"slug":"the-analysis-that-shows-its-working","title":"Make it compute, do not let it estimate","minutes":9}}}