Working with Columns and Rows

[#1]

This demonstration is going to illustrate how to manage data columns and data table rows. And I'll use a script to make a new data table with one column to start. So in the course journal in section I'm going to click Make New Data Table and Column. And you can see that we're using the new table function. We're going to store the data table object reference that this creates in the variable dt. And the table will be called Test Data. I'm going to have a single column called pH. The data type will be numeric and the modeling type will be continuous. And then I'm using the values argument, which I could also say set values-- both of those are going to be synonymous-- to populate using the index operator, which is an infix operator. So this is just we're going to count from one to seven in increments of one. And because I'm supplying those values, that is the number of rows that this data table will have. There is an optional argument to add a specified number of rows that I might use if I wasn't initially populating values. And you'll also see if you have JMP write a script for a data table for you with that get script message, that will include the argument to add the rows. We'll also specify a format using the fixed decimal argument with a field with the and three decimal places. We're going to turn on the lock option on the column so that it can't be modified without unlocking it. And we'll use the range check argument to assign that column property. And the syntax here L-E-L-E ( ) is just saying that is less than or equal to the value in the column is less than or equal to And then lastly we have an example of the set property argument, where we're setting the property of the spec limits. And because there's more than one option for indicating the specification limits, we have a list of the three limits-- the lower spec limit, the upper spec limit, and the target. And remember, too, that all of these optional arguments to new column could be sent as messages to the column after it's been created or, as we see here, as arguments to new column. So I'm going to run the script. And you can see in the log below here that we have created this instance of a data table object called test data. And of course, this is what I would see if I hovered over the variable dt in my script. And I've got the data table with a single column and seven rows. And we can see all of the elements from the script indicated in the table itself. So for one thing, I know that the data type is numeric because I can see the modeling type icon shows that's continuous. And the continuous modeling type only takes numeric data. And as well, the numeric values are right justified. If we had numbers being treated as character, they'd be left justified. If we take a look at the icons in the columns panel, there's the lock icon, indicating that I locked the column. The little checkmark indicates that range check. And then the asterisk indicates any number of additional properties that don't have their own unique symbols. So to go into the column info dialog, of course, there are several ways. We can take a shortcut by clicking on this asterisk. And we see that that property is spec limits. There might be, again, more than one property listed in this dropdown menu. And I can select that. And that takes me into column info. Of course, we can also select the column and use the columns menu or right click on the column and select column info. So there are the spec limits with the values indicated in the set property argument. We also have that range check. And again, it's the less than or equal, less than or equal-- that LELE is indicated in the generic syntax-- and then we have the actual values. Here we can see that the column has been locked. The data type is numeric. Modeling type is continuous. And the format is that fixed decimal with the field width of which is the maximum number of characters that can be displayed in the column and three decimal places. So I'm going to cancel out of the column info dialog. And I'll go ahead and deselect that pH column. Now, there's an option in the column properties dropdown menu to set your own user column property. And if you do that interactively, the menu option is just called Other. And you name the property and whatever value it's going to take. I have a script in the journal called Add User Column Property. And we're sending this to the pH column. This user column properly will be called Meter Calibration. And I just want to put today's date formatted as a month, day, year character string. And so the text here, the second argument to set property, in this case, I use the Today function to retrieve the number of seconds since midnight, January at the moment that I run this line of code. I wrap that in the format function and specify the character string format that I want to have this displayed as. And again, this might be a trial and error process when you're writing a script. If I just supplied what I have highlighted-- and I think I might have-- there's-- yeah. I wanted to make sure I had the correct number of parentheses specified. Let me run this, what I've just highlighted. So I select that. And I Ctrl+R. And I see that that character string appears exactly as I want it in the log. And so I might run this set property message, this code with the property message, without the eval function initially. And if I did that, what I would see when I looked in the column info for the meter calibration property is literally the code. It would not actually evaluate. So set property might not evaluate its second argument, requiring us to wrap it in the eval function. Now, running it outside of the set property message, this is going to just-- let me go ahead and run that-- it's going to return the exact same thing. So this might be something where you initially just have the format of the Today function and then discover that it didn't return what you wanted in your column properties. And then you would realize you needed to add that eval. So I'm going to not have any of this selected and I'm going to run this script. The message I get here in the log is simply scriptable. It means that I did something that was scriptable. So you will sometimes find that you'll run some code and what you're expecting to have happen doesn't happen. And you might just see this scriptable message. So then you need to try to figure out what went wrong in that case, because you don't really get information from the word scriptable. But in our case, let's go ahead to the table. And again, we can simply get into column info by clicking on this asterisk. And I'm doing it this way to show that now we have another item in that menu. So again, any column properties that don't have their own unique icons will show up in this asterisk menu. And I'll click on Meter Calibration so that when the column info dialog opens that's the property that's selected. And there is that evaluated result of using the Today function and formatting it as a character string. So I'll cancel out of that. And I'm just going to use the Add User Column property Script Editor Window to play around a little more with this table. It doesn't need to be quite so wide now. So I want to send new column as a message to the data table right now. And I'm going to call that new column Hydronium. And I'm going to add a formula after I create it. And of course, I could add the formula as part of the creation of the column. And I'm also actually going to store the column object reference that results from the code in a variable. And I'll just call it H-Y-D, hyd for hydronium. And I'll assign that the result of sending a message to dt, because that's what we stored the test data object reference in. So I send the message to dt new column and then open parentheses and character string. The only required argument for new column is that column name, Hydronium. And I'll go ahead and run that. And storing that result in this variable means that I don't need to type out the whole name Hydronium again. So I didn't have to store that object reference. I can still communicate with the column. But this just saves me a little bit of typing to have given it that shortened name. Now, let's suppose, as I mentioned, I want to add a formula to this column. So I'll just go to a new line. And I want to be careful not to accidentally run the entire script again and create a second Hydronium column. So I'm going to comment this first line out. And I'm going to send a message to that variable hyd. And the message is set formula. And the formula I'm going to raise to the power of the negative pH, so raise to-- and then I'll open parentheses-- minus-- and I'll use the colon scoping operator because I'm referring to the pH column, just to be safe, in case there's any other variable name ph floating around in my global namespace. I'm not using Names Default to Here, even though we would, in practice, recommend that as a best practice. So I'm going to type that semicolon and run this line of the script. Again, we see the scriptable message in the log. But we can examine the table and see that those values have been populated. There's a couple other things to play around with working with our columns and rows. Let's suppose that I want to color cells in a data table to flag values that exceed some number. So I'm going to go to a new row in the Script Editor window. I'll create a variable called callout. And I'll assign that the result of the data table stored in dt receiving the message get rows where. And then my Boolean comparison will be the values in the column pH are greater than So I'll run just that line. And you can see that rows and get rows where returns a column vector of row numbers to me. And then I'm going to send a message to pH. And since that column object reference is not stored in a variable, I'll just refer to it by name. So pH is going to receive the message color cells. So this is another message we can send to a column. And I'll use the name of the color as the first argument. There's also index values and you can use the rgb values. I'll use the name as a character string. So I'm going to color those cells red. And then the second argument is, which cells do you want to color? You can do the whole column or you can supply the row numbers. So I'm going to use the variable Callout, which is storing the rows where pH is greater than three. And I'll run just that line. By the way, notice I forgot to put a semicolon. So that illustrates that that ending semicolon is not necessary. It's only when I have additional arguments coming. If we look at the data table now, you can see that those four cells have been colored red. So this might be something nice to do if you've got a big data table and you've got maybe specification limits on some of the columns. You could color cells based on those specification limits for things that are out of spec, for example, to have those visually flagged in the data table before you even start looking at graphs. You certainly don't have to color cells, but it is an option that can be used. So in this demonstration, we saw an example of creating a new data table with a column, adding a column to that data table, and playing around a little bit with some of the things we can do with our columns and rows.

[Nita_Natchanok]

I tried to run the Set Property without "Eval", It still works. (Currently using JMP 16.2)

:pH << Set Property( "Meter Calibration2", Format( Today(), "m/d/y" ) );