Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- How to SUM rows with variable column names?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 10, 2014 8:15 AM
(3555 views)

I have a table with a variable number of columns. I want to add a column that SUM's all of the columns in a row no matter how many / few appear.

I tried this:

TotRow = Trans << **Get Column Names(**Numeric**)**;

TR = N Items**(**TotRow**)**;

Trans << **New Column(** "Total Time",

Numeric**)**;

For Each Row**(**:Total Time**[]**=Sum**(**Column**(**TotRow**[****1**::TR**])[]))**;

But it only SUMS the first row and not the rest. The number of columns and rows are always variable week to week.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 10, 2014 8:31 AM
(5816 views)

Solution

If you want to sum all numeric columns, and you don't need a formula column, you could do:

NamesDefaultToHere**(****1****)**;

// Make a table to work with

nr = **10**;

nc = **3**;

dt = NewTable**(**"Some Continuous Data"**)**;

For**(**c=**1**, c<=nc, c++,

dt << **NewColumn****(**"Col "||Char**(**c**)**, Numeric, Continuous, Formula**(**RandomInteger**(****1**, **10****)))**;

**)**;

dt << **AddRows****(**nr**)**;

// Get the data into a matrix

mat = dt << **GetAsMatrix**;

// Get the sum of each row

sumVec = **(**VSum**(**mat`**))**`;

// Add the sum column

dt << **NewColumn****(**"Sum", Numeric, Continuous, Values**(**sumVec**))**;

Note JSL does not have an 'HSum()' so you need to transpose and then transpose back.

5 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 10, 2014 8:31 AM
(5817 views)

If you want to sum all numeric columns, and you don't need a formula column, you could do:

NamesDefaultToHere**(****1****)**;

// Make a table to work with

nr = **10**;

nc = **3**;

dt = NewTable**(**"Some Continuous Data"**)**;

For**(**c=**1**, c<=nc, c++,

dt << **NewColumn****(**"Col "||Char**(**c**)**, Numeric, Continuous, Formula**(**RandomInteger**(****1**, **10****)))**;

**)**;

dt << **AddRows****(**nr**)**;

// Get the data into a matrix

mat = dt << **GetAsMatrix**;

// Get the sum of each row

sumVec = **(**VSum**(**mat`**))**`;

// Add the sum column

dt << **NewColumn****(**"Sum", Numeric, Continuous, Values**(**sumVec**))**;

Note JSL does not have an 'HSum()' so you need to transpose and then transpose back.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 10, 2014 8:45 AM
(2908 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 10, 2014 12:38 PM
(2908 views)

Ian,

It is great to have the answer, it is better to understand the answer.

In this statement:

sumVec = **(**VSum**(**mat`**))**`;

What are the ` for? I understand the VSum statement, just not why ` is needed on either side of it.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 10, 2014 1:27 PM
(2908 views)

That's the matrix transpose operator. It's a postfix operator, so it works on the argument to its left.

As Ian@JMP said, since JMP doesn't have an HSum() you have to use VSum() on the transposed matrix and then transpose it back.

-Jeff

-Jeff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 13, 2014 4:48 AM
(2908 views)

Jeff,

Thank you for the answer, and especially the link. Any day I learn something new is a good day.