topic Re: Create a associative array from unique selected rows and row number in Discussions

Create a associative array from unique selected rows and row number

Rajat — Tue, 14 Apr 2020 11:45:33 GMT

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

Re: Create a associative array from unique selected rows and row number

pmroz — Tue, 14 Apr 2020 12:42:14 GMT

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

Re: Create a associative array from unique selected rows and row number

Rajat — Tue, 14 Apr 2020 13:40:45 GMT

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

Re: Create a associative array from unique selected rows and row number

ian_jmp — Wed, 15 Apr 2020 09:56:35 GMT

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

Re: Create a associative array from unique selected rows and row number

brady_brady — Sat, 18 Apr 2020 00:34:56 GMT

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