topic Re: How can I split text in a column into multiple columns? in Discussions

How can I split text in a column into multiple columns?

tpinck27 — Wed, 19 Oct 2016 03:50:00 GMT

Sometimes I have a data set with information in one column that I would like split across multiple columns. For example in image 1 below I have four identifiers mashed into one column that I would like split into four columns. I want the lot number (11) text identifier (DAT) sample number (001,002 etc) and file type (.tif) all split into individual columns (as shown in image 2). What is the best way to do this in formula editor or using JSL. I've attached a sample data file.

What I have:

Image 1

What I want to have:

Image 2

Re: How can I split text in a column into multiple columns?

Jeff_Perkinson — Fri, 28 Oct 2016 13:29:02 GMT

If you're using JMP 12 or later you can use Cols -> Utilities -> Text to Columns.

If you're using JMP 11 or earlier you can get the add-in, Text to Columns, Version 2, which does the same thing as the now, built-in function.

If you want to do this by hand, either with JSL or the Formula Editor, you'll find the Word() function handy. Trust me.

Re: How can I split text in a column into multiple columns?

tpinck27 — Mon, 22 Aug 2016 19:08:22 GMT

Perfect, this is exaclty what I was looking for, thanks Jeff.

Re: How can I split text in a column into multiple columns?

lalvar39 — Wed, 03 May 2017 06:46:42 GMT

How can this be written in code/JSL?

Re: How can I split text in a column into multiple columns?

txnelson — Wed, 03 May 2017 12:35:29 GMT

Names Default To Here( 1 );
dt = New Table( "sample",
	New Column( "column 0", character, values( {"11 DAT001.tif", "11 DAT002.tif", "11 DAT003.tif", "11 DAT004.tif"} ) )
);
// Pause so you can see it working
Wait( 2 );

// add some as an example of the elements using a formula
dt << New Column( "Column 1", formula( Num( Word( 1, :column 0 ) ) ) );
dt << New Column( "Column 2", character, formula( Substr( Word( 2, :column 0 ), 1, 3 ) ) );
dt << New Column( "Column 3", character, formula( Substr( Word( 2, :column 0 ), 6, 1 ) ) );

// Now do the last one, using JSL code
dt << New Column( "Column 4", character );
For( i = 1, i <= N Rows( dt ), i++,
	:Column 4[i] = Word( -1, :Column 0[i],".");
);

Re: How can I split text in a column into multiple columns?

Jeff_Perkinson — Wed, 03 May 2017 13:54:34 GMT

I've recently posted Text to Columns, Version 3 which adds support for calling Text to Columns from JSL.

Re: How can I split text in a column into multiple columns?

pmroz — Wed, 03 May 2017 13:45:40 GMT

The documentation for col << text to columns is en empty stub in the scripting index. I've tried various combinations but can't get it to work. Can someone from JMP let us know the proper syntax?

Re: How can I split text in a column into multiple columns?

ian_jmp — Wed, 03 May 2017 15:11:29 GMT

In my build of 13.1, I see:

Re: How can I split text in a column into multiple columns?

pmroz — Wed, 03 May 2017 18:42:14 GMT

Thanks Ian. I'm using JMP 13.1.0 and that page is blank.

So back to the most recent question here's how to call text to columns in JSL:

dt = New Table( "Text2Col Example", Add Rows( 4 ),
	New Column( "Col1", Character, "Nominal", 
		Set Values(
			{"11 DAT 001.tif", "11 DAT 002.tif", "11 DAT 003.tif", "11 DAT 004.tif"}
		)
	),
	New Column( "Col2", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [159.4308, 229.5031, 164.3414, 162.0241] )
	)
);

dt << text to columns(delimiter(" "), columns(:Col1));

Re: How can I split text in a column into multiple columns?

poulravn — Tue, 08 May 2018 09:43:53 GMT

Hi, I am running JMP 13 and have the Text to Columns add-in installed and can use it from the menus.

Now, I have a data table character column which represent spectrum data from an spectrophotometer. There are in total 138 substrings separated with commas. I want to code in JSL, using the add-in, the generation of 138 numerical columns each with one value in it. (Since the data is from a diode array, the wavelengfh separation is constant,so I do not have it in the data, and therefore do not need to convert that..)

I've read some of the examples on how to do this, but I fail to get the right syntax.

A little push of help would be appreciated: What is the syntax for generating 138 columns from a specific column using the add-in?

Thanks in advance

Poul R S

Re: How can I split text in a column into multiple columns?

txnelson — Tue, 08 May 2018 12:27:32 GMT

Here is the example from the Addin's documentation. Make sure you have installed version 3 of the Addin, and then run this example.

bc_party = Open( "$SAMPLE_DATA\Consumer Preferences.jmp" );

com.jmp.jperk:texttocolumns( Column( bc_party , "floss delimited"), ",", bc_party );

If it works, then using this code is how you incorporate it into your JSL

Re: How can I split text in a column into multiple columns?

poulravn — Tue, 08 May 2018 18:50:58 GMT

Hi again, thanks a lot. It works and my data gets split up. There are now 138 columns with names SPDATA 1 - SPDATA 138.

I know it is a little bit outside the thread theme and perhaps something I should be able to, but:

How can I in a compact JSL statement set all column properties to numeric and continous?

And secondly, how can I create a transformed data table, where the spectrumdata is ONE column with multilpes of 138 rows according to some categorial data also available (datetime values that have the same value for all 138 datapoints).

Sincerely Poul R S

Re: How can I split text in a column into multiple columns?

txnelson — Tue, 08 May 2018 19:08:57 GMT

Your compact code for setting the Data Type and Modeling Type for all columns would be

dt = current data table();
for( i = 1, i <= NCols( dt ), i++,
     column( dt, i ) << data type( "numeric" ) << modeling type( "continuous" );
);

Concerning your transpose, you should be able to get what you want using the Stack Platform

Tables==>Stack

Re: How can I split text in a column into multiple columns?

Mark_Bailey — Tue, 08 May 2018 20:12:46 GMT

Jim answered your question, but you don't need a script.

Select all the columns to be changed.
Select Cols > Standardize Attributes.
Click Attributes and select Data Type.
Click Attributes and select Modeling Type.
Change both of these attributes the way you want them.
Click OK.

Scripting is great but JMP is optimized for interaction.

Re: How can I split text in a column into multiple columns?

ENTHU — Wed, 24 Oct 2018 00:57:49 GMT

Hi,

Just working on an extension of test to columns feature. I have a string ACE_100_bb_29_icfg2 in a column named item code. Is there a way to separate this text by _ and then only have first 3 characters(ABC) in the column item code.

The way I am doing it now is - deleting all other columns that is produced by this function that is item code 1 ,2 and so far and renaming the column with ABC as item code. Was wondering if there is an efficient way to do this.

Thanks

Re: How can I split text in a column into multiple columns?

txnelson — Wed, 24 Oct 2018 01:58:21 GMT

Look into the "Text to Columns" function and the Word() function to accomplish what you are asking about

Re: How can I split text in a column into multiple columns?

bjbreitling — Thu, 09 Dec 2021 19:30:42 GMT

Hi Jeff,

This "split" and "stack" function in JMP is really nice. I don't suppose you know the SQL equivalent or a way to do that in SQL? If that's not advice you give that's fine. Just save me looking at SQL boards.

Re: How can I split text in a column into multiple columns?

pmroz — Thu, 09 Dec 2021 19:36:20 GMT

Can you supply some example data with before and after looks for splitting and stacking?