Subscribe Bookmark RSS Feed

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

csoon1

Community Trekker

Joined:

Feb 9, 2016

Basically I have this original table:

                                      

Column AColumn BColumn AColumn B
aeim
bfjn
cgko
dhlp

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

                                                                                                                                                                        

New   Column IDColumn AColumn BColumn AColumn B
1aeim
1bfjn
1cgko
1dhlp
2aeim
2bfjn
2cgko
2dhlp
3aeim
3bfjn
3cgko
3dhlp
xaeim
xbfjn
xcgko
xdhlp

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
Solution

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
ian_jmp

Staff

Joined:

Jun 23, 2011

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.

Solution

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)));

ian_jmp

Staff

Joined:

Jun 23, 2011

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);


csoon1

Community Trekker

Joined:

Feb 9, 2016

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