Getting Data from Cell: Dynamic Structure needed?

Hello again! Your expertise is needed to help as I've reached a point where I no longer know how to proceed. Let me explain

I have different datasets from flow measurements in rivers. The tables all look something like this

On the left side you can see a the column "Method" which describes at how many depth levels data for the flow was collected. For Method "1 Punkt" (short: 1) only at 60% of the depth a measurement is done. For Method "3" it is done at 20, 60 and 80% respectively. There are 6 different Methods in total, however these are the ones used in the example. My idea is it to create a script which can handle datasets regardless of the different Method's that are used.

I've now run into a problem: Before I just told Matlab to create a list of every value that is not 0. However as you can see in this example in Row 4 the method ("1 Punkt") indicates that in column 5 speed data is to be found however the speed that was measured was 0. Which breaks my algorithm.

I've tried to come up with a for-loop but I don't see any possibility which does not need hard coding countless if structures.

The idea is to have a loop go through the rows and depending on which method is used to take the value of the needed data and create a list or vector with it and append the values of the row below etc. I Best case scenario the script can handle even more complex datasets where every Method from 1-6 is used for example.

The different calculations Methods need the following Columns for Calculation:

Does this not need a dynamic structure? I'm not good in programming so I need help to solve the problem.

I hope this reaches you well and is as clear as possible and hopefully you can help me.

Thank you guys!

Simon

Answer 1

[Stephen23]

"Does this not need a dynamic structure?"

I don't know what a "dynamic structure" is, but it looks like some very basic indexing would do what you need. Because you did not upload data to make answering this question easier for us I will have to invent a fake data file (attached), which is not a good as having your actual data to work with, and slows down getting a working answer.

You can see from the second pair of outputs displayed in the loop, that it works perfectly when the data are zero.

T = readtable('test.csv', 'VariableNamingRule','preserve', 'TextType','string')

T = 17×7 table
       Methode       Oberfl. (m/s)    0,2 (m/s)    0,4 (m/s)    0,6 (m/s)    0,8 (m/s)    Sohle (m/s)
    _____________    _____________    _________    _________    _________    _________    ___________

    "linkes Ufer"          0                0          0              0            0           0     
    "1 Punkt"              0                0          0         -0.049            0           0     
    "1 Punkt"              0                0          0              0            0           0     
    "1 Punkt"              0                0          0          0.063            0           0     
    "1 Punkt"              0                0          0          0.446            0           0     
    "1 Punkt"              0                0          0          0.417            0           0     
    "1 Punkt"              0                0          0          0.562            0           0     
    "1 Punkt"              0                0          0          0.189            0           0     
    "1 Punkt"              0                0          0          0.497            0           0     
    "1 Punkt"              0                0          0          0.615            0           0     
    "1 Punkt"              0                0          0          0.722            0           0     
    "3 Punkt"              0            0.652          0          0.441        0.148           0     
    "3 Punkt"              0            0.693          0          0.438        0.167           0     
    "3 Punkt"              0            0.585          0          0.441        0.236           0     
    "1 Punkt"              0                0          0          0.251            0           0     
    "1 Punkt"              0                0          0          0.157            0           0     

C = {5,[3,6],[3,4,6],[2,3,5,6,7],[2,3,4,5,6,7]}; % column indices.
for k = 1:height(T) % loop over rows of the table.
    N = sscanf(T{k,1},'%f'); % extract "Method" number.
    if isscalar(N)
        display(N)
        V = T{k,C{N}} % extract the specified row and columns from the table.
        ... do whatever you want with those values in V.
    end
end

N = 1

V = -0.0490

N = 1

V = 0

N = 1

V = 0.0630

N = 1

V = 0.4460

N = 1

V = 0.4170

N = 1

V = 0.5620

N = 1

V = 0.1890

N = 1

V = 0.4970

N = 1

V = 0.6150

N = 1

V = 0.7220

N = 3

V = 1×3

0.6520 0 0.1480

N = 3

V = 1×3

0.6930 0 0.1670

N = 3

V = 1×3

0.5850 0 0.2360

N = 1

V = 0.2510

N = 1

V = 0.1570

[Simon Möbs]

First of all! Thank you for such a quick answer. You guys will always amaze me with your helpfulness. I will check your idea tomorrow and will then accept your answer but it looks very promising, just so you're not wondering.

Thank you also for reminding me to provide some more data. I uploaded it, however I think your interpretation is more than good enough and I think I can make things work but will keep you updated!

Answer 2

[Peter Perkins]

Simon, what you should do depends very heavily on what you want to do with these data, i.e. what "take the value of the needed data and create a list or vector with it and append the values of the row below etc." means.

Your zipped up files seem like an exercise in pre-processing, so I've used Stephen23's example file.

>> T = readtable('test.csv', 'VariableNamingRule','preserve', 'TextType','string');
>> T.Methode = categorical(T.Methode,["1 Punkt" "2 Punkt" "3 Punkt"]);
>> T = T(~ismissing(T.Methode),:)
T =
  15×7 table
    Methode    Oberfl. (m/s)    0,2 (m/s)    0,4 (m/s)    0,6 (m/s)    0,8 (m/s)    Sohle (m/s)
    _______    _____________    _________    _________    _________    _________    ___________
    1 Punkt          0                0          0         -0.049            0           0     
    1 Punkt          0                0          0              0            0           0     
    1 Punkt          0                0          0          0.063            0           0     
    1 Punkt          0                0          0          0.446            0           0     
    1 Punkt          0                0          0          0.417            0           0     
    1 Punkt          0                0          0          0.562            0           0     
    1 Punkt          0                0          0          0.189            0           0     
    1 Punkt          0                0          0          0.497            0           0     
    1 Punkt          0                0          0          0.615            0           0     
    1 Punkt          0                0          0          0.722            0           0     
    3 Punkt          0            0.652          0          0.441        0.148           0     
    3 Punkt          0            0.693          0          0.438        0.167           0     
    3 Punkt          0            0.585          0          0.441        0.236           0     
    1 Punkt          0                0          0          0.251            0           0     
    1 Punkt          0                0          0          0.157            0           0     

I can use rowfun to step through that table and do "something" with each one. Here, "something" is "put the values of interest in a cell as a row vector", but you can do anything you want:

>> T1 = rowfun(@myFun1,T,OutputVariableNames=["Methode" "Ragged"])
T1 =
  15×2 table
    Methode           Ragged        
    _______    _____________________
    1 Punkt    {[           -0.049]}
    1 Punkt    {[                0]}
    1 Punkt    {[            0.063]}
    1 Punkt    {[            0.446]}
    1 Punkt    {[            0.417]}
    1 Punkt    {[            0.562]}
    1 Punkt    {[            0.189]}
    1 Punkt    {[            0.497]}
    1 Punkt    {[            0.615]}
    1 Punkt    {[            0.722]}
    3 Punkt    {[0.652 0.441 0.148]}
    3 Punkt    {[0.693 0.438 0.167]}
    3 Punkt    {[0.585 0.441 0.236]}
    1 Punkt    {[            0.251]}
    1 Punkt    {[            0.157]}

where myFun1 is

function [punkt,ragged] = myFun1(punkt,oberfl,zwei,fier,sechs,acht,sohle)
if punkt == "1 Punkt"
    ragged = {sechs};
elseif punkt == "2 Punkt"
    ragged = {[zwei acht]};
elseif punkt == "3 Punkt"
    ragged = {[zwei sechs acht]};
end
end

Of course, myFun1 could have done some else like taken the mean value:

function [punkt,meanVal] = myFun1(punkt,oberfl,zwei,fier,sechs,acht,sohle)
if punkt == "1 Punkt"
    meanVal = sechs;
elseif punkt == "2 Punkt"
    meanVal = mean([zwei acht]);
elseif punkt == "3 Punkt"
    meanVal = mean([zwei sechs acht]);
end
end

Another possibility is to collect all the data for each method and do something with it:

>> T2 = rowfun(@myFun2,T,GroupingVariables="Methode",InputVariables=1:7)

T2 =
  2×3 table
    Methode    GroupCount        Var3     
    _______    __________    _____________
    1 Punkt        12        {12×1 double}
    3 Punkt         3        { 3×3 double}

where myFun2 is

function value = myFun2(punkt,oberfl,zwei,fier,sechs,acht,sohle)
if punkt == "1 Punkt"
    value = {sechs};
elseif punkt == "2 Punkt"
    value = {[zwei acht]};
elseif punkt == "3 Punkt"
    value = {[zwei sechs acht]};
end
end