topic Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column in Discussions

I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

learning_JSL — Wed, 13 Sep 2023 19:08:40 GMT

Hi - I have a table with columns that are made up of lab results (numeric values) that were entered as a text string. They are text string because some have a "<" in front of the numeric value. I need to be able to perform numeric calculations on the values so I am trying to remove the "<" symbol and place it in a new column that is named "ORIGCOLNAME_QUALIFIER" (where ORIGCOLNAME is the name of the column from which the "<" symbols were removed).

Unfortunately, the below code is not working.

Names Default To Here( 1 );
dt = Current Data Table();

compList = {};
For( i = 1, i <= N Cols( dt ), i++,
colName = Column( dt, i ) << get name;
);

// Create the columns
For Each( {comp, i}, compList,
Eval(
Parse(
"dt << New Column( \!"" || compList[i] || "_QUALIFIER" ||
"\!",
character, nominal,
formula(
x=.; 

if (comp << contains ("<"), x = <);

I get the following message:

Any input would be most appreciated.

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

learning_JSL — Wed, 13 Sep 2023 14:13:29 GMT

Table attached, just in case.

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

jthi — Wed, 13 Sep 2023 14:22:26 GMT

This might give some ideas on what you could do

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(2),
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Set Values({"<1", "<3"})),
	New Column("Column 2", Character, "Nominal", Set Values({"<2", "<4"}))
);


col_names = dt << Get Column Names("String");

For Each({col_name}, col_names,
	dt << New Column(col_name ||"_Q", Numeric, Continuous, << Set Each Value(
		Num(Substitute(AsColumn(dt, col_name), "<", ""));
	));
);

Search Scripting Index and Scripting Guide for Substitute() to learn more about how it can be used

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

learning_JSL — Wed, 13 Sep 2023 19:11:28 GMT

Thanks jthi - The first part of your code creates a new 2-row table with pre-named columns.

Names Default To Here( 1 );
dt = New Table( "Untitled",
	Add Rows( 2 ),
	Compress File When Saved( 1 ),
	New Column( "Column 1", Character, "Nominal", Set Values( {"<1", "<3"} ) ),
	New Column( "Column 2", Character, "Nominal", Set Values( {"<2", "<4"} ) )
);

However, I am trying to keep my column names intact while adding a new column with name corresponding to each of my existing columns but with "_qualifier" on the end. (Ex: say my existing columns are chloride (character), sulfate (sulfate), ..., the new columns would be chloride (numeric), sulfate (numeric), chloride_qualifier (character), sulfate_qualifier (character), etc.

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

jthi — Wed, 13 Sep 2023 16:20:52 GMT

Just substitute that part with a reference to your table. You might also want to cleanup the column selection if you know which should be used (instead of just using << get column names("String") to get all columns).

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

learning_JSL — Wed, 13 Sep 2023 17:14:53 GMT

Unfortunately I'm not following how that code applies to my original question. I probably was not clear in my original post or am missing something (I'm a jsl novice). In any case, I'll keep trying and may need to re-frame my question. (By the way, I have a very large number of columns in play, and all but the datetime column need this treatment - far too many to define by hand.) Thanks again.

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

txnelson — Wed, 13 Sep 2023 19:13:11 GMT

First, when posting JSL to a Community Discussion page, please use the JSL icon at the top of the page, so the code properly shows up ion the page.

Below is a script that checks for < and for any Post Script values such as "J" or "UJ" etc, and moves the values to a new Qualifier column. It then converts the column from a character column to a numeric column

Names Default To Here( 1 );
dt = Current Data Table();

colNames = dt << get column names( string, character );

For Each( {col, i}, colNames,
	GTFound = "NO";
	postScriptFound = "NO";
	
	// Find rows that have < in them
	theRows = dt << get rows where( Contains( As Column( dt, col ), "<" ) );
	// If < is found then 
	//      1. Create a new column to hold the < in
	//      2. Loop through the rows with < and
	//         1. Add < to the appropriate row in the new Qualifier column
	//         2. Delete the < from the current column
	If( N Rows( theRows ) > 0,
		GTFound = "YES";
		dt << add Multiple Columns( col, 1, Character );
		Column( dt, N Cols( dt ) ) << set name( col || "_QUALIFIER" );
		dt << move selected column( after( As Column( col ) ) );
		For Each( {fixRow, k}, theRows,
			As Column( dt, col || "_QUALIFIER" )[fixRow] = "<";
			As Column( dt, col )[fixRow] = Substr( Column( col )[fixRow], 2 );
		);
	);
	
	// Find if there are any post script values on any of the rows
	theRows = dt << get rows where( Word( 2, As Column( dt, col ), " " ) != "" );
	
	// If found, then if a new column has not already been add, add the new Qualifer column
	If( N Rows( theRows ) > 0,
		postScriptFound = "YES";
		If( GTFound == "NO",
			dt << add Multiple Columns( col, 1, Character );
			Column( dt, N Cols( dt ) ) << set name( col || "_QUALIFIER" );
			dt << move selected column( after( As Column( col ) ) );
		);

		// Loop through the rows with post scripts and
		//     1. Add the post scripts to the Qualifer column
		//     2. Delete the post scripts from the current column
		For Each( {fixRow, k}, theRows,
			As Column( dt, col || "_QUALIFIER" )[fixRow] = Trim(
				As Column( dt, col || "_QUALIFIER" )[fixRow] || " " || Word( 2, As Column( dt, col )[fixRow], " " )
			);
			As Column( dt, col )[fixRow] = Word( 1, As Column( dt, col )[fixRow], " " );
		);
	);
	
	// If eiter < or Post Scripts were found, convert the column to numeric, continuous
	If( GTFound == "YES" | postScriptFound == "YES",
		Column( dt, col ) << data type( numeric ) << modeling type( continuous )
	);
);

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

learning_JSL — Wed, 13 Sep 2023 19:17:54 GMT

That worked perfectly. And the comment lines are most helpful as they will help me understand each step in the process. This augments what I can find in the help documentation as I learn how to actually apply these jsl fundamentals to my dataset. Thank you very much.

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

gzmorgan0 — Wed, 13 Sep 2023 20:50:46 GMT

I might not understand your need, and there are likely more efficient ways to do this, especially if your data has many rows, but here is a brute force method if this table looks like the result you want.

Names Default To Here(1);
id_colnames = {"well", "Sample Date"};

dt = Current Data Table();
nr =nrow(dt);

col_names = dt << Get Column Names("String");

For Each({col_name}, col_names,
   If( !Contains(id_colnames, col_name),
   	  dt << New Column(col_name||"_Q", Character, << Set Each Value(
   	  	If(Contains(AsColumn(dt,col_name), "<"), "<",""))
   	  );
   	  
      curr_col = column(dt,col_name);
      For(i=1, i<=nr, i++,
         curr_col[i]= Substitute( word(1, curr_col[i]) ,"<","");
       );
      curr_col << Set Data Type(Numeric) << Set Modeling Type ("Continuous");
   	); // end If()
 );  //end For Each()

Re: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

learning_JSL — Wed, 20 Sep 2023 19:16:56 GMT

Thanks GZMorngan0! Sorry I'm slow responding. I just checked back on the thread.

I appreciate seeing another technique.