topic Re: JMP Script: New Column for every specified value in column in Discussions

JMP Script: New Column for every specified value in column

AlphaLion662 — Thu, 08 Jun 2023 16:30:56 GMT

Hello All,

I am trying to create new columns for every number value that comes after |90= and label the columns as the string that comes before |90, so the pX or whatever else comes before it. See attached photo below as example.

So, in the first row, I'd like to grab that 93 that comes after "p1|90=", and make a new column labelled "p1". I'd like to do that every number that comes after any and all "pX|90=". That pX might also be sX or rX or pXa/b/c/...and so on. So I'd also like to make new columns for those values as well. The number of data points per row varies greatly, from just one(1) pX|90= value to 15 different pX|90=values, perhaps with some values with sX|90= or rX|90=.

I was using the Word function that grabbed pX|00 data values. Now I want to grab just 90, and tried using the previous script I was using for that but found that it wasn't grabbing all of the values I wanted.

This is for hundreds, soon to be thousands, of rows, so manually doing this isn't tenable.

What function(s) makes sense to use here?

I am new to JMP scripting, am willing to learn and listen to any ideas. Thank you!

Re: JMP Script: New Column for every specified value in column

vince_faller — Tue, 07 Mar 2023 17:11:47 GMT

No idea if this is the best way but it's fairly straight forward.

Names default to here(1);
dt= New Table( "splitting bunches",
	Add Rows( 1 ),
	New Column( "Fun",
		Character,
		"Nominal",
		Set Values( {"<p>p1|00=51;p1|90=32;p2|00=48;p2|90=90;", 
			"<p>p1|00=47;p1|90=60;p4|00=73;p4|90=49;"
		} )
	), 
	
);

dt << New Column("just_delim", 
// this is just getting rid of that beginning part as I image you don't want it. 
	character, formula(substitute(:Fun, "<p>", ""))
);
// split it on ; into however many columns
dt << Text To Columns(
	delimiter( ";" ),
	columns( :just_delim )
);
// get the columns you just made
cols_to_stack = (dt << get Column References)[3::ncols(dt)];
// stack them now 
dt_stack = dt << Stack(
	columns( cols_to_stack ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ), output table("stacked")
);
// same thing 
dt_stack << Text To Columns(
	delimiter( "|=" ),
	columns( :Data )
);
dt_stack:"Data 1"n<<Set Name("pX");
dt_stack:"Data 2"n<<Set Name("num_after_pipe");
dt_stack:"Data 3"n<<Set Name("value");

// assuming you want these to be numbers
dt_stack:value << Set Data Type("Numeric") << Set Modeling Type("Continuous");

// can now filter out the 90s if you want
rows = dt_stack << Get Rows Where(:num_after_pipe != "90");
//delete rows
dt_stack << Delete Rows(rows);

// now just split the table and you should be good. 
dt_split = dt_stack << Split(
	Split By( :pX ),
	Split( :value ),
	Group( :Fun ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

Re: JMP Script: New Column for every specified value in column

AlphaLion662 — Tue, 07 Mar 2023 18:34:08 GMT

Worked for me AND it makes sense. Thank you! I am glad you found this to be "fun".