topic Re: Sum values in Discussions

Sum values

UberBock — Tue, 07 Apr 2020 20:44:19 GMT

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?

Re: Sum values

txnelson — Tue, 07 Apr 2020 20:56:06 GMT

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 5

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.

Re: Sum values

UberBock — Tue, 07 Apr 2020 21:17:52 GMT

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.

Re: Sum values

Craige_Hales — Wed, 08 Apr 2020 06:24:01 GMT

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.

Re: Sum values

UberBock — Fri, 10 Apr 2020 15:24:13 GMT

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.