Duplicate and random sample by unique ID

Hi,

I've been facing a challenge to arrange some data I have. I have the table below. Due a technical limitation, I receive data like this where there is a duplicate for ID 1 (like sample 6033DDA) and I need to choose one of the ID 2 I see allocated to it, either ending HU1R005294 or HU4R003527 as example.

The issue is that right below for the next duplicate ID 1 (6043DDA) I will have to get either HU1R005294 in case I have considered already the HU4R003527 or the other way around.

I would either like to have the number HU4R003527 associated to both lines of 6033DDA, then HU1R005294 associated to both lines of 6043DDA.

The count I put there because there are scenarios which I have 3 times the same ID 1 and 3 different ID 2, which I have to apply the same logic.

Hope I was clear enough.

TIA

ID 1	ID 2	Count
2022080100000000006033DDA	HU1R005294	2
2022080100000000006033DDA	HU4R003527	2
2022080100000000006043DDA	HU1R005294	2
2022080100000000006043DDA	HU4R003527	2
2022080100000000006109DDA	282C001135	2
2022080100000000006109DDA	282C001138	2
2022080100000000006110DDA	282C001135	2
2022080100000000006110DDA	282C001138	2
2022080100000000006196DDA	362C000672	2
2022080100000000006196DDA	362C000674	2
2022080100000000006198DDA	362C000672	2
2022080100000000006198DDA	362C000674	2

Datasets

Preparation

Accepted answers

gyang3

Hey @brunosa I've attached a workflow that'll get you to what I believe you're looking for your output. I've basically pulled in Unique tools to remove any duplicates and lastly join all of the unique records together. I've built this workflow taking into account scenarios in which you may have 3 of the same ID1. See screenshot below, and workflow attached.

If this helped solve your issue, please mark this as the solution. Thanks!

Duplicate - Random Sample.yxmd

gyang3

@brunosa I've revised the workflow to get to get to your desired solution. I've included those with 2 counts as well in the workflow. However, it'll work whether you have 2 counts, 3 counts, or both in the same file. See attached. Let me know if that helps.

Duplicate - Random Sample Revised.yxmd

All comments

binu_acs

@brunosa Can you provide the expected result?

AdwaitTarudkar

Hi @brunosa

Hope this helps-

1. Unique tool 1 - ID1

2. Unique tool 2 - ID 2

3. Join tool 1 - Join both U

4. Join tool 2 - Join both D

5. Union tool for the desired solution

gyang3

If this helped solve your issue, please mark this as the solution. Thanks!

Duplicate - Random Sample.yxmd

brunosa

Hi Adwait,

You did the join by specific fields? or position?

brunosa

Hi @gyang3, it worked perfectly for the 2 of same ID but when it brings down to 3, I have an issue still. Send how I would like it to be.

RAW DATA	RESULT WITH THE SOLUTION	DESIRED SOLUTION
ID 1	ID2	COUNT	ID 1	ID2	COUNT	ID 1	ID2	COUNT
2022080200000000007287DDA	C08C014804	3	2022080200000000007287DDA	C07C008677	3	2022080200000000007287DDA	C07C008677	3
2022080200000000007287DDA	C08C014786	3	2022080200000000007287DDA	C07C008677	3	2022080200000000007287DDA	C07C008677	3
2022080200000000007287DDA	C07C008677	3	2022080200000000007287DDA	C07C008677	3	2022080200000000007287DDA	C07C008677	3
2022080200000000007289DDA	C08C014804	3	2022080200000000007289DDA	C08C014786	3	2022080200000000007289DDA	C08C014786	3
2022080200000000007289DDA	C07C008677	3	2022080200000000007289DDA	C08C014786	3	2022080200000000007289DDA	C08C014786	3
2022080200000000007289DDA	C08C014786	3	2022080200000000007289DDA	C08C014786	3	2022080200000000007289DDA	C08C014786	3
2022080200000000007290DDA	C07C008677	3	2022080200000000007290DDA	C08C014786	3	2022080200000000007290DDA	C08C014804	3
2022080200000000007290DDA	C08C014786	3	2022080200000000007290DDA	C08C014786	3	2022080200000000007290DDA	C08C014804	3
2022080200000000007290DDA	C08C014804	3	2022080200000000007290DDA	C08C014786	3	2022080200000000007290DDA	C08C014804	3

gyang3

Duplicate - Random Sample Revised.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703