461792_en-US

liliconcom

How to convert Column Data type from Character into Numeric wihtout losing Numeric values in the column

Dears,

Started with JMP few weeks ago !! It's a naive question.

I am trying to convert a column Data type from Character to Numeric, however i am losing the all the Numeric values from the column while converting the data type to Numeric.

Please find the attached screenshots for the same.

 

How to retain the numeric values from a column when i change it's data type from Character into Numeric??

Tried by replacing the characters using following commands,

 

values1 = dt:RequiredColumn<

Regex( :RequiredColumn, "[^0-9]", "", GLOBALREPLACE );

 

with this all the numeric values are also getting replaced.

 

Simply i would like to retain the Numeric values from the column (originally in Character data type). Tried to search for this problem in our community couldn't achieve any relevant information about it.

 

Picture01 - OriginalColumnFormat  - Screenshot of Column's original data type

Picture02 - ColumnafterChangingDatatype- Screenshot of Column's after changing the data type to Numeric.

 

Thanks in Advance...

Data TableScriptingWindowsRe: How to convert Column Data type from Character into Numeric wihtout losing Numeric values in the column

There are likely multiple ways to accomplish what you want and I'll bet someone (e.g., Jim) will have a script to make it more efficient, but here is a potential work flow:

BTW, your jpg's don't allow us to see what character columns you want to change look like.  If there are any characters that are not numeric, these need to be dealt with before you change data type.

1. Right click on the column and use Recode.  Remove all non-numeric values.

2. Then, right click and choose Column info. (like the jpg's). change Data Type from character to numeric and modeling type to continuous.

Re: How to convert Column Data type from Character into Numeric wihtout losing Numeric values in the column

Dear statman,

 

Thank you for your timely reply.

I did try your suggestion by removing non-numeric values using Recode option from the column and tried to convert the columns data type from character to Numeric. However still all the values are disappearing as before when the data type is changed to Numeric.

 

Please find the attached screenshots for the same.

In recoding option, JMP was able to differentiate Numeric values and string characters.

Will post the result of Mr. JIM's suggestion soon.

 

Thank you..

Re: How to convert Column Data type from Character into Numeric wihtout losing Numeric values in the column

Converting the table you have is "easy", but in general this sort of conversion isn't. There are so many different values you will have to take into account. Then there are is an issue/feature with how JMP's Num function works. Below is one idea, but this will still leave out many different "numeric" formats, like durations, dates, times, percentages, thousand separators and so on. Also it only removes some of the possible characters which are not related to numbers

Names Default To Here(1);

dt = Current Data Table();

//remove alphabetical characters, should also remove some special characters
regex_one = "[a-zA-Z\s]";
dt << New Column("no_alph", Character, Nominal, << Set Each Value(
 Regex(:MeasuredValues, regex_one, "", GLOBALREPLACE); 
));

regex_two = "^-?[0-9]+\.?[0-9]*$";
dt << New Column("nums", Numeric, Nominal, << Set Each Value(
 Num(Regex(:no_alph, regex_two))
));

 

 

Re: How to convert Column Data type from Character into Numeric wihtout losing Numeric values in the column

Thanks jthi !!

That did the job...

Re: How to convert Column Data type from Character into Numeric wihtout losing Numeric values in the column

It appears that JMP has a bug in the Num() function.  Character strings of the pattern

     "n.n.n"

should always return a JMP missing value, however in many cases, it returns a valid numeric value

Num( "9.4.333" )

returns

-49554633600

I can not find any usage note on this issue in the JMP Documentation(a non exhaustive search).  I am putting in a bug report to the JMP support team.