topic How to split a column cells into different columns in Discussions

How to split a column cells into different columns

Wed, 21 Apr 2010 21:23:12 GMT

Not sure how I can split a column of cells into different columns. For example I have this ONE column with both First and Last names as one cell

Name
1 Lee, Bob
2 Miller, Mike
3 Smith, Jack

Not split this one column into Two columns, one of First, and second column of Last

Last First
1 Lee Bob
2 Miller Mike
3 Smith Jack

Re: How to split a column cells into different columns

Anonymous — Wed, 21 Apr 2010 22:46:13 GMT

There are a couple ways to tackle this. The easiest way is to use the "Word" function.
Assuming your full names are in Column "Name":

Last Name: Word( 1, :Name, ", " )
First Name: Word( 2, :Name, ", " )

The harder way is to do it with the "munger" function :)

Re: How to split a column cells into different columns

Anonymous — Wed, 21 Apr 2010 22:49:50 GMT

Since I'm bored:

First Name: Munger( :Name, Munger( :Name, 1, ", " ) + 2, -1 )
Last Name: Munger( :Name, 0, Munger( :Name, 1, ", " ) - 1 )

Re: How to split a column cells into different columns

Wed, 21 Apr 2010 23:53:06 GMT

Thanks. I am a newbie to JMP. Actually my situation is a little more complicated. Each of my row has a column of data that contains varying # of data which I want to split out. Pls see below

Name Classes
1 Jack a, b, f, h
2 Bill b
3 Steve e, r, t
4 Mary a, b, c, d, e

Since the # of classes each person has taken are varying, what is the correct way to break the classes column out into something like below

Name Classes
1 Jack

Re: How to split a column cells into different columns

Wed, 21 Apr 2010 23:56:16 GMT

Thanks. I am a newbie to JMP. Actually my situation is a little more complicated. Each of my row has a column of data that contains varying # of data which I want to split out. Pls see below. The Classes column is one column with "a,b,f" being one character string

Name Classes
1 Jack a, b, f,
2 Bill b
3 Steve e, r, t
4 Mary a, b, c, d

Since the # of classes each person has taken are varying, what is the correct way to break the classes column out into something like below

Name Classes
1 Jack a
2 Jack b
3 Jack f
4 Bill b
5 Steve e
6 (and so on).

Appreciate any insight. Thanks

Re: How to split a column cells into different columns

ms — Thu, 22 Apr 2010 11:00:37 GMT

I suggest you export your file as tab-delimited text and then reimport (select "Open" from File menu and "Open as: Data (using preview)") using comma and spaces as field delimiters.
Then you use "Stack" from the tables menu and choose to stack all columns in your imported data table except the name column (make sure "drop non-stacked columns" is un-checked).

This would result in the expanded table you wanted, except for a number of empty rows. Those can easily be deleted after sorting.

Re: How to split a column cells into different columns

pmroz — Fri, 26 Oct 2018 19:34:59 GMT

I'm doing something very similar, where several columns have lists of varying lengths. Here's a solution adapted from my code:

current_dataset = current data table();
dataset_name = current_dataset << Get Name;

name_list = {};
class_list = {};

nR = NRow(current_dataset);

For (i = 1, i <= nR, i++,

split_classes = Words(Column("Classes"), ",");
nclasses = NItems(split_classes);

/* Add the parsed classes to the longer class list */
insertinto(class_list, split_classes);

/ Add Name to its list multiple times (same # as # of classes) */
For (k = 1, k <= nclasses, k++,
insertinto(name_list, (current_dataset:Name));
)
);

/* OK now we've created lists in memory containing Name and class data. Now
convert these to a dataset */
class_dataset = New Table(dataset_name || " (Classes)");
class_dataset << Minimize Window;

class_dataset << New Column("Name", Character);
class_dataset << New Column("Class", Character);

/* Add lists to their respective columns */
class_dataset:Name << values(name_list);
class_dataset:Class << values(class_list);

Re: How to split a column cells into different columns

pmroz — Fri, 23 Apr 2010 13:24:13 GMT

My posting got chopped off for some reason. I'm working with tech support to resolve the issue.

Re: How to split a column cells into different columns

pmroz — Fri, 26 Oct 2018 19:35:58 GMT

OK I figured out how to post. Here's the code:

current_dataset = current data table();
dataset_name = current_dataset << Get Name;

name_list = {};
class_list = {};

nR = NRow(current_dataset);

For (i = 1, i <= nR, i++,

// Parse the list of Classes using , as the delimiter. Take out extra spaces
split_classes = Words(substitute(Column("Classes")[i], ", ", ","), ",");
nclasses = NItems(split_classes);

// Add the parsed classes to the longer class list
insertinto(class_list, split_classes);

// Add Name to its list multiple times (same # as # of classes)
For (k = 1, k <= nclasses, k++,
insertinto(name_list, (current_dataset:Name)[i]);
)
);

// OK now we've created lists in memory containing Name and class data. Now
// convert these to a dataset
class_dataset = New Table(dataset_name || " (Classes)");
//class_dataset << Minimize Window;

class_dataset << New Column("Name", Character);
class_dataset << New Column("Class", Character);

// Add lists to their respective columns
class_dataset:Name << values(name_list);
class_dataset:Class << values(class_list);

Re: How to split a column cells into different columns

Mon, 03 May 2010 17:10:02 GMT

Apology for being late in thanking everyone. I ended up doing something similar to the first suggestion of using excel to break up the contents on one column and then import everything into JMP and then used stack function. I am not good at writing formulas or scripts, so good thing to learn from the more knowledgeable. Thanks again.

Re: How to split a column cells into different columns

laura_archer — Mon, 16 Oct 2017 17:38:36 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, as described by thechadd

Last Name: Word( 1, :Name, ", " )
First Name: Word( 2, :Name, ", " )

Re: How to split a column cells into different columns

ENTHU — Fri, 26 Oct 2018 17:19:52 GMT

Looking for an extension of Text to Columns/Word function.I have a path in a column -

Z:\abc\def\foldername\filefolder\filename.

I need to extract 3 things from this path -

1.foldername

2.Path until file folder

3.file name

I am basically getting this data from data base.Need to post process it and fill an excel template.

I tried text to columns;going through multiple iterations will get me there but its very inefficient.

Looking for recommendation.

Thanks

Re: How to split a column cells into different columns

txnelson — Fri, 26 Oct 2018 17:36:35 GMT

Take a look at the Word() function and the Substr() function. And you could also use a regular expression with Regex() function