Forum Discussion
Generating numbers conditional on another column
My dataset contains a variable (X) with two categories, A and B.
The data looks like this:
X
A
A
A
A
B
B
B
B
I would like to generate a new variable, Y, such that there will be a certain percentage of 1s, 2s, etc. within the categories of X.
For the data above, I have 4 As, and 4 Bs. I want to create Y so that when X='A', Y will have 50% 1s and 50% 2s.
Similarly, when X='B', Y will have 25% 3s and 75% 4s.
For the table above, the final table would look like--
X Y
A 1
A 1
A 2
A 2
B 4
B 3
B 4
B 4
Any suggestions or clue to solving this would be much appreciated. Thank you.
Hi erchs,
You can add a calculated column with RANDBETWEEN and SWITCH function to calculate the result:
Y = SWITCH([X],"A",SWITCH(RANDBETWEEN(0,1),0,1,2),"B",SWITCH(RANDBETWEEN(0,3),0,3,4),-1)
Regards,
Xiaoxin Sheng
3 Replies
Hi erchs,
You can add a calculated column with RANDBETWEEN and SWITCH function to calculate the result:
Y = SWITCH([X],"A",SWITCH(RANDBETWEEN(0,1),0,1,2),"B",SWITCH(RANDBETWEEN(0,3),0,3,4),-1)
Regards,
Xiaoxin Sheng
For my general understanding, would you please explain how do the % of As, Bs and Cs are being taken into consideration here? Thank you.
Hi erchs,
>> would you please explain how do the % of As, Bs and Cs are being taken into consideration here?
For random 0 to 1, each item has 50% chance. it suitable for your request A.
I want to create Y so that when X='A', Y will have 50% 1s and 50% 2s.
For random between 0 to 3, each items has 25% chance. I setting one item equal to 3(1*25%) and others to 4(3*25%), so it is suitable for you request B.
when X='B', Y will have 25% 3s and 75% 4s.
Regards,
Xiaoxin Sheng
