Export averaged values from excel according to event

I would like to average and export values from excel according to a defined event/category.

Specifically, every time there is an event in column B (represented in the attached doc as [h]) I would like to average the values for columns I and K until the next event. I would then like to repeat this process between each event until the end of the experiment and export the averaged values to a separate .csv file. I would also like to repeat this process for multiple .xlsx files in a folder.

Could someone please advise if this is possible? Thanks MATLAB community!

Answer 1

[Voss]

your_folder = '.';
output_file_name = './master_averages.csv';
output_var_names = {'Start_Time','End_Time','I_avg','K_avg'};

S = dir(fullfile(your_folder,'*.xlsx'));

ffn = fullfile({S.folder},{S.name});

cols = [9 11]; % I'm assuming all files have the same column order

T_new = cell(1,numel(ffn));

for jj = 1:numel(ffn)
    
    T = readtable(ffn{jj},'VariableNamingRule','preserve');
    
    event_rows = find(strcmp(T{:,2},'h'));
    N_events = numel(event_rows);
    event_rows(end+1) = size(T,1)+1;
    
    data = NaN(N_events,4);
    for ii = 1:N_events
        data(ii,:) = [T{event_rows(ii+[0 1])+[0;-1],1}.' ...
            mean(T{event_rows(ii):event_rows(ii+1)-1,cols},1)];
    end
    
    T_new{jj} = array2table(data,'VariableNames',output_var_names);
    
    [~,fn,ext] = fileparts(ffn{jj});
    T_new{jj}.file = repmat({[fn ext]},size(T_new{jj},1),1);
    T_new{jj} = T_new{jj}(:,[end 1:end-1]);
    
end

T_new = vertcat(T_new{:});

writetable(T_new,output_file_name);

% check
disp(readtable(output_file_name))

                file                Start_Time    End_Time      I_avg        K_avg  
    ____________________________    __________    ________    _________    _________

    {'Calibration_Example.xlsx'}        1.6         2.33       -0.22613    0.0060652
    {'Calibration_Example.xlsx'}       2.37          3.2       -0.17159      0.10171
    {'Calibration_Example.xlsx'}       3.23          4.3       -0.10055      0.17838
    {'Calibration_Example.xlsx'}       4.33         5.07      -0.022583      0.27757
    {'Calibration_Example.xlsx'}        5.1         5.77       0.039576      0.36209
    {'Calibration_Example.xlsx'}        5.8         6.53        0.11219      0.43751
    {'Calibration_Example.xlsx'}       6.57         7.33        0.16085       0.5174
    {'Calibration_Example.xlsx'}       7.37         8.17        0.22738      0.58427
    {'Calibration_Example.xlsx'}        8.2          8.8        0.30034      0.65417
    {'Calibration_Example.xlsx'}       8.83          9.5        0.36613      0.73275
    {'Calibration_Example.xlsx'}       9.53        10.23        0.42703      0.81404
    {'Calibration_Example.xlsx'}      10.27         11.3         0.4815      0.88526
    {'Calibration_Example.xlsx'}      11.33        12.03        0.51702      0.94639
    {'Calibration_Example.xlsx'}      12.07         12.8        0.57278       1.0287
    {'Calibration_Example.xlsx'}      12.83        13.77        0.62439       1.0994
    {'Calibration_Example.xlsx'}       13.8         14.9        0.66153       1.1771
    {'Calibration_Example.xlsx'}      14.93         15.9        0.72058       1.2475
    {'Calibration_Example.xlsx'}      15.93        16.87        0.77673       1.3143
    {'Calibration_Example.xlsx'}       16.9        17.87        0.82793       1.3714
    {'Calibration_Example.xlsx'}       17.9           19        0.86575       1.4284

[Annabel Sorby-Adams]

Perfect, thank you!

[Voss]

You're welcome!