Combine rows and average contents in table

I have a 17,700x4 table T with 4 columns containing the results of multiple tests. First column is the name of a test. There are 149 rows for each test. The fourth column contains 149 time points with values ranging from 0.085 seconds to 0.9 seconds for each test. The second and third columns are voltage values assessed at each time point. I have four time ranges ([0.085-0.125, 0.17-0.23, 0.27-0.45, 0.6-0.9) within which I want to average the voltage values for each named test.

Example of a few rows for one test 'MLD_CG_11_FCL' in current table

TEST                        V1         V2       Time
MLD_CG_11_FCL             -0.68      -0.73      0.086
MLD_CG_11_FCL             -0.98      -0.89      0.090
MLD_CG_11_FCL             -1.22      -0.97      0.094
MLD_CG_11_FCL             -1.37      -0.98      0.098
MLD_CG_11_FCL             -1.41      -0.93      0.102
MLD_CG_11_FCL             -1.33      -0.85      0.105
MLD_CG_11_FCL             -1.16      -0.75      0.109
MLD_CG_11_FCL             -0.93      -0.64      0.113
MLD_CG_11_FCL             -0.66      -0.48      0.117
MLD_CG_11_FCL             -0.39      -0.28      0.121
MLD_CG_11_FCL             -0.14      -0.03      0.125

I'm looking to get this. Where V1 and V2 are now the average of the values within the time range thus collapsing the 149 rows per test into 4 rows per test.

TEST                        V1         V2       Time range
MLD_CG_11_FCL             -0.93      -0.69      0.085-0.125
MLD_CG_11_FCL             -0.50      -0.90      0.17-0.23
MLD_CG_11_FCL             -1.28      -0.95      0.27-0.45
MLD_CG_11_FCL             -1.40      -0.33      0.6-0.9

Can anyone advise how I do this?

[dpb]

Be easier to illustrate with an actual data file covering the ranges, but discretize and findgroups along with rowfun will solve the problem -- if.

The IF on that solution is whether the time bins are indeed disjoint as given and there are data possibly between the group boundaries defined as given above.

If there are, one will have to calculate the range differently; to use discretize one would set the boundaries at the midpoint between the upper and lower boundaries for the middle ranges on the assurance there's not data in the cracks being included that shouldn't be.

MATLAB doesn't have a builtin function that handles disjoint bins altho I suppose one could create an additional bin between the regions above now that think about it.

Anyway, pick a subset of your table and attach it as a .mat file -- altho the whole thing is probably only a few MB???

[DavidL88]

Thanks. I attach the table here. I removed times outside the ranges given above.

Answer 1

[dpb]

% smoosh edges of disjoint regions to midpoints between...
edges=[0.085 0.125; 0.17 0.23; 0.27 0.45; 0.6 0.9];
e=edges.'; e=e(:);
e=[e(1) mean(reshape(e(2:end-1),2,[])) e(end)];
load voltage
Tfinish.Properties.VariableNames(1)={'Test'};
Tfinish.Test=categorical(Tfinish.Test);
Tfinish.TimeBin=discretize(Tfinish.Var4,e);         %  create the variable for grouping by time 
tMeanByTest=rowfun(@(x,y)deal(mean(x,1),mean(y,1)),Tfinish, ...
                    'InputVariables',{'Var2','Var3'}, ...
                    'GroupingVariables',{'Test','TimeBin'}, ...
                    'SeparateInputs',1, ...
                    'OutputVariableNames',{'MeanV2','MeanV3'});

provides

>> head(tMeanByTest)
ans =
  8×5 table
            Test            TimeBin    GroupCount    MeanV2     MeanV3 
    ____________________    _______    __________    _______    _______
    CG_HGH_CG_LOW_11_CPL       1           11        0.91667    0.23436
    CG_HGH_CG_LOW_11_CPL       2           15         2.1104     1.0282
    CG_HGH_CG_LOW_11_CPL       3           46         4.5008     3.2523
    CG_HGH_CG_LOW_11_CPL       4           77         2.7981     2.0317
    CG_HGH_CG_LOW_11_CPR       1           11        0.54696    0.53518
    CG_HGH_CG_LOW_11_CPR       2           15         1.6762     1.0518
    CG_HGH_CG_LOW_11_CPR       3           46         2.4635     1.0401
    CG_HGH_CG_LOW_11_CPR       4           77         1.4723     1.1045
>> 

You can turn the TimeBin variable to categorical with the bin names/ids you've outlined above for labels.

If the above approximation for the bin edges for discretize is not allowable, two ways to go at it--

1. add extra edges for bins between those defined and then remove the unwanted bins, or
2. use interp1 with a 'nearest' interpolation scheme to do the discretization process in which you can dump all those in the unwanted regions into one bin and remove it.

Or, of course, you can use explicit coding with case statement or if...elseif... construct.

[DavidL88]

That worked cheers!

[dpb]

Having data makes things much easier to illustrate! :)

And, indeed, grouping variables and rowfun and/or splitapply are exceedingly powerful constructs worth getting to know. There's a learning curve, but once work through one or two, it becomes relatively painless although certain "tricks" like the deal() one to return multiple outputs from an anonymous function are things that generally only come with longer "time in grade" using MATLAB to know...

[DavidL88]

Thank you! I'll keep practising. Could you advise on another thing?

I'm trying to create a new column in this table called ERP based on data in the other columns. I used the below script.

tMeanByTest.ERP = repmat("No", height(tMeanByTest), 1)
tMeanByTest.ERP(tMeanByTest.TimeBin == 1) = "P1"
tMeanByTest.ERP(tMeanByTest.TimeBin == 2) = "P2"
tMeanByTest.ERP(tMeanByTest.TimeBin == 3) = "P3"
tMeanByTest.ERP(tMeanByTest.TimeBin == 4) = "LPP"

For the second conditional, ending in 'P2', is there a way to add another conditional here where I get 'P2', but if POL or POR, or simply PO, are contained in tMeanByTest.Test then I get 'N1' instead of 'P2'?

[dpb]

Hmmm...as noted earlier, you really want a categorical variable for the binning variable; I just took the expedient.

I had figured the categorical labels would be strictly in concert with the numerical order; I've not thought about the idea of trying to create a conditional categorial.

With that backdrop, a Q? Is this truly then a categorical breakdown of the Test type/condition/whatever based on the existence of the string or will this, I gather, build in essence a bi-valued ID for bin 2?

That is, you still want averages over the presently as-defined time bins, but some pieces of a given bin are still different than others.

[DavidL88]

Yes I still want the averages within those defined time bins. The FC/CP/PO (& Left or Right) are different regions of interests. The P2 and N1 components arise in the same time window (TimeBin 2), with the N1 component arising in the PO regions. So I was looking for a way to differentiate between these automatically to help interpret the data.

Hope that answers your question.

[dpb]

BTW, "the MATLAB way" to write the above would be more akin to

ERPLABELS=["P"+(1:3) "LPP"];           % define labels by bin number
tMeanByTest.ERP=ERPLABELS(tMeanByTest.TimeBin).';

This could also be done by creating a categorical variable instead of string just by defining ERPLABELS as categorical().

Handling the split category Q? is still open at this point

[DavidL88]

Thank you. I don't understand the question? Two ERP components (P2 & N1) occur within the same time window (TimeBin 2) but in different regions so for these two components I would need to define them based on both time window and region. So these two components would require both a breakdown based on string and being placed in bin 2?

[dpb]

Well, remember I have no idea what an ERP is, so I may ask dumb Q? because I simply don't recognize what is obvious to an expert in the field. :)

To address the second set would/does then need a second variable for grouping if want to group between the two -- I was trying to see if could work around the second variable and just keep the one.

The above already assigns the "P2" to bin 2 globally, to add the conditional in that logic would be more convoluted than just taking the subsequent step of

isPO=contains(string(tMeanByTest.Test),'PO')&tMeanByTest.TimeBin==2;
tMeanByTest.ERP(isPO)="N1";

A search of the given dataset, however, reveals

>> sum(contains(string(tMeanByTest.Test),'PO'))
ans =
     0
>> 

that there are no test IDs that match. Maybe that's a known result and there are other dataset content not included?

[DavidL88]

That code worked thanks! It classified the results exactly as I wanted. I didn't know how to use a conditional based on two variables.

ERP is event-related potential from electroencephalography. I made a mistake in extracting the data for this table so POR/POL weren't included with other regions of interest. Sorry about that!

[dpb]

We didn't have any ERPs in NucE, so thanks for explanation! :)

The above syntax/MATLAB coding idiom is use of "logical indexing"; a very powerful and important feature in MATLAB. Anywhere you can use a numeric indexing expression you can also use a logical one; the result will be to address those elements of the addressing expression containing TRUE and also recognize that one can use the NOT operator "~" to change the sense for things like

isOK=~cellfun(@isempty,somecellstrarray);

Answer 2

[Bob Thompson]

I'm not sure how you're defining the time time ranges but here's some psuedo code that I thought about for the rest of the process.

1) Import data into table; readtable

2) Determine names of different tests; unique

3) Loop through each test; for with ismember to select relevant rows

4) Determine time ranges; Not sure if you want to use user inputs or what for this

5) Loop through each time range; for with ismember

6) Should now have specific dataset for test and time, do average; mean

7) Append test name, averages, and time (two columns to keep as numbers?) to output array.

[DavidL88]

Thank you. The above script worked.

Answer 3

[Peter Perkins]

In addition to dpb's solution that uses rowfun, here's a similar sol'n that uses varfun. The difference is that varfun works on one var at a time, while rowfun allows you to apply a function to multiple vars at once. So in that sense, rowfun is overkill here.

It looks like there are times that are exactly .125, but none that are exactly .23 or .45. That's what the wrangling of categories is all about.

>> load('Sample_table_voltage.mat')
>> Tfinish.Properties.VariableNames = ["Test" "V1" "V2" "Time"];
>> Tfinish.Test = categorical(Tfinish.Test);
>> Tfinish.Time = seconds(Tfinish.Time)
>> edges = seconds([0.085 0.125 0.17 0.23 0.27 0.45 0.6 0.9]);
>> catNames = ["0.085-0.125" "UpperEdge1" "0.17-0.23" "Discard1" "0.27-0.45" "Discard2" "0.6-0.9"];
>> Tfinish.TimeBin = discretize(Tfinish.Time,edges,'categorical',catNames);
>> Tfinish.TimeBin = mergecats(Tfinish.TimeBin,["0.085-0.125" "UpperEdge1"]);
>> Tfinish.TimeBin = removecats(Tfinish.TimeBin,["Discard1" "Discard2"])
Tfinish =
  17731×5 table
            Test                V1          V2            Time          TimeBin  
    _____________________    ________    _________    ____________    ___________
    MLD_CG_11_FCL            -0.67827     -0.73036    0.085938 sec    0.085-0.125
    MLD_CG_11_FCL             -0.9777     -0.89304    0.089844 sec    0.085-0.125
    MLD_CG_11_FCL             -1.2218     -0.97293     0.09375 sec    0.085-0.125
[snip]
    MOD_HGH_CG_HGH_22_CPL      1.2733      0.64462     0.89062 sec    0.6-0.9    
    MOD_HGH_CG_HGH_22_CPL      1.2546      0.62261     0.89453 sec    0.6-0.9    
    MOD_HGH_CG_HGH_22_CPL       1.192      0.59208     0.89844 sec    0.6-0.9    
>> varfun(@mean,Tfinish,"GroupingVariables",["Test" "TimeBin"])
ans =
  472×6 table
            Test               TimeBin      GroupCount     mean_V1      mean_V2      mean_Time 
    _____________________    ___________    __________    _________    _________    ___________
    CG_HGH_CG_LOW_11_CPL     0.085-0.125        11          0.91667      0.23436    0.10547 sec
    CG_HGH_CG_LOW_11_CPL     0.17-0.23          15           2.1104       1.0282    0.19922 sec
    CG_HGH_CG_LOW_11_CPL     0.27-0.45          46           4.5008       3.2523    0.36133 sec
[snip]
    MOD_LOW_CG_LOW_22_FCR    0.17-0.23          15           1.3824       1.3781    0.19922 sec
    MOD_LOW_CG_LOW_22_FCR    0.27-0.45          46         -0.38971    -0.025518    0.36133 sec
    MOD_LOW_CG_LOW_22_FCR    0.6-0.9            77         -0.60483     -0.47444       0.75 sec