Discovering JMP • Working with Your Data • Managing Data
Previous
 • 
Next
Managing Data
The commands on the Tables menu (and Tabulate on the Analyze menu) summarize and manipulate data tables into the format that you need for graphing and analyzing. This section describes five of these commands:
Summary
Creates a table that contains summary statistics that describe your data.
Tabulate
Provides a drag and drop workspace to create summary statistics.
Subset
Creates a table that contains a subset of your data.
Join
Joins the data from two data tables into one new data table.
Sort
Sorts your data by one or more columns.
For complete details about these and the other Tables menu commands, see Using JMP.
Requesting Summary Statistics
Summary statistics, such as sums and means, can instantly provide useful information about your data. For example, if you look at the annual profit of each company out of thirty-two companies, it’s difficult to compare the profits of small, medium, and large companies. A summary shows that information immediately.
Create summary tables by using either the Summary or Tabulate commands. The Summary command creates a new data table. As with any data table, you can perform analyses and create graphs from the summary table. The Tabulate command creates a report window with a table of summary data. You can also create a table from the Tabulate report.
Summary
A summary table contains statistics for each level of a grouping variable. For example, look at the financial data for computer and pharmaceutical companies. Suppose that you want to calculate the mean of sales and the mean of profits, for each combination of company type and size.
1.
Select Help > Sample Data Library and open Companies.jmp.
2.
Select Tables > Summary.
3.
Select Type and Size Co and click Group.
4.
Select Sales ($M) and Profits ($M) and click Statistics > Mean.
Completed Summary Window
5.
Click OK.
JMP calculates the mean of Sales ($M) and the mean of Profit ($M) for each combination of Type and Size Co.
Summary Table
The summary table contains the following:
•
There are columns for each grouping variable (in this example, Type, and Size Co).
•
The N Rows column shows the number of rows from the original table that correspond to each combination of grouping variables. For example, the original data table contains 14 rows corresponding to small computer companies.
•
There is a column for each summary statistic requested. In this example, there is a column for the mean of Sales ($M) and a column for the mean of Profits ($M).
The summary table is linked to the source table. Selecting a row in the summary table also selects the corresponding rows in the source table.
Tabulate
Use the Tabulate command to drag columns into a workspace, creating summary statistics for each combination of grouping variables. This example shows you how to use Tabulate to create the same summary information that you just created using Summary.
1.
Select Help > Sample Data Library and open Companies.jmp.
2.
Select Analyze > Tabulate.
Tabulate Workspace
3.
Select both Type and Size Co.
4.
Drag and drop them into the Drop zone for rows.
Dragging Columns to the Row Zone
5.
Right-click a heading and select Nest Grouping Columns.
The initial tabulation shows the number of rows per group.
Initial Tabulation
6.
Select both Sales ($M) and Profits ($M), and drag and drop them over the N in the table.
Adding Sales and Profit
The tabulation now shows the sum of Sales ($M) and the sum of Profits ($M) per group.
Tabulation of Sums
7.
The final step is to change the sums to means. Right-click Sum (either of them) and select Statistics > Mean.
Final Tabulation
The means are the same as those obtained using the Summary command. Compare Final Tabulation to Summary Table.
Creating Subsets
If you want to look closely at only part of your data table, you can create a subset. For example, suppose that you have already compared the sales and profits of big, medium, and small computer and pharmaceutical companies. Now you want to look at the sales and profits of only the medium-sized companies.
Creating a subset is a two-step process. First select the target data, and then extract the data into a new table.
Subsetting with the Subset Command
1.
Select Help > Sample Data Library and open Companies.jmp.
Selecting the Rows and Columns That You Want to Subset
2.
Select Rows > Row Selection > Select Where.
3.
Select Size Co in the column list box on the left.
4.
Enter medium in the text enter box.
5.
Click OK.
6.
Hold down the Ctrl key and select the Type, Sales ($M), and Profits ($M) columns.
Creating the Subset Table
7.
Select Tables > Subset to launch the Subset window.
Subset Window
8.
Select Selected columns to subset only the columns that you selected. You can also customize your subset table further by selecting additional options.
9.
Click OK.
The resulting subset data table has seven rows and three columns. For complete details about the Subset command, see Using JMP.
Subsetting with the Distribution Platform
Another way to create subsets uses the connection between platform results and data tables.
Example: Creating a Subset Using the Distribution Command
1.
Select Help > Sample Data Library and open Companies.jmp.
2.
Select Analyze > Distribution.
3.
Select Type and click Y, Columns.
4.
Click OK.
5.
Double-click on the histogram bar that represents Computer to create a subset table of the Computer companies.
Caution: This method creates a linked subset table. This means if you make any changes to the data in the subset table, the corresponding value changes in the source table.
Joining Data Tables
Use the Join option to combine information from multiple data tables into a single data table. For example, suppose that you have a data table containing results from an experiment on popcorn yields. In another data table, you have the results of a second experiment on popcorn yields. To compare the two experiments or to analyze the trials using both sets of results, you need to have the data in the same table. Also, the experimental data was not entered into the data tables in the same order. One of the columns has a different name, and the second experiment is incomplete. This means that you cannot copy and paste from one table into another.
Example: Joining Two Data Tables
1.
Select Help > Sample Data Library and open Trial1.jmp and Little.jmp.
2.
Click on Trial1.jmp to make it the active data table.
3.
Select Tables > Join.
4.
In the Join ‘Trial1’ With box, select Little.
5.
From the Matching Specification menu, select By Matching Columns.
6.
In the Source Columns boxes, select popcorn in both boxes, and then click Match.
7.
In the same way, match batch and oil amt to oil in both boxes.
Your matching columns do not have to have the same name.
8.
Select Include non-matches for both tables.
Since one experiment is partial, you want to include all rows, including any with missing data.
9.
To avoid duplicate columns, select the Select columns for joined table option.
10.
From Trial1, select all four columns and click Select.
11.
From Little, select only yield and click Select.
Completed Join Window
12.
Click OK.
Joined Table
Sorting Tables
Use the Sort command to sort a data table by one or more columns in the data table. For example, look at financial data for computer and pharmaceutical companies. Suppose that you want to sort the data table by Type, then by Profits ($M). Also, you want Profits ($M) to be in descending order within each Type.
1.
Select Help > Sample Data Library and open Companies.jmp.
2.
Select Tables > Sort.
3.
Select Type and click By to assign Type as a sorting variable.
4.
Select Profits ($M) and click By.
At this point, both variables are set to be sorted in ascending order. See the ascending icon next to the variables in Sort Ascending Icon.
Sort Ascending Icon
5.
To change Profits ($M) to sort in descending order, select Profits ($M) and click the descending button.
Change Profits to Descending
The icon next to Profits ($M) changes to descending.
6.
Select the Replace Table check box.
When selected, the Replace Table option tells JMP to sort the original data table instead of creating a new table with the sorted values. This option is not available if there are any open report windows created from the original data table. Sorting a data table with open report windows might change how some of the data is displayed in the report window, especially in graphs.
7.
Click OK.
The data table is now sorted by type alphabetically, and by descending profit totals within type.