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 Inputs on Transforming a Table or Matrix

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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Need Inputs on Transforming a Table or Matrix

Mar 3, 2016 10:33 PM
(1802 views)

This question is related or a follow-up to my previous post (Need suggestion on best way to add table column for an ID that does this....). On that post, my question was specifically answered and there were lots of tips I learned. However, I am again in need of a good suggestion on how to manipulate a table or matrix to transform it from the original form to the intended form.

Assuming this is my original table (or matrix of similar form):

Table 1: Original Table

Column A | Column B | Column C | Column D |

a | e | i | m |

b | f | j | n |

c | g | k | o |

d | h | l | p |

I wanted each row from this table (or matrix) to be repeated "n" times such that the resulting table (or matrix) would look like this:

Table 2: Intended New Table Format

Column A | Column B | Column C | Column D |

a | e | i | m |

a | e | i | m |

a | e | i | m |

a | e | i | m |

b | f | j | n |

b | f | j | n |

b | f | j | n |

b | f | j | n |

c | g | k | o |

c | g | k | o |

c | g | k | o |

c | g | k | o |

d | h | l | p |

d | h | l | p |

d | h | l | p |

d | h | l | p |

In the example above, each row from the original table was repeated "4" times.

As I was playing with "shaping" a matrix and applying the suggestion of concatenating tables "n" times from my previous post, I was able to create this table (from my original table)

Table 3: Some manipulated form of Table 1 done by Shaping the Matrix and Repeating the rows "n" times

Column A | Column B | Column C | Column D | Column E | Column F | Column G | Column H | Column I | Column J | Column K | Column L | Column M | Column N | Column O | Column P |

a | e | i | m | b | f | j | n | c | g | k | o | d | h | l | p |

a | e | i | m | b | f | j | n | c | g | k | o | d | h | l | p |

a | e | i | m | b | f | j | n | c | g | k | o | d | h | l | p |

a | e | i | m | b | f | j | n | c | g | k | o | d | h | l | p |

However, I am stuck here. I would have wanted this table to look like my intended table (Table 2) as described above. I appreciate any comments and suggestions. I believe there are more intuitive and efficient ways of handling this than what I have in mind. Thanks.

3 REPLIES 3

Highlighted
##

I just thought of something now... I think it's possible to simply clone the original table "n" times... and then just sort the rows according to a certain column (let's say Column A). Do you think this works?

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

Re: Need Inputs on Transforming a Table or Matrix

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

Re: Need Inputs on Transforming a Table or Matrix

That looks like it will give you 'Table 2' above. In JSL (if 'dt' is a reference to your table):

dt << Sort(By(:Column A));

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

Re: Need Inputs on Transforming a Table or Matrix

Adding to Ian's response:

The cloning of the table can be done in one step.

Names Default to Here(1);

dt= current data table();

dt << Concatenate(dt,dt,dt,append to first table(1));

dt << Sort(By(Column A));

In many cases where I have used this technique, there does not exist a column to sort on to get the final order. So what I do in that case is to create such a column

Names Default to Here(1);

dt= current data table();

dt<<new column("rownum",values(1::N Rows(dt));

dt << Concatenate(dt,dt,dt,append to first table(1));

dt << Sort(By(Rownum));

dt<<delete columns("rownum");

Jim