cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
View Original Published Thread

merging values from 2 columns into one (mixed numeric and character values)

shlomi_bakish
Level II

Hi

 

I have 2 columns, one containing numeric values, and one containing strings.

I want to create a third column that takes the numeric value if the string value is 'parametric', and the string value if it's not.

 

I tried this, but I'm getting an error "Column TEST_RESULT requires numeric values", although it's defined as Character type column.

I'm using JMP15 if it matters.

 

col_test_result =dt <<new column ("TEST_RESULT",Character);

Column(dt_subset,"TEST_RESULT")<<set formula(if(:COMMENTS=="Parametric",:NUMERIC_RESULT,:COMMENTS));

Later on I want to split this column so the output table will have either numeric or character type data, will JMP be able to set it to numeric based
on the contents although the source column was character?

 

 

12 REPLIES 12
txnelson
Super User


Re: merging values from 2 columns into one (mixed numeric and character values)

You need to use the Char() function to convert the numeric data to character

col_test_result = dt << New Column( "TEST_RESULT", Character );

dt_subset:TEST_RESULT << set formula( If( :COMMENTS == "Parametric", Char( :NUMERIC_RESULT ), :COMMENTS ) );
Jim
jthi
Super User


Re: merging values from 2 columns into one (mixed numeric and character values)

You cannot have mixed values in one column. You could add Char() around your :NUMERIC_RESULT if those are numeric values

 

Column(dt_subset, "TEST_RESULT") << set formula(If(:COMMENTS == "Parametric", Char(:NUMERIC_RESULT), :COMMENTS));

And when you split the column just set the data type to what you want. Do note that if you have character values in numeric column, those will be changed to missing.

 

-Jarmo
shlomi_bakish
Level II


Re: merging values from 2 columns into one (mixed numeric and character values)

Thanks ! adding the 'char()' solved the first issue.

But now - I have 2 other issues, after splitting the merged column.
I'm splitting it by another column, so the resulting columns all have one type of data (string or numeric), there are ~150 different columns.

The problems :

 

1. All the split columns are still with the original formula from merged column. How do I make the source column a value (like in excel - paste speical, values).

2. All the split columns are generated as character type, and that's not what I wanted, I want them to got automatic type based on their content, just like
when I'm opening a csv file - JMP sets the type automatically, so if all values in a column are numeric, it will get numeric type.

 

txnelson
Super User


Re: merging values from 2 columns into one (mixed numeric and character values)

Here is a little piece of code that will go through a data table and convert any character column that is made up of all valid numeric values, to a numeric data type;

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

For Each( {value, index}, dt << get column names( string ),
	If( Column( value ) << get data type == "Character",
		vals = Column( value ) << get values;
		nonMissing = Col Number( As Column( value ) );
		Column( value ) << set data type( numeric ) << modeling type( continuous );
		If( nonMissing != Col Number( As Column( value ) ),
			Column( value ) << set data type( character ) << set values( vals )
		);
	)
);
Jim
shlomi_bakish
Level II


Re: merging values from 2 columns into one (mixed numeric and character values)

Thanks ! that looks exactly what I need, but unfortunately it's not working.

I tried to debug - the first line is working - getting list of columns names.

The second one get the below error. any idea?

shlomi_bakish_0-1661250829655.png

 

Georg
Level VII


Re: merging values from 2 columns into one (mixed numeric and character values)

Just as a comment:

It is not a restriction, but it is the basic mindset of JMP that each column has a defined datatype, that you can actively set according to your needs.

The datatype, once set properly, makes following analyses so easy to perform.

Georg
Georg
Level VII


Re: merging values from 2 columns into one (mixed numeric and character values)

I think you're using < Version 16, so you have to use std "For" syntax, see

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Big Class.jmp" );

// JMP16 "For Each"
dt << get column names( string );
For Each( {value, index}, dt << get column names( string ), Print( value ) );

// JMP <16 "For"
col_lst = dt << get column names( string );
For( i = 1, i <= N Items( col_lst ), i++,
	Print( col_lst[i] )
);

  

Georg
shlomi_bakish
Level II


Re: merging values from 2 columns into one (mixed numeric and character values)

 

 

Thanks it's working now !

now just one thing - how do I get rid of the original formula? I just want the resulting value of the formula, is it possible?

 

shlomi_bakish
Level II


Re: merging values from 2 columns into one (mixed numeric and character values)

 

well sort of working.. I found a problem. There was a column with 'char' data type that had a fixed string content : "R001"

after running the script it changes to numeric type, with value of '1'. Why is this hapenning?