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

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

shlomi_bakish — Sat, 10 Jun 2023 23:53:28 GMT

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?

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

txnelson — Tue, 23 Aug 2022 06:35:51 GMT

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

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

jthi — Tue, 23 Aug 2022 06:39:20 GMT

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.

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

shlomi_bakish — Tue, 23 Aug 2022 08:55:21 GMT

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.

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

txnelson — Tue, 23 Aug 2022 09:54:37 GMT

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

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

shlomi_bakish — Tue, 23 Aug 2022 10:35:42 GMT

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?

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

Georg — Tue, 23 Aug 2022 10:40:04 GMT

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.

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

Georg — Tue, 23 Aug 2022 10:58:53 GMT

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

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

shlomi_bakish — Tue, 23 Aug 2022 11:07:12 GMT

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?

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

shlomi_bakish — Tue, 23 Aug 2022 11:44:24 GMT

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?

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

txnelson — Tue, 23 Aug 2022 12:43:40 GMT

The R followed by an all numeric value is being interpreted by JMP as a currency. The South African Rand's notation is in the form: R123 or R55.67 etc. So JMP is interpreting it as numeric values. You could add a check to the code to check for this exception.

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

shlomi_bakish — Tue, 23 Aug 2022 13:54:01 GMT

wow I didn't see this coming :) thanks a lot for your help!

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

Craige_Hales — Tue, 27 Sep 2022 01:13:52 GMT

JMP 17 will have an optional argument for num("R001", <<Restrict) to prevent this behavior.