Examples of Joining Data Tables
The following sections provide examples using the Join command.
Example of Joining by Row Number
Joining tables by row number joins the two tables side by side. The new table has all of the columns from both tables, unless you specify to include only certain columns.
To join tables with an unequal number of rows
If the two tables that you want to join have an unequal number of rows, the new table contains values for the rows found in both tables.
1. Select Help > Sample Data Library and open Species1.jmp and Species2.jmp.
Notice that the Species1.jmp table has two rows, and the Species2.jmp table has four rows.
2. From the Species1.jmp table, select Tables > Join.
3. In the Join...with box, select Species2.
4. From the Matching Specification area, select By Row Number.
5. Click OK.
Figure 6.23 Joined Tables by Row Number
If one table with two rows is joined with a table with four rows, then the new table contains four rows.
To join columns with the same name
If the two tables have column names that are the same, the names of these columns in the new table appear as “column name of table name.” For example, suppose that you want to combine the eight rows from the Trial1.jmp and Trial2.jmp data tables shown in Figure 6.24 into a single table. You want to combine them so that the new table contains all of the columns from both tables.
1. Select Help > Sample Data Library and open Trial1.jmp andTrial2.jmp.
2. From the Trial1.jmp data table, select Tables > Join.
3. In the Join...with box, select Trial2.
4. From the Matching Specification menu, select By Row Number.
5. Click OK.
Figure 6.24 Original Tables and the Joined Table
A column name can be the same in the two original tables. The output column name is then qualified by the source table name. For example, the column names in the new table appear as <variable name> of table name.
To join only specified columns
Suppose that you do not want all of the columns from the original data tables to be in the joined table. Proceed as follows:
1. Select Help > Sample Data Library and open Trial1.jmp and Trial2.jmp.
2. From the Trial1.jmp data table, select Tables > Join.
3. In the Join...with box, select Trial2.
4. From the Matching Specification menu, select By Row Number.
5. Click Select columns for joined table to specify the subset of columns that you want to include.
6. In the Source Columns list, select popcorn and yield from the Trial1 list and select yield from the Trial2 list.
Because identical data exists in the popcorn column of both tables, you need to select only one column.
7. Click Select.
8. Click OK.
Figure 6.25 Joining Only Specified Columns
Examples of a Cartesian Join
When doing a Cartesian join, JMP joins two tables in a Cartesian fashion: the new table consists of all possible combinations of the rows from two original tables. This creates cases in the output table where there are one case for each combination of column values.
Simple Example of a Cartesian Join
1. Select Help > Sample Data Library and open Species1.jmp and Species2.jmp.
2. From the Species1.jmp table, select Tables > Join.
3. In the Join...with box, select Species2.
4. From the Matching Specification menu, select Cartesian Join.
5. Click OK.
Figure 6.26 Joining Tables Using Cartesian Join
The data in Species1.jmp is crossed with the data in Species2.jmp to produce the joined table, which shows all combinations of the values in each set.
Complex Example of a Cartesian Join
In this example, use the Tables > Join command twice:
• The first join combines the Oil Amount.jmp table with the Batch.jmp table using the Cartesian option.
• The second join combines the resulting table (Cartesian oil amount + batch) with the Popcorn Type.jmp table and produces a final table with all tables joined.
1. Select Help > Sample Data Library and open Oil Amount.jmp, Batch.jmp, and Popcorn Type.jmp.
2. From the Oil Amount.jmp table, select Tables > Join.
3. In the Join...with box, select Batch.
4. From the Matching Specification menu, select Cartesian Join.
5. Under Output table name, type Oil Amount and Batch.
6. Click OK.
Figure 6.27 Oil Amount and Batch Joined Table
The joined table contains all of the columns from the Oil Amount.jmp and Batch.jmp tables. Add the Popcorn Type.jmp columns, as follows:
7. From the Oil Amount and Batch table that you just created, select Tables > Join.
8. In the Join...with box, select Popcorn Type.
9. From the Matching Specification menu, select Cartesian Join.
10. Click OK.
Figure 6.28 Oil Amount and Batch Joined with Popcorn Type
The final table contains all of the columns from all three original tables. Keep in mind that the number of rows produced by a Cartesian join is the product of the number of rows in the original tables.
Examples of Joining By Matching Columns
When you join data tables by matching columns, JMP finds specified column values that exist in both tables. All values associated with the specified column values are combined into a new data table.
In order to join by matching columns, the columns must have the same data type (numeric, character, or row state).
You can also join a data table to itself, in order to remove duplicate values or rows from the data table.
To join tables with the same rows in a different order
1. Select Help > Sample Data Library and open Students1.jmp and Students2.jmp.
The Students1.jmp data table contains names, ages, and sexes of the students. The Students2.jmp data table contains names, height, and weight of the students. Instead of working with two separate tables, you would like to combine the tables into one. Notice that the students’ names are not in the same order in both tables. For example, Alice is in row 7 and row 9.
2. From the Students1.jmp data table, select Tables > Join.
3. In the Join...with box, select Students2.
Because both tables have one column (name) that contains the same values, you need to tell JMP that they are matches. JMP then examines each of the values in the name column of the first table to determine whether there was a corresponding value in the second table’s name column. For example, it detects that Alice is located in both tables. It creates a name column in the new table with Alice as a value. It then takes the age and sex of Alice from table one and puts it in the new table. Then it takes the height and weight of Alice and puts them in the new table.
4. Select By Matching Columns in the Matching Specification area.
5. From the Students1 and Students2 lists, select name.
6. Click Match.
7. You want the new table to contain only one row for each name, so select the Drop multiples boxes for both tables.
8. Click OK.
Figure 6.29 Joined Table
To join tables with different numbers of rows and different column names
Suppose that Sarah and Joe are performing a popcorn experiment. They are popping different types of popcorn (gourmet and plain) in different amounts of oil. They are recording the amount (yield) of popcorn that is produced. Sarah gave you the first trial data in a file named Trial1.jmp. Joe gave you the second trial data in a file named Little.jmp. You want to combine the two tables into one table.
1. Select Help > Sample Data Library and open Trial1.jmp and Little.jmp.
2. From the Trial1.jmp table, select Tables > Join.
3. In the Join...with box, select Little.
You can see that three of the columns (popcorn, oil amt/oil, and batch) contain the same values in both tables. Identify these columns as matches. Also, because Sarah and Joe gave the oil and oil amt columns different names, you can tell JMP that oil amt and oil match.
4. Deselect Preserve main table order.
The joined table is sorted by matching columns, not by the order of data in Trial1.jmp,
5. Select By Matching Columns in the Matching Specification area.
6. From the Trial1 list, select popcorn, oil amt, and batch.
7. From the Little list, select popcorn, oil, and batch.
8. Click Match.
Looking at the two data tables, you can see that they have different numbers of rows. Trial1.jmp has values for eight experimental conditions, and Little.jmp has values for only four of those conditions. Sarah completed her experiment, but Joe only partially completed his experiment. You want the joined table to contain all of the rows in Trial1.jmp. even if that row in the Little.jmp table contains a missing value.
9. Select the Include non-matches boxes for both tables.
In the joined table, you want only one column for popcorn, one column for oil, and one column for batch. However, you want two columns for yield: one representing the yield from Trial1.jmp, and another representing the yield from Little.jmp.
10. Select the box beside Select columns for joined table.
11. From the Trial1 list, select all of the columns.
12. Click Select.
13. From the Little list, select yield.
14. Click Select.
Figure 6.30 Completed Join Window
15. Click OK.
Figure 6.31 Trial1.jmp and Little.jmp Joined
The joined table is sorted by the matching columns. Note that the yield column from the Little.jmp table (Yield of Little) has missing values indicating no matching values with the Trial1.jmp table.
To join a table to itself (to remove duplicate entries)
1. Select Help > Sample Data Library and open Coffee Shop Purchases.jmp.
You can see that some of the customers had the same drink on the same date. You want to consolidate these duplicate rows using Join.
2. Select Tables > Join.
3. In the Join...with box, select Coffee Shop Purchases.
4. Select By Matching Columns in the Matching Specification area.
5. From both Coffee Shop Purchases lists, select all three columns: Date, Customer, and Beverage.
6. Click Match.
7. Select the Drop multiples boxes for both tables (the Main Table and the With Table).
8. Type Coffee Shop Purchases Final for the Output table name.
9. Click OK.
Figure 6.32 Original and Joined Coffee Shop Purchases Data Tables
