averaging and interpolating random number of rows of data

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!

[Adam Danz]

See

• readtable
• readmatrix
• mean, movmean (not sure how their time points will be used for averaging)
• interp1

[Bryan Tan]

Okay I used xlsread() to read my spreadsheet to remove all the variables and only numbers. So those cells that were initially 'particle 1 or 2...' are labelled as NaN.

For now how do I average the first value after every NaN, then 2nd value after NaN and so on? Is there a way to do that?

eg:

I have 3 rows of NaN, but I have numbers after that. So after every 3rd NaN, I want to make it as row 1, so I can average each row 1.

( the first data has no NaN before it):

1

2

3

NaN

NaN

NaN

4

5

6

10

NaN

NaN

NaN

7

8

9

So the output average should be:

4

5

6

10

Thank you.

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

[Adam Danz]

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.

https://www.mathworks.com/help/matlab/ref/xlsread.html

I didn't follow your averaging example. I can't imaging what would produce an avg of 4, for example.

[Bryan Tan]

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)

Answer 1

[Image Analyst]

Yes, try this (untested):

badRows = isnan(T{:, 1}); % Find nans in column 1.
T = T(~badRows, :); % Extract all rows except the bad rows.

[Bryan Tan]

Okay thank you very much. I had installed the latest matlab now and the code you provided works in extracting all the values except NaN and re input them into the table. Thank you very much!

Next, how can you code it in a way that if the first number in the column (time) is 0, it will consider it as a new row, so I can average every 1st,2nd,3rd..... row after every 0 in column 1 and 2.

Thank you very much!

[Image Analyst]

Not sure I understand your new question. Please give an example input table and output table.

[Bryan Tan]

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!!