Pandas Dataframe Examples: Duplicated Data

Last updated:
Pandas Dataframe Examples: Duplicated Data
Source
Table of Contents

All code available on this jupyter notebook

Show duplicated rows

I.e. show rows that are duplicates of existing rows.

import pandas as pd

df = pd.DataFrame({
    'title': ['foo','bar','baz','baz','foo','bar'],
    'contents':[
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.',
        'Consectetur adipiscing elit.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.'
    ],
    'year':[2010,2011,2000,2005,2010,2011]
})

df[df.duplicated()]

source-dataframe Rows 1 and 5 are duplicates
         
alt-text-image-2 Show only duplicated rows

Show duplicated rows including original rows

Use df[df.duplicated(keep=False)].

This shows both duplicated and original rows.

import pandas as pd

df = pd.DataFrame({
    'title': ['foo','bar','baz','baz','foo','bar'],
    'contents':[
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.',
        'Consectetur adipiscing elit.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.'
    ],
    'year':[2010,2011,2000,2005,2010,2011]
})

df[df.duplicated(keep=False)]

source-dataframe Source dataframe
         
alt-text-image-2 Using keep=False means that
both duplicates and the original
rows are displayed

Get number of duplicated rows in dataframe

In other words, the number of rows that are duplicates of other existing rows.

Use len(df[df.duplicated()])

import pandas as pd

df = pd.DataFrame({
    'title': ['foo','bar','baz','baz','foo','bar'],
    'contents':[
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.',
        'Consectetur adipiscing elit.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.'
    ],
    'year':[2010,2011,2000,2005,2010,2011]
})

len(df[df.duplicated()])
# >>> 2

Drop duplicates, keep original row

Just use df.drop_duplicates().

For all duplicated groups, the first row will be kept and the rest will be dropped.

This depends upon how the dataframe is sorted.

import pandas as pd

df = pd.DataFrame({
    'title': ['bar','bar','baz','baz','foo','foo'],
    'contents':[
        'Sed mollis tempor accumsan.',
        'Sed mollis tempor accumsan.',
        'Nullam et feugiat turpis, non condimentum dolor.',
        'Aenean eu aliquam nunc.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.'
    ],
    'year':[2010,2010,2005,2005,2011,2011]
})

df.drop_duplicates()

source-dataframe By default, rows are considered
duplicates if values in all columns
(except the index) are identical.
         
drop-duplicates AFTER: dropped duplicated rows

Drop duplicates based on some columns only

import pandas as pd

df = pd.DataFrame({
    'title': ['bar','bar','baz','baz','foo','foo'],
    'contents':[
        'Sed mollis tempor accumsan.',
        'Sed mollis tempor accumsan.',
        'Nullam et feugiat turpis, non condimentum dolor.',
        'Aenean eu aliquam nunc.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.'
    ],
    'year':[2010,2010,2005,2005,2011,2011]
})

# only consider title and year when defining duplicates
df.drop_duplicates(subset=['title','year'])

source-dataframe If we only take into account
columns title and year, then
these rows are duplicates
         
with-different-subsets By setting only title and year to the
subset parameter, we have a more
aggressive
duplicate filter, which ends
dropping more rows

Drop all rows that have duplicates or are duplicates

I.e. if there are two identical rows, this will drop both from the dataframe.

Use df.drop_duplicates(keep=False)

import pandas as pd

df = pd.DataFrame({
    'title': ['bar','bar','baz','baz','foo','foo'],
    'contents':[
        'Sed mollis tempor accumsan.',
        'Sed mollis tempor accumsan.',
        'Nullam et feugiat turpis, non condimentum dolor.',
        'Aenean eu aliquam nunc.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.'
    ],
    'year':[2010,2010,2005,2005,2011,2011]
})

df.drop_duplicates(keep=False)

source-dataframe These rows either have duplicates
or ARE duplicates themselves
         
with-keep-false Setting keep=False means that you want to
keep neither the original rows
nor the duplicates.

Mark/flag duplicate rows with a flag column

Often, it's better to mark bad rows in a dataframe instead of deleting them.

This helps keep track of what modifications were done from the raw data until the processed dataframe.

Use df.assign() to create a new is_duplicate column:

import pandas as pd

df = pd.DataFrame({
    'title': ['bar','bar','baz','baz','foo','foo'],
    'contents':[
        'Sed mollis tempor accumsan.',
        'Sed mollis tempor accumsan.',
        'Nullam et feugiat turpis, non condimentum dolor.',
        'Aenean eu aliquam nunc.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.'
    ],
    'year':[2010,2010,2005,2005,2011,2011]
})

# assign doesnt happen in place
df=df.assign(
    is_duplicate= lambda d: d.duplicated()
).sort_values(['title','contents','year']).reset_index(drop=True)

df

source-dataframe Source dataframe
         
with-helper-column Using a helper column aids in keeping
track of data lineage.
Note that all columns were used
to define duplicates

Arbitrary keep criterion

There's no out-of-the-box way to do this so one answer is to sort the dataframe so that the correct values for each duplicate are at the end and then use drop_duplicates(keep='last')

Example: drop duplicated rows, keeping the values that are more recent according to column year:

import pandas as pd

df = pd.DataFrame({
    'title': ['bar','bar','baz','baz','foo','foo'],
    'contents':[
        'Sed mollis tempor accumsan.',
        'Sed mollis tempor accumsan.',
        'Nullam et feugiat turpis, non condimentum dolor.',
        'Aenean eu aliquam nunc.',
        'Lorem ipsum dolor sit amet.',
        'Lorem ipsum dolor sit amet.'
    ],
    'year':[2009,2019,2005,2005,2015,1995]
})

df.sort_values(
  ['title','contents','year']
).drop_duplicates(subset=['title','contents'],keep='last')

source-dataframe Source dataframe: these have the
same title and contents, but
one is newer
         
dataframe-after Only the most recent information
for each duplicated group was kept.

Dialogue & Discussion