Chapter 8: Rows, Columns, and Tables
Question 8.1: I can manipulate a data table interactively, such as by selecting and deleting rows, by adding new columns, and so on. How can I get JMP to produce the script for these actions?
Question 8.4: How can I select from the currently selected rows?
Question 8.5: How can I perform a Select Where on numeric datetime values?
Question 8.6: How can I determine the number of selected, excluded, or hidden rows in a data table?
Question 8.9: I want to delete all the character columns in a data table. How can I do this?
Question 8.10: Are there any JSL commands for recoding data, as in Cols► Recode?
Question 8.14: How can I delete the formula from every column in my data table?
Question 8.15: How can I use a variable in a new column with a formula?
Question 8.17: How can I get a list of all open data tables?
Question 8.18: How can I open and concatenate multiple files that have the same format?
Question 8.1: I can manipulate a data table interactively, such as by selecting and deleting rows, by adding new columns, and so on. How can I get JMP to produce the script for these actions?
As we mentioned in chapter 1, JMP does not record your steps. It can, however, reproduce the data table or column in its current state using the Get Script command:
Current Data Table() << Get Script;
:colName << Get Script;
Note: The result of running the previous code is pasted in the log. You can copy the New Table() or New Column() commands from the log and paste into your main script.
If you have only a few manipulations, it might be easier to write the code yourself for the desired manipulation. The following code demonstrates how to do a few of the table manipulations mentioned previously. Further details can be found in the “Data Tables” chapter of the Scripting Guide.
/* Open the sample data table, Big Class. */
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
/* Select rows where the age is 15 or greater. */
dt << Select Where( :age >= 15 );
/* Delete the selected rows. */
dt << Delete Rows;
/* Add a new column containing a formula to calculate
the weight/height ratio. */
dt << New Column( " Weight Height Ratio", Numeric, "Continuous", Formula( :weight / :height ) );
The resulting data table should have 27 rows and 6 columns, as shown in Figure 8.1.
Figure 8.1 Data Table After After Deleting Rows and Adding a Formula Column
Question 8.2: How can I select rows where more than one condition is true? How can I select rows where at least one of the specified conditions is true?
Use the logical operator AND or ‘&’ to select within a selection, as shown in the following code:
/* Open the sample data table, Big Class.jmp. */
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
/* Select rows where age is greater than 14
AND height is less than 65. */
dt << Select Where( :age > 14 & :height < 65 );
Use the logical operator OR or ‘|’ to extend a selection:
/* Select rows where age is greater than 14
OR height is less than 65. */
dt << Select Where( :age > 14 | :height < 65 );
The following images show the results of using the ‘&’ and ‘|’ operators for selecting rows.
Figure 8.2 Row Selection Where Age > 14 and Height < 65
Figure 8.3 Row Selection Where Age > 14 or Height < 65
Question 8.3: There are already rows selected in my data table. How can I select additional rows without deselecting any of the currently selected rows?
The Select Where() command offers an optional second argument that enables you to specify the type of selection to be performed. The argument name is Current Selection(). To instruct JMP to select rows without deselecting any rows, use the string "Extend" as the argument to Current Selection().
/* Open the sample data table, SATbyYear.jmp. */
dt = Open( "$SAMPLE_DATA/SATbyYear.jmp" );
/* Select the data where the SAT Verbal score is 580 or greater. */
dt << Select Where( :SAT Verbal >= 580 );
Wait( 1 ); //For demonstration purposes.
/* Select rows where the SAT Math score is 600 or greater,
without deselecting any of the previously selected rows. */
dt << Select Where(
:SAT Math >= 600,
Current Selection( "Extend" )
);
Note: If these two selection criteria were being applied at the same time, the conditions would be specified with the logical operator OR or ‘|’ to extend a selection. See Solution 8.2 for an example using the OR operator.
The “Select Rows” section of the “Rows” chapter in the Scripting Guide (Help ►Books ►Scripting Guide) provides many additional examples of using Select Where().
Figure 8.4 Extended Row Selection
The Select Where() command offers an optional second argument that enables you to specify the type of selection to be performed. The argument name is Current Selection(). To instruct JMP to select from only the currently selected rows, use the string "Restrict" as the argument to Current Selection().
/* Open the sample data table, SATbyYear.jmp. */
dt = Open( "$SAMPLE_DATA/SATbyYear.jmp" );
/* Select the data from the Northeast region. */
dt << Select Where( :Region == "Northeast" );
Wait( 1 ); //For demonstration purposes.
/* Select rows in the current selection where
the 2004 ACT score was greater than 21. */
dt << Select Where(
As Column( "ACT Score (2004)" ) > 21,
Current Selection( "Restrict" )
);
Note: If these two selection criteria were being applied at the same time, the conditions would be specified with the logical operator AND or ‘&’ to select within a selection. See Solution 8.2 for an example using the AND operator.
The “Select Rows” section of the “Rows” chapter in the Scripting Guide (Help ►Books ► Scripting Guide) provides many additional examples of using Select Where().
Figure 8.5 Restricted Row Selection
To select rows based upon datetime values, you can use a date literal, as shown in the following code.
/* Open the sample data table, Abrasion.jmp. */
dt = Open( "$SAMPLE_DATA/Abrasion.jmp" );
/* Select rows where the date and time are between
noon, April 30, 1995 and noon, May 1, 1995. */
dt << Select Where( 30Apr1995:12:00:00 <= As Column( "Date/Time" ) <= 01May1995:12:00:00 );
Notice that a date literal does not need to be placed within double quotation marks. For more information about date literals, visit the “Date-Time Functions and Formats” section in the Scripting Guide.
Figure 8.6 Rows Selected within Specified Datetime Criteria
Use the Get Selected Rows, Get Excluded Rows, and Get Hidden Rows functions to obtain a matrix of corresponding row numbers. Then use the N Row() function to obtain the number of rows in the matrix, as shown in the following code:
/* Open the sample data table, Fitness.jmp. */
dt = Open( "$SAMPLE_DATA/Fitness.jmp" );
/* Select, exclude, and hide some rows for demonstration
purposes */
dt << Select Where( :age <= 40 ) << Exclude( 1 ) << Hide( 1 );
selRows = N Row( dt << Get Selected Rows );
exclRows =N Row( dt << Get Excluded Rows );
hidnRows =N Row( dt << Get Hidden Rows );
/* Print variable values in the log. */
Show( selRows, exclRows, hidnRows );
Figure 8.7 Log After Script Was Executed
Question 8.7: I am attempting to select some rows, and then create a subset data table. When no rows are selected, all the rows are included in the subset data table. How can I create a subset only if there were rows selected?
Get the number of rows selected using the Get Selected Rows command, and then verify that the result is greater than 0 before creating the subset, as shown in the following code:
/* Open the Big Class sample data table. */
dt = Open( "$SAMPLE_DATA/Fitness.jmp" );
/* Attempt to select rows based on the desired conditions. */
dt << Select Where( :sex == "F" & :age < 35 );
/* Perform a subset if rows were selected. Otherwise, print
a message in the log. */
If( N Row( dt << Get Selected Rows ) > 0,
dt << Subset( Output Table Name( "Females, Under 35" ) ),
Print( "No rows selected." )
);
Because the Fitness sample data table does not contain any rows that meet the criteria specified in the Select Where statement, the statement "No rows selected." is printed in the log upon running the script.
Figure 8.8 Log After Script Was Executed
Question 8.8: How can I determine the number of selected columns in a table? How can I get a list of the selected columns?
Use the Get Selected Columns command to obtain a list of column names, and then use the N Items() function to obtain the number of selected columns in the list, as shown in the following code:
/* Open a sample data table for demonstration purposes. */
dt = Open( "$SAMPLE_DATA/Big Class.JMP" );
/* Select the first two columns for demonstration purposes. */
Column( "name" ) << Set Selected( 1 );
Column( "age" ) << Set Selected( 1 );
/* Store the list of selected columns in a variable. */
selColList = dt << Get Selected Columns;
/* Store the number of items in the list in a variable. */
numSelCols = N Items( selColList );
/* Show variable values in the log. */
Show( selColList, numSelCols );
Figure 8.9 Log After Script Was Executed
The data table message Get Column Names returns a list of all column names in the data table. By specifying the optional Character argument, a list of only the character columns in the table is returned. Use Delete Columns to delete all columns named in the list.
/* Open the sample data table, Big Class.jmp. */
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
/* Store all the character column names in a list. */
cols = dt << Get Column Names( "Character" );
/* Delete the columns. */
dt << Delete Columns( cols );
Figure 8.10 Data Table After All Character Columns Were Deleted
You can select the desired column(s) and then use the Recode command to bring up the Recode launch dialog for the user to make their desired changes.
/* Open a sample data table for demonstration purposes. */
dt = Open( "$SAMPLE_DATA/Cities.jmp" );
/* Select the State column. */
:State << Set Selected( 1 );
/* Bring up the Recode launch dialog. */
dt << Recode;
Currently, there are no JSL commands specifically for Recode from the Cols menu. Instead, there are a variety of functions available that you can use to perform the same actions. For example, when you interactively select Formula Column as the destination for the new values, JMP creates a new column and uses the Match() function in the formula to recode the values. You can easily write a Match() function and choose to place the results in a new column (no formula), a formula column, or in place. The following code samples demonstrate each of these options.
New Column
/* Open a sample data table */
dt = Open( "$SAMPLE_DATABig Class.jmp" );
/* Create a new formula column for the recoded values */
dt << New Column( "New Column", Character );
/* Assign new values to the New Column in loop */
For Each Row( dt,
/* Use Match() to assign the new values
To the new column */
:New Column = Match( :sex,
"F", "FEMALE",
"M", "MALE",
:sex
)
);
Formula Column
/* Open a sample data table */
dt = Open( "$SAMPLE_DATABig Class.jmp" );
/* Create a new formula column for the recoded values */
dt << New Column( "Formula Column",
Character,
Formula(
/* Use Match() to assign the new values */
Match( :sex,
"F", "FEMALE",
"M", "MALE",
:sex )
)
);
In Place
/* Open a sample data table */
dt = Open( "$SAMPLE_DATABig Class.jmp" );
/* Assign new values in loop */
For Each Row( dt,
/* Use Match() to assign the new values
to the same column */
:sex =Match( :sex,
"F", "FEMALE",
"M", "MALE",
:sex
)
);
Figure 8.11 Big Class after Each Recode Method
The following are JSL functions that map to some of the red triangle options. Examples of each of these can be found in the Scripting Index (Help►Scripting Index).
Table 8.1 Functions for Red Triangle Options
|
Red Triangle Option |
JSL Function |
Description |
|
Convert to Titlecase |
Titlecase() |
Changes to uppercase any a-z character encountered after a character that is not a-z, except apostrophe |
|
Convert to Uppercase |
Uppercase() |
Changes any lowercase letters to uppercase letters |
|
Convert to Lowercase |
Lowercase() |
Changes any uppercase letters to lowercase letters |
|
Trim Whitespace |
Trim Whitespace() Trim() |
Removes leading, trailing, or both leading and trailing spaces |
|
Collapse Whitespace |
Collapse Whitespace() |
Removes leading, trailing, and duplicate interior spaces |
Note: To script the remaining options accessible from the red triangle menu would require additional knowledge not discussed in this book or access to internal functions, which are not currently available.
Question 8.11: I have a set of values that I want to use as the control limits for a data table column. How can I use these values to create the Control Limits column property?
When working with column properties, we recommend adding the property interactively first. Then right-click on the column to bring up the context menu, where you can select the option to Copy Column Properties in order to retrieve the proper syntax for the column property. Once you have the correct syntax, you can then modify it to fit your needs.
When using a variable in the assignment of a column property, you must do something to replace the variable with its value before it can be assigned. In the following example, we have demonstrated the use of Eval(), Eval Expr(), and Expr() for this purpose.
As noted in the comments, the variable is wrapped in the Expr() function. This identifies the variable is something to be evaluated. The Eval Expr() function searches the expression for the Expr() function. It evaluates the variable and returns the expression with the variable replaced with its value. Finally, the outer Eval() function evaluates the entire expression after the replacement has occurred.
/* Open a sample data table for demonstration purposes. */
dt = Open( "$SAMPLE_DATA/Quality Control/Pickles.jmp" );
/* Create the list of values to be used in the Control Limits property. */
newAvg = 8.25;
newLCL = 7.5;
newUCL = 13;
newSubgroup = 4;
/* Replace the limits and subgroup variables with the listed values. */
Eval(//Evaluates the expression after replacement has occurred.
Eval Expr(//Evaluates anything inside of the Expr() function.
:acid << Set Property(
"Control Limits",
{XBar(
Avg( Expr( newAvg ) ),
LCL( Expr( newLCL ) ),
UCL( Expr( newUCL ) ),
Subgroup Size( Expr( newSubgroup ) )
)}
)
)
);
Figure 8.12 Control Limits Assigned with Actual Values
Question 8.12: How can a character column containing datetime values be converted to a numeric column of JMP date values using a script?
In order to change a character column of datetime values to a numeric column of JMP datetime values, the format must be specified in the Data Type() message for the column. Otherwise, the data will be converted to missing.
In the following example, we first demonstrate concatenating a character date column with a character time column into a new character column called DateTime. The new column is then converted to numeric with the proper datetime format. Afterward, we also demonstrate converting the Date and Time columns to numeric separately.
/* Open a sample data table that contains date and time columns */
dt = Open( "$SAMPLE_DATAQuality ControlPickles.jmp" );
/* Create a new column that concatenates the Date and Time columns */
dt << New Column( "DateTime", "Character", Formula( :Date || " " || :Time ) );
/* Remove the formula */
:DateTime << Delete Formula;
/* Convert the new column to numeric with the proper date time format */
:DateTime << Data Type( "Numeric", Format( "m/d/y h:m:s" ) )
<< Modeling Type( "Continuous" );
/* Alternately, convert the Date and Time columns to numeric columns with the proper formats */
:Date << Data Type( "Numeric", Format( "m/d/y" ) )
<< Modeling Type( "Continuous" );
:Time << Data Type( "Numeric", Format( "h:m:s" ) )
<< Modeling Type( "Continuous" );
Figure 8.13 Conversion of Dates from Character to Numeric
Question 8.13: I can replace all the missing numeric values in a table with zeros interactively by performing Edit►Search►Find and Replace. How can I do the same procedure using scripting?
After getting a list of all the numeric columns in the table, use a For() loop to traverse each of the numeric columns. The Get Rows Where() function nested with the Is Missing() function will return a vector with the missing values for the named column. This vector can be used as the column subscript to set all missing rows to zero with a single statement.
dt = Open( "$SAMPLE_DATA/Cities.jmp" );
/* Obtain a list of all numeric column names as strings. */
numCols = dt << Get Column Names( Numeric, String );
/* Loop through each numeric column. */
For( i = 1, i <= N Items( numCols ), i++,
missingRows = dt << Get Rows Where( Is Missing( As Column( numCols[i] ) ) );
Column( numCols[i] )[missingRows] = 0;
);
Figure 8.14 Find and Replace Missing Values with Zeros
In the previous version of this book, we demonstrated using the Get Property command to capture the formula, if any, and then conditionally delete the formula property, as shown in the following code:
/* Open the IRLS Example sample data table. */
dt = Open( "$SAMPLE_DATA/Nonlinear Examples/IRLS Example.jmp" );
/* Loop through each column in the data table. */
For( i = 1, i <= N Col( dt ), i++,
/* Obtain the formula column property. */
fmla =Column( dt, i ) << Get Property( "Formula" );
/* Delete the formula, if one exists. */
If( !Is Empty( fmla ),
Column( dt, i ) << Delete Formula
);
);
As we’ve mentioned before, there are multiple ways to accomplish this task. In the following example, we demonstrate how you could just wrap the Delete Formula expression in a Try() function. The Try() will escape any error generated when attempting to delete a formula from a column that has no formula.
/* Loop through each column in the data table. */
For( i = 1, i <= N Col( dt ), i++,
/* Attempt to delete the formula from each column. */
Try( Column( dt, i ) << Delete Formula )
);
|
Figure 8.15 Before Deleting the Formulas
|
Figure 8.16 After Deleting the Formulas
|
When using JSL variables in column formulas, the variable should be replaced with its value before it is assigned to the formula. If you do not replace the variable with its value in the formula, the result of the formula can change each time the value of the variable changes, such as when formulas are added in a For() loop.
As explained in chapter 7, Eval Expr() searches its expression argument for Expr() functions. For every Expr() function found, JMP evaluates and replaces the variable with its evaluated value. So in the following example, JMP will replace Expr( colList ) with {:SAT Verbal, :SAT Math}. The outer Eval() function will cause JMP to evaluate the entire New Column statement after the replacement has occurred.
/* Open a sample data table */
dt = Open("$SAMPLE_DATASATByYear.jmp");
/* Establish a list of columns */
colList = {:SAT Verbal, :SAT Math};
Eval( //Evaluate New Column expression after replacement
Eval Expr( //Return expression with the evaluated value
dt << New Column("SAT Total",
Numeric,
Continuous,
Formula( Sum( Expr( colList ) ) )
)
)
);
Figure 8.17 Column Formula Showing Variable Replaced with List Values
Question 8.16: How can I add multiple columns with formulas in a loop? I want one new column for each continuous column that calculates the mean by a grouping column.
In this example, the user does not know how many columns they will need to create because it will depend upon how many numeric, continuous columns there are in the table. Using the Get Column Names() function, we can obtain a list of the column names of all the numeric, continuous columns in the table. We can create the loop based upon the number of items in that list.
Just as in Question 8.15, we use the Eval(), Eval Expr(), and Expr() functions to replace any JSL variable(s) with the evaluated value.
/* Open a sample data table */
dt = Open( "$SAMPLE_DATABig Class.jmp" );
/* Obtain a list of all the numeric, continuous column names as strings */
colList = dt << Get Column Names( Continuous, String );
/* Loop through each string in the list */
For( i = 1, i <= N Items( colList ), i++,
Eval( //Evaluate New Column expression after replacement
Eval Expr( //Return expression with the evaluated value
dt << New Column("Mean of " || colList[i] || " by Age",
Numeric,
Continuous,
Formula( Col Mean( As Column( Expr( colList[i] ) ), :age ) )
)
)
)
);
In chapter 4, we discussed two functions for column name resolution: As Column() and Column(). In the preceding code, we used As Column(). The following script will accomplish the same goal but demonstrates using the Column() function with the Row() subscript. Remember that the Column() function returns a reference to the column as a whole. Because the formula evaluates on each row, the Row() function is added as a subscript to reference the current row, which is controlled by the formula evaluation.
/* Open a sample data table */
dt = Open( "$SAMPLE_DATABig Class.jmp" );
/* Obtain a list of all the numeric, continuous column names as strings */
colList = dt << Get Column Names( Continuous, String );
/* Loop through each string in the list */
For( i = 1, i <= N Items( colList ), i++,
Eval( //Evaluate New Column expression after replacement
Eval Expr( //Return expression with the evaluated value
dt << New Column( "Mean " || colList[i] || " by Age",
Numeric,
Continuous,
Formula(Col Mean( Column( Expr( colList[i] ) )[Row()], :age ) )
)
)
)
);
Figure 8.18 Formula for Mean Height by Age Using As Column()
Figure 8.19 Formula for Mean Weight by Age Using As Column()
Figure 8.20 Formula for Mean Height by Age Using Column() with Row() Subscript
Figure 8.21 Formula for Mean Weight by Age Using Column() with Row() Subscript
New in JMP 14, the Get Data Table List() function returns a list of references to the open data tables. Although invisible tables are included, private tables are not included in the list that is returned.
/* Open some sample tables */
Open("$SAMPLE_DATAAir Traffic.jmp");
Open("$SAMPLE_DATAAircraft Incidents.jmp");
Open("$SAMPLE_DATAAirline Delays.jmp");
/* Obtain a list of visible and invisible data tables */
dtList = Get Data Table List();
Figure 8.22 Log After the Preceding Script Is Run
Prior to JMP 14, a For() loop was needed to access each table name and then insert the name into a list. The N Table() function returns the number of tables open. The Get Name command returns the name of the data table. Insert Into() inserts the value in the second argument (the data table name, in this example) into the list specified in the first argument.
/* Initialize an empty list where the table names will be stored. */
dtNames = {};
/* Loop through each open data table and insert its name into the list. */
For( i = 1, i <= N Table(), i++,
Insert Into( dtNames, Data Table( Data Table( i ) ) )
);
/* Print names in the log. */
Print( dtNames );
Figure 8.23 Log After the Preceding Script Is Run
In JMP 14, there is a new feature called Multiple File Import. As we discussed in chapter 1, you can perform the import and stacking of the data interactively and then extract the Source table script to re-create the same action.
If you don’t have JMP 14 yet, you can still import and concatenate multiple files. It will just take a little more scripting.
This example uses the Pick File() function to prompt the user to select the four UN Malaria CSV files in the sample data. The list returned includes the full path for each file. Then the script loops through the list of the files to open and concatenate each file into a main table.
/* Create a list of all the files to be concatenated. */
fileList =Pick File(
"Select the 4 UN Malaria CSV Files:",
"$SAMPLE_IMPORT_DATA",
{"CSV Files|csv"},
1,
0,
"",
"multiple"
);
/* Loop through each file */
For( i = 1, i <= N Items( fileList ), i++,
/* Import each file. */
dt = Open( fileList[i] );
/* Add a column containing the table name. */
dt << New Column( "File Name", Character, Set Each Value( dt << Get Name ) );
/* If this is the first file, assign it to mainDt and give it a name. */
If( i == 1,
mainDt = dt;
mainDT << Set Name( "Concatenated Files" );
,
/* Else, concatenate the data to the main table. */
mainDt = mainDt << Concatenate( dt, "Append to First Table" );
/* Close the most recently opened table. */
Close( dt, NoSave );
Wait( 0 );
);
);
At the end of each loop, the data table is closed. Because JMP considers the closing of a data table a system-related task, it is possible that the table is actually closed at a later time than you expect. Because we are reassigning the global variable that represents a different data table in each iteration of the loop, it is necessary for JMP to close the data table before starting the next iteration. To ensure that JMP has time to complete any system-related task, a Wait() statement was added with an argument of zero. This tells JMP to pause until pending system-related tasks are completed. Further information about the Wait() function can be found in the Scripting Index (Help ► Scripting Index).
Figure 8.24 Table Result from Concatenation
Question 8.19: I have imported several files and performed some data cleanup. How can I concatenate all the open tables without knowing exactly how many tables are open?
If you have JMP 14, you can use the new Get Data Table List() to return a list of references to all the open visible and invisible tables. This list can then be used to concatenate all the files at once.
/* Open some sample data tables and make invisible */
Open( "$SAMPLE_Import_DATA/UN Malaria 2009.csv", Invisible );
Open( "$SAMPLE_Import_DATA/UN Malaria 2010.csv", Invisible );
Open( "$SAMPLE_Import_DATA/UN Malaria 2011.csv", Invisible );
Open( "$SAMPLE_Import_DATA/UN Malaria 2012.csv", Invisible );
/* Obtain a list of open data tables. */
dtList = Get Data Table List();
/* Assign one table to be primary. */
main = dtList[1];
/* Remove the main table from the list. */
Remove From( dtList, 1 );
/* Concatenate the remaining tables to main. */
main << Concatenate( dtList,
"Create Source Column",
Output Table Name( "Concatenated Files" )
);
If you do not yet have JMP 14, you can create a list of open data tables using the NTables() function.
/* Open some sample data tables and make invisible*/
Open( "$SAMPLE_Import_DATA/UN Malaria 2009.csv", Invisible );
Open( "$SAMPLE_Import_DATA/UN Malaria 2010.csv", Invisible );
Open( "$SAMPLE_Import_DATA/UN Malaria 2011.csv", Invisible );
Open( "$SAMPLE_Import_DATA/UN Malaria 2012.csv", Invisible );
/* Assign one table to be primary. */
main = Data Table( 1 );
/* Establish a list variable to hold the data table references */
dtList = {};
/* Loop through the remaining open tables */
For( i = 2, i <= N Table(), i++,
/* Insert a table reference into the list */
Insert Into( dtList, Data Table( Data Table( i ) << Get Name ) )
);
/* Concatenate the tables to main */
main << Concatenate( dtList,
"Create Source Column",
Output Table Name( "Concatenated Files" )
);
Figure 8.25 Table Result from Concatenation
