Subscribe Bookmark



Jun 23, 2011

New in JMP 11: In-the-flow column transformations

JMP is all about data exploration, and a big part of exploring is not knowing the questions up front. JMP 11 adds a feature called “transform columns” that allows you to create a temporary computed column within almost any column list in JMP. That means you can create a new column right in Graph Builder or Tabulate or even the Local Data Filter without having to go back to the data table and interrupt the flow of exploration.

In the JMP Discovery 2013 keynote, I showed several transformations using data from the SAS solar farm. The data table's date/time data is at 15-minute intervals, but I wanted to look at the data by month. So I right-clicked on the Date Time column and applied a Month Year transformation.


And I wanted to look at the efficiency of the panels, so I selected Power and Irradiance (the amount of solar energy at the site), and applied the Ratio transformation to get an efficiency measure.


With those operations, I get two new columns to use in the graph: "Month Year" and "Power/Irradiance."


Using box plots, I can see there is a fairly consistent monthly median efficiency and some extreme outliers, which turned out to be bad irradiance values.


At this point, the columns exist only in Graph Builder and not in the data table, but I can right-click on the transform columns option and choose “Add to Data Table” if I want to keep them as real formula columns.

As the drop-down menus in the above pictures suggest, there are several categories of transformations to apply and many transformation functions within each category. Most are straightforward mathematical transformations, such as Ratio, Log, and Square Root. The Row submenu, however, contains some row-wise transformations like row number, cumulative sum and an exponentially weighted moving average (EWMA).

For example, the JMP sample data file contains only one column, which is a series of historical flow levels for the Nile River. Using transform columns, we can explore the data over time in Graph Builder by adding columns for row number and the moving average.


You can learn more about this and other JMP 11 features by watching the on-demand Mastering JMP webcasts.

Community Member

Mike Clayton wrote:

Very nice. Looking ahead, how rich might GraphBuilder for I-Pad get by itself only starting with JMP table and not running JMP on the tablet?


Xan Gregg wrote:

Thanks, Mike. The iPad app continues to pick up new features of desktop Graph Builder, such as new summary statistics and the features shown in my JMP 11 preview blog post. Data Filter was also added to the JMP 11 iPad app. For other JMP output, the interactive HTML 5 export will be increasingly a good option for any tablet.

Community Member

Ed Boyer wrote:

There seems to be an error with the "year quarter" transform. It does not return values for dates in month 12. Is there a new script available to correct this error?


Xan Gregg wrote:

Thanks for the report Ed. Looks like the Year Quarter formula is using Round when it should use Floor. You can change the formula if you use Add to Data Table to make it a formula column first. Otherwise, it will be fixed in a maintenance update.