Removing duplicate data in Pandas
Tags: python, pandas
Published: 2020-11-30 00:21:49

---
title: Removing duplicate data in Pandas
published: true
date: 2020-11-30 00:16:48 UTC
tags: Python,pandas
canonical_url: https://www.wrighters.io/2020/11/30/removing-duplicate-data-in-pandas/
---

It can be very common when dealing with time series data to end up with duplicate data. This can happen for a variety of reasons, and I’ve encountered it more than one time when and tried different approaches to eliminate the duplicate values. There’s a [gem of a solution on Stack Overflow](https://stackoverflow.com/questions/13035764/remove-rows-with-duplicate-indices-pandas-dataframe-and-timeseries#34297689) and I thought it would be helpful to walk through the possible solutions to this issue.

To keep things simple, I’ll just work with a `Series` of floating point data. This could be anything, but we could pretend it’s something that’s manually maintained, like an earnings estimate for a stock, or a temperature reading, or a sales for a store on a given date.

```
>>> import pandas as pd
>>> import numpy as np
>>>
>>> items = pd.Series(np.random.random_sample(10) * 100, pd.date_range('2020-01-01', periods=10))
>>>
>>> items
2020-01-01 65.685092
2020-01-02 15.376536
2020-01-03 89.002061
2020-01-04 38.614841
2020-01-05 39.274465
2020-01-06 6.886290
2020-01-07 96.453986
2020-01-08 56.488125
2020-01-09 61.667168
2020-01-10 18.023665
Freq: D, dtype: float64
```

At this point, we have 10 periods of data, and the index (a `DatetimeIndex` with 10 days) all have unique values. But let’s say in our data, corrected data appears in the same source file. I’ll do something a bit contrived here and concatenate two `Series` that have some of the same dates in them, but in real life you can imagine a number of ways that data will show up in your sources with duplicated data for the same time stamp.

```
>>> corrected = pd.Series(np.random.random_sample(3) * 75, pd.date_range('2020-01-04', periods=3))
>>> combined = pd.concat([items, corrected])
```

Now, how do we get rid of this duplicated data? Let’s say that we want to only keep the most recent data in our file, assuming that it was a correction or updated value that we prefer to use. Instead of going right to the accepted solution on Stack Overflow, I’m going to work through the pandas documentation to see what the possible solutions are, and hopefully end up in the same place!

First, let’s see if we can answer the question of whether our data has duplicate items in the index. In the pandas docs, we see a few promising methods, including a [`duplicated`](https://pandas.pydata.org/docs/reference/api/pandas.Index.duplicated.html) method, and also a `has_duplicates` property. Let’s see if those report what we expect.

```
>>> combined.index.has_duplicates
True
```

Now the methods available to look at are `duplicated` and `drop_duplicates`. For `duplicated`, the method will return an array of boolean values, where `True` indicates the duplicate. You can use the `keep` argument to keep either the first (default) or last occurrence of the value in your index. In `drop_duplicates`, you get an `Index` returned with the duplicates already removed, and you can pass in the same `keep` argument with the same meaning.

```
>>> combined.index.duplicated(keep='last')
array([False, False, False, True, True, True, False, False, False,
       False, False, False, False])
>>> combined.index.drop_duplicates(keep='last')
DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-07',
               '2020-01-08', '2020-01-09', '2020-01-10', '2020-01-04',
               '2020-01-05', '2020-01-06'],
              dtype='datetime64[ns]', freq=None)
```

Ok, so what do we do now with these two options? The first boolean array can be used to just pick the values that we want to keep, but the `True` values are the ones we want to drop. That is pretty easy, just invert it with a `~`. This can be used to select the values you want out of the array, and gets us to a good solution. We need to sort the index since it is not in chronological order.

```
>>> ~combined.index.duplicated(keep='last')
array([ True, True, True, False, False, False, True, True, True,
        True, True, True, True])
>>> combined[~combined.index.duplicated(keep='last')].sort_index()
2020-01-01 65.685092
2020-01-02 15.376536
2020-01-03 89.002061
2020-01-04 28.990089
2020-01-05 22.371910
2020-01-06 21.548301
2020-01-07 96.453986
2020-01-08 56.488125
2020-01-09 61.667168
2020-01-10 18.023665
dtype: float64
```

Now if we want to use the second method, `drop_duplicates`, we need to find a way to use that to grab the values out of our `Series` that we want to keep. This is a bit more complicated. First, we can use the `reset_index` method which is a handy way to take the index (in our case a `DatetimeIndex`) and turn it into a column on a `DataFrame` momentarily with a new regular, non-repeating index. Now you can see that our `Series` looks a little different, but the new column can now be used to remove duplicates.

```
>>> combined.reset_index()
        index 0
0 2020-01-01 65.685092
1 2020-01-02 15.376536
2 2020-01-03 89.002061
3 2020-01-04 38.614841
4 2020-01-05 39.274465
5 2020-01-06 6.886290
6 2020-01-07 96.453986
7 2020-01-08 56.488125
8 2020-01-09 61.667168
9 2020-01-10 18.023665
10 2020-01-04 28.990089
11 2020-01-05 22.371910
12 2020-01-06 21.548301
```

Now, we can use `drop_duplicates`, but we’ll use the `DataFrame` version of the method which has a `subset` argument that can be used to only consider a certain column (our new ‘index’ column) for duplicates to drop. Now since this is now a `DataFrame` and not a `Series`, we will reset the index to our index column using `set_index` and return the column `0`. This gives us the same result as the earlier method, but in a much more roundabout way. I also find it a lot more confusing.

```
>>> combined.reset_index().drop_duplicates(subset='index', keep='last').set_index('index')[0].sort_index()
index
2020-01-01 65.685092
2020-01-02 15.376536
2020-01-03 89.002061
2020-01-04 28.990089
2020-01-05 22.371910
2020-01-06 21.548301
2020-01-07 96.453986
2020-01-08 56.488125
2020-01-09 61.667168
2020-01-10 18.023665
Name: 0, dtype: float64
```

One other way to do this is to use `groupby` and a grouping function (in this case the `last`) to select the values we want. This method provides us with sorted output and also looks simple.

```
>>> combined.groupby(combined.index).last()
2020-01-01 65.685092
2020-01-02 15.376536
2020-01-03 89.002061
2020-01-04 28.990089
2020-01-05 22.371910
2020-01-06 21.548301
2020-01-07 96.453986
2020-01-08 56.488125
2020-01-09 61.667168
2020-01-10 18.023665
dtype: float64
```

What’s the best way to do this? Like the question on Stack Overflow, I prefer the first method for readability, but the last is also pretty simple. One good argument for choosing the first method is speed.

```
%timeit combined[~combined.index.duplicated(keep='last')].sort_index()
282 µs ± 15.2 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

%timeit combined.reset_index().drop_duplicates(subset='index', keep='last').set_index('index')[0].sort_index()
1.56 ms ± 28.8 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

%timeit combined.groupby(combined.index).last()
578 µs ± 5.21 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
```

Well, after digging through all that, I hope you understand a bit more about how to remove duplicate items from a `Series` or `DataFrame` and why some methods might be better to choose than others.