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
- :
- Re: Need suggestion on best way to add table column for an ID that does this....

News

On June 1, we’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.

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

Highlighted

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

Feb 28, 2016 11:34 PM
(10819 views)

Basically I have this original table:

Column A | Column B | Column A | Column B |

a | e | i | m |

b | f | j | n |

c | g | k | o |

d | h | l | p |

Now what I want to do is to create another table with a "new column ID" that looks like this

New Column ID | Column A | Column B | Column A | Column B |

1 | a | e | i | m |

1 | b | f | j | n |

1 | c | g | k | o |

1 | d | h | l | p |

2 | a | e | i | m |

2 | b | f | j | n |

2 | c | g | k | o |

2 | d | h | l | p |

3 | a | e | i | m |

3 | b | f | j | n |

3 | c | g | k | o |

3 | d | h | l | p |

x | a | e | i | m |

x | b | f | j | n |

x | c | g | k | o |

x | d | h | l | p |

What I have on my mind now is to clone the original table by creating a subset. And on this subset I add the "new column ID" with a certain value (the same for all rows). I repeat the same process creating as many subsets as needed (with each subset a "new column ID" is added). After I am done, I can just concatenate all subsets into one table.

I want to know if you have better ideas that you can share.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

You do not need to make subsets to concatenate multiple copies. You can just add the original table multiple times in the concatenate dialog. If the new ID is a counter a column formula can be used to generate the ID.

A jsl example:

// Stack dt n times

dt = Current Data Table**()**;

n = **10**;

tables = Repeat**({**dt**}**, n-**1****)**;

new_dt = dt << **concatenate****(**tables**)**;

new_dt << **New Column****(**"New Column ID", Numeric, Formula**(**Ceiling**(**Row**()** / **4****)))**;

4 REPLIES 4

Highlighted
##

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

Re: Need suggestion on best way to add table column for an ID that does this....

Please find an alternative, but I'm not sure that one can say it's 'better':

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

// Sample (numeric!) data

dt1 = NewTable**(**"Source Data",

NewColumn**(**"A", Numeric, Continuous, Formula**(**RandomNormal**()))**,

NewColumn**(**"B", Numeric, Continuous, Formula**(**RandomInteger**(****1**, **10****)))**,

AddRows**(****6****)**;

**)**;

// Clone the data in dt1 'n1' times

n1 = **2**;

n2 = NRow**(**dt1**)**;

source = dt1 << **getAllColumnsAsMatrix**;

destination = **[]**;

for**(**i=**1**, i<=**2**, i++, destination = VConcat**(**destination, source**))**;

// Make an ID column as a vector

id = SortAscending**(**Repeat**((****1**::n1**)**`, n2**))**;

// Assemble the final table

cols = Insert**(**dt1 << **getColumnNames****(**String**)**, "ID", **1****)**;

dt2 = AsTable**(**id||destination, << **ColumnNames****(**cols**))**;

dt2 << **setName****((**dt1 << **getName****)**||" cloned "||Char**(**n1**)**||" times"**)**;

If the data is not all numeric, 'Get All Columns As Matrix' still works, but you have the additional work of figuring out how to remap values back to Character when you assemble dt2.

Highlighted

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

You do not need to make subsets to concatenate multiple copies. You can just add the original table multiple times in the concatenate dialog. If the new ID is a counter a column formula can be used to generate the ID.

A jsl example:

// Stack dt n times

dt = Current Data Table**()**;

n = **10**;

tables = Repeat**({**dt**}**, n-**1****)**;

new_dt = dt << **concatenate****(**tables**)**;

new_dt << **New Column****(**"New Column ID", Numeric, Formula**(**Ceiling**(**Row**()** / **4****)))**;

Highlighted
##

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

Re: Need suggestion on best way to add table column for an ID that does this....

As is so often the case, brevity is a virtue!

I liked Marcus' way so much, I made a function out of it for later re-use:

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

// Sample data

dt1 = NewTable**(**"Source Data",

NewColumn**(**"A", Numeric, Continuous, Formula**(**RandomNormal**()))**,

NewColumn**(**"B", Numeric, Continuous, Formula**(**RandomInteger**(****1**, **10****)))**,

AddRows**(****6****)**;

**)**;

// Clone table 'dt' 'n' times . . .

cloneTable =

Function**({**dt, n**}**, **{**Default Local**}**,

tables = Repeat**({**dt**}**, n-**1****)**;

new_dt = dt << **concatenate****(**tables**)**;

new_dt << **setName****((**dt << **getName****)**||" cloned "||Char**(**n**)**||" times"**)**;

idCol = new_dt << **newColumn****(**"ID", Numeric, Nominal, Formula**(**Ceiling**(**Row**()** / NRow**(**dt**))))**;

idCol << **deleteFormula**;

new_dt << **moveSelectedColumns****({**idCol**}**, "ToFirst"**)**

**)**;

dt2 = cloneTable**(**dt1, **20****)**;

Highlighted
##

@Ian and @MS thanks for your good suggestions. On my present script I am incorporating this suggestion from @Ian (with a mix from @MS).

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

Re: Need suggestion on best way to add table column for an ID that does this....