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

Sum values

I have a column that has 3000 values.  I'd like to use a script to create another table that has values 1-5 from the original table summed and then 6-10 summed and so on.  So the new table would have 600 values since each value is the summation of 5 values from the original table.

 

Any ideas?

4 REPLIES 4
txnelson
Super User

Re: Sum values

Create a new column.  Go to the Column Info for the column.  Click on the Initialize Data down arrow and select "Sequence Data".  Change the Repeat each value N times to 5col.PNG

Click on OK and the new column will be created.  Next go to

     Tables==>Summary

pull down menu.  Choose your columns that you want Summed, and place then in the statistics selection box, and take the new column just added and place it in the Group selection box.  Click on OK and it will create the new data table you are asking for.

Jim
UberBock
Level III

Re: Sum values

Thank you!   That did did provide the change in the data that  I needed.  I there a way to put this into a script?  I intend to use it with a SQL query that gets the data for may different samples.  Each will have these 3000 values.

Craige_Hales
Super User

Re: Sum values

slightly different approach. (smaller example table, adjust as needed.)

dt=New Table( "Untitled 4",
	Add Rows( 12 ),
	New Column( "data",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 10, 20, 30, 100, 200, 300, 1000, 2000, 3000] )
	)
);

values=dt:data<<getvalues;// [1, 2, 3, 10, 20, 30, 100, 200, 300, 1000, 2000, 3000]
values=shape(values,nrows(values)/3)`;// transpose after reshape
values=vsum(values)`; // transpose after summing columns
newtable=astable(values);

The shape() function converts the 12x1 matrix to a 4x3, then transpose to 3x4. Vsum() does a vertical sum of the columns, leaving a 1x4, which is transposed back to a 4x1. Astable() makes a data table from that with 4 rows.

 

or...

 

Using Jim's approach, you could build the grouping column like this (using table above):

dt<<newcolumn("group",numeric,values(ceiling((1::12)/3)`));

Then grab the summary script from the source script in a manually created table.

Craige
UberBock
Level III

Re: Sum values

Thank you!

The last part helped as I could use it to sum portions of the 3000.

 

Now I have to figure out how to add a column with a time element such that it changes when 

values=shape(values,nrows(values)/3)`;// transpose after reshape  is different.