在Matlab中通过分组来找到导致最小类似于SQL的值 [英] group by in Matlab to find the value that resulted minimum similar to SQL

问题描述

我有一个包含a，b，c和d
列的数据集我想按a，b分组数据集并找到c，这样d对于每个组都是最小值
我可以做group通过使用grpstats作为：

grpstats（M，[M（：，1）M（：，2）]，{'min'}）;

我不知道如何找到M（：，3）的值，结果是min
<在SQL中，我想我们使用嵌套查询并使用主键，我怎样才能在Matlab中解决它？

这是一个例子：

 >> M = [4,1,7,0.3; 
 2,1,8,0.4; 
 2,1,9,0.2; 
 4,2,1,0.2; 
 2,2,2,0.6; 
 4,2,3,0.1; 
 4 ，3,5,0.8; 
 5,3,6,0.2; 
 4,3,4,0.5;] 
 
>> grpstats（M，[M （：，1）M（：2）]'min'）
 ans = 
 
 2.0000 1.0000 8.0000 0.2000 
 2.0000 2.0000 2.0000 0.6000 
 4.0000 1.0000 7.0000 0.3000 
 4.0000 2.0000 1.0000 0.1000 
 4.0000 3.0000 4.0000 0.5000 
 5.0000 3.0000 6.0000 0.2000 
  

但是M（1,3）和M（4,3）是错误的。我正在寻找的正确答案是：

  2.0000 1.0000 9.0000 0.2000 
 2.0000 2.0000 2.0000 0.6000 
 4.0000 1.0000 7.0000 0.3000 
 4.0000 2.0000 3.0000 0.1000 
 4.0000 3.0000 4.0000 0.5000 
 5.0000 3.0000 6.0000 0.2000 
  

最后，我不想要第三列的最小值;但我希望它的值在第4列中对应最小值

解决方案

grpstats 不会做到这一点，MATLAB并不像你希望的那样容易。

有时候，蛮力是最好的，即使它不太好MATLAB风格：
$ b $ pre $ code> [b，m，n] = unique（M（：，1：2），'rows'） ;
for i = 1：numel（m）
idx = find（n == i）;
[〜，subidx] = min（M（idx，4））;
a（i，:) = M（idx（subidx），3：4）;
结束

>> [b，a]
ans =
2 1 9 0.2
2 2 2 0.6
4 1 7 0.3
4 2 3 0.1
4 3 4 0.5
5 3 6 0.2

I have a dataset having columns a, b, c and d I want to group the dataset by a,b and find c such that d is minimum for each group I can do "group by" using 'grpstats" as :

grpstats(M,[M(:,1) M(:,2) ],{'min'});

I don't know how to find the value of M(:,3) that resulted the min in d

In SQL I suppose we use nested queries for that and use the primary keys. How can I solve it in Matlab?

Here is an example:

>> M =[4,1,7,0.3;
2,1,8,0.4;
2,1,9,0.2;
4,2,1,0.2;
2,2,2,0.6;
4,2,3,0.1;
4,3,5,0.8;
5,3,6,0.2;
4,3,4,0.5;]

>> grpstats(M,[M(:,1) M(:,2)],'min')
ans =

2.0000    1.0000    8.0000    0.2000
2.0000    2.0000    2.0000    0.6000
4.0000    1.0000    7.0000    0.3000
4.0000    2.0000    1.0000    0.1000
4.0000    3.0000    4.0000    0.5000
5.0000    3.0000    6.0000    0.2000

But M(1,3) and M(4,3) are wrong. The correct answer that I am looking for is:

2.0000    1.0000    9.0000    0.2000
2.0000    2.0000    2.0000    0.6000
4.0000    1.0000    7.0000    0.3000
4.0000    2.0000    3.0000    0.1000
4.0000    3.0000    4.0000    0.5000
5.0000    3.0000    6.0000    0.2000

To conclude, I don't want the minimum of third column; but I want it's values corresponding to minimum in 4th column

解决方案

grpstats won't do this, and MATLAB doesn't make it as easy as you might hope.

Sometimes brute force is best, even if it doesn't feel like great MATLAB style:

[b,m,n]=unique(M(:,1:2),'rows');
for i =1:numel(m)
    idx=find(n==i);
    [~,subidx] = min(M(idx,4));
    a(i,:) = M(idx(subidx),3:4);
end

>> [b,a]
ans =
        2            1            9          0.2
        2            2            2          0.6
        4            1            7          0.3
        4            2            3          0.1
        4            3            4          0.5
        5            3            6          0.2

这篇关于在Matlab中通过分组来找到导致最小类似于SQL的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！