Random value generation from a list

[akshatkumar87]

Hey Guys,

Trying to create some dummy data for a project. I have sample dataset of 1000 doctors and there is a feild called speciality which has certain list of values i want to randomly allocate each docter value from that very list of 10 values.

Sample data

Docid    Docname 

1           A

2           B

3           C

4           D

Speciality

Oconology

Dentist

My output should be randomly generated as :

Docid  Docname Speciality

1            A             Dentist

2            B             Oconology

3            C             Dentist

4            D             Dentist

Thank you in advance

[syedimranhashmi]

Is there a way to apply weightage to the random assignments ? For example, if I wanted twice as many dentists compared with any other specialty. 

My actual distribution looks something like this: 

Group A - 63%

Group B - 16%

Group C - 10%

Group D - 6%

Group E - 5% 

(each of these groups are a specialty - Virologists, Oncologists, etc)

Any help would be much appreciated ! 

Imran

[DultonM]

Hi @syedimranhashmi! There is definitely a way to accomplish a weighted random assignment. Here's one approach in a nutshell:

1. Make a table like the one you provided with the groups in one column and the weights in another. The weights should be decimals (.63, .16, etc.)
2. Use the Running Total and Multi-Row Formula tools to create Upper and Lower bounds (0-.63, .63-.79, etc.). This creates a range for each group. I'll call this result the "weighting grid".
3. Use the Append tool to bring together this weighting grid to your main data. Before the append, create a new field in your main data with the Formula tool's "Rand()" function. This function generates a random decimal between 0 and 1.
4. The append will duplicate every record of the main data with each row from the weighting grid. Now add a filter to keep only rows where "[Random]>=[LowerBound] and [Random]<[UpperBound]". This will keep the 1 row per original record where the random digit from 0-1 falls within the range of weights.

At this point, the group connected with the kept range will be attached to your main data, effectively randomly assigning the data, with weights! I attached a workflow that does what I described above. Let me know if you have any questions!