Data Cleaning and Transformation

From Raw Data to Actionable Insights

Real-world data is rarely clean. You’ll find duplicates from double imports, missing values because a field was optional, date formats that change depending on who entered the data, and columns inherited from legacy tools. Before you can analyze anything, you must clean. This step has long been the most thankless part of the job. ChatGPT transforms it into a conversation: you describe the problem, it generates the cleaning code, executes it, and shows you the result for verification before you proceed.

Diagnosing Your Data Quality

Cleaning without diagnosis is like repairing something blindfolded. Start every session with an audit:

Analyze the quality of this file: missing values per column,
duplicates, detected data types, and potential outliers.

The report provides the percentage of missing values column by column, the number of duplicate rows, columns with inconsistent types—a mix of text and numbers almost always indicates manual entry—and extreme values likely to be typos. On an orders file, the audit might reveal customers without postal codes or an order for €950,000 instead of €950. A shorter prompt works too: “Check this file: what common issues do you find?” ChatGPT can then suggest several ways to fix them; ask about the consequences of each before choosing.

Handling Missing Values

This is the most frequent problem, and there is no one-size-fits-all solution: the right strategy depends on what the empty space means in your context. A missing amount is not the same as a missing email address.

Deleting incomplete rows works when missing data is scarce and non-critical. An email campaign has no use for a contact without an address: that row is unrecoverable.

Delete all rows where the "email" column is empty.

Filling with a default value makes sense when you know the logical value. If your business operates solely in France and the country field was optional, absence means “France”; an empty amount on a discount line means zero, not “unknown.”

Replace missing values in "country" with "France"
and those in "amount" with 0.

Finally, interpolation suits time series and continuous data, where a null value would create an artificial gap in the curve.

For missing values in "monthly_revenue", use the average
of the previous and following months for the same customer.

Removing Duplicates

Duplicates skew all downstream statistics: counting a customer twice doubles their contribution to revenue and artificially boosts their ranking. However, you must first define what constitutes a duplicate, since two orders from the same customer on the same day can be legitimate.

Identify and remove duplicates based on the columns
"customer_name" and "order_date". Keep the first occurrence.

Ask ChatGPT to show you the affected rows before removing them, and take the time to review them: this is precisely when you’ll see whether you’re deleting a true import duplicate or a genuine second order.

Standardizing Formats

Dates are the first hurdle. A file fed by multiple sources often contains “15/03/2026”, “2026-03-15”, and “15 March 2026”, which pandas cannot sort together: your monthly curve will remain silent until this is resolved.

Convert all dates in the "purchase_date" column
to YYYY-MM-DD format and flag those that cannot be converted.

Text poses more insidious difficulties because it remains readable to the human eye. “Paris”, “PARIS”, and “paris” count as three distinct cities; a trailing space after “Lyon ” creates a fourth; “Saint-Étienne” versus “St Etienne” splits your regional sales into two lines, neither of which is correct.

Normalize the "city" column: capitalize the first letter,
remove extra spaces, and group variants
(Saint/St, missing hyphens).

Finally, numbers often arrive formatted for display rather than calculation. As long as the column contains “1,250.50 €”, no summation is possible.

The "price" column contains values like "1,250.50 €".
Convert it to a decimal number (1250.50) and remove the euro symbol.

Creating New Columns

Transformation isn’t just about fixing; it’s about enriching. A date column becomes a quarterly axis for comparing periods; an amount column becomes a commercial segment usable in your analyses.

Create a "quarter" column from "order_date"
and a "revenue_tier" column that categorizes revenue as:
"Small" (< 1000), "Medium" (1000-5000), "Large" (> 5000).

ChatGPT generates the corresponding pandas code, using pd.cut() or logical conditions, and executes it. Then check the resulting distribution: if 90% of your customers fall into “Small”, your thresholds are poorly calibrated and the segmentation will teach you nothing.

Exporting Cleaned Data

Once cleaning is complete, retrieve the clean file. The download button, located at the top right of a table produced by ChatGPT, saves it in CSV format. For a specific format, ask for it:

Export the cleaned DataFrame as CSV (UTF-8, semicolon separator)
so I can download it.

Then open the file in your spreadsheet software and compare the row count with the original: you walk away with a file usable elsewhere—Excel, BI tool, next session—without having to redo everything the following week. Always work on a copy, and keep the original file intact so you can revert if needed.

Your Turn

Your first cleanup.

1. Import a copy of a real file and request a quality audit
2. Handle missing values column by column, based on what the empty space means
3. Define what constitutes a duplicate, examine the affected rows, then remove them
4. Standardize dates, cities, and amounts
5. Download the clean file and compare the row count with the original

Official sources: Extracting Insights with ChatGPT Data Analysis · Data analysis with ChatGPT (OpenAI Help Center)

Key Takeaways

• Always start with a quality audit before analyzing
• ChatGPT handles missing values, duplicates, and inconsistent formats in a few messages
• Systematically verify intermediate results—ask for a preview after each operation
• Export the cleaned file to reuse it in other tools if needed
• The Python code is visible: you can copy and reuse it in your own scripts