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

Setting multiple rows in a single column to a single value

StarfruitBob — Fri, 28 Jul 2023 22:26:33 GMT

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'.

I know of the << Set all values function, but that copies a single value for all rows in a column.
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.
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:

{ UID => [rows] }
{ UID => value }

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

StarfruitBob — Fri, 28 Jul 2023 23:04:54 GMT

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

Craige_Hales — Sat, 29 Jul 2023 01:44:25 GMT

Use data table indexing like this

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

txnelson — Sat, 29 Jul 2023 01:50:12 GMT

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

hogi — Sat, 29 Jul 2023 20:11:39 GMT

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

Craige_Hales — Sat, 29 Jul 2023 21:18:45 GMT

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

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

StarfruitBob — Tue, 01 Aug 2023 15:21:54 GMT

@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

StarfruitBob — Tue, 01 Aug 2023 15:54:18 GMT

@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