254880_en-US

liliconcom

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: <BR />ID,Name,Country,Column1_Value,Column2_Value_More,Percent_Name_Count<BR />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<BR /><BR />Keep: <BR />ID,Name,Country,Column1_Value,Column2_Value_More,Percent_Name_Count<BR />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.

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.

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.

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);

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.

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);

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

 

 

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]), "_|" );
 );
);