Find duplicates by ID with different value in another column pandas dataframe

Paulo Cortez 0
Viewed 24

I have the following dataframe:

ID  Location    Valid
1   Avenue      Yes
1   Block       No
2   Avenue      Yes
3   Street      No
3   Street      No
4   Av.         Yes
4   Street      No
4   Av.         Yes

I need to find a way to filter this dataframe returning all duplicated rows (by ID) that have a different value in the "Location" column . If the ID has different value, return all columns with that ID. If it appears 3 times or more in the data, if a single location is different, return all rows, resulting in this data:

ID  Location    Valid
1   Avenue      Yes
1   Block       No
4   Av.         Yes
4   Street      No
4   Av.         Yes

What is the best way to perform this operation? Thanks!

find duplicates filter pandas python
1 Answers

Use GroupBy.transform with DataFrameGroupBy.nunique and comapre if greater like 1:

df = df[df.groupby('ID')['Location'].transform('nunique').gt(1)]
print (df)
   ID Location Valid
0   1   Avenue   Yes
1   1    Block    No
5   4      Av.   Yes
6   4   Street    No
7   4      Av.   Yes
jezrael0
Related