563852_en-US

liliconcom

How to count for a set of items across columns

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!

Automation and ScriptingData Blending and CleanupMass CustomizationQuality and Process EngineeringRe: How to count for a set of items across columns

Thanks @txnelson !

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

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

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