Free Code lesson

Data cleaning with pandas: from a messy export to a table you can trust

Data cleaning with pandas means loading a messy file into a DataFrame and fixing missing values, duplicates, untidy text, numbers stored as text and inconsistent dates, in a repeatable order. This lesson explains each step in plain words and names the pandas functions involved, so you know what to write or what to ask an AI assistant to write.

For you or your whole team

Start with a useful first step.

7-day free trial. No credit card needed.

You will continue on the Tosheo.ai app to create your account and review the trial terms.

What this lesson builds

Skills worth putting to work.

Careful loading

Declaring missing-value markers and inspecting types before changing data.

Text and type fixes

Standardising text and converting numbers and dates with coerce and review.

Targeted removal

Using subset with drop_duplicates and dropna so only the right rows go.

Verification

Counting rows per step and reconciling totals with an independent source.

Example task / fictional source material

Write Tomasz's cleaning plan

Using the fictional Fernhill Gardens sample above, write a numbered cleaning plan in plain words that names the pandas function and key argument for each step and says how you will check it. You may ask an approved AI assistant to review the plan; use only the sample values.

Review criterion

Sensible order

Text is standardised before duplicates are removed, and types are converted before gaps are handled.

Review criterion

Correct functions

Names functions such as read_csv, to_numeric, to_datetime, drop_duplicates and dropna with suitable arguments.

Review criterion

Targeted removal

Duplicates use order_id and missing values use a subset, not the whole row.

Review criterion

Checks included

Row counts per step and a reconciliation with a trusted total.

Review criterion

Safe data use

No real customer data shared with an AI tool.

Copy and adapt

Prompts to try

Replace the parts in square brackets with your own details, and check every output before you use it.

Draft a cleaning plan

I have a CSV with these columns and problems: [column name: problem, using made-up example values]. Write a numbered pandas cleaning plan in plain English that names the function and key arguments for each step, in a sensible order, and says how to check each step.

Explain a pandas function

Explain the pandas function [function name] in plain English: what it does, its most important arguments and defaults, and one common mistake. Point me to the official pandas documentation page to confirm.

Review my cleaning steps

Here are my cleaning steps for a dataset about [topic]: [steps]. Identify any step that could remove valid rows, any order problem, and any check I am missing.

Why clean data in pandas rather than by hand?

Cleaning data in pandas is worth it because every step is written down and can be re-run on next month's export in seconds, while manual fixes in a spreadsheet are slow, easy to forget and impossible to audit.

Imagine a fictional garden centre group with four sites, Fernhill Gardens. The finance analyst, Tomasz, receives a monthly CSV of online orders. In the sample below, the same order appears twice, the site name is typed several ways, amounts include a currency symbol and a comma, dates mix day-first and year-first formats, and missing values appear as blanks, 'N/A' and '-'. Fixing it by hand takes a day, and the totals never quite match the payment provider's report.

order_id	site	order_date	amount	postcode
10231	Leeds	03/11/2026	£1,240.00	LS6
10231	Leeds	03/11/2026	£1,240.00	LS6
10232	leeds	2026-11-04	86.50	N/A
10233	YORK	05/11/2026	-	YO1
10234	Harrogate		142

How do you clean data with pandas step by step?

Clean data in pandas by loading it with missing-value markers declared, inspecting it, standardising text, converting numbers and dates, removing duplicates, deciding what to do with the remaining gaps, and then checking the result against a trusted total. Doing the steps in this order stops one fix from undoing another.

1. Load with missing markers declared

pandas' read_csv function reads the file into a DataFrame. Its na_values argument lets you list extra strings, such as 'N/A' and '-', to treat as missing, alongside the defaults pandas already recognises, such as empty fields. Declaring them here avoids hunting for them later.

2. Inspect before changing anything

Look at the first rows with head, the column types and non-missing counts with info, and the number of missing values per column by combining isna with sum. Write down what you find: at Fernhill, amount is stored as text and order_date has mixed formats.

3. Standardise text

Use the string methods on a column: str.strip removes leading and trailing spaces, and str.lower or str.title makes capitalisation consistent, so ' Leeds', 'leeds ' and 'LEEDS' become one site. Then list the unique values with unique to catch remaining spellings.

4. Convert numbers

Remove the currency symbol and thousands comma with str.replace, then convert with to_numeric. Setting its errors argument to coerce turns anything it cannot parse into a missing value instead of stopping, so you can count and review those rows rather than lose them silently.

5. Convert dates

to_datetime turns text into proper dates. For mixed formats, give an explicit format where you can, or set dayfirst for UK-style dates, and use errors set to coerce so unparseable dates become NaT, pandas' marker for a missing date. Note that dayfirst is a preference, not a strict rule, so check the results.

6. Remove duplicates

drop_duplicates removes repeated rows. By default a row counts as a duplicate only if every column matches; its subset argument lets you judge duplicates on chosen columns such as order_id, and keep decides whether the first or last copy survives.

7. Decide on remaining gaps

dropna removes rows with missing values, but by default it drops a row if any column is missing. Use its subset argument to drop only rows missing what you truly need, such as amount, or fillna to fill a gap with a stated value. Then use astype to set final column types, for example a category type for site.

What does a weak and an improved cleaning pass look like?

The weak pass loads the file with read_csv, calls dropna with no arguments and then drop_duplicates with no arguments. Because postcode is often blank, dropna removes a large share of valid orders, and because ' Leeds' and 'leeds ' still differ, some duplicates survive. Revenue comes out far too low, and nobody knows which rows went.

The improved pass declares 'N/A' and '-' as missing on load, strips and title-cases site, converts amount with to_numeric after removing '£' and commas, converts order_date with dayfirst and coerce, removes duplicates judged on order_id keeping the first, and drops only rows missing amount. It then prints how many rows each step removed and compares total revenue with the payment provider's report. Tomasz can now explain every difference.

How do you check that cleaning worked?

Check cleaning by counting rows before and after each step, comparing a key total with an independent source, and reviewing the rows that were coerced to missing. Run info again to confirm the column types and list unique values for categories such as site. If a step removed more rows than expected, look at them before continuing.

How can an AI assistant help with pandas cleaning?

An AI assistant can draft the cleaning steps if you describe each column, its problems and the rule you want, and it can explain unfamiliar functions. Describe the structure with made-up example values rather than pasting real customer data such as names, addresses or full postcodes. Ask it to report row counts after each step, and check every function argument it uses against the official pandas documentation, because assistants sometimes suggest options that do not exist or have changed between versions.

What are the common mistakes when cleaning data with pandas?

The most common mistake is calling dropna with no arguments and losing rows that were fine apart from an optional column. Others: removing duplicates before standardising text, converting dates without checking day and month order, overwriting the original file, and never comparing the cleaned total with a trusted figure.

Where does this lesson sit in Tosheo.ai?

This is a Code lesson in the Data Science and Analytics category of the data science course. Every Tosheo.ai subject starts with concepts and no-code practice, and the Code lessons are an optional deeper track: after sign-up, the full hands-on version of this lesson has you write and run each cleaning step in Python with pandas, marked against a rubric. If you are new to Python, start with the Python course. It builds practical skill, not a bootcamp or a job guarantee.

Sources and scope

pandas documentation: read_csv (checked 19 October 2026) ↗

pandas user guide: working with missing data (checked 19 October 2026) ↗

pandas documentation: DataFrame.dropna (checked 19 October 2026) ↗

pandas documentation: DataFrame.drop_duplicates (checked 19 October 2026) ↗

pandas documentation: DataFrame.astype (checked 19 October 2026) ↗

pandas documentation: to_numeric (checked 19 October 2026) ↗

pandas documentation: to_datetime (checked 19 October 2026) ↗

pandas documentation: Series.str.strip (checked 19 October 2026) ↗

Before you decide

A few good questions.

How do I remove duplicates in pandas?

Use the drop_duplicates function. By default it treats rows as duplicates only when every column matches; use its subset argument to compare chosen columns, such as an order ID, and keep to choose which copy stays.

What does dropna do in pandas?

dropna removes rows, or columns if you ask, that contain missing values. By default it drops a row if any value is missing, so use its subset argument to check only the columns you need.

How do I convert text to numbers or dates in pandas?

Use to_numeric for numbers and to_datetime for dates. Setting errors to coerce turns values that cannot be converted into missing values, which you can then count and review.

Do I need to know Python to follow this lesson?

No. This page explains each step in plain words. To write the code yourself, Tosheo.ai's optional Code track has the hands-on version after sign-up, and the Python course covers the basics first.

Give your team a path, not another playlist.

Explore role-specific lessons, practical tasks and the manager view.

Start free trial7-day free trial · No credit card needed

Data cleaning with pandas: from a messy export to a table you can trust

Start with a useful first step.

Skills worth putting to work.

Careful loading

Text and type fixes

Targeted removal

Verification

Write Tomasz's cleaning plan

Sensible order

Correct functions

Targeted removal

Checks included

Safe data use

Prompts to try

Draft a cleaning plan

Explain a pandas function

Review my cleaning steps

Why clean data in pandas rather than by hand?

How do you clean data with pandas step by step?

1. Load with missing markers declared

2. Inspect before changing anything

3. Standardise text

4. Convert numbers

5. Convert dates

6. Remove duplicates

7. Decide on remaining gaps

What does a weak and an improved cleaning pass look like?

How do you check that cleaning worked?

How can an AI assistant help with pandas cleaning?

What are the common mistakes when cleaning data with pandas?

Where does this lesson sit in Tosheo.ai?

Sources and scope

A few good questions.

Keep exploring.

Data science course for professionals

Python course from zero

All free AI lessons

Lesson: Google Sheets formulas for clean-up and lookups

Give your team a path, not another playlist.