Reorganization of data in matrix

Hi

I need to reorganize some data. Its seems like a simple problem but I never seem to end up with the right solution!

I have matrix like this. The first column is the group (all the sames are in the same group, hence all are '1'). The second column denotes all the observations from the same sample. So, the sample one has 4 observations, same for sample two same for sample four, sample five had 3 observations.

1 1 0.270588872500870
1 1 0.191034400222846
1 1 0.316821350897659
1 1 0.391794699189989
1 2 0.290746129652351
1 2 0.329669792655481
1 2 0.320279988895299
1 2 0.296043180762110
1 4 0.334951618712500
1 4 0.212164936092998
1 4 0.338920673290546
1 4 0.374281296411533
1 5 0.363666308502410
1 5 0.291735460896171
1 5 0.388343803429984

I want to reorganise this so that each sample is then in its own column, as per below.

0.270589 0.290746 0.334952 0.363666

0.191034 0.32967 0.212165 0.291735

0.316821 0.32028 0.338921 0.388344

0.391795 0.296043 0.374281

It seems a simple problem but I seem to have hit a brick wall!

Thanks

Answer 1

[Stephen23]

You could DOWNLOAD Jos' excellent PADCAT:

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

and use it something like this:

S = load('mCopy.mat')

S = struct with fields:
    mCopy: [120×3 double]

M = S.mCopy

M = 120×3

1.0000 1.0000 0.2706 1.0000 1.0000 0.1910 1.0000 1.0000 0.3168 1.0000 1.0000 0.3918 1.0000 2.0000 0.2907 1.0000 2.0000 0.3297 1.0000 2.0000 0.3203 1.0000 2.0000 0.2960 1.0000 4.0000 0.3350 1.0000 4.0000 0.2122

C = accumarray(M(:,1:2),M(:,3),[],@(a){a});
C(cellfun(@isempty,C)) = []; % optional, remove empty samples
Z = padcat(C{:})

Z = 4×32

0.2706 0.2907 0.3350 0.3637 0.3362 0.2682 0.1732 0.3468 0.4089 0.2244 0.3977 0.3658 0.5310 0.4320 0.4572 0.3493 0.3247 0.3694 0.2398 0.1518 0.1579 0.1766 0.1507 0.1987 0.2412 0.1887 0.1210 0.1839 0.1361 0.1773 0.1910 0.3297 0.2122 0.2917 0.3107 0.3231 0.3224 0.2890 0.3949 0.3174 0.3836 0.4325 0.4596 0.3996 0.4607 0.3214 0.3123 0.3030 0.4166 0.1676 0.0540 0.1686 0.1466 0.1402 0.0435 0.2354 0.1808 0.1891 0.1208 0.1708 0.3168 0.3203 0.3389 0.3883 0.2502 0.2396 0.3193 0.2905 0.3839 0.3676 0.4057 0.4246 0.4458 0.4008 0.4270 0.3818 0.5186 0.3292 0.3541 0.1955 0.1212 0.1698 0.1366 0.1325 0.1964 0.1744 0.1741 0.1677 0.2149 0.1581 0.3918 0.2960 0.3743 NaN 0.3736 NaN 0.2954 NaN NaN 0.4512 0.4724 0.4225 0.3706 0.4395 0.4395 NaN 0.3106 0.3245 0.3087 0.1130 0.1783 0.1868 0.1258 0.1631 0.1198 0.1758 0.1417 0.2256 0.1822 0.2095

[James Alix]

Hi

Thanks for the suggestion. I'm not sure what its done to the data though.

I'm trying to end up with each sample (sample number in the second column) in its own row. So something like this:

0.2706 0.2907 0.3350 0.3637

0.1910 0.3297 0.2122 0.2917

0.3168 0.3203 0.3389 0.3883

0.3918 0.2960 0.3743 NaN ... And so on for all samples.

Thanks

[Stephen23]

"I'm not sure what its done to the data though."

It gives what you requested in your original question: "I want to reorganise this so that each sample is then in its own column, as per below."

0.270589 0.290746 0.334952 0.363666
0.191034 0.32967  0.212165 0.291735
0.316821 0.32028  0.338921 0.388344
0.391795 0.296043 0.374281

So my code has rearranged your data into the arrangement that you requested. Because numeric arrays cannot have holes in them, I used PADCAT to fill the holes with NaNs.

It just occured to me that perhaps you think that my code has changed the values (because only four fractional digits are shown): of course it hasn't changed the values, MATLAB simply displays numeric values according to the current FORMAT setting. Change the FORMAT to show more digits, if you want to. Note that changing what is displayed makes absolutely no difference to what data are stored in memory. What is in memory does not change with the FORMAT.

"I'm trying to end up with each sample (sample number in the second column) in its own row. "

Confusing: do you want each sample in its own column (in your question) or in its own row (in your comment)?

In any case, the difference is a simple transpose, which I am sure you are capable of.

[James Alix]

My apologies, I meant each sample in its own column, as you correctly deduced. This now works perfectly. Thanks very much.

[Stephen23]

"This now works perfectly."

My pleasure! Please remember to accept the answer that best resolves your question.

Answer 2

[Alan Stevens]

Like this?

M = [1 1 0.270588872500870
    1 1 0.191034400222846
    1 1 0.316821350897659
    1 1 0.391794699189989
    1 2 0.290746129652351
    1 2 0.329669792655481
    1 2 0.320279988895299
    1 2 0.296043180762110
    1 4 0.334951618712500
    1 4 0.212164936092998
    1 4 0.338920673290546
    1 4 0.374281296411533
    1 5 0.363666308502410
    1 5 0.291735460896171
    1 5 0.388343803429984];

% You need to add a row so that the end result forms a matrix
M = [M; 1 5 NaN];

M2 = reshape(M(:,3),4,4)

M2 = 4×4

0.2706 0.2907 0.3350 0.3637 0.1910 0.3297 0.2122 0.2917 0.3168 0.3203 0.3389 0.3883 0.3918 0.2960 0.3743 NaN

[James Alix]

Thanks for quick answer! Yes, that works a treat for that matrix.

Can I ask how I alter it for something with a different number of samples? E.g. still up to maxiumum of four observations per sample but now, say, 32 samples with sample numbering (i.e. column 2 in the first example) that starts at 1 and ends at, say, 68?

I thought that might be:

M = [M; 1 68 NaN];

M2 = reshape(M(:,3),4,32) - didn't work

So I tried:

M = [M; 1 68 NaN];

M2 = reshape(M(:,3),32,4) - didn't work.

Thanks

[Alan Stevens]

You need to make sure that each sample set has 4 rows, each set padded with NaNs in the last column if there are fewer than four rows for that set. Just adding one row at the end only works if the last set is the only one with three rows.

If you upload your larger data set I'll try to demonstrate with it.

[James Alix]

A larger data set is attached.

Thanks

J