Delete similar Data

Question

drop Duplicates from Data which are similar based on measurement inaccuracy

I'm struggling with a new Problem in Python for filtering of Data Duplicates. I am especially searching for a possibility to use it on big Data with more than 100 rows and over 25 columns.

Reducing to an easy example with the following Dataframe:

>>> df
   a         b         c         d
0  1.764052  0.400157  0.978738  2.240893
1  1.764052  0.400157  0.978738  2.240893
2 -0.103219  0.410599  0.144044  1.454274
3  0.761038  0.121675  0.443863  0.333674
4 -0.103219  0.410599  0.144044  1.454274
5  1.230291  1.202380 -0.387327 -0.302303
6  1.230291  1.202380 -0.387327 -0.302303
7  1.532779  1.469359  0.154947  0.378163
8  1.230291  1.202380 -0.387327 -0.302303
9  1.230291  1.202380 -0.387327 -0.302303

>>> df1 = df.drop_duplicates()

   a         b         c         d
0  1.764052  0.400157  0.978738  2.240893
2 -0.103219  0.410599  0.144044  1.454274
3  0.761038  0.121675  0.443863  0.333674
4 -0.103219  0.410600  0.144044  1.454274
5  1.240291  1.202380 -0.387327 -0.302303
7  1.532779  1.469359  0.154947  0.378163
8  1.230291  1.202380 -0.387327 -0.302303



>>> df2 = df. spezial code ?

   a         b         c         d
0  1.764052  0.400157  0.978738  2.240893
2 -0.103219  0.410599  0.144044  1.454274
3  0.761038  0.121675  0.443863  0.333674
5  1.240291  1.202380 -0.387327 -0.302303
7  1.532779  1.469359  0.154947  0.378163
8  1.230291  1.202380 -0.387327 -0.302303

So the drop.duplicates() in pandas is very efficient and super fast and works very well. But it only filters duplicate which are totally the same. But to minimize Date with look on measurement inaccuracy I would also like to drop Data which is similar and based on defined measurement inaccuracy the same.

So should also drop Row 4 which is 'almost' the same than Row 2 in column c.

On the other hand, it should stay Row 8 which is similar to Row 5 (in column a) but not in the measurement inaccuracy.

following a posibility what solve the the question for smal data but unfortunatly it is way to slow to work on big Data.

tolerances = {'a':0.001,
              'b':0.5,
              'c':0.5,
              'd':0.05}


df_clean = pd.DataFrame(columns=df.columns.to_list())
df_clean = df_clean.append(df.iloc[1])

for i in range(df.shape[0]):
    for j in range(df_clean.shape[0]):
        m = 0
        for key in tolerances:
            if ((df.iloc[i].loc[key] <= df_clean.iloc[j].loc[key]+tolerances[key]) and (df.iloc[i].loc[key] >= df_clean.iloc[j].loc[key]-tolerances[key])):
                m = m+1
            else:
                break
        if m == len(tolerances):
            break
    if j == (df_clean.shape[0]-1):
        df_clean = df_clean.append(df.iloc[i])


df_clean.sort_index(inplace=True)

>>> print(df_clean)
           a         b         c         d
0  1.764052  0.400157  0.978738  2.240893
1 -0.103219  0.410599  0.144044  1.454274
2  0.761038  0.121675  0.443863  0.333674
4  1.240291  1.202380 -0.387327 -0.302303
5  1.532779  1.469359  0.154947  0.378163
6  1.230291  1.202380 -0.387327 -0.302303

user_6159698 · Answer

Here's your input data:

from scipy.spatial.distance import pdist, squareform
import numpy as np
import pandas as pd

data = {'a': {0: '1.764052', 1: '-0.103219', 2: '0.761038', 3: '-0.103219', 4: '1.240291', 5: '1.532779', 6: '1.230291'}, 'b': {0: '0.400157', 1: '0.410599', 2: '0.121675', 3: '0.410600', 4: '1.202380', 5: '1.469359', 6: '1.202380'}, 'c': {0: '0.978738', 1: '0.144044', 2: '0.443863', 3: '0.144044', 4: '-0.387327', 5: '0.154947', 6: '-0.387327'}, 'd': {0: '2.240893', 1: '1.454274', 2: '0.333674', 3: '1.454274', 4: '-0.302303', 5: '0.378163', 6: '-0.302303'}}
df = pd.DataFrame(data, columns=["a", "b", "c", "d"])
tolerances = {'a': 0.001, 'b': 0.5, 'c': 0.5, 'd': 0.05}
tolerances_values = np.fromiter(tolerances.values(), dtype=float)

>>> print(df)
           a         b          c          d
0   1.764052  0.400157   0.978738   2.240893
1  -0.103219  0.410599   0.144044   1.454274
2   0.761038  0.121675   0.443863   0.333674
3  -0.103219  0.410600   0.144044   1.454274
4   1.240291  1.202380  -0.387327  -0.302303
5   1.532779  1.469359   0.154947   0.378163
6   1.230291  1.202380  -0.387327  -0.302303

You want to remove rows that are similar enough based on a distance you provide: differences between rows must not be larger than values defined in tolerances.

from scipy.spatial.distance import pdist, squareform

# Define your similarity function between rows. 
def is_similar(x, y):
    """
    Returns True if x is similar to y, False else
    """
    diffs = np.abs(y-x)  #  Look at absolute differences
    similar = all(diffs <= tolerances_values)  # True if all columns diffs are within tolerances
    return bool(similar)

# Compute similarities on all your dataframe
similarity_values = pdist(df.to_numpy(), is_similar)

# Convert np.array() into a pd.DataFrame()
similarity_df = pd.DataFrame(squareform(similarity_values), index=df.index, columns= df.index)

# Get indices of similar rows
similar_indices = similarity_df[similarity_df == True].stack().index.tolist() 

# Remove symmetric indices (from i,j i,i and j,i only keep i,j)
similar_indices = [sorted(tpl) for tpl in similar_indices if tpl[0] < tpl[1]]  

# Flatten 
similar_indices = list(set([item for tpl in similar_indices for item in tpl]))

Now here you go:

>>> df[~df.index.isin(similar_indices)]
          a         b         c         d
0  1.764052  0.400157  0.978738  2.240893
2  0.761038  0.121675  0.443863  0.333674
4  1.240291  1.202380 -0.387327 -0.302303
5  1.532779  1.469359  0.154947  0.378163
6  1.230291  1.202380 -0.387327 -0.302303

[Outdated] Other example using a cosine_similarity distance

Define a function to compute similarities and retrieve indices where similarity is higher than a threshold:

from sklearn.metrics.pairwise import cosine_similarity  # any other can be used

def remove_similar(df, distance, threshold):
    distance_df = cosine_similarity(df)
    similar_indices = [(x,y) for (x,y) in np.argwhere(distance_df>threshold) if x != y]
    similar_indices = list(set([item for tpl in similar_indices for item in tpl]))
    return df[~df.index.isin(similar_indices)]

Now you can try with distance=cosine_similarity and play with thresholds:

>>> remove_similar(df, cosine_similarity, 0.9)

          a         b         c         d
0  1.764052  0.400157  0.978738  2.240893
2  0.761038  0.121675  0.443863  0.333674
5  1.532779  1.469359  0.154947  0.378163

>>> remove_similar(df, cosine_similarity, 0.9999999)

          a         b          c          d
0  1.764052  0.400157   0.978738   2.240893
2  0.761038  0.121675   0.443863   0.333674
4  1.240291  1.202380  -0.387327  -0.302303
5  1.532779  1.469359   0.154947   0.378163
6  1.230291  1.202380  -0.387327  -0.302303

Delete similar Data

1 Answers