Gains:
- Ability to recognize missing data types (MCAR/MAR/MNAR), outliers, and coding errors and use AI with the correct data to produce cleanup code and diagnostics
- Ability to see how each cleaning step (deletion, assignment, transformation) suggested by artificial intelligence will affect the analysis result and establish a reversible and documented workflow
- Maintaining that cleanup decisions are based on knowledge of the process that generates data, and that artificial intelligence is a supervised assistant, not a blind automaton
Every experienced analyst knows one truth: most of the time of an empirical project is not modeling, but data cleaning (the work of correcting errors, omissions, and inconsistencies in the data and making it ready for analysis). As a rough rule of thumb, about 70-80% of the time goes into understanding, cleaning and transforming data; only 20-30% is left for the actual analysis. In this unit, we will see step by step how artificial intelligence (AI) eases this heavy burden, but which decisions should still remain with you and why.
Let's put two basic facts first. First, cleaning decisions are based on your knowledge of the process that produces the data: only you know why a value is missing, why a number is overly large. AI does not see the data, does not know the context; Writes code, doesn't make decisions. Second, each cleaning step may change the analysis result. Deleting an outlier can invert the coefficient; Filling in the missing with the mean can artificially reduce the variance. So cleanup should be reversible and documented: never touch the raw data, do each step in code, record the impact of each step.
Step-by-step cleaning workflow
1. Know the data. First see the structure: number of rows (observations) and columns (variables), type of each variable (numeric, categorical, date), summary statistics, number of missing. You can ask the AI for this "data profile" code; But you read the output and ask questions like "why did this variable come as text?"
2. Understand and classify missing data. Missing data is divided into three types. MCAR (Missing Completely At Random): the missing is not due to anything, for example a sensor failed randomly. MAR (Missing At Random): missingness depends on other observed variables, for example older people leave a question blank more often, but you know age. MNAR (Missing Not At Random): missingness depends on the unobserved value itself, for example high earners do not report their income. This distinction is critical because it determines the solution method and the AI cannot decide this for you.
3. Deal with the deficiency. Options: listwise deletion, mean/median imputation, model-based multiple imputation. Deletion in MCAR is relatively safe but reduces the sample size. Multiple assignment is more appropriate in MAR. No simple method guarantees unbiasedness in MNAR; The deficiency mechanism should be modeled or at least a sensitivity analysis should be performed. Mean-filling is easy but dangerous: it reduces variance, weakens relationships, and hides uncertainty.
4. Examine outliers. An outlier is an observation that stands very far away from the others. Separate the two questions: Is this an error (age 999 was written in the data entry) or is it a real but outlier value (income of a billionaire)? Fix bugs; Do not delete true outliers because they represent data. Deleting the outlier "because it bothers" you is skewing the data towards the outcome.
5. Coding and consistency. Standardize categories ("Male", "male", "E" all into one code), bring dates into one format, units into one scale, detect duplicate rows. This is the least risky phase where AI helps best.
6. Document and freeze. Record each step with code and a comment line, keeping raw and cleaned data separate. So when a referee asks "why were these observations dropped?" you have your answer ready.
Caution: Do not make cleaning decisions based on results. Deleting a line saying "When you delete this line, the coefficient becomes significant" is the most insidious form of p-hacking, which we will see in the next unit.
Comparison table: missing data methods
Method
When is it appropriate?
risk
Role of AI
Delete a row
MCAR, low missing rate
Sample becomes smaller, bias in MAR/MNAR
Writes the code; you decide
Mean/median assignment
Quick preliminary analysis
Reduces variance, hides relationship
It's easy but doesn't recommend it; should warn
Multiple assignment (MI)
MAR, important variables
If not installed correctly it will be misleading
Recommends code and package (mice)
Mechanism modeling
MNAR
Complex, assumption-intensive
Draft only; expert is required
Four copyable prompts
1. Data profiling:
Your role: data cleansing assistant. I do not share the data, I want the R code. I have a data.frame called 'digit'; variables: id, age (numeric), income (numeric), education (categorical), region (categorical), date (date). Task: write code that produces type, missing number and rate for each variable, summary statistics for numeric ones, and frequency table for categorical ones. Add comment line.
2. Missing data diagnosis:
Write code that examines the missing pattern for the 'digit' data above: - the missing rate of each variable, - a simple table/graph showing the relationship of missingness to other variables. I will look at the output of the code and make the MCAR/MAR/MNAR distinction; You just produce the diagnostic tool, DO NOT decide on the assignment method.
3. Outlier inspection (not deletion):
Write code for variable 'income' that examines outliers WITHOUT DELETING: boxplot, IQR-based bounds, and table listing outlier observations + values. I will see if these are mistakes or real. Add automatic deletion.
4. Coding standardization:
In the 'education' variable, the values are written mixed: "Primary school","primary school","PRIMARY","High school","high school","University","univ". Write R code that recodes these into consistent categories; Show the mapping table clearly so I can confirm each conversion. Set the value you do not recognize to "UNKNOWN".
Weak prompt / Strong prompt
Weak prompt:
Clean up the data, fill in the gaps, discard the outliers.
This demand is dangerous: It gives blind authority to AI. What type of missing, what kind of filling, what contradictory truth - none of it is clear. The result may be a secretly biased data set.
Powerful prompt:
Your role: cleaning assistant, you are not the decision maker. Go step by step and write code that reports the impact of EACH step: 1) show the missing pattern (DO NOT delete/fill), 2) list outlier candidates (DO NOT delete), 3) fix category inconsistencies with the mapping table. Print the row count and summary statistic after each step so I can see and confirm the change. Automatic assignment/deletion insertion.
The difference is clear: strong will positions the AI as a supervised assistant, producing reversible and documented steps.
three mini cases
Case 1 — Average assignment trap. One analyst's income data for 5,000 people was missing 18%. He told AI to "fill in the gaps"; AI averaged them all. Result: the variance of income decreased by 22%, and the coefficient of the education-income relationship turned out to be weaker than it was. Correct way: the deficiency was MAR (due to education), had to be filled by multiple assignment (mice). Lesson: the filling method depends on the mechanism.
Case 2 — Outlier cleaning reverses the finding. There were 3 very large firms (0.6% of the total observation) in one firm data. These were deleted by the AI's "cleaning" suggestion; The economies of scale coefficient turned from positive to negative. However, those 3 companies were real and an important part of the industry. The correct way was to report with both full and robust estimation instead of deleting. Lesson: real outliers are data, not noise.
Case 3 — Silent coding error. In one panel, the date variable came in two different formats ("2020-03-01" and "01/03/2020"). When the combination code produced by the AI mistook them for different values, 1,400 observations were mismatched and the growth rates became ridiculous. It wouldn't matter if the analyst didn't check the row count. Lesson: observation counts and sanity checks after each step are a must.
Common mistakes
- Blindly filling the gap with the average. It reduces variance, hides uncertainty, and creates bias in MAR/MNAR. First classify the mechanism.
- Deleting the outlier "because it bothers". True outliers represent the data; deleting is bending the result. Correct what is wrong, keep what is real.
- Tapping raw data. Never modify raw data; Do all the cleanup with the code in a separate copy so it can be reverted to.
- Not measuring the impact of steps. If the row count and summary statistics are not checked after each step, silent errors will accumulate.
- Cleaning up as a result. The logic of "When you add this line, the result becomes better" is p-hacking; Cleaning should be planned before and independently of the analysis result.
Tip: Keep a “before/after” table that puts the same basic statistics (mean, median, number of observations, a few correlations) side by side before and after the cleanup. An unexpected jump is the best harbinger of a hidden error.
In summary
Data cleaning is the most time-consuming and decision-requiring phase of empirical work. The AI is a powerful code generator at this, but it can't call the shots: you classify the missing data type (MCAR/MAR/MNAR), determine whether the outlier is an error or real, keep each step reversible and documented. Instead of giving the AI blind “clear” authority, establish a step-by-step workflow whose impact is measured and validated. Checking the number of observations and summary statistics after each step is the best antidote to silent errors.
Application task
Select at least two variables that contain missing and outliers in a data set (your own data or a sample set). Request a diagnostic code from the AI via the "step by step, do not delete/fill" prompt above and run it. Classify the deficiency as MCAR/MAR/MNAR and write your justification in one sentence. Examine the contradictory candidates one by one and decide which one is a mistake and which one is real. Finally, produce a "before-after" table before/after cleaning and report if there are any unexpected changes.
checklist
- [ ] I cleaned the raw data in a separate copy without changing it.
- [ ] I classified the deficiency as MCAR/MAR/MNAR and chose the method accordingly.
- [ ] I examined the outliers one by one whether they were errors or real; I didn't blindly delete it.
- [ ] I checked the number of observations and summary statistics after each cleaning step.
- [ ] I documented all the steps with code and a comment line; reversible.
- [ ] I based the cleaning on knowledge of the process that produces the data, not on the analysis result.