Cleaning and analyzing data
Making sense of spreadsheets and incident logs, after the identifying columns come out.
Outcome
A reproducible analysis whose cleaned data, formulas, assumptions, and checks can be inspected by another person.
Data gate
- Public: published datasets can use checked cloud or local analysis tools.
- Internal: operational or financial data needs an approved organisation account or local route, with unnecessary columns removed from the working copy.
- Identifiable: incident logs, case outcomes, contact details, precise locations, and small groups stay in a controlled local workflow or are aggregated before use.
Use AI when
- You need help writing a formula, cleaning categories, documenting code, or exploring a public copy.
- You retain the original file and can reproduce every transformation.
- Totals and classifications will be checked independently.
Do not use AI when
- A spreadsheet has not been checked for hidden sheets, comments, metadata, or identifying columns.
- The model is expected to calculate or classify without a reproducible formula or rule.
- The result will decide eligibility, credibility, risk, or services for a person.
Workflow
- Prepare: preserve the original, create a working copy, classify columns, and write the question and expected unit of analysis.
- Process: clean one transformation at a time and record formulas, code, category rules, and row counts.
- Verify: reconcile totals independently, inspect missing values, and spot-check source rows.
- Store: save the original, cleaned data, transformation log, analysis, and reviewer separately under appropriate access controls.
Human verification checklist
- Row counts, units, currencies, and denominators are explicit.
- Totals reconcile with an independent calculation.
- Categories and place names were reviewed for false merges.
- Small groups and outliers do not reveal people in the published output.
If it fails
Restore the working copy from the unchanged original, isolate the last transformation, and compare before and after counts. If the route is not reproducible, discard the result and rebuild it with formulas or documented code.
Start next
Use the receipt or document recipes below only with suitable data. Coding tools can help produce a repeatable transformation, but code that runs successfully can still apply the wrong rule.
Prompts for this task
Redact first
Get cleaning steps for a spreadsheet, without uploading it
20 minutes · Gives you numbered steps
Answer in [ANSWER LANGUAGE], even if the material below is in another language. I have a spreadsheet I cannot upload. I will describe it instead. The three rows below are invented by me, not copied out of the file, and I will never paste a real row. Do not ask me for the file or for real rows. Columns and what they hold: [LIST THE COLUMN NAMES AND WHAT EACH CONTAINS] Three invented rows, in the same shape as the real ones: [PASTE THREE INVENTED ROWS] What is wrong with it: [DESCRIBE THE PROBLEMS: mixed date formats, duplicates, mixed digits: ١٢٣ alongside 123, trailing spaces, merged cells, several answers in one cell] What I need at the end: [DESCRIBE THE RESULT YOU WANT] Give me, in this order: A. Numbered steps, in the order I should do them, each saying what to do and what the file will look like afterwards. B. The formula, or the script, for each step that needs one. Say which program it is for. C. The assumptions you made about my data, one per line, and for each one what it will silently break if the assumption is wrong and how I would spot that. D. What to check after each step to know it worked, including the row count before and after. Work only from my description. If my description is not enough to write a step safely, say what you need to know instead of guessing.
Redact first
Find the difference between receipts and a ledger
20 minutes · Gives you numbered steps
Answer in [ANSWER LANGUAGE], even if the material below is in another language. My receipts and my spreadsheet do not agree, and I can upload neither of them. I will describe the spreadsheet instead. Real rows are never pasted here, so do not ask me for them and do not ask me to upload a file. Columns and what each one holds: [LIST THE COLUMN NAMES AND WHAT EACH CONTAINS] Three invented rows in the same shape as the real ones: [PASTE THREE FAKE ROWS] What does not match: [DESCRIBE THE DIFFERENCE: the two totals differ, a receipt has no row, a row has no receipt, one payment is split across two receipts, mixed digits: ١٢٣ alongside 123, two currencies, dates written in two formats] Rules: 1. Work only from my description and the invented rows. Never invent a figure of mine, and never assume a total. 2. If a step cannot be done without seeing my data, say so and tell me what to look at myself. 3. Order the steps so that the cheapest check comes first. Give me, in this order: A. Numbered steps, in the order I should do them, each one narrowing down where the difference is. B. The formula or the script for each step that needs one, and which program it is for. C. The assumptions you made about my spreadsheet, as a list, and for each one, what it silently breaks if it is wrong. D. What to check after each step, including the row count and the total before and after.
Tools that help with this
- Only approved or redacted sensitive materialHas a free versionWorks offlineWorks in Iraq
- Controlled route for sensitive materialHas a free versionWorks offlineWorks in Iraq
Step-by-step guides for this
- Only approved or redacted sensitive material