comparison of the datasets

5 views (last 30 days)
MatLab Code N
MatLab Code N on 9 Apr 2019
Commented: Andrei Bobrov on 10 Apr 2019
Hi,
In the attached excel file, there are two different sheet (X_val and range_val). The X_val sheet contains a series of numbers. The range_val sheet contains R1 and R2 columns which are the lower and higher numbers of a range and the third column in range _val is a X-Id of the repective range. I want a loop which will check folloing stuffs:
  1. Check each values in X-val to see if it is a number which lies bettwen the numbers in range_val column 1 and 2.
  2. if any x-val is a number lying between range_val column 1 and 2, then create a column in X-val and write the corresponding X_ID.
  3. if any-number is not in a required range, then just put NaN next to the X-val.
the final desired output should be a excel sheet which look like:
X-val X_ID
2.11 NaN
3.05 NaN
4.09 NaN
5.6 c
2.5 NaN
6 c
4.2 b
8.9 h
25 NaN
Note: I have a long dataset, given example is just a part of it.
Thanks!!

Accepted Answer

Andrei Bobrov
Andrei Bobrov on 9 Apr 2019
Edited: Andrei Bobrov on 9 Apr 2019
T = readtable('example.xlsx','sheet',1);
T2 = readtable('example.xlsx','sheet',2);
R12 = unique([T2.R1;T2.R2+eps(1e3)]);
C = categorical(repmat({'NoN'},numel(R12),1));
[lo,ii] = ismember(R12,T2.R1);
C(lo) = T2.X_ID(ii(lo));
out = table(T.X,discretize(T.X,[-inf;R12;inf],C([end,1:end])),'v',{'X','X_ID'});
  2 Comments
Guillaume
Guillaume on 9 Apr 2019
Well, that's a bit complicated!

Sign in to comment.

More Answers (1)

Guillaume
Guillaume on 9 Apr 2019
Edited: Guillaume on 9 Apr 2019
I don't see why 4.2 should get a 'b' id.
X_val = readtable('example.xlsx', 'Sheet', 'X_val')
range_val = readtable('example.xlsx', 'Sheet', 'range_val')
[destrow, idrow] = find(X_val.X >= range_val.R1' & X_val.X <= range_val.R2');
X_val.X_ID(destrow) = range_val.X_ID(idrow)
Note that this leaves empty the cells that don't match instead of putting NaN in them. In general, it's not a good idea to mix text and numbers (even NaN) in the same column.

Community Treasure Hunt

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

Start Hunting!