Cleaning data

Awa's sales staff fill in the ledger by hand, and some days they go too fast. Look at this file: there are empty cells, and one sale seems to be written twice. And if she calculates anyway? Then her result will be wrong, and nothing will warn her. So we must clean the data before calculating.

To find the gaps, the isna method looks at each cell and answers true if it is empty. Then the sum method counts these answers, column by column. Look on the right: the empty cells are surrounded by dotted lines.

The result is clear: one city, one item and one amount are missing. So each column has a single empty cell, and never on the same row. Now that she knows this, Awa must decide what to do with each gap.

Without the city, this sale cannot be used in any calculation by city. So Awa removes this row with the dropna method, stating the column to look at. Look: row number 4 disappears. The word subset means a part: we ask it to look only at the city column, otherwise it would remove any row with a gap anywhere. Notice also that row 5 keeps its number: pandas does not renumber the rows.

For the item, the amount of the sale is still useful, so we don't want to lose the row. The fillna method fills the empty cell, here with the word Unknown. She writes the result into the same column, because otherwise the table would not change. Look at row 2: the word is now in the cell.

And the empty amount, does she use the average? Look at the fabric sale of 50,000 francs. This one sale pulls the average up to about 9617 francs, while nearly all the others are much smaller. The median, however, is the middle value when we put the amounts in order, and here it is 1900. Since there are six amounts, the middle falls between 1500 and 2300, and we take the value halfway between them.

So Awa fills the empty amount with the median. This time, the table has no empty cells left. Look at row 6: it now contains 1900, so the big fabric sale has not distorted this value.

Now look at rows 1 and 5: same city, same item, same amount. The duplicated method spots a row identical to another one above it, and sum counts them. There is one. The word duplicated means copied twice, and it marks only the second row, not the first.

So she can delete it right away? Careful: two customers may very well buy the same rice at the same price. So she first checks that it is a typing mistake. Only then does the drop_duplicates method remove the duplicate. It keeps the first row and removes only the second.

Here is one last trap. In this file, someone wrote the word unknown instead of an amount. So pandas reads the whole column as text. Look at what the sum gives: the amounts are stuck together, like words.

The to_numeric function turns the column into numbers. And with errors equals coerce, whatever it cannot read becomes an empty cell, instead of causing an error. The word coerce means to force: we force the conversion, and we accept losing what is not a number.

This time, the sum gives 4600, and the empty cell reminds us that one sale still needs checking.

In the lesson, clean a real dataset yourself, then reread a script written by an artificial intelligence. Try without watching the video again: this effort is what makes you remember.