Find the first matched string/value from a table

Hi,

I have got a table like below:

StepNum Activity TypeNum

5098 Stand 1-96

5099 Stand 1-96

5101 Stationary 1-96

5105 Stationary 1-96

5106 Stand 1-97

5107 Stand 1-97

5113 Stand 1-97

5114 Trenching 1-98

5115 Trenching 1-98

5116 Trenching 1-98

What I would like to do is to find the first matched of the string 'Trenching', then read the row/column number so that I can get the value/string in adjacent cells/rows. Just wondering if anyone here can help me out? Many thanks in advance.

Regards,

Kane

Answer 1

[Star Strider]

One approach —

 VN = {'StepNum'  'Activity'  'TypeNum'}; 
 
 step = [5098 
 5099 
 5101  
 5105 
 5106 
 5107 
 5113 
 5114 
 5115 
 5116];
 
v23 = ["Stand"  "1-96" 
"Stand"  "1-96" 
"Stationary"  "1-96"  
"Stationary"  "1-96" 
"Stand"  "1-97" 
"Stand"  "1-97" 
"Stand"  "1-97" 
"Trenching"  "1-98" 
"Trenching" "1-98" 
"Trenching" "1-98"];

T1 = table(step, v23(:,1), v23(:,2), 'VariableNames',VN)

T1 = 10×3 table
    StepNum      Activity      TypeNum
    _______    ____________    _______

     5098      "Stand"         "1-96" 
     5099      "Stand"         "1-96" 
     5101      "Stationary"    "1-96" 
     5105      "Stationary"    "1-96" 
     5106      "Stand"         "1-97" 
     5107      "Stand"         "1-97" 
     5113      "Stand"         "1-97" 
     5114      "Trenching"     "1-98" 
     5115      "Trenching"     "1-98" 
     5116      "Trenching"     "1-98" 

idx = find(ismember(T1{:,2}, "Trenching"));

DesiredResult = T1{idx(1),:}                            % Contents Of Row

DesiredResult = 1×3 string array
    "5114"    "Trenching"    "1-98"

DesiredResult = T1(idx(1),:)                            % Sub-Table

DesiredResult = 1×3 table
    StepNum     Activity      TypeNum
    _______    ___________    _______

     5114      "Trenching"    "1-98" 

.

[Kane Lok]

Hi Star Strider,

Thanks for the help first.

The command of 'DesiredResult = T1{idx(1),:}' is not working, and I think it is down to the fact that I didn't explain my application thoroughly.

Basically I import a table from an excel file by using the following command:

data = readtable('InputFile.xlsx','Sheet','RecordInfo','range','A:K');

Once I have got 'data' in my MATLAB workspace, I then search for the first matched 'Trenching' and get the row number. So I could read the relevant TypeNum cell content and read other cell contents (like Volt and Current) in the previous rows (like stand and stationary) (i.e. the table that I posted intially is only a small part of the data).

StepNum Activity TypeNum Volt Current

_______ ____________ _______ ____ _______

5098 "Stand" "1-96" 1.1 0.1

5099 "Stand" "1-96" 1.2 0.2

5101 "Stationary" "1-96" 1.3 0.3

5105 "Stationary" "1-96" 1.3 0.3

5106 "Stand" "1-97" 1.8 0.8

5107 "Stand" "1-97" 2 0

5113 "Stand" "1-97" 1.9 1.9

5114 "Trenching" "1-98" 8 79

5115 "Trenching" "1-98" 11.5 80.5

5116 "Trenching" "1-98" 24 80.2

Those retrived cell content values will be used in a calculation to produce the results like R = (Volt(5114) - Volt(5113)) / (Current(5114) - Current(5113)). The calculated R will be used to produce another table.

Hopefully it is making sense to you and you are able to give me some suggestion? Thanks a lot.

Regards,

Kane

[Star Strider]

Attach the Excel file. Use the ‘paperclip’ icon in the top toolbar to do that.

My code returns the necessary information, however I had to create it from the text since the Excel file was not provided. In any event, to use the information my code in my answer returns, use the str2double function to retrieve the numeric results. This might not be necessary using the actual Excel file.

[Kane Lok]

Hi Star Strider,

The excel file attached and thanks for your help.

Regards,

Kane

[Star Strider]

My pleasure!

Try this (including a calculation for ‘R’) —

T1 = readtable('activity_table.xlsx', 'VariableNamingRule','preserve')

T1 = 2307×9 table
    SignNum    StepName     ActivityStepNum    LoopNum    StepNum    Current(A)    Voltage(V)    Power(W)    RelativeTime(d.h:min:s.ms)
    _______    _________    _______________    _______    _______    __________    __________    ________    __________________________

       1       {'Stand'}        {'1-1'}           1          1           0           3.5252         0            {'0.00:01:00.000'}    
       2       {'Stand'}        {'1-1'}           1          1           0           3.5252         0            {'0.00:02:00.000'}    
       3       {'Stand'}        {'1-1'}           1          1           0           3.5252         0            {'0.00:03:00.000'}    
       4       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:04:00.000'}    
       5       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:05:00.000'}    
       6       {'Stand'}        {'1-1'}           1          1           0           3.5252         0            {'0.00:06:00.000'}    
       7       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:07:00.000'}    
       8       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:08:00.000'}    
       9       {'Stand'}        {'1-1'}           1          1           0           3.5252         0            {'0.00:09:00.000'}    
      10       {'Stand'}        {'1-1'}           1          1           0           3.5252         0            {'0.00:10:00.000'}    
      11       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:11:00.000'}    
      12       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:12:00.000'}    
      13       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:13:00.000'}    
      14       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:14:00.000'}    
      15       {'Stand'}        {'1-1'}           1          1           0           3.5253         0            {'0.00:15:00.000'}    
      16       {'Stand'}        {'1-1'}           1          1           0           3.5252         0            {'0.00:16:00.000'}    

VN = T1.Properties.VariableNames;
idx = find(ismember(T1{:,2}, "Trenching"))

idx = 1347×1

930 931 932 933 934 935 936 937 938 939

DesiredResult = T1(idx(1)+[-1 1],:)                            % Contents Of Row

DesiredResult = 2×9 table
    SignNum      StepName       ActivityStepNum    LoopNum    StepNum    Current(A)    Voltage(V)    Power(W)    RelativeTime(d.h:min:s.ms)
    _______    _____________    _______________    _______    _______    __________    __________    ________    __________________________

      929      {'Stand'    }        {'1-3'}           1          3              0          4.19            0         {'0.01:00:00.000'}    
      931      {'Trenching'}        {'1-4'}           1          4        -17.001        4.1828       -71.11         {'0.00:00:00.020'}    

Volt = DesiredResult{:,ismember(VN,'Voltage(V)')}

Volt = 2×1

4.1900 4.1828

Current = DesiredResult{:,ismember(VN,'Current(A)')}

Current = 2×1

0 -17.0006

R = (Volt(2) - Volt(1)) / (Current(2) - Current(1))

R = 4.2351e-04

See if that does what you want.

.