Project: build a clean orders dataset and answer three business questions

Your manager's Monday questions. It's Monday morning. Your manager forwards last week's order export from the online shop and wants three answers before the ten o'clock meeting. Which product category earned the most? Which marketing channel brings the biggest orders? And where does most of our revenue come from? You open the file and spot trouble straight away. One order appears twice, one has no amount, and someone typed a category in lower case. If you sum that file as it is, you'll hand over wrong numbers with total confidence. In this project lesson we bring together everything from this course and go from a messy export to three answers you can defend.

What you will learn. By the end of this lesson you'll be able to do three things. First, inspect a raw export and write down exactly what's wrong with it, before you touch a single number. Second, clean it with a short, repeatable set of pandas steps. You'll remove duplicate rows, turn text amounts into real numbers, fix inconsistent labels, and make a clear decision about missing values. Third, answer three real business questions with groupby. Groupby is the pandas tool that splits rows into groups and summarizes each group, much like a pivot table in Excel. Keep a notebook open and type along. The whole project fits on a few screens.

The project plan. Here's the plan, and it's the same plan you'll use on every dataset from now on. We load the raw export into a DataFrame, which is pandas' version of a spreadsheet table. Then we inspect it: how many rows there are, what type each column is, and where the blanks and repeats are. Only then do we clean, because you can't fix what you haven't measured. With a clean table, each business question becomes one group-and-summarize step. At the end we turn the numbers into plain sentences for the manager. Think of it like cooking: you check the ingredients, wash them, and only then cook.

Load and inspect the export. Here's the export. It has eight rows, each with an order id, the channel the customer came from, the product category and the amount. We read it with read CSV, using io dot StringIO so the text behaves like a file. Now the inspection. Shape tells us there are eight rows and four columns. The dtypes line shows the type of each column. Look closely: amount isn't a number. It's text, because every value carries a dollar sign. Is N A dot sum counts the blanks in each column, and amount has one. Duplicated counts rows that exactly copy an earlier row, and there's one: order ten oh two appears twice. And did you spot the lowercase hats in order ten oh four? Write all four problems down.

Clean it in four moves. Now we fix the four problems, one step each. Drop duplicates removes the second copy of order ten oh two. Next comes amount. We strip the dollar sign with string replace, and then to numeric turns the text into real numbers. After that, string title capitalizes every category, so lowercase hats becomes Hats and joins its group. The last problem is the missing amount. We can't know what order ten oh three was worth, so we set that row aside with drop N A and note it in our report. We don't invent a number. The printout shows six clean orders, and amount is now float sixty-four, a proper number column.

Question 1: top category by revenue. Question one: which category earned the most? From here on, each snippet starts from the six clean orders we just built. They're typed in so each snippet runs on its own. We group by category, take the amount column, and sum it. That's the pandas version of a pivot table with category in the rows and the sum of amount in the values. Sorted from largest, the printout puts Bags first, then Shoes, then Hats, and prints the total below. Let's write those sums out by hand so you can see where every number comes from.

Question 1 worked out. Here are the sums behind that printout. Bags: its two orders are one hundred and twenty plus eighty, which is two hundred dollars. Shoes: its two orders are fifty-nine ninety plus forty-five fifty, which is one hundred and five dollars forty. Hats: its two orders are twenty-five plus thirty, which is fifty-five dollars. Add the three for the total: two hundred plus one hundred and five forty plus fifty-five is three hundred and sixty dollars forty. Half of the total is three hundred and sixty forty divided by two, which is one hundred and eighty dollars twenty. Two hundred is bigger, so Bags has more than half. So the answer for the manager: Bags earned the most, two hundred dollars.

Question 2: biggest orders by channel. Question two: which channel brings the biggest orders? The measure for that is average order value. It's the mean amount per order, which means the total divided by the number of orders. We group by channel and use mean instead of sum. Notice that changing one word changed the question. The printout puts ads on top, then organic, then email. Rounding to two decimals keeps the output readable, like setting a cell's number format in Excel. Let's work the three averages out by hand.

Question 2 worked out. Here's how those averages come out. Ads: two orders, one hundred and twenty plus forty-five fifty, divided by two, is eighty-two dollars seventy-five. Organic: two orders, eighty plus thirty, divided by two, is fifty-five dollars. Email: two orders, fifty-nine ninety plus twenty-five, divided by two, is forty-two dollars forty-five. Is ads almost twice email? Twice the email average is two times forty-two forty-five, which is eighty-four dollars ninety. Eighty-two seventy-five is just under that. So ads brings the biggest orders, eighty-two dollars seventy-five per order, almost twice email.

Question 3: where revenue comes from. Question three: where does our revenue come from? Now we want each channel's share of the total. We sum the amount per channel, divide by the grand total, and multiply by one hundred to get a percentage. The printout puts ads first, then organic, then email, rounded to one decimal. Let's write out the channel totals and the shares, step by step.

Question 3: channel totals. First each channel's total. Ads total: one hundred and twenty plus forty-five fifty is one hundred and sixty-five dollars fifty. Organic total: eighty plus thirty is one hundred and ten dollars. Email total: fifty-nine ninety plus twenty-five is eighty-four dollars ninety. Check: one sixty-five fifty plus one hundred and ten plus eighty-four ninety is three hundred and sixty dollars forty, the grand total.

Question 3 worked out. Now the shares of three hundred and sixty forty. The ads share, one sixty-five fifty divided by three sixty forty, times one hundred, rounds to forty-five point nine percent. The organic share, one hundred and ten divided by three sixty forty, times one hundred, rounds to thirty point five percent. The email share, eighty-four ninety divided by three sixty forty, times one hundred, rounds to twenty-three point six percent. Check: forty-five point nine plus thirty point five plus twenty-three point six, they add up to one hundred. So ads brings the most revenue, forty-five point nine percent.

One pattern behind all three. Step back and look at the three answers together. Each one followed the same sentence: split the rows by a key, pick a column, and summarize it. Only the summary changed. We used sum for totals, mean for averages, and sum divided by the grand total for shares. Count, which gives the number of orders, fits the same slot. Notice that questions two and three agree this week, but they're different questions and could easily disagree next week. The pandas user guide calls this split, apply, combine, and its group by page is worth a bookmark. Once you see this pattern, most weekly questions about a shop are one line of code away.

Common mistake: grouping dirty data. The most common mistake in a project like this is jumping straight to the groupby and skipping the cleaning. Keep the duplicate, and Bags reads one hundred and twenty plus one hundred and twenty plus eighty, which is three hundred and twenty dollars, because order ten oh two is counted twice. The right Bags total is one hundred and twenty plus eighty, two hundred dollars. A second trap is to fill the blank with zero. Then the ads average is one hundred and twenty plus zero plus forty-five fifty, divided by three, about fifty-five dollars seventeen. The right ads average is one hundred and twenty plus forty-five fifty, divided by two, eighty-two dollars seventy-five. Neither mistake throws an error. That's why we inspect first and fix each problem on purpose.

Your exercise. Here's your task before the next lesson. Add two rows to the raw export. The first is a new order, ten oh eight, from ads, in the Hats category, for ninety-five dollars. The second is another copy of order ten oh seven. Rerun the cleaning steps and all three questions. Then write down which answers changed and which stayed the same. Does Bags still lead on revenue? Is ads still the biggest spender per order? Finally, write one sentence you'd send your manager, and include how many orders the answer is based on. If the duplicate slips through, your cleaning step isn't doing its job.

Recap. Let's recap. First, inspect before you touch anything. Shape, column types, blanks and duplicates tell you what's wrong. Second, clean on purpose, one problem per step, and record every row you removed. That way your manager knows the answers rest on six orders. Third, with a clean table, most business questions are a single groupby with sum, mean, or a share of the total. You've just done a complete small analysis, from raw export to three answers you can defend. That's the whole workflow of this course in one project.