Augmented Data Science: Data cleaning agent

Duygu Dagli & Gorkem Turgut (G.T.) Ozer
Image – Augmented Data Science: Data cleaning agent
tl;dr (never ai;dr)
  • Data cleaning is an execution-heavy task, but only after the data scientist defines the modeling objective.
  • We developed and tested a data-cleaning agent skill that profiles the data, separates method-invariant cleaning from method-dependent cleaning, and labels each step as diagnose, act, or ask.
  • On a large, rich dataset with Amazon purchases and purchaser demographics, the agent produced a modeling-ready table compared to a baseline prompt that generically applied data cleaning.
  • The skill made decisions explicit. This helped detect leakage in our use case: the skill excluded same-period purchase-derived features such as price, spend, and frequency because those features leak the target.
  • Our analysis here supported the Augmented Data Science framework we introduced earlier: the LLM can execute cleaning, but the data scientist must set the target, method, and acceptable assumptions.

Podcast-style summary by NotebookLM

Note: This joint post combines the Academic’s Take and the Director’s Cut.

This is the fourth article in our Augmented Data Science series. Earlier posts focused on intent-heavy tasks: hypothesis search and method selection. Data cleaning sits on the other side of the matrix. It is execution-heavy, but not context-free. A dataset is not generically "clean"; it is clean for a specific modeling task.

In practice, data cleaning involves critical decisions. We focus on making these decisions more explicit. Tasks such as joining tables, parsing dates, and stripping whitespace are largely method-invariant. But handling missingness, categorical encoding, scaling, outlier treatment, and class imbalance decisions depend on the model and target.

Cleaning without defining the modeling task, therefore, creates a tidy file that may still be wrong for analysis.

The data cleaning agent

The data cleaning agent (get the skill file for your preferred LLM) asks for the modeling goal, preferred method, package, and source files. It then profiles the data and asks more detailed questions. The cleaning plan separates:

  • Method-invariant steps: type coercion, format standardization, deduplication, column removal, category cleanup, domain checks, encoding repair, joins, unit consistency, etc.
  • Method-dependent steps: missingness, scaling, categorical encoding, outlier treatment, class imbalance, etc.

Each step is labeled:

  • Diagnose: run a check and report the result
  • Act: apply a deterministic fix
  • Ask: ask for the data scientist's judgment

The skill keeps raw data read-only. The cleaned file, cleaning plan, and reusable cleaning script are saved as separate artifacts. This is to keep the data cleaning and overall data science pipeline reproducible.

Testing the agent

We tested the skill on two files with real Amazon data (Berke et al., 2024):1 amazon-purchases-with-parent.csv with 1,850,717 purchase rows, and survey.csv with 5,027 respondents (You can see and download the data here). We used a preprocessing step to map the raw product codes to parent categories, 35 of which appeared in the final modeling table. We then pursued a predictive model to estimate the probability that a customer with given demographics purchases from a given parent category, using CatBoost as our preferred method.

We tested the skill using Claude Opus 4.8 and OpenAI GPT 5.5 under two conditions:

  • Baseline: no skill, only the generic instruction to join and clean the data files.
  • Agent Skill: the data-cleaning skill loaded, with the modeling goal and method specified.

Summary of the results

The baseline runs without the skill produced useful joined tables. Both models stripped whitespace, parsed dates and numeric fields, derived useful variables, validated the many-to-one join, preserved missing product metadata as NAs, and wrote a clean purchase-level file. The agent skill changed the unit of analysis based on the modeling goal. Instead of a purchase-level table, the agent created a customer-by-parent-category grid with 5,027 respondents across 35 categories. The two skill runs agreed on the core structure: the same customer-category grid, target definition, exclusion of purchase-derived features, and nearly identical category-level target rates.

See the detailed detailed results here: Outputs of the data cleaning agent skill in Opus 4.8 and GPT 5.5

Takeaways

  • Change in unit of analysis and method-specific choices. The agent screened for leakage and excluded purchase-derived fields such as price, spend, and frequency because the same-period purchase behavior leaks the target. Because the method was CatBoost, the agent rejected scaling and one-hot encoding, kept categorical predictors as strings, filled categorical missingness only where needed, and examined class imbalance by category. The skill separated deterministic cleaning from judgment. For example, missingness and multi-select survey fields were not silently imputed or transformed without being marked as data scientist decisions.
  • Opus probed where GPT didn't. Opus 4.8 expanded multi-select survey fields into indicators and flagged a subtle validation issue: because each customer appears in 35 rows, a random train/test split leaks demographics across rows. It then recommended a customer-grouped split. GPT 5.5 did not raise that issue. GPT also reported extreme full-data diagnostics, including a maximum unit price of 999,999 and quantity of 4,000, which were not reproducible. The takeaway is straightforward: even structured agent output needs to be checked and validated.
  • Multi-select survey fields required a data scientist decision and the agent knew that. Opus split them into indicator columns; GPT kept them as categorical strings. Since the method was CatBoost, GPT's choice was technically compatible with the model. The question was whether it accurately represented the survey construct. If Q-life-changes contains sparse combinations such as Moved place of residence or Lost a job, splitting the field into indicators is usually cleaner than treating each full string as its own CatBoost category.
  • The agent provides directions, not decisions. The agent cleaned the data for a specified modeling task and method. In the process, both models used the data scientist's inputs at points where judgment was required. The skill guided the models to structure and make explicit the handoffs between execution and intent.

Consistent with the Augmented Data Science framework, LLMs remain assistants for data cleaning, not substitutes, retaining the human data scientist as the decision maker. The data scientist must define the target, method, unit of analysis, and leakage boundary. The LLM then executes the search for cleaning steps inside those boundaries.

References

[1] Berke, A., Mahari, R., Pentland, S., Larson, K., & Calacci, D. (2024). Insights from an experiment crowdsourcing data from thousands of US Amazon users: The importance of transparency, money, and data use. Proceedings of the ACM on Human-Computer Interaction, 8(CSCW2), Article 466. ↩

Other popular articles

How to (and not to) log transform zero

Image
Image courtesy of the authors: Survey results of the papers with log zero in the American Economic Review Podcast-style summary by NotebookLM Academic's take Log transformation is widely used in linear models for several reasons: Making data "behave" or conform to parametric assumptions, calculating elasticity, etc. The figure above shows that nearly 40% of the empirical papers in a selected journal used a log specification and 36% had the problem of the log of zero. When an outcome variable naturally has zeros, however, log transformation is tricky. In most cases, the solution is to instinctively add a positive constant to each value in the outcome variable. One popular idea is to add 1 so that raw zeros remain as log-transformed zeros. Another is to add a very small constant, especially if the scale of the variable is small. Well, the bad news is that these are all arbitrary choices that bias the resulting estimates. If a model is correlational, a small bias due t...
Read more

Explaining the unexplainable Part II: SHAP and SAGE

Image
Image courtesy of iancovert.com Podcast-style summary by NotebookLM Academic's take This post is an exception to close the loop we opened with our post on LIME:  Explaining the unexplainable Part I: LIME . Starting in 2025, we're changing the scope of Data Duets to focus on business cases (as opposed to methods). Having discovered an excellent write-up  that explains both SHAP (SHapley Additive exPlanations) and SAGE (Shapley Additive Global importancE), I will focus on the why questions, possible links to counterfactuals and causality, and the implications for data centricity. Why do we need SHAP and SAGE? The need for explainability methods stems clearly from the fact that most ensemble methods are black boxes in nature: they minimize prediction error but they obscure how the predictions are made. This is problematic because some predictions are only as good as the underlying conditions are favorable. For example, Zillow's iBuying was a notorio...
Read more

Measuring long-term outcomes using short-term data and surrogates

Image
Image courtesy of Cai et al. (2023) Podcast-style summary by NotebookLM Solo post: Director's cut When measuring the outcomes of an intervention, organizations usually observe and quantify immediate or short-term results. For example, marketing could drive additional traffic, a discounted shipping rate could increase conversion rates, a price promotion or a loyalty program could drive sales. In most cases, however, these interventions would have effects that materialize over a longer period of time. After being exposed to a promotion, customers may become more price sensitive and start buying cheaper products or strategically time their purchases to take advantage of the next promotion. In general, companies will not conduct multi-month (or even multi-year) experiments to compare alternatives and find the option that optimizes long-term return on investment (ROI). Decisions must be made in the absence of long-term results. To address this shortcoming, in 2019, Susan Athey et ...
Read more