nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

row data split but don't split column header

sam_t

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.

Accepted answers

txnelson

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

txnelson

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

All comments

sam_t

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.

txnelson

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

sam_t

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.

txnelson

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

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

sam_t

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