cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
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
cwillden
Super User (Alumni)

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
cwillden
Super User (Alumni)

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
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!

cwillden
Super User (Alumni)

Re: Extracting from a column that has comma delimited data

glad it worked well for you!
-- Cameron Willden