- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.