Subscribe Bookmark RSS Feed

Col Shuffle for multiple groups

guyon

Community Trekker

Joined:

Dec 16, 2014

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
Solution

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

);

3 REPLIES
vince_faller

Super User

Joined:

Mar 17, 2015

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

Solution

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

);

guyon

Community Trekker

Joined:

Dec 16, 2014

This worked thank you!