cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
dwaterson
Level III

In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

1 ACCEPTED SOLUTION

Accepted Solutions
julian
Community Manager Community Manager

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

Byron's suggestion will work perfectly so long as you don't have any characters stored in any of the rows. If you do, those entries will be deleted upon changing the column to numeric.

Since you have those leading zeros in your data that column must already be set as character, I'm assuming because you are storing letters as well as numbers. To eliminate those zeros in place you could use regular expressions in Edit > Search > Find.  Regular expression are quite powerful but aren't so friendly to look at. For your situation, the following regular expression will search out leading zeros but will not find zeros that are inside the string:

(?<!\d)0+(?=\d+)

To use this, paste the regular expression in the "Find" section, leave the replace section blank, and check the box for "use regular expressions." I would also check the box for "restrict to selected columns (and make sure you have selected your column of interest). With everything set up, you can now click "replace all" and JMP will find those leading zeros and replace them with blanks. I've also attached a screenshot of this setup below. 

7054_Screen Shot 2014-07-29 at 7.08.36 AM.png

I hope this helps!

Julian

View solution in original post

8 REPLIES 8
Byron_JMP
Staff

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

By changing the column data type to numeric and the modeling type to continuous, the leading zeros will drop off. 

JMP Systems Engineer, Health and Life Sciences (Pharma)
julian
Community Manager Community Manager

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

Byron's suggestion will work perfectly so long as you don't have any characters stored in any of the rows. If you do, those entries will be deleted upon changing the column to numeric.

Since you have those leading zeros in your data that column must already be set as character, I'm assuming because you are storing letters as well as numbers. To eliminate those zeros in place you could use regular expressions in Edit > Search > Find.  Regular expression are quite powerful but aren't so friendly to look at. For your situation, the following regular expression will search out leading zeros but will not find zeros that are inside the string:

(?<!\d)0+(?=\d+)

To use this, paste the regular expression in the "Find" section, leave the replace section blank, and check the box for "use regular expressions." I would also check the box for "restrict to selected columns (and make sure you have selected your column of interest). With everything set up, you can now click "replace all" and JMP will find those leading zeros and replace them with blanks. I've also attached a screenshot of this setup below. 

7054_Screen Shot 2014-07-29 at 7.08.36 AM.png

I hope this helps!

Julian

jarowe
Level II

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

I'm really new to jmp for please excuse my ignorance.

Is there a way to change all my data to have zeros in front or to change the listing so that it is in actual numeric order instead of 1, 11, 12, 13, 2, 3, 4, etc.?

julian
Community Manager Community Manager

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

Hi Jarowe,

If your column is a character column, JMP will sort the data as you said, 1, 11, 12, 13, 2, 3 etc.  If the column is numeric, JMP will sort the column with natural ordering, 1, 2, 3, 4 etc.  Does your column contain characters? If so, changing to numeric won't be an option, so we'll need to do something more creative. What I would do is prepend a number of zeros to the data, and then trim the result to constrain the number of total characters. For instance, if you have the following values, 1a, 2e, 13h, 33f, 201v,  what we want is to prepend zeros such that we end up with 001a, 002e, 013h, 033f, 201v. Notice this means that we are prepending a different number of zeros (sometimes 2, sometimes 1, and sometimes none). We could handle this through some conditional logic, but it will be easier to prepend the maximum number of zeros we will need (2) to all the data, and then use a character function called "right" to take just the right most characters from each row. For example, imagine we prepend 2 zeros to every value (which yields 001a, 002e, 0013h, 0033f, 00201v) and then we apply another formula that takes only the right most 4 characters from each (which yields what we want, 001a, 002e, 013h, 033f, 201v). We can do this all through formulas in JMP with just the mouse (and a few key presses).

1. Make a new column by going to Cols menu > New Column.

7081_Screen Shot 2014-07-30 at 10.36.24 AM.png

2. For the data type, select "Character." This is necessary if you wish to have leading zeros and characters (numeric columns will ignore leading zeros as they have no meaning for actual numbers, and can't store letters). Once you do this, click OK

3. Select your new column, then go to Cols menu > Formula

4. This is the formula editor. Here, you can define a formula that will apply to the entire column. What we want is a formula that will prepend a zero (or any characters you want) to your existing values in the original column. We can accomplish this with a character function called "concatenate." Here are the steps to set up that formula:

    a. In the upper right section (the function group list) select "Character" and then select "Concat"

7082_Screen Shot 2014-07-30 at 10.36.56 AM.png

    b.  In the formula editor, you will now see the following:

7083_Screen Shot 2014-07-30 at 10.38.49 AM.png

      c. The two rectangles are called arguments of this function-- they need to be populated with either columns or text. In your case, you wish to add values to the start of another column, so first select the second box by clicking on it, and then select the original column in the columns list on the top left. Here is what you should have (but with your own column's name):

7084_Screen Shot 2014-07-30 at 10.40.54 AM.png

    d. In my case, the Random ID column is already set as a character column because it has letters. If your original column is numeric, you will need tell JMP to convert the numeric data into a string that you are prepending. To do this, select the "Character" function group again, and select "Char." This will update the formula and add in the instructions to JMP to convert the content of the column you selected to be a character.

7085_Screen Shot 2014-07-30 at 10.44.07 AM.png

    e. Next we need to decide what we are prepending. Double click the first rectangle, which will open the editor for that field. Here we want to prepend the MOST number of zeros we will need. For the example I used above, we would prepend with two zeros. Enter (with the quotes) "00" and then hit the enter or return key.  Here is what that would look like before we click enter:

7086_Screen Shot 2014-07-30 at 10.45.39 AM.png

    f. Once you click return JMP will close the editor for that field. Now, if we were to apply this formula we would end up with two zeros in front of every value. But, the values like "201v" don't need zeros, and if we prepend zeros to those values our sorting won't work. So, we're going to apply one more function to this formula to keep only the rightmost 4 characters. To do this, select the entire formula by clicking the outermost box of the formula we have written. When you do this the box will be highlighted like below:

7087_Screen Shot 2014-07-30 at 11.05.10 AM.png

    g. We will now apply one more character function from the character function group. Select the character group, and select "right"  This will apply the right() function to this entire formula, and will give us a space to enter the number of characters we wish to extract from the right. In this case, I will enter 4 by double clicking the "n" field and entering 4 (without any quotes). When I click return, this is what I have:

7088_Screen Shot 2014-07-30 at 11.06.40 AM.png

    h. Your formula is ready to be run. Click the OK button, and you should see your new column fill with all your original values but prepended with as many zeros as are necessary.  In your case you will probably need to modify the number of zeros you add, and the right trim number. To determine this, figure out the number of characters for your highest value (that will be the right trim number), and then determine how many zeros are necessary to add for your smallest value (that will be the number of zeros you should prepend to everything).

I hope this helps!

Julian

jarowe
Level II

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

Hi Julian,

The column only contains numbers but I’d like to be able to change it to a continuous column instead of just nominal or ordinal so that I can do specific analyses that require the data to be in a continuous format.

Respectfully,

Jennifer

julian
Community Manager Community Manager

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

Hi Jennifer,

Great! If you only have numbers this is made much easier, change the column data type to numeric and that will take care of the sorting for you. To change the column data type, select the column, then go to Cols menu > Column Info. There you an change the data type to numeric, and the modeling type to continuous.   Now, when you sort by that column (either by right clicking the column in the data table > Sort, or Tables Menu > sort)  JMP will order those numbers naturally.

Julian

jarowe
Level II

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

Nevermind, I think I figured it out. I had to change it from character to numeric, which is what I believe you were trying to tell me. Sorry. Thanks again for all your help!!

Respectfully,

Jennifer

julian
Community Manager Community Manager

Re: In a column I have some data with leading zeros and some without. Is it possible to remove the leading zeros without creating a new column?

Hi Jennifer,

I'm glad you figured it out, and I'm very happy to help! Never hesitate to post questions if you get stuck -- more likely than not there are many people who have a similar question!

I hope you enjoy using JMP!

Julian