Clear Filters
Clear Filters

use unstack to reshape table with dummy variable (edited: alternative crosstab method)

7 views (last 30 days)
After I sort table according to a group variable, I like to put the group value as the 'header' column and put its members in the other columns in the same row.
Data for the sake of demostration:
C = {'q1', 'q1', 'q2', 'q3', 'q3', 'q3';
'apple', 'appl', 'banana', 'orange', 'orang', 'orange'}';
T = cell2table(C, 'VariableNames', {'code', 'fruit'});
[GroupsID, Groups] = findgroups(T.code);
unique_groupID = unique(GroupsID);
gT = table('Size', [10,4], 'VariableTypes', {'string', 'string', 'string', 'string'});
Method 1. (edited.) brutal for-loop that I don't like, and its result needs more processing to remove redundancy in each row.
for k=1:size(unique_groupID)
% extract group elements from 'fruit'
tmp = T.fruit(GroupsID==unique_groupID(k));
l = size(tmp',2);
gT(k,1) = {Groups(k)};
gT(k,2:l+1) = tmp';
end
rmmissing(gT, "MinNumMissing", 3)
ans = 3×4 table
Var1 Var2 Var3 Var4 ____ ________ _________ _________ "q1" "apple" "appl" <missing> "q2" "banana" <missing> <missing> "q3" "orange" "orang" "orange"
Method 2 using unstack
I created a dummy variable for this method in order to use unstack( ). The code is shorter but doesn't give me the result I want.
D = {'dm1', 'dm2', 'dm3', 'dm4', 'dm5', 'dm6';
'q1', 'q1', 'q2', 'q3', 'q3', 'q3';
'apple', 'appl', 'banana', 'orange', 'orang', 'orange'}';
T = cell2table(D, 'VariableNames', {'dummy', 'code', 'fruit'});
unstack(T, "fruit", "dummy")
ans = 3×7 table
code dm1 dm2 dm3 dm4 dm5 dm6 ______ __________ __________ __________ __________ __________ __________ {'q1'} {'apple' } {'appl' } {0×0 char} {0×0 char} {0×0 char} {0×0 char} {'q2'} {0×0 char} {0×0 char} {'banana'} {0×0 char} {0×0 char} {0×0 char} {'q3'} {0×0 char} {0×0 char} {0×0 char} {'orange'} {'orang' } {'orange'}
Edited. Method 3 using crosstab. This method works nicely, but I wish I don't have to use a for-loop. The result from this method is exactly what I want.
[tb,~,~,lbs] = crosstab(T.code, T.fruit);
For-loop to create the intended table:
m = size(tb,1);
header = lbs(1:m,1);
fruits = lbs(:,2);
gT = table('Size',[6,4], 'VariableTypes', {'string','string','string','string'});
for i=1:m
tmp = fruits(tb(i,:)>0)';
l = size(tmp,2);
gT(i,"Var1") = header(i);
gT(i, 2:l+1) = tmp;
end
rmmissing(gT, "MinNumMissing",4)
ans = 3×4 table
Var1 Var2 Var3 Var4 ____ ________ _________ _________ "q1" "apple" "appl" <missing> "q2" "banana" <missing> <missing> "q3" "orange" "orang" <missing>
Edited. After I post the above code, I thought about that Method 3 may be made leaner.
ix = find(tb>0);
[rows,cols]=ind2sub([3,4],ix);
% then for-loop through rows and cols to populate the final table.
% I still can't avoid for-loop.
  2 Comments
the cyclist
the cyclist on 1 Apr 2023
I'm confused about the organizing principle of the output you want. Is the following correct?
  • Currently, you have one row in your table for each code/fruit pair.
  • Instead, you one row for each code
  • The columns are "1st fruit", "2nd fruit", "3rd fruit"
  • In any row, you want the list of the fruits for that code
  • If the code doesn't have 3 fruits, have an empty entry in the table
Also, your Solution 1 and Solution 3 are different, but you say they are both correct. That's confusing to me.
If this is all correct, I'd be curious what your downstream step is. Your data are currently stored in what is known as tidy format, and that is almost always better for analysis.
Simon
Simon on 1 Apr 2023
Thanks for your quick response. The result from Solution 1 needs more processing to remove redundancy in each row. Solution 3 has the correct result.
My real data have more than hundreds of thousands of rows. Its first column stores 'account codes', the second column, 'account definition', and the third column is financial numerical values. For example, '1XXXXX' is the code, and 'Assets' is the account definition. These two should have a perfect 1-1 relationship. However, owing to human factor, the actual entry for 'account definition' for a given account code might slightly vary. For example, 'Assets' may be keyed in as 'Assest', or 'Aset'.
The code's downstream step is for me to visually check if there is anything odd about the 'account codes'-'account definition'. There are only around a hundred of unique 'account codes', which is more managable to human inspection than the original super-tall table.

Sign in to comment.

Accepted Answer

Stephen23
Stephen23 on 1 Apr 2023
Edited: Stephen23 on 27 Apr 2023
Using UNSTACK is quite a neat solution because it will automatically pad different-length data to the same number of columns, adding "missing" values as required. This is otherwise fiddly to replicate. But to use UNSTACK, we need to add a variable that tells UNSTACK which columns to move the data into:
C = {'q1','q1','q2','q3','q3','q3';'apple','appl','banana','orange','orang','orange'}.';
T = cell2table(C, 'VariableNames', {'code','fruit'})
T = 6×2 table
code fruit ______ __________ {'q1'} {'apple' } {'q1'} {'appl' } {'q2'} {'banana'} {'q3'} {'orange'} {'q3'} {'orang' } {'q3'} {'orange'}
U = unique(T,'rows');
G = findgroups(U.code); % note1
F = @(n)(1:nnz(n==G)).'; % note1
U.count = cell2mat(arrayfun(F,unique(G),'uni',0)) % note1
U = 5×3 table
code fruit count ______ __________ _____ {'q1'} {'apple' } 1 {'q1'} {'appl' } 2 {'q2'} {'banana'} 1 {'q3'} {'orange'} 1 {'q3'} {'orang' } 2
U = unstack(U,"fruit","count", "VariableNamingRule","modify")
U = 3×3 table
code x1 x2 ______ __________ __________ {'q1'} {'apple' } {'appl' } {'q2'} {'banana'} {0×0 char} {'q3'} {'orange'} {'orang' }
note1: This just gives a unique index to each element of a group. Astonishingly there does not seem to be an easy inbuilt way to achieve this... does anyone have any tips?: e.g. [1,1,1,2,2,1] -> [1,2,3,1,2,4] .
EDIT: I found a neater way:
G = findgroups(U.code);
U.count = grouptransform(ones(size(G)),G,@cumsum);
  11 Comments
Simon
Simon on 13 Apr 2023
When run over my real data, this soulution took elassed time of only 3.67 seconds. Your verctorized codes have both elegance and speed superiority over this forloop algorithm. Probably, the unique( ) funttion in the for-loop runs slows thing down, or maybe it's the array segmental assignment (U(k,2:...) = V. In my experience, value assignment to a segment of array, cell, and table causes sluggishness.
Great thanks for it nevertheless.
Stephen23
Stephen23 on 27 Apr 2023
Edited: Stephen23 on 27 Apr 2023
I thought of another approach based on GROUPTRANSFORM:
As mentioned in my answer, the desired transformation is [1,1,1,2,2,1] -> [1,2,3,1,2,4] .
G = [1;1,;1;2;2;1]; % must be column vector
Y = grouptransform(ones(size(G)),G,@cumsum)
Y = 6×1
1 2 3 1 2 4
Nice, it seems to work as we want. However in this case G luckily consists of integers 1..N. In all other cases we need to use e.g. FINDGROUPS first. Lets try it with the fake data that I used in my answer:
C = {'q1','q1','q2','q3','q3','q3';'apple','appl','banana','orange','orang','orange'}.';
T = cell2table(C, 'VariableNames', {'code','fruit'})
T = 6×2 table
code fruit ______ __________ {'q1'} {'apple' } {'q1'} {'appl' } {'q2'} {'banana'} {'q3'} {'orange'} {'q3'} {'orang' } {'q3'} {'orange'}
U = unique(T,'rows');
G = findgroups(U.code);
U.count = grouptransform(ones(size(G)),G,@cumsum)
U = 5×3 table
code fruit count ______ __________ _____ {'q1'} {'appl' } 1 {'q1'} {'apple' } 2 {'q2'} {'banana'} 1 {'q3'} {'orang' } 1 {'q3'} {'orange'} 2
U = unstack(U,"fruit","count", "VariableNamingRule","modify")
U = 3×3 table
code x1 x2 ______ __________ __________ {'q1'} {'appl' } {'apple' } {'q2'} {'banana'} {0×0 char} {'q3'} {'orang' } {'orange'}

Sign in to comment.

More Answers (1)

Peter Perkins
Peter Perkins on 5 Apr 2023
I'm having trouble understanding the desireed output, but others have created what is essentially a crosstabulation of counts, so, new in R2023a
>> T = cell2table(C, 'VariableNames', {'code', 'fruit'});
>> pivot(T,Rows="code",Columns="fruit")
ans =
3×7 table
code appl apple banana orang orange oranges
______ ____ _____ ______ _____ ______ _______
{'q1'} 1 2 0 0 0 0
{'q2'} 0 0 1 0 0 0
{'q3'} 0 0 0 1 2 1
As cyclist points out, there are a bunch of empty bins, so the original "tidy" format may be more useful. To me, this looks like a case of "fruit ought to be categorical, and you ought to apply mergecats to clean up those typos/different spellings".
  7 Comments
Simon
Simon on 11 Apr 2023
Hi Peter, I think I agree with you. I did have tried with categorical, like categorical(A) .* categorical(B). I had a huntch that it would work. Then the thought of using unstack( ) took hold of me, so I started pursuing that idea. I had used stack( ) and unstack( ) to solved other problems and really enjoyed using them. Then, I ran into a wall, so I posted the problem here. Thankfully, a very neat solution crafted from unstack( ) was soon provided by Stenpen23.
Simon
Simon on 13 May 2023
| > EDIT: I found a neater way:
|> G = findgroups(U.code);
|> U.count = grouptransform(ones(size(G)),G,@cumsum);
Sorry about being late response. I was overwhelmed by things. This is indeed a very neat solution. I used to think grouptransform( ) is quite limited in its function. But when it is put to work over a dummy/extra variable, it could be quite versatile in problem solving.

Sign in to comment.

Categories

Find more on Data Type Identification in Help Center and File Exchange

Products


Release

R2023a

Community Treasure Hunt

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

Start Hunting!