Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Extract and rearrange data in Matlab

  1. May 10, 2013 #1
    Hi all,

    I need to rearrange my data. I have an excel file with 12 columns and 2045 rows, which I transformed that into a .mat.

    I need to query my data rearranged as follows (cf. example matrix below):

    1-sum all values of Col 3 if:
    a-they belong to a given value of Col 1 and to a given value in Col 2.
    2-mean of values of Col 4 for each given value of Col 1

    3-NaN's where conditions above are not met
    4-all saved in a new matrix

    The difficulty is to make this work for the 2045 rows of my database. I guess it needs a for-loop that reiterates the calculations and at each step saves the values in a matrix.

    Here is an example:

    input matrix A:

    1 NaN NaN 20
    1 2 4 10
    1 2 3 15
    1 NaN NaN 10
    1 5 2 25
    1 6 1 10
    1 2 5 5
    2 NaN NaN 5
    2 1 3 10
    2 1 2 25
    3 6 5 15
    3 4 3 10
    3 3 1 30

    This is what I'd like to get:

    1 2 3 4 5 6 Mean
    1 NaN 12 NaN NaN 2 1 13.57
    2 5 NaN NaN NaN NaN NaN 13.33
    3 NaN NaN 1 3 NaN 5 18.33

    I'd really appreciate your help and will include you in the acknowledgments of the 2 papers I will get out of this.

    Thanks a lot!!

    g
     
  2. jcsd
  3. May 10, 2013 #2
    (hope) these are nicer matrices:

    Input:

    1 NaN NaN 20
    1 2 4 10
    1 2 3 15
    1 NaN NaN 10
    1 5 2 25
    1 6 1 10
    1 2 5 5
    2 NaN NaN 5
    2 1 3 10
    2 1 2 25
    3 6 5 15
    3 4 3 10
    3 3 1 30

    desired output:

    1 2 3 4 5 6 mean
    1 NaN 12 NaN NaN 2 1 13.57
    2 5 NaN NaN NaN NaN NaN 13.33
    3 NaN NaN 1 3 NaN 5 18.33
     
  4. May 11, 2013 #3

    kreil

    User Avatar
    Gold Member

    Can you be more specific? The example you gave wasn't very clear, but this should be a simple problem to overcome. For ex, the first row in your "desired output" has 6 values, and the rest have 8..
     
  5. May 11, 2013 #4
    Hi Kreil,

    Sorry for the bad format...
    Let me know if the attachments work...

    Here is what my data means in real life:

    Input matrix
    Rows are individual observations
    Col 1 = transect number
    Col 2= species code
    Col 3= count of animals of corresponding species
    Col 4= % ice cover for that specific observation

    Ouput:

    Col 1= transect number
    Col 2 to Col 7 = sum of animal counts of species 1 to species 6
    Col 8= mean ice cover for corresponding transect

    N=NaN

    Thanks!

    g
     

    Attached Files:

    Last edited: May 11, 2013
  6. May 11, 2013 #5

    kreil

    User Avatar
    Gold Member

    I'll write up an m-file that works on that data, but you'll probably need to edit it to make it work with your actual data (since it has more columns and possibly more than 3 transects)
     
  7. May 11, 2013 #6
    thanks so much Kreil!!
    :-)
    g
     
  8. May 11, 2013 #7

    kreil

    User Avatar
    Gold Member

    I wrote this up quickly so there are likely ways you can improve it. For the time being, it returns your desired output.
    Code (Text):

    %sample data matrix, A
    A = [1 NaN NaN 20;1 2 4 10;1 2 3 15;1 NaN NaN 10;1 5 2 25;1 6 1 10;1 2 5 5;2 NaN NaN 5;2 1 3 10;2 1 2 25;3 6 5 15;3 4 3 10;3 3 1 30];

    %Change NaN's in A to zeros for summing purposes
    A(isnan(A))=0;

    %Change these vars to match real counts
    Transects = 3;
    Species = 6;

    %Preallocate output
    Out = zeros(Transects,8);

    for i = 1:Transects
        %First column is just transect number
        Out(i,1) = i;
        %B is the subset of A for current transect
        B = A(A(:,1)==i,:);
        %C is the subset of B for current species
        for k = 1:Species
            C = B(B(:,2)==k,:);
            Out(i,k+1) = sum(C(:,3));
        end
        %Ice cover means
        Out(i,end) = mean(B(:,4));
    end

    %Convert the zeros back to NaN's
    Out(Out==0)=NaN;
     
    Since a numeric matrix in matlab cannot have an empty entry or a char like 'mean', this output is identical to your desired output without the first row. You'll just have to remember that column 2 is species 1, column 3 is species 2, etc...
     
  9. May 11, 2013 #8
    fantastic, Kreil! I'll try it out on monday and will let you know!
    g
     
  10. May 13, 2013 #9
    Kreil, please drop me a line:
    cortoeldemalta at yahoo dot com dot ar
    geoffrey
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Extract and rearrange data in Matlab
  1. Matlab data extracting (Replies: 6)

Loading...