topic Re: Stack Columns with multiple names in Discussions

Stack Columns with multiple names

xXjmplearnerXx — Wed, 20 Mar 2024 11:13:37 GMT

Hello everyone!
Lets say I have a data table which looks like that:

A,B1,B2,C1,C2,D

1,2,3,4,5,16

6,7,8,9,10,16

11,12,13,14,15,16

I want to stack them so the new table looks like that:

A,B,C

1,2,4

1,3,5

6,7,9

6,8,10

11,12,14

11,13,15

I have three lists: one containing columns that need to be kept: list_to_keep= {A},list_to_stack_before= {B1,C1} and list_to_stack_after = {B2,C2}.

First i wrote a script to create a new list with the new column names:

new_column_names = {};

str = char(list_to_stack_before);
newstr = substitute(str,"", "", "_be_sam","");
new_column_names = parse(newstr);

Then I tried to loop the stack function but it always just adds more rows. I can not use the normal Stack function because it doesnt let me use multiple names in the Stacked Data Column argument. How can I solve this problem? Thanks a lot i am very new to jmp.

Re: Stack Columns with multiple names

GregF_JMP — Wed, 20 Mar 2024 12:49:57 GMT

Hello new member

While scripting can be done, perhaps see how far you can go prior to coding (protip- in JMP it's often pretty far).

The Tables Menu, Stack command will get most of the way there. The realtime preview is really helpful.

After executing this command, rename the "Data" Cols to A and B, delete the Label Column.

If you want to script it, after executing the stack command the green "Source" triangle in upper left panel will re-execute the action. Right click/edit to see the script syntax.
To get script syntax - use workflow builder to record steps, then red triangle> save to script window.

Re: Stack Columns with multiple names

xXjmplearnerXx — Wed, 20 Mar 2024 13:28:44 GMT

Hello Greg, thanks a lot for your reply!

I am familiar with the stack interface and also used the workflowbuilder to get a script to stack. The problem with renaming is that I do not have 3 columns that I want to stack but more like 40 and I expect it to grow with new measurements coming in..

I realized I could probably write a script to rename the columns using a list but this seems like a not so nice workaround and prone to mistakes in the order. I was hoping there is a smoother method. It's very unfortunate that the "new col names" option does not allow for more than one argument.

Best

Re: Stack Columns with multiple names

mmarchandTSI — Wed, 20 Mar 2024 17:04:49 GMT

Are you dropping column D? In your real data tables, how do you decide which columns to drop? What is the naming convention for the columns (pre- and post-stack)? This shouldn't be too hard to automate via JSL.

*edit*

Also, is the number of multiples always the same? For instance, if there's a B1 and B2, will there always be C1 and C2, or could there also be a C3? If so, how should that be handled?

Re: Stack Columns with multiple names

GregF_JMP — Wed, 20 Mar 2024 19:09:19 GMT

OK-
Good that you are able to script. Thanks for clarifying that you are looking for something scalable to avoid renaming many columns..

First as an aside/tangent- as a tip&trick for column name manipulation in a manual mode, but at scale: this is quick & powerful (but needs to be done with care)

in Columns Pane, select all columns- "copy"(puts the text in the copy buffer)
In another data table, (or a spreadsheet or word processing program), "Paste"- the list of columns is created as text.
Manipulate it as you wish (type edit text in place, make formula column to add prefix/suffix... whatever)...
"Copy" the edited list of text new column names
Return to original table. Clear columns selection, then reselect list of columns in columns pane.. "Paste" to overwrite edited column names

Back to the table operations method proposed before.
If the lowest character will always be 1/2 (or at least indicate the column family that should be stacked), this can be done in two table operations and a formula. These steps could be scripted or captured in workflow builder if the actions need to be repeatable.

Add formula column to trim "Label", excluding lowest character

Table "Split" Data by Trimmed Label....

Re: Stack Columns with multiple names

hogi — Wed, 20 Mar 2024 21:46:18 GMT

wow, nice link between multiple-series stack and stack + split !