topic Re: How to count for a set of items across columns in Discussions

How to count for a set of items across columns

Vins — Fri, 09 Jun 2023 00:56:38 GMT

Hi JMP staff and experts,

I have a simple issue I think JSL would be the best approach, but I do not know how to code in JSL. I should probably find time and learn!

Basically, I have a list of items, "Tea", "Eggs", "Bread", "Milk" etc. Each item appears alone in a column, and the number of columns can vary but would be fixed in a particular table instance. Each row represents an independent observation of items. A specific item can appear in any column, in any row but only once in a row or not appear at all.

I would like to use a JSL script to indicate in a new column wether a particular set of items (say "Tea", "Milk", "Bread") exist in a particular row or not. Order of occurrence in a row does not matter. However, the set of items I am counting have to all appear. I hope to use this JSL as a template to modify and query for the occurrence of sets of items in tables with rows of upto 10000. Eventually I would like to derive stats such as: 10% of the entries have "Tea" & "Bacon", 5% have "Tea", "Milk", "Yoghurt" etc

Is there a JMP platform that can do this?

Attached is an example dataset.

Many thanks!

Re: How to count for a set of items across columns

txnelson — Thu, 03 Nov 2022 02:11:10 GMT

Here is a formula that will place a 1 in the indicator column if the combination of Bread and Yoghurt are found in a given row, and a 0 if not found

dt = Current Data Table();
itemList = dt[Row(), Index( 1, 5 )];
If( Contains( itemList, "Bread" ) & Contains( itemList, "Yoghurt" ),
	1,
	0
);

Re: How to count for a set of items across columns

jthi — Thu, 03 Nov 2022 06:34:45 GMT

Here is one option using associative arrays and their set operations

Names Default To Here(1);

dt = Open("$DOWNLOADS/items.jmp");

list_to_search_for =  {"Tea", "Milk", "Bread"};
aa_mask = Associative Array(list_to_search_for);

dt << New Column("Match", Numeric, Continuous, << Set Each Value(
	aa_row = Associative Array(dt[Row(), 0]);
	temp_mask = aa_mask;
	temp_mask << Remove(aa_row);
	If(N Items(temp_mask) == 0,
		1
	,
		0
	);
));

JMP's Consumer Research platform might also be helpful but I haven't used it so I'm not sure if it can look for such combinations as you need (Conditional Association might be helpful?)

Re: How to count for a set of items across columns

Vins — Sun, 06 Nov 2022 22:36:05 GMT

Thanks @txnelson !