topic How to copy and paste data cells from different ID cells? in Discussions

How to copy and paste data cells from different ID cells?

Reinaldo — Tue, 10 Apr 2018 14:06:05 GMT

Hi,

I have an example of data table enclosed in this post. I need to copy the information from ProTest cells from Phase B to Phase A in the same column. However, first I need to match the Test_IDs following the rule:

Phase A <-> Phase B

1 11

3 13

5 15

7 17

9 19

I have different numbers of participants because of missing data.

How can I do it, please?

Many thanks!

Re: How to copy and paste data cells from different ID cells?

gzmorgan0 — Wed, 11 Apr 2018 12:08:54 GMT

Even though your table is sorted and nice, this script assumes nothing, even allows the possibility that there is no corresponding ProTest.

Names Default to Here(1);
dt = Data Table("example2");

For(i=1, i<=nrow(dt), i++,
 if (:Phase[i] =="A",
 	uid = :Subject_ID[i];
 	tid = :Test_ID[i] +10;
    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid);
    if(nrow(xx)==1,
      :ProTest[i] = :ProTest[xx[1]]
    );
 ); //end if Phase
); // end For i

Re: How to copy and paste data cells from different ID cells?

Reinaldo — Wed, 11 Apr 2018 16:41:51 GMT

Brilliant, @gzmorgan0! :)

I have two questions:

1. In case I would like to add more "filters" or "By", should I create more variables (e.g., uid, tid) and insert them into the function "get rows where ( )", adding & "Column name == 'new variable'"?

2. If the Test_id "cannot" be defined as a logical sequence such as "sum 10" please do you think it is possible to create, for example, before (or inside) "For looping statement" a matrix which associates each Test_id from Phase A to Phase B? In this way, the user could define any association between id's in both phases.

I would like to thank you for your help!

Best!

PS: It is enclosed in this post a suggestion for following association and I added your script into there:

Phase A Phase B

1 20

2 22

5 55

8 18

9 9

Re: How to copy and paste data cells from different ID cells?

gzmorgan0 — Wed, 11 Apr 2018 19:37:02 GMT

You should look into JMP's associative array. Think of an associative array as a keyed list. You define the key and the associated values. Keys can be strings, expressions, lists, almost anything but numeric decimal values, integers are okay. See the modified script below. In other scripting languages, an associative array is called a dictionary. I use it often.

The answer is yes to your other question, add more filter variables.

Good luck.

Names Default to Here(1);
dt = Data Table("example2_modified");

decode_aa = Associative Array({1,2,5,8,9}, {20,22,55,18,9});

For(i=1, i<=nrow(dt), i++,
 if (:Phase[i] =="A",
 	uid = :Subject_ID[i];
 	tid = decode_aa[ :Test_ID[i] ];
    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid);
    if(nrow(xx)==1,
      :ProTest[i] = :ProTest[xx[1]]
    );
 ); //end if Phase
); // end For i

Re: How to copy and paste data cells from different ID cells?

Reinaldo — Wed, 11 Apr 2018 20:02:28 GMT

Hi @gzmorgan0

Thank you for your solution! Just one score couldn't be copied from Phase B to Phase A. It's Test_id = 9 (equal ids). I didn't understand why.

Your script is attached.

Many thanks!

Re: How to copy and paste data cells from different ID cells?

gzmorgan0 — Wed, 11 Apr 2018 20:09:25 GMT

oops! The script is always getting just the first value which is phase A. So modify the statement with an additional filter & :Phase=="B"

    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid & :Phase=="B");

Re: How to copy and paste data cells from different ID cells?

Reinaldo — Wed, 11 Apr 2018 20:24:58 GMT

Amazing, @gzmorgan0!! Worked!!

Where can I learn more about those commands such as get rows where () and nrow( ) and others to have a full control from rows and columns? Does the nrow( ) accept an integer inside it in case I wish to limit JMP scrolls the vector?

I will try to adapt to my problem... _/\_

Many thanks!!

Re: How to copy and paste data cells from different ID cells?

gzmorgan0 — Wed, 11 Apr 2018 22:43:30 GMT

oops! The script is always getting just the first value which is phase A. So modify the statement with an additional filter & :Phase=="B"

    xx = dt << get rows where(:Subject_ID ==uid & :Test_ID == tid & :Phase=="B");