How to extract certain rows from an excel sheet based on two categorical value columns?

Hello everyone!

I'm facing a problem by trying to extract just certain rows from an excel sheet using readtable, which I couldn't because of the way of the criterion. Hope I can find a solution here. Thanks in adavance.

The excel sheet has 9 columns with a multitude of rows (see attached file). The first column contains categorical values which are all numbers (many values of 1, many values of 2, and so on). The fifth column also contains categorical values such as 'Left', 'Right', 'Down', and 'Up', but also empty cells.

Suppose that for a row R1, the value in the first column is 1 and the fifth column is empty, while for a row R2 the value in the first column is also 1, but the fifth column contains the value "Right".

If in a row R the fifth column value is "Right", I want to extract that row as well as all other rows that the first column value is same with the first column value in row R, and write them in a new sheet.

I used the following, while I properly knew it wouldn't achieve all what I want:

T = readtable('196.xlsx','Sheet','196');
T(1:5,1:9);
data = T(:,{'id','name','frame','speed','turn','acceleration','xdistance','ydistance','time'});
data.turn = categorical(data.turn);
data0=data(data.turn=='Right',:);
filename = '196.xlsx';
writetable(data0,filename,'Sheet',2);

This wrote table with only rows with "Right" in the fith column, but instead I also need other rows that the fifth column is empty or has another value, but that the first column value is same with the first column value of a row that the fifth column value is "Right".

I'll apreciate your effort, if you could help me complete this code. Thanks!

Answer 1

[Voss]

You can try this:

T = readtable('196.xlsx','Sheet','196');
T(1:5,1:9);
data = T(:,{'id','name','frame','speed','turn','acceleration','xdistance','ydistance','time'});
data.turn = categorical(data.turn);
% data0=data(data.turn=='Right',:);
data0 = data(ismember(data{:,1},data{data.turn=='Right',1}),:);
filename = '196.xlsx';
writetable(data0,filename,'Sheet',2);

An example to demonstrate how it works:

data = table([1 2 3 2 1].',{'Right' 'Right' 'Left' '' 'Right'}.','VariableNames',{'id','turn'})

data = 5×2 table
    id       turn   
    __    __________

    1     {'Right' }
    2     {'Right' }
    3     {'Left'  }
    2     {0×0 char}
    1     {'Right' }

data.turn = categorical(data.turn);
data0 = data(ismember(data{:,1},data{data.turn=='Right',1}),:)

data0 = 4×2 table
    id       turn    
    __    ___________

    1     Right      
    2     Right      
    2     <undefined>
    1     Right      

[ADJE JEREMIE ALAGBE]

Thank you so much for your kind support.

I have tried your suggestion and the output sheet looks like attached screenshot; except the table header, all cells are empty. Do you know the reason? Please see it with me. Thanks again!

I will also attach the excel file, so that you may try the code directly on it.

[Image Analyst]

Uh, you didn't. Try again to attach the Excel workbook with the paperclip icon. And there is no "attached screenshot below" either.

[ADJE JEREMIE ALAGBE]

Thank you. Please check again, all related files are attached.

[Voss]

Use 'RIGHT' instead of 'Right':

data0 = data(ismember(data{:,1},data{data.turn=='RIGHT',1}),:);

and it should work.

The resulting file I got, with the second sheet containing results, is attached.

[ADJE JEREMIE ALAGBE]

Thank you so much. Still having some imperfection that may be due to my wrong explanation somewhere.

Actually, I need all rows of id(s) with "RIGHT" turn (no need "LEFT", "UP" and "DOWN"). However, some ids (e.g., 16) may being "RIGHT" turn sometimes (e.g., E64) and "empty" turn sometimes (e.g., E17). So, because this id 16 has been "RIGHT" turn at least once, then I have to take all the rows with id 16, including when it is "RIGHT" turn and "empty" turn.

[Voss]

I think the code is doing what you are saying. See comments in this code that examines rows 64 and 17 from the first sheet of the input file specifically, which both appear to me to make it to the second sheet as output, i.e., they both seem to be correctly included as part of the set of ids with any RIGHT turns:

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/958895/196.xlsx','Sheet','196');

% check rows 64 and 17 from the file (which are rows 63 and 16 in table T):
T([64 17]-1,:)

ans = 2×9 table
    id     name     frame    speed       turn       acceleration    xdistance    ydistance     time  
    __    ______    _____    _____    __________    ____________    _________    _________    _______

    16    {'aC'}      5       21      {'RIGHT' }       -0.07         80.764       7.2438      0.16667
    16    {'aC'}      3        0      {0×0 char}           0         80.514       6.9801          0.1

% run the code
data = T(:,{'id','name','frame','speed','turn','acceleration','xdistance','ydistance','time'});
data.turn = categorical(data.turn);
idx = ismember(data{:,1},data{data.turn=='RIGHT',1});
data0 = data(idx,:);

% check that rows 64 and 17 from the file are in data0
% (yes, they are both there):
disp(idx([64 17]-1))

   1
   1

% look at all the rows of data0 where id is 16;
% these include lines 64 (3rd row below) and 
% 17 (1st row below) from the file:
disp(data0(data0.id == 16,:))

    id     name     frame    speed       turn        acceleration    xdistance    ydistance     time  
    __    ______    _____    _____    ___________    ____________    _________    _________    _______

    16    {'aC'}      3        0      <undefined>           0         80.514       6.9801          0.1
    16    {'aC'}      4        0      <undefined>           0         80.653       7.1466      0.13333
    16    {'aC'}      5       21      RIGHT             -0.07         80.764       7.2438      0.16667
    16    {'aC'}      6       18      DOWN               0.04         80.847       7.4103          0.2
    16    {'aC'}      7       16      DOWN              -0.07         80.903       7.5074      0.23333
    16    {'aC'}      8       13      DOWN               0.01         80.958       7.6184      0.26667
    16    {'aC'}      9       12      DOWN              -0.04         81.014       7.6878          0.3
    16    {'aC'}     10        9      DOWN                  0         81.014       7.7711      0.33333
    16    {'aC'}     11        9      DOWN                  0         81.069       7.8405      0.36667
    16    {'aC'}     12       10      DOWN                  0         81.125       7.9237          0.4

% also look at all the ids that have a RIGHT turn:
unique(data{data.turn=='RIGHT',1}).'

ans = 1×6

1 2 13 16 24 28

unique(data0.id).'

ans = 1×6

1 2 13 16 24 28

% write the new sheet:
filename = '196.xlsx';
writetable(data0,filename,'Sheet',2);

Warning: Added specified worksheet.

% check the new sheet's contents in the file:
new_T = readtable(filename,'Sheet',2);
disp(new_T);

    id     name     frame    speed       turn       acceleration    xdistance    ydistance     time  
    __    ______    _____    _____    __________    ____________    _________    _________    _______

     1    {'aC'}      3        0      {0×0 char}           0         6.7164        47.945         0.1
     2    {'aC'}      3        0      {0×0 char}           0          86.62        51.789         0.1
    13    {'aC'}      3        0      {0×0 char}           0         82.984        17.249         0.1
    16    {'aC'}      3        0      {0×0 char}           0         80.514        6.9801         0.1
     1    {'aC'}      4        0      {0×0 char}           0         6.7442        47.945     0.13333
     2    {'aC'}      4        0      {0×0 char}           0         86.648        51.803     0.13333
    13    {'aC'}      4        0      {0×0 char}           0         83.095        17.346     0.13333
    16    {'aC'}      4        0      {0×0 char}           0         80.653        7.1466     0.13333
    24    {'aC'}      4        0      {0×0 char}           0         93.558       0.91588     0.13333
     1    {'aC'}      5        0      {0×0 char}           0         6.7997        47.959     0.16667
     2    {'aC'}      5        0      {0×0 char}           0         86.731        51.803     0.16667
    13    {'aC'}      5       16      {'DOWN'  }       -0.02         83.151        17.457     0.16667
    16    {'aC'}      5       21      {'RIGHT' }       -0.07         80.764        7.2438     0.16667
    24    {'aC'}      5        0      {0×0 char}           0         93.947        1.1657     0.16667
     1    {'aC'}      6        0      {0×0 char}           0         6.8552        47.973         0.2
     2    {'aC'}      6        7      {'RIGHT' }       -0.04         86.759        51.817         0.2
    13    {'aC'}      6       13      {'DOWN'  }       -0.04         83.151        17.554         0.2
    16    {'aC'}      6       18      {'DOWN'  }        0.04         80.847        7.4103         0.2
    24    {'aC'}      6       48      {'RIGHT' }       -0.09          94.28        1.3461         0.2
     1    {'aC'}      7        0      {0×0 char}           0          6.883        47.973     0.23333
     2    {'aC'}      7        0      {0×0 char}           0         86.786        51.817     0.23333
    13    {'aC'}      7       12      {'DOWN'  }        0.01         83.151        17.665     0.23333
    16    {'aC'}      7       16      {'DOWN'  }       -0.07         80.903        7.5074     0.23333
    24    {'aC'}      7       47      {'RIGHT' }        0.07         94.641        1.6097     0.23333
     1    {'aC'}      8        0      {0×0 char}           0         6.9107        47.987     0.26667
     2    {'aC'}      8        0      {0×0 char}           0         86.814        51.803     0.26667
    13    {'aC'}      8       11      {'DOWN'  }       -0.03         83.206        17.735     0.26667
    16    {'aC'}      8       13      {'DOWN'  }        0.01         80.958        7.6184     0.26667
    24    {'aC'}      8       57      {'DOWN'  }        0.11         95.029        2.0122     0.26667
     1    {'aC'}      9        9      {'RIGHT' }         0.1         7.0495            48         0.3
     2    {'aC'}      9        0      {0×0 char}           0         86.842        51.817         0.3
    13    {'aC'}      9       12      {'RIGHT' }        0.06         83.317        17.818         0.3
    16    {'aC'}      9       12      {'DOWN'  }       -0.04         81.014        7.6878         0.3
    24    {'aC'}      9       60      {'DOWN'  }       -0.06         95.279        2.4423         0.3
     1    {'aC'}     10       10      {'RIGHT' }       -0.07          7.105        48.014     0.33333
     2    {'aC'}     10        0      {0×0 char}           0          86.87        51.817     0.33333
    13    {'aC'}     10       28      {'DOWN'  }        0.22         83.484        18.123     0.33333
    16    {'aC'}     10        9      {'DOWN'  }           0         81.014        7.7711     0.33333
    24    {'aC'}     10       61      {'DOWN'  }        0.07         95.612        2.8864     0.33333
     1    {'aC'}     11        0      {0×0 char}           0         7.1328        48.028     0.36667
     2    {'aC'}     11        7      {'RIGHT' }        0.08         86.953         51.83     0.36667
    13    {'aC'}     11       24      {'UP'    }       -0.29         83.512        18.054     0.36667
    16    {'aC'}     11        9      {'DOWN'  }           0         81.069        7.8405     0.36667
    24    {'aC'}     11       67      {'DOWN'  }        0.05         96.029        3.3443     0.36667
     1    {'aC'}     12        0      {0×0 char}           0         7.1883        48.042         0.4
     2    {'aC'}     12        9      {'RIGHT' }       -0.03         87.036         51.83         0.4
    13    {'aC'}     12        6      {'RIGHT' }       -0.05         83.539         18.04         0.4
    16    {'aC'}     12       10      {'DOWN'  }           0         81.125        7.9237         0.4
    24    {'aC'}     12       71      {'DOWN'  }        0.03         96.389        3.8578         0.4
     1    {'aC'}     13        0      {0×0 char}           0          7.216        48.056     0.43333
     2    {'aC'}     13        0      {0×0 char}           0         87.036        51.844     0.43333
    13    {'aC'}     13        7      {'DOWN'  }        0.07         83.595        18.123     0.43333
    24    {'aC'}     13       68      {'DOWN'  }        -0.1         96.695        4.3157     0.43333
     1    {'aC'}     14        6      {'RIGHT' }        0.06         7.2993         48.07     0.46667
     2    {'aC'}     14        0      {0×0 char}           0         87.064        51.817     0.46667
    13    {'aC'}     14       19      {'RIGHT' }        0.16         83.789         18.29     0.46667
    24    {'aC'}     14       65      {'RIGHT' }        0.05         97.111         4.732     0.46667
     1    {'aC'}     15       11      {'RIGHT' }        0.03         7.4103        48.056         0.5
     2    {'aC'}     15        0      {0×0 char}           0          87.12         51.83         0.5
    13    {'aC'}     15       20      {'DOWN'  }       -0.15         83.817        18.387         0.5
    24    {'aC'}     15       63      {'DOWN'  }       -0.08         97.416        5.1483         0.5
     1    {'aC'}     16        8      {'RIGHT' }       -0.09          7.438        48.056     0.53333
     2    {'aC'}     16        0      {0×0 char}           0          87.12         51.83     0.53333
    13    {'aC'}     16        9      {'DOWN'  }       -0.05         83.845        18.442     0.53333
    24    {'aC'}     16       64      {'DOWN'  }         0.1         97.805        5.6063     0.53333
     1    {'aC'}     17        0      {0×0 char}           0         7.4936        48.056     0.56667
     2    {'aC'}     17        0      {0×0 char}           0         87.147         51.83     0.56667
    13    {'aC'}     17        8      {'DOWN'  }        0.02         83.845        18.526     0.56667
    24    {'aC'}     17       66      {'DOWN'  }       -0.07         98.082        6.0781     0.56667
    28    {'aC'}     17        0      {0×0 char}           0         90.339        1.3877     0.56667
     1    {'aC'}     18        8      {'RIGHT' }        0.03         7.5768         48.07         0.6
     2    {'aC'}     18        0      {0×0 char}           0         87.175         51.83         0.6
    13    {'aC'}     18        9      {'DOWN'  }           0         83.872        18.609         0.6
    24    {'aC'}     18       68      {'DOWN'  }        0.09         98.415        6.6193         0.6
    28    {'aC'}     18        0      {0×0 char}           0         90.589        1.7485         0.6
     1    {'aC'}     19        8      {'RIGHT' }       -0.01         7.6323        48.056     0.63333
     2    {'aC'}     19        0      {0×0 char}           0         87.175         51.83     0.63333
    13    {'aC'}     19        8      {'DOWN'  }       -0.03           83.9        18.665     0.63333
    24    {'aC'}     19       67      {'DOWN'  }       -0.11         98.776        7.0217     0.63333
    28    {'aC'}     19       52      {'DOWN'  }        0.04         90.894        2.1093     0.63333
     1    {'aC'}     20        8      {'RIGHT' }           0         7.7156        48.056     0.66667
     2    {'aC'}     20        0      {0×0 char}           0         87.203         51.83     0.66667
    13    {'aC'}     20        0      {0×0 char}           0           83.9        18.692     0.66667
    24    {'aC'}     20       67      {'DOWN'  }        0.11         99.165        7.5074     0.66667
    28    {'aC'}     20       53      {'DOWN'  }       -0.02         91.172        2.4701     0.66667
     1    {'aC'}     21        6      {'RIGHT' }       -0.03         7.7433        48.042         0.7
     2    {'aC'}     21        0      {0×0 char}           0         87.231         51.83         0.7
    13    {'aC'}     21        6      {'DOWN'  }        0.06         83.956        18.762         0.7
    24    {'aC'}     21       66      {'DOWN'  }       -0.11          99.47        7.9376         0.7
    28    {'aC'}     21       53      {'DOWN'  }        0.01         91.449        2.8309         0.7
     1    {'aC'}     22        8      {'RIGHT' }        0.07         7.8544        48.028     0.73333
     2    {'aC'}     22        0      {0×0 char}           0         87.231         51.83     0.73333
    13    {'aC'}     22        6      {'LEFT'  }       -0.07         83.956        18.748     0.73333
    24    {'aC'}     22       69      {'DOWN'  }        0.15         99.914        8.4511     0.73333
    28    {'aC'}     22       54      {'DOWN'  }        0.02         91.782        3.1917     0.73333
     1    {'aC'}     23       10      {'RIGHT' }       -0.05         7.9376        48.028     0.76667
     2    {'aC'}     23        0      {0×0 char}           0         87.258        51.803     0.76667
    13    {'aC'}     23        0      {0×0 char}           0         83.983        18.817     0.76667
    24    {'aC'}     23       76      {'DOWN'  }       -0.02         100.33        8.9506     0.76667
    28    {'aC'}     23       54      {'DOWN'  }       -0.01         92.032        3.5803     0.76667
     1    {'aC'}     24        9      {'RIGHT' }        0.03         8.0209        48.028         0.8
     2    {'aC'}     24        0      {0×0 char}           0         87.286        51.803         0.8
    13    {'aC'}     24        6      {'LEFT'  }       -0.03         83.928        18.803         0.8
    24    {'aC'}     24       74      {'DOWN'  }       -0.02         100.69        9.4641         0.8
    28    {'aC'}     24       54      {'DOWN'  }        0.01         92.365        3.9272         0.8
     1    {'aC'}     25       12      {'RIGHT' }        0.01         8.1319        48.028     0.83333
     2    {'aC'}     25        0      {0×0 char}           0         87.314        51.817     0.83333
    13    {'aC'}     25        6      {'DOWN'  }        0.03         83.956        18.873     0.83333
    24    {'aC'}     25       72      {'DOWN'  }       -0.02         101.05        9.9637     0.83333
    28    {'aC'}     25       54      {'DOWN'  }       -0.02         92.615        4.3157     0.83333
     1    {'aC'}     26       10      {'RIGHT' }       -0.04         8.2152        48.014     0.86667
     2    {'aC'}     26        0      {0×0 char}           0         87.286         51.83     0.86667
    24    {'aC'}     26       74      {'DOWN'  }        0.06          101.5        10.463     0.86667
    28    {'aC'}     26       48      {'DOWN'  }       -0.09         92.865        4.6071     0.86667
     1    {'aC'}     27        8      {'RIGHT' }           0         8.2707        48.014         0.9
     2    {'aC'}     27        0      {0×0 char}           0         87.314        51.817         0.9
    24    {'aC'}     27       74      {'DOWN'  }       -0.05         101.88        10.949         0.9
    28    {'aC'}     27       57      {'DOWN'  }        0.27         93.281         5.079         0.9
     1    {'aC'}     28        0      {0×0 char}           0         8.2984        48.014     0.93333
     2    {'aC'}     28        0      {0×0 char}           0         87.314         51.83     0.93333
    24    {'aC'}     28       73      {'DOWN'  }        0.03         102.27        11.476     0.93333
    28    {'aC'}     28       62      {'RIGHT' }       -0.18         93.614        5.3843     0.93333
     1    {'aC'}     29        9      {'RIGHT' }        0.12         8.4372            48     0.96667
     2    {'aC'}     29        0      {0×0 char}           0         87.342        51.803     0.96667
    24    {'aC'}     29       80      {'DOWN'  }        0.11         102.74        12.059     0.96667
    28    {'aC'}     29       52      {'DOWN'  }       -0.01         93.864        5.7728     0.96667
     1    {'aC'}     30       12      {'RIGHT' }       -0.06         8.5204        48.014           1

% check just the rows where id is 16;
% I see rows 64 and 17 from the first sheet in there:
disp(new_T(new_T.id == 16,:));

    id     name     frame    speed       turn       acceleration    xdistance    ydistance     time  
    __    ______    _____    _____    __________    ____________    _________    _________    _______

    16    {'aC'}      3        0      {0×0 char}           0         80.514       6.9801          0.1
    16    {'aC'}      4        0      {0×0 char}           0         80.653       7.1466      0.13333
    16    {'aC'}      5       21      {'RIGHT' }       -0.07         80.764       7.2438      0.16667
    16    {'aC'}      6       18      {'DOWN'  }        0.04         80.847       7.4103          0.2
    16    {'aC'}      7       16      {'DOWN'  }       -0.07         80.903       7.5074      0.23333
    16    {'aC'}      8       13      {'DOWN'  }        0.01         80.958       7.6184      0.26667
    16    {'aC'}      9       12      {'DOWN'  }       -0.04         81.014       7.6878          0.3
    16    {'aC'}     10        9      {'DOWN'  }           0         81.014       7.7711      0.33333
    16    {'aC'}     11        9      {'DOWN'  }           0         81.069       7.8405      0.36667
    16    {'aC'}     12       10      {'DOWN'  }           0         81.125       7.9237          0.4

[ADJE JEREMIE ALAGBE]

Thank you so much for you kind help! I believe your code might be correct and maybe my data has a problem. Actually, based on the original meaning of the data, the fifth column of the resulting sheet should include only RIGHT turns and empty cells.

I will check if there is something wrong with the data.

But I'm going to accept the answer.

Thanks again!

[Voss]

You're welcome!