Combine rows and average contents in table

조회 수: 3(최근 30일)
DavidL88 2021년 4월 7일
댓글: dpb 2021년 4월 9일
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
DavidL88 2021년 4월 7일
Thanks. I attach the table here. I removed times outside the ranges given above.

댓글을 달려면 로그인하십시오.

채택된 답변

dpb 2021년 4월 8일
% 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.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, ...
>> 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.
  댓글 수: 10
dpb 2021년 4월 9일
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

댓글을 달려면 로그인하십시오.

추가 답변(1개)

Bob Thompson
Bob Thompson 2021년 4월 7일
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.
  댓글 수: 1
DavidL88 2021년 4월 8일
Thank you. The above script worked.

댓글을 달려면 로그인하십시오.


Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by