Sometimes data isn't obviously wrong or missing, it has a more subtle error.

Many of these errors can only be found by using our knowledge of the data and

how it was collected.

We'll be continuing to fix our example data set.

Hopefully you saved the changes you made in the previous lessons, but

if not, the updated versions are available in the teacher's notes.

There's also link to the code I'll be using, if you'd like to download that.

Go ahead and pause this video while you download any files you need and

setup your programming environment.

Ready, great.

We're going to start off by talking about nonsensical or impossible data.

These are data entries that are nonsensical or impossible, based on

the way that the data site was created or the nature of what was recorded.

First, we want to load in the libraries we'll need, numpy and pandas.

We'll also be working with all three files again, so I'll load those as well.

Now, let's find some impossible data.

Weight is a good example for impossible data.

We know a person can never have a negative weight.

So let's print a description of our weight column.

According to the summary of the weight column,

the lowest recorded weight is negative 149 kilograms.

We are not sure with the way we are suppose to have been, so

we can just remove all values less than zero.

First we find all rows with the way less than zero.

Then we replace all those values with not a number.

Now, we can view the description of our data again.

Now our lowest weight is just over three kilograms, a feasible value for a baby.

This is a good sanity check for weight, but it's very simplistic.

After all, we have all ages in our data set.

And a feasible weight range for

a two year old would be drastically different than one for a 50 year old.

If we wanted to be more careful, we could design more complicated checks and

take a participant's age into account.

More complicated checks are outside the scope of this course.

But something to keep in mind as you're cleaning your own data.

Categorical values can also have impossible values.

Let's take a look at the possible entries for weight comment.

The column for

weight comment as four possible values, coded as one through four.

Any other entry in that column is an error.

Let's take a look at the unique entries in the weight comment column.

It looks like the numbers 7 and 11 have also been entered.

Since they aren't valid entries, we need to remove them from our data set.

Again, we'll find all the invalid entries and replace them with not a number.

Let's see what our new data entries are now.

There we have it, only entries of one through four, or not a number.

Often data entries are problematic because they have extreme values.

According to Guinness World Records,

the heaviest person in the world weighed 635 kilograms at their heaviest.

Let's take another look at our weight column.

Our data set has a maximum weight of over 12,000 kilograms.

That's much higher than the world record.

Something must be wrong with those entries.

Since they don't make sense, let's remove all weights heavier than 635.

We can do the same thing as before.

Find all the rows with a weight above 635.

Then replace those rows with not a number.

And then finally we can look at our data again.

Now the maximum weight is a much more reasonable 193 kilograms.

Some analyses will also remove outliers, or values too far from the average.

Values too far from normal could bias the data set, or

there may be something wrong with this data to cause those values.

On the other hand, if we don't have a good reason to suspect a problem with them

these outliers may be valid, although unusual, data entries.

In that case, removing them eliminates important information from the data set.

Whether you remove outliers or not, will depend on your particular circumstances.

One way to find outliers is to determine how many standard deviations a value

is from the average for that group.

This is sometimes called the z-score.

To find the z-score of the maximum and minimum weight value,

we first calculate the mean and standard deviation in the entire column.

Note that we need to use nanmean and nanstd so that the functions can deal with

the present of not a number of values correctly.

Let's find the z-score for these maximum and minimum weights.

Like with the mean and standard deviation, we use nanmin and nanmax to find

the maximum and minimum values, while ignoring not a number entries.

To get the z-score for the lowest weight,

we subtract the mean from our minimum value.

Then we divide by the standard deviation.

We do the same thing to get the z-score for the maximum weight.

And then let's print out the z-scores we just calculated.

And I misspelled high as height.

There we go. Our maximum weight is four standard

deviations from the mean.

And our minimum weight is one standard deviation below the mean.

Finally, let's look for saturated data.

According to the code book, all ages above 85 were recorded as 85.

This means that the age value saturates at 85,

anything above that value is a mistake.

Also, we don't know the true age of anyone recorded as 85,

only that they are at least 85.

If a person's exact age is important,

we may want to remove all saturated entries or run a separate analysis for

people 85 and older, that does not include age as a term.

In other cases,

grouping all those over 84 into a single age may not affect the analysis.

Let's see what our maximum age is.

We have at least one entry above the saturation limit,

with a maximum listed at 109.

If we feel confident that someone just forgot to list ages at a maximum of 85,

we can replace all higher values with 85.

If we're concerned that some other error caused the problem,

it may be better to remove the values entirely.

Let's replace all values above 85 with a value 85.

Remember to save all the changes we've made to the data files.

That's it for the video portion of this lesson.

Now it's time for you to try out what you've learned on your own.

The teacher's notes have a link to the practice notebook for this lesson.

There are two practice exercises for you this time.

You'll need to check some of the columns in the occupation file for

non-sensible values.

Then you'll find the z-score of the maximum and

minimum values of column OCQ180 in occupation.

Make sure you don't count values 7777, or 9999, since those

are extra code values and not actually the number of hours worked.

Remember to save your data again when you're finished.

Afterwards, it's time for another quiz.

When you've completed the quiz,

we'll talk about ways to help automate the data cleaning process.