topic Re: Col Shuffle for multiple groups in Discussions

Col Shuffle for multiple groups

guyon — Thu, 07 May 2015 00:14:44 GMT

I have a dataset with several samples and each sample has 10-30 measurements in separate rows. I want to be able to assign a random value from 1 to the number of measurements for each sample. I can use Col Shuffle to randomize but it requires subetting the data into several tables and applying a formula to each subset before updating the original table with the randomized numbers. Is there a method to do this with one equation?

Re: Col Shuffle for multiple groups

vince_faller — Thu, 07 May 2015 00:37:05 GMT

I assumed 2 columns, 1 named sample and 1 named Values.

dt = current data table();

samples = Associative Array(:Sample);

dt<<Sort(By(:Sample), replace table);

for(i=1, i<=Nitems(samples), i++,

rows = dt << get rows where(:Sample == samples[i]);

start_row = min(rows);

end_row = max(rows);

samples[i]= :Values[Random Integer(start_row, end_row)];

);

show(samples);

Re: Col Shuffle for multiple groups

pmroz — Thu, 07 May 2015 00:47:16 GMT

Try something like this:

// Create a test table

dt = New Table( "Sample Measurements", Add Rows( 13 ),

New Column( "Sample", Character, Nominal, Set Values(

{"A", "A", "A", "A", "A", "B", "B", "B", "C", "C", "C", "C", "C"} ) ),

New Column( "Measurement", Numeric, Continuous, Format( "Best", 12 ),

Set Values( [65, 76, 23, 43, 67, 65, 546, 34, 64, 76, 32, 67, 88] )

);

// Add a random column

dt << New Column( "Random Value", Numeric, Continuous, Format( "Best", 12 ),

Formula( Random Uniform() ) );

// sort

dt << sort(replace table, by(:Sample, :Random Value));

// Add a sequence column

dt << new column("Sequence", numeric, continuous);

// Add sequence values

old_value = "XYZZ";

for (i = 1, i <= nrows(dt), i++,

sample_value = dt:sample[i];

if (sample_value != old_value,

old_value = sample_value;

k = 1;

);

dt:sequence[i] = k;

k++;

);

Re: Col Shuffle for multiple groups

guyon — Thu, 07 May 2015 02:21:49 GMT

This worked thank you!