In our last lesson, we talked about missing data in our dataset.

Now we're going to take a look at the opposite problem,

a dataset with duplicate information.

We want to make sure each example is only counted once.

Otherwise, our analysis could place too much importance

on the related examples since, from the computer's perspective,

those sets of values occur several times in our dataset.

We'll be looking for three types of duplicates.

Perfect duplicates, where the rows or columns are completely identical.

Split duplicates, where information is split between the duplicated rows or

columns.

And conflicting duplicates, where there are discrepancies between the data

contained in different rows or columns.

When dealing with duplicates, it's important to keep in mind

whether it's possible to have perfectly duplicated entries.

For example, we could have two people named Sam Smith who are 54 years old.

But it's less likely to have two people with the same name, address, and birthday.

We'll be using data files you saved after the last lesson.

But you can also download the exact version I'll be using

from the links in the teacher's notes.

There's also a link to the code I'll be using.

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

set up your programming environment.

Once you're ready, let's get started.

Let's load in the Pandas and Numpy libraries.

And now we load in our data.

Let's load in all three data files this time.

Let's look for any duplicated rows in demographics dataframe.

We know the sequence column should have a unique number for each person.

So let's see if it has any duplicates.

First, we sort the dataframe so

that the numbers in the sequence column are in ascending order.

This isn't necessary, but it does make the data easier to look at.

Now we use the Panda's duplicated method to find all rows where the sequence column

is duplicated.

Setting the argument keep to be false tells it to mark all duplicated rows.

We'll store these in the variable ind for index.

Now let's print out the number of duplicated rows.

Let's also print the first few rows of all the columns.

We have 1213 duplicated rows in our dataset.

We can see the first five here,

but there's far too much data to go through everything by hand.

Lets start with the simplest case, getting rid of any pure duplicates.

Pandas has a drop duplicates method for

dataframes that will drop all rows where all entries are identical.

In our case,

we know each entry should have a unique value in the sequence column.

So it's safe to drop all identical duplicates.

Now let's take another look at how many repeated

entries we have by copying the code we used earlier.

Now we're down to 814 duplicates.

Next, we'll look for examples where an entry is split across multiple rows.

One example of how this could happen is if data is collected on multiple days and

the data entry process starts a new entry every time new data is entered.

We want to use the other duplicated rows to try and fill in all the missing values.

If this creates a perfect match across all the rows,

we can then drop all but one of them.

To do this we use the itertools library for

its useful product function, which we'll need in a moment.

Now wevneed a unique list of all IDs that are duplicated.

We can get this by selecting only the duplicated rows in the SEQN column.

And then using the unique method to show each ID only once.

We want to iterate over all duplicate IDs, so we use a for loop.

We need to get the location of all rows that match our current ID number.

And here is where that product function comes in.

We pass in our duplicated rows and specify the repeat parameter to be 2.

It will return all possible pairs of these rows.

This is almost identical to two nested for

loops, where each loop's over every possible row.

Except that it skips the cases where both rows are the same.

Now for each of pair rows,

we want to fill in the missing values of one row with the values of the other.

We can do this with the dataframe's fillna method.

It will fill in all not a number entries from the first row with the data from

the row passed as an argument.

Now let's drop all our duplicates again, and see how many are left.

We're down to 416 duplicated rows.

All remaining duplicates should belong to our third case,

they have conflicting information.

We can see an example of this in the duplications for ID 60,

the values for column RIDEXMON don't match.

We have no way of knowing which value is correct, so

it's safest to drop all rows with conflicting information.

So first, we need to get all duplicated rows.

Then we remove them from our dataset by selecting only

those that aren't duplicated.

And after printing our duplicated rows,

we can see that we've gotten rid of the last of them.

It's also possible to have conflicting columns.

Here, we want to make sure that the only duplicated column between our

demographics and body measures dataframes is the sequence column.

If we cast a list of columns in demo to a set,

we can find the intersection of it and the bmx dataframe.

An intersection tell us which values are present in two sets.

It looks like our sequence column is in both data frames, but so

is the column for age.

The code books say that age should only be recorded in the demographics file.

So let's drop it from the body measures data frame.

Great, now the only column in both dataframes is the sequence column.

Now, we can merge these two datasets into a single dataframe using the merge

function.

And as before, we wanna save any changes we've made to our dataset.

Before we move on, there are some more practice exercises for you.

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

You'll be fixing any duplicate rows or columns in the occupation file.

And then you'll be merging all three files into a single dataset.

Don't forget to save your files again when you finish.

Once you've completed these exercises, you'll be ready for the next lesson.

In our next lesson, we'll cover a couple more data issues, infeasible data and

extreme data.