Create a associative array from unique selected rows and row number

Question

Hi All,

I have table in which I have selected rows where subject is science or Math (using select where function). Now I want to create associative array which stores the row number of all selected with unique entries of a particular column.

After that I want to start a loop for all keys of the associative array. Which will subset the rows from key[i] to Key[i+1] for each value.

For Example:

Row_NumberStudentSequenceSubject1A1Economics2A2Science3A4Physics4A5Math5A2Science6A4Chemistry7A6Science8A2Math9A1Social

*Rows in blue color, I have selected using select where function.

* I want to create variables which store Associative Array : ["Science" => {2,5,7}], "Math" => {4,8}].
* Then I want to create a loop for both keys which will subset and generate 3 tables.* Table 1: From row number 2 to 5
* Table 2: From row number 5 to 7
* Table 3 : From row number 4 to 8

Please help, how I can solve.

Thanks :)

pmroz · Accepted Answer

This code should get you started.

dt = New Table( "Subjects", Add Rows( 9 ),
	New Column( "Student", Character( 16 ), "Nominal",
		Set Values( {"A", "A", "A", "A", "A", "A", "A", "A", "A"} ) ),
	New Column( "Sequence", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 4, 5, 2, 4, 6, 2, 1] ) ),
	New Column( "Subject", Character( 16 ), "Nominal",
		Set Values( {"Economics", "Science", "Physics", "Math", "Science", "Chemistry",
			"Science", "Math", "Social"} ) )
);

key_list = {"Science", "Math"};
subject_aa = associative array();
for (i = 1, i <= nitems(key_list), i++,
	found_rows = dt << get rows where(as column(dt, "Subject") == key_list[i]);
	subject_aa[key_list[i]] = found_rows;
);
show(subject_aa);

ian_jmp · Accepted Answer

@pmroz has done most of the work, which one can add to:

NamesDefaultToHere(1);

dt = New Table( "Subjects",
	Add Rows( 9 ),
	New Column( "Student",
		Character( 16 ),
		"Nominal",
		Set Values( {"A", "A", "A", "A", "A", "A", "A", "A", "A"} )
	),
	New Column( "Sequence",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 4, 5, 2, 4, 6, 2, 1] )
	),
	New Column( "Subject",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"Economics", "Science", "Physics", "Math", "Science", "Chemistry", "Science", "Math", "Social"}
		)
	)
);

// Make any row selection (in this case, the 'blue' rows)
dt << selectRows({2, 4, 5, 7, 8 });

// Get key list from the current row selection
subjectVals = Column(dt, "Subject")[dt << getSelectedRows];
key_list = AssociativeArray(subjectVals) << getKeys;

// Build associative arrays from current key list
subject_aa = associative array();
for (i = 1, i <= nitems(key_list), i++,
	found_rows = dt << get rows where(as column(dt, "Subject") == key_list[i]);
	subject_aa[key_list[i]] = found_rows;
);
show(subject_aa);

Right click on the JSL key words in the editor and do 'Help Scripting Index' to get more information.

Using your example, once you have the associative arrays the specification of the thee tables you say you want was unclear to me.

brady_brady · Accepted Answer

To get the subset tables that I believe you want, modify the last part of Ian's code so it appears as below. Specifically, nest the (new) j-indexed for-loop within Ian's i-indexed for loop.

Cheers,

Brady

// Build associative arrays from current key list
subject_aa = associative array();
for (i = 1, i <= nitems(key_list), i++,
	found_rows = dt << get rows where(as column(dt, "Subject") == key_list[i]);
	subject_aa[key_list[i]] = found_rows;
	for(j = 1, j<=nitems(found_rows)-1, j++,
		dt << subset(rows(found_Rows[j] :: found_Rows[j+1]));
	)
);
show(subject_aa);