averaging and interpolating random number of rows of data

1 view (last 30 days)
Hello, I have a list 800 particles data in an excel file. A total of 174755 rows...
Is there a way to extract these values and then average them based on their time point, later interpolate them based on the time point. I would like to average the first row of every particle 1,2,3....800. So after NaN, it will consider that as row 1,2,3...last number before NaN, then another row 1 again.
For example:
row 1 1 5
row 2 2 6
row 3 3 7
NaN
NaN
NaN
row 1 4 8
row 2 5 9
row 3 6 10
row 4 10 11
NaN
NaN
NaN
row 1 7 1
row 2 8 2
row 3 9 3
So the desired output average should be:
average of row 1 4 4.67
average of row 2 5 5.75
average of row 3 6 6.67
average of row 4 10 11
filename = 'plottemperature.xlsx';
T = xlsread(filename); %read excel file
[rows,columns] = size(T); %List the number of rows and columns
nanLocations = isnan(T); %declare all NaN locations in the excel file
for row = 1: rows
for col = 1:columns
if ~nanLocations(row,col) %I wanted to write it so that after the 3rd NaN it will be another row 1
%I am not sure how to continue from here to set the rows after every NaN as row 1 and then average all row 1
Please guide me. Thank you very much!
  4 Comments
Adam Danz
Adam Danz on 29 Sep 2020
Unless you're using an old version of matlab, you sould not use xlsread. Instead, use one of the other functions recommended in the documentation. See the top of this page.
I didn't follow your averaging example. I can't imaging what would produce an avg of 4, for example.
Bryan Tan
Bryan Tan on 30 Sep 2020
Alright I had downloaded the latest matlab and I can use read table code now. Thanks.
In the simplified example i gave, the average have 4 rows because some of the data have 4 rows, some have 3. So it will be:
(1+4+7)/3 (The average of all the first rows)
(2+5+8)/3 (The average of all the second rows)
(3+6+9)/3 (The average of all the third rows)
10/1 (Since there is only 1 forth row, its just 10)

Sign in to comment.

Answers (1)

Image Analyst
Image Analyst on 29 Sep 2020
Yes, try this (untested):
badRows = isnan(T{:, 1}); % Find nans in column 1.
T = T(~badRows, :); % Extract all rows except the bad rows.
  3 Comments
Image Analyst
Image Analyst on 30 Sep 2020
Not sure I understand your new question. Please give an example input table and output table.
Bryan Tan
Bryan Tan on 30 Sep 2020
Edited: Bryan Tan on 30 Sep 2020
Sorry for the bad explanation, I will create a table:
Column 1(Time) Column 2 (Temperature)
row 1 0 20
row 2 1 25
row 3 2 28
row 1 0 23 Because column 1 is '0' again, this will be row 1 again. row 2 1.1 24
row 3 2.1 27
row 4 3.1 32 Some data have more rows than others
Desired Output
Column 1(Time) Column 2 (Temperature)
Average of all row 1s 0 21.5
Average of all row 2s 1.05 24.5
Average of all row 3s 2.05 27.5
average of all row 4s 3.1 32
I hope this is clearer now. Currently, I am up until here, I was playing around with mean codes but could not figure out.
filename = 'plottemperature.xlsx'; %declare file name
T = readtable(filename); %read the file
[r, c] = size(T); %display number of rows and columns
%ca=cell(rows,columns)%crete array of rows and columns
badRows = isnan(T{:, 1}); % Find NaNs in column 1
T = T(~badRows, :); %Extract all rows except the bad rows
Tm=T{:,:}; %convert table to matrix
a=0;
if Tm(:,1)==0
%I am thinking when column 1 = 0 then move the rows below it over to 2 new columns.
%But it is not ideal because I will need to check every new columns for 0 to repeat the moving of the rows over again.
%Once every numbers are in their new columns then I will average every even columns and odd columns based on their rows.
end
Thank you!!

Sign in to comment.

Products


Release

R2020a

Community Treasure Hunt

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

Start Hunting!