Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
UberBock
Level III

Extracting from a column that has comma delimited data

I have a column fro a query that has data within that is comma delimited.

 

Reads

1,2,3,2,4,3,4,5,2,1,3,4,5,2,1,3,4,2,1,2,3,4,2,1,2,4,2,3,4,5

 

I would like to have a script that parses out each value into a new column with the column title of D01, D02, D03 etc....

 

I have used an addin for Text to Cols to work with this column but it would great to have a script as part of post-Query script that makes those columns

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
cwillden
Super User

Re: Extracting from a column that has comma delimited data

There actually is a Text to Columns() function.  Here's a way to do it with a table where I start with 2 columns, Column 1 and Column 2.  Because Text to Columns() will insert the new columns directly after the column being split, you need to determine the position of the column so you can easily find where the new columns are in the table to rename them.

dt = Current Data Table();
col = "Column 1"; //name of column that needs to be split out

//Need to determine where new columns will go:
colnames = dt << Get Column Names(string);
which_col = Loc(colnames, col)[1]; //get position of column being split

//Get number of columns before split
n_col_pre = N Col(dt);

//Split the column by delimiter
dt << Text to Columns( delimiter(","), 
	columns(Column(dt, col))
);

//Determine number of new columns
new_cols = N Col(dt) - n_col_pre;

//Rename New Columns
for(i = 1, i <= new_cols, i++,
	col_pos = which_col + i; //get position of current column
	Column(dt, col_pos) << Set Name("D"||if(i < 10, "0","")||char(i));
	Column(dt, col_pos) << Set Data Type(Numeric); //makes each column numeric, not sure if you needed that too
);

Before:

cwillden_0-1583884747641.png

After:

cwillden_1-1583884787138.png

 

-- Cameron Willden

View solution in original post

3 REPLIES 3
Highlighted
cwillden
Super User

Re: Extracting from a column that has comma delimited data

There actually is a Text to Columns() function.  Here's a way to do it with a table where I start with 2 columns, Column 1 and Column 2.  Because Text to Columns() will insert the new columns directly after the column being split, you need to determine the position of the column so you can easily find where the new columns are in the table to rename them.

dt = Current Data Table();
col = "Column 1"; //name of column that needs to be split out

//Need to determine where new columns will go:
colnames = dt << Get Column Names(string);
which_col = Loc(colnames, col)[1]; //get position of column being split

//Get number of columns before split
n_col_pre = N Col(dt);

//Split the column by delimiter
dt << Text to Columns( delimiter(","), 
	columns(Column(dt, col))
);

//Determine number of new columns
new_cols = N Col(dt) - n_col_pre;

//Rename New Columns
for(i = 1, i <= new_cols, i++,
	col_pos = which_col + i; //get position of current column
	Column(dt, col_pos) << Set Name("D"||if(i < 10, "0","")||char(i));
	Column(dt, col_pos) << Set Data Type(Numeric); //makes each column numeric, not sure if you needed that too
);

Before:

cwillden_0-1583884747641.png

After:

cwillden_1-1583884787138.png

 

-- Cameron Willden

View solution in original post

Highlighted
UberBock
Level III

Re: Extracting from a column that has comma delimited data

It worked great!  Thank you.  Also thank you for the comments in the script they will help me understand it and learn from it.

 

Thanks again!

Highlighted
cwillden
Super User

Re: Extracting from a column that has comma delimited data

glad it worked well for you!
-- Cameron Willden
Article Labels

    There are no labels assigned to this post.