How to get mean / averaged values among of duplicated data array?

Hi, Community

I have a question about how to get mean value among of duplicated data array. So i have this data for example :

{TIME} {DATA}
2015-11-08 12:00:00 31.68 
2015-11-08 12:01:00 37.67 
2015-11-08 12:02:00 36.66 
2015-11-08 12:03:00 39.66 
2015-11-08 12:04:00 31.15 %% Duplicated Time Data 
2015-11-08 12:04:00 33.75 %% Duplicated Time Data 
2015-11-08 12:04:00 35.65 %% Duplicated Time Data 
2015-11-08 12:04:00 39.75 %% Duplicated Time Data 
2015-11-08 12:05:00 39.64 
2015-11-08 12:06:00 31.64 
2015-11-08 12:07:00 31.63 
2015-11-08 12:08:00 31.62 
2015-11-08 12:09:00 41.81 
2015-11-08 12:10:00 31.61 %% Duplicated Time Data 
2015-11-08 12:10:00 41.51 %% Duplicated Time Data 
2015-11-08 12:10:00 38.61 %% Duplicated Time Data 
2015-11-08 12:10:00 33.61 %% Duplicated Time Data 
2015-11-08 12:11:00 30.60 

The data above is a timetable array with variable data in each of timestamp data. I want to automatically change the duplicated timestamp data to become 1 timestamp data by averaging the data in each of those duplicated data's variables. In this case, i want to change all of four (4) duplicated 2015-11-08 12:04:00 timestamp data's variable to become 1 data (2015-11-08 12:04:00) instead by averaging :

31.15

33.75

35.65

39.75

---------- +

140.3 / 4 = 35.075

And implemented to the duplicated 2015-11-08 12:10:00 timestamp data's variable also automatically, so the data become :

{TIME} {DATA}
2015-11-08 12:00:00 31.68 
2015-11-08 12:01:00 37.67 
2015-11-08 12:02:00 36.66 
2015-11-08 12:03:00 39.66 
2015-11-08 12:04:00 35.075 %% Duplicated Time Data 
2015-11-08 12:05:00 39.64 
2015-11-08 12:06:00 31.64 
2015-11-08 12:07:00 31.63 
2015-11-08 12:08:00 31.62 
2015-11-08 12:09:00 41.81 
2015-11-08 12:10:00 36.335 %% Duplicated Time Data  
2015-11-08 12:11:00 30.60 

Could it possible to create a code or function to do that? Iam very grateful if anyone would lend me a hand to help me in solve my probleme here... Thank you so much, Everyone.... /.\ /.\ /.\

Answer 1

[Simon Chan]

Use function groupsummary

opts = detectImportOptions('demo.txt');
opts.VariableNames = {'Time','Data'};
T = readtable('demo.txt',opts);
groupsummary(T,'Time','mean')

ans = 12×3 table
           Time            GroupCount    mean_Data
    ___________________    __________    _________

    2015-11-08 12:00:00        1           31.68  
    2015-11-08 12:01:00        1           37.67  
    2015-11-08 12:02:00        1           36.66  
    2015-11-08 12:03:00        1           39.66  
    2015-11-08 12:04:00        4          35.075  
    2015-11-08 12:05:00        1           39.64  
    2015-11-08 12:06:00        1           31.64  
    2015-11-08 12:07:00        1           31.63  
    2015-11-08 12:08:00        1           31.62  
    2015-11-08 12:09:00        1           41.81  
    2015-11-08 12:10:00        4          36.335  
    2015-11-08 12:11:00        1            30.6  

[Tyann Hardyn]

thats SOOO great, Sir... I remember you, Mr Simon, you helped me a lot a long time ago... Thank you so much..... And one question, Sir. Is the function can ignore NaN value to be averaged?

[Simon Chan]

Hi Tyann, the function ignore NaN value as well.

See the example where I modified the .txt file a little bit.

opts = detectImportOptions('demo.txt');
opts.VariableNames = {'Time','Data'};
T = readtable('demo.txt',opts)

T = 18×2 table
           Time            Data 
    ___________________    _____

    2015-11-08 12:00:00    31.68
    2015-11-08 12:01:00    37.67
    2015-11-08 12:02:00    36.66
    2015-11-08 12:03:00    39.66
    2015-11-08 12:04:00    31.15
    2015-11-08 12:04:00      NaN
    2015-11-08 12:04:00    35.65
    2015-11-08 12:04:00    39.75
    2015-11-08 12:05:00    39.64
    2015-11-08 12:06:00      NaN
    2015-11-08 12:07:00    31.63
    2015-11-08 12:08:00    31.62
    2015-11-08 12:09:00    41.81
    2015-11-08 12:10:00    31.61
    2015-11-08 12:10:00    41.51
    2015-11-08 12:10:00    38.61

groupsummary(T,'Time','mean')

ans = 12×3 table
           Time            GroupCount    mean_Data
    ___________________    __________    _________

    2015-11-08 12:00:00        1           31.68  
    2015-11-08 12:01:00        1           37.67  
    2015-11-08 12:02:00        1           36.66  
    2015-11-08 12:03:00        1           39.66  
    2015-11-08 12:04:00        4          35.517  
    2015-11-08 12:05:00        1           39.64  
    2015-11-08 12:06:00        1             NaN  
    2015-11-08 12:07:00        1           31.63  
    2015-11-08 12:08:00        1           31.62  
    2015-11-08 12:09:00        1           41.81  
    2015-11-08 12:10:00        4          36.335  
    2015-11-08 12:11:00        1            30.6