Data Cleansing in Tables

I want to clean a data table and create a new table/overwrite the incorrect one. To create a dummy case run following code to create a table

test = table();
test.index = ['11'; '12'; '13'; '14'; '15';'16'];
test.id = ['A'; 'B'; 'C'; 'D';'E';'Z'];
test.id2 = ['B'; 'C'; 'D'; 'E'; 'F'; 'Z'];
test.price = [1;2;3;4;5;6];
test.price1 = [10;20;30;40;50;60];

The output of the table is

index    id    id2    price    price1
    _____    __    ___    _____    ______

     11      A      B       1        10  
     12      B      C       2        20  
     13      C      D       3        30  
     14      D      E       4        40  
     15      E      F       5        50  
     16      Z      Z       6        60  

In above table index of table is properly aligned with id2 and price, and id is properly aligned with price1. Based on this knowledge I want to create a new table with correct data. Desired Output:

index     id    price    price1
    _______    __    _____    ______

    {[NaN]}    A      NaN       10  
    {'11' }    B        1       20  
    {'12' }    C        2       30  
    {'13' }    D        3       40  
    {'14' }    E        4       50  
    {'15' }    F        5      NaN  
    {'16' }    Z        6       60 

Since for id A we do not index so we will assign it NaN Similarly price is also not avaliable so we can keep that NaN but we do have price1 so we keep that. For id B we have index and price from 1st row of faulty table and we can extract price1 from the next row of faulty table to create 2nd row of desired output. What is optimal way to achieve this using MATLAB code. Also note that distinct values from id and id2 should all be in id of desired output.

Answer 1

[Sindar]

test_rejoined = outerjoin(test(:,[1 3 4]),test(:,[2 5]),'LeftKeys','id2','RightKeys','id','MergeKeys',true);

• separate table into two tables, each with aligned columns
• use outerjoin to merge these tables based on the shared key

edited: strings > chars since older versions of Matlab often only accepts chars (single quotes) not strings (double quotes). In 2017b, table variable names must be passed as chars

[Furqan Hashim]

@Sindar I am getting an error while using your soultion as follows:

Error using tabular/outerjoin (line 141)
Table variable subscripts must be real positive integers, logicals, character vectors, or cell arrays of character vectors.

[Sindar]

is the test data erroring? If so, it may be an issue with the Matlab version, and you should check the 2017b outerjoin documentation

If not, can you check whether the actual table has different data types or shape? This'll give you a good picture:

summary(test)

[Furqan Hashim]

@Sindar the solution you provided does not works on 2017b. I've already mentioned the version that I am using in my post. I've checked the documentation the input variables are legal and can be passed as far as the data type is concerned see below results from summary(test)

Variables:

    index: 6×2 char

    id: 6×1 string

    id2: 6×1 string

    price: 6×1 double

        Values:

            Min         1    
            Median    3.5    
            Max         6    

    price1: 6×1 double

        Values:

            Min       10      
            Median    35      
            Max       60      

[Sindar]

Ok, reading the old documentation, it doesn't list strings as a valid option for LeftKeys and RightKeys. So, try this:

test_rejoined = outerjoin(test(:,[1 3 4]),test(:,[2 5]),'LeftKeys','id2','RightKeys','id','MergeKeys',true);

edit: rereading the error message you posted, I'm confident this is the problem. I sometimes forget how recent strings are (2016b) and that they weren't integrated into the rest of Matlab immediately. Sorry about that

[Furqan Hashim]

Ok no worries can you post this as an answer so I can accept it.

[Sindar]

edited the answer since it will work in 2017b and newer versions