cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • Learn how to build custom Python data connectors and further customize JMP’s Data Connector Framework with the Python Data Connector Demo, available now in the JMP Marketplace!
  • See how to create experiments to support product design and ID useful product features. Register for June 12 webinar, 2pm US Eastern Time.

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
aliegner1
Level IV

Group Columns by a list of values from another column?

I'm working to group several thousand columns together by name that matches with something from a list of values from an initial column

 

example:

Col1 values = {step1, step2, step3}

Col2:5000 names :step1_blahblahblah, step2_blahblbahblah, etc

 

I guess I'm not quite sure how to define the if(contain) statement uses the list of values from Col1.

 

dt = current data table();
Summarize( dt, StepIDIndex= by( :RecipeStepId) );//get list of unique RecipeStepID's
colNamesList = dt << get column names(string, continuous);  //get all the column names
StepList = {}; //Create an empty list for the matching columns

// Find matching column names
For( i = 1, i <= N Items( colNamesList ), i++,
	If( 
		contains( colNamesList[i], StepIDIndex),
		Insert Into( StepList , colNamesList[i] )
	)
);

// If groups of columns are found, create col groupings
If( N Items( StepList ) > 0,
	dt<< select columns( StepList );
	dt<< group columns();
);

 

 

1 REPLY 1
jthi
Super User

Re: Group Columns by a list of values from another column?

If your column names(?)  have always step1_something format, you could use Word to separate the step from rest of the string to make comparison easier. Below is an example of what I think you would like to do (requires JMP16 due to For Each):

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(3),
	Set Header Height(44),
	New Column("Col1", Character, "Nominal", Set Values({"step1", "step2", "step3"})),
	New Column("step1_a", Numeric, "Continuous", Format("Best", 12), Set Values([., ., .])),
	New Column("step1_b", Numeric, "Continuous", Format("Best", 12), Set Values([., ., .])),
	New Column("step1_c", Numeric, "Continuous", Format("Best", 12), Set Values([., ., .])),
	New Column("step2_a", Numeric, "Continuous", Format("Best", 12), Set Values([., ., .])),
	New Column("step2_b", Numeric, "Continuous", Format("Best", 12), Set Values([., ., .]))
);

Summarize(dt, uniq_vals = by(:col1)); 
col_list = dt << get column names(string, continuous);

//initialize col group collector associative array
col_groups = Repeat({{}}, N Items(uniq_vals));

For Each({col_name}, col_list,
	found_idx = Contains(uniq_vals, Word(1, col_name, "_"));
	If(found_idx,
		Insert Into(col_groups[found_idx], col_name);
	);
);

For Each({col_group, idx}, uniq_vals,
	If(N Items(col_groups[idx]) > 0,
		Eval(EvalExpr(dt << Group Columns(col_group, Expr(col_groups[idx]))));
	);
);
-Jarmo

Recommended Articles