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

1 view (last 30 days)
Tyann Hardyn
Tyann Hardyn on 26 Mar 2022
Commented: Simon Chan on 26 Mar 2022
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.... /.\ /.\ /.\

Accepted Answer

Simon Chan
Simon Chan on 26 Mar 2022
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
  2 Comments
Simon Chan
Simon Chan on 26 Mar 2022
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

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by