0
$\begingroup$

I have a table and I'm trying to remove all the duplicate and keep the
the rows that has the latest datestamp.

Here is the table:

email address       orgin_date   new_opt_in_date   datestamp
123@ax.tu            1/1/1900     1/1/1900          3/15/2016
123@ax.tu            1/1/1900     1/1/1900          3/15/2016
iron_man@metrix.com  2/15/2015    3/5/2015          6/6/2017
iron_man@metrix.com  2/15/2015    3/5/2015          7/6/2018
sleep@dort.st        2/15/2015    3/5/201           7/6/2018
sleep@dort.st        2/15/2015    3/5/201           5/6/2018

I'm trying to keep only the data that has the recent datestamp and the output will like this:

 email address       orgin_date   new_opt_in_date   datestamp
 123@ax.tu            1/1/1900     1/1/1900          3/15/2016
 iron_man@metrix.com  2/15/2015    3/5/2015          6/6/2017
 sleep@dort.st        2/15/2015    3/5/201           7/6/2018

I use this formula:

df.drop_duplicates(keep = False) 

or this one:

df1 = df.drop_duplicates(subset   
['emailaddress', 'orgin_date', 'new_opt_in_date','datestamp'],keep='first')
print(df1)

but nothing works

$\endgroup$
1
$\begingroup$

You can see from the documentation of the method that you can change the keep argument to be "last".

In your case, as you only want to consider the values in one of your columns (datestamp), you must specify this in the subset argument. You had tried passing all column names, which is actually the default behaviour. Now we can use this (along with the correct value for the keep argument) to get this:

For example, a dataframe with duplicates:

In [1]: import pandas as pd

In [2]: df = pd.DataFrame({'datestamp': ['A0', 'A0', 'A2', 'A2'],
                           'B': ['B0', 'B1', 'B2', 'B3'],
                           'C': ['B0', 'B1', 'B2', 'B3'],
                           'D': ['D0', 'D1', 'D2', 'D3']}, 
                           index=[0, 1, 2, 3]).T

In [3]: df                                                                      
Out[3]: 
  datestamp   B   C   D
0        A0  B0  B0  D0
1        A0  B1  B1  D1
2        A2  B2  B2  D2
3        A2  B3  B3  D3

Now we drop duplicates, passing the correct arguments:

In [4]: df.drop_duplicates(subset="datestamp", keep="last")                     
Out[4]: 
  datestamp   B   C   D
1        A0  B1  B1  D1
3        A2  B3  B3  D3

By comparing the values across rows 0-to-1 as well as 2-to-3, you can see that only the last values within the datestamp column were kept.

$\endgroup$
3
  • $\begingroup$ I did try that, but unfortunately it didn't work. I thought using that will only keep the last value, but nothing change or none of the rows were dropped. $\endgroup$ – Learning May 29 '19 at 0:53
  • $\begingroup$ @Learning - please see the edited post, which drop duplicate rows only keeping the most recent row, using the datestamp column. $\endgroup$ – n1k31t4 May 29 '19 at 1:30
  • $\begingroup$ I just used this formula but some reason it is not deleting anything. $\endgroup$ – Learning May 29 '19 at 12:52
0
$\begingroup$

I think its better to convert datestamp column to datetime format. Using this way I got the required result.

After converting to datetime format, you data will look like this-

>>> df

         email address  orgin_date   new_opt_in_date  datestamp
0            123@ax.tu   1/1/1900        1/1/1900    2016-03-15
1            123@ax.tu   1/1/1900        1/1/1900    2016-03-15
2  iron_man@metrix.com  2/15/2015        3/5/2015    2017-06-06
3  iron_man@metrix.com  2/15/2015        3/5/2015    2018-07-06
4        sleep@dort.st  2/15/2015         3/5/201    2018-07-06
5        sleep@dort.st  2/15/2015         3/5/201    2018-05-06  

Then sort by both email address and datestamp (in order) and drop duplicates-

>>> df.sort_values(['email address', 'datestamp']).drop_duplicates(subset = 'email address', keep = 'last')

         email address orgin_date   new_opt_in_date  datestamp
1            123@ax.tu   1/1/1900        1/1/1900    2016-03-15
3  iron_man@metrix.com  2/15/2015        3/5/2015    2018-07-06
4        sleep@dort.st  2/15/2015         3/5/201    2018-07-06 
```
$\endgroup$
4
  • $\begingroup$ I just tried using this formula but I'm getting an error with email address. $\endgroup$ – Learning May 29 '19 at 12:51
  • $\begingroup$ What is the error? $\endgroup$ – Ankit Seth May 29 '19 at 12:57
  • $\begingroup$ This is what I got: ----> 1 remixing.sort_values(['Email_Addresss', 'Datestamp']).drop_duplicates(subset = 'Email_Address', keep = 'last') 1380 values = self.axes[axis].get_level_values(key)._values 1381 else: -> 1382 raise KeyError(key) 1384 # Check for duplicates KeyError: 'Email_Addresss' $\endgroup$ – Learning May 29 '19 at 13:09
  • $\begingroup$ The column name is "email address" not email addresss. $\endgroup$ – Ankit Seth May 29 '19 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.