Finding the set of unique values for another set of unique values

hi everyone,

i have a dataset of which the first column contains buyers ID', the second column has a certin index, i need an output that shows what are the indeces associated with each unique buyer ID, for example: if i have a set like this:

1 3

1 3

1 4

2 5

2 4

3 1

3 7

3 7

3 7

3 9

M = [ 1,3;1,3;1,4;2,5;2,4;3,1;3,7;3,7;3,7;3,9];

i want an output in the form of

1 [3 4]

2 [5 4]

3 [1 7 9]

can anyone please help me?

Answer 1

[Guillaume]

i am looking for an array type [...] can it be filled with NaN or zeros?

NaN is probably wiser.

M = [ 1,3;1,3;1,4;2,5;2,4;3,1;3,7;3,7;3,7;3,9];

Mdedup = unique(M, 'rows');  %remove duplicate rows.
[id, ~, sub] = unique(Mdedup(:, 1)); %list of unique IDs and corresponding location
maxcount = max(accumarray(sub, 1));  %get max of histogram of IDs = width of matrix to create
result = [id, cell2mat(accumarray(sub, Mdedup(:, 2), [], @(v) {[v.', nan(1, maxcount - numel(v))]}))]

[MJ]

This code works perfect, thank you very much for your time!

Answer 2

[Ollie A]

This should do the trick:

M = [ 1,2;1,3;1,2;2,4;2,4;2,5]; % Your matrix
u = unique(M(:,1));
for x = 1:length(u)
    N{x} = unique(M(M(:,1)==u(x),2));
end
T = table(u,N'); % Output as table

The output is a table, which displays the data in the way you requested.

[MJ]

Thank you very much for your prompt response,

i just have two questions,

the output which you just coded would be in the form of

1 [2;3]

2 [4;5]

is there anyway to separate the 2 and 3?

another question,

N{x} = unique(M(M(:,1)==u(x),2));

what does the 2 is the end refer to?

thank you in advance,

[Guillaume]

@Ollie A, get in the habit of preallocating your arrays rather than growing them in a loop, Growing an array in a loop means that at each step of the loop matlab has to reallocate a whole new array and copy over all the previously assigned elements. It's slow. In addition, since you don't preallocate the array, if the variable already exists but is of a different type, you'll get an error. If the variable already exists, is the correct type but larger than length(u), you'll end up with extra elements.

N = cell(size(u));

before the loop would work.

Another way to obtain the same without a loop:

M = [1, 2; 1, 3; 1, 2; 2, 4; 2, 4; 2, 5];
[u, ~, subs] = unique(M(:, 1))
N = accumarray(subs, M(:, 2), [], @(v) {v});
T = table(u, N)

@mohammad,

"is there anyway to separate the 2 and 3?"

What does that even mean? The 2 and 3 are stored as a vector in the particular row of the table. You seem to be confusing storage and display

"what does the 2 is the end refer to?"

This is basic indexing. M(something, 2) always refers to column 2 of M. In this particular case, it's all the elements of column 2 for which column 1 is equal to u(x).

[MJ]

@Guillaume thank you for your reply as well.

"What does that even mean? The 2 and 3 are stored as a vector in the particular row of the table. You seem to be confusing storage and display"

i want to separate the values in the same cell to two different cells, i.e if i have a cell which contains [2;3], i want the the table be in the shape [ 2 ] [ 3 ] as i show in the attached photos (actually i want it as an array not table).

vs.

As per your code, it gives the following output,

which is not precisely what i am after, i am after getting the unique value in column N, and at the same time seapate these values into different columns.

thanks,

[Guillaume]

"I want to separate the values in the same cell to two different cells"

A table/cell array/matrix is always rectangular. In the case where there's less elements in a row than others what do you want to put in the missing columns?

With your original example, the ouput would be

1 3 4 x
2 5 4 x
3 1 7 9

What should x be?

Also, what type of output are you looking for (matrix? cell array? table?)

[MJ]

"what do you want to put in the missing columns?"

can it be filled with NaN or zeros?

"what type of output are you looking for (matrix? cell array? table?)"

i am looking for an array type, i want to use the output numbers in other calculations.

Answer 3

[Stephen23]

>> F = @(v){unique(v,'stable').'};
>> C = accumarray(M(:,1),M(:,2),[],F);
>> C{:}
ans =
   3   4
ans =
   5   4
ans =
   1   7   9

[MJ]

Thank you for your reply,

I am almost there, I just need on elast step,

how can i store the value

4 3

5 4

1 7 9

in a matrix or table or array?

[Stephen23]

@mohammad aljarrah: one very easy solution is to download padcat:

https://www.mathworks.com/matlabcentral/fileexchange/22909-padcat

and simply use it like this:

 >> padcat(C{:})
ans =
     3     4   NaN
     5     4   NaN
     1     7     9

[MJ]

thank you very much