data sorting for finding average and standard deviation

I have a .csv file that contains data for different speakers (s1, s2, s3, s4,s 5, s6, and s8) and words (a and i). Please see attached the input.csv. I want to create a .csv file that will calculate the average and standard deviation for each speaker and word and save it as my output.csv. Please note that the no. of repetitions of each speaker is different. It contains five repetitions but can vary. The output.csv is attached the way I want to sort out the data. The 1st block contains the average of the data for word a, the second block contains the standard deviation for word a, the third block contains the average for word i, and the fourth block contains the standard deviation for word b.

Any helpor suggestions would really be helpful.

Thanks

[KALYAN ACHARJYA]

Can you share the data in more simplify way ( Just sample data)?

[Anu]

How can I simplify the data more? I removed the filename; I believe we need everything else to compute the output.

Answer 1

[Cris LaPierre]

I would look into using groupsummary. I'll let you worry about getting the output formatted the way you want, but here's how I would do it.

file = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/1185463/input.csv';
in = readtable(file, ReadVariableNames=true)

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

in = 70×9 table
           Filename           speaker    word       x0       x25       x50       x75       x100      x_10 
    ______________________    _______    _____    ______    ______    ______    ______    ______    ______

    {'s1_ba_hand1_sn.mat'}    {'s1'}     {'a'}    26.193    27.783    29.115    17.074    30.826    26.214
    {'s1_ba_hand2_sn.mat'}    {'s1'}     {'a'}     27.76    33.447    35.136    37.654    22.891    31.369
    {'s1_ba_hand3_sn.mat'}    {'s1'}     {'a'}    25.837    32.221     35.78    21.734    18.505      26.8
    {'s1_ba_hand4_sn.mat'}    {'s1'}     {'a'}    25.019    27.369    33.085    33.949    17.378    27.304
    {'s1_ba_hand5_sn.mat'}    {'s1'}     {'a'}    26.226    34.217     25.61    18.947      20.2    25.139
    {'s2_ba_hand1_sn.mat'}    {'s2'}     {'a'}    28.427    23.724    36.614    40.826    42.439    32.939
    {'s2_ba_hand2_sn.mat'}    {'s2'}     {'a'}    37.886    35.471    38.374    43.248    50.057    40.417
    {'s2_ba_hand3_sn.mat'}    {'s2'}     {'a'}    24.966     30.21    35.149    47.312    39.151    35.332
    {'s2_ba_hand4_sn.mat'}    {'s2'}     {'a'}    40.954    33.632    34.467    48.077    44.693    39.876
    {'s2_ba_hand5_sn.mat'}    {'s2'}     {'a'}    30.454    30.629    34.875    42.623    41.651    36.028
    {'s3_ba_hand1_sn.mat'}    {'s3'}     {'a'}    19.576    30.789    26.856    36.898    34.848    30.703
    {'s3_ba_hand2_sn.mat'}    {'s3'}     {'a'}    26.645    26.327    35.067    30.363    41.449    31.354
    {'s3_ba_hand3_sn.mat'}    {'s3'}     {'a'}    27.859    34.175    31.954    37.054    34.548    32.769
    {'s3_ba_hand4_sn.mat'}    {'s3'}     {'a'}    31.169    33.918    33.692    39.192    41.542     35.69
    {'s3_ba_hand5_sn.mat'}    {'s3'}     {'a'}     30.14    25.299     25.09    34.739    43.441    31.506
    {'s4_ba_hand1_sn.mat'}    {'s4'}     {'a'}    19.031    13.718    16.913    19.005    18.918    17.241

out = groupsummary(in,["word","speaker"],["mean","std"],["x0" "x25" "x50" "x75" "x100" "x_10"])

out = 14×15 table
    word     speaker    GroupCount    mean_x0    std_x0     mean_x25    std_x25    mean_x50    std_x50    mean_x75    std_x75    mean_x100    std_x100    mean_x_10    std_x_10
    _____    _______    __________    _______    _______    ________    _______    ________    _______    ________    _______    _________    ________    _________    ________

    {'a'}    {'s1'}         5         26.207     0.99506     31.007     3.2156      31.745       4.305     25.872      9.3078      21.96       5.3736      27.365       2.3788 
    {'a'}    {'s2'}         5         32.537       6.671     30.733     4.4795      35.896      1.6046     44.417      3.1328     43.598       4.1179      36.918       3.1675 
    {'a'}    {'s3'}         5         27.078      4.5604     30.102     4.1516      30.532      4.3504     35.649      3.3488     39.166       4.1564      32.404       1.9833 
    {'a'}    {'s4'}         5         15.921       1.995     12.586     6.7532      19.368      2.2317     19.144      3.3637     18.284       3.0697      16.657       1.4024 
    {'a'}    {'s5'}         5          32.45      5.9051     28.397     7.7048      28.772      7.2628     29.067      4.8905      32.25       4.7641      30.026       3.5693 
    {'a'}    {'s6'}         4         24.483      5.7752     27.424     5.5905      28.006      3.3546     34.017      1.0486     36.207       1.2769      30.129       1.7499 
    {'a'}    {'s8'}         6         25.664      1.5629     29.728     4.4663      32.854      2.4832     33.248      4.4637     35.208       1.3438      31.379       2.3633 
    {'i'}    {'s1'}         5         15.858      1.8331     13.817     1.6314      12.734     0.95818     11.436     0.60638     11.502      0.58935      13.129      0.92649 
    {'i'}    {'s2'}         5         17.269      8.6292     14.882     5.6135      13.394      7.2987     12.528      4.7957     16.626       6.7042      15.183       4.8653 
    {'i'}    {'s3'}         5         24.859      4.1878     19.584     2.9017      14.638      4.1973     17.799      2.9111     16.152       4.6308      18.357       1.4848 
    {'i'}    {'s4'}         5         16.386      18.964     9.8822     10.748      10.023      8.8792     3.2944      2.3035     4.2764       3.1281      9.0576       6.9605 
    {'i'}    {'s5'}         5         16.226      4.5978     11.171      2.626      11.487      6.6092     12.849      9.7372     11.639       4.9018      12.654       3.4315 
    {'i'}    {'s6'}         6         20.605      18.693     14.119     17.016      14.059      6.2357     13.706      1.3367      13.71       1.5873      15.061       6.5605 
    {'i'}    {'s8'}         4         11.145      1.9183     2.8262     1.3731      3.7963      3.7148     6.2533        7.53     12.593       6.1108       7.344       3.5923