# How to average strings and numbers in the same table

1 view (last 30 days)
Elizabeth Korthals on 7 Oct 2021
Commented: Steven Lord on 7 Oct 2021
I have a table that looks like this:
filename X Y Item Number
'A1B10' 60 25 'A1B10-0'
'A1B10' 45 21 'A1B10-0'
'A3B10' 70 24 'A3B10-1'
'A3B10' 40 23 'A3B10-2'
'A3B5' 38 21 'A3B5-1'
I want to average all rows that have the exact same "Item Number". However, I am not sure how to do this while preserving the "filename".
In other words, the table would look like this in the end:
filename X Y Item Number
'A1B10' 82.5 23 'A1B10-0'
'A3B10' 70 24 'A3B10-1'
'A3B10' 40 23 'A3B10-2'
'A3B5' 38 21 'A3B5-1'
Any thoughts on how I could approach this?
Image Analyst on 7 Oct 2021
and attach your table in a .mat file with the paperclip icon. Or else give us code to build that table. If you make it easy for people to help you, you'll get more and faster answers. We don't have this table -- you do. So please supply it to us so we can try things and give you code.
In the meantime, check out the splitapply(), findgroups(), and groupsummary() functions.

Steven Lord on 7 Oct 2021
Something like this?
filename = {'A1B10'; 'A1B10'; 'A3B10'; 'A3B10'; 'A3B5'};
X = [60; 45; 70; 40; 38];
Y = [25; 21; 24; 23; 21];
itemNumber = {'A1B10-0'; 'A1B10-0'; 'A3B10-1'; 'A3B10-2'; 'A3B5-1'};
T = table(filename, X, Y, itemNumber)
T = 5×4 table
filename X Y itemNumber _________ __ __ ___________ {'A1B10'} 60 25 {'A1B10-0'} {'A1B10'} 45 21 {'A1B10-0'} {'A3B10'} 70 24 {'A3B10-1'} {'A3B10'} 40 23 {'A3B10-2'} {'A3B5' } 38 21 {'A3B5-1' }
G = groupsummary(T, {'itemNumber', 'filename'}, @mean)
G = 4×5 table
itemNumber filename GroupCount fun1_X fun1_Y ___________ _________ __________ ______ ______ {'A1B10-0'} {'A1B10'} 2 52.5 23 {'A3B10-1'} {'A3B10'} 1 70 24 {'A3B10-2'} {'A3B10'} 1 40 23 {'A3B5-1' } {'A3B5' } 1 38 21
Elizabeth Korthals on 7 Oct 2021
Yes! That's great thank you!

Matt J on 7 Oct 2021
Edited: Matt J on 7 Oct 2021
T,
T = 5×4 table
filename X Y Item Number _________ __ __ ___________ {'A1B10'} 60 25 {'A1B10-0'} {'A1B10'} 45 21 {'A1B10-0'} {'A3B10'} 70 24 {'A3B10-1'} {'A3B10'} 40 23 {'A3B10-2'} {'A3B5' } 38 21 {'A3B5-1' }
Tmean=varfun(@mean, T,'Group',{'filename','Item Number'},'Input',{'X','Y'});
Tmean=Tmean(:,[1,4,5,2]);
Tmean.Properties.VariableNames(2:3)={'X','Y'}
Tmean = 4×4 table
filename X Y Item Number _________ ____ __ ___________ {'A1B10'} 52.5 23 {'A1B10-0'} {'A3B10'} 70 24 {'A3B10-1'} {'A3B10'} 40 23 {'A3B10-2'} {'A3B5' } 38 21 {'A3B5-1' }
Steven Lord on 7 Oct 2021
You can also vote for the answers that helped you.