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:
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:
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 aligns the rows in the two tables by matching column values.
Caution: If the matched columns do not result in unique rows, proceed carefully.
Notes:
• The columns used for matching must have the same data type (numeric, character, or row state).
• You can join a data table to itself, in order to remove duplicate values or rows from the data table.
• You can use a virtual join as an alternative to creating a new data table. See Virtually Join Data Tables.
To join tables with a unique identifier
You have two files of information about hot dogs. The second file contains one column, Size, that is missing from the first file. Use Join with a matching column to combine the data tables.
1. Select Help > Sample Data Library and open Hot Dogs.jmp and Hot Dogs2.jmp.
The Product Name column is a unique identifier for the rows in your data tables. Each table has 54 unique product names.
2. From the Hot Dogs data table, select Tables > Join.
3. In the Join ‘Hot Dogs’ with list, select Hot Dogs2.
4. Select By Matching Columns in the Matching Specification area.
5. From the Hot Dogs and Hot Dogs2 lists, select Product Name.
6. Click Match.
7. In the Options, select Merge same column names. When the merge columns is used the Match Flag is automatically selected.
8. Select Keep dialog open in the lower left corner of the window so that you can easily return to the Join window in the case that the joined data table is not as you expected.
9. Click Apply.
Figure 6.29 Joined Table by Matching Columns
The resulting table now includes the Size column that was in the Hot Dogs2 table. The Match Flag column indicates that the data originated from both tables for all rows.
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
