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
WernerL
Level III

How do I copy a column multiple times while shifting each time one row down?

Hi JMP Community, I am looking for a solution to facilate copying, in one go, a column a number times (in the example below I have chosen 6 times), but all data should move one row down with every new copied column that is created? Thank you for your help!

 

CopyColumnXtimesOneRowDown(WLI_22Nov2019).JPG

1 ACCEPTED SOLUTION

Accepted Solutions

Re: How do I copy a column multiple times while shifting each time one row down?

You already have two good solutions (and one marked as the solution), but there is another quick interactive way to do this too, that people sometimes forget. Select the original column and right-click on the column header choosing New Column Formula > Row > Lag Multiple. In the dialog put in the First Lag as 1 and the Last Lag as 6.

Dan Obermiller

View solution in original post

4 REPLIES 4
julian
Community Manager Community Manager

Re: How do I copy a column multiple times while shifting each time one row down?

Hi @WernerL ,

Below is some code to do that (writing either values, or column formulas), but might I ask, why do you need to create a table like this? If you need to perform some operations row-wise with a value in a previous row from a previous column, there are more efficient ways than explicitly creating those lag columns. 

 

This form writes values:

dt = Open("$SAMPLE_DATA\Big Class.jmp");
baseCol = Column("height");
nCopies = 6;

For( i = 1, i <= nCopies, i++, 
	spacerMat = J( i, 1, . );
	valuesMat = spacerMat |/ Eval( baseCol << Get Values );
	New Column( "Copy " || char(i), Numeric, "Continuous", Set Values( valuesMat ) );	
);

 

This form writes column formulas:

dt = Open("$SAMPLE_DATA\Big Class.jmp");
baseCol = Column("height");
nCopies = 6;

For( i = 1, i <= nCopies, i++,
	Eval(Substitute( Expr( New Column( "Copy " || Char( i ), Numeric, "Continuous", Formula( Lag( Subscript(xxCol,row()), i ) ) ) ), Expr( i ), i, expr(xxCol), baseCol ))
);

 

I hope this helps get you started!

@julian 

 

//Edit 11/24/19: made a correction to the column formula version to correctly substitute the column defined at the start of the script into the column formula

txnelson
Super User

Re: How do I copy a column multiple times while shifting each time one row down?

I agree with @julian in that I don't understand why you have such a need, but below are the steps that would allow you to do this interactively.

  1. Copy all of the values from the column 1 into the paste buffer.
  2. Right click on one of the rows in the rowstate column(the column with the row numbers) and select "Add Rows"
  3. In the Add Rows dialog box, select 6 rows to be added at the end.
  4. Go to the empty column header next to the header for the first column and right click and select "New Columns"
  5. In the New Columns dialog box, specify that you want to add 6 columns
  6. Once the columns are created, go to the second cell in the first new column and paste the values from the paste buffer into the cell.  It will copy all of the data starting at cell(row) 2.
  7. Repeat this for each new column, moving down one row for each new column.
Jim

Re: How do I copy a column multiple times while shifting each time one row down?

You already have two good solutions (and one marked as the solution), but there is another quick interactive way to do this too, that people sometimes forget. Select the original column and right-click on the column header choosing New Column Formula > Row > Lag Multiple. In the dialog put in the First Lag as 1 and the Last Lag as 6.

Dan Obermiller
WernerL
Level III

Re: How do I copy a column multiple times while shifting each time one row down?

Many thanks Dan, this is an incredibly straightforward and simple solution. I am sure there are many more hidden such hidden treasures in JMP!