Skip records on Random % tool Sampling

Hi - Thank you for looking into this.

I am trying to sample a dataset using the Random % tool by 10%, which is straight forward.

But i need the random tool to skip records flagged as 'inactive', and only output the sampled records which are flagged as 'active'.

I don't want to filter out the 'inactive' records before the random tool because then i would not get the desired 10% of the whole population.

For example, if dataset has 1000 records - 600 active and 400 inactive. I need result to be 100 active sampled records. Not 10% of 600 active.

Can any one help me with this?

Thanks

Developer

Developer Tools

Fuzzy Match

Dynamic Processing

Expression

Interface Tools

Machine Learning

Iterative Macro

Accepted answers

atcodedog05

Hi @win_flags

You can do something like this.

1. In summarize tool you can take groupby on group

2. Instead of order (record id tool) i am using multi-row formula tool to create group order (using groupby on group)

3. Instead of append tool i am using join tool to get each groups sample count.

4. Filter where order <= sample count.

Edit : filter formula for wrong fixed it.

Hope this helps 🙂

random sample active (1).yxmd

All comments

atcodedog05

Hi @win_flags

Since this a custom scenario normal random sample cant be used. Here is how you can achieve your requirement.

Upper

1. Using RandInd() function in formula tool i am generating random id for each row (this will change on each run)

2. Sorting it by RandID hence order is randomized

3. Filtering to keep only active

4. Adding order (record id) using record id tool. Order will be later used for filtering

Lower

1. Using summarize to get total count

2. Using formula tool to set random %(10%) and calculate sample count(1000*0.1=100)

3. Using append tool to join sample count with the data (upper branch)

4.using filter tool to keep only first n% (10%) which will give first 100 records which were randomly ordered.

You can test it to check whether it satisfies the condition

Hope this helps 🙂

random sample active.yxmd

win_flags

@atcodedog05 - Thank you. This makes sense.

I just have one more condition to this.

So, I have another column in source data, lets call it 'Group' with values as A, B, C across 1000 records. I need 10% active employees from within each group.

Example -

Group Type Records

A Active 200

A Inactive 100

10% active records from group A - 30

B Active 300

B Inactive 100

10% active records from group B - 40

C Active 100

C inactive 200

10% active records from group C - 30

I want to group them then do the random sampling in each group, resulting in only active records maintaining the total numbers of records.

Thanks in advance!

Qiu

@win_flags

I was able to implement the Random % Sample into a batch macro.

But the Npercent of Ramdom % Sample tool will only take integer so there are cases that not exactly 10% generated.

random sample active-r1.yxmc

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703