Turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Help with column formula based on column locations?

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

Nov 5, 2012 12:55 PM
(3315 views)

Hello,

I have a list of strings that are column names. I want to determine their column location(i) in a data table and then add a new column with the formula( Column( i ) + Column( i+1 )). My code looks like this:

A | B | C | D | E | F |
---|---|---|---|---|---|

1 | 2 | 3 | 4 | 5 | 6 |

1 | 2 | 3 | 4 | 5 | 6 |

1 | 2 | 3 | 4 | 5 | 6 |

cols = dt << Get Column Names( St

ring);

list = { "B", "D"};

n = N Items( list);

locate = {}:

For( i=1, i<=n, i++,

locate* = Loc( dt, list );*

New Column( "New", Formula( Column( locate*) + Column( locate + 1)))*

);

I am having trouble because Loc() returns a matrix and Column() can only evaluate a number. I want my final data table to look like this:

A | B | C | D | E | F | New | New 2 |
---|---|---|---|---|---|---|---|

1 | 2 | 3 | 4 | 5 | 6 | 5 | 9 |

1 | 2 | 3 | 4 | 5 | 6 | 5 | 9 |

1 | 2 | 3 | 4 | 5 | 6 | 5 | 9 |

1 | 2 | 3 | 4 | 5 | 6 | 5 | 9 |

Can anyone help me out?

1 ACCEPTED SOLUTION

Accepted Solutions

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

A formula column will naturally not work if it's dependent columns are removed. If you want to delete the columns that are used by the formula, then what's the point of using column formulas in the first place? If you're only after the sum and not interested in the dynamic advantages that a column formula offers, I suggest you don't use formulas (which also make this a lot easier task).

Try this on the table above:

list = **{**"B", "D"**}**;

locate = Expr**(** Loc**(** cols, list**[**i**]** **)[****1****]** **)**;

For**(** i = **1**, i <= N Items**(** list **)**, i++,

cols = dt << Get Column Names**(** String **)**;

col = dt << New Column**(** "New", numeric **)**;

For Each Row**(** col**[]** = Column**(** locate **)[]** + Column**(** locate + **1** **)[]** **)**;

dt << delete columns**(** Column**(** locate **)**, Column**(** locate + **1** **)** **)**;

col << set name**(** list**[**i**]** **)**;

**)**;

4 REPLIES

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

Re: Help with column formula based on column locations?

To get column variables into column formulas by jsl is not straightforward. The formula must look exactly as the final expression, i.e nothing is evaluated automatically. The trick is to define and manipulate expressions (or strings) and then use the right combination of eval, parse etc.

Below is an example that seem to work. It's a bit circular as it locates the columns by its name and than gets it name again which then is parsed. Pew! I could not find an simpler procedure, but is interested if anyone has a more elegant solution idea.

//make the example table

dt = New Table**(** "test",

Add Rows**(** **3** **)**,

New Column**(** "A", Numeric, Continuous, Format**(** "Best", **1** **)**, Set Values**(** **[****1**, **1**, **1****]** **)** **)**,

New Column**(** "B", Numeric, Continuous, Format**(** "Best", **12** **)**, Set Values**(** **[****2**, **2**, **2****]** **)** **)**,

New Column**(** "C", Numeric, Continuous, Format**(** "Best", **12** **)**, Set Values**(** **[****3**, **3**, **3****]** **)** **)**,

New Column**(** "D", Numeric, Continuous, Format**(** "Best", **12** **)**, Set Values**(** **[****4**, **4**, **4****]** **)** **)**,

New Column**(** "E", Numeric, Continuous, Format**(** "Best", **12** **)**, Set Values**(** **[****5**, **5**, **5****]** **)** **)**,

New Column**(** "F", Numeric, Continuous, Format**(** "Best", **12** **)**, Set Values**(** **[****6**, **6**, **6****]** **)** **)**

**)**;

//Script starts here

cols = dt << Get Column Names**(** String **)**;

list = **{**"B", "D"**}**;

// Locate column expr

locate = Expr**(** Loc**(** cols, list**[**i**]** **)[****1****]** **)**;

// New column with formula expr

f = Expr**(** New Column**(** "New", Formula**(** Expr**(** Parse**(** Column**(** locate **)** << get name **)** **)**

+ Expr**(** Parse**(** Column**(** locate + **1** **)** << get name **)** **)** **)** **)** **)**;

//Loop over list

For**(** i = **1**, i <= N Items**(** list **)**, i++,

Eval**(** Eval Expr**(** f **)** **)**

**)**;

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

Re: Help with column formula based on column locations?

Thank you so much! This approach works very well.

I also have a follow-up question. I would like to make some changes to the columns of this new table:

A | B | C | D | E | F | New | New 2 |
---|---|---|---|---|---|---|---|

1 | 2 | 3 | 4 | 5 | 6 | 5 | 9 |

1 | 2 | 3 | 4 | 5 | 6 | 5 | 9 |

1 | 2 | 3 | 4 | 5 | 6 | 5 | 9 |

Is there a way to remove the 4 columns that were summed to create the "New" and "New 2" columns and rename them using list = { "B", "D"} so that the resulting table looks like this:

A | F | B | D |
---|---|---|---|

1 | 6 | 5 | 9 |

1 | 6 | 5 | 9 |

1 | 6 | 5 | 9 |

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

A formula column will naturally not work if it's dependent columns are removed. If you want to delete the columns that are used by the formula, then what's the point of using column formulas in the first place? If you're only after the sum and not interested in the dynamic advantages that a column formula offers, I suggest you don't use formulas (which also make this a lot easier task).

Try this on the table above:

list = **{**"B", "D"**}**;

locate = Expr**(** Loc**(** cols, list**[**i**]** **)[****1****]** **)**;

For**(** i = **1**, i <= N Items**(** list **)**, i++,

cols = dt << Get Column Names**(** String **)**;

col = dt << New Column**(** "New", numeric **)**;

For Each Row**(** col**[]** = Column**(** locate **)[]** + Column**(** locate + **1** **)[]** **)**;

dt << delete columns**(** Column**(** locate **)**, Column**(** locate + **1** **)** **)**;

col << set name**(** list**[**i**]** **)**;

**)**;

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

Re: Help with column formula based on column locations?

Thank you for your help. I really appreciate it.