Extract unique minimum distance pairs using each element only once

user_12962709 0
Viewed 226

From a distance matrix I have generated a dataframe consisting of three columns.

Column x and y contains all the unique combinations of the ids: a, b, c, and d. (The id's are simplified for illustration purposes, the actual data consists of strings of digits, i.e. '1234', '2323', '2443')

The test matrix is sorted by distance as I'm interested in all the unique combinations containing the lowest distance measures.

Is there a function capable of extracting unique pairs from columns x and y such that each element only appear once in either column x and y?

In the following scenario I am interested in only one pair containing A, one pair containing B, and so on.

Example:

Input:

test = pd.DataFrame(np.array([['a', 'b', 0.1], ['b', 'a', 0.1], 
                              ['c', 'a', 0.2], ['a', 'c', 0.2], 
                              ['b', 'd', 0.3], ['d', 'b', 0.3], 
                              ['c', 'd', 0.4], ['d', 'c', 0.4]]), columns=['x', 'y', 'distance'])  
test
   x  y distance
0  a  b      0.1
1  b  a      0.1
2  c  a      0.2
3  a  c      0.2
4  b  d      0.3
5  d  b      0.3
6  c  d      0.4
7  d  c      0.4

Desired output:

   x  y distance
0  a  b      0.1
1  c  d      0.4

Whether the output presents (a, b) or (b,a) is not important.

Note that dropping duplicates in this example is not sufficient, as pair (c, d) and (d, c) would be removed.

EDIT:

Here's a (messy) while loop that provide the right output, but I was hoping for a less complex solution to speed up the for loop in which this function will be nested. 

i = 0

while i < len(test):

    comp_x = test.loc[i,'x']
    comp_y = test.loc[i,'y']

    test.loc[i+1:len(test),['x','y']] = test.loc[i+1:len(test),['x','y']][~test.loc[i+1:len(test),:][['x','y']].isin([comp_x])]
    test.loc[i+1:len(test),['x','y']] = test.loc[i+1:len(test),['x','y']][~test.loc[i+1:len(test),:][['x','y']].isin([comp_y])]

    test.dropna(inplace=True)
    test.reset_index(drop=True, inplace=True)

    i+=1

print(test)
   x  y distance
0  a  b      0.1
1  c  d      0.4
pandas duplicates python
3 Answers

Please test to see if this is what you are after:

xy=set()
records=[]

for e in test.itertuples():
    if ((e[1] not in xy) and (e[2] not in xy)):
        records.append(e)
        xy.update(list(e[1:3]))

pd.DataFrame(records).set_index('Index').rename_axis('')

    x   y   distance        
0   a   b   0.1
6   c   d   0.4
user_6678830

You can try by combining the columns 'x' & 'y' and drop duplicates from this new column


test['filter'] = test[['x', 'y']].apply(lambda row: '-'.join(sorted(row, key=lambda y: ord(y))), axis=1)

test = test.drop_duplicates(subset=['filter']).reset_index()[['x', 'y', 'distance']]

# solution
#    x  y distance
# 0  a  b      0.1
# 1  c  a      0.2
# 2  b  d      0.3
# 3  c  d      0.4
user_13714810

This should work,

the idea is to create a new series and sort your values along their axis and use a sort_by

s = test[["x", "y"]].sum(axis=1).apply(
    lambda x: sorted(x)
).explode().drop_duplicates().reset_index(drop=True)


s1 = (
    s.groupby(s.index // 2)
    .agg(",".join)
    .str.split(",", expand=True)
    .rename(columns={0: "x", 1: "y"})
)

test.sort_values("distance").loc[(test["x"].isin(s1["x"])) & (test["y"].isin(s1["y"]))]

out:

   x  y distance
0  a  b      0.1
6  c  d      0.4
user_93751020
Related