How to compare multi-value duplicates in Dataframe

user_15870185 0
Viewed 74

Inputs

I have a Dataframe with several columns. And list

proof_path = 

   #1  X  Y  #2  Z  #3  W  #4
0  p1  a  b  p2  c  p2  a  p3
1  p1  a  b  p2  c  p3  a  p1
2  p1  a  b  p2  d  p3  e  p4

rule = [('#1', 'X', 'Y'), ('#2', 'X', 'Z'), ('#3', 'W', 'Z'), ('#4', 'W', 'Y')]

In the above Dataframe, I want to examine whether each row is duplicated between (#1, X, Y), (#2, X, Z), (#3, W, Z), and (#4, W, Y).

For example In the row corresponding to index 0, (#2, X, Z) and (#3, W, Z) overlap (P2, a, c).

In addition, (#1, X, Y) and (#4, W, Y) in row corresponding to index 1 overlap (P1, a, b). I'm going to drop rows that overlap between these multi-values from that dataframe.

My desired output is

output

proof_path = 

   #1  X  Y  #2  Z  #3  W  #4
2  p1  a  b  p2  d  p3  e  p4

And i tried as follows.

for depth in range(len(rule)-1):
    for i in range(1, len(rule)-depth):
        current_rComp = proof_path[[rule[depth][0], rule[depth][1], rule[depth][2]]]
        current_rComp.columns = ['pred', 'subj', 'obj']
        next_rComp = proof_path[[rule[i+depth][0], rule[i+depth][1], rule[i+depth][2]]]
        next_rComp.columns = ['pred', 'subj', 'obj']
        proof_path = proof_path[current_rComp.ne(next_rComp).any(axis=1)]

Although these methods were able to achieve desired results, they are inefficient by generating new Dataframes for each iteration. Is there a simple way to accomplish these tasks?

pandas python performance
2 Answers

Create a placeholder mask initially containing False values, essentially this mask will contain True if there any duplicates found in the corresponding row.

Generate length two combinations from rule list, then for each combination compare the slices of dataframe in order to create a boolean mask, now reduce this mask with all along axis=1 and take the logical or of the reduced mask with the placeholder mask

from itertools import combinations

mask = np.full(len(df), False)
for x, y in combinations(rule, 2):
    mask |= (df[[*x]].values == df[[*y]].values).all(1)

Alternatively we can also wrap the above approach inside a list comprehension

mask = np.any([(df[[*x]].values == df[[*y]].values).all(1) 
               for x, y in combinations(rule, 2)], axis=0)

>>> df[~mask]

   #1  X  Y  #2  Z  #3  W  #4
2  p1  a  b  p2  d  p3  e  p4
user_128331660

You can drop the rows that have duplicates on a subset of columns like -

df = df.drop_duplicates(subset=['#1', 'X', 'Y'],keep=False)
df = df.drop_duplicates(subset=['#2', 'X', 'Z'],keep=False)
df = df.drop_duplicates(subset=['#3', 'W', 'Z'],keep=False)

Refer to the documentation for additional parameters.

user_31726840
Related