comparison of the datasets

Question

MatLab Code N 2019-4-9

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/455371-comparison-of-the-datasets

评论： Andrei Bobrov 2019-4-10

example.xlsx

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:

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.
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.
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!!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Andrei Bobrov 2019-4-9

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/455371-comparison-of-the-datasets#answer_369845

编辑：Andrei Bobrov 2019-4-9

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 个评论
显示无隐藏无

MatLab Code N 2019-4-9

Thanks!

Guillaume 2019-4-9

Well, that's a bit complicated!

请先登录，再进行评论。

Answer 2

Guillaume 2019-4-9

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/455371-comparison-of-the-datasets#answer_369840

编辑：Guillaume 2019-4-9

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.