Gains:
- Ability to distinguish the cause of missing values (random, systematic, meaningful) and choose the appropriate strategy and calculate the fill value from training alone
- Ability to examine outliers before deleting them and distinguish between a data error and a true rare event
- Ability to prevent silent loss by monitoring the impact of each step on the number of lines/blanks while bringing type and format inconsistencies to the standard
Approximately 60-80 percent of a data scientist's time goes to cleaning; In the industry, this is half-jokingly called "data wrangling" (data wrangling or data cleaning). Because real-world data almost never comes ready for analysis: dates are in mixed formats, numbers are stored as text, some cells are blank, a customer appears three times in the same table with two different spellings. In this unit we will cover three basic aspects of cleanup: missing values, outliers, and type/format conversion. Artificial intelligence is an extraordinarily fast assistant in this work; But it is you who decide what to clean and how, because each cleaning choice changes the analysis.
The golden rule of cleaning: every change is a decision
Deleting a cell, filling in a missing value with the mean, trimming an outlier—none of these are “neutral” operations. Each changes the data and affects the result. So the golden rule of cleanup: write every change into the code, note the rationale, never overwrite the original data. The AI gives you quick cleanup code, but it is your responsibility to understand what that code does; Do not run it without seeing how many lines are gone when you say "delete empty lines".
Caution: Never modify the original raw data. Write the cleaned version to a separate file/table. This way, if you spot an error, you can go back to square one and maintain reproducibility.
Missing values: why is it empty, what to do
A missing value (usually appearing as NaN — "Not a Number" in pandas) is when a cell is empty. But the cause of the gap determines the solution. There are three typical situations. Random missing: sensor did not work for a moment; It may be reasonable to fill it out. Systematic missing: a form field is only asked for certain customers; The gap here is actually information. Significant missing: if "return date" is blank, the customer did not return; This space means 0 or "none", it is not filled.
Major strategies:
Strategy
When is it appropriate?
risk
Delete row
Missing rate is very low (<5%) and random
Loss of data and representation
Delete a column
Most of the column is empty (>60%)
loss of information
Average/median fill
Numeric, missing randomly
It reduces the variance and distorts the distribution
Category "unknown"
Categorical, systematic missing
Generates additional categories
Prediction with model
Complex, precious column
Leak risk, complexity
Critical point: the imputation value should be calculated only from the training data and the same value should be applied to the test data. If you include the average of the test data, you create leakage (Unit 10). The median (the middle value of ordinal data) is often preferred to the mean because it is more robust to outliers than the mean.
Outliers: error or real?
An outlier can be one of two things: a data error (999 in the age column) or a real but rare event (a customer's $2 million order). Confusing the two is disastrous: delete a true outlier and you throw away important information; If you let go of a mistake, your averages will suffer. Therefore, it is necessary to examine the outlier first, not to delete it automatically.
Common detection methods: IQR method (interquartile range; values that are more than 1.5 times the difference between the 25% and 75% quintiles of the data are considered outliers) and z-score (the number of standard deviations away from the mean a value is; usually an outlier if it is greater than 3). AI writes the code for these calculations in seconds; But before you say "delete", check what those values are.
Type and format conversion: silent error source
One of the most headaches is data type confusion. If an "amount" column is stored as text, you cannot add; The program incorrectly reads a Turkish formatted number such as "1,250.50" instead of "one thousand two hundred and fifty". Dates ("01/03/2024" day-month or month-day?), categories ("Male"/"male"/"M" are the same thing?) and units (TL or kuruş?) silently produce incorrect results. A big part of cleaning up is pulling these inconsistencies into the standard: dates into one format, categories into one spelling, numbers into one unit.
three mini cases
Case 1 — The average trap. A team filled in the 320 missing values in the income column with the average ($48,500). But the shortcomings were systematic: these were the low-income segment that had never declared income. Average filling made this group artificially rich and the credit model was wrong. Lesson: ask “why is it blank” before filling it out.
Case 2 — Outlier that should not be deleted. A retail analyst deleted 4 huge orders (1.8 million TL each) in the sales data, thinking they were "errors". However, these were real corporate orders and were 22% of the total turnover. The post-deletion forecast model completely missed institutional demand. Lesson: examine the outlier before deleting it.
Case 3 — Date format disaster. In a CSV, the dates were mixed into both "2024-03-01" and "01.03.2024". When the code written by YZ was parsed according to the first format, 6,400 lines became NaT as "invalid date" and were silently excluded from analysis. The analyst only noticed this when the number of lines decreased. Lesson: always check the number of lines and blanks after conversion.
Four copyable templates
1) Missing value map:
I have pandas df. Write code that produces a table showing the number and percentage of missing (NaN) for each column. Then, list separately the columns with a missing rate exceeding 40% and those with a missing rate below 5%. Just generate this diagnosis code, not what strategy you suggest; I will make the decision.
2) Outlier inspection (not deletion):
Write code that DETECTS (not deletes!) outliers for my "amount" column using the IQR method. Put the outlying rows in a separate df so I can manually examine them. Also print the number of outliers and their share in the total.
3) Type/format standardization:
Write code that standardizes the following columns:- "date": can be mixed formats ("2024-03-01" and "01.03.2024"); convert them all to datetime, count the untranslatable ones and report (throw away silently). - "gender": "Male"/"male"/"M" -> "M", "Female"/"female"/"F" -> "K". - "amount": Turkish formatted text ("1.250,50") -> decimal number. Print how many rows are affected after each conversion.
4) Check before/after cleaning:
Write code that compares df.shape, missing count, and summary statistics (mean, median, min, max) of selected columns before and after a cleanup step. Purpose: to see what cleaning changes.
Weak prompt / Strong prompt
Weak prompt:
Clear this data.
"Clear" is ambiguous; AI does not know what missing parts should be deleted, what to fill in, which column to process and how. The result: blind, irreversible changes.
Powerful prompt:
Your role: data cleansing assistant. df columns: customer_id (int), registration_date (mixed format text), revenue_tl (text, "1,200.00"), city (text, inconsistent spelling). Rules:- Changing original df; Work on the copy named df_clean.- Convert income_tl to number, count what cannot be translated.- Change record_date to datetime, report the error.- Do not delete any rows without my approval; Show the candidates separately. After each step, print df_temiz.shape and the missing number.
Here the source is protected, every transformation is counted, deletion is left to the human.
Common mistakes
- Filling in the gaps without asking "why is it empty?" Filling in systematic/significant missingness with the mean distorts the data.
- Deleting the outlier without examining it. Discarding real but rare events destroys important information.
- Calculating the padding value from all data. Including test data creates leakage; just calculate from training.
- Not checking the number of rows/blanks after conversion. Rows that are silently NaT/NaN are excluded from analysis.
- Overwriting the original data. Return and reproducibility are lost.
Tip: Run the cleanse with a "before-after" comparison. Print df.shape, missing count, and summary statistics of critical columns after each step. So you immediately see that one step unexpectedly destroys 6,000 rows.
In summary
Cleansing is the most time-consuming and decision-requiring phase of data science. Every change changes the data, so each one is a conscious decision. For missing values, ask "why is it empty?" Review any outliers before deleting them; Bring type and format to standard. Calculate the fill value from the training data only, keep the original, and track the impact of each step by counting it. AI is a tremendous accelerator in this business, but humans decide what you clean and why.
Application task
Take (or create) a small table and deliberately insert three problems into it: a missing value tuple, an outlier, a mixed date format. Request diagnosis and standardization code from AI with the above templates; compare number of rows and blanks before/after each step. Try to catch at least one "silent loss" and note how you noticed it.
checklist
- [ ] Did I keep the original raw data and work on the copy?
- [ ] Have I asked the question "why is it empty" for each missing column?
- [ ] Did I review outliers before deleting them?
- [ ] Did I calculate the padding value from training data only?
- [ ] Am I checking the number of lines/blanks after each step and eliminating silent loss?