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

1 view (last 30 days)
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)

Categories

Find more on Matrices and Arrays 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!