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
sam_t
Level III

row data split but don't split column header

Hi,

 

I can open the csv file w/o any issue below. How do read in the first value of each column that has _ and | split. I want to keep the column header the same name so I can plot it later.

I tried to open w/ Data Preview to remove _ or | but it splits the column header as well.

 

Data: 
ID,Name,Country,Column1_Value,Column2_Value_More,Percent_Name_Count
125480,Jack,CA,0.310_0.310_0.310_0.310_0.310_0.310_0.310_0.310,0.610|0.610|0.610|0.610|0.610|0.610|0.610|0.610,0.7

Keep:
ID,Name,Country,Column1_Value,Column2_Value_More,Percent_Name_Count
125480,Jack,CA,0.310,0.610,0.7

 

 

 

csv_file = "C:/test3.csv";
Open(csv_file); 

 

 

test3.csv

ID,Name,Country,Column1_Value,Column2_Value_More,Percent_Name_Count
125480,Jack,CA,0.310_0.310_0.310_0.310_0.310_0.310_0.310_0.310,0.610|0.610|0.610|0.610|0.610|0.610|0.610|0.610,0.7
125481,Jane,US,0.300_0.300_0.300_0.300_0.300_0.300_0.300_0.300,0.600|0.600|0.600|0.600|0.600|0.600|0.600|0.600,0.69
125482,Joe,BZ,0.360_0.360_0.360_0.360_0.360_0.360_0.360_0.360,0.620|0.620|0.620|0.620|0.620|0.620|0.620|0.620,0.71
125483,Jim,FR,0.340_0.340_0.340_0.340_0.340_0.340_0.340_0.340,0.640|0.640|0.640|0.640|0.640|0.640|0.640|0.640,0.73
125484,Jerry,US,0.330_0.330_0.330_0.330_0.330_0.330_0.330_0.330,0.630|0.630|0.630|0.630|0.630|0.630|0.630|0.630,0.72
125485,John,CA,0.360_0.360_0.360_0.360_0.360_0.360_0.360_0.360,0.620|0.620|0.620|0.620|0.620|0.620|0.620|0.620,0.71

Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
txnelson
Super User

Re: row data split but don't split column header

This is a very simple manipulation using the Word() function.  JMP has some very powerful character manipulation capabilities.  See the Scripting Guide and the Scripting Index for documentation.word.PNG

names default to here(1);
dt=open("c:\test3.csv");

For Each Row(
	:Column1_Value = word(1, :Column1_Value, "_" );
	:Column2_Value_More = word(1, :Column2_Value_More, "|" );
);

dt:Column1_Value << data type(numeric);
dt:Column1_Value << modeling type(continuous);
dt:Column2_Value_More << data type(numeric);
dt:Column2_Value_More << modeling type(continuous);
Jim

View solution in original post

Highlighted
txnelson
Super User

Re: row data split but don't split column header

Very close, but if you look at the last code I sent, you will see that when referencing the column to be acted on, I placed an As Column() in front of it, so what you actually need is

names default to here(1);

csv_file = "C:/test3.csv";
CSV_data = Open( csv_file ); 

col = CSV_data << get column names( string );
 
nc = N Items( col );

// You don't want the first 3 columns processed,
// or the last one processed, so the index values
// to be used needs to be changed to handle this
For( i = 4, i <= nc - 1, i++, 
	For Each Row(
		as column(col[i]) = Word( 1, as column( col[i]), "_|" );
	);
);
Jim

View solution in original post

6 REPLIES 6
Highlighted
sam_t
Level III

Re: row data split but don't split column header

Not sure why the format got messed up. Wanted to show what is from the data and what I wan to keep.

 

Data: 
ID,Name,Country,Column1_Value,Column2_Value_More,Percent_Name_Count
125480,Jack,CA,0.310_0.310_0.310_0.310_0.310_0.310_0.310_0.310,0.610|0.610|0.610|0.610|0.610|0.610|0.610|0.610,0.7

Keep: 
ID,Name,Country,Column1_Value,Column2_Value_More,Percent_Name_Count
125480,Jack,CA,0.310,0.610,0.7

Thanks again.

Highlighted
txnelson
Super User

Re: row data split but don't split column header

This is a very simple manipulation using the Word() function.  JMP has some very powerful character manipulation capabilities.  See the Scripting Guide and the Scripting Index for documentation.word.PNG

names default to here(1);
dt=open("c:\test3.csv");

For Each Row(
	:Column1_Value = word(1, :Column1_Value, "_" );
	:Column2_Value_More = word(1, :Column2_Value_More, "|" );
);

dt:Column1_Value << data type(numeric);
dt:Column1_Value << modeling type(continuous);
dt:Column2_Value_More << data type(numeric);
dt:Column2_Value_More << modeling type(continuous);
Jim

View solution in original post

Highlighted
sam_t
Level III

Re: row data split but don't split column header

Jim,
 
Thank you for the fast respond. How can I avoid using hard code the specific column with word(). Can I use for loops in Cols to dynamic change of _ and |?
 
Thanks.
Highlighted
txnelson
Super User

Re: row data split but don't split column header

  1. Given the question you are asking, you need to spend some time learning about the basics of JSL.  Please take the time to read the Scripting Guide:   Help==>JMP Documentation Library==>Scripting Guide
  2. To answer your question, I have modified one of the Word() functions from my original script to use dynamic referencing
    names default to here(1);
    dt=open("c:\users\jim\documents\test3.csv");
    
    myWord = 1;
    myColumn = "Column1_Value";
    myDelimiter = "_";
    
    For Each Row(
    	:Column1_Value = word(myWord, as Column( myColumn ), myDelimiter );
    	:Column2_Value_More = word(1, :Column2_Value_More, "|" );
    );
    
    dt:Column1_Value << data type(numeric);
    dt:Column1_Value << modeling type(continuous);
    dt:Column2_Value_More << data type(numeric);
    dt:Column2_Value_More << modeling type(continuous);
Jim
Highlighted
sam_t
Level III

Re: row data split but don't split column header

Jim,

 

Sorry about the confusion as I'm new & learning JSL. 

If I have more column headers, I do not want to hard code the column header name but loop through to get the first value of split _ or |. I tried to attempt below and I know my code is broken.

 

 

csv_file = "C:/test3.csv";
CSV_data = Open( csv_file ); 

col = CSV_data << get column names( string );
nc = N Items( col );

For( i = 1, i <= nc, i++, 
	For Each Row(
		:col[i] = Word( 1, col[i], "_" );
		:col[i] = Word( 1, col[i], "|" );
	);
);

 

 

 

ID,Name,Country,Column1_Value,Column2_Value_More,Percent_Name_Count,COL1,COL2_ABC
125480,Jack,CA,0.310_0.310_0.310_0.310_0.310_0.310_0.310_0.310,0.610|0.610|0.610|0.610|0.610|0.610|0.610|0.610,0.7,0.7,0.9_0.9
125481,Jane,US,0.300_0.300_0.300_0.300_0.300_0.300_0.300_0.300,0.600|0.600|0.600|0.600|0.600|0.600|0.600|0.600,0.69,0.7,0.9_0.9
125482,Joe,BZ,0.360_0.360_0.360_0.360_0.360_0.360_0.360_0.360,0.620|0.620|0.620|0.620|0.620|0.620|0.620|0.620,0.71,0.7,0.9_0.9
125483,Jim,FR,0.340_0.340_0.340_0.340_0.340_0.340_0.340_0.340,0.640|0.640|0.640|0.640|0.640|0.640|0.640|0.640,0.73,0.7,0.9_0.9
125484,Jerry,US,0.330_0.330_0.330_0.330_0.330_0.330_0.330_0.330,0.630|0.630|0.630|0.630|0.630|0.630|0.630|0.630,0.72,0.7,0.9_0.9
125485,John,CA,0.360_0.360_0.360_0.360_0.360_0.360_0.360_0.360,0.620|0.620|0.620|0.620|0.620|0.620|0.620|0.620,0.71,0.7,0.9_0.9

 

 

Highlighted
txnelson
Super User

Re: row data split but don't split column header

Very close, but if you look at the last code I sent, you will see that when referencing the column to be acted on, I placed an As Column() in front of it, so what you actually need is

names default to here(1);

csv_file = "C:/test3.csv";
CSV_data = Open( csv_file ); 

col = CSV_data << get column names( string );
 
nc = N Items( col );

// You don't want the first 3 columns processed,
// or the last one processed, so the index values
// to be used needs to be changed to handle this
For( i = 4, i <= nc - 1, i++, 
	For Each Row(
		as column(col[i]) = Word( 1, as column( col[i]), "_|" );
	);
);
Jim

View solution in original post

Article Labels

    There are no labels assigned to this post.