create formula to generate columns of design matrix

[#1]

I am a beginner to use JMP, and cannot find similar answers to my questions in JMP community. Suppose that I have a data table with several columns, and the user needs to select the column from the data table and then a design matrix will be generated according the selected column. The selected column and the design matrix will be created in a new data table. But this is not enough. If the user want to change the value in the selected column in the new data table, then the design matrix will be updated accordingly. My problems is that I cannot update the design matrix correctly with the code below:

// create a window to select column

// omit some code to create the window

CheckAndConvert = Expr(

selectedColumns = c << Get Items;

// If inputs are valid, we continue

If(N Items(selectedColumns) > 0,

win << Close Window;

A = Column(selectedColumns);

s = A << GetAsMatrix;   // extract the select column in s  

// create the new data table "Design Matrix"

dt = New Table("Design Matrix");

dt << New Column("slice", Numeric, Values(s[0, 1]));  // copy s to the column "slice" in the new data table

// create corresponding columns of design matrix and adding them into the new data table

For( i=1, i<=Col Max(dt:slice), i++,

  dt << New Column("X"||Char(i), Numeric,

  After Last,

  Formula(

  If(dt:slice == i, 1,

  0

  )

  )

  );

);

);

);

PS:  I can get the correct answer when run the code without change the value in the slice column, but I cannot update X columns if I change the value of slice. Note that the number of X columns is the maximum value in the slice column, which is not fixed with different values. In following pictures, I show the examples to select column 1 and column 2 in the test data.

Thanks in advance,

Pulong

[#2]

I wasn't quite sure exactly what you were looking for. But given you are new to JMP, the code below may give you some ideas.

NamesDefaultToHere(1);

// Test table

dt = NewTable("Test",

  NewColumn("C1", Numeric, Continuous, Formula(RandomInteger(1,5))),

  NewColumn("C2", Numeric, Continuous, Formula(RandomInteger(1,5))),

  NewColumn("C3", Numeric, Continuous, Formula(RandomInteger(1,5))),

  AddRows(10)

  );

// UI

NewWindow("Update Design",

  PanelBox("Pick a columnn to see the design",

  lub = LineUpBox(NCol(2),

  clb = ColListBox(dt, All, Numeric, MaxItems(1), MinItems(1), clbScript)  )

  )

  );

// Script to dynamically update the line up box lb depending on the selection in clb

clbScript =

Expr(

  // Get the values in the selected column

  vals = Column(dt, clb << getSelected) << getAsMatrix;

  // Delete any existing matrix box

  Try(mb << delete);

  // Get the design matrix in a matrix box

  mb = MatrixBox(Design(vals));

  // Append the new matrix box

  lub << append(mb);

);

[#3]

Thanks for your kind help.

I need to generate design matrix from a column, so that I can also update the design matrix if the values in the column are changed. In fact, I want to create a formula to generate the design matrix based on selected columns, and save the formula so that I can update the design matrix if some values in the selected columns are modified.

In my code, the design matrix is generated, but I cannot get the updated design matrix if I change certain value in the selected column, which is the problem I am struggling with. I really appreciated it if you could provide some suggestion on my problem.

Thanks,

Pulong

[#4]

I could certainly be wrong (depending on how general you need your solution to be), but it doesn't feel to me that formula columns are the best way to attempt this. The fact that one column in your source table (generally) maps to multiple columns, and the fact that the number of such columns is the (variable) number of levels in the source column make this difficult.

Before offering another suggestion, perhaps you could describe the workflow you envisage in a little more detail, please? Also indicate which steps involve manual intervention.

[#5]

Since this is the first time to use JMP, I will try to describe my problem in the language of JMP script. Briefly, I have a data table, within which, there exist a "slice" column, and I want to generate the design matrix based on the slice column, and also generate some other columns based on the design matrix. What I need to do is to develop a JMP add-in which implements this task, so that other colleagues can also use my add-in for computer experiments.

First, I would like to develop a window to select the slice column, and generate a data table including the slice column and the design matrix, and save the formula to automatically update the design matrix if some values in the slice column are changed later. ( I need to save formula , because my project manager requested it, but I could convince him without using the formula columns if I come up with a better solution.) 

Now, I can create the window, and generate the data table which includes the sliced column and design matrix, but the design matrix cannot be updated if I change the values in the slice column.

Regarding the sliced column:

I know that the design function in JMP can automatically generate the design matrix, but it's not helpful if I want  formula columns. So I wrote my own function to generate my design matrix, as shown in my script. Suppose that I have the slice column a = [1, 1, 2, 3]'. Then the design matrix will be generated as

1     0     0

1     0     0

0     1     0

0     0     1.

The sliced column can be vary large, and hence it's difficult to know how many columns will the design matrix have by manually looking at the slice column. Besides, the values in the sliced column can be modified arbitrarily.

I think the key steps to create generate the design matrix are the following:

1. select the sliced column, click the button "OK", then a new table will be generated, which includes the slice column and design matrix. Clearly, this step involves manually intervention.

2. If I modify some values in the slice column in the new table manually, then the original design matrix will be deleted automatically, and the corresponding new design matrix will be generated.

Second, I also need to generate some columns based on the design matrix, and I also wrote out the script to generate them, but I do not know how to save them as formula columns. This issue will not be a problem if I know how to save the formula columns for design matrix.

Last, if I succeeded to create the design matrix and some columns, then I would package them as a add-in, and test it under many different circumstance.

Thanks for your suggestion. Let me know if I make myself understood.

Pulong

[#6]

Many thanks for your more detailed explanation. Perhaps I can echo it back in different words to be sure I have understood?

Suppose the first table (containing all possible slices) is called dt0. Thinking about your key steps above:

1. The user selects a column (a slice) in dt0, and makes a new table (call this dt1) containing just this slice, plus the columns of the design matrix (plus the other derived columns you mention, but don't spell out).
2. You want the user to later be able to modify the slice column in dt1 and have the other columns in dt1 update 'automatically'.

I imagine that you final add-in would have two menu options: 'Define Slice' and 'Update Slice'. If my description of 1. above is correct, this 'Design Slice' script is easily obtained by tweaking the code I've already suggested: The table dt1 could be put in a shared folder so others could get it, and you can add whatever metadata you want to the table or the columns within it.

The 'Update Slice' script could present the user with all the available tables in the shared folder, and allow them to select one. At this point, though, they would not edit the table directly: Rather they would be presented with a report window allowing them to modify the values of the slice column in the table. Here, 'modify' could mean update existing values, or append new rows. When they click OK in this report to signify they are happy, dt1 could be automatically updated to reflect all their changes, and you could also update the metadata as required. Of course this 'update' code would be common with 'Define Slice'.

Unless I've misunderstood, it seems that this workflow would be reasonable, perhaps even better than giving users free access to dt1 itself.

[#7]

Thanks for you reply. Your understanding about the workflow is precisely what I am doing.

Regard "Define Slice":

I have already designed the window to achieve this task.

Regard "Update Slice":

The difficulty lies in the automatic update to whatever change the user will make. I can generate the design matrix, but I do not know how to automatically update the design matrix if the user makes some change in slice column. Besides, if I know how to automatically update the design matrix, it will not be a problem to automatically generate other columns based on the design matrix by tweaking the code a little bit.

Thanks,

Pulong

[#8]

OK, many thanks.

So it sounds like the missing piece is to be able to edit the cells of a (numeric) column in a report window, in which case you can use 'NumberColEditBox()'. For a character column you can use 'StringColEditBox'. By combining the two you could present any table in an editor window that your code controls (though I guess you would need to skip row state and expression columns):

NamesDefaultToHere(1);

// Use a numeric column

dt = Open("$SAMPLE_DATA/Big Class.JMP");

cName = "height";

cVals = Column(dt, cName) << getValues;

// UI

nw = NewWindow("Edit Cells", ceb = NumberColEditBox(cName, cVals), ButtonBox("Update", updateCells));

// Update script

updateCells =

Expr(

  nw << closeWindow;

  newVals = ceb << getAsMatrix;

  dt << BeginDataUpdate;

  Column(dt, cName) << setValues(newVals);

  dt << EndDataUpdate;

);

[#9]

I really appreciate for your help.

I have already figured out a solution to my problem today.

[#10]

I think I have a similar -if not identical- question.

I want to create a design matrix from predictor variables similar to x2fx in Matlab.

For example: by selecting predictor variables & specify the designtype (2nd order interactions) and then generate the design table.

See example from Matlab below: (but I want to do this in JMP).

Example 1

The following converts 2 predictors X1 and X2 (the columns of X) into a design matrix for a full quadratic model with terms constant, X1, X2, X1.*X2, X1.^2, and X2.^2.

X = [1 10
     2 20
     3 10
     4 20
     5 15
     6 15];

D = x2fx(X,'quadratic')
D =
     1     1    10    10     1   100
     1     2    20    40     4   400
     1     3    10    30     9   100
     1     4    20    80    16   400
     1     5    15    75    25   225
     1     6    15    90    36   225