data sorting for finding average and standard deviation

1 view (last 30 days)
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
  2 Comments
Anu
Anu on 8 Nov 2022
How can I simplify the data more? I removed the filename; I believe we need everything else to compute the output.

Sign in to comment.

Answers (1)

Cris LaPierre
Cris LaPierre on 8 Nov 2022
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

Categories

Find more on Shifting and Sorting Matrices in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by