How to conditionally pick the rows of tow datasets?

20 Jul 2022
1 Answer
Answer Accepted
Updated 21 Jul 2022
16 Views (30 days)

0 votes

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.

Sign in to answer this question.

 Accepted Answer

Voss
Voss on 20 Jul 2022
Ran in:

1 vote

Ran in:
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

7 Comments
Show 5 older comments Hide 5 older comments

Kevin Holly
Kevin Holly on 20 Jul 2022
Ran in:
Ran in:
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
Andi on 20 Jul 2022
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
Andi on 20 Jul 2022
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
Voss on 20 Jul 2022
Edited: Voss on 20 Jul 2022
Ran in:
Ran in:
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
Andi on 20 Jul 2022
@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
Voss on 21 Jul 2022
Can you give an example?
Andi
Andi on 21 Jul 2022
Thanks, I already fixed that issue.

Sign in to comment.

More Answers (0)

Categories

Find more on Just for fun in Help Center and File Exchange

Products

Tags

Asked:

Andi
Andi
on 20 Jul 2022

Commented:

Andi
Andi
on 21 Jul 2022

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!