How do I get a list of all the duplicate items using pandas in python?
PythonPandasDuplicatesPython Problem Overview
I have a list of items that likely has some export issues. I would like to get a list of the duplicate items so I can manually compare them. When I try to use pandas duplicated method, it only returns the first duplicate. Is there a a way to get all of the duplicates and not just the first one?
A small subsection of my dataset looks like this:
ID,ENROLLMENT_DATE,TRAINER_MANAGING,TRAINER_OPERATOR,FIRST_VISIT_DATE
1536D,12-Feb-12,"06DA1B3-Lebanon NH",,15-Feb-12
F15D,18-May-12,"06405B2-Lebanon NH",,25-Jul-12
8096,8-Aug-12,"0643D38-Hanover NH","0643D38-Hanover NH",25-Jun-12
A036,1-Apr-12,"06CB8CF-Hanover NH","06CB8CF-Hanover NH",9-Aug-12
8944,19-Feb-12,"06D26AD-Hanover NH",,4-Feb-12
1004E,8-Jun-12,"06388B2-Lebanon NH",,24-Dec-11
11795,3-Jul-12,"0649597-White River VT","0649597-White River VT",30-Mar-12
30D7,11-Nov-12,"06D95A3-Hanover NH","06D95A3-Hanover NH",30-Nov-11
3AE2,21-Feb-12,"06405B2-Lebanon NH",,26-Oct-12
B0FE,17-Feb-12,"06D1B9D-Hartland VT",,16-Feb-12
127A1,11-Dec-11,"064456E-Hanover NH","064456E-Hanover NH",11-Nov-12
161FF,20-Feb-12,"0643D38-Hanover NH","0643D38-Hanover NH",3-Jul-12
A036,30-Nov-11,"063B208-Randolph VT","063B208-Randolph VT",
475B,25-Sep-12,"06D26AD-Hanover NH",,5-Nov-12
151A3,7-Mar-12,"06388B2-Lebanon NH",,16-Nov-12
CA62,3-Jan-12,,,
D31B,18-Dec-11,"06405B2-Lebanon NH",,9-Jan-12
20F5,8-Jul-12,"0669C50-Randolph VT",,3-Feb-12
8096,19-Dec-11,"0649597-White River VT","0649597-White River VT",9-Apr-12
14E48,1-Aug-12,"06D3206-Hanover NH",,
177F8,20-Aug-12,"063B208-Randolph VT","063B208-Randolph VT",5-May-12
553E,11-Oct-12,"06D95A3-Hanover NH","06D95A3-Hanover NH",8-Mar-12
12D5F,18-Jul-12,"0649597-White River VT","0649597-White River VT",2-Nov-12
C6DC,13-Apr-12,"06388B2-Lebanon NH",,
11795,27-Feb-12,"0643D38-Hanover NH","0643D38-Hanover NH",19-Jun-12
17B43,11-Aug-12,,,22-Oct-12
A036,11-Aug-12,"06D3206-Hanover NH",,19-Jun-12
My code looks like this currently:
df_bigdata_duplicates = df_bigdata[df_bigdata.duplicated(cols='ID')]
There area a couple duplicate items. But, when I use the above code, I only get the first item. In the API reference, I see how I can get the last item, but I would like to have all of them so I can visually inspect them to see why I am getting the discrepancy. So, in this example I would like to get all three A036 entries and both 11795 entries and any other duplicated entries, instead of the just first one. Any help is most appreciated.
Python Solutions
Solution 1 - Python
Method #1: print all rows where the ID is one of the IDs in duplicated:
>>> import pandas as pd
>>> df = pd.read_csv("dup.csv")
>>> ids = df["ID"]
>>> df[ids.isin(ids[ids.duplicated()])].sort("ID")
ID ENROLLMENT_DATE TRAINER_MANAGING TRAINER_OPERATOR FIRST_VISIT_DATE
24 11795 27-Feb-12 0643D38-Hanover NH 0643D38-Hanover NH 19-Jun-12
6 11795 3-Jul-12 0649597-White River VT 0649597-White River VT 30-Mar-12
18 8096 19-Dec-11 0649597-White River VT 0649597-White River VT 9-Apr-12
2 8096 8-Aug-12 0643D38-Hanover NH 0643D38-Hanover NH 25-Jun-12
12 A036 30-Nov-11 063B208-Randolph VT 063B208-Randolph VT NaN
3 A036 1-Apr-12 06CB8CF-Hanover NH 06CB8CF-Hanover NH 9-Aug-12
26 A036 11-Aug-12 06D3206-Hanover NH NaN 19-Jun-12
but I couldn't think of a nice way to prevent repeating ids so many times. I prefer method #2: groupby on the ID.
>>> pd.concat(g for _, g in df.groupby("ID") if len(g) > 1)
ID ENROLLMENT_DATE TRAINER_MANAGING TRAINER_OPERATOR FIRST_VISIT_DATE
6 11795 3-Jul-12 0649597-White River VT 0649597-White River VT 30-Mar-12
24 11795 27-Feb-12 0643D38-Hanover NH 0643D38-Hanover NH 19-Jun-12
2 8096 8-Aug-12 0643D38-Hanover NH 0643D38-Hanover NH 25-Jun-12
18 8096 19-Dec-11 0649597-White River VT 0649597-White River VT 9-Apr-12
3 A036 1-Apr-12 06CB8CF-Hanover NH 06CB8CF-Hanover NH 9-Aug-12
12 A036 30-Nov-11 063B208-Randolph VT 063B208-Randolph VT NaN
26 A036 11-Aug-12 06D3206-Hanover NH NaN 19-Jun-12
Solution 2 - Python
With Pandas version 0.17, you can set 'keep = False' in the duplicated function to get all the duplicate items.
In [1]: import pandas as pd
In [2]: df = pd.DataFrame(['a','b','c','d','a','b'])
In [3]: df
Out[3]:
0
0 a
1 b
2 c
3 d
4 a
5 b
In [4]: df[df.duplicated(keep=False)]
Out[4]:
0
0 a
1 b
4 a
5 b
Solution 3 - Python
df[df.duplicated(['ID'], keep=False)]
it'll return all duplicated rows back to you.
According to documentation:
> keep : {‘first’, ‘last’, False}, default ‘first’ > > - first : Mark duplicates as True except for the first occurrence. > - last : Mark duplicates as True except for the last occurrence. > - False : Mark all duplicates as True.
Solution 4 - Python
As I am unable to comment, hence posting as a separate answer
To find duplicates on the basis of more than one column, mention every column name as below, and it will return you all the duplicated rows set:
df[df[['product_uid', 'product_title', 'user']].duplicated() == True]
Alternatively,
df[df[['product_uid', 'product_title', 'user']].duplicated()]
Solution 5 - Python
df[df['ID'].duplicated() == True]
This worked for me
Solution 6 - Python
sort("ID") does not seem to be working now, seems deprecated as per sort doc, so use sort_values("ID") instead to sort after duplicate filter, as following:
df[df.ID.duplicated(keep=False)].sort_values("ID")
Solution 7 - Python
Using an element-wise logical or and setting the take_last argument of the pandas duplicated method to both True and False you can obtain a set from your dataframe that includes all of the duplicates.
df_bigdata_duplicates =
df_bigdata[df_bigdata.duplicated(cols='ID', take_last=False) |
df_bigdata.duplicated(cols='ID', take_last=True)
]
Solution 8 - Python
This may not be a solution to the question, but to illustrate examples:
import pandas as pd
df = pd.DataFrame({
'A': [1,1,3,4],
'B': [2,2,5,6],
'C': [3,4,7,6],
})
print(df)
df.duplicated(keep=False)
df.duplicated(['A','B'], keep=False)
The outputs:
A B C
0 1 2 3
1 1 2 4
2 3 5 7
3 4 6 6
0 False
1 False
2 False
3 False
dtype: bool
0 True
1 True
2 False
3 False
dtype: bool
Solution 9 - Python
You could use:
df[df.duplicated(['ID'])==True].sort_values('ID')
duplicated rows and their index loc # for all column values
def dup_rows_index(df):
dup = df[df.duplicated()]
print('Duplicated index loc:',dup[dup == True ].index.tolist())
return dup
Solution 10 - Python
For my database duplicated(keep=False) did not work until the column was sorted.
data.sort_values(by=['Order ID'], inplace=True)
df = data[data['Order ID'].duplicated(keep=False)]
Solution 11 - Python
Inspired by the solutions above, you can further sort values so that you can look at the records that are duplicated sorted:
df[df.duplicated(['ID'], keep=False)].sort_values(by='ID')