677359_en-US

liliconcom

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

Table attached, just in case.

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

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

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

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

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

 

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

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.

Basic Data Analysis and ModelingContent OrganizationData Blending and CleanupRe: I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

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

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

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

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

I appreciate seeing another technique.