Pandas: add indicator for duplicate on columns

My original answer required N**2 iterations for N rows. The answer by sammywemmy loops over permutations(..., 2), which is essentially a loop over N*(N-1) combinations. The answer by warped is more efficient because it starts with a quicker matching on the A and B columns, but there is still a slow search for the conditions on the C and D columns. The number of iterations is therefore N*M where M is the average number of rows sharing the same A and B values.

If you're willing to change the requirement of "C equal +/-0.05" to "C is equal when rounded to 1 decimal", it gets better, with N*K iterations where K is the average number of rows having the same A, B, and C values. Here is one implementation; you can also adapt warped's approach.

df = pd.DataFrame(
    {'A': {0: 1, 1: 1, 2: 1, 3: 3, 4: 3, 5: 1, 6: 1},
     'B': {0: 2, 1: 2, 2: 2, 3: 4, 4: 4, 5: 2, 6: 9},
     'C': {0: 1.0, 1: 1.01, 2: 1.0, 3: 0.0, 4: 0.0, 5: 1.0, 6: 1.0},
     'D': {0: 'a', 1: 'a', 2: 'b', 3: 'b', 4: 'c', 5: 'c', 6: 'c'}})

# alternative to "equal +/- 0.05"
df['C10'] = np.around(df['C']*10).astype('int')

# convert int64 tuples to int tuples
ituple = lambda tup: tuple(int(x) for x in tup)

# records : [(1, 2, 10), (1, 2, 100, (1, 2, 10), (3, 4,0), ...]
records = [ituple(rec) for rec in df[['A', 'B', 'C10']].to_records(index=False)]

# dupd: dict with records as key, list of indices as values.
# e.g. {(1, 2, 10): [0, 1, 2, 5], ...}
dupd = {} # key: ABC tuples; value: list of indices

# Build up dupd based on equal A, B, C columns.
for i, rec in enumerate(records):
    # each record is a tuple with integers; can be used as key in dict
    if rec in dupd:
        dupd[rec].append(i)
    else:
        dupd[rec] = [i]
        
# build duplicates for each row, remove the ones with equal D
dups = []
D = df['D']
for i, rec in enumerate(records):
    dup = [j for j in dupd[rec] if i!=j and D[i] != D[j]]
    dups.append(tuple(dup))
    
df.drop(columns=['C10'], inplace=True)
df['Dups'] = dups
        
print(df)

Output:

   A  B     C  D       Dups
0  1  2  1.00  a     (2, 5)
1  1  2  1.01  a     (2, 5)
2  1  2  1.00  b  (0, 1, 5)
3  3  4  0.00  b       (4,)
4  3  4  0.00  c       (3,)
5  1  2  1.00  c  (0, 1, 2)
6  1  9  1.00  c         ()

Here is the original answer, which scales as O(N**2), but is easy to understand:

import pandas as pd
import numpy as np

df = pd.DataFrame(
    {'A': {0: 1, 1: 1, 2: 1, 3: 3, 4: 3, 5: 1, 6: 1},
     'B': {0: 2, 1: 2, 2: 2, 3: 4, 4: 4, 5: 2, 6: 9},
     'C': {0: 1.0, 1: 1.01, 2: 1.0, 3: 0.0, 4: 0.0, 5: 1.0, 6: 1.0},
     'D': {0: 'a', 1: 'a', 2: 'b', 3: 'b', 4: 'c', 5: 'c', 6: 'c'}})


dups = []
for i, irow in df.iterrows():
    dup = []
    for j, jrow in df.iterrows():
        if (i != j and 
            irow['A'] == jrow['A'] and
            irow['B'] == jrow['B'] and 
            abs(irow['C']-jrow['C']) < 0.05 and
            irow['D'] != jrow['D']
            ):
            dup.append(j)
    dups.append(tuple(dup))
df['Dups'] = dups

print(df)

This is far from pretty, but it does get the job done:

tolerance=0.05

dups={}
for _, group in df.groupby(['A', 'B']):
    for i, row1 in group.iterrows():
        
        data = []
        
        for j, row2 in group.iterrows():
            if i!=j:
                if abs(row1['C'] - row2['C']) <= tolerance:
                    if row1['D'] != row2['D']:
                        print(i,j)
                        data.append(j)
        
        dups[i] = data
        
dups = [dups.get(a) for a in range(len(dups.keys()))]
df['dups'] = dups

df

    A   B   C       D   dups
0   1   2   1.00    a   [2, 5]
1   1   2   1.01    a   [2, 5]
2   1   2   1.00    b   [0, 1, 5]
3   3   4   0.00    b   [4]
4   3   4   0.00    c   [3]
5   1   2   1.00    c   [0, 1, 2]
6   1   9   1.00    c   []

Convert to dictionary :

res = df.T.to_dict("list")
res

{0: [1, 2, 1.0, 'a'],
 1: [1, 2, 1.01, 'a'],
 2: [1, 2, 1.0, 'b'],
 3: [3, 4, 0.0, 'b'],
 4: [3, 4, 0.0, 'c'],
 5: [1, 2, 1.0, 'c'],
 6: [1, 9, 1.0, 'c']}

Get pairing of index and values into each sublist :

box = [(key,*value) for key, value in res.items()]
box

[(0, 1, 2, 1.0, 'a'),
 (1, 1, 2, 1.01, 'a'),
 (2, 1, 2, 1.0, 'b'),
 (3, 3, 4, 0.0, 'b'),
 (4, 3, 4, 0.0, 'c'),
 (5, 1, 2, 1.0, 'c'),
 (6, 1, 9, 1.0, 'c')]

Use itertools permutations along with your conditions to filter out matches :

from itertools import permutations
phase1 = [(ind, (first, second),*_) for ind, first, second, *_ in box]

#can be refactored with something cleaner
phase2 = [((*first[1],*first[2:]), second[0]) 
          for first, second in permutations(phase1,2) 
          if first[1] == second[1] and second[2] - first[2] <= 0.05 and first[-1] != second[-1]
         ]
phase2

[((1, 2, 1.0, 'a'), 2),
 ((1, 2, 1.0, 'a'), 5),
 ((1, 2, 1.01, 'a'), 2),
 ((1, 2, 1.01, 'a'), 5),
 ((1, 2, 1.0, 'b'), 0),
 ((1, 2, 1.0, 'b'), 1),
 ((1, 2, 1.0, 'b'), 5),
 ((3, 4, 0.0, 'b'), 4),
 ((3, 4, 0.0, 'c'), 3),
 ((1, 2, 1.0, 'c'), 0),
 ((1, 2, 1.0, 'c'), 1),
 ((1, 2, 1.0, 'c'), 2)]

Get the pairings via defaultdict :

from collections import defaultdict

    d = defaultdict(list)
    for k, v in phase2:
        d[k].append(v)
    
    d


defaultdict(list,
            {(1, 2, 1.0, 'a'): [2, 5],
             (1, 2, 1.01, 'a'): [2, 5],
             (1, 2, 1.0, 'b'): [0, 1, 5],
             (3, 4, 0.0, 'b'): [4],
             (3, 4, 0.0, 'c'): [3],
             (1, 2, 1.0, 'c'): [0, 1, 2]})

Combine values in d to string :

e = [(*k,",".join(str(ent) for ent in v)) for k,v in d.items()]
e

[(1, 2, 1.0, 'a', '2,5'),
 (1, 2, 1.01, 'a', '2,5'),
 (1, 2, 1.0, 'b', '0,1,5'),
 (3, 4, 0.0, 'b', '4'),
 (3, 4, 0.0, 'c', '3'),
 (1, 2, 1.0, 'c', '0,1,2')]

Create dataframe from extract :

cols = df.columns.append(pd.Index(["Dups"]))
dups = pd.DataFrame(e, columns=cols)

Merge with original dataframe :

result = df.merge(dups, how="left", on=["A", "B", "C", "D"])
result

    A   B   C       D   Dups
0   1   2   1.00    a   2,5
1   1   2   1.01    a   2,5
2   1   2   1.00    b   0,1,5
3   3   4   0.00    b   4
4   3   4   0.00    c   3
5   1   2   1.00    c   0,1,2
6   1   9   1.00    c   NaN