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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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
Method 2
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
Method 3
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.
Method 4
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()]
Method 5
df[df['ID'].duplicated() == True]
This worked for me
Method 6
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")
Method 7
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)
]
Method 8
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
Method 9
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)]
Method 10
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
Method 11
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')
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0