# Combine rows and average contents in table

6 views (last 30 days)
DavidL88 on 7 Apr 2021
Answered: Peter Perkins on 3 Mar 2022
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?
##### 2 CommentsShowHide 1 older comment
DavidL88 on 7 Apr 2021
Thanks. I attach the table here. I removed times outside the ranges given above.

dpb on 8 Apr 2021
% 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)];
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
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.
dpb on 9 Apr 2021
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);

Bob Thompson on 7 Apr 2021
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 on 8 Apr 2021
Thank you. The above script worked.

Peter Perkins on 3 Mar 2022
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.
>> 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]);
>> Tfinish.TimeBin = discretize(Tfinish.Time,edges,'categorical',catNames);
>> Tfinish.TimeBin = mergecats(Tfinish.TimeBin,["0.085-0.125" "UpperEdge1"]);
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