How to conditionally pick the rows of tow datasets?

Hi everyone,

I have two data set and want to pick rows that have common first element. In the below example, when first element of matrix A (row) is same with the first element of matrix B (row) the second element of B added to matrix A.

Thank you!

A = [1, 6, 3; 
    8, 11, 1; 
    4, 2, 0; 
    9, 2, 1]

B = [3, 11;
    3, 1;
    8, 4; 
    2, 5; 
    1, 8;
    9, 7; 
    4, 6;
    0, 1;
    11, 25;
    6, 15]

%% Dimesnions of A and B are not same 
% then C
C = [1, 6, 3, 8; 
    8, 11, 1, 4; 
    4, 2, 0, 6; 
    9, 2, 1, 7]

% D be another empty arry that store the upper and lower values of second
% coloumn (B)

D=[4, 5, 8, 7, 6;
    11, 1, 4, 5, 8;
    8, 7, 6, 1, 25;
    5, 8, 7, 6, 1]

% need to repeat this process for 5000 rows of column A, where the length
% of B is 17000

Here is the proposed methods alongwith the real data set, but none of them working on real data.

A=readmatrix('data_1.csv');
B=readmatrix('data_2.csv');

% Approach 1


B = array2table(B)
[ism,idx] = ismember(A(:,1),B(:,1));
C(ism,end+1) = B(idx(ism),2)


% Approach 2

C = outerjoin(A,B,'Key','Var1','Type','left')

The data is also attached here.

Answer 1

[Voss]

A = [1, 6, 3; 8, 11, 1; 4, 2, 0; 8, 1, 5; 9, 2, 1]

A = 5×3

1 6 3 8 11 1 4 2 0 8 1 5 9 2 1

B = [8, 4; 7, 1; 2, 5; 1, 8; 9, 7; 4, 6]

B = 6×2

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

C = A;
[ism,idx] = ismember(A(:,1),B(:,1));
C(ism,end+1) = B(idx(ism),2)

C = 5×4

1 6 3 8 8 11 1 4 4 2 0 6 8 1 5 4 9 2 1 7

[Kevin Holly]

Here is an alternative way with tables

A = array2table([1, 6, 3; 8, 11, 1; 4, 2, 0; 8, 1, 5; 9, 2, 1])

A = 5×3 table
    Var1    Var2    Var3
    ____    ____    ____

     1        6      3  
     8       11      1  
     4        2      0  
     8        1      5  
     9        2      1  

B = array2table([8, 4; 7, 1; 2, 5; 1, 8; 9, 7; 4, 6])

B = 6×2 table
    Var1    Var2
    ____    ____

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

C = outerjoin(A,B,'Key','Var1','Type','left')

C = 5×5 table
    Var1_A    Var2_A    Var3    Var1_B    Var2_B
    ______    ______    ____    ______    ______

      1          6       3        1         8   
      4          2       0        4         6   
      8         11       1        8         4   
      8          1       5        8         4   
      9          2       1        9         7   

C.Var1_B=[]

C = 5×4 table
    Var1_A    Var2_A    Var3    Var2_B
    ______    ______    ____    ______

      1          6       3        8   
      4          2       0        6   
      8         11       1        4   
      8          1       5        4   
      9          2       1        7   

[Andi]

Thanks a lot, this serve for my purpose. I need a slight more extendtion in my code e.g. if teh first element match pick the 2 elements before and after that coressponding element and save in an empty array. and repeat thsi process for 10000 points. (I also added secodn part in the main question).

[Andi]

I attemped both of the proposed method but none of them working on real data. I have attched my data set and script for reference .

A=readmatrix('data_1.csv');
B=readmatrix('data_2.csv');

% Approach 1


B = array2table(B)
[ism,idx] = ismember(A(:,1),B(:,1));
C(ism,end+1) = B(idx(ism),2)


% Approach 2

C = outerjoin(A,B,'Key','Var1','Type','left')

[Voss]

A=readmatrix('data_1.csv');
B=readmatrix('data_2.csv');

% Approach 1
C = A; % this is necessary, and don't convert B to a table in this approach
[ism,idx] = ismember(A(:,1),B(:,1));
C(ism,end+1) = B(idx(ism),2);

disp(C) % is this right now?

   1.0e+05 *

    7.3809    0.0004    0.0153
    7.3808    0.0005    0.0153
    7.3799    0.0006    0.0146
    7.3799   -0.0001    0.0146
    7.3797    0.0002    0.0145
    7.3796   -0.0003    0.0145
    7.3782    0.0002    0.0148
    7.3762   -0.0000    0.0143
    7.3761    0.0004    0.0140
    7.3759   -0.0003    0.0141
    7.3756   -0.0000    0.0141
    7.3739    0.0006    0.0145
    7.3734   -0.0001    0.0150
    7.3733   -0.0000    0.0152
    7.3730   -0.0002    0.0155
    7.3712   -0.0001    0.0140
    7.3711    0.0002    0.0143
    7.3708    0.0006    0.0145
    7.3702   -0.0002    0.0146
    7.3701    0.0003    0.0147
    7.3696    0.0002    0.0152
    7.3695    0.0002    0.0154
    7.3689    0.0005    0.0148
    7.3605   -0.0000    0.0144
    7.3497    0.0003    0.0138
    7.3497    0.0000    0.0138
    7.3497    0.0000    0.0138
    7.3460    0.0004    0.0139
    7.3431   -0.0000    0.0138
    7.3430    0.0001    0.0139
    7.3429   -0.0001    0.0139
    7.3427    0.0000    0.0139
    7.3423    0.0000    0.0138
    7.3423    0.0003    0.0138
    7.3421   -0.0003    0.0139
    7.3231    0.0001    0.0140
    7.3231    0.0000    0.0140
    7.3230   -0.0005    0.0141
    7.3228    0.0004    0.0143
    7.3228   -0.0000    0.0143
    7.3227   -0.0005    0.0145
    7.3227    0.0000    0.0145
    7.3226   -0.0001    0.0147
    7.3198   -0.0000    0.0144
    7.3198   -0.0000    0.0144
    7.3195   -0.0002    0.0144
    7.3194   -0.0002    0.0144
    7.3190    0.0005    0.0150
    7.3189    0.0004    0.0152
    7.3178   -0.0000    0.0152
    7.3166    0.0005    0.0137
    7.3160    0.0002    0.0142
    7.3160   -0.0001    0.0142
    7.3152    0.0006    0.0149
    7.3152    0.0000    0.0149
    7.3150   -0.0000    0.0150
    7.3131    0.0002    0.0137
    7.3128    0.0001    0.0138
    7.3116   -0.0001    0.0148
    7.3114   -0.0000    0.0149
    7.3091    0.0000    0.0138
    7.3090   -0.0000    0.0139
    7.3088    0.0002    0.0142
    7.3085    0.0001    0.0141
    7.3083    0.0004    0.0141
    7.3081   -0.0001    0.0146
    7.3081   -0.0001    0.0146
    7.3081   -0.0000    0.0146
    7.3079   -0.0000    0.0147
    7.3054   -0.0002    0.0140
    7.3047    0.0002    0.0145
    7.3044   -0.0000    0.0150
    7.3044    0.0004    0.0150
    7.3041    0.0003    0.0152
    7.3039    0.0002    0.0154
    7.3038    0.0002    0.0154
    7.3035    0.0004    0.0154
    7.3029    0.0002    0.0142
    7.3026   -0.0000    0.0138
    7.3018    0.0001    0.0143
    7.3016   -0.0001    0.0146
    7.3009   -0.0000    0.0149
    7.3007   -0.0001    0.0152
    7.2997   -0.0000    0.0152
    7.2995   -0.0000    0.0149
    7.2988    0.0002    0.0144
    7.2985   -0.0000    0.0144
    7.2984   -0.0006    0.0145
    7.2979   -0.0002    0.0148
    7.2973    0.0005    0.0149
    7.2972    0.0000    0.0151
    7.2970    0.0004    0.0152
    7.2968   -0.0003    0.0153
    7.2965   -0.0003    0.0155
    7.2958    0.0001    0.0139
    7.2952    0.0003    0.0135
    7.2950   -0.0001    0.0134
    7.2945    0.0003    0.0141
    7.2940    0.0002    0.0146
    7.2934   -0.0001    0.0150
    7.2923    0.0000    0.0152
    7.2919    0.0001    0.0143
    7.2919    0.0005    0.0143
    7.2919    0.0005    0.0143
    7.2918    0.0001    0.0141
    7.2914   -0.0001    0.0139
    7.2908    0.0002    0.0142
    7.2908   -0.0001    0.0142
    7.2907   -0.0000    0.0143
    7.2906    0.0005    0.0144
    7.2903    0.0000    0.0145
    7.2900    0.0004    0.0148
    7.2899    0.0003    0.0149
    7.2895    0.0003    0.0152
    7.2894    0.0002    0.0154
    7.2894   -0.0000    0.0154
    7.2892    0.0002    0.0155
    7.2889   -0.0001    0.0155
    7.2889   -0.0001    0.0155
    7.2887   -0.0002    0.0153
    7.2884   -0.0003    0.0147
    7.2881    0.0005    0.0143
    7.2880   -0.0002    0.0143
    7.2878    0.0001    0.0140
    7.2877    0.0001    0.0139
    7.2876   -0.0002    0.0139
    7.2867    0.0004    0.0146
    7.2866    0.0004    0.0146
    7.2837   -0.0000    0.0133
    7.2834   -0.0002    0.0139
    7.2832    0.0000    0.0140
    7.2792   -0.0001    0.0152
    7.2791   -0.0001    0.0153
    7.2786    0.0001    0.0155
    7.2770    0.0001    0.0143
    7.2770    0.0001    0.0143
    7.2770   -0.0001    0.0143
    7.2768    0.0004    0.0142
    7.2756   -0.0006    0.0147
    7.2755    0.0005    0.0148
    7.2752    0.0000    0.0151
    7.2720    0.0002    0.0146
    7.2714    0.0005    0.0153
    7.2700    0.0001    0.0151
    7.2569    0.0002    0.0147
    7.2550    0.0005    0.0140
    7.2549    0.0002    0.0138
    7.2536   -0.0001    0.0142
    7.2533   -0.0000    0.0146
    7.2522    0.0004    0.0149
    7.2519   -0.0000    0.0131
    7.2514   -0.0001    0.0130
    7.2511    0.0000    0.0132
    7.2511   -0.0003    0.0132
    7.2507   -0.0003    0.0138
    7.2507   -0.0003    0.0138
    7.2496    0.0000    0.0150
    7.2495    0.0001    0.0152
    7.2473    0.0004    0.0137
    7.2472    0.0004    0.0138
    7.2468   -0.0001    0.0146
    7.2465   -0.0000    0.0148
    7.2461    0.0001    0.0152
    7.2461   -0.0001    0.0152
    7.2457   -0.0006    0.0154
    7.2455   -0.0003    0.0155
    7.2451    0.0006    0.0155
    7.2442    0.0004    0.0139
    7.2438    0.0003    0.0135
    7.2437    0.0001    0.0135
    7.2435   -0.0000    0.0135
    7.2429    0.0004    0.0139
    7.2426   -0.0002    0.0141
    7.2413   -0.0001    0.0146
    7.2412    0.0004    0.0134
    7.2410   -0.0005    0.0132
    7.2407    0.0002    0.0133
    7.2392    0.0001    0.0142
    7.2391   -0.0003    0.0142
    7.2390    0.0004    0.0143
    7.2384    0.0002    0.0148
    7.2384   -0.0003    0.0148
    7.2373   -0.0002    0.0133
    7.2369   -0.0005    0.0133
    7.2358    0.0005    0.0141
    7.2349    0.0004    0.0148
    7.2348   -0.0002    0.0148
    7.2338   -0.0001    0.0148
    7.2337   -0.0001    0.0142
    7.2323    0.0004    0.0139
    7.2316    0.0000    0.0144
    7.2315    0.0003    0.0145
    7.2307   -0.0000    0.0155
    7.2305    0.0002    0.0155
    7.2289    0.0002    0.0144
    7.2287    0.0006    0.0145
    7.2281    0.0002    0.0148
    7.2276   -0.0001    0.0149
    7.2271    0.0003    0.0153
    7.2269    0.0001    0.0152
    7.2262   -0.0002    0.0132
    7.2261    0.0004    0.0132
    7.2253    0.0004    0.0138
    7.2253    0.0004    0.0138
    7.2253    0.0004    0.0138
    7.2249   -0.0003    0.0145
    7.2241   -0.0003    0.0148
    7.2237   -0.0003    0.0153
    7.2232   -0.0006    0.0154
    7.2232   -0.0001    0.0154
    7.2230   -0.0001    0.0152
    7.2220   -0.0001    0.0132
    7.2220   -0.0001    0.0132
    7.2220   -0.0001    0.0132
    7.2218   -0.0002    0.0133
    7.2216    0.0002    0.0138
    7.2215    0.0005    0.0141
    7.2205    0.0004    0.0150
    7.2202   -0.0000    0.0153
    7.2195    0.0001    0.0152
    7.2193    0.0004    0.0152
    7.2193    0.0004    0.0152
    7.2191    0.0001    0.0151
    7.2190   -0.0000    0.0151
    7.2189    0.0000    0.0152
    7.2187    0.0002    0.0148
    7.2186    0.0004    0.0148
    7.2182    0.0004    0.0150
    7.2175    0.0002    0.0152
    7.2174    0.0004    0.0152
    7.2173   -0.0003    0.0153
    7.2171   -0.0002    0.0152
    7.2169    0.0002    0.0153
    7.2166    0.0001    0.0153
    7.2164   -0.0002    0.0153
    7.2163   -0.0001    0.0153
    7.2163   -0.0000    0.0153
    7.2156   -0.0001    0.0142
    7.2156   -0.0001    0.0142
    7.2152    0.0004    0.0127
    7.2150    0.0004    0.0124
    7.2148   -0.0004    0.0121
    7.2139    0.0004    0.0112
    7.2139    0.0005    0.0112

[Andi]

@Voss wow thanks this works perfectly, how about if i want to pick 5 or 10 elements before and after the ismember entry of dataset-2

[Voss]

Can you give an example?

[Andi]

Thanks, I already fixed that issue.