BookmarkSubscribe
Choose Language Hide Translation Bar
Community Trekker

## Col Shuffle for multiple groups

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Col Shuffle for multiple groups

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] )

),

);

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

Formula( Random Uniform() )     );

// sort

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

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

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++;

);

3 REPLIES 3
Super User

## Re: Col Shuffle for multiple groups

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);

Vince Faller - Predictum
Super User

## Re: Col Shuffle for multiple groups

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] )

),

);

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

Formula( Random Uniform() )     );

// sort

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

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

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++;

);

Community Trekker

## Re: Col Shuffle for multiple groups

This worked thank you!