topic Re: How do I script a recode into new column function? in Discussions

How do I script a recode into new column function?

Miki — Thu, 15 Feb 2018 08:58:24 GMT

Hi,

I've been searching for an answer for this for a few hours, but oddly can't find one.

I have data that represents the serial numbers of the answers a person has given each question. I need to recode the serial number into ranks, but keep the original answers. I need to do that for 90 questions (where each answer has a different serial number). What is the best way to do that? The only way I could think of was writing a script for each question, I just need to know how to write in the script to recode it into a new column. Any suggestions will be most appreciated.

Thanks in advance.

Re: How do I script a recode into new column function?

txnelson — Thu, 15 Feb 2018 13:56:39 GMT

Do the "Serial Numbers" for each question equate to the same "rank"? Is there a table that has the relationship between the "Serial Numbers" and the "ranks"? Or when you say "ranks", do you want to some how calcualte a statistical ranking?

Could you possibly provide a small sample of your initial data table and what you need as your final results?

Re: How do I script a recode into new column function?

Miki — Thu, 15 Feb 2018 14:18:51 GMT

Thanks for your reply.

Each question has 5 possible answers differing from question to question, and the ranking of the answers is always 1-5. But, like I said, due to the nature of the excell file, each answer only has a serial number and not its rank. What I need to do is to fit a rank for each answer. I have the rankings on the test itself (not on a table).

I really don't see a way to do this other than to script a recode command, and then manually write it in for each question. Am I wrong?

It is important though, that I don't lose the original data, so I need to recode it into new columns. How do I add that into the script?

The data looks something like that:

subject number	Question 1	Question 2
1	40042	33157
2	40041	33154
3	40043	33156
4	40042	33156
5	40044	33155
6	40045	33153

Re: How do I script a recode into new column function?

txnelson — Thu, 15 Feb 2018 14:59:34 GMT

You can easily do what you want, by just building a table where you match up the serial number with it's ranking

40041	4
40042	3
40043	2
40044	1
33151	4
33152	3
33153	2

Then, what you do is read in that table.

Next take your questions data table and stack all 90 questions using

Tables==>Stack

This will create a new table with a column called Label and one called Data. Label will contain the column name from the original table, and Data will contain the serial number.

Then what you do, is to use

Tables==>Join

to put the stacked question data together with the ranks data. If you indicate to the Join platform to match the Serial Number column from the ranks data table, with the Data column in the stacked table, JMP will create a new table with the data matched up.

Now all you have to do is to use

Tables==>Split

to transform the newly joined table back into the original shaped data table. You will end up with a data table with both the original serial number and the ranks data

Re: How do I script a recode into new column function?

Miki — Fri, 16 Feb 2018 11:07:40 GMT

Thank you so much!! That is really helpful. I'll try doing that.

In any case, do you happen to know how I insert the "new column" to a script of recode? This will be helpful with other data as well.

Re: How do I script a recode into new column function?

txnelson — Fri, 16 Feb 2018 11:16:57 GMT

There are 2 sources of documentation on JSL provided to you when JMP is installed, the Scripting Guide and the Scripting Index. If you are going to be using JSL, you need to become familiar with both of these.

If you go into the Scripting Index

Help==>Scripting Index

And then scroll down to

Data Table==>Data Table Cols

You will see both the definition of the New Column() function, and at lease 1 example for the function

Re: How do I script a recode into new column function?

Bill_Worley — Fri, 16 Feb 2018 11:49:04 GMT

A non-scripting option is also available.

Under the Columns > Recode you should see a "Done" button up in the right hand corner of the dialog box. That is essentially a drop down menu that lists 4 choices for saving your recoded data.

In Place, New Column, Formula Column and Script. Choosing the first option will overwrite your original data, the next two will create a new column and so on. The Formula Column is a nice option because it creates a formula for what you have recoded. This gives you a record of what and how you chose to recode your data.

HTH

Bill

Re: How do I script a recode into new column function?

Miki — Mon, 19 Feb 2018 20:39:25 GMT

Thank you again. You have helped me tremendously

Re: How do I script a recode into new column function?

Miki — Mon, 19 Feb 2018 20:41:18 GMT

Thank you. I'll look into that option as well. I know all the other three, but didn't know what the formula was for.