Find duplicates in dataframe where one column can be within a range

Question

I've got everything figured out as far as finding duplicates. I have a column marking them True or False and then I'm deleting one with a certain value. At this point I just need to include anything where one column is within a range of rows.

For an example:

       Status Height Object  Store
0        Here   100'    ABC  EFG
1  Maybe here    99'    ABC  EFG
2  Maybe here   102'    ABC  JKL
3  Maybe here    99'    ABC  QRS
4        Here    80'    XYZ  QRS
5  Maybe here    78'    XYZ  JKL

Desired output:

       Status Height Object  Store
0        Here   100'    ABC  EFG
2  Maybe here   102'    ABC  JKL
3  Maybe here    99'    ABC  QRS
4        Here    80'    XYZ  QRS
5  Maybe here    78'    XYZ  JKL

The "Maybe here" rows should be deleted because their height is within +/- 4ft. Can anyone point me in the right direction?

Thank you.

Answer 1

You can use numpy solution with specify values for get +-4 range and filter by boolean indexing:

print (df)
       Status Height Object
0        Here   100'    ABC
1  Maybe here    99'    ABC
2  Maybe here   102'    ABC
3  Maybe here    99'    ABC
4        Here    80'    XYZ
5  Maybe here    78'    XYZ


#specify values for check ranges
vals = [100, 80]
#remove traling 'and convert to integer
a = df['Height'].str.strip("'").astype(int)

#convert to numpy array and compare, get abs values
arr =  np.abs(np.array(vals) - a.values[:, None])
print (arr)
[[ 0 20]
 [ 1 19]
 [ 2 22]
 [ 1 19]
 [20  0]
 [22  2]]

---

#xreate boolean mask for match at least one True
mask = np.any((arr > 0) & (arr < 4), axis=1)
print (mask)
[False  True  True  True False  True]

#inverting condition by ~
print (df[~mask])
  Status Height Object
0   Here   100'    ABC
4   Here    80'    XYZ

Similar:

#invert conditions and check if all values Trues per row
mask = np.all((arr <= 0) | (arr >= 4), axis=1)
print (mask)
[ True False False False  True False]

print (df[mask])
  Status Height Object
0   Here   100'    ABC
4   Here    80'    XYZ

EDIT:

Solution is similar only chained new boolean mask created by DataFrame.duplicated:

#specify values for check ranges
vals = [100, 80]
#remove traling 'and convert to integer
a = df['Height'].str.strip("'").astype(int)

#convert to numpy array and compare, get abs values
arr =  np.abs(np.array(vals) - a.values[:, None])
print (arr)
[[ 0 20]
 [ 1 19]
 [ 2 22]
 [ 1 19]
 [20  0]
 [22  2]]

---

#create boolean mask for match at least one True
mask1 = np.any((arr > 0) & (arr < 4), axis=1)
print (mask1)
[False  True  True  True False  True]

mask2 = df.duplicated(subset=['Object','Store'], keep=False)
print (mask2)
0     True
1     True
2    False
3    False
4    False
5    False
dtype: bool


mask = mask1 & mask2

#inverting condition by ~
print (df[~mask])
       Status Height Object Store
0        Here   100'    ABC   EFG
2  Maybe here   102'    ABC   JKL
3  Maybe here    99'    ABC   QRS
4        Here    80'    XYZ   QRS
5  Maybe here    78'    XYZ   JKL

---

#invert conditions and check if all values Trues per row
mask3 = np.all((arr <= 0) | (arr >= 4), axis=1)
print (mask3)
[ True False False False  True False]

mask = mask3 | ~mask2

print (df[mask])
       Status Height Object Store
0        Here   100'    ABC   EFG
2  Maybe here   102'    ABC   JKL
3  Maybe here    99'    ABC   QRS
4        Here    80'    XYZ   QRS
5  Maybe here    78'    XYZ   JKL

Answer 2

To decide whether to delete a row based on height, check if at least one element in [height-threshold, height+threshold] is already present in the dictionary. If present, remove the height

For example, if height=80 & threshold=4, check if at least one number among 76, 77, 78, 79, 80, 81, 82, 83, 84 is present in the dictionary. If present, delete the row.

global dictionary

def can_i_remove(item, threshold):
    global dictionary
    key = item-threshold
    while(key <= (item+threshold)):
        if(dictionary.get(key) != None):
            return True
        key = key+1
    dictionary[item] = False
    return False

def main():
    global dictionary
    dictionary = dict()
    threshold = 4
    ret = can_i_remove(100, threshold)
    print(str(dictionary) + " -> 100 - " + str(ret))
    ret = can_i_remove(96, threshold)
    print(str(dictionary) + " -> 96 - " + str(ret))
    ret = can_i_remove(95, threshold)
    print(str(dictionary) + " -> 95 - " + str(ret))
    ret = can_i_remove(104, threshold)
    print(str(dictionary) + " -> 104 - " + str(ret))
    ret = can_i_remove(105, threshold)
    print(str(dictionary) + " -> 105 - " + str(ret))

main()

Output :

{100: False} -> 100 - False
{100: False} -> 96 - True
{100: False, 95: False} -> 95 - False
{100: False, 95: False} -> 104 - True
{100: False, 95: False, 105: False} -> 105 - False