Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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
Highlighted
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.

Highlighted
Craige_Hales
Staff (Retired)

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
Highlighted
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.

 

Article Labels

    There are no labels assigned to this post.