Pandas for Analysts: Cleaning and Checking a Small Dataset
A worked pandas example: clean a hypothetical orders export, remove duplicates, group revenue by channel and check row counts and totals against the input.
6 min readBranislav Mateas
Copy post as Markdown
On this page
Cleaning a dataset in pandas takes a few lines. The harder part is knowing that the result still describes the same orders as the input. A wrong cleaning step rarely raises an error. It drops a row, skips a value or merges two labels, and the final table looks fine.
This article follows one small, hypothetical orders export through one transformation: revenue by channel. Every step has a check that compares the output with the input, and every number below is worked out by hand. The code follows the pandas 3.1.1 documentation but has not been run. Setting up Python and structuring a full script are out of scope.
The input: nine rows with four problems
The hypothetical export has nine rows. It is small on purpose, so every problem is visible.
Channel labels differ in case and in a trailing space (rows 1 to 4).
Row 6 stores revenue with a thousands separator.
Rows 4 and 5 are exact copies.
Order 1007 appears twice with different revenue (rows 8 and 9).
Row 7 also has no channel. Read as written, the revenue column adds up to 1,785.50: 120 + 80.50 + 45 + 60 + 60 + 1,200 + 35 + 90 + 95. That number is the starting point for every later check.
The target is one row per channel with an order count and a revenue sum. In this example, that means one row per order before grouping.
Fix labels and numbers before removing anything
Read every column as text first. read_csv documents dtype=str as the way to keep values as they appear in the file. By default, it still reads empty cells as missing, so row 7's channel arrives as NaN.
import pandas as pd
raw = pd.read_csv("orders.csv", dtype=str)
df = raw.copy()
df["channel"] = (
df["channel"].str.strip().str.lower().fillna("unknown")
)
test = pd.to_numeric(df["revenue"], errors="coerce")
print(df.loc[test.isna(), ["order_id", "revenue"]])
df["revenue"] = pd.to_numeric(df["revenue"].str.replace(",", ""))
str.strip removes leading and trailing whitespace, and str.lower lowers the case. Rows 1 to 4 now share two labels: email and paid search. fillna gives row 7 an explicit unknown label instead of leaving it empty.
The revenue step checks before it fixes. With errors="coerce", to_numeric turns unparseable values into NaN. The printout should show one row: order 1005 with 1,200.00. That tells you what the fix must handle. str.replace treats the pattern as literal text by default, so the comma is removed without a regular expression. The final to_numeric call uses the default errors="raise", so any value the fix missed stops the code.
Do not keep the coerced column as the result. Series.sum skips NaN by default. A revenue column with row 6 coerced to NaN would still sum without an error. Using the deduplicated orders from the next section, that total would be 435.50 instead of 1,635.50.
Separate exact copies from conflicting rows
The two duplicate problems need different decisions.
duplicated compares all columns by default. Only row 5 matches an earlier row exactly, so the count is 1. Run this after cleaning the labels. If row 5 had said Email instead of email, it would only match row 4 after str.lower.
Rows 8 and 9 are not exact copies, so the first drop_duplicates keeps both. With keep=False, the printout shows every row whose order_id repeats. Choosing between 90.00 and 95.00 is a business rule, not a pandas setting. This example assumes the later row is a correction, so keep="last" keeps 95.00. That only works if the file is sorted by time. A real export should have an update timestamp to sort on first. is_unique then confirms one row per order.
The table now has 7 rows. The removed rows had revenue of 60.00 and 90.00, so the expected total is 1,785.50 − 60 − 90 = 1,635.50.
This uses named aggregation to set the output column names. Worked by hand, the result is:
channel
orders
revenue
email
2
180.00
organic
2
1,295.00
paid search
2
125.50
unknown
1
35.00
Total
7
1,635.50
Email is orders 1001 and 1004 (120 + 60). Organic is 1005 and 1007 (1,200 + 95). Paid search is 1002 and 1003 (80.50 + 45). The totals match: 2 + 2 + 2 + 1 = 7 orders and 180 + 1,295 + 125.50 + 35 = 1,635.50.
The unknown label matters because groupby drops rows with missing keys by default (dropna=True). Without fillna, order 1006 would vanish from the summary and its revenue would fall to 1,600.50. Here dropna=False is a second safety net. It keeps any missing key that the label step did not catch.
Checks that compare the output with the input
Write the expected numbers down before running the checks. A check that only prints a value invites you to accept whatever appears.
Row count out: were exactly the expected rows removed, and no others?
No missing revenue: did every value convert to a number?
Grouped count: did any row fall out during grouping?
Grouped and row-level revenue: do both match the hand-reconciled total?
The reconciliation behind these checks is short. 9 rows minus 1 copy minus 1 superseded row gives 7 rows. 1,785.50 minus 60 minus 90 gives 1,635.50. If a check fails, the difference points to the step. A gap of 1,200 points to the revenue conversion. A gap of 35 points to grouping.
The round call is a precaution. Decimal amounts are stored as floating point numbers, so sums of values with cents can differ slightly from exact decimal arithmetic. The same reasoning about row counts and totals applies when joins multiply rows in SQL.
When the dataset is larger
A real export will not fit in a table you can read. The method stays the same. Record the input row count and total first. Name each row you remove and why. Check that the output adds back to the input. When a total does not match, print the rows that explain the gap instead of adjusting the code until the number looks right.
