663654_en-US

liliconcom

Setting multiple rows in a single column to a single value

Hello,

 

Noob question...

 

I have a column with a unique identifier (UID) for certain types objects, for instance {'cats', 'dogs', 'birds'}. Each UID has multiple rows associated with it, which I have captured in an associative array.

Example: aa = { 'cats' => [1, 2, 3], 'dogs' => [4, 5, 6], 'birds' => [7, 8, 9] }

For each UID, all rows associated, I need to set a different column (col2) to a single value.

For example: For UID 'cats', rows 1, 2 & 3, in col2, need to be set to 'likes napping'.

 

1. I know of the << Set all values function, but that copies a single value for all rows in a column.
2. If I use dt:col 2[ aa[ 'cats' ] ] = 'likes napping', a L-value error will occur, which informs me I need a matrix of values equal to the number rows, so each row can be assigned that value.
3. Or, I can use a for loop nested in a for loop to assign values where I need.

 

This all seems clunky though. There must be a better way.

 

Here are the 2 associative arrays:

1. { UID => [rows] }
2. { UID => value }

 

Automation and ScriptingBasic Data Analysis and ModelingContent OrganizationData AccessWindowsRe: Setting multiple rows in a single column to a single value

When making this post, I forgot to add another possibility. Note: this dataset has a lot of rows.

// This completely removes the need to store rows in an AA, but takes a decent amount of time
for( i = 1, N rows( dt ), i++,
    row_UID = dt:UID[i];
    dt:col2[i] = aa_value[row_UID];
);

Re: Setting multiple rows in a single column to a single value

Use data table indexing like this

Data table subscripting 

Re: Setting multiple rows in a single column to a single value

I would use the table manipulation capabilities of JMP for this operation

Names Default To Here( 1 );
animals = {"cats", "dogs", "birds"};
dt = New Table( "lots of animals",
 add rows( 100 ),
 New Column( "UID",
  character,
  set each value( animals[Random Integer( 1, 3 )] )
 )
);

// Build the attribute table
dtLookup = new table("Lookup",
    add rows(3),
 new column("UID", character,
  values({"cats", "dogs", "birds"})
 ),
 new column("col2", character,
  values({"likes napping","likes frisbee","likes cracker"})
 )
);

// Update the table to expand all values into matching rows
Data Table( "lots of animals" ) << Update(
 With( Data Table( "Lookup" ) ),
 Match Columns( :UID = :UID )
);

 

Re: Setting multiple rows in a single column to a single value

Data table substripting is sooo cool!

why the associative array?

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
myMatrix= dt<< getrowswhere(age==12);
dt[myMatrix,{age}]=999

Re: Setting multiple rows in a single column to a single value

Only because of the original question, but associative arrays are cool too!

 

Re: Setting multiple rows in a single column to a single value

@Craige_Hales, there's a thread somewhere on this site that has this trick for subscripting. I should probably bookmark it because of tricks like this!  Thank you!

Re: Setting multiple rows in a single column to a single value

@Craige_Hales, here's a link to your thread on subscripting for those who want to know more.

PS - these tricks can save you a bunch of time! It's 100% worth the read!

https://community.jmp.com/t5/Uncharted/Data-table-subscripting/ba-p/21013