Clean and Analyze Data with AI

Why Spreadsheets Feel Like a Chore

Every data task starts the same way: you open a file someone exported from an undocumented system, and the header row is merged, half the column is text pretending to be numbers, dates are in three formats, and there is a mysterious column nobody can explain. Getting from that messy spreadsheet to a clean, trustworthy table is normally a two-hour fight with formulas. This tutorial shows you how to use conversational AI to turn that chaos into clean, analyzable data in minutes, even if you have never written a formula more complicated than a SUM.

The strategy is simple: let the AI see your data, let it propose a cleaning plan, approve the plan, then turn it loose on the boring transformations. You keep control of the decisions that matter, and the machine does the repetitive work. Along the way you will learn the exact prompts that make a chatbot feel like a data analyst rather than a confused intern.

What You Need Before You Start

• Any modern AI assistant that can read files and write code, though plain text pasted into a chat works too.
• A copy of your messy data, or a public CSV you deliberately mangle for practice.
• A single text file where you paste every prompt, so you can rerun the workflow on new data later.

Step 1: Introduce the Data to the AI

Most people skip this step and immediately ask for the answer. That is a mistake. A good analyst first looks at the shape of the data, and you should make your AI do the same. The very first prompt should invite exploration, not demand a final chart.

I am going to paste a sample of a dataset I need to clean and analyze. Before we do anything else, please: (1) describe what you think each column represents, (2) flag anything unusual about the values, missing cells, or formats, and (3) list the three biggest problems you would fix first. Do not change any data yet. Here is the sample:
[PASTE 10-20 ROWS]

Paste a representative slice, not the whole file. Twenty well-chosen rows expose all the common problems while keeping the answer readable. When the AI lists its observations, read them carefully. This is your moment to catch things the AI got wrong about intent. For example, if it guesses a column is "customer ID" but you know it is really "order ID reference," correct it now so the rest of the workflow builds on the right assumptions.

Step 2: Approve a Cleaning Plan

Now ask for a written plan before you say "go." A cleaning plan turned into a conversation is much safer than letting an AI transform your only copy of the data in one shot. You want it to tell you, in plain language, exactly what transformations it will apply and why.

Based on what you observed, write out a step-by-step cleaning plan. For each step state: what you are changing, the rule you will apply, and what the result should look like. Flag any step where I need to make a choice because there are multiple reasonable options. Here are my priorities: [e.g. correct date formats, split the full-name column, drop duplicate rows, keep missing values visible].

The value of this step is that it forces decisions into the open. You will discover choices you did not know you were making, like whether to drop rows with missing addresses or keep them and fill "unknown." Approve the plan, adjust anything that feels wrong, and then all the mechanical work becomes straightforward.

A Minimal Cleaning Checklist

• Standardize dates. Get every cell into one unambiguous format such as YYYY-MM-DD.
• Normalize text. Strip leading and trailing spaces, standardize casing, fix typos in category names.
• Fix types. Make numbers numeric, dates date-like, and booleans true or false rather than "yes"/"no"/"Y".
• Handle blanks. Decide deliberately whether missing means zero, unknown, or "not applicable" for each column.
• Remove duplicates. Define what "duplicate" means, because it is rarely the same as "identical row."

Step 3: Let the AI Run the Transformations

With the plan approved, ask the AI to execute it and, crucially, to write the transformation in code that you can inspect. The output should be reproducible, not a one-time fix that vanishes when the chat closes.

Please apply the cleaning plan we agreed on using Python (pandas). Show me the code you run, print the first five rows before and after, and print a short summary of how many rows and columns changed. Save the cleaned version to a file called clean_data.csv. Assume the raw data is in raw_data.csv.

Most strong assistants will produce a small script. You do not need to understand every line, but you should read the comments and the printed before-and-after summary. This is where you catch a transformation that silently deleted the column you actually needed. Keeping the code means you can rerun everything next week when someone sends you the updated export.

Step 4: Ask Questions in Plain English

Now the real fun starts. With clean data in hand, you can ask analytical questions the way you would ask a colleague, and let the AI translate them into calculations. Forget remembering the syntax for a pivot table; just say what you want to know.

Using the cleaned dataset, answer these questions, and for each one tell me which column(s) you used and whether the answer is exact or an estimate: (1) What is the average order value per region? (2) Which category has the highest month-over-month growth? (3) How many customers placed more than one order? (4) What share of revenue comes from the top 10% of customers?

The habit of asking the AI to state its assumptions is what separates a trustworthy workflow from a slick demo. When it says "I grouped by region using the shipping_region column," you can immediately judge whether that is the right column to group by.

Step 5: Build a Reusable Analysis Script

Finally, wrap what worked into a single repeatable script. The goal is to stop typing prompts and instead run one command, so that next month's cleaning is a ten-second task.

Turn the cleaning and the analysis into one standalone Python script called analyze.py that reads raw_data.csv, cleans it, prints answers to my questions, and writes both clean_data.csv and a summary.json. Comment each section so I can edit thresholds later. Also write a short README.

Weekly reproducible output beats a perfect one-off analysis. Build scripts your future self can rerun on fresh data without remembering any context. The highest-leverage data habit is making the process repeatable, not making the current output perfect.

When to Trust, and When to Double-Check

• Trust the AI for mechanical transformations you have reviewed once; it is reliable and fast at tedious work.
• Double-check anything with money, dates, or identity in it, because those are exactly where subtle mistakes cause real damage.
• Always keep an untouched copy of the original file. Cleaning should be reversible, not destructive.

Ready to Clean Real Data

You now have a five-step workflow you can reuse on any dataset: introduce the data, approve a plan, run the transformations in code, ask questions in plain language, and package the result as a script. The best way to learn is to take the messiest spreadsheet you own and run it through this exact routine. Start small, keep the original, and let the AI do the parts that are boring. Within a week you will wonder why you ever cleaned data by hand.