Need suggestion on best way to add table column for an ID that does this....<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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.
Re: Need suggestion on best way to add table column for an ID that does this....@Ian and @ms thanks for your good suggestions. On my present script I am incorporating this suggestion from @Ian (with a mix from @ms).
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);
Re: Need suggestion on best way to add table column for an ID that does this....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)));
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.
