Cleaning

So far, you know how to read a CSV file into a list of dictionaries, with one dictionary for each record. Before we get to analysis, we need to talk about data cleaning. If there are errors in the data, we might not get very far with analysis.

First, we'll talk about type conversion. Then we'll get to cleaning errors.

Sloth thieves

Photo by Michael Jerrard

Sloths are cute, and harmless. Well, that's how it seems. In fact, sloths are outstanding pickpockets. Nobody suspects them, but they can flash into a pocket or purse, and extract some cash.

Seriously! Sloths are a hazard!

Here's a CSV file (that's a link) with data on sloths at one slothary. Here's part of the file:

Let's write a program to total the amount stolen by sloths. I'll open a new .py file, and put the function read_csv_data_set into it, the thing we talked about earlier. This program will show how many sloths there are in the file.

The first line reads the data, and the second shows the number of items in the list.

Good! for loops over sloth_theft:

• The first time through the loop, sloth is set to sloth_theft[0], and the print runs.
• The second time through the loop, sloth is set to sloth_theft[1], and the print runs.
• The third time through the loop, sloth is set to sloth_theft[2], and the print runs.
• ...

Correct!

Totaling

Now let's write a program to total up the amounts. Here's some code:

Try this in the console:

(type shows the data type of the param.)

(From the console:)

Aye, that's it!

The data in the file is int, but it's a currency amount, and they can have decimal parts. I'd make it a float, based on the fact that it's a currency.

Remember, you might use the program again, with a different data set, maybe from a different slothary. They could use fractions, like 260.85.

Missing and bad data

Often data will have mistakes. Let's see how to handle missing and bad data.

Here's another data set. Cthulhu teaches a database class to goats. It had an idea for improving a lesson. It gave some goats the old lesson, then a test (the "before" test), then the new lesson, then another test (the "after" test). What happened to the scores?

OK, a poor experimental design. Tell Cthulhu, if you like. Though it might eat you. With picante sauce.

Here's part of the data set:

Aisha got 17 on the first test, 16 on the second.

Let's mess up the data a bit. You can download the file (that's a link).

• Line 3 is missing the goat name, but still has the "s.
• Line 4 is missing the name field altogether, quotes and all.
• Line 5 has a negative test score.
• Line 6 is missing a test score.
• Lines 7 and 8 have text for a score, with and without "s.
• Line 9 has a missing comma.

Let's see what happens when you read the bad data.

Nice!

Let's see what read_csv_data_set makes of the bad data.

Here's that record again:

There's one comma. Python's CSV library isn't smart enough to break up "15 19" into two fields. "Carrie" goes into the first field, "15 19" into the second. The third isn't there, so you get the special value None of the special type NoneType.

Let's use the console to explore further. (Recall the console remembers variable values from the last program run.)

goat_scores[7] is Carrie's record. Try goat_scores[7] in the console to confirm this.

We want to get the value for After, so try this: goat_scores[7]['After']. Notice you can use [] in the line twice. The first pair accesses list element 7, which is a dictionary, and the second pair gets the element After from that dictionary.

So, if we look at an MT string in the console, like goat_scores[1]['Goat'], we get nothing. If we look at None in the console, like goat_scores[7]['After'], we get nothing.

Are None and an MT string the same?

Try this:

That's true, the console tells us.

Here's the data again:

That's it! An MT string and None are not the same.

Another thing to try:

Computations

Now run it.

What to do?

Here's some of the data again.

There are seven broken lines in the entire data set, and 43 that are OK. If we want to compute totals for Before and After...

Yes. A question, though. For the record...

Before is invalid, but After is OK. Do we omit the entire record, or just the -22? If we omit just the -22, we'd still add 23 to the After total.

With a record like...

The most common things would be:

• Drop the entire record from the analysis, called case-wise deletion.
• Drop -22, but keep the rest of the fields.
• Replace -22 with the average of all the other values for that field.

What you do depends on how much data you have, what questions you're asking, and other things.

In this course, we'll do case-wise deletion only. That is, if a record has any errors in any field, we ignore it. It's the most cautious approach.

A validation function

Let's imagine code we'd like to have in the main program if we want to compute totals. This didn't work:

Imagine we had a function returning true if a record was OK, or false if the record has errors. If we had such a function, we could fix the program with one line:

Aye.

Deciding what functions do what can make your life easier. Let's list what could go wrong.

• Name could be MT or None.
• Before could be nonnumeric (MT, a string, or None), too small, or too large.
• After could be nonnumeric (MT, a string, or None), too small, or too large.

Aye!

Yes, that's it. That's common, and very effective.

Decomposition is the most important thing in making programming easier, and programmers more productive.

Checking Before and After

Let's do this one first, since you've done some of this already.

Good, that will work. You send a score into a validation function. You call it like this:

Aye, you could. You'd need to send in the record, so the function could look up the key: is_score_field_ok(record, key).

You'd call it like this:

That would work, too. I prefer Ethan's way a little, since the function is more generic, and more reusable in other programs.

However, both are correct. It's common for different solutions to be equally correct.

The function should detect when:

• The value can't be made numeric. E.g., it's a word.
• The value is too small or too large.

Yep. Looks good.

Test is_score_ok

Did is_score_ok work?

Checking an entire record

Let's see where we are. We're writing code to test a goat score record, like this: "Aisha",17,16. The function's signature is is_record_ok(goat_record), returning true or false.

Here are the errors is_record_ok should look for:

• Name could be MT or None.
• Before score could be nonnumeric (MT, a string, or None), too small, or too large.
• After score could be nonnumeric (MT, a string, or None), too small, or too large.

The last two are almost the same, so we wrote a function what would work for both. We wrote is_score_ok(value).

I love it! Each step is simple. Comments make it easy to follow. Nice work!

Assembling everything

We wrote a program to total the Before and After scores, but it didn't work, because of errors in the data. We decided we wanted something like this:

Most of the complexity is in validation, but we hid that away in is_record_ok. When we're writing is_record_ok, we didn't need to think abut the rest of the program. We just need to know it takes a record, and returns a boolean.

Here's is_record_ok:

We made another function for numeric and range checking: is_score_ok. It takes a value, and returns a boolean. We didn't need to think about how is_score_ok would be used when we were writing it. We'd already done that. We defined what is_score_ok should do, and just focused on it when writing the function.

Finally, we had our entire program.

Decomposition - breaking a task into bite-sized chunks - is a programmer's second-best friend. Their best friend is their doggo, of course.

Summary

• read_csv_data_set data is always strings. You need to convert it to int or float if you want to do numeric work.
• Python has a special constant None used when data is missing.
• If there's an error in a record, and you're using case-wise deletion, you drop the entire record.
• Decomposition: Programs are too complex to think about all at one time. You need to break them into pieces, and work on one piece at a time. Of course, the pieces must work together.

Exercises