cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Rajat
Level IV

Create a associative array from unique selected rows and row number

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_NumberStudentSequenceSubject
1A1Economics
2A2Science
3A4Physics
4A5Math
5A2Science
6A4Chemistry
7A6Science
8A2Math
9A1Social

 

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
pmroz
Super User

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

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

View solution in original post

ian_jmp
Staff

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

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

View solution in original post

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

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

View solution in original post

4 REPLIES 4
pmroz
Super User

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

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);
Rajat
Level IV

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

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

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

@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

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