Overview

Lesson 4.2: Data Cleaning and Preprocessing with AI

This lesson teaches researchers how to use AI as a collaborative data scientist, leveraging AI's ability to generate data cleaning and preprocessing code while maintaining human oversight of every data decision. You will learn to assess data quality systematically before cleaning, specify cleaning procedures in plain language and receive executable Python, R, or SQL code in return, verify that generated code produces intended results on sample data, handle missing values strategically, standardize inconsistent variables, detect and evaluate outliers, and document the full cleaning pipeline for reproducibility. The lesson includes ready-to-use prompt templates for each cleaning task and covers the specific verification steps that prevent small code errors from propagating through all subsequent analyses.

Title

Lesson 4.2: Data Cleaning and Preprocessing with AI

Purpose

This lesson teaches researchers how to use AI as a collaborative data scientist, leveraging AI's ability to generate data cleaning and preprocessing code while maintaining human oversight of data decisions. You will learn to specify cleaning procedures in plain language, have AI generate executable code, verify that code produces intended results, and document data preparation decisions for reproducible research.

By the end of this lesson you will be able to: (1) conduct a systematic data quality assessment before beginning cleaning; (2) generate cleaning code in Python or R from natural language descriptions; (3) apply verification protocols to AI-generated code before deploying on full datasets; (4) choose an appropriate missing data strategy for your specific data pattern; (5) standardize categorical variables, date formats, and column naming conventions programmatically; and (6) produce a data cleaning documentation log that meets reproducibility standards.

Core Concepts

Data cleaning is one of the most time-consuming phases of research, consuming 60-80% of analysis time in many empirical studies. AI dramatically accelerates this phase by generating code from natural language descriptions, but only if the researcher understands what problems to look for and how to verify that the generated code actually solves them.

Systematic Data Quality Assessment

Before writing a single line of cleaning code, systematic assessment identifies what problems actually exist. This prevents wasted effort cleaning problems that do not exist and ensures that real problems are addressed before they propagate into analysis. A quality assessment should examine six categories:

1. Missing values: How many missing values exist per variable? Are they missing completely at random (MCAR), missing at random (MAR), or missing not at random (MNAR)? The pattern of missingness determines which handling strategy is valid.
   2. Duplicates: Are there duplicate rows (identical across all variables) or partial duplicates (same respondent ID but different responses)? Duplicates from data export or merge operations inflate sample size and distort results.
   3. Variable types and formats: Are numeric variables stored as strings? Are dates stored as character strings in multiple formats? Type mismatches cause silent failures in analysis code.
   4. Categorical inconsistencies: Are the same categorical values spelled or capitalized differently across rows (e.g., 'Male', 'male', 'M', 'MALE')? Are there unexpected values that do not map to any valid category?
   5. Outliers: Are there values that fall far outside the plausible range for a variable? Could they be data entry errors (e.g., age = 999) or legitimate extreme values?
   6. Logical inconsistencies: Are there combinations of values that are logically impossible (e.g., age at first diagnosis earlier than age at birth, or end date before start date)?

AI can generate the assessment code from a description of your dataset and variables. Provide: the file format, a list of variables with their intended types and value ranges, and any known logical constraints. Receive: a quality assessment script that produces a summary report of all six problem categories.

AI-Assisted Code Generation for Cleaning

The core AI workflow for data cleaning is: describe the problem in plain language, provide a representative data sample (10-20 rows, never full sensitive data), receive executable code, verify on sample data, then apply to the full dataset. This is faster than writing cleaning code from scratch and more reliable than manual editing because AI applies rules consistently.

The quality of the generated code depends heavily on the specificity of the description. Vague requests ('clean the data') produce generic boilerplate. Specific requests ('rename all column headers to snake_case, convert the date_collected column from MM/DD/YYYY string format to a datetime object, and create a flag variable called missing_income that equals 1 when income is NULL') produce precise, directly usable code.

Missing Data Strategies

Missing data handling is a researcher decision with real consequences for validity, not a default to apply mechanically. Three main strategies:

Complete case analysis (listwise deletion): Remove all rows with any missing values. Appropriate when missingness is MCAR and less than 5% of data is missing. Fast and simple but loses statistical power and can introduce bias if missingness is not truly random.

Single imputation: Replace missing values with a substitute (mean, median, mode, or regression-predicted value). Appropriate for variables with small amounts of missingness when the substitution is defensible. Underestimates standard errors and overestimates precision because it treats imputed values as known.

Multiple imputation: Generate several complete datasets by imputing from a predictive model, run analyses on each, and pool results using Rubin's rules. Appropriate for variables with 5-40% missingness when MAR is plausible. The preferred statistical approach for most research contexts but requires more sophisticated implementation.

AI can generate code for any of these approaches. For multiple imputation, a request like 'Generate R code using the mice package to create 5 multiply imputed datasets for a dataframe with these variables [list]. Explain which variables to include in the imputation model and why' produces a technically sound starting implementation.

Standardization

Standardization creates the consistency required for reliable analysis. Three categories are most commonly needed: variable name normalization (consistent snake_case or camelCase, no spaces or special characters), categorical value harmonization (standardizing all variants of the same category to a single canonical form), and date format normalization (converting all date strings to a single ISO 8601 format: YYYY-MM-DD). AI-generated standardization code is more reliable than manual editing because it applies the transformation rule to the entire dataset in one pass rather than row-by-row.

Outlier Detection and Evaluation

Outliers in data may be legitimate extreme values or data entry errors, and the distinction matters enormously. Automated detection flags candidates using statistical criteria (values more than 3 standard deviations from the mean, or outside 1.5×IQR from the quartiles), but determining legitimacy requires domain knowledge. A participant reporting income of $2,000,000 may be a data entry error or a genuine high-income outlier. Age = 999 is almost certainly a missing value code, not a real age.

The correct workflow: use AI to generate outlier detection code, review every flagged value with domain knowledge, classify each as legitimate extreme value or data error, document the classification and action taken (retain, correct, remove, recode as missing), and report outlier handling transparently in the methods section.

Practical Applications

The following workflows illustrate how to apply AI assistance at each stage of a realistic data cleaning pipeline.

Workflow 1: Survey Data from an Online Platform

A researcher exports a raw CSV from Qualtrics with 847 responses, 42 variables, and known issues: duplicate response IDs from a platform bug, variable names containing spaces and special characters, dates in MM/DD/YYYY string format, and Likert items coded 1-5 but exported with labels ('Strongly Agree') rather than numbers.

Step 1 - Quality assessment prompt: 'I have a CSV with 847 rows and 42 variables. Please generate a Python script using pandas that: (a) reports the count and percentage of missing values per column; (b) identifies fully duplicate rows; (c) identifies rows with duplicate values in the ResponseID column; (d) reports the unique values for each column with fewer than 20 unique values; (e) reports min, max, and dtype for each numeric column.'

Step 2 - Cleaning prompt: 'Based on the quality report, please generate a Python cleaning script that: (a) removes fully duplicate rows; (b) keeps the first occurrence of duplicate ResponseIDs and flags the duplicates in a new column called duplicate_flag; (c) renames all columns to snake_case with no spaces or special characters; (d) converts the StartDate and EndDate columns from MM/DD/YYYY string format to datetime; (e) recodes the Likert label strings to integers using this mapping: [provide mapping].'

Step 3 - Verification: Run the script on a 20-row sample, inspect output, confirm each transformation is correct before applying to the full 847-row dataset.

Workflow 2: Clinical Dataset with Missing Values Requiring Imputation

A researcher has a clinical dataset with 15% missingness in the primary outcome variable and 8% missingness in several covariates. The missingness pattern is MAR (missingness in outcome is related to treatment arm, which is fully observed).

Prompt: 'I have a clinical dataset in R with these variables: [list variables with types]. The outcome variable [name] has 15% missing values. The missingness is related to the treatment arm variable, which is fully observed. Please generate R code using the mice package to: (a) create a predictor matrix that includes all relevant variables; (b) run 10 imputations using predictive mean matching for continuous variables and logistic regression for binary variables; (c) check convergence using a trace plot; (d) pool results from a linear regression predicting [outcome] from [predictors] across the 10 imputed datasets using Rubin's rules.'

Workflow 3: Categorical Variable Harmonization

A researcher has merged data from three study sites. The treatment type variable has 23 unique values representing 4 actual treatment categories, with variations like 'CBT', 'cbt', 'Cognitive Behavioral Therapy', 'Cognitive-Behavioral Therapy', 'cognitive behavioral'.

Prompt: 'I have a categorical variable called treatment_type with the following unique values: [paste the 23 values]. These should map to 4 categories: CBT, DBT, ACT, and Other. Please: (a) generate a Python dictionary mapping each observed value to its correct canonical category; (b) write code that applies this mapping to standardize the column; (c) flag any values that do not clearly map to a canonical category so I can review them manually.'

Documentation: The Cleaning Log

Every data cleaning decision must be documented in a cleaning log. A minimal log entry contains: date, variable(s) affected, problem identified, action taken, code used or reference to script, and rationale. This log is what allows another researcher, or your future self, to reproduce the cleaned dataset from the raw data. AI can generate a template cleaning log and even help you fill in entries after each cleaning step: 'Summarize what the following data cleaning script does in one sentence per operation, suitable for inclusion in a methods appendix.'

Key Takeaways

AI generates cleaning code efficiently from plain language specifications, compressing the time from raw data to analysis-ready dataset from days to hours. The bottleneck shifts from writing code to specifying exactly what you need and verifying the output, which requires strong methodological understanding, not programming skill.

Verification before full deployment is essential. AI-generated code should always be tested on a small, inspectable sample before applying to the full dataset. Small errors, a wrong column name, an off-by-one index, an incorrect regex pattern, propagate silently through all subsequent analyses if not caught early.

Missing data handling is a substantive methodological decision. The choice between complete case analysis, single imputation, and multiple imputation depends on the pattern of missingness, the proportion of missing data, and the validity requirements of your analysis. AI can generate code for any approach but cannot make the strategic decision for you.

Standardization should be automated, not manual. Applying categorical harmonization, date format normalization, and column renaming through code is more reliable than manual editing because the rules apply consistently to every row.

Outlier detection surfaces candidates; domain knowledge determines action. Automated detection with AI-generated code identifies flagged values efficiently, but every outlier decision, retain, correct, remove, recode, requires researcher judgment and must be documented.

Documentation enables reproducibility and defensibility. A cleaning log recording what was changed, why, and how is not optional. It is the methodological record that allows others to evaluate your data preparation, reproduce your results, and understand any anomalies in your findings.

Prompt Templates for Data Cleaning

These ready-to-adapt templates cover the most common data cleaning tasks.

Quality Assessment Prompt
'I have a [file format] dataset with [N] rows and [K] columns. Please generate a [Python/R] script that produces a data quality report including: (1) count and percentage of missing values per column; (2) count of fully duplicate rows; (3) for columns with fewer than 20 unique values, list all unique values; (4) for numeric columns, report min, max, mean, and standard deviation; (5) flag any column where the stored dtype does not match the expected type for that variable. Variables are: [list variable names and intended types].'

Cleaning Code Generation Prompt
'Based on the following data quality issues: [describe issues], please generate a [Python/R] cleaning script that addresses each issue. For each transformation, add a comment explaining what it does. The dataset has the following structure: [paste first 5-10 rows or describe columns]. I need the script to be fully reproducible with a random seed where applicable.'

Multiple Imputation Prompt
'I have a dataset in R with [N] rows. The variable [outcome] has [X%] missing values. Missingness pattern is [MCAR/MAR/describe pattern]. Please generate R code using the mice package to: (a) set up an appropriate imputation model including these predictor variables [list]; (b) run [M] imputations; (c) diagnose convergence; (d) pool results from [analysis type] using Rubin's rules.'

Outlier Detection Prompt
'Please generate [Python/R] code to detect outliers in the following numeric columns: [list columns]. Use both the IQR method (flag values outside 1.5×IQR from quartiles) and the z-score method (flag values more than 3 SD from the mean). Output a dataframe listing each flagged row, the variable, the observed value, and which method flagged it. Do not remove any values, just flag them for my review.'

Cleaning Documentation Prompt
'I ran the following data cleaning script on my dataset: [paste script]. Please summarize what each block of code does in one plain-English sentence per operation, suitable for inclusion in a data preparation section of a research methods appendix.'