How to average strings and numbers in the same table

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]

The first way I'd approach it is to read this link:

TUTORIAL: How to ask a question (on Answers) and get a fast answer

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.

Answer 1

[Steven Lord]

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]

Yes! That's great thank you!

Answer 2

[Matt J]

load data

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' }

[Elizabeth Korthals]

Thank you! That worked great!

[Matt J]

You're welcome, but please Accept-click one of the (effective) answers.

[Steven Lord]

You can also vote for the answers that helped you.