Create a associative array from unique selected rows and row number

[#1]

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_Number	Student	Sequence	Subject
1	A	1	Economics
2	A	2	Science
3	A	4	Physics
4	A	5	Math
5	A	2	Science
6	A	4	Chemistry
7	A	6	Science
8	A	2	Math
9	A	1	Social

*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  

[#2 · Accepted solution]

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

[#3]

@pmroz Thanks for replying.
My data set is very big and using this approach might take more time.
Also, my key_list is not fixed it depends based on condition whether it contains particular text or not.

[#4 · Accepted solution]

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

[#5 · Accepted solution]

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