Agfdhyk

Is there a nice way to bring only one row, preferably random in one-to-many matching during left join in Pandas?

e.g

left = [[1,1,1], [2,2,2],[3,3,3], [9,9,9], [1,3,2]]

right = [[1,2,2],[1,2,3],[3,2,2], [3,2,9], [3,2,2]]

left = np.asarray(left)

right = np.asarray(right)

left = pd.DataFrame(left)

right = pd.DataFrame(right)

joined_left = left.merge(right, how="left", left_on=[0], right_on=[0])

So this is what we get

   0  1  2

0  1  1  1

1  2  2  2

2  3  3  3

3  9  9  9

4  1  3  2



   0  1  2

0  1  2  2

1  1  2  3

2  3  2  2

3  3  2  9

4  3  2  2



   0  1_x  2_x  1_y  2_y

0  1    1    1  2.0  2.0

1  1    1    1  2.0  3.0

2  2    2    2  NaN  NaN

3  3    3    3  2.0  2.0

4  3    3    3  2.0  9.0

5  3    3    3  2.0  2.0

6  9    9    9  NaN  NaN

7  1    3    2  2.0  2.0

8  1    3    2  2.0  3.0

So now I want to have output to be of the same size as my left dataframe and when there are more than one match in right dataframe I want to bring only single random column.

Is there a nice way of doing it using pandas short cut tricks?

thank you!

You can shuffle right and drop_duplicates(...[, keep='first']) before merging.

right2 = right.sample(frac=1).drop_duplicates(subset=[0])

left.merge(right2, how='left', left_on=[0], right_on=[0])



   0  1_x  2_x  1_y  2_y

0  1    1    1  2.0  2.0

1  2    2    2  NaN  NaN

2  3    3    3  2.0  2.0

3  9    9    9  NaN  NaN

4  1    3    2  2.0  2.0

We shuffle right first, and then drop every duplicate except the first row (considering only column #0), which is the same as randomly selecting a row.