Cleaning messy data: missing values, duplicates, and data types

The report that didn't add up. Picture Monday morning. Your online shop's order export lands in your inbox, and your manager wants last week's revenue and average customer rating before ten. You total the columns and something feels off. Revenue is higher than the payment system says, and the rating looks oddly low. So what went wrong? Nothing in your maths. Some orders appear twice, some survey ratings are blank, and a few prices were saved as text with a dollar sign. Real exports are almost always messy like this. If you skip cleaning, every decision you make from that data inherits the mess. In this lesson you'll learn to spot these problems and fix them in a few lines of pandas.

What you will learn. By the end of this lesson you'll be able to do three things. First, inspect a DataFrame and find the three most common problems: missing values, duplicate rows, and columns stored as the wrong data type. Second, fix each problem with a short pandas command you can reuse on every export. Third, decide sensibly what to do with blanks, because there's no single right answer. A missing price, a missing survey rating, and a missing support channel each call for a different choice. We'll work through orders, survey ratings, and support ticket times so you see all three situations.

A cleaning routine. Cleaning goes faster when you follow the same routine every time, like a pilot's pre-flight checklist. Start by inspecting the raw export to see what's wrong. Then fix data types, so numbers really are numbers. Next, remove duplicate rows. Then handle missing values, the empty cells. Finally, check your totals against something you trust, such as the figure in your payment system. The order matters, and later in this lesson you'll see exactly why fixing types before removing duplicates catches rows you'd otherwise miss. Let's take each step in turn.

Step one: inspect. Here's a small order export with the problems built in. Three commands tell you almost everything. The first, d types, shows each column's data type, meaning what kind of value pandas thinks it holds: whole numbers, decimals, or text. Notice that price says object, which is pandas' word for text. That's a red flag for a money column. The second command, is n a, then sum, counts the blank cells in each column. Pandas treats both None and N a N, short for "not a number", as missing. We have one missing price and two missing ratings. The third command counts repeated order IDs, and we find one. Three problems, found in three lines.

Step two: fix data types. Why does the data type matter so much? Watch what happens when we sum prices stored as text. Pandas doesn't add them, it glues them end to end, like joining words into one long string. You get nonsense instead of a total. The fix takes two steps. First, remove the dollar sign with string replace. Then convert the result to numbers with p d dot to numeric. Now pandas prints a sum of fifty-five dollars and seventy-five cents, and we'll check that figure by hand in a moment. The last two lines show what to do when a column holds real junk like "N slash A": add errors equals coerce, and pandas turns anything it can't read into a blank you can handle later.

Step three: remove duplicates. Duplicates usually come from a checkout page that was submitted twice, or from two exports pasted together. Each copy inflates your revenue. Order one oh three appears twice here, so before cleaning pandas prints five rows and a total of seventy-five dollars and seventy-five cents. We call drop duplicates with subset equals order ID, which tells pandas that two rows are the same order when their order ID matches, even if another column differs slightly. Pandas keeps the first copy and drops the rest. Afterwards it prints four rows and a total of fifty-five dollars and seventy-five cents. The last line compares that clean total with the payment system's figure and prints True. Always print the before and after, so you know exactly how much the cleaning changed.

Checking the totals by hand. Let's check those totals by hand. Add the four real orders: twelve fifty plus eight plus twenty plus fifteen twenty-five is fifty-five seventy-five. Now add the repeated order: fifty-five seventy-five plus twenty is seventy-five seventy-five, the too-high total from before. Remove the copy: seventy-five seventy-five minus twenty is fifty-five seventy-five. So the clean revenue is fifty-five dollars and seventy-five cents. The clean total matches the payment system, and that check is how you know the cleaning worked.

Step four: missing values. Missing values need a decision, not a reflex. You have three options. You can drop the row, which suits a value you truly need, like the price of an order. Without it the row can't contribute to revenue. You can fill the blank with a sensible default, like the word "unknown" for a missing support channel. Or you can simply leave it blank. Here's the part people often don't realise: pandas skips blanks when it calculates a mean, a sum, or a count of values. So leaving a blank is often the most honest choice. The pandas guide on missing data, shown here, covers every option in detail.

Worked example: support tickets. Let's apply those choices to support tickets, one column at a time. Ticket two has no channel recorded. We still want to count it, so fill n a replaces the blank with "unknown". Ticket two is also missing its resolution time. For the average we don't need to do anything, because pandas averages only the three known times and prints twenty. If a later chart needs every row to have a time, drop n a with subset equals minutes removes only the rows missing that value and keeps everything else. Notice that you decide per column, not for the whole table.

Checking the average ticket time. Let's check that twenty by hand. Add the three known times: twelve plus thirty plus eighteen is sixty. Divide by three tickets: sixty divided by three is twenty. So the average ticket takes twenty minutes, and ticket two's blank is simply not counted.

Why types come before duplicates. Remember the routine put fixing types before removing duplicates? Here's why. These two rows are the same order, but one system wrote the price with a dollar sign and the other didn't. As text, "dollar twenty" and "twenty" are different strings, so drop duplicates, which compares every column here, keeps both rows. After we strip the sign and convert to numbers, both prices are the same twenty, and drop duplicates correctly removes the copy. Two rows become one, just by doing the steps in the right order. Clean the formats first, and then your duplicate check can see what's really repeated.

Common mistake: filling blanks with zero. The most common cleaning mistake is filling every blank with zero, because it makes the table look tidy. Look at these six survey answers, two of them blank. The first print lets pandas skip the blanks, and it prints three point five. The second fills the blanks with zero first, and it prints about two point three three. Same survey, two very different averages. Let's see exactly why.

Why zero drags the average down. Here's the arithmetic. Add the four real ratings: five plus four plus three plus two is fourteen. Divide by four real answers: fourteen divided by four is three point five, the honest average rating. Now the zeros make it six answers: fourteen divided by six is about two point three three. You've just invented two furious customers who never said anything. A blank means no answer, not the worst answer. Only fill with zero when zero is genuinely true, such as zero refunds on an order.

Recap. Let's recap. First, always inspect before you calculate. Three quick commands reveal the wrong data types, the blank cells, and the repeated rows. Second, follow a fixed routine: fix types so numbers are numbers, remove duplicates, handle missing values, and finish by checking your totals against a figure you trust. Doing types first lets your duplicate check catch rows that only looked different. Third, treat blanks as a decision you make per column. Drop rows only when the value is essential, fill when there's a sensible default, and leave blanks alone when pandas can simply skip them. Never fill a missing rating with zero.

Your exercise. Now it's your turn. Build a small orders DataFrame of your own with ten rows. Give it two repeated order IDs, write a few prices as text with dollar signs, and leave two survey ratings blank. Before cleaning, print the row count and try to total revenue, so you see the problem for yourself. Then run the routine: convert prices to numbers, drop duplicates on order ID, and leave the blank ratings alone. Print the revenue and average rating again, and write one sentence explaining how much each step changed the result. In the next lesson we'll group this clean data to answer real business questions.