Documentation

This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

join

Combine two tables or timetables by rows using key variables

Syntax

C = join(A,B)
C = join(A,B,Name,Value)
[C,ib] = join(___)

Description

example

C = join(A,B) merges tables or timetables A and B by matching up rows, using all the variables with the same names as key variables. The key values must be common to both A and B but can appear in different orders. A and B can be tables, timetables, or one of each.

  • If A is a table, then join returns C as a table.

  • If A is a timetable, then join returns C as a timetable.

join retains all the variables from A and appends the corresponding contents from the nonkey variables of B.

The row labels of A and B can be key variables. Row labels are the row names of a table, or the row times of a timetable.

example

C = join(A,B,Name,Value) joins the tables or timetables with additional options specified by one or more Name,Value pair arguments.

For example, you can specify which variables to use as key variables.

example

[C,ib] = join(___) also returns an index vector ib such that each element of ib identifies the row in B that corresponds to that row in C. You can use this syntax with any of the input arguments of the previous syntaxes.

Examples

collapse all

Create a table, A.

A = table({'Janice','Jonas','Javier','Jerry','Julie'}',[1;2;1;2;1],...
    'VariableNames',{'Employee' 'Department'})
A=5x2 table
    Employee    Department
    ________    __________

    'Janice'    1         
    'Jonas'     2         
    'Javier'    1         
    'Jerry'     2         
    'Julie'     1         

Create a table, B, with a variable in common with A.

B = table([1 2]',{'Mary' 'Mona'}',...
    'VariableNames',{'Department' 'Manager'})
B=2x2 table
    Department    Manager
    __________    _______

    1             'Mary' 
    2             'Mona' 

Create a new table, C, containing data from tables A and B. Use the join function to repeat and append Manager data from table B to the data from table A, based on the key variable, Department.

C = join(A,B)
C=5x3 table
    Employee    Department    Manager
    ________    __________    _______

    'Janice'    1             'Mary' 
    'Jonas'     2             'Mona' 
    'Javier'    1             'Mary' 
    'Jerry'     2             'Mona' 
    'Julie'     1             'Mary' 

Create a table, A.

A = table([5;12;23;2;6],...
    {'cereal';'pizza';'salmon';'cookies';'pizza'},...
    'VariableNames',{'Age','FavoriteFood'},...
    'RowNames',{'Amy','Bobby','Holly','Harry','Sally'})
A=5x2 table
             Age    FavoriteFood
             ___    ____________

    Amy       5     'cereal'    
    Bobby    12     'pizza'     
    Holly    23     'salmon'    
    Harry     2     'cookies'   
    Sally     6     'pizza'     

Create a table, B, with one variable in common with A.

B = table({'cereal';'cookies';'pizza';'salmon';'cake'},...
    [110;160;140;367;243],...
    {'B';'D';'B-';'A';'C-'},...
    'VariableNames',{'FavoriteFood','Calories','NutritionGrade'})
B=5x3 table
    FavoriteFood    Calories    NutritionGrade
    ____________    ________    ______________

    'cereal'        110         'B'           
    'cookies'       160         'D'           
    'pizza'         140         'B-'          
    'salmon'        367         'A'           
    'cake'          243         'C-'          

Create a new table, C, with data from tables A and B. The variable in common, FavoriteFood, is used as a key variable by the join function.

C = join(A,B)
C=5x4 table
             Age    FavoriteFood    Calories    NutritionGrade
             ___    ____________    ________    ______________

    Amy       5     'cereal'        110         'B'           
    Bobby    12     'pizza'         140         'B-'          
    Holly    23     'salmon'        367         'A'           
    Harry     2     'cookies'       160         'D'           
    Sally     6     'pizza'         140         'B-'          

Table C does not include information from the last row of table B about 'cake' because there is no corresponding entry in table A.

Create a table, A.

A = table([10;4;2;3;7],[5;4;9;6;1],[10;3;8;8;4])
A=5x3 table
    Var1    Var2    Var3
    ____    ____    ____

    10      5       10  
     4      4        3  
     2      9        8  
     3      6        8  
     7      1        4  

Create a table, B, giving Var2 of table B the same contents as Var2 from table A.

B = table([6;1;1;6;8],[5;4;9;6;1])
B=5x2 table
    Var1    Var2
    ____    ____

    6       5   
    1       4   
    1       9   
    6       6   
    8       1   

Create a new table, C, containing data from tables A and B. Use Var2 in tables A and B as the key variable to the join function.

C = join(A,B,'Keys','Var2')
C=5x4 table
    Var1_A    Var2    Var3    Var1_B
    ______    ____    ____    ______

    10        5       10      6     
     4        4        3      1     
     2        9        8      1     
     3        6        8      6     
     7        1        4      8     

join adds a unique suffix to the nonkey variable, Var1, to distinguish the data from tables A and B.

Create a new table with data from tables A and B. If any nonkey variables have the same name in both tables, keep only the copy from table A.

Create a table, A.

A = table([10;4;2;3;7],[5;4;9;6;1])
A=5x2 table
    Var1    Var2
    ____    ____

    10      5   
     4      4   
     2      9   
     3      6   
     7      1   

Create a table, B, giving Var2 of table B the same contents as Var2 from table A.

B = table([6;1;1;6;8],[5;4;9;6;1],[10;3;8;8;4])
B=5x3 table
    Var1    Var2    Var3
    ____    ____    ____

    6       5       10  
    1       4        3  
    1       9        8  
    6       6        8  
    8       1        4  

Create a new table, C, with data from tables A and B. Use Var2 as a key variable to the join function and keep only the copy of Var1 from table A. The output table C does not contain the Var1 data from table B.

C = join(A,B,'Keys','Var2','KeepOneCopy','Var1')
C=5x3 table
    Var1    Var2    Var3
    ____    ____    ____

    10      5       10  
     4      4        3  
     2      9        8  
     3      6        8  
     7      1        4  

Create a table, A.

A = table(['M';'M';'F';'F';'F'],[38;43;38;40;49],...
    'VariableNames',{'Gender' 'Age'},...
    'RowNames',{'Smith' 'Johnson' 'Williams' 'Jones' 'Brown'})
A=5x2 table
                Gender    Age
                ______    ___

    Smith       M         38 
    Johnson     M         43 
    Williams    F         38 
    Jones       F         40 
    Brown       F         49 

Create a table, B, such that the rows of A and the rows of B have a one-to-one correspondence.

B = table([64;69;67;71;64],...
    [119;163;133;176;131],...
    [122 80; 109 77; 117 75; 124 93; 125 83],...
    'VariableNames',{'Height' 'Weight' 'BloodPressure'},...
    'RowNames',{'Brown' 'Johnson' 'Jones' 'Smith' 'Williams'})
B=5x3 table
                Height    Weight    BloodPressure
                ______    ______    _____________

    Brown       64        119       122     80   
    Johnson     69        163       109     77   
    Jones       67        133       117     75   
    Smith       71        176       124     93   
    Williams    64        131       125     83   

Create a new table, C, with data from tables A and B. Use the row names as keys to the join function.

C = join(A,B,'Keys','RowNames')
C=5x5 table
                Gender    Age    Height    Weight    BloodPressure
                ______    ___    ______    ______    _____________

    Smith       M         38     71        176       124     93   
    Johnson     M         43     69        163       109     77   
    Williams    F         38     64        131       125     83   
    Jones       F         40     67        133       117     75   
    Brown       F         49     64        119       122     80   

The rows of C are in the same order as A.

Create a table, A.

A = table([10;4;2;3;7],[5;4;9;6;1],[10;3;8;8;4])
A=5x3 table
    Var1    Var2    Var3
    ____    ____    ____

    10      5       10  
     4      4        3  
     2      9        8  
     3      6        8  
     7      1        4  

Create a table, B, giving Var2 of table B the same contents as Var1 from table A, but in a different order.

B = table([6;1;1;6;8],[2;3;4;7;10])
B=5x2 table
    Var1    Var2
    ____    ____

    6        2  
    1        3  
    1        4  
    6        7  
    8       10  

Create a new table, C, containing data from tables A and B. Use Var1 from table A with Var2 from table B as key variables to the join function.

[C,ib] = join(A,B,'LeftKeys',1,'RightKeys',2)
C=5x4 table
    Var1_A    Var2    Var3    Var1_B
    ______    ____    ____    ______

    10        5       10      8     
     4        4        3      1     
     2        9        8      6     
     3        6        8      1     
     7        1        4      6     

ib = 

     5
     3
     1
     2
     4

C is the horizontal concatenation of A and B(ib,2).

Create two timetables that have the same row times but different variables.

Traffic = [0.8 0.9 0.1 0.7 0.9]';
Noise = [0 1 1.5 2 2.3]';
A = timetable(hours(1:5)',Traffic,Noise)
A=5x2 timetable
    Time    Traffic    Noise
    ____    _______    _____

    1 hr    0.8          0  
    2 hr    0.9          1  
    3 hr    0.1        1.5  
    4 hr    0.7          2  
    5 hr    0.9        2.3  

Distance = [0.88 0.86 0.91 0.9 0.86]';
B = timetable(hours(1:5)',Distance)
B=5x1 timetable
    Time    Distance
    ____    ________

    1 hr    0.88    
    2 hr    0.86    
    3 hr    0.91    
    4 hr     0.9    
    5 hr    0.86    

Merge the timetables. join uses the row times as the key variables.

C = join(A,B)
C=5x3 timetable
    Time    Traffic    Noise    Distance
    ____    _______    _____    ________

    1 hr    0.8          0      0.88    
    2 hr    0.9          1      0.86    
    3 hr    0.1        1.5      0.91    
    4 hr    0.7          2       0.9    
    5 hr    0.9        2.3      0.86    

Create a timetable and a table.

Measurements = [0.13 0.22 0.31 0.42 0.53 0.57 0.67 0.81 0.90 1.00]';
Device = ['A';'B';'A';'B';'A';'B';'A';'B';'A';'B'];
A = timetable(seconds(1:10)',Measurements,Device)
A=10x2 timetable
     Time     Measurements    Device
    ______    ____________    ______

    1 sec     0.13            A     
    2 sec     0.22            B     
    3 sec     0.31            A     
    4 sec     0.42            B     
    5 sec     0.53            A     
    6 sec     0.57            B     
    7 sec     0.67            A     
    8 sec     0.81            B     
    9 sec      0.9            A     
    10 sec       1            B     

Device = ['A';'B'];
Accuracy = [0.023;0.037];
B = table(Device,Accuracy)
B=2x2 table
    Device    Accuracy
    ______    ________

    A         0.023   
    B         0.037   

Merge the timetable and table. Device is the key variable because both A and B have a variable with that name. C is a timetable.

C = join(A,B)
C=10x3 timetable
     Time     Measurements    Device    Accuracy
    ______    ____________    ______    ________

    1 sec     0.13            A         0.023   
    2 sec     0.22            B         0.037   
    3 sec     0.31            A         0.023   
    4 sec     0.42            B         0.037   
    5 sec     0.53            A         0.023   
    6 sec     0.57            B         0.037   
    7 sec     0.67            A         0.023   
    8 sec     0.81            B         0.037   
    9 sec      0.9            A         0.023   
    10 sec       1            B         0.037   

Input Arguments

collapse all

Input tables, specified as tables, timetables, or as one of each. For all key variables, each row of A must match exactly one row in B.

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside single quotes (' '). You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: 'Keys',2 uses the second variable in A and the second variable in B as key variables.

collapse all

Variables to use as keys, specified as the comma-separated pair consisting of 'Keys' and a positive integer, vector of positive integers, character vector, cell array of character vectors, logical vector, or 'RowNames'.

You cannot use the 'Keys' name-value pair argument with the 'LeftKeys' and 'RightKeys' name-value pair arguments.

Row labels can be key variables. Row labels are the row names of a table or the row times of a timetable. You can specify 'Keys' as the row labels only, as one or more variables in A and B, or as row labels and variables together.

If you specify the value 'RowNames', then join uses the row names of A and row names of B as keys. In this case, there must be a row in B for every row in A. You cannot specify 'RowNames' when A is a timetable.

Example: 'Keys',[1 3] uses the first and third variables from A and B as key variables.

Variables to use as keys in A, specified as the comma-separated pair consisting of 'LeftKeys' and a positive integer, vector of positive integers, character vector, cell array of character vectors, or logical vector.

You must use the 'LeftKeys' name-value pair argument in conjunction with the 'RightKeys' name-value pair argument. 'LeftKeys' and 'RightKeys' both must specify the same number of key variables. join pairs key values in A and B based on their order.

Row labels can be key variables. Row labels are the row names of a table or the row times of a timetable. You can specify 'LeftKeys' as the row labels of A, as one or more variables in A, or as row labels and variables together.

Example: 'LeftKeys',1 uses only the first variable in A as a key variable.

Variables to use as keys in B, specified as the comma-separated pair consisting of 'RightKeys' and a positive integer, vector of positive integers, character vector, cell array of character vectors, or logical vector.

You must use the 'RightKeys' name-value pair argument in conjunction with the 'LeftKeys' name-value pair argument. 'LeftKeys' and 'RightKeys' both must specify the same number of key variables. join pairs key values in A and B based on their order.

Row labels can be key variables. Row labels are the row names of a table or the row times of a timetable. You can specify 'RightKeys' as the row labels of B, as one or more variables in B, or as row labels and variables together.

Example: 'RightKeys',3 uses only the third variable in B as a key variable.

Variables from A to include in C, specified as the comma-separated pair consisting of 'LeftVariables' and a positive integer, vector of positive integers, character vector, cell array of character vectors, or logical vector.

You can use 'LeftVariables' to include or exclude key variables, as well as nonkey variables, from C. However, you cannot include row names or row times from A, because they are not variables.

By default, join includes all variables from A.

Variables from B to include in C, specified as the comma-separated pair consisting of 'RightVariables' and a positive integer, vector of positive integers, character vector, cell array of character vectors, or logical vector.

You can use 'RightVariables' to include or exclude key variables, as well as nonkey variables, from C. However, you cannot include row names or row times from B, because they are not variables.

By default, join includes all variables from B except the key variables.

Variables for which join retains only the copy from A, specified as the comma-separated pair consisting of 'KeepOneCopy' and a character vector or a cell array of character vectors that specify variable names.

Key variables appear once in C, but if nonkey variables with identical names occur in A and B, then join retains both copies in C by default. Use the 'KeepOneCopy' name-value pair to retain only the copy from A.

Example: 'KeepOneCopy',Var2 keeps only the copy from A of the nonkey variable Var2.

Output Arguments

collapse all

Merged data from A and B, returned as a table or a timetable. The table, C, contains one row for each row in A, appearing in the same order.

join creates C by horizontally concatenating A(:,LeftVars) and B(ib,RightVars). By default, LeftVars is all the variables of A, and RightVars is all the nonkey variables from B. Otherwise, LeftVars consists of the variables specified by the 'LeftVariables' name-value pair argument, and RightVars consists of the variables specified by the 'RightVariables' name-value pair argument.

If A and B contain nonkey variables with the same name, join adds a unique suffix to the corresponding variable names in C, unless you specify the 'KeepOneCopy' name-value pair argument.

If A is a table, then C is also a table. If A is a timetable and B is either a timetable or a table, then C is a timetable.

You can store additional metadata in C, such as descriptions, variable units, variable names, and row names. For more information, see the Properties sections of table or timetable.

Index to B, returned as a column vector. Each element of ib identifies the row in B that corresponds to that row in the output table or timetable, C.

More About

collapse all

Key Variable

Variable used to match and combine data between input tables A and B.

Key Value

Entry in a key variable of A.

Algorithms

The join function first finds one or more key variables. Then, join uses the key variables to find the row in input table B that matches each row in input table A, and combines those rows to create a row in output table C.

  • If there is a one-to-one mapping between key values in A and B, then join sorts the data in B and appends it to table A.

  • If there is a many-to-one mapping between key values in A and B, then join sorts and repeats the data in B before appending it to table A.

  • If there is data in a key variable of B that does not map to a key value in A, then join does not include that data in the output table, C.

Extended Capabilities

Introduced in R2013b

Was this topic helpful?