How to Remove Duplicated Data in Pandas: A Step-by-Step Guide

We‘ve all started at a data point in a spreadsheet and thought, “Why does this look so familiar?” There’s, of course, a reason: It’s repeated data. Duplicate data takes up unnecessary storage space and slows down calculations. Removing duplicate data with Pandas can help you avoid this scenario in the future.

Pandas is a Python library designed to help developers manage structured data. Plus, Pandas supports data cleansing, which includes removing duplicated rows and columns.

This post will focus on the .duplicated and .drop_duplicates methods of the pandas library. Topics we’ll cover include:

How to Count the Number of Duplicated Rows in Pandas DataFrame

Let’s create a simple example of duplicated rows in a Pandas DataFrame.

 # create a example dataframe with duplicate rows
data = {
    'Car': ['Forester', 'Outback', 'Outback', 'Outback', 'Forester'],
    'Color': ['Brown', 'Silver', 'Silver', 'Silver', 'Black'],
    'Interior': ['Black', 'Black', 'Black', 'Brown', 'Green']

The example allows us to see that the rows with index 1 and 2 have the same values for all three columns. Upon applying the .drop_duplicates( ) function, the first row is retained. The remaining duplicate rows are removed.

Before we start removing duplicate rows, it‘s wise to get a better idea of where the duplicates are in our data set. Then, we can decide how best to deal with them. Let’s get started.

[Video: Drop Duplicates from pandas DataFrame | How to Remove Repeated Row | All & Multiple Selected Columns]

Below is a product inventory of kitchen cutlery and utensils. This will be our base DataFrame over the following tutorials:

With the .duplicated method, we can identify which rows are duplicates:

Here, we are calling .duplicated() on our DataFrame kitch_prod_df. Once executed, .duplicated() returns booleans organized into a pandas Series (also known as a column). We can inspect this output by printing to the terminal:

To interpret these results, think of .duplicated as asking, “Is this row duplicated?” The method looks through each row to determine if all the data present completely matches the values of any other row in the DataFrame. If it finds an exact match to another row, it returns True. In other words, it answers the question “Is this row duplicated?” with “Yes.”

This means that any rows that are marked False are unique. To return to the printout, this means we have four unique rows and two duplicate rows (“fork” and “spoon” at index positions 3 and 4).

The default behavior of .duplicated is to mark the second instance of a row as the duplicate (and any other instances that follow it). It considers the first row to contain the matching data as unique. However, you can modify this behavior by changing the arguments.

Let’s review the arguments available for the .duplicated method and how you can adjust them to fit your needs.

 kitch_prod_fd.duplicated(keep = False)

 kitch_prod_df.duplicated(keep = 'first')

If you want to keep the last instance of a duplicate row, you can invert the default behavior of .duplicated by changing the argument to “last”:

Instead of marking the fourth and fifth rows, we have now identified the initial occurrences of the duplicate data as True.

In some scenarios, it makes sense to limit our search for duplicates to specific columns instead of the entire DataFrame. To achieve this, we use the subset argument.

2. The Subset Argument

Best for: Narrowing the scope of the rows that .duplicated inspects to suspect columns to produce more focused results.

Scanning the entire DataFrame isn‘t always practical, especially if it’s dozens of columns or hundreds of rows. If we identify columns where duplicates are likely to occur, we can pass the column names to .duplicated with the subset argument.

Here’s the original DataFrame for reference:

In this code, we are checking the DataFrame for duplicates in the “department” column:

 kitch_prod_df.duplicated(subset="department")

Here, we set the subset argument equal to “department” so that .duplicated() only examines the column matching that label. The output is below.

If we went off these results, we could conclude that our entire DataFrame is full of duplicate records. With context, we realize that all the products are under the “kitchen” department label. The subset argument allows us to expand our scope beyond a single column by using a Python list:

 kitch_prod_fd.duplicated(subset = ['sku', 'department'])

This time, we are searching for duplicates in both the “sku” and “department” columns. Only if the data is the exact same in both columns will a row get marked as a duplicate. Even though every record shares the same department name, only the fourth and fifth rows are flagged as duplicates:

The keep and subset arguments are also available to us in the .drop_duplicates method, which we’ll review in the following sections.

 kitch_prod_fd.duplicated(inplace = True)

Here, .drop_duplicates has identified duplicates in the values of the “sku” column. The function dropped the rows belonging to those repeat values — the fourth and fifth rows.

This video from DataDaft provides a live demonstration of the .drop_duplicates method.

[Video: How to Remove Duplicate Rows From a Data Frame in Pandas (Python)]

Now, let’s look at how we can modify this approach to remove duplicate columns.

 no_dup_columns = kitch_prod_df.T.drop_duplicates().T