In JMP 19, select Help > Scripting Index from the menu bar and look for JSL functions added specifically to version 19.
Various functions have been added, but as you can see in the red box in the image below , many Col functions have been added .
In this article, we will introduce the new Col() function added in JMP 19 and the benefits of using it, along with some example data.
What is the Col() function?
The Col() function returns summary statistics for a specified column.
For example, if you specify Col Mean(Column1) for a continuous column "Column1", the mean value of "Column1" will be returned for all cells.
Additionally , many Col() functions allow you to specify a By variable as an argument, which returns summary statistics for each group.
For example, if you specify Col Mean(height (inches), age) for the JMP sample data "Big Class.jmp," you can find the average height for each age (12 to 17), which is the second argument.
With this in mind, we will introduce the new Col() function added in JMP 19. We will explain Col At() and Col Score() with examples.
Col At function
The Col At() function is an enhanced version of the Lag() function, which can return data from a few rows back. It can be used like the Lag() function, which allows you to specify a By variable.
Usage Examples
Data : Power consumption measured every minute for a household over three days from January 1st to 3rd
Purpose : To find out the time periods with the largest changes in power, calculate the difference between the amount of power at a certain time and the amount of power one minute earlier (Lag(1)), and examine the difference on a graph.
In the data below, "Power" is measured for each "Date" and "Time". A newly created column, "Power_Diff", is used to achieve this purpose, using the Col At() function.
(Please refer to the attached file "Example_ColAt.jmp" in this blog.)
Col At(col, index, byVar, Relative(1)) returns the value of col at row position index within the byVar group.
In this example, the power value for the previous row (-1) is returned for each day.
This function is used to find the lag on a certain day (power in row n - power in row (n-1)).
The traditional Lag() function cannot find lags within groups, but the Col At() function can.
The graph below shows the difference in time by day using a calculated column. You can see that on January 3rd, there are times when the difference is particularly large, after 12:00.
Note: The argument Relative(1) can be used to return the index number of previous values as shown above. If this argument is not included, the first and last row values in the byVar group will be returned. For details, please refer to the "First Power" and "Last Power" columns in the attached data.
Col Score function
The Col Score() function returns the rank (integer score) for a specified column. By specifying a By variable, you can obtain the rank within a group.
Usage Examples
Data : 1500m running times and ball throwing distances of 30 men and women
Purpose : To determine the rankings of 1500m running times by gender and to examine the relationship between those rankings and the distance the ball can be thrown.
(Please refer to the attached file "Example_ColScore.jmp" for more details.)
Col Score(xCol, byVar) returns the ranking for the values of xCol for each byVar. In this example, we rank by gender based on 1500m running time.
Using the newly created column "Rank by Sex," we plotted the ball throw distance for each gender's ranking in a bar graph. We found that the higher the ranking for men, the longer the throw distance, but this trend was not observed for women.
Other added Col() functions
In addition to the above, JMP 19 adds the following Col() functions:
Unique N Neck : Returns the number of unique values in the column.
Col Interpolate : Returns the interpolated value of y for a given x position.
Col Sequence : Returns the position for a row within a byVar group (an enhanced version of the Row() function).
See the Script Index for more information.
These Col() functions are related to a feature called column scoping.But we'll talk about that feature another time.
by Naohiro Masukawa (JMP Japan)
Naohiro Masukawa - JMP User Community
