Data Analyst · Step 4 of 8 · 9 min
Cleaning data without losing the truth
You can find duplicates, missing values and broken types, and write down every fix you make.
Look before you fix
Before changing anything, profile the data: how many rows, how many unique IDs, how many blanks per column, the minimum and maximum of each number and date. Odd values jump out quickly, like an order dated 1900 or a price of minus 5.
Always keep the raw file untouched. Work on a copy, or in SQL and code where the original stays as it is.
Duplicates and missing values
Duplicates come from double exports, retries and merges. Decide what makes a row unique (an order ID, or customer plus date plus product) and count rows per key. Remove exact duplicates; investigate near-duplicates before deleting.
Missing values need a decision, not a reflex. A blank might mean zero, unknown, or not applicable. Filling blanks with zero can change an average a lot. Often the honest choice is to leave them out and say how many you excluded.
Types, dates and text
Fix numbers stored as text, trim extra spaces, and standardise categories so “Hanoi”, “Ha Noi” and “HN” become one value. Dates are the usual trap: 03/04/2026 is 3 April in much of the world and 4 March in the US. Check a few dates where the day is above 12 to confirm the format.
Watch time zones too. An order at 23:30 in one zone can fall on a different day in another.
Document every step
Keep a short cleaning log: what you changed, why, and how many rows were affected. “Removed 214 exact duplicate orders. Excluded 37 orders with no date.” This lets others trust your numbers and lets you redo the work when new data arrives.
Steps in SQL, Power Query or a Python notebook document themselves. Manual edits in a spreadsheet do not, so write them down.
Try it
Clean a messy sample
- 1.Ask a chatbot to generate a fake messy dataset using the prompt below.
- 2.Paste it into a spreadsheet and profile it: row count, blanks, unique IDs.
- 3.Fix what you find, and keep a cleaning log of each change with row counts.
- 4.Compare your log with the list of problems the chatbot planted.
Create a fake dataset of 40 online orders as CSV with columns order_id, order_date, customer_city, product, quantity, price. Deliberately include: 3 duplicate rows, some missing prices, numbers stored with currency symbols, inconsistent city spellings, and mixed date formats. After the data, list every problem you planted, separately, so I can check my work.